MySQL master-slave synchronization configuration records
/**************** MySQL master-slave synchronization configuration records (5.1.17 and later versions) ********************/
Create a mybackup user on the master database. The user will synchronize data in the master and slave databases.
[Root @ test ~] # Mysql-u root-p
Mysql> grant replication slave on *. * to 'mybackup' @ '192. 168.20.ed' identified by 'work123 ';
Modify the configuration file of the master database
[Root @ test ~] # Vi/etc/my. cnf
Server-id = 1 # specify the database server ID
Log_bin = mysql-bin # enable the binary log system
Binlog-do-db = mydb1 # specify the database to be synchronized
Binlog-do-db = mydb2
Binlog-ignore-db = mysql # specify databases that do not need to be synchronized
Restart the master database to view configuration information.
[Root @ test ~] #/Etc/init. d/mysql restart
[Root @ test ~] # Mysql-u root-p
Mysql> show master status;
File Position Binlog_Do_DB Binlog_Ignore_DB
............
Note that the values of File and Position are the information required for the backup database configuration.
Modify the configuration file of the slave Database
[Root @ test ~] # Vi/etc/my. cnf
Server-id = 2 # backup database server ID
Log_bin = mysql-bin # enable the binary log system
Replicate-do-db = mydb1 # Set the database for synchronization
Replicate-do-db = mydb2
Replicate-ignore-db = mysql # Set a database that is not synchronized
Restart the slave database to make the configuration take effect.
[Root @ test ~] #/Etc/init. d/mysql restart
Configure database information
[Root @ test ~] # Mysql-u root-p
Mysql> stop slave; # disable the synchronization process
Mysql> change master to master_host = '192. 168.20.163 ', master_user = 'mysync', master_password = 'work123', master_log_file = 'file', master_log_pos = Position; # configure synchronization Information
Mysql> slave start; # enable the synchronization process
Copy the database to be synchronized to the slave Database
If necessary, change the database to read-only mode before backing up the master database to prevent data writing during the backup and unlock the database after the backup is complete.
Mysql> flush tables with read lock;
Mysql> unlock tables;
Export the two databases to be synchronized and copy them to the slave database server.
[Root @ test ~] # Mysqldump-u root-p mydb1>/home/mydb1. SQL
[Root @ test ~] # Mysqldump-u root-p mydb2>/home/mydb2. SQL
[Root @ test ~] # Scp/home/mydb1. SQL 192.168.20.133:/home/
[Root @ test ~] # Scp/home/mydb2. SQL 192.168.20.133:/home/
Create a database on the slave database and import data
[Root @ test ~] # Create database mydb1;
[Root @ test ~] # Create database mydb2;
[Root @ test ~] # Use mydb1;
[Root @ test ~] # Source/home/mydb1. SQL
[Root @ test ~] # Use mydb2;
[Root @ test ~] # Source/home/mydb2. SQL
Verify
View synchronization information on the slave Database
Mysql> show slave status \ G
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
The two parameters "Yes" are regarded as successful.
You also need to perform data operations on the master database to verify the synchronization of the slave database.
-------------------------------------- Split line --------------------------------------
Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization
Production Environment MySQL master/Master synchronization primary key conflict handling
MySQL Master/Slave failure error Got fatal error 1236
MySQL master-slave replication, implemented on a single server
Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization
MySQL 5.5 master-slave bidirectional Synchronization
MySQL 5.5 master-slave synchronization troubleshooting
MySQL master-slave replication asynchronous semi-sync instance
-------------------------------------- Split line --------------------------------------
This article permanently updates the link address: