Docker部署主从MySQL

Docker部署主从MySQL

1. 拉取镜像

   docker pull mysql:5.7.37

2. 准备文件夹及配置文件

   mkidir /mysql/data/
   mkidir /mysql/conf.d/
   touch /mysql/my.cnf

   my.conf

   [mysqld]
   lower_case_table_names=1
   user=mysql
   log-bin=mysql-bin
   server-id=9
   character-set-server=utf8
   default_authentication_plugin=mysql_native_password
   secure_file_priv=/var/lib/mysql
   expire_logs_days=7
   sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
   max_connections=1000
   
   [client]
   default-character-set=utf8
   
   [mysql]
   default-character-set=utf8

   从库操作同上，my.conf略有不同：

   mkidir /mysql-slave/data/
   mkidir /mysql-slave/conf.d/
   touch /mysql-slave/my.cnf

   my.conf

   [mysqld]
   lower_case_table_names=1
   user=mysql
   log-bin=mysql-bin
   server-id=1
   slave-skip-errors=1032,1062
   
   character-set-server=utf8
   default_authentication_plugin=mysql_native_password
   secure_file_priv=/var/lib/mysql
   expire_logs_days=7
   sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
   max_connections=1000
   
   [client]
   default-character-set=utf8
   
   [mysql]
   default-character-set=utf8

3. 启动容器

   #主库
   docker run  -di -v /root/mysql/data:/var/lib/mysql -v /root/mysql/conf.d:/etc/mysql/conf.d -v /root/mysql/my.cnf:/etc/mysql/my.cnf -p 3306:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.37
   
   #从库
    docker run  -di -v /root/mysql-slave/data:/var/lib/mysql -v /root/mysql-slave/conf.d:/etc/mysql/conf.d -v /root/mysql-slave/my.cnf:/etc/mysql/my.cnf -p 3307:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.37

4. 配置主从

   主库

   #连接主库
   mysql -h 127.0.0.1 -P 3306 -u root -p123456
   ##创建sync用户
   create user 'sync'@'%' identified by '123';
   ##授权用户
   grant all privileges on *.* to 'sync'@'%' ;
   ###刷新权限
   flush privileges;
   #查看主服务器状态(显示如下)
   show master status;
   +------------------+----------+--------------+------------------+-------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.000002 |  2735529 |              |                  |                   |
   +------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.01 sec)

   从库

   #连接从库
   mysql -h 127.0.0.1 -P 3306 -u root -p123456
   change master to master_host='49.234.xx.xx',master_port=3306,master_user='sync',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0;
   #启用从库
   start slave;
   #查看从库状态，看到Slave_IO_Running: Yes Slave_SQL_Running: Yes即可
   show slave status\G;

参考：

1. https://zhuanlan.zhihu.com/p/90486568
2. https://blog.csdn.net/weixin_29664819/article/details/113137387