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
如果有错误，根据错误提示，解决问题
