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

This article introduces the relevant knowledge of "what is the method of MySQL data backup and restore". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve 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) backup 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:\ backup.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) backup 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:\ backup.sql

(3) backup 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 the 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. The syntax for restoring a database backed up with 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 numbers of the two MySQL databases are the same. Tables of type MyISAM are valid, but are not available for tables of type InnoDB, and table spaces of InnoDB tables cannot be replicated directly.

This is the end of the content of "what is the method of backup and restore of MySQL data". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.