mysql 主从切换 恢复_mysql主从切换 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜...

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 配置主从服务器