MySQL Replication设置(Master/Slave)实现主从复制

一、Master配置

1、my.cnf配置

# vim /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
log-bin=mysql-bin   //[必须]启用二进制日志
server-id=140       //[必须]服务器唯一ID，默认是1，一般取IP最后一段

2、重启mysql

sudo service mysql restart

3、在主服务器上建立帐户并授权slave

# mysql -u root -p sD9854fals87s34GB98s
mysql> GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'sD9854fals87s34GB98s';

一般不用root帐号，%表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.245.139，加强安全。

4、登录mysql，查询master的状态

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000014 |      318 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。
上面的Position需要记住用于下方使用

二、Slave配置

1、my.cnf配置

# vim /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
log-bin=mysql-bin   //[必须]启用二进制日志
server-id=139       //[必须]服务器唯一ID，默认是1，一般取IP最后一段

2、重启mysql

sudo service mysql restart

3、配置从服务器Slave：

master_log_pos是上面Position的值

mysql> change master to master_host='192.168.245.140',master_user='mysync',master_password='sD9854fals87s34GB98s',master_log_file='mysql-bin.000014',master_log_pos=318;
# 注意不要断开，"318"无单引号。
mysql>start slave;
# 启动从服务器复制功能

4、检查从服务器复制功能状态：

复制代码

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.245.140
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 5669
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 5482
        Relay_Master_Log_File: mysql-bin.000003
             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: 5669
              Relay_Log_Space: 5639
              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: 140
1 row in set (0.00 sec)

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态。

三、主从服务器测试

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据，观看从库是否也增加了相应的数据库、数据表、数据。

四、清除Slave的配置信息

有时候我们的有许多台Slave库,根据业务需要一台甚至几台不在使用需要下线了.这时候就不在需要从Master同步数据了.那么我们如何清理Slave:

reset slave all;

四、常见错误

可以从Last_IO_Error和Last_SQL_Error配置。

1、

service mysql status

如果出现如下错误需要配置skip-name-resolve参数,作用是不再进行反解析（ip不反解成域名），这样可以加快数据库的反应时间。：

Nov 16 19:26:28 iZ2zedt5e7ronlirnw6vl3Z mysqld[22185]: 181116 19:26:28 [Warning] IP address '180.76.0.0' could not be resolved: Name or service not known

修改配置文件添加如下代码并重启：

[mysqld] 
skip-name-resolve

2、在没有停止slave进程的情况下change master

mysql> change master to master_host='IP', master_user='USER', master_password='PASSWD', master_log_file='mysql-bin.000001',master_log_pos=106;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

首先需要执行STOP SLAVE停止

3、slave没有启动

执行start slave启动从服务器复制功能:

# show slave status\G
----------
Slave_IO_Running: No
Slave_SQL_Running: No

4、Got fatal error 1236 from master when reading data from binary log

参考：Got fatal error 1236 from master when reading data from binary log

5、Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000017' at 1367, the last event read from 'mysql-bin.000017' at 1367, the last byte read from 'mysql-bin.000017' at 1368.'

解决方案：

stop slave;

下方的master_log_file ，master_log_pos是前方让记住的文件位置，一定不能错

MariaDB [test1]> CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=329;
Query OK, 0 rows affected (0.072 sec)

然后start slave;可以解决,参考：同一条服务器（centos7）配置mysql 主从同步

6、Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000017' at 1367, the last event read from 'mysql-bin.000017' at 1367, the last byte read from 'mysql-bin.000017' at 1368.'

使用如下代码刷新master的配置，然后根据上方更新一下Slave

flush logs;
show master status;

本文参考：MySQL Replication浅析