MySQL synchronous backup in CentOS

MySQL supports unidirectional and asynchronous replication. One server acts as the master server during the replication process, and one or more other servers act as slave servers. The master server writes updates to the binary day.

MySQL supports unidirectional and asynchronous replication. One server acts as the master server during the replication process, and one or more other servers act as slave servers. The master server writes updates to the binary day.

Note: MySQL supports unidirectional and asynchronous replication. One server acts as the master server during the replication process, and one or more other servers act as slave servers. The master server writes updates to the binary log file and maintains an index of the log file to track log loops. Location of the last successful update read by the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of the next update. In actual projects, the two hosts distributed in different regions are installed with MySQL databases, and the two servers are both active and standby. The customer requires that when one of the hosts fails, another application that can take over the server requires the data of the two databases to be consistent in real time. Here, the MySQL synchronization function is used to Achieve Synchronous replication between two machines.

The following are examples:

I. Environment Configuration

1. Master Data Server: CentOS release 5.6 mysql-5.0.77-4.el5_5.4 IP: 192.168.4.200

2. From the Data Client: CentOS release 5.69 mysql-server-5.0.95-5.el5_9 IP: 192.168.4.244

2. Add a synchronization account in the master and slave Databases

1. Add an account that can log on to the master database from the slave database.

MySQL>
GRANT all privileges ON *. * TO tongbu @ '192. 168.4.244 'identified BY '20140901 ';
(Note: tongbu is the account used for synchronization, and 123456 is the password of tongbu)

2. Add an account that can log on to the master database in the slave database.

MySQL>

GRANT all privileges ON *. * TO tongbu @ '192. 168.4.200 'identified BY '123 ';

(Note: tongbu is the account used for synchronization, and 123456 is the password of tongbu)

3. Configure database Parameters

1. log on to the master database as the root user and modify the my. cnf file of the master database;

[Root @ jw01 mysql] # vim/etc/my. cnf

[Mysqld] Add the following parameters;

Default-character-set = utf8
Log-bin = MySQL-bin
Relay-log = relay-bin
Relay-log-index = relay-bin-index
Server-id = 1
Master-host = 192.168.4.244 # IP address of the synchronization server
Master-user = tongbu # account used for synchronization
Master-password = 123456 # password used for account Synchronization
Master-port = 3306 # synchronization server port
Master-connect-retry = 30 # synchronization interval, in seconds
Binlog-do-db = zhubao # databases to be synchronized, zhubao, HyDatabase, zentao, and collect;
Binlog-do-db = HyDatabase
Binlog-do-db = zentao
Binlog-do-db = collect
Replicate-do-db = zhubao
Replicate-do-db = HyDatabase
Replicate-do-db = zentao
Replicate-do-db = collect

2. log on to the slave database as the root user and modify the my. cnf file of the slave database;

[Root @ jw02 mysql] # vim/etc/my. cnf

[Mysqld] Add the following parameters;

Default-character-set = utf8
Log-bin = MySQL-bin
Relay-log = relay-bin
Relay-log-index = relay-bin-index
Server-id = 2
Master-host = 192.168.4.200
Master-user = tongbu
Masters-password = 123456
Master-port = 3306
Master-connect-retry = 30
Binlog-do-db = zhubao
Binlog-do-db = HyDatabase
Binlog-do-db = zentao
Binlog-do-db = collect
Replicate-do-db = zhubao
Replicate-do-db = HyDatabase
Replicate-do-db = zentao
Replicate-do-db = collect
Slave-net-timeout = 60 # failed to read log data from the master database
Slave-skip-errors = all # ignore incorrect Parameters