MySQL Replication 配置

  • 发布日期:
  • 修改日期: 2018 年 07 月 28 日
  • 分类: Linux
  • 阅读:(1217)

一、前言

MySQL Replication 又称“AB复制”或者“主从复制”，是为了应对高并发、大访问量的情况。

如果网站访问量和并发量太大了，少量的数据库服务器是处理不过来的，会造成网站访问慢。数据写入会造成数据表或记录被锁住，锁住的意思就是其他访问线程暂时不能读写要等写入完成才能继续，这样会影响其他用户读取速度。采用主从复制可以让一些服务器专门读，一些专门写可以解决这个问题。

简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。MySQL主从复制是基于binlog的，首先必须打开master端的binlog记录功能，否则就无法实现。

整个主从过程大致有3个步骤：

  • 主库在运行中会将SQL语句记录成一个二进制文件binlog，通过log dump线程传递给从库。
  • 从库通过I/O线程将主库的binlog事件同步到本地的relaylog中继日志文件。
  • 从库的SQL线程根据relaylog中的SQL语句按顺序执行操作到本地。

二、配置 Replication

分别在两台服务器上安装 MySQL，详情见 MySQL 二进制免编译安装 一文。

msyql一主一从和一主多从的配置大体上相似，不同的地方在于slave上my.cnf配置文件中的server_id要和master中的区别开来，不能和其它mysql数据库相同。

服务器：

mysql   5.6
master    192.168.237.6
slave    192.168.237.11

1）、配置 master

首先需要在master上配置server_id，以及开启log-bin的名字。
修改my.cnf配置文件，在[mysqld]部分增加如下配置：

[mysqld]
server_id = 128
log-bin=123
binlog-do-db=aaa
binlog-ignore-db=
  • binlog-do-db定义要复制的数据库，多个数据库用英文逗号分隔；
  • binlog-ignore-db定义不需要复制的数据库，二选一即可。

重启 mysql 数据库，然后进入MySQL数据库，在master上创建负责主从复制的用户：

grant replication slave on *.* to 'repl'@'192.168.237.11' identified by 'veXwS3A15WXCHDow';

锁定数据库写操作，如果不停止语句的执行，则最终将导致从库的数据与主库的不一致或损坏。

flush tables with read lock;

mysql不同版本中的语句可能不一样：

flush table with read lock;

再者，必须让执行这条语句的客户端保持运行，如果窗口关闭将会导致锁表失效。

获取master状态，将返回的信息记录下来，配置slave时需要：

show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| 123.000001 |      120 |     aaa      |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

如果先前没有启用二进制日志记录binlog，这里的返回将会为空。
然后将需要进行 MySQL 主从操作的数据库备份，上传到 slave 服务器上：

mysql -uroot -p7z7jD70qvrpxvAQP aaa > aaa.sql
rsync -av aaa.sql 192.168.237.11:/root/

2）、配置slave

修改my.cnf配置文件：

[mysqld]
server_id = 129
replicate-do-db=aaa
replicate-ignore-db=
  • 从上的server_id不能与 master 相同;
  • replicate-do-db参数和replicate-ignore-db与 master 上配置文件中的两个参数相似，一一对应。
  • 从库一般不开启binlog功能，除非从库做备份，或做级联主从。

然后重启mysql服务，创建同名数据库，执行数据库恢复操作：

mysql -uroot -pn3DTNUevaIFm5pBl -e "create database aaa"
mysql -uroot -pn3DTNUevaIFm5pBl aaa < aaa.sql

配置slave，将show master status得到的信息一一配置：

# 首先要停止处理来自主设备的二进制日志
stop slave;
# 配置
change master to master_host='master_host_ip',
master_user='repl',
master_password='veXwS3A15WXCHDow',
master_log_file='123.000001', master_log_pos=120;
# 最后开启slave
start slave;
  • master_host：表示master的IP地址
  • master_user：负责主从的数据库用户
  • master_log_file和master_log_pos：上文获取的master信息。

如果报错：

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

执行：

reset slave;
start slave;

在master上执行以下命令，打开数据库的写操作：

mysql -uroot -p7z7jD70qvrpxvAQP -e "unlock tables"

返回 slave，查看状态：

show slave status\G;

确认以下两项参数都为 Yes：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

需要关注以下信息，如果主从发生故障，可以从Errno得到提示：

Seconds_Behind_Master: 0
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
  • Seconds_Behind_Master：为主从延迟的时间

也可以在master上检查正在运行的进程列表来检查连接的从站的状态：

mysql> SHOW PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 6
   User: repl
   Host: 192.168.237.11:36716
     db: NULL
Command: Binlog Dump
   Time: 22165
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

以及有关从库的基本信息：

SHOW SLAVE HOSTS;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       129 |      | 3306 |         1 | 72126d2f-a5b6-11e7-90c6-000c296372f1 |
+-----------+------+------+-----------+--------------------------------------+

三、生产环境不锁表操作

mysql服务器的主从配置，这样不但可以实现读写分离，也可以在主库死机后从备用库中恢复，如果有多个从库，还能实现集群，但是一直以来网上的很多教程都需要重启主库，在生产环境中这是不允许的。

在做MySQL备份的时候加上--master-data=1这个参数，它会将MASTER_LOG_FILE和MASTER_LOG_POS信息记录到备份文件中：

mysqldump -uroot -pn3DTNUevaIFm5pBl --master-data=1 aaa > aaa.sql

可以在aaa.sql文件中找到这么一段：

-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='123.000001', MASTER_LOG_POS=120;

--

如果不加则不会记录。以及当--master-data=2时，这条语句虽然会记录，但却被注释掉。

在主库中检查状态中也是一样的信息：

mysql> show master status\G
*************************** 1. row ***************************
             File: 123.000001
         Position: 120
     Binlog_Do_DB: aaa
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

或者这样：

mysql> SHOW PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 4
   User: repl
   Host: 192.168.237.11:45952
     db: NULL
Command: Binlog Dump
   Time: 596
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

然后在从库中只需要：

stop slave;
change master to master_host='192.168.237.12',
master_user='repl',
master_password='veXwS3A15WXCHDow',
master_log_file='123.000001',
master_log_pos=120;
start slave;

这样就做好主从操作了。

标签: MySQL, 优化

添加新评论