MySQL基于binlog实现主从复制，实现一主两从

在移动互联网开发中，为了减少数据库单机的压力、增加数据库的并发访问能力，同时大多数移动互联网项目均是读多写少，鉴于此，将数据库配置一主多从显得尤为重要，下面我们将基于MySQL实现一主多从的配置。

准备工作

虚拟机准备

准备装有CentOS 7的虚拟机两台，IP为192.168.0.10 与 192.168.0.11

IP可以根据自己的环境进行设置

MySQL安装与启动

wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm 下载MySQL的repo源

rpm -ivh mysql57-community-release-el7-8.noarch.rpm 验证repo源

yum install mysql-server 安装MySQL

systemctl start mysqld 启动MySQL

登录到MySQL

由于MySQL 5.7 版本之后对于root账号有一个随机密码，可以通过 grep "password" /var/log/mysqld.log获得随机密码

mysql -u root -p 回车

粘贴随机密码

更改密码

由于root默认的随机密码不具有操作权限，所以需要更改root的密码之后才能对数据库进行操作

set password for root@localhost=password(‘1qaz2wsx’); 设置密码

如果需要设置简单一点的密码，可以选择降低安全策略

set global validate_password_length=1

set global validate_password_policy=0

开放IP访问

GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;

FLUSH PRIVILEGES;

使用MySQL中二进制日志实现主从同步的原理分析

MySQL实现主从同步的原理是 MySQL使用了3个线程来进行数据复制功能，主服务器一个，从服务器上两个。当从服务器发出start slave时，从服务器创建一个I/O线程，连接主服务器并让主服务器发送记录在binlog中的数据库语句。主线程创建一个线程将binlog中的内容发送给从服务器。该线程可以通过主服务器上使用命令 show processlist 输出查看，Binlog Dump线程即为此线程。从服务器I/O线程读取主服务器Binlog Dump线程发送过来的内容并将该数据拷贝到从服务器数据目录中的本地文件中。第三个线程是sql线程，由从服务器创建，用于读取从服务器目录中的本地文件包含的更新。在从服务器上，读取和执行更新语句被分成两个独立的任务。

主从配置

配置主服务器

创建repl用户

repl用户主要用于数据复制

创建repl用户并设置密码为repl

CREATE USER repl IDENTIFIED BY 'repl';

为用户repl用户赋予REPLICATION SLAVE 权限

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl' ;

修改my.cnf文件

进入到/etc目录，编辑my.cnf文件，在 [mysqld] 区段下增加如下配置

[mysqld]
# enable mysql bin
log-bin=mysql-bin
# server id
server-id=10

重启MySQL

systemctl restart mysqld

登录到MySQL，查看状态

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

配置从服务器

修改my.cnf文件

进入/etc目录，编辑my.cnf文件，在[mysqld]区段下增加如下配置

[mysqld]
# server id
server-id=11
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
read_only=1

重启MySQL

systemctl restart mysqld

登录到MySQL，建立同步连接

mysql> CHANGE MASTER TO master_host='192.168.0.10',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

启动slave

START SLAVE;

查看状态

SHOW SLAVE STATUS\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000016
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 10
                  Master_UUID: 217af41a-eeeb-11e7-8ed4-000c295dbc95
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

\G 表示格式化输出

Slave_IO_Running 与 Slave_SQL_Running 均为 YES 表示主从配置成功

另外的主机按照主机11从机配置即可

验证

在主机192.168.0.10上创建数据库，并创建user表验证主从配置状态

CREATE DATABASE test;
CREATE TABLE t_user( id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));

在主机192.168.0.11上登录到MySQL查看数据库时候存在

SHOW DATABASES;
USE test;
SHOW TABLES;

OK, 至此，关于MySQL的主从配置已经实现，如果阅读中发现有什么问题，欢迎指正！