环境：
Mysql主库：39.104.x.x Mysql从库：122.114.x.x
1.主从配置文件
master：/home/sishu/mysql/conf/my.cnf
[mysqld]
server_id=1
log-bin=mysql-bin
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
slow_query_log = ON
slow_query_log_file=/var/lib/mysql/localhost-slow.log
long_query_time = 1
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[server]
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
slaver: /home/sishu/mysql/conf/my.cnf
[mysqld]
server_id=2
log-bin=mysql-bin
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
slow_query_log = ON
slow_query_log_file=/var/lib/mysql/localhost-slow.log
long_query_time = 1
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[server]
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
2.docker创建主从数据库
master：
docker run \
-d \
-p 13306:3306 \
--restart always \
--privileged=true \
-v /home/sishu/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /home/sishu/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=xxxxxxxx \
-e TZ="Asia/Shanghai" \
--name mysql_master \
mysql:5.7 \
--lower_case_table_names=1 \
--character-set-server=utf8 \
--collation-server=utf8_general_ci
slaver:
docker run \
-d \
-p 13306:3306 \
--restart always \
--privileged=true \
-v /home/sishu/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /home/sishu/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=xxxxxxxx \
-e TZ="Asia/Shanghai" \
--name mysql_slaver \
mysql:5.7 \
--lower_case_table_names=1 \
--character-set-server=utf8 \
--collation-server=utf8_general_ci
3.锁定主数据库
>flush tables with read lock;
4.备份主数据库
mysqldump -uroot -pxxxxxxxx --all-databases > bak20190307.sql
5.导入从数据库
mysql -uroot -pxxxxxxxxx < bak20190307.sql
6.配置主从数据库
Master:
docker exec -it mysql_master /bin/bash
mysql -uroot -pxxxxxxxx
>GRANT REPLICATION SLAVE ON *.* to 'sishu'@'%' identified by 'Sishu2111';
>show master status;
Slave:
docker exec -it mysql_slave /bin/bash
mysql -uroot -pxxxxxxxx
>change master to master_host='39.104.x.x',master_user='sishu',master_password='Sishu2111',master_log_file='mysql-bin.000003',master_log_pos=438,master_port=13306;
>start slave;
>show slave status\G
7.解锁主数据库
unlock tables;
8.测试主从复制
create database ddd;
