MySQL 5.7.12 Replication(复制)配置

数据库MySQL5.7.12的Replication配置和之前MySQL5.1有所不同，目前尚不可知是从哪个版本开始变更的-待后续学习，本文主要讲如何做Replication。

MySQL 5.7.12 支持多源复制、多线程复制，当前猜测是因为多源复制取消了my.cnf中配置master-host之类，采用CHANGE MASTER TO 可配置多个源。

1. master 配置
   my.cnf

server_id = 2123
log-bin=mysql-bin
binlog-do-db=test 
replicate-same-server-id

新建同步账号

/opt/mysql/bin/mysql -uroot -p123456 --socket=/opt/mysql/mysql.sock -e "create user rep identified by 'admin2012';" 
/opt/mysql/bin/mysql -uroot -p123456 --socket=/opt/mysql/mysql.sock -e "grant replication slave on *.* to 'rep'@'192.168.220.%' identified by 'admin2012';"

启动mysql

/opt/mysql/bin/mysqld --defaults-file=/opt/mysql/my.cnf --user=root

登录

mysql -uroot -p123456 --socket=/opt/mysql/mysql.sock

检查master

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

2. slave 配置
   修改配置my.cnf

server_id = 2126

启动mysql

 /opt/mysql/bin/mysqld --defaults-file=/opt/mysql/my.cnf --user=root

登录

mysql -uroot -p123456 --socket=/opt/mysql/mysql.sock

设置master

mysql>CHANGE MASTER TO MASTER_HOST='192.168.220.123',MASTER_USER='rep', MASTER_PASSWORD='admin2012',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;

启动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.220.123
                  Master_User: rep
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysqld-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: test
          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: 528
              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: 2123
                  Master_UUID: 44f02265-5a22-11e6-a7d6-1418774a0abb
             Master_Info_File: /opt/mysql/data/master.info
                    SQL_Delay: 0