Mysql配置主从同步

1. 主库

docker run -p 3306:3306 --name mysql-slave \
-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:5.7

增加一个从库用于备份(或一些对数据实时性的统计等)

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:5.7

2. 主库增加配置

vim /mydata/mysql/conf/my.cnf

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqlId]
init_connect='SET collation_connection=utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

[mysqld]
server_id = 1
log-bin= mysql-bin
read-only=0 
binlog-do-db=mall

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
修改完重启

3. 从库增加配置（只修改了一个mysqlId）

vim /mydata/mysql/slave/conf/my.cnf

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqlId]
init_connect='SET collation_connection=utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

[mysqld]
server_id = 2
log-bin= mysql-bin
read-only=0 
binlog-do-db=mall

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

4. 主库配置(连接Navicat)
   执行命令

GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' identified by 'xxx'; //(账号密码)
show master status;

5. 从库执行(配置同步前要先把之前的数据同步过去)
   告诉从Mysql要同步哪个

change master to master_host='120.79.114.33',master_user='slave',master_password='xx',master_log_file='mysql-bin.000001',master_log_pos=0,master_port=3306;
改完之后重启一下docker
show slave status; (Slave_IO_Running 跟Slave_SQL_Running 都必须为Yes)

如何同步报错等 进入docker查看日志，然后调试重新开启

STOP  SLAVE;
START SLAVE;
SHOW SLAVE STATUS

6. 修改同步表(直接停止slave 修改conf文件，然后即可)

主库从库里都增加需要同步的表
binlog-do-db=mall-message
然后重启容器即可