搭建 MySQL 主从复制，以及复制实现细节分析

• 转载出处：http://www.ymq.io

概念

主从复制可以使 MySQL 数据库主服务器的主数据库，复制到一个或多个 MySQL 从服务器的从数据库，默认情况下为异步复制；根据配置，可以复制主数据库中的所有数据库，选定的数据库或甚至选定的表。

MySQL中主从复制的优点

横向扩展解决方案

在多个从库之间扩展负载以提高性能。在这种环境中，所有写入和更新在主库上进行。但是，读取可能发生在一个或多个从库上。该模型可以提高写入的性能（由于主库专用于更新），同时在多个从库上读取，可以大大提高读取速度。

数据安全性

由于主库数据被复制到从库，从库可以暂停复制过程，可以在从库上运行备份服务，而不会破坏对应的主库数据。

分析

可以在主库上创建实时数据，而信息分析可以在从库上进行，而不会影响主服务器的性能。

长距离数据分发

可以使用复制创建远程站点使用的数据的本地副本，而无需永久访问主库。

准备工作

Mysql版本：MySQL 5.7.19
Master-Server：172.16.1.51
Slave-Server：172.16.1.52

从库只会备份建立主从关系后产生的数据，之前的数据需要手动导入

主库导出数据

mysqldump -uroot -p密码 -S /var/lib/mysql/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=64M > /tmp/full.sql

推送数据去从库

scp -p /tmp/full.sql 172.16.1.52:/tmp/

从库导入数据

mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql;
mysql> set sql_log_bin=1;

关闭防火墙

systemctl stop firewalld.service
首先在两台机器上装上，保证正常启动，可以使用

Master-Server 配置

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

停止MySQL服务

systemctl stop mysqld

开启 binlog ，添加信息，每台设置不同的 server-id

vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1

启动MySQL服务

systemctl start mysqld

登录MySQL

mysql -uroot -p

创建用户

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

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

登录MySQL

mysql -uroot -p

创建用户及密码

mysql> CREATE USER ‘replication‘@‘172.16.1.52‘ IDENTIFIED BY ‘123456‘;

授权用户

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication‘@‘172.16.1.52‘;

Slave-Server 配置

停止MySQL服务

systemctl stop mysqld

修改 my.cnf

vim /etc/my.cnf

[mysqld]
server-id=2

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

启动MySQL服务

systemctl start mysqld

登录MySQL

mysql -uroot -p

配置主库通信

查看 Master-Serve 的 binlog File 文件名称和 Position值位置 并且记下来
mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

要设置从库与主库进行通信，进行复制，使用必要的连接信息来配置从库，在从库上执行以下语句将选项值替换为与系统相关的实际值

参数格式，请勿执行

mysql> CHANGE MASTER TO
    -> MASTER_HOST=‘master_host_name‘,
    -> MASTER_USER=‘replication_user_name‘,
    -> MASTER_PASSWORD=‘replication_password‘,
    -> MASTER_LOG_FILE=‘recorded_log_file_name‘,
    -> MASTER_LOG_POS=recorded_log_position;

实际执行

mysql> CHANGE MASTER TO
    -> MASTER_HOST=‘172.16.1.51‘,
    -> MASTER_USER=‘replication‘,
    -> MASTER_PASSWORD=‘123456‘,
    -> MASTER_LOG_FILE=‘mysql-bin.000002‘,
    -> MASTER_LOG_POS=154;

也可以放在一行执行
CHANGE MASTER TO MASTER_HOST=‘172.16.1.51‘, MASTER_USER=‘replication‘, MASTER_PASSWORD=‘123456‘, MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=154;

启动从库服务器复制线程
mysql> START SLAVE;

查看复制状态
mysql> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 154
......

检查主从复制通信状态

Slave_IO_State
从库服务器的当前状态

Slave_IO_Running：Yes
读取主程序二进制日志的 I/O 线程是否正在运行

Slave_SQL_Running：Yes
执行读取主服务器中二进制日志事件的SQL线程是否正在运行，与 I/O 线程一样

Seconds_Behind_Master
是否为0，0就是已经同步了

必须都是 Yes

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

网络不通

密码不对

MASTER_LOG_POS 不对

mysql 的 auto.cnf server-uuid 一样（可能你是复制的mysql）

检查server-uuid
vim /var/lib/mysql/auto.cnf

[auto]
server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc

按照这个16进制格式，修改server-uuid，重启mysql即可

测试主从复制

启动MySQL服务

systemctl start mysqld

登录MySQL

mysql -uroot -p

在 Master-Server 创建测试库

mysql> CREATE DATABASE `replication.io`;
mysql> use `replication.io`;
mysql> CREATE TABLE `sync_test` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

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

mysql> show databases;

+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| mysql                   |
| performance_schema      |
| replication.io          |
| sys                     |
+-------------------------+

mysql> use replication.io;
mysql> show tables;

+-----------------------------------+
| Tables_in_replication.io          |
+-----------------------------------+
| sync_test                         |
+-----------------------------------+

其他一些命令

在从服务器查看主服务器的运行状态

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1190 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

在主服务器查看从服务器主机列表

mysql> show slave hosts;

+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 6b831bf2-8ae7-11e7-a178-000c29cb5cbc |
+-----------+------+------+-----------+--------------------------------------+

获取binlog文件列表

mysql> show binary logs;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1190 |
+------------------+-----------+

只查看第一个binlog文件的内容

mysql> mysql> show binlog events;

+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                  |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.19-log， Binlog ver: 4                                                                                                                                                                 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                                                                                                                                                                                       |
| mysql-bin.000001 | 420 | Anonymous_Gtid |         1 |         485 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘                                                                                                                                                                  |
| mysql-bin.000001 | 485 | Query          |         1 |         629 | GRANT REPLICATION SLAVE ON *.* TO ‘replication‘@‘172.16.1.52‘                                                                                                                                         |
| mysql-bin.000001 | 629 | Anonymous_Gtid |         1 |         694 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘                                                                                                                                                                  |
| mysql-bin.000001 | 694 | Query          |         1 |         847 | CREATE DATABASE `replication.io`                                                                                                                                                                      |
| mysql-bin.000001 | 847 | Anonymous_Gtid |         1 |         912 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘                                                                                                                                                                  |
| mysql-bin.000001 | 912 | Query          |         1 |        1190 | use `replication.io`; CREATE TABLE `sync_test` (`id` int(11) NOT NULL AUTO_INCREMENT， `name` varchar(255) NOT NULL， PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8          |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

查看指定binlog文件的内容

mysql> mysql> show binlog events in ‘mysql-bin.000001‘;

+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                  |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.19-log， Binlog ver: 4                                                                                                                                                                 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                                                                                                                                                                                       |
| mysql-bin.000001 | 420 | Anonymous_Gtid |         1 |         485 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘                                                                                                                                                                  |
| mysql-bin.000001 | 485 | Query          |         1 |         629 | GRANT REPLICATION SLAVE ON *.* TO ‘replication‘@‘172.16.1.52‘                                                                                                                                         |
| mysql-bin.000001 | 629 | Anonymous_Gtid |         1 |         694 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘                                                                                                                                                                  |
| mysql-bin.000001 | 694 | Query          |         1 |         847 | CREATE DATABASE `replication.io`                                                                                                                                                                      |
| mysql-bin.000001 | 847 | Anonymous_Gtid |         1 |         912 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘                                                                                                                                                                  |
| mysql-bin.000001 | 912 | Query          |         1 |        1190 | use `replication.io`; CREATE TABLE `sync_test` (`id` int(11) NOT NULL AUTO_INCREMENT， `name` varchar(255) NOT NULL， PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8          |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

启动从库复制线程

mysql> START SLAVE;

停止从库复制线程

mysql> STOP SLAVE;

复制实现细节分析

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

Binlog转储线程

当从服务器与主服务器连接时，主服务器会创建一个线程将二进制日志内容发送到从服务器。

该线程可以使用语句 SHOW PROCESSLIST(下面有示例介绍) 在服务器 sql 控制台输出中标识为Binlog Dump线程。

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

从服务器 I/O 线程。

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

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

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

使用\G语句终结符而不是分号，是为了易读的垂直布局，这个命令在上面查看从服务器状态用到过。
mysql> SHOW SLAVE STATUS\G

从服务器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次)

搭建 MySQL 主从复制，以及复制实现细节分析

原文：https://www.cnblogs.com/magicsimba/p/12180864.html