1. Master Vault Master Outage
Sign in from library show Processlist\g
See update status for two threads
mysql> show processlist\G*************************** 1. row *************************** Id: 1 User: system user Host: db: NULLCommand: Connect Time: 22997 State: Waiting for master to send event Info: NULL*************************** 2. row *************************** Id: 2 User: system user Host: db: NULLCommand: Connect Time: 21500 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL*************************** 3. row *************************** Id: 6 User: root Host: localhost db: NULLCommand: Query Time: 0 State: NULL Info: show processlist3 rows in set (0.00 sec)
Log in from the library to view Master.info separately
cat /data/3307/data/master.infocat /data/3308/data/master.info
Ensure that the update is complete, see two from the library which library of the Binlog location of the fastest update, after testing without delay POS gap is very small, even consistent.
[[email protected] ~]# sed -n "2,3p" /data/3307/data/master.info mysql-bin.000010633
Select Update Fastest, POS max as Main library
Or the use of synchronous function directly choose to do a real-time synchronization of this from the library.
Authorizing synchronization of users as the main library
If only the main library down, the server can still get up also need to pull down the main library Binlog, in from the library to complement the gap back. Then take this from the library as the main library.
Steps:
(1) Ensure all relay log updates are complete
The Stop slave io_thread;show processlist is executed on each of the Slave libraries
Until you see slave have read all relay log indicates that the update from the library is complete.
(2) Log in from the library
mysql –uroot –p123456 –S /data/3307/mysql.sock -e "Stop slave;Retset masterQuit"Stop slave :停止同步Retset master :删除所有的binglog日志文件,并将日志索引文件清空,重新开始所有新的日志文件Quit
(3) Go to delete master.info from library database data root directory Relay-log.info
cd /data/3307/data/rm –f master.info relay-log.info
Check the authorization table, read-only and other parameters, if you have configured these parameters to clean up
(4) 3307 promotion from library to main library
vim /data/3307/mysql-bin
Open:
log-bin = /data/3307/mysql-bin
If there is log-slave-updates read-only, etc. must be commented out
Restart MySQL service/data/3307/mysql restart
End Upgrade Main Library Complete
(5) If the main library server is not down, you need to go to the main binlog to improve the main library from the library
(6) Other operations from the library
Checked (synchronizing user rep office exists)
Log in from library
Stop slave;Change master to master_host =’192.168.1.115’;如果不同步就指定位置点Start slave;Show slave status\G
If you do not synchronize the binlog of the newly generated main library, then sync down.
(7) Modify the program configuration file from the primary database specified from the database
If you access the database with a domain name, you can directly modify the hosts resolution.
(8) Repair of damaged main library complete use as from library or switch
For more information about recovery, refer to the FAQ in the sixth chapter of the MySQL manual.
2. Downtime outside the main library, such as: We have a plan to switch, how to do it?
A. Main Library lock table, set synchronization parameters change master
B. Log in to all libraries to see if the synchronization status is complete.
3.mysql5.5 support for semi-synchronous
A plugin
One master multiple from the main library down how to recover, determine the new main library through Master.info.
A master multi-slave recovery under semi-synchronization is directly determined from the library to set the semi-synchronous to the main library.
Let a certain stability from the library and the main library exactly the same, that is, the main library and this from the library to update the data, in return to the user update success.
Advantages:
Ensure that at least one is consistent from library and master library data
Disadvantages:
The user experience is poor when the network latency between master and slave or the library has problems, of course you can set the timeout time to 10 seconds.
4. Downtime from library slave
Recovery method: Redo Slave
A. Importing data
mysql –uroot –p123456 –S /data/3307/mysql.sock < test.sql &
B. Configuring sync parameters from Library change master
mysql> CHANGE MASTER TO -> MASTER_HOST=‘192.168.1.115‘, 主库的IP -> MASTER_PORT=3306, 主库的端口,从库端口可以和主库不同 -> MASTER_USER=‘rep‘,主库上建立的用于复制的用户rep -> MASTER_PASSWORD=‘123456‘, 这里是rep的密码 -> MASTER_LOG_FILE=‘mysql-bin.000036‘,这里是show master status;查看到的二进制日志文件名称注意不能有空格。 -> MASTER_LOG_POS=335;这里也是show master status时看到的二进制日志偏移量注意不能多空格。
C. Turn on the sync switch from the library to view the sync status
start slave;show slave status\G
Mysql DBA Advanced OPS Learning Note-one master multiple from the library to switch between master continuation and sync from library process