1.新建主服务器容器实例3307
docker run -p 3307:3306 --name mysql-master\ -v /mydata/mysql-master/log:/var/log/mysql \ -v /mydata/mysql-master/data:/var/lib/mysql \ -v /mydata/mysql-master/conf:/etc/mysql \ -e MYSQL_ROOT_PASSWORD=root \ -d mysql
2.进入//mydata/mysql-master/conf目录下新建my.cnf
# The MySQL Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL ##设置server_id 局域网唯一 server_id=101 ##开启二进制日志 log-bin=mysql-bin ###忽略不需要同步的数据库 binlog-ignore-db=mysql ##二进制日志使用内存 binlog_cache_size=1M ##二进制日志格式()mixed,statement,row binlog_format=mixed #二进制日志过期清理时间 expire_logs_days=7 ##跳过主从复制中所有错误或指定类型的错误,避免slave端复制中断 ###1062主键重复,1032主重数据不一致 slave_skip_errors=1062
3.重启master实例
docker restart 6d9cba13f549
4.进入mysql-master 容器
docker exec -it 6d9cba13f549 /bin/bash
5.master容器实例内创建数据同步用户
create user 'slaver'@'%' identified by 'root'; grant replication slave replication client on *.* to 'slave'@'%';
6.新建从服务器容器实例3308
docker run -p 3307:3306 --name mysql-slave\ -v /mydata/mysql-slave/log:/var/log/mysql \ -v /mydata/mysql-slave/data:/var/lib/mysql \ -v /mydata/mysql-slave/conf:/etc/mysql \ -e MYSQL_ROOT_PASSWORD=root \ -d mysql
7.进入/mydata/mysql-master/conf目录下新建my.cnf
# The MySQL Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL server_id=102 log-bin=mysql-bin binlog-ignore-db=mysql binlog_cache_size=1M binlog_format=mixed expire_logs_days=7 slave_skip_errors=1062 ##配置中继日志 relay_log=mall-mysql-relay-bin ##表示slave将复制事件写进自己的二进制日志 log_slave_updates=1 ##slave设置为只读 read_only=1
8.重启slave实例
docker restart 容器id
9.在主数据库查看主从同步状态
[root@localhost conf]# docker exec -it 6d9cba13f549 /bin/bash root@6d9cba13f549:/# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32 Server version: 8.0.26 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show master status -> ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000012 | 976 | | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
11.进入mysql-slave 容器
[root@localhost conf]# docker exec -it 46836c28419b /bin/bash root@46836c28419b:/# mysql -uroot -p Enter password: Welcome to the MySQL monitor.
12.在从数据库中配置主从复制
mysql>change master to master_host='172.17.0.4', master_user='slave', master_password='slave@master', master_port=3307, master_log_file='mysql-bin.000012', master_log_pos=156,master_connect_retry=30;
13.在从数据库中查看主从同步状态
mysql>show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.0.15 Master_User: slave Master_Port: 3307 Connect_Retry: 30 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 976 Relay_Log_File: mall-mysql-relay-bin.000002 Relay_Log_Pos: 1144 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: No Slave_SQL_Running: No 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: 976 Relay_Log_Space: 1358 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: 101 Master_UUID: c6dd0d39-7b79-11ec-acd6-0242ac110004 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: 1 row in set, 1 warning (0.01 sec)
14.在从数据库中开启主从同步
mysql>start slave; mysql>show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.0.15 Master_User: slave Master_Port: 3307 Connect_Retry: 30 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 976 Relay_Log_File: mall-mysql-relay-bin.000002 Relay_Log_Pos: 1144 Relay_Master_Log_File: mysql-bin.000012 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: 976 Relay_Log_Space: 1358 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: 101 Master_UUID: c6dd0d39-7b79-11ec-acd6-0242ac110004 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: 1 row in set, 1 warning (0.01 sec)
15.测试主库写入,从库同步主库
在主库新建表插入数据,查看从库有没有相应的变化