Mysql 5.6 Master和Slave 主备切换

--master/slave切换记录，用于后期查找

环境：
master:
rhel 6.6
mysql 5.6
192.168.15.101


salve:
rhel 7.2
mysql 5.6
192.168.15.102

主备库切换要点：




主库配置文件：
[root@rhel66db ~]# more /etc/my.cnf
[mysqld]  
port = 3306  
character_set_server=utf8  
character_set_client=utf8  
collation-server=utf8_general_ci  
lower_case_table_names=1  
max_connections=1000  
datadir=/usr/local/mysql  
socket=/tmp/mysql.sock  
user=mysql  
# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
server-id=101
log-bin=mysql-bin 
auto_increment_increment=2
auto_increment_offset=1
binlog-ignore=mysql
binlog-ignore=information_schema
replicate-do-db=gaoyc
  
[mysqld_safe]  
log-error=/usr/local/mysql/mysqld.log  
pid-file=/usr/local/mysql/mysqld.pid  




备库配置文件：
[root@rhel72 ~]# more /etc/my.cnf
[mysqld]  
port = 3306  
character_set_server=utf8  
character_set_client=utf8  
collation-server=utf8_general_ci  
lower_case_table_names=1  
max_connections=1000  
datadir=/usr/local/mysql  
socket=/tmp/mysql.sock  
user=mysql  
# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
server-id=102
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
binlog-ignore=mysql
binlog-ignore=information_schema
replicate-do-db=gaoyc


  
[mysqld_safe]  
log-error=/usr/local/mysql/mysqld.log  
pid-file=/usr/local/mysql/mysqld.pid  




--主库操作，配置为只读

mysql> set global read_only=1;
mysql> show variables like 'read%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_buffer_size     | 131072 |
| read_only            | ON     |
| read_rnd_buffer_size | 262144 |

检查是否有更新
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000005 |     1112 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000005 |     1112 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+

确定没有数据变化后，转到备库操作：

--备库操作：
mysql> select user,host,password from mysql.user;
+---------+----------------+-------------------------------------------+
| user    | host           | password                                  |
+---------+----------------+-------------------------------------------+
| root    | localhost      | *CF0659A9DA04B3263D578512677CCFD35F0CC0B9 |
| root    | 127.0.0.1      |                                           |
| root    | ::1            |                                           |
| mysql11 | 192.168.15.101 | *76AC9DFD234FC9D9CA343C75B782D05407B53BCC |
+---------+----------------+-------------------------------------------+
4 rows in set (0.00 sec)


mysql> show processlist;
+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db    | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+
|  1 | root        | localhost | gaoyc | Query   |    0 | init                                                                        | show processlist |
|  7 | system user |           | NULL  | Connect |  833 | Waiting for master to send event                                            | NULL             |
|  8 | system user |           | NULL  | Connect |  833 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)


mysql> show processlist;
+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db    | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+
|  1 | root        | localhost | gaoyc | Query   |    0 | init                                                                        | show processlist |
|  7 | system user |           | NULL  | Connect |  841 | Waiting for master to send event                                            | NULL             |
|  8 | system user |           | NULL  | Connect |  841 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

检查发现 Slave has read all relay log后，确保所有日志已应用后，停止复制。
mysql> STOP SLAVE IO_THREAD
    -> ;
Query OK, 0 rows affected (0.00 sec)


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.15.101
                  Master_User: mysql12
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 2740
               Relay_Log_File: rhel72-relay-bin.000003
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: gaoyc
          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: 2740
              Relay_Log_Space: 2248
              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: NULL
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: 101
                  Master_UUID: 3e688040-3c80-11e8-a80a-0800275acb61
             Master_Info_File: /usr/local/mysql/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.00 sec)


ERROR: 
No query specified


mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.15.101
                  Master_User: mysql12
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 2740
               Relay_Log_File: rhel72-relay-bin.000003
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: gaoyc
          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: 2740
              Relay_Log_Space: 2248
              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: NULL
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: 101
                  Master_UUID: 3e688040-3c80-11e8-a80a-0800275acb61
             Master_Info_File: /usr/local/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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.00 sec)


ERROR: 
No query specified


mysql> RESET MASTER;
Query OK, 0 rows affected (0.02 sec)


mysql> reset slave;
Query OK, 0 rows affected (0.02 sec)


mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysql12'@'192.168.15.101' IDENTIFIED BY 'mysql12';
Query OK, 0 rows affected (0.00 sec)


mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)


mysql> select user,host,password from mysql.user;
+---------+----------------+-------------------------------------------+
| user    | host           | password                                  |
+---------+----------------+-------------------------------------------+
| root    | localhost      | *CF0659A9DA04B3263D578512677CCFD35F0CC0B9 |
| root    | 127.0.0.1      |                                           |
| root    | ::1            |                                           |
| mysql12 | 192.168.15.101 | *BEE13BB659FAA4D3ABFA2A849B1F9A6776DB65E2 |
| mysql11 | 192.168.15.101 | *76AC9DFD234FC9D9CA343C75B782D05407B53BCC |
+---------+----------------+-------------------------------------------+
5 rows in set (0.00 sec)


mysql> quit
Bye
[root@rhel72 local]# ps -ef | grep mysql
root      5789     1  0 02:04 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql --pid-file=/usr/local/mysql/rhel72.pid
mysql     6112  5789  0 02:04 pts/0    00:00:10 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/mysqld.log --pid-file=/usr/local/mysql/rhel72.pid --socket=/tmp/mysql.sock --port=3306
root      6174  2877  0 02:18 pts/1    00:00:00 tail -30f mysqld.log
root      8263  2195  0 04:29 pts/0    00:00:00 grep --color=auto mysql
[root@rhel72 local]# kill -9 6112  5789
[root@rhel72 local]# pwd
/usr/local
[root@rhel72 local]# ./mysql.server start
Starting MySQL SUCCESS! 
[root@rhel72 local]# mysql -uroot -pgyc1234
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.38-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)


Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> select user,host,password from mysql.user;
+---------+----------------+-------------------------------------------+
| user    | host           | password                                  |
+---------+----------------+-------------------------------------------+
| root    | localhost      | *CF0659A9DA04B3263D578512677CCFD35F0CC0B9 |
| root    | 127.0.0.1      |                                           |
| root    | ::1            |                                           |
| mysql12 | 192.168.15.101 | *BEE13BB659FAA4D3ABFA2A849B1F9A6776DB65E2 |
| mysql11 | 192.168.15.101 | *76AC9DFD234FC9D9CA343C75B782D05407B53BCC |
+---------+----------------+-------------------------------------------+
5 rows in set (0.00 sec)


mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000002 |      120 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)


--原主库操作
mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'read%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_buffer_size     | 131072 |
| read_only            | OFF    |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
3 rows in set (0.00 sec)


mysql> RESET MASTER;
Query OK, 0 rows affected (0.03 sec)


mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      120 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)


mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.15.102',
    -> MASTER_USER='mysql12',
    -> MASTER_PASSWORD='mysql12',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=120;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)


mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.15.102',
    -> MASTER_USER='mysql12',
    -> MASTER_PASSWORD='mysql12',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.02 sec)


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


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.15.102
                  Master_User: mysql12
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: gaoyc
          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: 120
              Relay_Log_Space: 457
              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: 102
                  Master_UUID: 81add948-3c6e-11e8-a796-08002706db9b
             Master_Info_File: /usr/local/mysql/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.00 sec)


ERROR: 
No query specified
--验证
##主库
mysql> ues gaoyc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ues gaoyc' at line 1
mysql> use gaoyc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> insert into gyc01 select * from gyc01;
Query OK, 80 rows affected (0.01 sec)
Records: 80  Duplicates: 0  Warnings: 0


#备库


mysql> use gaoyc;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_gaoyc |
+-----------------+
| gyc             |
| gyc01           |
| tab1            |
+-----------------+
3 rows in set (0.00 sec)


mysql> select count(*) from gyc01;
+----------+
| count(*) |
+----------+
|       80 |
+----------+
1 row in set (0.00 sec)


mysql> select count(*) from gyc01;
+----------+
| count(*) |
+----------+
|      160 |
+----------+
1 row in set (0.00 sec)


slave 状态参数说明
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.15.101
                  Master_User: mysql12
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 2264
               Relay_Log_File: rhel72-relay-bin.000002
                Relay_Log_Pos: 1435
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: gaoyc
          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: 2264
              Relay_Log_Space: 1609
              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: 101
                  Master_UUID: 3e688040-3c80-11e8-a80a-0800275acb61
             Master_Info_File: /usr/local/mysql/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


The key fields from the status report to examine are:


Slave_IO_State: The current status of the slave. See Section 8.14.6, “Replication Slave I/O Thread States”, andSection 8.14.7, “Replication Slave SQL Thread States”, for more information.


Slave_IO_Running: Whether the I/O thread for reading the master's binary log is running. Normally, you want this to beYes unless you have not yet started replication or have explicitly stopped it with STOP SLAVE.


Slave_SQL_Running: Whether the SQL thread for executing events in the relay log is running. As with the I/O thread, this should normally be Yes.


Last_IO_Error, Last_SQL_Error: The last errors registered by the I/O and SQL threads when processing the relay log. Ideally these should be blank, indicating no errors.


Seconds_Behind_Master: The number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to handle events from the master in a timely fashion.


A value of 0 for Seconds_Behind_Master can usually be interpreted as meaning that the slave has caught up with the master, but there are some cases where this is not strictly true. For example, this can occur if the network connection between master and slave is broken but the slave I/O thread has not yet noticed this—that is, slave_net_timeout has not yet elapsed.


It is also possible that transient values for Seconds_Behind_Master may not reflect the situation accurately. When the slave SQL thread has caught up on I/O, Seconds_Behind_Master displays 0; but when the slave I/O thread is still queuing up a new event, Seconds_Behind_Master may show a large value until the SQL thread finishes executing the new event. This is especially likely when the events have old timestamps; in such cases, if you execute SHOW SLAVE STATUS several times in a relatively short period, you may see this value change back and forth repeatedly between 0 and a relatively large value.


Several pairs of fields provide information about the progress of the slave in reading events from the master binary log and processing them in the relay log:


(Master_Log_file, Read_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave I/O thread has read events from that log.


(Relay_Master_Log_File, Exec_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave SQL thread has executed events received from that log.


(Relay_Log_File, Relay_Log_Pos): Coordinates in the slave relay log indicating how far the slave SQL thread has executed the relay log. These correspond to the preceding coordinates, but are expressed in slave relay log coordinates rather than master binary log coordinates.


The SHOW STATUS statement also provides some information relating specifically to replication slaves. The replication heartbeat information displayed by SHOW STATUS lets you check that the replication connection is active even if the master has not sent events to the slave recently. The master sends a heartbeat signal to a slave if there are no updates to, and no unsent events in, the binary log for a longer period than the heartbeat interval. The MASTER_HEARTBEAT_PERIOD setting on the master (set by theCHANGE MASTER TO statement) specifies the frequency of the heartbeat, which defaults to half of the connection timeout interval for the slave (slave_net_timeout). The Slave_last_heartbeat variable for SHOW STATUS shows when the replication slave last received a heartbeat signal.