If the main Mysql database is down, how to solve the problem?

—- master/slave synchronization schematic diagram of MySQL



Mysql main database down

1) Hardware problems, (server, ECS, virtual host, etc.) down

2) Service problem, Mysql down, service exception, port exception, etc

Second, hardware problem processing ideas

If a hardware problem occurs, you can view IDC inspection records or view the hardware running status on the remote control card. Then, you can report a hardware fault based on the actual situation and rectify the fault.

1) Check the alarm information, confirm whether the business is affected, and switch from the database for data exchange if necessary. 2) IDC inquiry and troubleshooting 3) Confirm the hardware failure, which cannot be repaired in a short time, and initiate Case processing 4) Notify department leaders, process progress, and record in real time. 5) Write fault report and meeting notification after the event processing is completed.Copy the code

MySQL Service is faulty

1) The first thing to do is to determine whether services are affected and whether it is necessary to cut the database, so as to ensure the primary task when services are running. 2) If the slave database needs to be cut at this time, install the following steps:

Show processList \GThe primary and secondary databases are in sync =====. If the primary and secondary databases are in sync =====
#state: waiting for master to send event I/O thread
#state: has read all relay log; Waiting for the slave I/O thread to update it SQL thread2> Log in to the secondary library and check:  cat /data/3306/data/master.info cat /data/3307/data/master.infoThe master.info of the master library is updated to indicate which master library is more consistent, so the latest master library is determined.Choose the largest POS library as the master library or use the semi-synchronization function, directly select the real-time synchronization of the slave library. 3> Ensure all relay agentslogAll updated. stop slave io_thread; show processlist; [Execute on each slave library]Read all relay log; Indicates that all updates from the library are complete4> log in to the secondary database mysql -uroot -p Your password -s /data/3306/mysql.sockThe sock path is modified according to its ownstop slave; reset master; quit; 5> Go to the database data directory and delete master.info relay-log.infocd /data/3306/data/master
rm -f master.info relay-log.info
## Check the authorization table, similar to the read-only parameterVim /data/3306/my.cnflog-bin =/data/3306/mysql-bin
## // If log-slave-updates read-only is present, comment it out.
/data/3306/mysql  stop
/data/3306/mysql  start
Copy the code

At this point, the promotion of the main library is complete

4. All slaves point to the new master

7> If the master database server is not down, you need to go to the master database to get bin-log to upgrade the slave database. 8> Other slave database operations [to the new master] checked (synchronization user rep exists) log in to the slave database stop slave; change master to master_host='192.168.1.32'; // If not synchronized, specify the position point start slave; show slave status\G;Copy the code

The above is about how to quickly restore the database business introduction, if you have a better way to tell me, the following is the centralized database high availability scheme:

1. MHA high available at http://blog.51cto.com/qiuyt/1930629

2. The high availability of RDS + since the disaster arouses borrow figure 】 【 https://www.aliyun.com/product/rds/mysql



3. The snapshot

There are many ways, depending on how you choose.Copy the code

In addition, ECS cannot use MHA to explain the problem

2. Aliyun ECS does not support floating IP address. Aliyun HaVIP is disabledNote: Ali Cloud's ECS server is different from our own virtual machine, it does not support floating IP3, Keepalived can only set unicast on ali cloud server 4, Keepalived configuration file add a script to determine whether mysql startup needs to write a good configuration fileCopy the code

Aliyun work order feedback screenshot