Usage of data backup and restoration in MySQL notes _ MySQL

Usage of data backup and restoration in MySQL notes

Data is very important. I want to know this with my toes. to ensure data security, we need to regularly back up data.

Here are some notes about data backup and restoration.

Data backup use the mysqldump command to back up a database

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

Dbname indicates the database name, and table1 and table2 indicate the table name. If this parameter is not set, the entire database is backed up.

The BackupName. SQL parameter indicates the name of the backup file. you can add an absolute path before the file name.

Note that only tables in the database are backed up during Backup. if you want to restore tables in an existing database

Tips:In general, the suffix of a file is. SQL. of course, it can also be. txt, but it is not professional.

Use the mysqldump command to back up multiple databases

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

Here -- databases indicates the meaning of backing up multiple databases, followed by the database name

In addition, all databases can be backed up.

Use the mysqldump command to back up all databases

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

Directly copy the entire database Directory

Directly copying the Database Directory is the simplest backup method, but it is not the best

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

This method is not applicable to tables of the InnoDB storage engine and is convenient for tables of the MyISAM storage engine.

Restore data using mysql commands

Mysql-u root-p [dbname]
 
  
[Dbname] is optional. if you use the -- all-databases parameter to back up the entire database instead of backing up the table separately
  
The dbname parameter is not used.
  
 
Copy directly to the Database Directory
  
To use this method, you must ensure that the primary version number of the database is consistent.
  
It is usually effective for MyISAM and not applicable to InnoDB
BitsCN.com