Mysql master-slave backup _ MySQL

Mysql master-slave backup bitsCN.com

I. prepare servers

Because the binlog formats of different MySQL versions (binary logs) may be different, the best combination is that the MySQL version of the Master is the same or lower than the Slave version, the Master version must not be higher than the Slave version.

Assume that the Master server and Slave server (Slave) versions are 5.0.63.

Assume that the host name of the synchronization Master is A (IP: 192.168.0.1), the Slave host name is B (IP: 192.168.0.2), and the two basedir directories of MySQL are/usr/local/mysql, datadir is both:/var/mysql.

II. set synchronization server

1. set synchronization Master

Modify the my. cnf file in
# Replication Master Server (default)
# Binary logging is required for replication
Add the following content:

Java code

1. Log-bin = mysql-bin
2. Server-id = 1
3. Binlog-do-db = test
4. Binlog-ignore-db = mysql

Restart MySQL and create a MySQL account for synchronization

SQL code

1. Grant replication slave, RELOAD, SUPER, ON *. * TO backup@192.168.0.2 identified by 'slavepass ';
2. Flush privileges;

Copy a database:

SQL code

1. Flush tables with read lock;

Lock the table to clear the write operation.

SQL code

1. Show master status;

The File column shows the log name, while the Position column shows the offset, record the backup.
We recommend that you use direct file replication for MYSQL stored in MYI to back up the database, which is much more efficient. Unlock after backup.

SQL code

1. Unlock tables;

After the backup, import the database from the server

You can also use mysqldump for the above replication process. if the database is providing services, you must add -- lock-all-tables to ensure that the data is at the same time. When you add -- master-data = 1 TO dump, the binlog name and offset value will be recorded and stored in the file in the form of change master, in this way, you do not need to set the binlog file and offset value of the master after importing the slave.

2. set synchronization Slave

Modify the my. cnf file and add

Java code

1. Server-id = 2
2. Master-host = 192.168.0.1
3. Master-user = backup
4. Master-password = slavepass
5. Master-port = 3306
7. Replicate-ignore-db = mysql
8. Replicate-do-db = test

Server-id cannot be the same as master

Restart MySQL

3. start synchronization

Run the MySQL command on the master server:

SQL code

1. Show master status;

Display (of course, this is my machine, you cannot be the same as me, just an example ):
+ ------------------ + ---------- + ------------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + ------------------- + ------------------ +
| Mysql-bin.000028 | 313361 | test | mysql |
+ ------------------ + ---------- + ------------------- + ------------------ +

Under the slave server B MySQL command:

SQL code

1. Slave stop;
2. MySQL> CHANGE MASTER
3. -> MASTER_HOST = 'master _ host_name ',
4. -> MASTER_USER = 'replication _ user_name ',
5. -> MASTER_PASSWORD = 'replication _ password ',
6. -> MASTER_LOG_FILE = 'recorded _ log_file_name ',
7. -> MASTER_LOG_POS = recorded_log_position;
8. Slave start;

Use show slave status to check synchronization from the server
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
If all are yes, it indicates that the synchronization is in progress.

Write data to the table and test whether the synchronization is successful. if the synchronization fails, it is definitely not your RP problem. then check the Operation steps!


4. set bidirectional synchronization


In fact, this is the reverse operation of A-> B One-way synchronization! Bidirectional synchronization is that simple!

III. synchronization error handling

It is found that the mysql slave server often reports errors due to the update statements generated by some special characters or symbols, and the entire synchronization will also be stuck there. The initial method is to manually go to the wrong machine, run the following three SQL statements to skip the error.

SQL code

1. Slave stop;
2. Set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1;
3. Slave start;

IV. underlying backup knowledge

1. binlog
The binlog of mysql records all the operations of the database, for example, I have a new database bbs, the binlog is also enabled when the database is established, then mysql will generate a mysql-bin.000001 in the var Directory, this file records all SQL commands for database bbs operations. Each binlog file defaults to 1 GB and is automatically switched to the mysql-bin.000002 when it is exceeded. The mysql-bin.index records the names of all mysql-bin.

Mysql-bin.index example:

Java code

1. /Mysql-bin.000001
2. /Mysql-bin.000002
3. /Mysql-bin.000003
4. /Mysql-bin.000004
5. /Mysql-bin.000005
6. /Mysql-bin.000006
7. /Mysql-bin.000007
8. /Mysql-bin.000008
9. /Mysql-bin.000009
10. /Mysql-bin.000010

In fact, the master node sends the binlog to slave and then runs these SQL statements locally.

2. master.info
The master.info file exists in the var directory of slave and records the master information.
Master.info example:

Java code

1. 15
2. Mysql-bin.000105 # binlog file for master
3. 498027148 # offset value
4. 192.168.0.1
5. Backup
6. Slavepass
7. 3306
8. 60
9. 0
15. 0

3. relay-log.info
The relay-log.info file exists in the var Directory of the slave and records information about the binlog file executed by the slave.
Relay-log.info example:

Java code

1. ./Lab-relay-bin.000050 # slave files that store the binlog of The master
2. 210263408 # slave offset value
3. Mysql-bin.000105
4. 498027148
5. 2

From the mysql-bin.000105 of the master sends information to the lab-relay-bin.000050, and slave then retrieves the SQL statement from the lab-relay-bin.000050 for execution.

BitsCN.com