Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve data backup and restore in MySQL, the content is concise and easy to understand, absolutely can make your eyes bright, through the detailed introduction of this article, I hope you can get something.

I. 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 are stored in the generated text file.

The mysqldump command works simply. It first finds out 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 then create the table and insert the data.

1. Back up a database

Basic mysqldump syntax:

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

Where:

The dbname parameter represents the name of the database

The table1 and table2 parameters indicate the name of the table that needs to be backed up. If empty, the entire database is backed up.

The BackupName.sql parameter table designs the name of the backup file, which can be preceded by an absolute path. The database is usually divided into a file with the suffix sql

Using root users to back up person tables under the test database

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

The generated script is as follows:

The version of MySQL, the hostname of the backup, and the database name are recorded at the beginning of the file.

What starts with "-" in the file is a comment in SQL language, and what begins with "/ *! 40101" is a comment related to MySQL. 40101 is the version number of the MySQL database. If the version of MySQL is higher than 1.11, the content between / *! 40101 and * / is executed as a SQL command, and if it is lower than 4.1.1, it is treated as a comment.

2. Back up multiple databases

Syntax:

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: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. Copy the whole 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 way.

Before that, however, stop the server so that the data in the database will not change during replication. If there are still data writes in the process of copying the database, it will cause data inconsistency. This is fine in a development environment, but it is difficult to allow backup servers in a production environment.

Note: this method does not apply to tables of the InnoDB storage engine, but is convenient for tables of the MyISAM storage engine. At the same time, the version of MySQL should be the same when restoring.

3. Use the mysqlhotcopy tool to back up quickly

You can tell it's a hot backup when you look at the name. Therefore, mysqlhotcopy supports not stopping MySQL server backups. Also, mysqlhotcopy backs up faster than mysqldump.

Mysqlhotcopy is a perl script that is mainly used on Linux systems. It uses 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 format of the command is as follows:

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

Dbname: database name

BackupDir: which folder to back up to;

Common options:

-- help: view mysqlhotcopy help

-- allowold: if the same backup file exists in the backup directory, add the old backup file to _ old

-- keepold: if the same backup file exists in the backup directory, do not delete the old backup file, but rename the old file

-- flushlog: record updates to the database in the log after this seniority

-- noindices: only data files are backed up, not index files

-- user= user name: used to specify the user name, which can be replaced by-u

-- password= password: used to specify a password, which 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 socket file, which can be replaced by-S;

Mysqlhotcopy is not included with mysql. You need to install the database interface package of Perl.

Currently, the tool can only back up tables of type MyISAM.

II. Data restoration

1. Restore backup database using mysqldump command

The syntax is as follows:

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

Example:

Mysql-u root-p < C:backup.sql the above content is how to achieve data backup and restore in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.