MYSQL master slave database synchronization backup configuration method

The following steps are very detailed, please see below for details.

1. Prepare

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 for master slave servers is that the MYSQL version should be the same, and if not, at least the MYSQL version of the slave server must be higher than the MYSQL version of the master server

2. Configure the master server

1. Log on to Master server and edit ES24en.cnf

#vim /etc/my.cnf

Add the following in [mysqld] :

log-bin=mysql-bin
server-id=1
binlog-do-db=extmail
binlog-ignore-db=mysql,test

Explanation: the ES38en-ES39en item is required for the Master server to log base 2.
server-id =master_id where master_id must be 1 positive integer value between 1 and 232, 1;
binlog-do-db =database is the database to log;
binlog-ignore-db is the database name that should not be logged. Multiple databases are separated by commas (,);

2. Add the authorized account to access the master server from the slave server from the master server by following the 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 or hostname' IDENTIFIED BY 'password ';

3. Restart Mysql

4. Backup master database data

# mysqldump --master-data extmail > extmail_backup_20071120.sql

To add the -- ES96en-ES97en option, back up the master server, then import the slave server.

5. Check 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 slave server

1. Edit my. cnf

# vim /etc/my.cnf

Add the following in [mysqld] :

server-id=2
master-host=192.0.0.1
master-port=3306
master-user=abc
master-password=123
master-connect-retry=60

Explanation:

server-id for slave servers cannot be the same as master, nor can ES142en-ES143en be the same between multiple slave servers.
master-host is the hostname or IP address of the master server
master-user and ES152en-ES153en are the username and password we built on master
master-connect-retry is the time difference between reconnecting if the slave server discovers that the master server is down

2. Import the database backed up from the master database server into the slave server, that is, es161EN_backup_200711120. sql

# mysqladmin create extmail
# mysql extmail < extmail_backup_20071120.sql

Restart the mysql server

4. Stop slave service and set various parameters of the primary 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. Check the status of the master and slave servers

mysql > show processlist;