window下mysql的主從設定和切換

1：主從設定

環境說明：

Master：192.168.1.102

Slave：192.168.1.111

 MySQL 的 Master 配置：

配置my.ini：(一般要管理員權限 可以去cmd下)

[mysqld]

# The TCP/IP Port the MySQL Server will listen on

port=3306

server-id=102

log-bin=mysql-bin

relay-log=relay-bin

relay-log-index=relay-bin-index

*配置好後，重啟Master 的 MySQL服務！！！

//--------------------------------------------------

MySQL 的 Slave 配置：

配置my.ini：

[mysqld]

# The TCP/IP Port the MySQL Server will listen on

port=3306

server-id = 111

log-bin=mysql-bin

replicate-do-db = fvbiz

*配置好後，重啟Slave 的 MySQL服務！！！

//--------------------------------------------------

登入Slave，然後執行下面命令：

mysql> stop slave;

mysql> change master to master_host='192.168.1.102',master_user='root',master_password='123456';

mysql> start slave;

mysql> show slave status\G;

查看這兩項是否為YES，yes為正常。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

 

 2:主從切換

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

Master_User: root

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.111: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.102

Master_User: root

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、主庫變從庫

(注意：此值要在主庫重啟後 show master status \G獲得)

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.111′,

-> MASTER_USER=’root’,

-> MASTER_PASSWORD=’123456’,

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

如果有錯誤，根據錯誤提示，解決問題