Mysql DBA Advanced OPS Learning Note-one master multiple from the library to switch between master continuation and sync from library process

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