Mysql数据库主从同步 一、安装Mysql数据库 二、数据库配置 三、链接主从
数据库主从配置, 使用docker来配置
拉取mysql 5.7镜像:docker pull mysql:5.7
创建2个mysql数据库: 一主一从docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --name mysqlM mysql:5.7
docker run -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --name mysqlS mysql:5.7
二、数据库配置
1. 配置master
进入mysqlM交互模式docker exec -it mysqlM /bin/bash
安装vim编辑器, 编辑vim /etc/mysql/my.cnf
apt-get update
apt-get install vim -y
添加配置: vim /etc/mysql/my.cnf
[mysqld]
## 同一局域网内注意要唯一
server-id=100
## 开启二进制日志功能,可以随便取(关键)
log-bin=mysql-bin
重启mysql服务service mysql restart
启动Master,重启服务导致docker容器停止docker start mysqlM
2. 配置slave
进入mysqlS交互模式docker exec -it mysqlS /bin/bash
添加配置: vim /etc/mysql/my.cnf
[mysqld]
## 设置server_id,注意要唯一
server-id=101
## 开启二进制日志功能,以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin
重启mysql服务service mysql restart
启动Slavedocker start mysqlS
三、链接主从
1. 查看master状态
进入mysqlM交互模式docker exec -it mysqlM /bin/bash
进入mysql交互模式mysql -uroot -p123456
在Master中创建同步账户,用户主从数据同步CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
执行: show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 617 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
File 和Position字段非常重要,后续要要用到! 重要!!!
此刻数据库不要有任何操作,否则会引起这两个字段变化,无法链接主从!
也可以通过锁表的方式,防止数据库修改:
-
FLUSH TABLES WITH READ LOCK;
将数据库锁住,仅仅允许读,以保证数据一致性; -
FLUSH PRIVILEGES;
刷新权限 -
UNLOCK TABLES;
操作完,解锁表
2. 配置slave同步
进入mysqlS交互模式docker exec -it mysqlS /bin/bash
进入mysql交互模式,执行命令mysql -uroot -p123456
change master to master_host='192.168.183.220', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 617, master_connect_retry=30;
- master_host :Master的地址
- master_port:Master的端口号,指的是容器的端口号
- master_user:用于数据同步的用户
- master_password:用于同步的用户的密码
- master_log_file:指定 Slave 从哪个日志文件开始复制数据,即上文中提到的 File 字段的值
- master_log_pos:从哪个 Position 开始读,即上文中提到的 Position 字段的值
- master_connect_retry:如果连接失败,重试的时间间隔,单位是秒,默认是60秒
-- 来源: https://www.cnblogs.com/songwenjie/p/9371422.html
启动slave: start slave;
关闭slave: stop slave
查看启动状态: show slave status G;
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.183.220
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 617
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 617
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: a4b3e332-0c73-11ea-9906-0242ac110002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
Slave_IO_Running和Slave_SQL_Running都为Yes,说明主从搭建成功.
使用start slave开启主从复制过程后,如果SlaveIORunning一直是Connecting,则说明主从复制一直处于连接状态,这种情况一般是下面几种原因造成的,我们可以根据 Last_IO_Error提示予以排除。
- 网络不通: 检查ip,端口
- 密码不对: 检查是否创建用于同步的用户和用户密码是否正确
- pos不对: 检查Master的 Position
-- 来源: https://www.cnblogs.com/songwenjie/p/9371422.html
3. 测试主从
思路: 操作主数据库,查看从数据库变化,创建数据库:create database db1 default character set utf8mb4 collate utf8mb4_unicode_ci;
参考: