CentOS 7 + Mysql 5.7 实现主从复制

CentOS 7 + Mysql 5.7 实现主从复制

1.准备工作

Master-Server : 192.168.43.101
Slave-Server : 192.168.43.102

安装 MySQL

开放防火墙3306端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

2.Master-Server 配置

配置 Master 以使用基于二进制日志文件位置的复制，必须启用二进制日志记录并建立唯一的服务器ID,否则则无法进行主从复制。

停止MySQL服务

systemctl stop mysqld

开启binlog ，每台设置不同的 server-id

#修改配置文件
vim /etc/my.cnf
#添加修改内容
log-bin=mysql-bin
server-id=101

重新启动MySQL服务

systemctl start mysqld

登陆MySQL

mysql -u root -p

创建用户

每个从库使用MySQL用户名和密码连接到主库，因此主库上必须有用户帐户，从库可以连接。任何帐户都可以用于此操作，只要它已被授予 REPLICATION SLAVE权限。可以选择为每个从库创建不同的帐户，或者每个从库使用相同帐户连接到主库

虽然不必专门为复制创建帐户，但应注意，复制用到的用户名和密码会以纯文本格式存储在主信息存储库文件或表中 。因此，需要创建一个单独的帐户，该帐户只具有复制过程的权限，以尽可能减少对其他帐户的危害

CREATE USER '用户名'@'IP地址' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO '用户名'@'IP地址';

CREATE USER 'repl'@'192.168.43.102' IDENTIFIED BY 'Root!!2019';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.43.102';

查看 Master-Server ， binlog File 文件名称和 Position值位置 并且记下来

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1119 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3.Slave-Server 配置

停止MySQL服务

systemctl stop mysqld

修改配置文件

vim /etc/my.cnf

[mysqld]
server-id=102

如果要设置多个从库，则每个从库的server-id与主库和其他从库设置不同的唯一值。

启动MySQL服务

systemctl start mysqld

登陆MySQL

mysql -u root -p

使用主库授权的账号

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.43.101',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='Root!!2019',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=1119;

启动从服务器复制线程

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

查看复制状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.43.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1517
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 718
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

检查主从复制通信状态

Slave_IO_State #从站的当前状态 
Slave_IO_Running：Yes #读取主程序二进制日志的I/O线程是否正在运行 
Slave_SQL_Running：Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样 
Seconds_Behind_Master #是否为0，0就是已经同步了 

必须都是 Yes

如果不是原因主要有以下 4 个方面：

1、网络不通
2、密码不对
3、MASTER_LOG_POS 不对 ps
4、mysql 的 auto.cnf server-uuid 一样（可能你是复制的mysql）

$ find / -name 'auto.cnf'
$ vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc # 按照这个16进制格式，修改server-uuid，重启mysql即可

4.测试主从复制

在 Master-Server 创建测试库

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

mysql> use test;
Database changed
mysql> create table test (
    -> id int(11) not null auto_increment,
    -> name varchar(255) not null,
    -> primary key(id));

在 Slave-Server 查看是否同步过来

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

mysql> use test;
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

5.常用命令

查看主服务器的运行状态

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

查看从服务器主机列表

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       102 |      | 3306 |       101 | 6b831bf3-8ae7-11e7-a178-000c29cb5cbc |
+-----------+------+------+-----------+--------------------------------------+

获取binlog文件列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1517 |
+------------------+-----------+

查看指定binlog文件的内容

show binlog events in 'mysql-bin.000001';

启动从库复制线程

START SLAVE;

停止从库复制线程

STOP SLAVE;

6.复制实现细节分析

MySQL主从复制功能使用三个线程实现，一个在主服务器上，两个在从服务器上

1.Binlog转储线程。

当从服务器与主服务器连接时，主服务器会创建一个线程将二进制日志内容发送到从服务器。
该线程可以使用 语句 SHOW PROCESSLIST(下面有示例介绍) 在服务器 sql 控制台输出中标识为Binlog Dump线程。

二进制日志转储线程获取服务器上二进制日志上的锁，用于读取要发送到从服务器的每个事件。一旦事件被读取，即使在将事件发送到从服务器之前，锁会被释放。

2.从服务器I/O线程。

当在从服务器sql 控制台发出 START SLAVE语句时，从服务器将创建一个I/O线程，该线程连接到主服务器，并要求它发送记录在主服务器上的二进制更新日志。

从机I/O线程读取主服务器Binlog Dump线程发送的更新 （参考上面 Binlog转储线程 介绍），并将它们复制到自己的本地文件二进制日志中。

该线程的状态显示详情 Slave_IO_running 在输出端 使用 命令SHOW SLAVE STATUS

使用\G语句终结符,而不是分号,是为了，易读的垂直布局

这个命令在上面 查看从服务器状态 用到过

mysql> SHOW SLAVE STATUS\G

3.从服务器SQL线程。

从服务器创建一条SQL线程来读取由主服务器I/O线程写入的二级制日志，并执行其中包含的事件。

在前面的描述中，每个主/从连接有三个线程。主服务器为每个当前连接的从服务器创建一个二进制日志转储线程，每个从服务器都有自己的I/O和SQL线程。
从服务器使用两个线程将读取更新与主服务器更新事件，并将其执行为独立任务。因此，如果语句执行缓慢，则读取语句的任务不会减慢。

例如，如果从服务器开始几分钟没有运行，或者即使SQL线程远远落后，它的I/O线程也可以从主服务器建立连接时，快速获取所有二进制日志内容。

如果从服务器在SQL线程执行所有获取的语句之前停止，则I/O线程至少获取已经读取到的内容，以便将语句的安全副本存储在自己的二级制日志文件中，准备下次执行主从服务器建立连接，继续同步。

使用命令 SHOW PROCESSLIST\G 可以查看有关复制的信息

命令 SHOW FULL PROCESSLISTG

在 Master 主服务器 执行的数据示例

mysql>  SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 22
   User: repl
   Host: node2:39114
     db: NULL
Command: Binlog Dump
   Time: 4435
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

Id: 22是Binlog Dump服务连接的从站的复制线程
Host: node2:39114 是从服务，主机名 级及端口
State: 信息表示所有更新都已同步发送到从服务器，并且主服务器正在等待更多更新发生。
如果Binlog Dump在主服务器上看不到 线程，意味着主从复制没有配置成功; 也就是说，没有从服务器连接主服务器。

命令 SHOW PROCESSLISTG

在 Slave 从服务器 ，查看两个线程的更新状态

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 6
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 6810
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 7
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 3069
  State: Slave has read all relay log; waiting for more updates
   Info: NULL

Id: 6是与主服务器通信的I/O线程
Id: 7是正在处理存储在中继日志中的更新的SQL线程

在 运行 SHOW PROCESSLIST 命令时，两个线程都空闲，等待进一步更新

如果在主服务器上在设置的超时，时间内 Binlog Dump线程没有活动，则主服务器会和从服务器断开连接。超时取决于的 服务器系统变量 值 net_write_timeout(在中止写入之前等待块写入连接的秒数，默认10秒)和 net_retry_count;(如果通信端口上的读取或写入中断，请在重试次数，默认10次) 设置 服务器系统变量

该SHOW SLAVE STATUS语句提供了有关从服务器上复制处理的附加信息。请参见 第16.1.7.1节“检查复制状态”。