MYSQL master master cross-schema configuration under CENTOS6.3


MySQL master master mutual structure is based on MySQL based on incremental log, different from the structure of master-slave replication.
In the structure of primary primary copy, two servers any data on stock changes are synchronized to another server, the change is based on the SQL statement changes, if you delete the database source file or delete the newly created MYSQL tables to synchronize with the same name is invalid. Two servers from each other, and have to provide services, which has 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: the first before doing this configuration to ensure that the database is currently without any changes, it is recommended that shut down WEB service database changes before synchronizing service
Install and configure MySQL (omitted)
1. primary 1 MySQL configuration: (192.168.7.201)
Remnants of bin logs before deletion
# rm -rf /usr/local/mysql/log/bin*
Modify the MySQL configuration file:
# vi /etc/my.cnf
Add:
—————–
# Replication Master Server 1
# Bin log path
log-bin = /usr/local/mysql/log/bin.log
# The server ID number
server-id = 1
# Ignore MySQL database replication
binlog-ignore-db=mysql
# Each rose 2
auto-increment-increment = 2
# Set the automatic growth of the offset of a field, that is the initial value of 1
auto-increment-offset = 1
————
# mysql -u root -p123456
On the main 1 main 2 Add synchronization account
> grant replication slave on *.* to ‘slave’@’192.168.7.249’ identified by ‘123456’;
1 backup all the database tables, and sent to the main on 2 servers.
# /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 services
# service mysqld restart
# mysql -u root -p123456;
Configuration is connected to primary 2 Server (note that this step must be performed after the main 2 accounts)
> stop slave;
> change master to master_host=’192.168.7.249′,master_user=’slave’,master_password=’123456′;
> start slave;
2. primary 2 MySQL configuration (192.168.7.249)
Remnants of bin logs before deletion
# rm -rf /usr/local/mysql/log/bin*
Modify the MySQL configuration file:
# vi /etc/my.cnf
Add:
——————–
# 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
# Each rose 2
auto-increment-increment = 2
# Set the automatic growth of the offset of a field, that is the initial value of 2
auto-increment-offset = 2
———————
# mysql -u root -p123456
On the main 2 1 Add synchronization account
> grant replication slave on *.* to ‘slave’@’192.168.7.201’ identified by ‘123456’;
Restore the master database to 1 2
#/usr/local/mysql/bin/mysql -u root -p123456 < /root/allbak.sql
Restart services
# service mysqld restart
# mysql -u root -p123456;
Configuration connects to the primary 1 Server
> stop slave;
> change master to master_host=’192.168.7.201′,master_user=’slave’,master_password=’123456′;
> start slave;
Finally landing two servers MySQL background check the major primary backup connection status
# mysql -u root -p123456;
> show slave status\G;
Search for these three lines, as the primary master mutual configuration success
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
When host 1 database add, delete, change data, master 2 will also sync updates.
When the main 2 database, delete, change data, the 1 is also updated.
So that you can cope with keepalived to achieve double-machine hot standby + data synchronization between the database, greatly improving the reliability and safety of MYSQL.