Mysql Notes Data backup and restoration of the use of detailed _mysql

The data is very important, this point with toes to know, in order to ensure data security, so the need to regularly backup data

Let's write a few notes about data backup and restore

Data backup

Backing up a database using the mysqldump command

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

Where dbname represents the database name, table1 and table2 represent the name of the table, without which the entire database is backed up

The Backupname.sql parameter represents the name of the backup file, which can be preceded by an absolute path

It is important to note that only the tables in the database are backed up, and if you want to restore you must restore them in the existing database

tips: Typically, the suffix for a backup file is. sql, which can also be. txt, but not professional

Backing up multiple databases using the mysqldump command

mysqldump-u username-p --databases dbname1 dbname2...>backupname.sql 

Here--databases means to back up multiple databases, followed by the database name

In addition, you can back up all databases

Backing up all databases using the mysqldump command

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

Copy the entire database directory directly

Replicating the database directory directly is the easiest way to backup, but not the best

When using this method, the database service needs to be temporarily stopped in order to avoid data changes, but is not allowed in practice

Also, this method does not apply to the tables of the InnoDB storage engine, which is more convenient for the tables of the MyISAM storage engine

Data restore

Restore using the MySQL command

-P[dbname]<backup.sql    

[dbname] is optional if you use the--all-databases parameter to back up the entire database instead of backing up the table individually

Then you can not use the dbname parameter

Copy directly to the database directory

You must ensure that the major version number of the database is consistent in this way

Usually effective for myisam, not suitable for InnoDB