Docker搭建mysql主从同步集群
使用docker方式创建,主从数据库服务器使用同一IP,端口号不一致
docker服务名分别为:
主库 mysql-master port: 3306
从库1 mysql-slave1 port: 3307
从库2 mysql-slave2 port:3308
主库
- 创建并启动MySQL主服务器
docker run -d -p 3306:3306 -v /home/mysql/sharedingsphere-env/master/conf:/etc/mysql/conf.d -v /home/mysql/sharedingsphere-env/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name mysql-slave3 mysql:8.0.29
- 创建配置文件
vim /home/yuluo/shardingsphere-env/master/conf/my.cnf [mysqld] # 服务器唯一id,默认值1 server-id=1 # 设置日志格式,默认值ROW binlog_format=STATEMENT # 设置需要复制的数据库,默认复制全部数据库 #binlog-do-db=mytestdb # 设置不需要复制的数据库 #binlog-ignore-db=mysql #binlog-ignore-db=infomation_schema 可以设置需要复制的数据库,默认情况下全复制
使用docker ps|grep mysql-master查看docker容器mysql- master的状态,确保正确启动
- 测试连接
进入MySQL主服务器登录mysql #进入容器:env LANG=C.UTF-8 避免容器中显示中文乱码 docker exec -it mysql-master env LANG=C.UTF-8 /bin/bash #进入容器内的mysql命令行 mysql -u root -p123456 #修改默认密码校验方式 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
- master节点创建slave用户
CREATE USER 'yy_slave'@'%'; -- 设置密码 ALTER USER 'yy_slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; -- 授予复制权限 GRANT REPLICATION SLAVE ON *.* TO 'yy_slave'@'%'; -- 刷新权限 FLUSH PRIVILEGES;
- 查询master节点mysql状态
show master status 记下File和Position的值。执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化。 执行结果如下 mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000002 | 2475 | | | | +---------------+----------+--------------+------------------+-------------------+
从库(以slave1为例)
- 创建并启动MySQL主服务器
docker run -d -p 3307:3306 -v /home/mysql/sharedingsphere-env/slave1/conf:/etc/mysql/conf.d -v /home/mysql/sharedingsphere-env/slave1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name mysql-slave3 mysql:8.0.29
- 创建配置文件
vim /home/yuluo/shardingsphere-env/slave1/conf/my.cnf [mysqld] # 服务器唯一id,默认值1 server-id=2
使用docker ps|grep mysql-master查看docker容器mysql- master的状态,确保正确启动
- 测试连接
进入MySQL主服务器登录mysql #进入容器:env LANG=C.UTF-8 避免容器中显示中文乱码 docker exec -it mysql-slave1 env LANG=C.UTF-8 /bin/bash #进入容器内的mysql命令行 mysql -u root -p123456 #修改默认密码校验方式 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
- 在从机中配置主从关系
CHANGE MASTER TO MASTER_HOST='docker宿主机IP', MASTER_USER='yy_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=2475;
- 启动从机的复制功能
START SLAVE; -- 查看状态(不需要分号) SHOW SLAVE STATUS\G mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 47.92.133.104 Master_User: yy_slave Master_Port: 3307 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 2666 Relay_Log_File: 73418672d89e-relay-bin.000002 Relay_Log_Pos: 514 Relay_Master_Log_File: binlog.000002 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: 2666 Relay_Log_Space: 731 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: 1 Master_UUID: 943a9c95-0538-11ee-8909-0242ac110003 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace:
注意以上输出中Slave_IO_Running: Yes,Slave_SQL_Running: Yes需要同时为yes才算成功
主从同步测试
在主机中执行以下SQL,在从机中查看数据库、表和数据是否已经被同步
CREATE DATABASE db_user; USE db_user; CREATE TABLE t_user ( id BIGINT AUTO_INCREMENT, uname VARCHAR(30), PRIMARY KEY (id) ); INSERT INTO t_user(uname) VALUES('zhang3'); INSERT INTO t_user(uname) VALUES(@@hostname);
问题
在从机配置过程中会遇到如下错误
The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
这是由于我们写my.cnf文件没有生效,具体原因是由于文件权限问题引发的主从服务器的server-id重复。可以通过命令show variables like 'server_id' 分别在主从服务器上进行server-id的查询。
mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 1 row in set (0.01 sec)
解决方案
- 修改my.cnf 的权限,chmod 644 my.cnf 然后重启一下容器slave1,slave2即可解决
- 不重启方案,手动修改server-id. 在查询到主从服务器的server-id一致后,使用 SET GLOBAL server_id=新的server-id值进行server-id的设置。设置完后重新执行命令start slave即可解决问题。