MySQL cluster recovery process

Recently, MySQL-MMM was used in the production environment of the project to improve Database Availability and processing capabilities. In the initial stage of the project, MySQL-Mmm installation, configuration, and deployment were always transparent to our developers. So a "beautiful" desire began to grow in our hearts: we do not need to manage the database, once there is a problem, the system administrator will come to fix it. However, with the deepening of the project, this desire is gradually broken down. Due to improper operations by some developers (of course, developers should not have the right to operate databases directly, which is a management issue .), As a result, the master and slave states of the MySQL cluster are inconsistent and the synchronization cannot be completed. As a result, the master program cannot be started. At this time, our system administrator who initially created the environment was unable to leave because of other projects, and his original warning made us not "more than half a step ". Several intermediate problems were solved temporarily through different methods: Invited DBAs from other project teams, wrote scripts to regularly monitor the mysql-Mmm status, and so on. But by the end of September 30, everything had changed. Once again, the database crashes without warning. This is even worse: one slave cannot be started, and the two slave cannot be synchronized. Only the master is left, and it is still lingering (this word is too much !).

Is MySQL cluster really so troublesome? I can't bear it anymore. I can't pin my hopes on others! After the database reads and writes of the main program are switched to the master, the system tries to restore the MySQL cluster status.

Before proceeding, let's talk about the MySQL cluster configuration: typical writer/reader. Db01 and DB02 are master, db01, and DB02 are both writer, while DB02 is also reader, db03 and DB04 are both slave and reader. DB04 cannot be started.

In order to prevent failure from causing worse effects, DB04 is selected as the trainer object.

Problem 1: MySQL cannot be started.

Symptom 1: The progress continues when service MySQL start is used.

Back up the existing configuration file my. CNF and reinstall MySQL. After installation, MySQL starts normally. Recover my. CNF to/etc/and restart MySQL. Error:

Symptom 2: Starting mysql. Error! Manager of PID-file quit without updating file.

View the *. Err log under/var/lib/MySQL/, find the corresponding prompt, and perform corresponding operations according to the error prompt. The error cause can only be given here due to site loss:

A. The log folder is full and cannot be written.MySQL data files and log files are not stored in the default/var/lib/MySQL directory, but the/opt/MySQL/data directory And/opt/MySQL/log directory are also specified. Run the DF-H and Du-h -- Max-depth = 1 commands to check whether the/opt/MySQL directory is full, so all the files under/opt/MySQL are cleared and restarted.

B. The log file and data file do not exist.Therefore, create two directories, data and log. in CNF, copy the ibdata1, ib_logfile0, and ib_logfile1 branches under/var/lib/MySQL to the data and log directories respectively. Restart and the problem persists.

C. mysql users are not authorized to read data and log directories.Compared with the directories on several other servers, the error log shows that the preceding two folders were created by the root user, and the MySQL user has no read and write permissions. Chown-r mysql. MySQL DATA modifies the directory owner. Restart, and the same error message is coming soon.

D. MySQL cannot operate on existing data files and log files.Delete ibdata1, ib_logfile0, and ib_logfile1 and restart. After successful startup, go back to the directory and check that ibdata1, ib_logfile0, and ib_logfile1 have been created.

Problem 2: The dump file cannot be imported.

After the service is successfully started, configure it according to the mysql-Mmm Installation Guide. Dump the current database content from db01 and import it on DB04. Since the import was conducted on the afternoon of June 13, September 30, this error occurs at http://blog.csdn.net/mydeman/article/details/6843398in order not to delay the shuttle bus and forcibly exit the import process.

After deleting some large databases that are no longer in use today (Remember to back up your data !!). The data files dumped by dump are much smaller, but they exit directly during the export process. Through the PS view, it is found that MySQL has stopped and cannot be restarted. View the error log and find the following information:

[Error]/usr/sbin/mysqld: disk is full Writing '. /MyApp/session. myd' (errcode: 28 ). waiting for someone to free space... (keep CT up to 60 secs delay for server to continue after freeing disk space)

At the time of import, the database was created under the default/var/lib/MySQL directory, and the allocated space was very small, so it was full. Run the MV command to move all databases except MySQL to/opt/MySQL/data/and establish a connection through ln-s. Data is started normally, and the import process is restarted. Bingo!

Problem 3: The change master command cannot be executed.

After importing data files on DB04, follow the mysql-Mmm Installation Guide to complete the subsequent steps and start slave. Then go to the mysql-Mmm admin node and use mmm_control set_online DB04 to bring DB04 online. Mmm_control show: The status is normal.

Install and restore DB04 to DB02 and db03. The previous steps were successful, but an error occurred while executing the change master command. The error message in the log is as follows:

Failed to open the relay log '// opt/MySQL/log/mysql-bin-slave1.005457' (relay_log_pos 147636219 ).

To the/var/lib/MySQL directory, it is found that there are two files, master.info and relay-log.info. View the master.info file, which is set during the last synchronization. Delete these two files. Run the change master command again.

The MySQL-Mmm component has been installed before, so the mysql-Mmm configuration is not involved in this recovery process. This is the problem to be solved next.