MySQL master-slave backup and common problems processing

1.mysql master-Slave backup Fundamentals

MySQL supports one-way, asynchronous replication, during which one server acts as the primary server, and one or more other servers act as slave servers. MySQL replication tracks all changes to the database (updates, deletions, and so on) based on the primary server in the binary log. Therefore, to replicate, you must enable binary logging on the primary server. Each slave server receives a binary log from the primary server that the primary server has logged and gets the log information updated. By setting Master binlog it on, leaving it open, Slave reading through a I/O thread from above, Master binlog then transferring it to Slave the trunk log, and then using the SQL thread to read the trunk log and apply it to its own database, Thus realize the master-slave data synchronization function.

Premise: MySQL database master and slave database version is the best, the small version code is different also can, for example: 5.7.20 Backup to 5.7.11.

2. Primary Database Migration

Before the database master-slave backup, the first to determine the specific database that needs to be backed up, if the database is a new database, only the table structure, you can export the primary database SQL script, import into the database to execute, so that the primary database and from the structure of the database.

If the database already exists storage information, you need to lock the primary database, temporarily do not let any program operate the database, export the main database SQL script, execute SQL script from the database, ensure that the master-slave database structure, storage information consistent before the backup. You can also use database management tools such as Navicat premium to do data transfer operations directly.

Master-Slave backup operation under 3.windows environment <1> master database master configuration

1. Open the basic configuration file of the MySQL database, you can view the MySQL enabled profile information in the service, if the configuration file is not found on the server, set up the server to show the hidden files. Refer to the following:

2. Open the My.ini configuration file, set the main database parameter information, the main setting field is server-id,log_bin,binlog_do_db, other fields reference parameter define self-setting, the relevant parameters in the configuration file are defined as follows:

Parameters	meaning
server-id	database unique ID, a set of master from which this identification number cannot be duplicated. Where 1 represents the primary database (source) 2 for the secondary database (destination)
log_bin
binlog_do_db
binlog_ignore_db
max_binlog_size
binlog_cache_size	log cache size
binlog-do-db
binlog-ignore-db
expire_logs_day
binlog_format	bin-log log file format, set to mixed to prevent primary key duplication.

3. The primary server creates an account that allows data to be synchronized from the server:

4. Restart the MySQL service, view the master status, view the command: Show master, status;

<2> slave configuration from Database

1. Open the My.ini configuration from the server, set the parameter information from the database, set the field information server-id,binlog_do_db. Some important options are recommended on the slave library, such as Read Only, Relay_log_recovery, Sync_master_info, Sync_relay_log_info, Sync_relay_ Log these important options are turned on.

2. Stop slave Service, instruction is: stop slave;

3. Configure the slave server, open the synchronization mode, the key parameters are as follows:

When setting the synchronization mode, you need to ensure that the master/slave server is connected to the same network, the file log name is configured, and the index location is consistent with the information of the primary server query.

4. Start the slave service, the command is: start slave;

5. Restart the MySQL service, view the synchronization status from the database, view the command as: Show slave status, when the Slave_io_running:yes,slave_sql_running:yes of the query indicates that the synchronization status is normal, the master-slave configuration is successful.

Master-Slave backup operation under 4.linux environment

By analyzing the principle of MySQL master-slave backup, which itself is based on the primary database of binary log backup, so, the master-slave backup itself is affected by the operating system is small, in the Linux environment under the configuration of master-slave backup and under Windows configuration Master-Slave backup operation procedures are the same, modify parameters are the same. The only difference is that the Linux version of the database configuration file is my.cnf, generally under the/etc/my.cnf, modify the master-slave data configuration file information, restart the MySQL database service, you can complete the MySQL database master-slave backup.

I also personally tested, the Windows version of the MySQL database as the main database, the Linux version of the MySQL database as a database, or exchange, can be set up master-slave backup.

5.mysql master-slave backup common errors and solutions

After the first successful configuration of the MySQL database master-slave backup, I think since this can be all worry-free. But before long, through the query command to view the synchronization status from the server, found that the error, in the Internet to find a solution after the solution. There are other types of errors that will occur soon. In short, feel very tricky, also feel that the master-slave backup is not reliable, need people often to check the synchronization status, once the error occurred, the need for timely human processing. This typically occurs in the days when the database was initially synchronized, and in the case of a master server, or from a server that has been down for a long time. Common errors and solutions are as follows:

• [ERROR] Slave I/o: Got fatal error 1236 from master If reading data from binary log: ' Client requested Master to start Replicati On from impossible position ', error_code:1236

Solution: 1236, this error usually occurs when the master-slave server loses connection, and there is a situation of downtime. Common workaround, Requery the status of the master server, obtain a new position location, and reset the synchronization information from the server.

• last_errno:1032, Last_error:could not execute Update_rows event on table xuanzhi.test; Can ' t find record in ' Test ', error_code:1032; Handler error ha_err_key_not_found; The event ' s master log mysql

Solution: Appears 1032, indicating that a data record is missing from the database, and the master database modifies the record from the database in the wrong way. The solution is to use the database management tool directly, data transmission mode to deal with the specific exception of the table, to ensure that the main data and database corresponding to the error data table structure information.

Solution: 1062 appears, representing primary key conflicts, and a primary key information record that is not on the primary database appears above the database. The solution is to delete the exception data from the database directly, or to use the data transfer mode to handle the specific exception.

• last_errno:1594,last_errno:1593

Solution: Relay Log errors, typically caused by server outages, solutions and error 12,361. In mSQL 5.5 or above, a parameter Relay_log_recovery=1 can be added to the slave configuration file my.cnf.

Verify that the data above the master-slave server is exactly the same and can be manipulated by the tool Pt-table-checksum. please refer to this blog post for specific instructions.

MySQL master-slave backup and common problems processing