Mysql master-slave backup in linux

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:

[Mysqld]
Log-bin = mysql-bin
Server-id = 1
Binlog-do-db = extmail
Binlog-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
-> MASTER_HOST = '2017. 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
Show slave status;
Show processlist;
When the following content is displayed, the Master/Slave server is configured successfully.
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes


6. Manual data synchronization automatically creates a database. You need to set the ylmf permission.
Load data from master;