mysql主从切换

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:mysql主从切换

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1、修改配置文件
read-only=1(主库)
#read-only=1(备库)

2、查询从库状态
mysql> show processlist ;
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| 1 | root | localhost | ecp | Query | 0 | NULL | show processlist |
| 4 | system user | | NULL | Connect | 2 | Waiting for master to send event | NULL |
| 5 | system user | | NULL | Connect | 2 | 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 slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 107
Relay_Log_File: replicate.000007
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
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: 107
Relay_Log_Space: 549
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: 2
1 row in set (0.00 sec)

3、查询主库状态
mysql> show processlist;
+—-+——+——————-+——+————-+——+———————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+——————-+——+————-+——+———————————————————————–+——————+
| 1 | root | localhost | ecp | Query | 0 | NULL | show processlist |
| 2 | repl | 192.168.1.4:17948 | NULL | Binlog Dump | 6 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
+—-+——+——————-+——+————-+——+———————————————————————–+——————+
2 rows in set (0.00 sec)

mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

4、从库操作
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW PROCESSLIST;
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| 1 | root | localhost | ecp | Query | 0 | NULL | SHOW PROCESSLIST |
| 5 | system user | | NULL | Connect | 256 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
2 rows in set (0.00 sec)

确保状态为:has read all relay log

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 107
Relay_Log_File: replicate.000007
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
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: 107
Relay_Log_Space: 549
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: 2
1 row in set (0.00 sec)

5、查询主库状态
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

6、从库变主库
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

7、主库变从库
mysql> RESET MASTER;
Query OK, 0 rows affected (0.06 sec)

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

mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.4′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’xifenfei’,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.05 sec)

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

8、重启主和从库
[root@localhost mysql]# service mysql restart
Shutting down MySQL….[ OK ]
Starting MySQL…………….[ OK ]

9、检查主从是否都正常
主库
SHOW PROCESSLIST;
show master status \G

从库
SHOW PROCESSLIST;
start slave;
show slave status \G

如果有错误,根据错误提示,解决问题
创建主从复制,请见使用xtrabackup 配置主从服务器

此条目发表在 MySQL 分类目录。将固定链接加入收藏夹。

mysql主从切换》有 3 条评论

  1. 惜 分飞 说:

    1、重启是的修改的配置文件生效
    2、不重启,有时候,slave进程不会正常工作

  2. likydva 说:

    为什么最后还要重启呢? [root@localhost mysql]# service mysql restart

  3. 惜 分飞 说:

    1、修改主从参数
    主库:
    relay-log=/opt/mysql/mysqldata/mysqllog/replicate
    relay-log-index=/opt/mysql/mysqldata/mysqllog/replicate.index
    read-only=1
    skip-slave-start=1
    innodb_flush_log_at_trx_commit = 1
    sync-binlog=1
    备库:
    #read-only=1

    2、查看主备库状态
    show slave status \G
    show master status \G

    3、操作备库
    STOP SLAVE IO_THREAD;
    SHOW PROCESSLIST;
    看到Has read all relay log
    show slave status \G
    STOP SLAVE;
    RESET MASTER;
    RESET SLAVE;
    show master status \G

    4、操作主库
    show master status \G
    RESET MASTER;
    RESET SLAVE;
    CHANGE MASTER TO
    MASTER_HOST=’192.168.1.4′,
    MASTER_USER=’repl’,
    MASTER_PASSWORD=’xifenfei’,
    MASTER_LOG_FILE=’mysq_lbin.000001′,
    MASTER_LOG_POS=107;

    5、重启新主从库,开启slave进程,检查是否正常
    主库
    SHOW PROCESSLIST;
    show master status \G
    从库
    start slave;
    SHOW PROCESSLIST;
    show slave status \G