docker mysql 主主同步

转发自：https://blog.csdn.net/money9sun/article/details/85099134

第一步：安装docker   https://www.cnblogs.com/cyq632694540/p/10596276.html

第二步：检查mysql的镜像，并下载

# docker search mysql:5.7

# docker pull mysql:5.7

# docker images

REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
docker.io/mysql     5.7                 ae6b78bedf88        4 weeks ago         372 MB

第三步：开启两个mysql的容器

建立自定义的网络，用于两个容易互相通信

# docker network create --subnet=172.18.0.0/16 mynetwork

开启master01

# docker run -p 3308:3306 -p 22002:22 --name master01 -e MYSQL_ROOT_PASSWORD=123456 --net mynetwork --ip 172.18.0.2 -d mysql:5.7

开启master02

# docker run -p 3309:3306 -p 22003:22 --name master02 -e MYSQL_ROOT_PASSWORD=123456 --net mynetwork --ip 172.18.0.3 -d mysql:5.7

第三步：安装软件及修改配置

# docker ps -a

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                                      NAMES
a2e76f0faae1        mysql:5.7           "docker-entrypoint..."   26 hours ago        Up 25 hours         33060/tcp, 0.0.0.0:22004->22/tcp, 0.0.0.0:3309->3306/tcp   master02
1b6773fc8c3d        mysql:5.7           "docker-entrypoint..."   26 hours ago        Up 25 hours         33060/tcp, 0.0.0.0:22003->22/tcp, 0.0.0.0:3308->3306/tcp   master01

进入master01容器

# docker exec -it 1b6773fc8c3d /bin/bash

root@1b6773fc8c3d:/# apt-get update

root@1b6773fc8c3d:/# apt-get install vim

root@1b6773fc8c3d:/# cd /etc/mysql/mysql.conf.d

root@1b6773fc8c3d:/# ls

mysqld.cnf

修改配置文件

root@1b6773fc8c3d:/# vi mysqld.cnf

添加的内容如下：

server-id=31
auto-increment-increment=2
auto-increment-offset=1
log-bin=/var/log/mysql/mysql-bin.log
binlog_format=mixed
relay-log=relay-bin
log-slave-updates

保存；

service mysql restart 这时候容器会退出，再启动即可，命令 docker restart 容器id即可

同样的方法，进入master02容器，配置文件修改为

server-id=32
auto-increment-increment=2
auto-increment-offset=2 ### 这里要注意 offset是偏移量，保证不同的mysql主机生成的主键是不同的，避免主键冲突
log-bin=/var/log/mysql/mysql-bin.log
binlog_format=mixed
relay-log=relay-bin
log-slave-updates

保存并重启mysql服务。

第四步：创建同步账户，开启slave

1、进入master01容器

# mysql -u root -p

2、创建同步账户

mysql>grant replication slave,replication client on *.* to 'slave'@'%' identified by '123456';

mysql> flush privileges;

mysql> flush tables with read lock;

3、查看binlog的状态

mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     433 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

4、进入master02容器

# mysql -u root -p

5、创建同步账户

mysql>grant replication slave,replication client on *.* to 'slave'@'%' identified by '123456';

mysql> flush privileges;

mysql> CHANGE MASTER TO MASTER_HOST='172.18.0.2',MASTER_PORT=3308,MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=433; 

// PS：这边的用户信息，pos，和主服务器IP要填写正确；连接错误的话，请查看 show slave status G  里面的 【 Last_IO_Error 】和 【Last_SQL_Error】 错误信息，进行错误排查 

//相关操作

stop slave;

start slave;

show slave status G;

show master status;

mysql> start slave;

mysql> show slave status G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.0.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3184
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 1346
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

看到       Slave_IO_Running: Yes  Slave_SQL_Running: Yes 表示成功

7、查看master02 binlog的状态

mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     521 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

8、回到master01 并进入mysql控制台，创建同步账户

mysql> CHANGE MASTER TO MASTER_HOST='172.18.0.3',MASTER_PORT=3309,MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=521;

mysql> start slave;

mysql> show slave status G

2019-05-30 更新------>出现主主不同步的情况时：
进入mysql

> show slave status G; //查看错误日志

然后会有个提示 让你看下某个表下面的log

select * from performance_schema.replication_applier_status_by_worker G ;

然后看下什么原因导致的错误；解决问题后。
设置mysql的my.cnf 配置

添加此段信息 slave-skip-errors=all 跳过错误的同步，重启mysql 搞定