The example of mysql master-slave backup and master-slave switch, bitsCN.com, implements synchronization between two Mysql databases, namely the master-slave mode and the 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)
[Html]View plaincopy
- Use mysql;
-
- Delete from user;
-
- Grant all privileges on *. * to '$ username' @' % 'identified by' $ 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:
[Html]View plaincopy
- Replicate-do-db = $ database
-
- Server-id = 1
Specify binlog (because binlog is used for data synchronization)
Restart the database service after Configuration. use show master status to view the Master information.
Step B:
Specify
[Html]View plaincopy
- 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 master tomaster_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 Mysql 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,
Configuration andMysql master/slave modeIs the same.
BitsCN.com