CENTOS 6.3 MySQL primary host mutual Provisioning architecture configuration

The MySQL primary and standby architecture is based on the MySQL incremental log and differs from the master-slave replication structure.

In the primary replication structure, changes to the data inventory on either of the two servers are synchronized to another server, which is based on changes in the SQL statement, which is not valid if you delete the system database source file or delete the new MySQL table implementation synchronization. This allows both servers to be mutually dominant and to provide services outside, which is better performance than using master-slave replication.

Next I will use two of the same environment to achieve this effect:

System Environment: centos6.3

Database: mysql-5.6.10

Note: Before you make this configuration, make sure that there is no current changes to the database, and recommend that you turn off the Web services before synchronization to affect database changes

Install configuration MySQL (abbreviated)

1. Main 1 MySQL configuration: (192.168.7.201)

Delete a previously left bin log

# rm-rf/usr/local/mysql/log/bin*

To modify the MySQL configuration file:

# VI/ETC/MY.CNF

Add to:

-----------------

# Replication Master Server 1

# Bin Log Path

Log-bin =/usr/local/mysql/log/bin.log

# Server ID Number

Server-id = 1

# Ignore MySQL database replication

Binlog-ignore-db=mysql

# grow 2 each time

Auto-increment-increment = 2

# Set the offset of the automatically growing field, that is, the initial value is 1

Auto-increment-offset = 1

------------

# mysql-u root-p123456

Add sync account number on main 1 for main 2

> Grant replication slave on *.* to ' slave ' @ ' 192.168.7.249 ' identified by ' 123456 ';

Back up the Primary 1 database table and send it to the main 2 server.

#/usr/local/mysql/bin/mysqldump-u root-p123456--opt--skip-lock-tables--flush-logs--all-database >/root/allbak . sql

# CD ~

# SCP Allbak.sql Root@192.168.7.249:/root

Restart Service

# Service Mysqld Restart

# mysql-u root-p123456;

Configure the connection to the primary 2 server (note that this step must be performed after the main 2 account is added)

> Stop slave;

> Change Master to master_host= ' 192.168.7.249 ', master_user= ' slave ', master_password= ' 123456 ';

> Start slave;

2. Master 2 mysql configuration (192.168.7.249)

Delete a previously left bin log

# rm-rf/usr/local/mysql/log/bin*

To modify the MySQL configuration file:

# VI/ETC/MY.CNF

Add to:

--------------------

# Replication Master Server 2

# Bin Log Path

Log-bin =/usr/local/mysql/log/bin.log

server-id=2

# Ignore MySQL database replication

Replicate-ignore-db=mysql

# grow 2 each time

Auto-increment-increment = 2

# Set the offset of the automatically growing field, that is, the initial value is 2

Auto-increment-offset = 2

---------------------

# mysql-u root-p123456

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/OS/Linux/