Talking about backup and recovery of MySQL database

1. Common backup methods for MySQL

1. Direct copy of database files (physical copy)

2. Backup using the mysqldump tool

3. Backup using the mysqlhotcopy tool

4. Use mysql master-slave synchronous replication to achieve real-time synchronous backup of data

2. Introduction to MySQL physical data file structure

1. Log files

Error log err log
Binary Log binary log
Update Log update log
Query Log query log
Slow Query Log slow query log
redo Log for innodb

2. Data files

> > > For myisam:

Table structure information:.frm
Data information:.myd
Data index information;myi
> > > For Innodb:

Exclusive tablespace:.ibd
Shared tablespace:.ibdata

3. System Files

Configuration file: my.cnf
Process file: xxx.pid
socket file: xxx.sock

4. replication file

master.info: Stored in the slave side directory, information about master and slave
relay log: Stores bin-log information read by the I/O process from master, then parsed log information is read by the SQL thread at the slave end from binary log and converted into query statements that slave can execute
index: The path where binary log is stored, which is the directory file

3. Backup and recovery using mysqldump

1. Backup principles

mysqldump backup principle is relatively simple, first find out the table structure that needs to be backed up, and generate an create statement in the text file;Then all the data records in the table are converted into an insert statement.These statements allow you to create tables and insert data.

2. Back up a database

Basic grammar:

>>> mysqldump -u username -p dbname table1 table2 ... > BackupName.sql


Example description:

mysqldump -u root -p test person > /tmp/backup.sql

3. Back up multiple databases

Basic grammar:

mysqldump -u username -p --databases dbname2 dbname2 > BackupName.sql

Example description:

mysqldump -u root -p --databases test mysql > /tmp/backup.sql

4. Back up all databases

Basic grammar:

mysqldump -u username -p -all-databases > BackupName.sql

Example description:

mysqldump -u -root -p -all-databases > /tmp/all.sql


5. Data recovery

Basic grammar:

mysql -u root -p [dbname] < backup.sql

Example description:

mysql -u root -p < /tmp/backup.sql

4. Copy the database directory directly

MySQL has a very simple backup method, which is to copy the database files directly from MySQL.This is the simplest and fastest method.Before doing so, however, stop the server so that the data in the database does not change during replication.Data inconsistencies can occur if data is also written during database replication.This is possible in a development environment, but it is difficult to allow backup servers in a production environment.

Note: This method is not applicable to tables of the InnoDB storage engine, but is convenient for tables of the MyISAM storage engine.Also, the MySQL version is best the same when restoring.