In Linux, specify mysql database data to configure the master-master synchronization instance _ MySQL

Source: Internet
Author: User
Configure the master-master synchronization instance bitsCN.com for mysql database data in Linux

I. concept:

① Database synchronization (master-slave synchronization-write data to the slave server while writing data to the master database)
② Database synchronization (Master-master synchronization-two database servers write data to each other)

II. example

Master/master database synchronization server configuration
Database Server (A) master database IP address: 192.168.1.134
Database Server (B) master database IP address: 192.168.1.138
The user name for synchronization between the two servers is: bravedu password: brave123

I. primary database operation settings ():

① Create a user IP address (non-local IP address) that can be connected with the synchronization user name)

Grant replication slave on *. * to 'bravedu' @ '2017. 192.% 'identified by 'brave123 ';
Flush privileges;

② Change the mysql configuration file

[Mysqld]
Server-id = 1
Log-bin =/www/mysql/binlog (the path must be based on your own installation settings)
Binlog-do-db = dbname (name of the database to be synchronized)
Binlog-ignore-db = mysql

# Synchronization between the master and slave databases

Log-slave-updates
Sync_binlog = 1
Auto_increment_offset = 1
Auto_increment_increment = 2
Replicate-do-db = dbname
Replicate-ignore-db = mysql, information_schema

Restart the mysql server

③ Check the master database synchronization status IP: ***. 134

Mysql> flush tables with read lock;
Mysql> show master status/G
* *************************** 1. row ***************************
File: mysql-bin.000001 (note to use this when setting the slave server)
Position: 106 (use this parameter when setting the slave server)
Binlog_Do_DB: dbname
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

Mysql> unlock tables;
* ***** The master server has been set up to the current location *******


II. Slave Database operation settings (B ):

① Create a synchronization user name

Grant replication slave on *. * to 'bravedu' @ '2017. 192.% 'identified by 'brave123 ';
Flush privileges;

② Change the mysql configuration file

[Mysqld]
Server-id = 2
Log-bin =/www/mysql/binlog (the path must be based on your own installation settings)
Binlog-do-db = dbname (name of the database to be synchronized)
Binlog-ignore-db = mysql, information_schema
# Different places from master-slave synchronization
Binlog-do-db = dbname
Binlog-ignore-db = mysql
Log-slave-updates
Sync_binlog = 1
Auto_increment_offset = 2
Auto_increment_increment = 2

Restart the mysql server

View the master database synchronization status IP: ***. 138

Mysql> flush tables with read lock;
Mysql> show master status/G
* *************************** 1. row ***************************
File: mysql-bin.000005 (note to use this when setting the slave server)
Position: 106 (use this parameter when setting the slave server)
Binlog_Do_DB: dbname
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
Mysql> unlock tables;

③ Specify the master database server synchronization command
Note: IP addresses are the IP addresses, user names, passwords, log_file, and log_post servers of the master server.
This operation may need to unlock the table, stop the database, and start the table after running.

Mysql> stop slave;
# Set 192.168.1.138 database server configuration, so the host configuration file information is 134 information
Mysql> change master to master_host = '192. 168.1.134 ', master_user = 'bravedu', master_password = 'brave123', master_log_file = 'MySQL-bin.000005 ', master_log_pos = 192;
# Set 192.168.1.134 database server configuration, so the host and other configuration file information is 134 information
Mysql> change master to master_host = '192. 168.1.138 ', master_user = 'bravedu', master_password = 'brave123', master_log_file = 'MySQL-bin.000001 ', master_log_pos = 192;
Mysql> start slave;
Mysql> unlock tables;

④ Checking the synchronization status of the primary database will result in a lot of information, but you can only check the two statuses if they are both yes.

Mysql> show slave status/G;
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

So far, the master database synchronization is complete.

BitsCN.com

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.