CentOS 7.2下MySQL主从复制配置

共计 5469 个字符，预计需要花费 14 分钟才能阅读完成。

MySQL 主从原理及过程

原理

MySQL 的 Replication 是一个异步的复制过程（mysql5.1.7 以上版本分为异步复制和半同步两种模式），从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysql instance(我们称之 Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程 (Sql 线程和 IO 线程) 在 Slave 端，另外一个线程 (IO 线程) 在 Master 端。

要实现 MySQL 的 Replication，首先必须打开 Master 端的 Binary Log(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是 Slave 从 Master 端获取该日志然后再在自己身上完全 顺序的执行日志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用“—log-bin”参数选项，或者在配置文件中的 mysqld 参数组 ([mysqld] 标识后的参数部分)增加“log-bin”参数项。

基本过程

1.Slave 上面的 IO 线程连接上 Master，并请求从指定日志文件的指定位置 (或者从最开始的日志) 之后的日志内容；

2.Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置；

3.Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的 Relay Log 文件 (mysql-relay-bin.xxxxxx) 的最末端，并将读取到的 Master 端的 bin-log 的文件名和位置记录到 master- info 文件中，以便在下一次读取的时候能够清楚的告诉 Master“我需要从某个 bin-log 的哪个位置开始往后的日志内容，请发给我”

4.Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。

Mysql 复制的几种模式

. 从 MySQL 5.1.12 开始，可以用以下三种模式来实现：

– 基于 SQL 语句的复制(statement-based replication, SBR)，

– 基于行的复制(row-based replication, RBR)，

– 混合模式复制(mixed-based replication, MBR)

相应地，binlog 的格式也有三种：STATEMENT，ROW，MIXED。MBR 模式中，SBR 模式是默认的。

设定主从复制模式：
log-bin=mysql-bin

#binlog_format=”STATEMENT”

#binlog_format=”ROW”

binlog_format=”MIXED”

也可以在运行时动态修改 binlog 的格式。例如
mysql> SET SESSION binlog_format = ‘STATEMENT’;

mysql> SET SESSION binlog_format = ‘ROW’;

mysql> SET SESSION binlog_format = ‘MIXED’;

mysql> SET GLOBAL binlog_format = ‘STATEMENT’;

mysql 主从复制配置

版本：mysql5.7 CentOS7.2

场景描述：
 主数据库服务器：192.168.206.100，MySQL 已经安装，并且无应用数据。
 从数据库服务器：192.168.206.200，MySQL 已经安装，并且无应用数据。

1 主服务器上进行的操作

启动 mysql 服务
service mysqld start

通过命令行登录管理 MySQL 服务器
mysql -uroot -p’new-password’

授权复制权限给从数据库服务器 192.168.206.200
mysql> GRANT REPLICATION SLAVE ON *.* to ‘rep1’@’192.168.206.200’ identified by‘password’;

查询主数据库状态

配置从服务器时会用到
mysql> show master status;
 +————————-+———-+————–+——————+——————-+
 | File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +————————-+———-+————–+——————+——————-+
 | mysql-master-bin.000001 | 154 | | | |
 +————————-+———-+————–+——————+——————-+

这里需要注意一点，若查询时返回的是
mysql> show slave status;
Empty set (0.01 sec)

这是因为没有开启 bin-log 造成的，需要去修改 /etc/my.cnf 文件
server-id =1
log-bin=mysql-master-bin

修改文件时还需要注意一点，mysql5.7 之后，开启 binlog 时还需要同时指定 server-id，否则会报错

2 配置从服务器

修改从服务器的配置文件 /opt/mysql/etc/my.cnf

将 server-id = 1 修改为 server-id = 2，并确保这个 ID 没有被别的 MySQL 服务所使用。

启动 mysql 服务
service mysqld start

登录管理 MySQL 服务器
mysql -uroot -p’new-password’

执行同步 SQL 语句
change master to
master_host=’192.168.206.100′,
master_user=’root’,
master_password=’Xu261220..’,
master_log_file=’mysql-master-bin.000001′,
master_log_pos=154;

正确执行后启动 Slave 同步进程
mysql> start slave;

注意，这里又有一个坑了
 即使启动 start slave 成功了，主从复制任然是失败的
1、错误消息
mysql> show slave staus;
 
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
these UUIDs must be different for replication to work.
 
2、查看主从的 server_id 变量
master_mysql> show variables like ‘server_id’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id | 33|
+—————+——-+
 
slave_mysql> show variables like ‘server_id’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id | 11|
+—————+——-+
— 从上面的情形可知，主从 mysql 已经使用了不同的 server_id
 
3、解决故障
### 查看 auto.cnf 文件
[root@dbsrv1 ~] cat /data/mysqldata/auto.cnf  ### 主上的 uuid
[auto]
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
 
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ### 从上的 uuid，果然出现了重复，原因是克隆了虚拟机，只改 server_id 不行
[auto]
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
 
[root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf  /data/mysqldata/auto.cnf.bk  ### 重命名该文件
[root@dbsrv2 ~]# service mysql restart  ### 重启 mysql
Shutting down MySQL.[OK]
Starting MySQL.[OK]
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf  ### 重启后自动生成新的 auto.cnf 文件，即新的 UUID
[auto]
server-uuid=6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9
 
 
### 再次查看 slave 的状态已经正常
[root@dbsrv1 ~]# mysql -uroot -pxxx -e “show slave status\G”|grep Running
Warning: Using a password on the command line interface can be insecure.
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
### 主库端查看自身的 uuid
master_mysql> show variables like ‘server_uuid’;
+—————+————————————–+
| Variable_name | Value|
+—————+————————————–+
| server_uuid  | 62ee10aa-b1f7-11e4-90ae-080027615026 |
+—————+————————————–+
1 row in set (0.00 sec)
### 主库端查看从库的 uuid
master_mysql> show slave hosts;
+———–+——+——+———–+————————————–+
| Server_id | Host | Port | Master_id | Slave_UUID  |
+———–+——+——+———–+————————————–+
|33 |  | 3306 |11 | 62ee10aa-b1f7-11e4-90ae-080027615030 |
|22 |  | 3306 |11 | 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 |
+———–+——+——+———–+————————————–+

其中 Slave_IO_Running 与 Slave_SQL_Running 的值都必须为 YES，才表明状态正常。

如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：
(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> FLUSH TABLES WITH READ LOCK;

(2)查看主数据库状态
mysql> show master status;

(3)记录下 FILE 及 Position 的值。
 将主服务器的数据文件（整个 /opt/mysql/data 目录）复制到从服务器，建议通过 tar 归档压缩后再传到从服务器解压。

(4)取消主数据库锁定
mysql> UNLOCK TABLES;

3 验证主从复制效果

在主服务器上创建数据库 first_db
mysql> create database first_db;
Query Ok, 1 row affected (0.01 sec)

在主服务器上创建表 first_tb
mysql> create table first_tb(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)

在主服务器上的表 first_tb 中插入记录
mysql> insert into first_tb values (001,“myself”);
Query Ok, 1 row affected (0.00 sec)

在从服务器上查看

mysql> show databases;

：