Linux 笔记 - 第二十三章 MySQL 主从复制配置

一、前言

MySQL Replication，也被称为主从复制、AB 复制。简单来说就是 A 和 B 两台服务器做主从后，在 A 服务器上写入数据，B 服务器上也会跟着写入输入，两者之间的数据是实时同步的。主从复制技术主要用来提供实时备份或读写分离服务。

MySQL 主从是基于 binlog 的，主服务器必须开启 binlog 才能进行主从复制。主从复制过程大致有如下三个步骤。

1）主服务器将更改操作记录到 binlog 中。

2）从服务器将主的 binlog 事件（SQL 语句）同步到本机上并记录在 relaylog 中。

3）从服务器根据 relaylog 中的 SQL 语句按顺序执行。

主服务器上有一个 log dump 线程，用来和从服务器的 I/O 线程传递 binlog。从服务器上有两个线程，其中 I/O 线程用来同步主服务器的 binlog 并生成 relaylog，另外一个线程用来执行 relaylog 中的额 SQL 语句。

二、主从服务器准备

主数据库服务器 master：

host：masternode，IP：192.168.150.140，数据库：moonxy

从数据库服务器 slave：

host：backupnode，IP：192.168.150.139，数据库：moonxy

三、MySQL 安装和主从配置

3.1 安装 MySQL 服务

分别在主从服务器上安装 MySQL 数据库服务，如下：

[root@masternode ~]# cd /usr/local/src
[root@masternode src]# wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@masternode src]# tar zxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@masternode src]# ls -ltr
total 629756
-rw-r--r-- 1 root root 644869837 Apr 13 20:25 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x 9 root root       129 Oct  6 22:52 mysql-5.7.26-linux-glibc2.12-x86_64
[root@masternode src]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql
[root@masternode src]# useradd -s /sbin/nologin mysql
[root@masternode src]# mkdir -p /data/mysql
[root@masternode src]# chown mysql:mysql /data/mysql

MySQL 5.7 之前的版本使用如下 mysql_install_db 命令安装：

[root@masternode mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql

MySQL 5.7 和之后的版本不再推荐使用上面的方式，而使用如下 mysqld --initialize 命令安装：

[root@masternode mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql
2019-10-06T15:54:44.494874Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-10-06T15:54:45.070139Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-10-06T15:54:45.278962Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-10-06T15:54:45.362466Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9e141ebf-e851-11e9-bc47-000c2988299b.
2019-10-06T15:54:45.363436Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-10-06T15:54:45.364293Z 1 [Note] A temporary password is generated for root@localhost: iAepz0alaZ!R

可以看到最后会给 root 用户生成一个临时密码。

3.2 配置 MySQL 主服务器

修改 MySQL 的主配置文件：/etc/my.cnf，如下：

[root@masternode mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
port=3306
server_id=140
log_bin=master-log
socket=/tmp/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

注意设置 server_id=140、data_dir=/data/mysql 和 log_bin=master-log，这样 MySQL 重启重启后会在 /data/mysql 目录下会生成 master-log.000001（日志文件），master-log.index（索引文件）等。

创建 MySQL 用户 moon，该用户作为主从数据同步用户（即从 slave 端访问 master 端的用户），然后进行锁表操作，目的是不让数据继续读写，如下：

mysql> grant replication slave on *.* to 'moon'@'192.168.150.139' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

查看 master 主服务器的状态，前两个值在从服务器中设置 master_log_file 和 master_log_pos 时需要使用到，因此需要记住此值，如下：

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-log.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

上面添加锁之后，网站迁移或更换服务器时都需要进行数据备份，使用 mysqldump 命令既可以备份数据库，也可以备份表，此处备份需要进行主从复制的数据库 moonxy，如下：

[root@masternode log]# mysqldump -uroot -p123456 moonxy > /tmp/mysqlbak/moonxy.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

备份主服务器数据库之后，就可以拷贝到从服务器上面，然后导入。

3.3 配置 MySQL 从服务器

编辑 my.cnf 配置文件，设置 server_id 值和主服务器的值不相同，log_bin 无需设置，如下：

[root@backupnode ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
port=3306
server_id=139
socket=/tmp/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

导入数据库备份文件到从服务器，如下：

[root@backupnode log]# mysqldump -uroot -p123456 moonxy < /tmp/mysqlbak/moonxy.sql

在从服务器上设置主服务器的信息，此处需要使用上面提到的 master_log_file 和 master_log_pos，设置完毕之后，开启同步功能，如下：

mysql> change master to master_host='192.168.150.140', master_user='moon', master_password='123456', master_log_file='master-log.000001', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

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

查看 slave 状态，如下：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.150.140
                  Master_User: moon
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: backupnode-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-log.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

其中 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 表示同步成功。

主从设置成功之后，恢复 master 主服务器上面的写操作，如下：

mysql> unlock  tables;
Query OK, 0 rows affected (0.00 sec)

以上操作完成之后，就可以进行测试了。

3.4 MySQL 主从同步测试

同步数据功能支持同步指定的库和忽略指定的库，可以在主服务器的 my.cnf 中配置，例如只想同步 moonxy 库，忽略 test 库，如下：

[mysqld]
......
binlog-do-du=moonxy #同步指定库 moonxy
binlog-ignore-db=test #忽略指定库 test
......

也可以在从服务器中定义，如下：

[mysqld]
......
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table= #可以使用通配符匹配
replicate_wild_ignore_table= #可以使用通配符忽略匹配
......

下面我们进行主从同步测试，如下：

在 master 主服务器新建表结构并插入数据，如下：

INSERT INTO `user_test` (`id`,`name`,`phone`) VALUES
('10003','Lucy','333333'),
('10004','Lili','444444');

mysql> use moonxy;
Database changed

mysql> CREATE TABLE `user_test` (
    ->   `id` varchar(64) NOT NULL COMMENT '编号',
    ->   `name` varchar(100) default NULL COMMENT '姓名',
    ->   `phone` varchar(200) default NULL COMMENT '电话',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户测试表';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_test;
Empty set (0.00 sec)

mysql> INSERT INTO `user_test` (`id`,`name',`phone`) VALUES ('10001','ryan','111111'), ('10002','tom','222222');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user_test;
+-------+------+--------+
| id    | name | phone  |
+-------+------+--------+
| 10001 | ryan | 111111 |
| 10002 | tom  | 222222 |
+-------+------+--------+
2 rows in set (0.00 sec)

然后在 slave 从服务器上查看，如下：

mysql> use moonxy;
Database changed

mysql> select * from user_test;
+-------+------+--------+
| id    | name | phone  |
+-------+------+--------+
| 10001 | ryan | 111111 |
| 10002 | tom  | 222222 |
+-------+------+--------+
2 rows in set (0.00 sec)

可以看到数据同步成功了。

注意：

1）如果我们不小心在 slave 上写入数据，那么主从复制就被破坏了，因此，如果需要重启 master，请先关闭 slave，即在从服务器上执行 stop slave 命令（此时，Slave_IO_Running 和 Slave_SQL_Running 均会变成 No 值），然后再去重启 master 的 MySQL 服务，否则主从复制很有可能会中断。重启 master 后，再执行 start slave 命令开启主从复制的服务。（当然，通过以上的主从原理，我们也可以将两台服务器分别设置为互为主从，即每一台服务器都承担着主，也承担着另一台服务器的从）

2）在MySQL配置主从关系时，会用到 start slave，stop slave 命令方法和作用如下：

mysql> start slave;

不带任何参数，表示同时启动 I/O 线程和 SQL 线程。I/O线程从主库读取 bin log，并存储到 relay log 中继日志文件中。SQL 线程读取中继日志，解析后，在从库重放。

mysql> stop slave;

完成停止 I/O 线程和 SQL 线程的操作。

3）relay log 和 binary log 日志类似， 记录了数据库的变化，由一系列文件组成，relaoy log 记录了主节点发来的数据库变化信息，并且由 I/O thread 写入。之后 SQL  thread 在备节点上执行 relay log 文件里的事件。

master 主服务器 bin log 如下：

[root@masternode log]# ls -ltr /data/mysql/master-log*
-rw-r----- 1 mysql mysql 417622 Oct  8 00:15 /data/mysql/master-log.000001
-rw-r----- 1 mysql mysql    177 Oct  8 00:15 /data/mysql/master-log.000002
-rw-r----- 1 mysql mysql    892 Oct  9 23:15 /data/mysql/master-log.000003
-rw-r----- 1 mysql mysql     80 Oct 12 16:04 /data/mysql/master-log.index
-rw-r----- 1 mysql mysql    463 Oct 13 13:45 /data/mysql/master-log.000004

slave 从服务器 relay log 如下：

[root@backupnode ~]# ls -ltr /data/mysql/backupnode*
-rw-r----- 1 mysql mysql   5 Oct 12 16:04 /data/mysql/backupnode.pid
-rw-r----- 1 mysql mysql 379 Oct 12 16:04 /data/mysql/backupnode-relay-bin.000004
-rw-r----- 1 mysql mysql  60 Oct 12 16:04 /data/mysql/backupnode-relay-bin.index
-rw-r----- 1 mysql mysql 678 Oct 13 13:45 /data/mysql/backupnode-relay-bin.000005

4）主从同步必须关闭防火墙，否则会同步失败

通过以上操作步骤 MySQL 主从复制完成。