mysql 多主备_MySQL复制原理与MySQL 8.0一主多备配置

最新推荐文章于 2023-04-25 01:21:13 发布

顺德韭菜星

最新推荐文章于 2023-04-25 01:21:13 发布

阅读量189

点赞数

文章标签： mysql 多主备

本文链接：https://blog.csdn.net/weixin_29050271/article/details/113230411

版权

当前网上关于MySQL一主多备配置内容过于老旧，大部分都是基于MySQL5.6的配置，截止2020-7-31，当前MySQL已经到8.0.21。因此针对MySQL 8.0进行一主多备配置的指导就具有一定的参考意义。

本文假设在你的主机、备机上都已经安装好了MySQL，如果还没装，请自行搜索一下，安装指南挺多的，如果你已经联网，可通过sudo apt install mysql-server -y 在线安装。

1.复制原理

动手之前，首先读者朋友需要对复制有一定大概的了解，了解复制基本原理和工作过程。

复制解决的问题是将一台服务器的数据与其他服务器保持同步。MySQL支持两种复制方式：基于行的复制和基于语句的复制。两种复制的方式都是在主库上记录二进制日志，在北库重放日志的方式来实现异步的数据复制。

1.1.基于语句复制

基于语句的复制在数据库领域是比较少见的，MySQL 5.0以及之前的版本是基于语句的复制。基于语句的复制的基本原理是：主库记录对数据造成更改的查询，然后在备库读取并重放这些事件，也就是把主库执行过的SQL在备库再执行一遍。

基于语句复制的好处：实现简单。

不会占用太多带宽。

易于理解，方便问题定位。

允许更加灵活的操作，比如并不要求主备schema完全一致。

但其实基于语句的复制其实问题挺多：更新必须是串行的，这会用到更多的锁。关于Binlog记录更新语句按提交来排序，咱们在《MySQL事务隔离级别、数据一致性和加锁分析》一文提到过。

要求主备库环境一样，但其实很难做到，比如主备库同一个SQL的执行时间、获取用户信息(比如调用CURRENT_USER()函数)。

也就是说MySQL 5.0以及之前的复制技术其实很不成熟，说直白点就是Bug很多！

1.2.基于行复制

MySQL 5.1开始支持基于行的复制，这种方式会将实际数据记录在二进制日志中。基于行的复制的最大好处是可以正确地复制每一行。

相比基于语句的复制，一般来说，基于行的复制模式整体上更优，在实际应用中适合大部分场景。

基于行复制的优点：几乎没有基于行复制模式无法处理的场景。

有效减少锁的使用。

基于行的复制能够占用更少的CPU。

缺点：复制更加复杂。

复制需要占用更多的带宽。

不易理解，无法判断执行了哪些SQL。这是由于语句不在日志记录。很多时候就像一个黑盒。

1.3.复制到底快不快

复制理论上非常快！

MySQL二进制日志转储线程并不是通过轮询的方式从主库请求事件，而是由主库来通知备库新的事件。轮询比较低效，采用事件驱动就高效得多。因此，只要复制线程被唤醒并且能够通过网络传输数据，事件就会很快到达备库。

同时复制延迟也是很普遍的现象，部分原因在于MySQL单线程复制模式造成无法充分利用备机的CPU等系统资源。

1.4.复制是如何工作的

复制总体分三步：在主库把数据更改记录到二进制日志Binlog，这些记录称为二进制日志事件。

备库将主库的日志复制到自己的中继日志(Relay Log)中。

备库读取中继日志的事件，将其重放到备库数据之上。

如下图所示。

具体来说：Binlog记录主库的数据变更。

I/O线程负责从主机读取Binlog并写入中继日志，具体工作方式是：I/O线程跟主机建立一个普通的客户端连接，然后在主机上启动一个特殊的二进制转储线程(binlog dump)。

转储线程会读取主库上二进制日志的事件，然后发送给备库(转储线程不会去轮询事件，当线程追赶上主库，则进行休眠状态，主库通过信号量通知新事件产生时唤醒转储线程)。

备库I/O线程将接收到的事件记录到中继日志中。

SQL线程：负责读取中继日志的事件并重放。

上述复制架构实现了获取事件和重放事件的解耦，两个过程可以异步进行。

2.一主多备配置

好了，有了上面的基础，下面我们了解一下具体配置过程。配置过程分为：主库创建专用于复制的账号，给账号授权，并设置密码。

主库配置Master角色，需要同步的DB，重启服务。

从库配置Master角色，需要同步的DB，重启服务。

从库设置与主的连接信息，启动Slave。

2.1.主库配置

创建复制账号并设置权限

首先，设置创建专门用于复制的账号，如下所示。

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.10 sec)

其中，repl是用户名，123456是password。%是一个通配符表示主机接受任何主机通过此账号进行连接，处于安全考虑可以限定可访问的IP，比如192.168.0.%。读者也可反差mysql.user表查看用户是否真的创建成功了，如下所示。

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select user,host from user;

+------------------+-----------+| user | host |

+------------------+-----------+| repl | % |

| mysql.infoschema | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+------------------+-----------+5 rows in set (0.00 sec)

给创建的账号设置权限，如下所示。

mysql> GRANT replication slave ON *.* TO 'repl'@'%';

Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.03 sec)

配置主库角色、同步DB

配置文件在/etc/mysql/mysql.conf.d/mysqld.cnf，配置如下。

andy@andy:~$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

server_id = 1

log_bin = /var/log/mysql/mysql-bin.log

sync_binlog = 1

binlog_format = ROW

binlog_do_db = test

binlog_ignore_db= mysql

default_authentication_plugin = mysql_native_password

其中：server_id用于给服务器编号，注意主备编号不要重复。

log_bin：Binlog稳健路径。

sync_binlog：开启Binlog同步，在每次提交事务前会将二进制日志同步到磁盘。

binlog_format：binlog的日志格式，我们这种基于行的复制。

binlog_do_db：需要记录Binlog的DB，有多个的话设置多行。

binlog_ignore_db：忽略的DB。

default_authentication_plugin配置客户端连接的认证方式配置，非必须，当发生备库无法连接主库时可能需要配置，我们在后面再讲。

配置好后，重启服务。

andy@andy:/etc/mysql$ systemctl restart mysql

查看主库的状态

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

正常。

2.2.备库配置

配置被库角色、同步DB

配置文件在/etc/mysql/mysql.conf.d/mysqld.cnf，配置如下。

andy@andy:~$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

server_id = 2

log_slave_updates = 1

log_bin = /var/log/mysql/mysql-bin.log

binlog_format = ROW

replicate_do_db = test

replicate_ignore_db = mysql

重启服务。

andy@andywang:~$ systemctl restart mysql

设置与主库的连接

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.0.109',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='123456',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=0;

Query OK, 0 rows affected, 2 warnings (0.30 sec)

其中：MASTER_HOST设置成主机的IP。MASTER_USER和MASTER_PASSWORD是我们之前在主机设置的用于复制的专用账号和密码。

MASTER_LOG_FILE需要和通过查看主库状态的binlog文件一致。

MASTER_LOG_POS复制位置默认为0，表示从头开始跟踪事件。这种配置可能不是最好的，比如数据库本来就很大，可以先手动同步一把，我们后面再讲讲。

启动slave

mysql> START SLAVE;

Query OK, 0 rows affected (0.04 sec)

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.109

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 156

Relay_Log_File: andywang-relay-bin.000002

Relay_Log_Pos: 371

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 156

Relay_Log_Space: 583

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 9d536a8f-ca71-11ea-ad0a-502b73d816ce

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

1 row in set (0.00 sec)

ERROR:

No query specified

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

配置成功。到这里我们主备配置就成功了。但我们还可以进一步验证一下，看十分真的起作用了。

注：

start slave可能会出现下面错误

Last_IO_Error: error connecting to master 'repl@192.168.0.109:3306' - retry-time: 60 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 目前本人也没找到好的解决办法，规避方法是把mysql用户登录密码加密规则还原成mysql_native_password，如下所示。

default_authentication_plugin = mysql_native_password

然后重启服务。

2.3.验证&测试

查看主备processlist.

主库：

mysql> show processlist;

+----+-----------------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+

+----+-----------------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+

+----+-----------------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+

6 rows in set (0.00 sec)

Binlog Dump说明在主库上已经启动了Binlog Dump转储线程。

备库：

mysql> show processlist;

+----+-----------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+

+----+-----------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+

+----+-----------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+

4 rows in set (0.01 sec)

说明备机I/O线程和SQL线程已经启动。

写入测试

写入前，主备如下：

# 主

select * from t1;

c1|c2|

--|--|

1| 1|

5| 5|

10|10|

# 备

select * from t1;

c1|c2|

--|--|

1| 1|

5| 5|

10|10|

写入后：

# 主

insert into t1(c1,c2) values (20,20);

insert into t1(c1,c2) values (30,30);

c1|c2|

--|--|

1| 1|

5| 5|

10|10|

20|20|

30|30|

# 备

mysql> select * from t1;

+----+------+

| c1 | c2 |

+----+------+

| 1 | 1 |

| 5 | 5 |

| 10 | 10 |

| 20 | 20 |

| 30 | 30 |

+----+------+

5 rows in set (0.00 sec)

至此，验证成功。

The end.

转载请注明来源，否则严禁转载。

顺德韭菜星

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 多主备_MySQL复制原理与MySQL 8.0一主多备配置

当前网上关于MySQL一主多备配置内容过于老旧，大部分都是基于MySQL5.6的配置，截止2020-7-31，当前MySQL已经到8.0.21。因此针对MySQL 8.0进行一主多备配置的指导就具有一定的参考意义。本文假设在你的主机、备机上都已经安装好了MySQL，如果还没装，请自行搜索一下，安装指南挺多的，如果你已经联网，可通过sudo apt install mysql-server -y 在...
复制链接

扫一扫