MySQL主从切换
来源:互联网 发布:潍坊网络招聘 编辑:程序博客网 时间:2024/05/22 20:29
1、查看从库数据库状态:
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
|2 | root | 127.0.0.1:59012 | sakila |Query | 0| NULL | showprocesslist |
|3 | system user | | NULL | Connect | 17618 |Waiting for master to send event | NULL |
|4 | system user | | NULL | Connect | 17305 |Slave has read all relay log; waiting for the slave I/O thread toupdate it | NULL |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 172.16.33.143
Master_User: master
Master_Port: 3306
Connect_Retry:60
Master_Log_File: mysql.000003
Read_Master_Log_Pos:3296928
Relay_Log_File:slave-relay-bin.000002
Relay_Log_Pos:3296845
Relay_Master_Log_File:mysql.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:test
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:3296928
Relay_Log_Space: 3297001
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)
mysql> show processlist;
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db |Command | Time | State | Info |
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
|1 | root | 127.0.0.1:33623 | sakila | Query | 0 | NULL | show processlist|
|2 | master | 172.16.33.144:48988 | NULL | Binlog Dump | 17455 | Master has sent allbinlog to slave; waiting for binlog to be updated | NULL |
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB|
+--------------+----------+--------------+------------------+
| mysql.000003 |3296928 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2、修改配置文件:
主库:read_only = 1
从库:#read_only = 1
3、操作从库:
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
|2 | root | 127.0.0.1:59012 | sakila |Query | 0| NULL | showprocesslist |
|3 | system user | | NULL | Connect | 17770 |Waiting for master to send event | NULL |
|4 | system user | | NULL | Connect | 17457 |Slave has read all relay log; waiting for the slave I/O thread toupdate it | NULL |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
|2 | root | 127.0.0.1:59012 | sakila |Query | 0| NULL | showprocesslist |
|4 | system user | | NULL | Connect | 17526 |Slave has read all relay log; waiting for the slave I/O thread toupdate it | NULL |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
确认从库的状态为:Slave has read all relay log
mysql> show slave status\G
*************************** 1. row***************************
Slave_IO_State:
Master_Host: 172.16.33.143
Master_User: master
Master_Port: 3306
Connect_Retry:60
Master_Log_File: mysql.000003
Read_Master_Log_Pos:3296928
Relay_Log_File:slave-relay-bin.000002
Relay_Log_Pos:3296845
Relay_Master_Log_File:mysql.000003
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:test
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:3296928
Relay_Log_Space: 3297001
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: 1
1 row in set (0.00 sec)
4、查看主库状态:
mysql> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB|
+--------------+----------+--------------+------------------+
| mysql.000003 |3296928 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5、主从切换,从库变主库:
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB|
+--------------+----------+--------------+------------------+
| mysql.000001 | 107 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6、主从切换,主库变从库:
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master tomaster_host='172.16.33.144',master_port=3306,master_user='master',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.04 sec)
7、重启主从:
[root@yoon ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
8、查看主从是否正常:
主库:
show processlist;
show master status;
从库:
show processlist;
start slave;
show slave status\G
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
|
|
|
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row***************************
Master_SSL_Verify_Server_Cert: No
1 row in set (0.00 sec)
mysql> show processlist;
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
| Id | User
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
|
|
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+
| File
+--------------+----------+--------------+------------------+
| mysql.000003 |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2、修改配置文件:
主库:read_only = 1
从库:#read_only = 1
3、操作从库:
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
|
|
|
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
|
|
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
确认从库的状态为:Slave has read all relay log
mysql> show slave status\G
*************************** 1. row***************************
Master_SSL_Verify_Server_Cert: No
1 row in set (0.00 sec)
4、查看主库状态:
mysql> show master status;
+--------------+----------+--------------+------------------+
| File
+--------------+----------+--------------+------------------+
| mysql.000003 |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5、主从切换,从库变主库:
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+
| File
+--------------+----------+--------------+------------------+
| mysql.000001 |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6、主从切换,主库变从库:
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master tomaster_host='172.16.33.144',master_port=3306,master_user='master',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.04 sec)
7、重启主从:
[root@yoon ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
8、查看主从是否正常:
主库:
show processlist;
show master status;
从库:
show processlist;
start slave;
show slave status\G
0 0
- mysql主从备份切换
- Mysql+keepalived主从切换
- mysql主从数据库切换
- mysql主从切换步骤
- mysql主从切换
- mysql主从切换
- MYSQL主从切换
- mysql 主从服务器 切换
- mysql 主从切换
- mysql 主从切换
- MySQL主从切换步骤。
- mysql主从切换步骤
- MySQL主从切换
- mysql主从手动切换
- mysql主从切换步骤
- Mysql之主从切换
- MySQL主从切换
- mysql 主从切换
- MySQL不同库名相同表结构实现主从…
- MySQL主从同步报Client reque…
- java算法及应用场景
- MySQL主从故障修复
- 博客十年,感谢有你!
- MySQL主从切换
- 大数据
- MySQL 日志 undo | redo
- hdu 3530 Subsequence
- ALTER TABLE causes&nbs…
- MySQL通过Binlog恢复删除的表
- Barracuda VS antelope
- mysqldump 参数说明
- MySQL复制