docker-compose 快速搭建Mysql主从

目录结构

├── docker-compose.yml
├── master
│   ├── conf
│   │   └── my.cnf
│   └── log
├── phpmyadmin
│   ├── config.user.inc.php
│   └── readme.md
├── readme.md
└── slave
    ├── conf
    │   └── my.cnf
    └── log

启动服务

docker-compose up -d

进入master服务器

docker exec -it docker-mysql-master bash

#登录mysql
root@798f7e9dc2d4:/# mysql

#查看server_id是否生效
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.01 sec)

#给slave服务器设置访问权限
mysql> create user 'slave'@'%' identified by '123456';
mysql> grant replication slave,replication client on *.* to 'slave'@'%';
mysql> flush privileges;
#mysql8以下版本使用
mysql> grant replication slave,replication client on *.* to 'slave'@'%' identified by "123456";
mysql> flush privileges;

进入slave服务器

docker exec -it docker-mysql-slave bash

#登录mysql
root@798f7e9dc2d4:/# mysql

#查看server_id是否生效
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 2     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

#连接master服务器 {master_log_file}和 {master_log_pos}通过在master执行mysql> show master status;获得
change master to master_host='docker-mysql-master',master_user='slave',master_password='123456',master_port=3306,master_log_file='{master_log_file}', master_log_pos={master_log_pos},master_connect_retry=30;

#启动slave
mysql> start slave;
#查看slave 状态
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: docker-mysql-master
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1063
               Relay_Log_File: 72e31f8337e8-relay-bin.000002
                Relay_Log_Pos: 509
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
           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: 1063
              Relay_Log_Space: 725
              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: 7c8b7f66-4388-11ec-8cef-0242ac180002
             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.00 sec)

#如上,如果下面两个都是yes说明配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#如果不全是yes,需要先stop slave/reset slave;再执行change master...及后续步骤

通过phpmyadmin测试

http://localhost:8080/

代码

https://github.com/chudaozhe/docker-mysql-master-slave

参考

https://www.cnblogs.com/haima/p/14341903.html

感谢阅读这篇文章,如果你喜欢,或者遇到了问题,可以关注我的公众号