MySQL master-slave backup and master-slave switchover

This article implements two Database Synchronization modes: master-slave mode and mutual backup mode.

Implementation environment:

Two Linux machines

Sera IP: 10.0.0.20.

Serb IP: 10.0.0.234

Demo1: Master/Slave Mode

MASTER: sera slave: Serb

Create the account used for synchronization on Sera and Serb respectively (you can also directly assign access permissions to other accounts without creating a new account)

Use MySQL;

Delete from user;

Grant all privileges on *. *'$ Username'@' % 'Identified'$Password'With grant option;

Flush privileges;

The above is for security, some users are deleted (usually the default user without a password should be deleted)

Stepa:

Sera is configured as master and added to my. CNF on Sera:

Replicate-do-DB =$ Database

Server-id = 1

Specify BINLOG (because BINLOG is used for data synchronization)

After the configuration, restart the database service and use show Master Status to view the master information.

Step B:

Specify

Replicate-do-DB =$ Database

Server-id = 2

Specify BINLOG

Restart the Database Service after configuration. Use show slave status to view the slave information.

(After mysql5.1, my. in CNF, parameters such as master-host have been removed. After the service is started, change master... you can also add these parameters at startup. The following is an example of changing master)

Bin/MySQL-u$ Username-P$ Password-E "Stop slave; change masterMaster_host ='$ Mysqlmasterhost',Master_port =3306, Master_user =$ Username, Master_password =$ Password; Start slave ;"

Mysqlmasterhost is the master's IP address,

Master_port is the master port,

Master_user and master_password are the accounts and passwords used for synchronization.

The master_log_file and master_log_pos parameters also specify the data synchronization location.

You can run change master in MySQL command mode, and then start slave

Note: Because slave configuration information is set, MySQL generates master.info in the database directory.

Therefore, if you want to modify the slave configuration, you must first delete the file. Otherwise, the modified configuration does not take effect.

In this way, you can configure the master and slave nodes. Data is added to the specified database of sera, and data is also added to the database of Serb.

Simulate master-slave switchover:

If sera cannot provide services now, you only need to execute the show Master status record master_log_file and master_log_pos on Serb to stop the Server Load balancer.

Execute change master to Serb on Sera and specify master_log_file and master_log_pos to restore the data stored on Serb during Sera's downtime.

 

View some configuration and command results:

Note that the status of slave_io_running and slave_ SQL _running must be yes.

 

Demo2: Dual-host mutual backup

Dual-host mutual backup only works on both the master and slave machines at the same time,

That is, on Sera, direct its master to Serb, and the Serb's maser to sera,

The configuration is actually the same as the master-slave mode.