介绍

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

使用的好处

  1. 提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。

  2. 提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据

  3. 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能
    注意，mysql是异步复制的，而MySQL Cluster是同步复制的

Docker搭建主从同步

下载mysql 镜像

创建 两个mysql docker容器

1
2
docker run -d -p 3307:3306 --name mysql_instance1 -e MYSQL_ROOT_PASSWORD=123456 mysql
docker run -d -p 3308:3306 --name mysql_instance2 -e MYSQL_ROOT_PASSWORD=123456 mysql

连接数据库试一下

1
2
[root@szj ~]# mysql -h 192.168.3.148 -P 3307 -u root -p 123456
mysql> show databases;

配置my.cnf

进入第一个容器内部

1
2
3
4
5
6
[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不能使用，需要安装

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

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

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

添加如下 (主库)

1
2
3
#(不能为0，刚开始配置的是0，导致后面主从同步有问题，后来改为10)
server-id=1
log-bin=mysql-bin
1
2
3
4
5
6
7
8
#数据库日志binlog保存时效
max_binlog_size = 500M
expire_logs_days = 15
#log日志每达到设定大小后，会使用新的bin log日志。如mysql-bin.000002达到500M后
#创建并使用mysql-bin.000003文件作为日志记录。
max_binlog_size：bin 
#保留指定日期范围内的bin log历史日志，上示例设置的15天内。
expire_logs_days：

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

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

重启docker容器

先停止容器

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

再启动container mysql_instance1和mysql_instance2

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

配置master

登录3307创建用户并授权

1
2
3
4
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)

配置slave

登录3308创建用户并授权

1
2
3
4
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)

查看master容器的独立ip地址

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

登录master查看binlog的pos位置

1
2
3
4
5
6
7
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)

登录slave执行如下命令

1
2
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和端口才能够搭建成功)

查看主从同步状态

1
mysql> show slave status \G;

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

开启主从同步

1
2
3
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加密规则修改了导致的，我们这里改为老版本的加密规则。

修改用户的加密规则：

1
2
3
4
5
6
7
8
9
10
11
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导出再导入。然后重新开启主从同步，就可以了。）

测试

连接master创建个数据库test

连接slave，查询数据库

主从复制配置成功。

问题的解决方法

MySQL数据同步出现slave_sql_running：no或slave_io_running：no问题的解决方法

slave从数据库机器上有两个关键的进程：
一个是slave_io_running，负责与主机的io通信；
一个是slave_sql_running，负责自己的slave mysql进程。

slave_io_running：no

肯定是因为log文件或者pos位置跟主服务器不对应导致的；
解决办法：
修改从服务器与主服务器一致即可，每次主服务器的sql操作都会影响pos位置，主从服务器的pos同时改变；

1
2
3
4
mysql-> slave stop;  #关闭服务
mysql-> change     master to master_log_file='主服务器的文件', MASTER_LOG_POS=0;  
mysql-> slave start; #开启服务                              
mysql-> show slave status\G；  #查看状态是否为yes

slave_sql_running：no

一般是因为执行sql语句时发生错误导致，这种错误有很多触发方式，在此不一一列举：
解决办法：
简单粗暴：关闭服务->跳过一条记录->启动服务->查看是否为yes，否：再次跳过一条记录直到yes为止

1
2
3
4
mysql-> slave stop;  #关闭服务
mysql-> change     master to master_log_file='主服务器的文件', MASTER_LOG_POS=0;  
mysql-> slave start; #开启服务                              
mysql-> show slave status\G；  #查看状态是否为yes

读负载均

这里也只是讲一下思路。
读负载均衡解析：程序中，当我们连接从库时，可以加一个读均衡器，用均衡器随机得出我们连接选择。
我们给性能较好的slave分配更高的权重，这样就会有更高的几率连接该机器使其更好地发挥作用。
读负载均衡实现：当我们选择连接slave机器的时候，先读一下配置文件，查看针对该表设置的各个slave机器的权重：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
'mdb' => [   //主数据库
    'dbcharset' => 'utf8',
    'dbhost' => 'localhost',     
    'dbuser' => 'test',
    'dbpw' => '123456',
    'dbname' => 'myslave',
    'dbport' => NULL,
    'tbpre' => '',
    'slaves_balancer' => [ //读负载均衡器 
            'slave1'=>3,
            'slave2'=>1
        ],    
],
​
'slave1'=>[   //从数据库slave1
    'dbhost' => '192.168.xxx.xx1',
    'dbuser' => 'test',
    'dbpw' => '123456',
    'dbcharset' => 'utf8',
    'dbname' => 'myslave',
    'tbpre' => '',
],
​
'slave2'=>[   //从数据库slave2
    'dbhost' => '127.0.0.1',
    'dbuser' => 'test',
    'dbpw' => '123456',
    'dbcharset' => 'utf8',
    'dbname' => 'myslave',
    'tbpre' => '',
],

这里我们给读负载均衡器增加了slave2并且给了其权重1，slave1的权重为3；
注意：slave2我没有配置，所以给其设置iphost为127.0.0.1，不同于于mdb中的localhost的是，var_dump打印出的host为’127.0.0.1’，而不是’localhost’。测试效果一样。

‘slaves_balancer’ => [‘slave1’=>3,’slave2’=>1], //读负载均衡器

进而：

$gravity = [‘slave1’=>3,’slave2’=>1];

我们遍历这个gravity：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$pick = '';
foreach ($gravity as $sid => $weight)
{
    $total_weight += $weight;
    $weights[$total_weight] = $sid;
}

$rand_weight = mt_rand(1, $total_weight);
​
foreach ($weights as $weight => $sid)
{
    if ($rand_weight <= $weight)
    {
        $pick = $sid; break;
    }
}

最终我们得到的$pick就是我们最终的连接选择

返回 MySql 系列