Mysql主从复制

发表于 | 分类于
Mysql主从复制

主从复制要求

MySQL 主从复制对主机和 MySQL 的要求

（1）主从服务器操作系统版本和位数必须一致；
（2）主节点（Master）和从节点（Slave）数据库版本必须一致；
（3）主节点（Master）和从节点（Slave）数据库中的数据必须一致；
（4）主节点（Master）需要开启二进制日志；
（5）主节点（Master）和从节点（Slave）的 server-id 在局域网内必须唯一。

主从配置

主从服务器信息

主服务器

  • IP 192.168.0.15
  • 数据库 syncdb

从服务器

  • IP 192.168.0.16
  • 数据库 syncdb

主节点-配置

  1. 安装数据库；
  2. 修改数据库配置文件，指定 server-id，开启二进制日志(log-bin)；
  3. 启动数据库，查看当前是哪个日志，position 号是多少；
  4. 登录数据库，授权数据复制用户(IP 地址为从机 IP 地址)；
  5. 备份数据库(记得加锁和解锁)；
  6. 传送备份数据到 Slave；
  7. 启动数据库。

步骤

修改配置

1
vi /etc/my.cnf

增加以下配置项：

1
2
3
4
5
6
7
8
9
10
[mysqld]
server-id = 101           ## server-id，设置为 IP 地址的最后一段
binlog-ignore-db = mysql
binlog-ignore-db = mycat
log-bin = mysql01-bin     ## 开启二进制日志功能
binlog_cache_size = 1M    ## 为每个 session 分配的内存，用来存储二进制日志的缓存
binlog_format = mixed     ## 主从复制的格式（mixed,statement,row，默认格式是 statement）
expire_logs_days = 7      ## 二进制日志自动删除/过期的天数。默认值为 0，表示不自动删除。
slave_skip_errors = 1062  ## 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
                          ## 如：1062 错误是指一些主键重复，1032 错误是因为主从数据库数据不一致

说明：二进制日志（binlog）的复制类型

  1. 基于语句的复制（statement）：在 Master 上执行的 SQL 语句，在 Slave 上执行同样的语句。
    MySQL 默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选择基于行的复制。
  2. 基于行的复制（row）：把改变的内容复制到 Slave，而不是把命令在 Slave 上执行一遍。从mySQL5.0 开始支持。
  3. 混合类型的复制（mixed）：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

查看

1
grep "binlog-ignore-db" /etc/my.cnf

重启MySQL

1
service mysqld restart

创建同步用户

1
2
3
4
5
set global validate_password_policy=0;
set global validate_password_policy=0;

GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%' IDENTIFIED BY 'psvmc123456';
FLUSH PRIVILEGES;

锁表

1
flush tables with read lock;

查看状态

1
show master status\G

结果如图

image-20221212201047937

记录下里面的File和Position的值，从节点同步时要用。

注意点

注意点1

主从节点的server-id要不一样。

注意点2

注意以下两个配置是互斥的，不能同时配置

1
2
binlog-do-db = syncdb       ## 复制过滤：需要备份的数据库，输出 binlog
binlog-ignore-db = mysql  ## 复制过滤：不需要备份的数据库，不输出（mysql 库一般不同步）

如果同时配置的话

如上示例，我这测试的效果是除了mysql库，其它的库都产生binlog了，也就是说binlog-ignore-db生效了，而binlog-do-db没生效。

注意点3

如果要设置同步多个库这么写，不能用逗号分割

1
2
binlog-do-db = syncdb1
binlog-do-db = syncdb2

主节点-数据备份

备份 syncdb 数据库中的数据

1
mysqldump -uroot -p --add-drop-table syncdb > /tmp/syncdb-bak.sql

将主节点的 syncdb 备份传送到从节点

1
scp -P 2222 /tmp/syncdb-bak.sql root@192.168.0.16:/tmp/

从节点-数据导入

1
2
3
4
5
create database syncdb;
use syncdb;
source /tmp/syncdb-bak.sql
-- 或者退出后执行
mysql -uroot -p syncdb < /tmp/syncdb-bak.sql

从节点-配置

（1）安装数据库；
（2）修改数据库配置文件，指明 server-id；
（3）启动数据库，还原备份；
（4）指定 Master 的地址、用户、密码等信息；
（5）开启同步，查看状态。

修改配置

1
vi /etc/my.cnf

增加以下配置项：

1
2
3
4
5
server-id = 102                 ## server-id，一般设置为 IP 的最后一段
read_only = 1                   ## 防止改变数据(除了特殊的线程)
#relay_log 配置中继日志
#relay_log = mysql02-relay-bin   
replicate-wild-do-table = syncdb.%

说明：

  • 如果 Slave 为其它 Slave 的 Master 时，必须设置 bin_log；

  • relay_log 配置中继日志；

  • log_slave_updates 表示 slave 将复制事件写进自己的二进制日志。当设置 log_slave_updates 时，你可以让 slave 扮演其它 slave 的 master。

    此时，slave 把 SQL 线程执行的事件写进行自己的二进制日志（binary log），然后，它的 slave 可以获取这些事件并执行它。

重启MySQL

1
service mysqld restart

从节点也可以有多级

20221203175119

先停止之前的

1
stop slave;

设置主从复制

1
change master to master_host = '192.168.0.15',master_user = 'sync',master_password = 'psvmc123456',master_port = 13306,master_log_file = 'mysql01-bin.000001', master_log_pos = 720, master_connect_retry = 30;

参数说明：

1
2
3
4
5
6
7
8
9
master_host = '192.168.1.15'          ## Master 的 IP 地址
master_user = 'sync'                   ## 用于同步数据的用户（在 Master 中授权的用户）
master_password = 'psvmc123456'             ## 同步数据用户的密码
master_port = 3306                     ## Master 数据库服务的端口
master_log_file = 'mysql01-bin.000002' ## 指定 Slave 从哪个日志文件开始读复制数据（可在 Master 上
                                       ## 使用 show master status 查看到日志文件名）
master_log_pos = 598                  ## 从哪个 POSITION 号开始读
master_connect_retry = 30              ## 当重新建立主从连接时，如果连接建立失败，间隔多久后重试。
                                       ## 单位为秒，默认设置为 60 秒。

查看主从同步状态：

1
show slave status\G

开启主从复制

1
start slave;

查看主从同步状态：

1
show slave status\G

主要看以下两个参数，这两个参数如果是 Yes 就表示主从同步正常：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主节点-解锁表

开启同步后解锁主节点数据库表

1
unlock tables;

重置主从复制

如果遇到同步出错，可在 Slave 上重置主从复制设置，步骤如下：

重置主从复制设置

1
2
stop slave;
reset slave;

重新设置主从复制参数

1
change master to master_host = '192.168.0.15',master_user = 'sync',master_password = 'psvmc123456',master_port = 13306,master_log_file = 'mysql01-bin.000001', master_log_pos = 720, master_connect_retry = 30;

启动

1
start slave;

查看主从同步状态

1
show slave status\G

主从复制参数

主

  • binlog-do-db 主生成binlog的数据库

  • binlog-ignore-db 主生成binlog忽略的数据库

从

  • replicate-do-db 从复制的数据库

  • replicate-ignore-db 从复制忽略的数据库

  • replicate-wild-do-table 从复制的表
  • replicate-wild-ignore-table 从复制忽略的表

误区

这里说一个误区

binlog-do-db 、binlog-ignore-db、replicate-do-db、replicate-ignore-db并不像你想的那样工作.

想象如下的场景:

你设置了 binlog-ignore-db = logdb,

所以我们以为logdb数据库(在slave上不存在这个数据库) 中的数据不会被复制

但是当我们在主库中执行以下的操作

1
2
3
mysql> delete from logdb.tuser;
mysql> use logdb;
mysql> update syncdb.users set disabled = 1 where user = "root";

复制会broke2次,

第一次，主库会依旧会产生binlog，所以slave尝试着去执行你给第一条语句，但是slave上并没有这样的表logdb.tuser

第二次，因为use logdb;所以syncdb也不会产生binlog，所以对syncdb.users不会被复制

为什么?

因为 binlog-ignore-db 并不像你想的那样执行, 我们认为的： 在logdb数据库中的数据不会被复制 是错的,

实际上(数据库)并没有这么做.事实上, 他是通过默认的数据库为logdb的连接, 过滤二进制的(SQL)语句日志的.

换句话说：

过滤不是基于 查询的字符串的, 而实际于你used的数据库。

其他的配置选项也都类似.

安全的替换方案是 在 slave上配置过滤, 使用基于查询中真正涉及到的表的选项,

这些是: 

1
2
replicate-wild-do-table
replicate-wild-ignore-table

例如, 避免复制 logdb数据库中的数据的安全的方案是 配置: 

1
replicate-wild-ignore-table=logdb.%

这样做仍然有一些特殊的情况, 不能正常工作,但可以在更多的情况下正常工作,并且会遇到更少的意外.

推荐配置

主库配置:

1
2
3
binlog-ignore-db = mysql
binlog-ignore-db = mycat
binlog-ignore-db = logdb

从库配置:

1
replicate-wild-do-table = syncdb.%