MySQL Backup recovery

First, data backup

　　1. Backup with mysqldump command

The mysqldump command backs up the data in the database into a text file. The structure of the table and the data in the table are stored in the generated text file.

The mysqldump command works very simply. It first identifies the structure of the table that needs to be backed up, and then generates a CREATE statement in the text file. Then, convert all the records in the table into an INSERT statement. With these statements, you can create tables and insert data.

　　1. Back up a database

Mysqldump Basic Syntax:

　　Mysqldump-u username-p dbname table1 table2 ...-> backupname.sql

which

• The dbname parameter represents the name of the database;
• The table1 and table2 parameters represent the names of the tables that need to be backed up, and the entire database is backed up as empty;
• Backupname.sql parameter table design the name of the backup file with an absolute path before the file name. Typically the database is partitioned into a file with a suffix called SQL;

Use the root user to back up the person table under the test database

Mysqldump-u root-p Test Person > D:\backup.sql

　　

The script is generated as follows:

　　

The beginning of the file will record the MySQL version, the host name of the backup, and the database name.

The "--" in the file begins with the comments of the SQL language, which begins with "/*!40101", and is a MySQL-related comment. 40101 is the version number of the MySQL database, if the MySQL version is higher than 1.11, then the content between/*!40101 and/* is treated as a SQL command, if compared to 4.1.1 must not will be considered as comments.

　　2. Backing up multiple databases

Grammar:

Mysqldump-u username-p--databases dbname2 dbname2 > Backup.sql

The--databases option is added, followed by multiple databases

Mysqldump-u root-p--databases Test mysql > D:\backup.sql

　　3. Back up all databases

The syntax for backing up all databases with the mysqldump command is as follows:

Mysqldump-u username-p-all-databases > Backupname.sql

Example:

Mysqldump-u-root-p-all-databases > D:\all.sql

　　2. Copy the entire database directory directly

MySQL has a very simple backup method, which is to copy the database files in MySQL directly. This is the simplest and fastest method.

However, before you do this, you will need to stop the server before you can guarantee that the data for the database will not change during replication. If there is data written during the copying of the database, the data will be inconsistent. This situation is possible in the development environment, but it is difficult to allow backup servers in a production environment.

Note: This approach does not apply to tables of the InnoDB storage engine, but is convenient for MyISAM storage engine tables. At the same time, the MySQL version is best when restoring.

　　3. Quick Backup using Mysqlhotcopy tool

A look at the name will know it is a hot backup. Therefore, mysqlhotcopy support does not stop the MySQL server backup. In addition, Mysqlhotcopy is faster than mysqldump backup. Mysqlhotcopy is a Perl script that is primarily used under Linux systems. It uses the lock TABLES, FLUSH TABLES, and CP for quick backups.

Principle: First add a read lock to the database that needs to be backed up, then use flush tables to write the data in memory back to the database on the hard disk, and finally, copy the database files that need to be backed up to the target directory.

The command format is as follows:

[Email protected] ~]# mysqlhotcopy [option] dbname1 dbname2 backupdir/

• dbname: Database name;
• Backupdir: under which folder to back up;

Common options:

• --help: View Mysqlhotcopy help;
• --allowold: If the same backup file exists under the backup directory, the old backup file is added _old;
• --keepold: If the backup directory has the same backup file, do not delete the old backup file, but the old file name;
• --flushlog: After this generational, the database updates are recorded in the log;
• --noindices: Only back up data files, not backup index files;
• --user= user name: Used to specify the user name, you can use-u instead;
• --password= Password: Used to specify the password, you can use-P instead. When using-p, there is no space between the password and-p;
• --port= port number: Used to specify the access port, you can use-p instead;
• --socket=socket file: Used to specify the socket file, you can use-s instead;

Mysqlhotcopy is not a MySQL-band, it needs to install Perl's database interface package; for: http://dev.mysql.com/downloads/dbi.html

Currently, the tool is only able to back up tables of type MyISAM.

Second, data restoration

　　1. The syntax for restoring a database backed up using the mysqldump command is as follows:

Mysql-u root-p [dbname] < BACKUP.SQ

Example:

Mysql-u Root-p < C:\backup.sql

2. Restore the backup of the direct copy directory

　　When restoring in this way, you must ensure that the version number of the two MySQL databases is the same. Tables of the MyISAM type are valid, and table spaces for InnoDB tables cannot be copied directly for tables that have INNODB types that are not available.

MySQL Backup recovery