MySql主从同步

一. 背景介绍

---

• 2017-09-25日，早上上班，发现公司数据库被黑客绑票，要赎金。我们用的MySql 5.6.37，放在XX云上。给客服打电话，建议重装系统，重装MySql。干脆，顺手装了主从同步。
  
  
• 安装环境： Linux version 3.10.0-514.el7.x86_64 (builder@kbuilder.dev.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-11) (GCC) ) #1 SMP Tue Nov 22 16:42:41 UTC 2016；Mysql5.6.37
• 说明：【192.168.1.203（主库） 192.168.1.202（从库）】分别放在两个服务器上
• 环境安装好了，再往下面看。谢谢

二. 大致步骤

---

• 【主库】

  1. 修改MySql配置文件，在里面加一些配置(我这里设置了两个数据库的主从同步)

     sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
     
     log-bin=mysql-bin
     
     # 用于标识唯一的数据库，在设置从库的时候就需要设置为其他值
     
     server-id=1
     
     # 表示同步的时候忽略的数据库
     
     binlog-ignore-db=information_schema
     binlog-ignore-db=cluster
     binlog-ignore-db=mysql
     
     
     # 指定需要同步的数据库
     
     binlog-do-db=mydb
     binlog-do-db=mydb_store
     

  2. 【进入主库，执行sql】以这里为例，创建了一个用户叫masterslave，密码是123，来自192.168.1.202（也就是从库），允许该用户在主库上读取日志，赋予File权限，只赋予File权限还不行，还要给它REPLICATION SLAVE的权限才可以

     GRANT FILE ON *.* TO 'masterslave'@'192.168.1.202' IDENTIFIED BY '123';
     GRANT REPLICATION SLAVE ON *.* TO 'masterslave'@'192.168.1.202' IDENTIFIED BY '123';
     FLUSH PRIVILEGES;

  3. 重启mysql，查询主库信息

     service mysql restart;
     show master status;

     查询到的结果如下(记住File和Position的值)：

     mysql> show master status ;
     +------------------+----------+-----------------+----------------------------------+-------------------+
     | File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB                 | Executed_Gtid_Set |
     +------------------+----------+-----------------+----------------------------------+-------------------+
     | mysql-bin.000027 |      896 | mydb,mydb_store | information_schema,cluster,mysql |                   |
     +------------------+----------+-----------------+----------------------------------+-------------------+
     1 row in set (0.00 sec)
     

---

• 【从库】

  1. 修改MySql配置文件，在里面加一些配置

     sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
     log-bin=mysql-bin
     server-id=2 # 用于标识唯一的数据库，在设置从库的时候就需要设置为其他值
     binlog-ignore-db=information_schema # 表示同步的时候忽略的数据库
     binlog-ignore-db=cluster # 表示同步的时候忽略的数据库
     binlog-ignore-db=mysql # 表示同步的时候忽略的数据库
     replicate-ignore-db=mysql
     replicate-do-db=mydb # 指定需要同步的数据库
     replicate-do-db=mydb_store # 指定需要同步的数据库
     log-slave-updates
     slave-skip-errors=all
     slave-net-timeout=60

  2. 重启MySql

     service mysql restart;

  3. 进入mysql。关闭slave，设置参数，开启slave

     stop slave;
     
     # 【master_log_file 为上面记着的File，master_log_pos为上面记着的position】
     
     change master to master_host='192.168.1.203',master_user='masterslave',master_password='123',master_log_file='mysql-bin.000027', master_log_pos=896;
     show slave status \G;

  4. 如果不出意外，会看到如下信息：

     mysql> show slave status \G;
     
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.1.203
                   Master_User: masterslave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000027
             Read_Master_Log_Pos: 896
                Relay_Log_File: ecs-749d-0005-relay-bin.000002
                 Relay_Log_Pos: 1059
               Relay_Master_Log_File: mysql-bin.000027
                  Slave_IO_Running: Yes
                 Slave_SQL_Running: Yes
                   Replicate_Do_DB: mydb,mydb_store
               Replicate_Ignore_DB: mysql
                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: 896
                   Relay_Log_Space: 1240
                   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: 1
                       Master_UUID: 6f9f3a92-a1ce-11e7-b74d-fa163eca40a4
                  Master_Info_File: /usr/local/mysql/data/master.info
                         SQL_Delay: 0
               SQL_Remaining_Delay: NULL
           Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
                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
     1 row in set (0.01 sec)
     
     ERROR: 
     No query specified
     
     mysql> 

  5. 如果其中这两行都是YES，那就是成功了

     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes

  6. 如果这两行不是这样，那就说明某个环节配置有问题，常见情况是
     Slave_IO_Running: No
     或者
     Slave_IO_Running: Connecting
     具体信息请看上面slave status里面的字段【 Last_IO_Errno 和 Last_IO_Error】