mysql 主从切换

<pre name="code" class="sql">1> 正常切换

1）从服务器检查SHOW PROCESSLIST语句的输出，直到你看到Has read all relaylogwaiting for the slave I/O thread to update it




2）确保从服务器已经处理了日志中的所有语句。 mysql> STOP SLAVE IO_THREAD




当从服务器都执行完这些，它们可以被重新配置为一个新的设置。



3）在被提升为主服务器的从服务器上，发出 STOP SLAVE和RESET MASTER和RESET SLAVE操作。



4）然后重启mysql服务。


5）在备用服务器（新的主服务器）创建复制用户grant replication slave on *.* torepdcs@'IP of A' identified by 'replpwd'

grant allprivileges on *.* to 'repdcs'@'IP' identified by replpwd;


6) 在主服务器上RESET MASTER。然后CHANGE MASTER TOMASTER_HOST='原从服务器IP',MASTER_USER='用户名',MASTER_PASSWORD='密码' ,master_log_file='master-bin.000015' ;

7)查看状态 show slave status \G;

Show master status \G;

如果还不行再次重启主库，并在备库上执行CHANGE MASTER TOMASTER_HOST='192.168.0.110',MASTER_USER='repdcs',MASTER_PASSWORD='111111',master_log_file='mysqlbin.000002';

8）修改应用的连接地址到新的主库

切换完成。

2> 主机直接宕机

1> 在备机上执行STOP SLAVE 和RESET MASTER

2> 查看show slave status \G;

3> 然后修改应用的连接地址。

一般大部分切换为直接宕机主机已经没法提供服务



RESET MASTER:

   这个是重置master的核心语法，看一下官方解释。
    RESET MASTER removes all binary log files that are listed in the index file, leaving only a single, empty binary log file with a numeric suffix of .000001, whereas the numbering is not reset by 

PURGE BINARY LOGS.
    RESET MASTER is not intended to be used while any replication slaves are running. The behavior. of RESET MASTER when used while slaves are running is undefined (and thus unsupported), whereas 

PURGE BINARY LOGS may be safely used while replication slaves are running.


   RESET MASTER 删除所有的binary log 文件 在index file中显示的, 只留下一个单独的,空的2进制文件以.000001后缀, PURGE BINARY LOG 不会复位日志编号。

   RESET MASTER 不适用于 当任何的复制在运行.


    RESET SLAVE makes the slave forget its replication position in the master's binary log. This statement is meant to be used for a clean start: It deletes the master.info and relay-log.info 

files, all the relay log files, and starts a new relay log file. To use RESET SLAVE, the slave replication threads must be stopped (use STOP SLAVE if necessary). 


RESET SLAVE 使slave忘记 它的复制position 在master的binary log里,这个语句意味着一个清理的开始, 它删除master.info 和relay-log.info 文件

和所有的relay log 文件 ,启动一个新的relay log 文件,适用RESET SLAVE, slave复制必须停止(use STOP SLAVE if necessary). 



切换具体步骤:

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


2.确保从服务器已经处理了日志中的所有语句。 mysql> STOP SLAVE IO_THREAD

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.33.220
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000010
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: Yes



3）在被提升为主服务器的从服务器上，发出 STOP SLAVE和RESET MASTER和RESET SLAVE操作。

4）然后重启mysql服务。

记得去掉从的只读选项