Detailed operation process of MySQL master/Master Synchronization

Source: Internet
Author: User
Tags mysql login

Detailed operation process of MySQL master/Master Synchronization

This document is recorded when MySQL dual-master is deployed online at the company. For security purposes, IP addresses are changed to intranet IP addresses.

Version:

# Mysql-V

Mysql Ver 14.14 Distrib 5.5.37, fordebian-linux-gnu (x86_64) using readline 6.2

 

1. Enable the bin-log function for the master database and configure the server-id

Modify the my. cf configuration file, enable the bin-log function, and configure the server-id.

# Cat/etc/mysql/my. cnf

[Myqld]

Server-id = 1

Log_bin =/var/log/mysql/

Slave-net-timeout = 60


# The default value of salve-net-timeout is 3600 seconds. The shortening time is to prevent the illusion of Double YES.


# (In fact I have met, reference address: http://www.cnblogs.com/billyxp/p/3470376.html)

 

 

If you want to specify the databases to be synchronized or not to be synchronized, you can use the following parameters:

# Binlog-do-db = osyunweidb # Name of the database to be synchronized. If multiple databases exist, repeat this parameter. Each database has one row.
# Binlog-ignore-db = mysql # Do not synchronize the mysql System Database

 

2. Check whether bin-log and server-id are enabled:

View the command show variables like 'Log _ bin'; show variables like 'server _ id ';

Mysql> show variables like 'Log _ bin ';

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Log_bin | ON |

+ --------------- + ------- +

1 rowin set (0.00 sec)

Mysql> show variables like 'server _ id ';

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Server_id | 1 |

+ --------------- + ------- +

1 rowin set (0.00 sec)

 

3. Create a copy authorized user

Mysql> grant replication slave on *. * to replication @ '%' identified by '000000'; # authorize this user to copy all tables.

Mysql> flush privileges; # refresh Permissions

 

4. Lock table, record the log-binfile name and location

Mysql> flush tables with read lock; # lock all tables. data cannot be written to the database.

QueryOK, 0 rows affected( 0.05 sec)

 

Mysql> show master status; # view the latest bin-log file and location

+ ------------------------ + ------------ + --------------------- + ----------------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------------ + ------------ + --------------------- + ----------------------- +

| Mysql-bin.000001 | 26314 |

+ ------------------------ + ------------ + --------------------- + ----------------------- +

1row in set (0.00 sec)

 

5. Lock Table Status full backup mysql Data

Because you exit the current mysql login window, the table lock function becomes invalid. You need to clone a session for full backup.

# Mysqldump-uroot-p-A-B>/tmp/mysql_bak_2014_10_30. SQL .gz

 

Check the backup data size to confirm that the backup is successful.

# Ls-l/tmp/mysql_bak_2014_10_30. SQL .gz

-Rw-r -- 1 root 339222 October 30 11: 01/tmp/mysql_bak_2014_10_30. SQL .gz

 

6. Unlock the table

Mysql> unlock tables;

Or quit.

 

7. Enable the bin-log function from the slave database and configure the server-id

After the bin-log function is enabled from the slave database, the synchronization configuration will be performed on the master database.

# Cat/etc/mysql/my. cnf

[Myqld]

Server-id = 2

Log_bin =/var/log/mysql/mysql-bin.log

Slave-net-timeout = 60


# The default value of salve-net-timeout is 3600 seconds. The shortening time is to prevent the illusion of Double YES.


# (Reference: http://www.cnblogs.com/billyxp/p/3470376.html)

 

 

#/Etc/init. d/mysqlrestart

 

8. Check whether the slave database bin-log and server-id are enabled.

View the command show variables like 'Log _ bin'; show variables like 'server _ id ';

Mysql> show variables like 'Log _ bin ';

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Log_bin | ON |

+ --------------- + ------- +

1row in set (0.00 sec)

Mysql> show variables like 'server _ id ';

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Server_id | 2 |

+ --------------- + ------- +

1row in set (0.00 sec)

 

9. Import Full backup data from the master database

Extract data from the slave database.

# Gzip-d mysql_bak_2014_10_30. SQL .gz

Log onto mysql to import data

Mysql> source/root/mysql_bak_2014_10_30. SQL

 

10. Record the bin-log information of the slave Database

When importing full backup data from the slave database, the content of the master database is the same as that of the slave database, but the bin-log location is not necessarily the same.

Mysql> show master status; # view the latest bin-log file and location

+ ------------------------ + ------------ + --------------------- + ----------------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------------ + ------------ + --------------------- + ----------------------- +

| Mysql-bin.000003 | 2328055 |

+ ------------------------ + ------------ + --------------------- + ----------------------- +

1row in set (0.00 sec)

 

11. Synchronize master database settings from the slave Database

Here, the binlog file and Location status are the Location status viewed by the master database in show master status when the master database locks the table in Step 4.

 

CHANGE MASTER

MASTER_HOST = '10. 0.0.2 ',

MASTER_PORT = 8306,

MASTER_USER = 'replicase ',

MASTER_PASSWORD = '000000 ',

MASTER_LOG_FILE = 'mysql-bin.000001 ',

MASTER_LOG_POS = 26314;

 

12. enable synchronization from the slave database and check whether the synchronization is successful.

Use start slave to enable the synchronization function. Use show slave status \ G to check whether the synchronization is successful.

Mysql> start slave;

QueryOK, 0 rows affected( 0.00 sec)

Mysql> show slave status \ G # \ G does not output by table

* *************************** 1. row ***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 10.0.0.2

Master_User: replication

Master_Port: 8306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 136270

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 72697

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 98758

Relay_Log_Space: 110366

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 622 # Check the master-slave synchronization latency. If the latency is high, optimization may be required.

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_ SQL _Errno: 0

Last_ SQL _Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1row in set (0.00 sec)

 

# The SQL thread and I/O thread are both YES, and the slave configuration is successful.

 

13. Configure synchronization slave database for the master database

Since the slave database is full-backup, the replication account configured on the master database is also imported, so there is no need to authorize the replication user on the slave database.

The binlog file and Location status on the slave database are the Location status Viewed from the show master status when the slave database is just imported.

 

CHANGEMASTER

MASTER_HOST = '2017. 16.0.2 ',

MASTER_PORT = 3306,

MASTER_USER = 'replicase ',

MASTER_PASSWORD = '000000 ',

MASTER_LOG_FILE = 'mysql-bin.000003 ',

MASTER_LOG_POS = 2328055;

# Modify the relevant information and paste the configuration directly in mysql.

 

 

14. enable synchronization and check whether the synchronization is successful.

Use start slave to enable the synchronization function. Use show slave status \ G to check whether the synchronization is successful.

Mysql> start slave;

QueryOK, 0 rows affected( 0.00 sec)

 

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 172.16.0.2

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 107

Relay_Log_File: mysqld-relay-bin.000006

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 107

Relay_Log_Space: 556

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_ SQL _Errno: 0

Last_ SQL _Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2

1row in set (0.00 sec)

 

# Both the IO thread and the SQL thread are normal.

 

15. Mutual master-slave Test

Create a database for each of the two mysql databases to check whether the two databases can be synchronized.

Run create database test01 on the master database;

Execute create database test02 from the database;

Run show databases on the two databases to check whether there are both test01 and test02 tables.

My dual-master test is successful.

Production Environment MySQL master/Master synchronization primary key conflict handling

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

MySQL master/Master synchronization Configuration

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.