MySQL master-master Replication

MySQL master-master Replication

Recently, I have been busy with MySQL for a long time and have not updated the document. I have learned that I have just set up the master copy of MySQL and sent the document by the way. Hope to help you!

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL + KeepAlived + LVS single-point write master/Master synchronization high-availability architecture Experiment

MySQL master/Master synchronization Configuration

MySQL master-slave copy notes in CentOS 6.3

MySQL master-master replication in Linux

I. Environment Description:

IP	Host Name	Database Name	Connect users	Password
192.168.47.179	Mysql01	Retail	Server01	Server01
192.168.47.178	Mysql02	Retail	Server02	Server02

Ii. Setup steps

2.1 create a data connection user

Create a connection user server01 on Mysql01 and connect only through 192.168.47.178;

Script: mysql> grant replication slave on retail. * TO 'server01' @ '192. 168.47.178 'identified by 'server01 ';

Create a connection user server02 on Mysql02 and connect only through 192.168.47.149;

Script: mysql> grant replication slave on retail. * TO 'server02' @ '192. 168.47.179 'identified by 'server01 ';

2.2 modify the mysql parameter file

Modify the parameter file of Mysql01. The default configuration file of MySQL is/etc/my. cnf. Modify/Add the following content.

Server-id = 10

Log-bin = mysql-bin

Replicate-do-db = retail

Auto-increment = 2

Auto-increment-offset = 1
 

After modification, restart msyql to take effect: service mysqld restart

Modify the parameter file of Mysql02. The default configuration file of MySQL is/etc/my. cnf. Modify/Add the following content.

Server-id = 20

Log-bin = mysql-bin

Replicate-do-db = retail

Auto-increment = 2

Auto-increment-offset = 2

Parameter description:

A. When synchronizing data from the server-id database, it is used to identify the server from which the statement was originally written. It must be filled in during the construction of the master or slave database;

B. auto-increment: In database applications, we often use unique numbers to identify records. In MySQL, The AUTO_INCREMENT attribute of the data column can be automatically generated. To avoid duplicate codes generated by the two primary databases, you need to set this value. In this case, the auto-increment value is set

2.3 copy the database of one server to another server

Because the environment is completely new, the two environments are the same and data Initialization is required;

If necessary, perform the following steps to synchronize data from the source database Mysql01:
1. Lock the database and view the status

Mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

 

Mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Mysql-bin.000008 | 107 |

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

1 row in set (0.00 sec)

The source database is in the 107 position of binlog 8;

1. Back up the database

[Root @ mysql01 ~] # Mysqldump -- user = root-p retail>/tmp/retail. SQL

Enter password:

1. Unlock the database

Mysql> unlock tables;

2. Create a retail database on mysql02 and import the database;

Mysql> create database retail;

Mysql-uroot-p retail </tmp/retail. SQL # import the retail Database
 
1. view the status of the mysql02 Database

 

Mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Mysql-bin.000009 | 107 |

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

1 row in set (0.00 sec)

The backup file is located at 107 of binlog 9;

2.4 conduct communication connections between the master and the master;

A. Confirm the user and binlog on mysql01.

Mysql> change master to MASTER_HOST = '192. 168.47.178 ', MASTER_USER = 'server02', MASTER_PASSWORD = 'server02', MASTER_LOG_FILE = 'mysql-bin.000008', MASTER_LOG_POS = 192;

B. Confirm the user and binlog on mysql02.

Mysql> change master to MASTER_HOST = '192. 168.47.179 ', MASTER_USER = 'server01', MASTER_PASSWORD = 'server01', MASTER_LOG_FILE = 'mysql-bin.000009', MASTER_LOG_POS = 192;

For more details, please continue to read the highlights on the next page:

1 2 Next Page