mysql主从同步配置步骤详解

1 介绍

主从同步使得数据可以从一个服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器(master)，其余的服务器充当从服务器(slave)。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

使用主从同步的好处：

• 提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
• 提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
• 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

注意，mysql是异步复制的，而MySQL Cluster是同步复制的

2 Docker搭建mysql主从同步

2.1 docker镜像

2.2 创建docker容器

 docker run -d -p 3307:3306 --name mysql_instance1 -e MYSQL_ROOT_PASSWORD=123456 mysql 

连接数据库试一下

[root@szj ~]# mysql -h192.168.3.148 -P3307 -uroot -p123456
mysql> show databases;

2.3 配置my.cnf

进入第一个容器内部

[root@szj docker]# docker exec -it mysql_instance1 /bin/bash
[root@szj ~]# docker exec -it mysql_instance1 /bin/bash
root@8c2a85985cd1:/# cd /etc/mysql/
root@8c2a85985cd1:/etc/mysql# ls
conf.d  my.cnf  my.cnf.fallback

刚创建的容器vi不能使用，需要安装

root@8c2a85985cd1:/etc/mysql# apt-get update
…
Reading package lists... Done
root@5890745bbef6:/etc/mysql# apt-get install vim

安装成功后，编辑my.cnf文件

 root@5890745bbef6:/etc/mysql# vi my.cnf 

添加如下

server-id=10(不能为0，刚开始配置的是0，导致后面主从同步有问题，后来改为10)
log-bin=mysql-bin

同样进入第二个容器，做上面操作，my.cnf添加如下内容

server-id=1(不能为0)
log-bin=mysql-bin

2.4 重启docker容器

先停止容器

[root@szj ~]# docker stop mysql_instance1
[root@szj ~]# docker stop mysql_instance2

再启动container mysql_instance1和mysql_instance2

[root@szj ~]# docker start mysql_instance1
[root@szj ~]# docker start mysql_instance2

2.5 配置master

登录3307创建用户并授权

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql>

2.6 配置slave

登录3308创建用户并授权

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql>

2.7 查看master容器的独立ip地址

root@8c2a85985cd1:/etc/mysql# [root@szj ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql_instance1
172.17.0.2

2.8 登录master，查看binlog的pos位置。

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

2.9 登录slave，执行如下命令

mysql> change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=701, master_connect_retry=30;
Query OK, 0 rows affected, 1 warning (0.08 sec)

注意：master_port是3306，是容器内部端口，非宿主机端口。(在公司搭建时，发现master_host和master_port是宿主机ip和端口才能够搭建成功)

2.10 查看主从同步状态

此时正常情况下Slave_IO_Running和Slave_SQL_Running状态是No。因为没有开启主从同步。

2.11 开启主从同步

mysql> start slave;//关闭主从同步用stop slave;
Query OK, 0 rows affected (0.10 sec)

再执行 show slave status \G 

如果Slave_IO_running:running,且Last_IO_Error：Authentication plugin 'caching_sha2_password' reported error。这种情况是因为mysql8加密规则修改了导致的，我们这里改为老版本的加密规则。

修改用户的加密规则：

mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.12 sec)
mysql>
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> ALTER USER 'slave'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

修改加密规则之后再重新进行上面配置。重新开启主从同步。

如果Slave_IO_Running和Slave_SQL_Running都为Yes，则说明配置成功：

(在某次搭建的过程中，出现如下报错，原因是master之前日志是binlog.000006，配置过程中改为日志文件为mysql-bin.000001，所以复制的时候最新的日志里找不到数据库。解决方式是mysqldump导出再导入。然后重新开启主从同步，就可以了。)

2.12 测试

连接master创建个数据库test

连接slave，查询数据库

主从复制配置成功。