介绍

主从同步使得数据可以从一个服务器复制到其他服务器上,在复制数据时,一个服务器充当主服务器(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_instance1mysql_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 系列