MySQL master-slave synchronization configuration records

Source: Internet
Author: User

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.