MySql5.6 how to configure synchronous database backup on a Linux Server

The project has been completed, and maintenance will be performed later. of course, database maintenance will be included. mySql has a function that can be used for Database Synchronization and backup, so it is ready to do so. it took some time to finally get it out. record backup: Linux Database Backup Settings: (set for the first time) 1. database version: Two Server database versions

The project has been completed, and maintenance will be performed later. of course, database maintenance will be included. mySql has a function that can be used for Database Synchronization and backup, so it is ready to do so. it took some time to finally get it out. record backup: Linux Database Backup Settings: (set for the first time) 1. database version: Two Server database versions

The project has been completed, and maintenance will be performed later. Of course, database maintenance will be included.

MySql has a function that can be used for Database Synchronization and backup, so it is ready to do so.

It took some time to finally get it out. Record backup:

Linux Database Backup Settings: (set for the first time) 1. database version

It is recommended that the database versions of the two servers be consistent to avoid unpredictable errors caused by version issues.

2. Set the backup account name and password

After the database is installed, set an account dedicated for backup on both servers and authorize it (it is best to grant all permissions). The passwords of the two accounts are the same. Here, the account name used for backup is added to both the master and slave databases. The account name is backup and the password is 123123.

3. Copy data before Synchronization

To back up Data from the primary database to the backup database, you can use the navicat Data Transfer. You need to establish two connections to create a new database on the primary database and the slave database respectively, the same name as the primary database to be backed up. Make sure that the encoding rules are consistent. Then, select the primary database from the primary data connection and use the data function to transmit the data to the backup database, maintain data consistency between the two databases.

You can also use other methods to ensure data consistency between the master and slave databases before backup.

4. master database settings

Modify the configuration file of the primary database. In Linux

Under [mysqld], add

Server-id = 1 # This ID must be unique.

Log-bin = mysql-bin # record logs

Binlog-do-db = productInfo # Name of the database that records logs, that is, the database to be backed up

After modifying the configuration of the primary database, save, exit, and restart the primary database service.

Then execute an SQL command in the primary database (there are functions in the database for specific functions)

Set globallog_bin_trust_function_creators = 1, so use SQL to execute)

5. Set slave database 5.1 to modify slave Database Configuration File

In Linux

Under [mysqld], add

Server-id = 2 # slave Database ID

Relay-log = mysqld-relay-bin # log files from the database

Replicate-do-db = productInfo # Name of the database to be backed up

After modifying the configuration file, save, exit, and restart the slave database service.

5.2 set commands from database SQL

The following operations must be completed using SQL commands.

Run the SQL command as the root user of MySql:

Stop slave;

Change master to MASTER_HOST = '192. 168.1.200 ', MASTER_USER = 'backup', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000001 ', MASTER_LOG_POS = 98;

Start slave;

Log out of mysql and check whether the Server Load balancer instance was started successfully in linux.

Under the/usr/local/mysql/data DIRECTORY *. err: run the cat command to view the Error Log File. Check whether the error message is displayed in the last few lines based on the time when slave was started.

Because the MASTER_LOG_POS = 98 attribute indicates the starting position of the backup from the database (read from the master database log), it is generally not very accurate for the first time. You need to check the error message and modify it.

The error log is similar to the following: (for reference)

080922 10:29:01 [Note] Slave SQL threadinitialized, starting replication in log 'mysql-bin.000778 'at position2874108, relay log'./relay-bin.003287' position: 2874245

080922 10:29:01 [Note] Slave I/O thread: connected to master 'replication @ 192.168.0.10: 100', replication started in log 'mysql-bin.000778 'at position 3306

080922 10:29:01 [ERROR] Error reading packetfrom server: Client requested master to start replication from impossibleposition (server_errno = 1236)

080922 10:29:01 [ERROR] Got fatal error1236: 'client requested master to start replication from impossible position' from master when reading data from binary log

The starting position above is 98. If it is not correct, follow the error prompt to modify it (it is best that the master database has no one to operate, so that the location of the log record of the master database will not change)

Modification method:

Run the SQL command:

Stop slave;

Change master TOMASTER_LOG_FILE = 'mysql-bin.000001 ', MASTER_LOG_POS = pos prompted from the error log;

Start slave;

Such steps may need to be repeated more than once.

When you view *. err, the last recorded log prompts are note and wrning without error, indicating that the synchronization from the database to the master database is successful.

6. Check after setting

Perform insert update delete operations on the primary database and check whether the data in the slave database has changed.

Linux database restart settings:

When the primary database needs to be restarted, perform the following operations after the restart.

1. master database settings.

The data in the master database is backed up to the slave database using the data transfer function of navicat.

View the latest log file, the first is the mysql-bin.000001, the second is the mysql-bin.000002, and so on.

View the master database IP address 192.168.1.200 in the/usr/local/mysql/data directory.

After learning about the latest log file, write it down (to modify the slave backup settings of the database ).

Then execute an SQL command in the primary database.

Set global log_bin_trust_function_creators = 1, so use SQL to execute)

2. Slave database settings

Mysql enters with the root user, execute the SQL command :( assuming that the latest log file of the master database is a mysql-bin.000005)

Stop slave;

Change master to MASTER_LOG_FILE = 'mysql-bin.000005 ', MASTER_LOG_POS = 98;

Start slave;

Then, refer to the method for setting the slave from the database for the first time to view *. err: Check whether the synchronization is started successfully. If the synchronization is not successful, repeat it several times. Modify the MASTER_LOG_POS value based on the error log prompt.

3. Remarks:

Master-slave synchronization error adjustment can refer to http://www.linuxidc.com/Linux/2012-02/54729.htm