mysql部署一主两从

安装好mysql以后，需要搭建一下主从，这里以192.168.2.31-33这三台机器为例，介绍一下mysql主从的搭建（mysql安装可以参考我之前的文档）
我这里由于是测试环境 ，mysql的密码就设置为123456（线上环境不建议）

一、部署环境

mysql版本：5.7.27
系统：centos7
主从架构：一主两从
192.168.2.31主库
192.168.2.32从库
192.168.2.33从库
CPU：1核
内存：1G

首要前提：三台机器的server-id 一定得设置成不同数值
192.168.2.31:
server-id = 3133
192.168.2.32:
server-id = 3233
192.168.2.33:
server-id = 3333
修改完server-id以后记得重启一下mysql

二、部署操作

主库：192.168.2.31上需要执行

grant replication slave on . to ‘slave’@‘%’ identified by ‘123456’;
用于授予slave用户在所有数据库上进行复制操作的权限，并使用密码123456进行身份验证。
（5.7.27版本默认grant语句有创建用户的权限）

select User,Host from mysql.user;
查看mysql里面有多少个用户，都有哪些网段的权限

flush privileges;
刷新权限

reset master;
这条命令将删除主服务器上的所有二进制日志文件，并将二进制日志文件索引重置为1。

show master status;
获取主服务器的状态信息

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select User,Host from mysql.user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| slave         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

这里就可以看到slave用户已将创建成功了
并且重置了master的信息
这里可以看到二进制日志文件名跟节点
这个要记录下来，后面要用到

两台从库192.168.2.32和192.168.2.33上执行

stop slave;
CHANGE MASTER TO MASTER_HOST=‘192.168.2.31’,MASTER_USER=‘slave’,MASTER_PASSWORD=‘123456’,MASTER_PORT=3308,MASTER_LOG_FILE=‘mysql-bin.000001’,MASTER_LOG_POS=154;
主服务器主机名：192.168.2.31
主服务器用户名：slave
主服务器密码：123456
主服务器端口：3308
主服务器二进制日志文件名：mysql-bin.000001
主服务器二进制日志位置：154
start slave;

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.31',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_PORT=3308,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

然后再从库执行show slave status\G;看到以下两条yes表示主从构建成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.31
                  Master_User: slave
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                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: 531
              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: 33
                  Master_UUID: 4343ea4b-24b9-11ee-8eb2-000c29c3442e
             Master_Info_File: /opt/mysqldata/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)

报错处理思路

如果有一条不是yes就需要去排错，有可能是server-id相同，也有可能是上面的3308端口没开放
可以根据以下几行看到报错的内容是什么，目前是没有报错的情况，
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error

如果有IO报错的话，就会显示下面这样
Slave_IO_Running: No
Slave_SQL_Running: Yes

Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
这样，就可以根据报错去处理问题，我上面这个报错是因为三台机器server-id一样导致的，修改server-id再重启，然后重新指定主从就可以