MYSQL master-slave database synchronous backup configuration method _ MySQL

This article mainly introduces the relevant information. For more information, see the following steps.

I. preparation

Test with two servers:

Master Server: 192.0.0.1/Linux/MYSQL 4.1.12
Slave Server: 192.0.0.2/Linux/MYSQL 4.1.18

The principle of master slave server is that the MYSQL version must be the same. if not, the MYSQL version of the slave server must be higher than the MYSQL version of the master server.

II. configure the master server

1. log on to the Master server and edit my. cnf.

# Vim/etc/my. cnf

Add the following content in the [mysqld] section:

log-bin=mysql-binserver-id=1binlog-do-db=extmailbinlog-ignore-db=mysql,test

Explanation: The log-bin item is required for the Master server to record binary logs;
Server-id = master_id where master_id must be a positive integer between 1 and 232-1;
Binlog-do-db = database is the database to record logs;
Binlog-ignore-db is the database name that does not record logs. multiple databases are separated by commas;

2. from the master server, add an account with permissions to access the master server from the slave server. you can see the following command:

Mysql> grant replication slave on *.*
-> To 'ABC' @ '192. 0.0.2 'identified by '123 ';

Format: mysql> grant replication slave on *.*
-> TO 'account' @ 'slave server IP address or hostname 'identified BY 'password ';

3. restart Mysql

4. back up master database data

# Mysqldump -- master-data extmail> extmail_backup_2007111_ SQL

To add the -- master-data option, back up the data of the master server and import it to the slave server later.

5. check the Master status

mysql> show master status;+------------------+----------+--------------+------------------+| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 |    79 | extmail   | mysql,test    |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

3. configure the slave server

1. edit my. cnf

# Vim/etc/my. cnf

Add the following content in the [mysqld] section:

Server-id = 2
Master-host = 192.0.0.1
Master-port = 3306
Master-user = abc
Masters-password = 123
Master-connect-retry = 60

Explanation:

The server-id of the slave server cannot be the same as that of the master server, and the server-id cannot be the same between multiple slave servers.
The master-host is the host name or IP address of the master server.
Master-user and master-password are the usernames and passwords created on the master.
Master-connect-retry indicates the time difference between the reconnection if the master server is disconnected from the slave server.

2. import the database backed up from the master database server to the slave server, that is, our previous extmail_backup_2007111_ SQL

# Mysqladmin create extmail
# Mysql extmail <extmail_backup_2007111_ SQL

3. restart the mysql server

4. stop the slave service and set parameters of the master server.

mysql> slave stop;mysql> change master to-> MASTER_HOST='192.0.0.1',-> MASTER_USER='abc',-> MASTER_PASSWORD='123',-> MASTER_LOG_FILE='mysql-bin.000002',-> MASTER_LOG_POS=79;mysql> slave start;

5. view the status of the master/slave server

Mysql> show processlist;