MySQL Replication主从复制搭建

MySQL安装

环境版本：

Linux:CentOS6.5

MySQL:5.7.19

IP:192.168.48.33（Master）、192.168.48.34（Slave）

安装步骤：

1.根据Linux版本下载RPM

wget dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

2.安装RPM

 yum install mysql-community-release-el6-5.noarch.rpm

安装成功后，我们可以看到/etc/yum.repos.d/目录下增加了以下两个文件

# ls /etc/yum.repos.d
mysql-community-source.repo
mysql-community.repo

3.查看MySQL可用安装源

# yum repolist enabled | grep mysql

如果没看到mysql57，可修改配置文件（/etc/yum.repos.d/mysql-community.repo）使mysql57下面的enable=1

4.使用yum安装MySQL

yum install mysql-community-server

5.启动MySQL服务

shell> service mysqld start
Initializing MySQL database:                               [  OK  ]
Starting mysqld:                                           [  OK  ]

6.查看初始密码

初始化MySQL会生成一个临时密码，需要马上修改

shell> sudo grep 'temporary password' /var/log/mysqld.log
2017-09-07T12:32:45.775006Z 1 [Note] A temporary password is generated for root@localhost: !q0Tp+44OPXw

7.修改密码

MySQL默认开启了validate_password插件，要求密码至少一个大写字母一个小写字母一个数字和一个特殊符号，长度至少8字符

shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass4!';

8.关闭IP访问限制

如果需要从其他主机访问，比如Windows客户端连接，需要关闭IP访问限制：

grant all on 数据库名.* to 用户名@‘客户端IP地址’ identified by ‘密码’;

grant all on *.* to root@'%'identified by'password';

部分步骤示例：

Replication主从复制配置

在配置MySQL主从复制之前，最好先对MySQL主从复制的原理过程有个认识，这样配置起来会轻松的多，可以看看笔者的上一篇博客： MySQL Replication主从复制原理及拓扑结构

1.配置主库

修改/etc/my.cnf配置文件，启用二进制日志并创建唯一服务ID

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

2.重启服务

shell> service mysqld start

3.主库创建用户

备库连接到主库需要账号密码，所以需要在主库上创建用户，并授予REPLICATION SLAVE权限。可以为每个备库创建一个账号也可以一起共用一个

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

4.确认二进制日志是否在主库上创建

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

这里显示的二进制日志文件名称和偏移量可能和你定义的会有点不一样

5.配置备库

在另一台Linux服务器上配置备库，并重启服务

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

这里还可以配置relay_log，指定中继日志的位置和命名，log_slave_updates，允许备库将其重放的事件也记录到自身的二进制日志中。

6.启动复制

语法：

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='192.168.48.33',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='Slave123!',
    ->     MASTER_LOG_FILE='mysql-bin.000001',
    ->     MASTER_LOG_POS=0;

CHANGE MASTER TO语法详情传送门

7.检查复制是否正确执行

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.48.33
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: test-sz-34-relay-bin.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
                            ...

可以看到Slave_IO_State、Slave_IO_Running、Slave_SQL_Running 三列显示当前备库复制尚未运行。
需要注意Read_Master_Log_Pos显示当前日志开头不是0而是4，这是因为0其实不是日志真正开始的位置，第一个事件从文件的第4位开始读。

8.启动slave线程，开始复制

mysql> start slave;

再次查看slave状态：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.48.33
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 595
               Relay_Log_File: test-sz-34-relay-bin.000002
                Relay_Log_Pos: 808
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ...

主库上查看线程，可以看到备库I/O线程向主库发起的连接

mysql> show processlist;
+----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host                | db   | Command     | Time | State                                                         | Info             |
+----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  4 | root | localhost           | NULL | Query       |    0 | starting                                                      | show processlist |
| 15 | repl | 192.168.48.34:45862 | NULL | Binlog Dump |  271 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+

备库上也可以看到一个I/O线程和SQL线程

mysql> show processlist;
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                                                  | Info             |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
|  4 | root        | localhost | NULL | Query   |     0 | starting                                               | show processlist |
|  6 | system user |           | NULL | Connect |   204 | Waiting for master to send event                       | NULL             |
|  7 | system user |           | NULL | Connect | 12551 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+

测试

1.建库建表演示：

2.插入数据演示

3.更新数据演示

4.删除数据演示

总结

本文介绍了MySQL一主一从拓扑结构的复制配置过程，总结起来就三步：

1.创建复制账号

2.配置主库和备库

3.通知备库连接到主库复制数据

这里介绍的都是主库备库刚刚安装好且都是默认一致的数据，并且知道当前主库的二进制日志，但是通常的场景往往是主库已经运行一段时间，然后安装一台新的备库与之同步，那么我们就得首先初始化备库，先将主库之前的数据拷贝过来，然后找到主库日志文件坐标（logfile coordinates，就是二进制日志和位置），这样一来就非常麻烦，好在MySQL5.6版本推出了新特性GTID（global transaction identifier），即全局事务标识，能替代基于日志文件坐标的主从复制，有兴趣的同学可以自行学习，推荐阅读：

1.https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html

2.https://yq.aliyun.com/articles/57731#

3.http://blog.youkuaiyun.com/leshami/article/details/50630691

参考

[1] Replication官网参考手册

[2]《高性能MySQL》