mysql主从切换 日志_Mysql 主从切换

一主一从切换示例

1.修改配置文件

master 配置文件

innodb_flush_log_at_trx_commit=1

sync_binlog=1

read_only=1

slave 配置文件

innodb_flush_log_at_trx_commit=1

sync_binlog=1

#read_only=1

2.查询主，从库的状态

--master

mysql>  show processlist \G

*************************** 1. row ***************************

Id: 9

User: root

Host: localhost

db: test

Command: Sleep

Time: 576

State:

Info: NULL

*************************** 2. row ***************************

Id: 13

User: repl

Host: 192.168.1.106:41457

db: NULL

Command: Binlog Dump

Time: 404

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

*************************** 3. row ***************************

Id: 15

User: root

Host: localhost

db: test

Command: Query

Time: 0

State: NULL

Info: show processlist

3 rows in set (0.01 sec)

mysql> show master status \G

*************************** 1. row ***************************

File: mysql-bin.000007

Position: 2940

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

--slave

mysql>  show processlist \G

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 353

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 35

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 3. row ***************************

Id: 4

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

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.107

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 2940

Relay_Log_File: master-relay-bin.000012

Relay_Log_Pos: 1026

Relay_Master_Log_File: mysql-bin.000007

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: 2940

Relay_Log_Space: 1183

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.主库置只读状态

--master

mysql> set global read_only=1;

4.再次查看主，从状态，从接收完所有日志后,停止io线程

--slave

mysql> stop slave io_thread;

Query OK, 0 rows affected (0.00 sec)

mysql>  show processlist \G

mysql> show slave status \G

Make sure that the slave have processed any statements in their relay log.

show processlist \G中显示 State: Slave has read all relay log;

5.从库置为主库

--slave

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql> reset slave;

Query OK, 0 rows affected (0.01 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)

mysql> set global read_only=0;

6.主库置为从库

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql>  CHANGE MASTER TO

->         MASTER_HOST='master',

->         MASTER_USER='repl',

->         MASTER_PASSWORD='repl',

->         MASTER_LOG_FILE='mysql-bin.000001',

->         MASTER_LOG_POS=107;

Query OK, 0 rows affected (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 107

Relay_Log_File: slave1-relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000001

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: 410

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: 1

1 row in set (0.00 sec)