MySQL data backup and restore sample code

1. Data backup

1. Use mysqldump command to back up

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 will be stored in the generated text file.

The working principle of the mysqldump command is simple. It first finds out the structure of the table that needs to be backed up, and then generates a CREATE statement in a text file. Then, convert all the records in the table into one INSERT statement. Then through 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 

in:

The dbname parameter indicates the name of the database;

The table1 and table2 parameters indicate the names of the tables to be backed up. If they are left blank, the entire database will be backed up.
The BackupName.sql parameter table is used to design the name of the backup file. An absolute path can be added before the file name. The database is usually divided into a file with the suffix sql;

Back up the person table in the test database as root user

mysqldump -u root -p test person > D:ackup.sql

The generated script is as follows:

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

The comments that begin with "--" in the file are SQL language comments, and the comments that begin with "/*!40101" and other forms are related to MySQL. 40101 is the version number of the MySQL database. If the MySQL version is higher than 1.11, the content between /*!40101 and */ is executed as an SQL command. If it is lower than 4.1.1, it is treated as a comment.

2. Back up multiple databases

grammar:

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

Add the --databases option, followed by multiple databases

mysqldump -u root -p --databases test mysql > D:ackup.sql

3. Back up all databases

The syntax for the mysqldump command to back up all databases is as follows:

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

Example:

mysqldump -u -root -p -all-databases > D:all.sql

2. Directly copy the entire database directory

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

However, before doing so, you must stop the server to ensure that the database data does not change during the copy. If data is written during the database replication process, data inconsistency will occur. This is fine in a development environment, but it is difficult to allow backup servers in a production environment.

Note: This method is not applicable to tables using the InnoDB storage engine, but is convenient for tables using the MyISAM storage engine. At the same time, the MySQL version should be the same when restoring.

3. Use mysqlhotcopy tool for quick backup

You can tell from the name that it is hot backup. Therefore, mysqlhotcopy supports backup without stopping the MySQL server. Moreover, mysqlhotcopy's backup method is faster than mysqldump.

mysqlhotcopy is a perl script, mainly used in Linux systems. It uses LOCK TABLES, FLUSH TABLES, and cp for fast backups.

Principle: First add a read lock to the database to be backed up, then use FLUSH TABLES to write the data in the memory back to the database on the hard disk, and finally, copy the database file to be backed up to the target directory.

The command format is as follows:

[root@localhost ~]# mysqlhotcopy [option] dbname1 dbname2 backupDir/

dbname: database name;

backupDir: the folder to which the backup is to be placed;

Common options:

--help: View mysqlhotcopy help;
--allowold: If the same backup file exists in the backup directory, add _old to the old backup file;
--keepold: If the same backup file exists in the backup directory, the old backup file will not be deleted but renamed;
--flushlog: After this generation, the updates to the database will be recorded in the log;
--noindices: only back up data files, not index files;
--user=username: used to specify the user name, which can be replaced by -u;
--password=password: used to specify the password, can be replaced by -p. When using -p, there is no space between the password and -p;
--port=port number: used to specify the access port, which can be replaced by -P;
--socket=socket file: used to specify the socket file, which can be replaced by -S;

mysqlhotcopy is not included with mysql, you need to install the Perl database interface package; the download address is: http://dev.mysql.com/downloads/dbi.html

Currently, this tool can only back up MyISAM tables.

2. Data Restoration

1. Restore the database using the mysqldump command backup

The syntax is as follows:

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

Example:

mysql -u root -p < C:backup.sql

2. Restore the direct copy directory backup

When restoring in this way, you must ensure that the version numbers of the two MySQL databases are the same.

This function is valid for MyISAM tables but not for InnoDB tables. The table space of an InnoDB table cannot be copied directly.

Summarize