MySQL 主从复制搭建和为搭建好的主从复制增加备库

摘要:MySQL 主从复制搭建和为搭建好的主从复制增加备库,搭建只是步骤,理解主从复制原理和应对主从复制所带来的问题更为重要。

相关文章

MySQL 复制(主从复制)相关概述和复制原理剖析

MySQL 主从复制搭建和为搭建好的主从复制增加备库(本文)

MySQL 双主复制搭建和双主热备搭建

MySQL 主从复制延迟和主从复制不一致


环境准备

两台 centos linux release 7.2.1511 (core) 服务器,mysql 版本 5.7.26,我的 mysql 安装方式都是二进制安装。以下搭建的主从复制模式是一主一从,搭建只是步骤,理解主从复制原理和应对主从复制所带来的问题更总要。


Master 主库配置

1、配置 my.cnf 文件。

开启 binlog 日志、指定一个独一无二的 server_id、保存退出重启 mysql 服务。

[root@localhost ~]# vim /etc/my.cnf
...
[mysqld]
log_bin=mysql-bin
server_id=1 
binlog_format=mixed
...

[root@localhost ~]# systemctl restart mysqld

- log_bin = mysql-bin 开启 binlog 日志

- server_id = 1 设置主库 server id

- binlog_format = mixed 此处的复制模式设置成混合模式即可

如果没有对 binlog 日志指定路径,binlog 默认生成在 /usr/local/mysql/var/ 路径下(安装方式不同路径不同),以 mysql_bin.00000* 作为名称,每次重启都会生成一个新的 binlog 日志文件。server_id 是 mysql 的唯一标识,如果等于 0,则主机拒绝任何从服务器连接。

此时使用 show variables like 'log_bin'; 再次查看 binlog 日志状态,就是开启状态了。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

在 /usr/local/mysql/var/ 路径下 可以看到 binlog 日志已经生成,如果想查看 binlog 日志内容,可以使用 mysql 自带的 mysqlbinlog 工具。

[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ll
-rw-r-----. 1 mysql mysql      154 8月  17 03:29 mysql-bin.000001
-rw-r-----. 1 mysql mysql       19 8月  17 03:29 mysql-bin.index
```
其中 mysql-bin.index 文件记录了所有的 binlog 日志的名称。
```
[root@localhost mysql]# cat mysql-bin.index 
./mysql-bin.000001


2、创建复制账号

此处的复制账号要限制在本地网络,因为这是一个特权账号(尽管该账号无法执行 select 或修改数据,但仍然能够从二进制日志中获取一些数据)

mysql> grant replication slave, replication client on *.* to 'slave'@'192.168.136.%' identified by 'Slave123_';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


3、查看主库状态

其中 File:mysql-bin.000012 就是正在写的二进制文件名,Position: 154 正在写的位置,待会从数据库就要从 Position 位置进行复制。

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000012
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)


Slave 从库配置

1、配置 my.cnf 文件

修改 server_id、保存退出重启 mysql 服务。

[root@localhost ~]# vim /etc/my.cnf
...
[mysqld]
log_bin=mysql-bin
server_id=1 
binlog_format=mixed
relay_log=/usr/local/mysql/var/
log_slave_updates=1
read_only=1
...

[root@localhost ~]# systemctl restart mysqld

- log_bin = mysql-bin 开启 binlog 日志。

- server_id = 1 设置备库 server id。

- binlog_format = mixed 此处的复制模式设置成混合模式即可。

- relay_log=/usr/local/mysql/var/ 指定中继日志的位置和命名。

- log_slave_updates=1 允许备库将其重放的事件也记录到自身的二进制日志中,这个选项会给备库增加额外的工作。

- read_only=1 设置备库为只读库。

因为 server_id 要和主库区分开,所以将备库的 server_id 设置为 2。备库的 binlog 日志其实设置不设置意义不大,我们在做主从复制后肯定要做读写分离,备库不需要有写操作,只负责读操作。所以程序中配置数据库连接的时候一定要配置正确,写操作连接主库,读操作连接读库。

查看从库状态和从库线程,发现没有显示从库的状态也没有同步复制线程。

mysql> show slave status \G
Empty set (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.02 sec)


2、配置同步信息

配置备库如何连接到主库并重放二进制日志。我们直接使用 change master to 语句就可以,并且允许以后指向别的主库时无需重启备库。

mysql> change master to 
	master_host='192.168.136.129', 
	master_user='slave',
	master_password='Slave123_',
	master_log_file='mysql-bin.000012',
	master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

- master_host 主库服务器 ip

- master_user 复制账号

- master_password 复制密码

- master_port 主库服务器 mysql 端口

- master_log_file 主库正在写的 binlog 文件名,因为 mysql 重启一次会生成一个 binlog 日志,所以我们要确定 binlog 日志名称

- master_log_pos 正在写的二进制文件位置

MASTER_LOG_POS 参数设置为 0,因为要从日志的开头读起。使用 show slave status; 命令查看从库状态。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.136.129
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 4
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           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: 4
              Relay_Log_Space: 154
              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: NULL
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: 0
                  Master_UUID:
             Master_Info_File: /usr/local/mysql/var/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           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:
1 row in set (0.00 sec)

此时发现 Slave_IO_State、Slave_IO_Running、Slave_SQL_Running 这三列显示当前备库复制尚未运行。还有就是我们刚才配置的日志开头是 0 而不是 4,这是因为 0 其实不是日志真正开始的位置,它仅仅意味着“在日志文件头”,mysql 知道第一个事件从文件的第 4 位开始读。


4、启动备库同步复制线程

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

此处扩展一下:start slave 开启复制、stop slave 停止复制、reset slave 重启复制。使用 show processlist; 查看备库线程,发现多了两个复制线程。

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  2 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
|  3 | system user |           | NULL | Connect |    5 | Waiting for master to send event                       | NULL             |
|  4 | system user |           | NULL | Connect |    5 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

然后进入 /var/lib/mysql 目录下,发现多了两个中继日志。

[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ll
-rw-r-----. 1 mysql mysql      154 8月  17 03:49 localhost-relay-bin.000001
-rw-r-----. 1 mysql mysql       29 8月  17 03:49 localhost-relay-bin.index

再次查看备库服务器状态,验证是否成功,从输出可以看出 I/O 线程和 SQL 线程都已经开始运行, Seconds_Behind_Master 的值也不再为 NULL。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.129
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           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: 154
              Relay_Log_Space: 578
              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: 3702630d-8c8f-11eb-9491-000c2974b699
             Master_Info_File: /usr/local/mysql/var/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:
1 row in set (0.00 sec)

I/O 线程正在等待从主库传递过来的事件,这意味着 I/O 线程已经读取了主库所有的事件。日志位置发生了变化,表明已经从主库获取和执行了一些事件。如果在主库上做一些数据更新,就会看到备库的文件或者日志位置可能会增加。备库中的数据同样会随之更新。


主从复制测试

主库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

备库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

现在主库和备库数据库都是一样的,此时我们在主库上新建一个数据库,看看备库会不会同步?


主库

mysql> create database test_mysql;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_mysql         |
+--------------------+
5 rows in set (0.00 sec)

备库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_mysql         |
+--------------------+
5 rows in set (0.00 sec)

测试正常,到此一个简单的主从复制就搭建完成了,如果要搭建一主多从复制结构,原理也是一样的。我们可以从线程列表中看到复制线程。在主库上可以看到由备库 I/O 线程向主库发起的连接。

mysql> show processlist;
+----+-------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User  | Host                  | db   | Command     | Time | State                                                         | Info             |
+----+-------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 16 | slave | 192.168.136.130:35330 | NULL | Binlog Dump |  534 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+-------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

同样,在备库也可以看到两个线程,一个是 I/O 线程,一个是 SQL 线程。

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  3 | system user |           | NULL | Connect |  868 | Waiting for master to send event                       | NULL             |
|  4 | system user |           | NULL | Connect |  547 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

这些简单的输出来自一台已经运行了一段时间的服务器,所以 I/O 线程在主库和备库上的 Time 列的值较大。SQL 线程在备库已经空闲了 574 秒。这意味着 574 秒内没有重放任何事件。

主从复制搭建好后,不要在读库上进行写操作,这样可能会造成复制失败,我们只操作主库就可以了,主库会把数据复制到备库中,开发过程中我们要做好读写分离业务,写要写到主库,读取就读备库。


遇到问题

在搭建主从复制过程中可能会遇到问题,一般出现的问题有以下几种:

1. 主备 server_id 一致。

2. 主备 server_id 不一致,但还是失败,可能在备库操作的时候用的是主库的 ip 地址。

3. uuid 一致,这种大致是克隆导致的。直接进入数据库通过 select uuid() 命令生成一个 uuid,然后找到 auto.cnf 文件修改即可。

4. 备库连接不上主库,查看下是不是防火墙的问题。


推荐配置

主库

sync_binlog=1

在复制配置中一个重要的配置选项是 sync_binlog=1,如果开启该选项,mysql 每次在提交事务前会将二进制日志同步到磁盘上,sync_binlog=n,使执行 n 次写入后,与硬盘同步。1 是最安全的,但是也是最慢的。保证在服务器崩溃时不丢失事件。如果禁止该选项,服务器会少做一些工作,但是二进制日志文件可能在服务器崩溃时损坏或丢失信息。所以在备库上,可以关闭掉此选项以避免不必要的开销。

innodb_flush_logs_at_trx_commit=ON
innodb_support_xa=ON 
innodb_safe_binlog=ON

- innodb_flush_logs_at_trx_commit=ON 在事务提交时,要将内存中跟事务相关的数据立即刷写到事务日志中去。

- innodb_support_xa 基于它来做两段式提交功能


备库

skip_slave_start=ON
sync_master_info=1
sync_relay_log=1
sync_relay_log_info=1


为正在运行的主库增加备库

刚才的配置均为刚刚安装好且都是默认的数据,两个数据库数据都是初始状态,也就是数据都是相同的,并且知道当前主库的二进制日志,这种只要按照步骤配置即可。但大多数情况下有一个已经运行了一段事件的主库,然后用一台新安装的备库与之同步,此时这台备库还没有数据。最简单的理解就是,比如线上是一主二从,我们要增加一台从库怎么办?这台从库是空的。

复制的前提是两个数据库数据一致,由于主库已经运行了一段时间,那么此时新增的备库还是一个空库,我们首先要将备库数据与主库数据保持一致后才可以开启复制。

下面是一些服务器克隆备库的方法:

1. 冷备份。最基本的方法是关闭主库,把数据复制到备库。重启主库后,会使用一个新的二进制文件,我们通过执行 change master to 指向这个文件的起始处。

2. 使用 flush tables with read lock 命令锁住主库,让主库只读。然后导出主库数据,同步到备库上,备库在启动复制线程的时候添加主库的 binlog 文件和复制位置即可。 

3. 使用 mysqldump 命令

$ mysqldump -u root -pPassWord -h 192.168.136.129 --all-databases --events -B --single-transaction --master-data=1 > /data/backup/$(date +%F).sql

选项 --single-transaction 使得转储的数据为事务开始前的数据。如果使用的时非事务型表,可是使用 --lock-all-tables 选项来获取所有表的一致性转储。


mysqldump 具体操作如下:

此操作其实很简单,就是在不停止主库的情况下将数据导出,然后将数据导入到备库中,并配置好备库的 my.cnf 配置,然后设置好master_log_file 文件和 master_log_pos 参数,最后开启同步线程即可。

1、一般这种数据库操作都是在半夜进行,首先导出主库数据库,此时的主库可以正常写入和插入数据。

$ mysqldump -u root -pPassWord -h 192.168.136.129 --all-databases --events -B --single-transaction --master-data=1 > /data/backup/$(date +%F).sql

2、然后配置备库得 my.cnf 文件。

3、将主库的数据导入到备库中。

4、在备库执行 change master to语句,从刚才导出的文件中即可获取 master_log_file 文件和 master_log_pos 位置。

mysql> change master to 
    -> master_host='192.168.136.129',
    -> master_user='slave',
    -> master_password='Slave123_',
    -> master_log_file='mysql-bin.000012',
    -> master_log_pos=1811;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

5、备库开启同步复制 start slave。

6、备库 show slave status\G,检查同步状态,只要 Seconds_Behind_Master 值为 0 表示复制追赶完毕,可以加入集群。


参考资料

书籍:《高性能 MySQL》第 10 章 复制 10.2 配置复制

mysql主从复制——从库扩展

MYSQL 主从添加新从库

结束语:感谢您对本网站文章的浏览,欢迎您的分享和转载,但转载请说明文章出处。
Top