MySQL master-slave architecture configuration in centos

The MySQL master-slave structure is based on mysql incremental logs, which is different from the master-slave replication structure. In the master-master replication structure, data inventory occurs on any of the two servers.

The MySQL master-slave structure is based on mysql incremental logs, which is different from the master-slave replication structure. In the master-master replication structure, data inventory occurs on any of the two servers.

The MySQL master-slave structure is based on mysql incremental logs, which is different from the master-slave replication structure.

In the master-master replication structure, any changes to the data inventory on one of the two servers will be synchronized to the other server. This change is based on changes in SQL statements, if you delete the source file of the system database or create a new MYSQL table with the same name after deletion, the synchronization is invalid. In this way, the two servers are both master-slave and can provide external services, which has better performance than using master-slave replication.

Next, I will use two identical environments to achieve this effect:

System Environment: CentOS6.3

Database: mysql-5.6.10

Install and configure mysql (omitted)

1. Master 1 mysql configuration: (192.168.7.201)

Delete existing bin logs

# 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

# Server ID

Server-id = 1

# Ignore mysql Database Replication

Binlog-ignore-db = mysql

#2 increase each time

Auto-increment = 2

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

Auto-increment-offset = 1

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

# Mysql-u root-p123456

Add a synchronization account for Master 2 on Master 1

> Grant replication slave on *. * to 'slave '@ '192. 168.7.249' identified by '123 ';

Back up all the database tables in master 1 and send them to master 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 service

# Service mysqld restart

# Mysql-u root-p123456;

Configure to connect to the master 2 server (note that this step must be executed after the master 2 account is added)

> Stop slave;

> Change master to master_host = '192. 168.7.249 ', master_user = 'slave', master_password = '2016 ';

> Start slave;

2. Master 2 mysql configuration (192.168.7.249)

Delete existing bin logs

# 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

#2 increase each time

Auto-increment = 2

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

Auto-increment-offset = 2

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

# Mysql-u root-p123456

Add a synchronization account for Master 1 on Master 2

> Grant replication slave on *. * to 'slave '@ '192. 168.7.201 'identified by '123 ';

Restore the database of Master 1 to master 2

#/Usr/local/mysql/bin/mysql-u root-p123456 </root/allbak. SQL

Restart service

# Service mysqld restart

# Mysql-u root-p123456;

Configure connection to master 1 Server

> Stop slave;

> Change master to master_host = '192. 168.7.201 ', master_user = 'slave', master_password = '2016 ';

> Start slave;

Finally, log on to the mysql backend of the two servers to view the connection status of the master backup.

# Mysql-u root-p123456;

> Show slave status \ G;

Search for the three rows. The configuration of the master and slave nodes is successful as follows:

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

When the database of Master 1 is added, deleted, or changed, Master 2 is also updated synchronously.

When the database of Master 2 is added, deleted, or changed, Master 1 is also updated synchronously.

In this way, we can use keepalived to implement dual-machine Hot Standby + Data Synchronization and mutual backup of the database, greatly improving the reliability and security of MYSQL.

Keepalived + MySQL mutual backup