MySQL-06 Data Backup and recovery

Learning Goals

• Data backup
• Data recovery
• Database migration
• Import and Export

Data backup

A system crash or server hardware corruption can cause the database to be lost, so data backup is important in a production environment.

mysqldump Command Backup

The command can back up the database into a file file that contains data definition statements and data manipulation statements that can be recreated to recreate the database tables and insert data.

Syntax format:

Mysqldump-u user-h host-ppassword dbname[tablename,[tablename ...] > [Dump File.sql]

Example: Backing up a MySchool database

To back up a text file:

----dumping data for table ' subject '--lock TABLES ' subject ' write;/*!40000 ALTER TABLE ' Subject ' DISABLE KEYS */;insert into ' Subject ' VALUES (1, ' Database base ', 1,20), (2, ' Java Logic Foundation ', 1,18), (3, ' SQL Basics ', 1,15), (4, ' HTML ', 1,20), (5, ' Control+ajax ', 2,20), 
 (6, ' Project Management ', 4,40), (7, ' software engineering ', 3,28), (8, ' algorithm and data structure ', 2,15), (9, ' C ', 1,25), (10 , ' Deng Xiaoping Theory ', 1,18), (one, ' C # Object-oriented ', 2,16), 
 (13, ' Javaweb ', 3,40), (14, ' data mining ', 4,20), (+ (), ' English ', ' 1,10 '), (A, ' JavaScript '), 1,15), (16, ' software layered architecture ', 3,20);/*!40000 ALTER TABLE ' subject ' ENABLE KEYS * *; UNLOCK tables;/*!40103 Set [Email protected]_time_zone */;/*!40101 set [Email protected]_sql_mode */;/*! 40014 Set [Email protected]_foreign_key_checks */;/*!40014 set [Email protected]_unique_checks */;/*!40101 Set [Email protected]_character_set_client */;/*!40101 set [Email protected]_character_set_results */;/*! 40101 Set [Email protected]_collation_connection */;/*!40111 Set [email protected]_sql_notes */; 

　　

which

--: Indicates the content of the comment.

/*!...... */: Indicates executable comments, executable in MySQL, and annotated in other databases.

40101: The MySQL version indicates that this statement can only be performed in MySQL version 4.01.01 or later.

Example: Backing up a myschool.student table

Warning: It is not safe to enter a password on the command line. Ignore.

Example: Backing up multiple databases

Backing up multiple databases requires adding "--databases" parameters followed by multiple database names

If you want to back up all databases, use the parameters:--all-databases

Options for Mysqldump

Copy Database Directory

Lock TABLES is required before the backup, and flush TABLES is performed on the table. You can also stop the MySQL service for backup.

Disadvantages: 1, InnoDB engine table is not supported, 2, different versions of MySQL is not supported.

Mysqlhotcopy Fast Recovery

Only MyISAM engine tables can be backed up.

Example: Backing up MySQL to the d:/directory

Mysqlhotcopy-u root-p123 MySchool D:/db.sql

　　

Data recovery

Restores the backed up data.

Using the MySQL command to recover

Syntax format for MySQL commands

Mysql-u User-ppassword [DBName] < Backdbfilename.sql

　　

If Backdbfilename.sql is a file containing database statements created by the mysqldump command, you do not need to specify the database name when executing.

Example: Recovering a MySchool database

Note: You need to manually create the MySchool database and specify it first.

Copy directly to the database directory

Stop the MySQL service before execution and restart the MySQL service after copying.

Disadvantage: The InnoDB engine table is not supported.

Quick recovery with Mysqlhotcopy

Copy the mysqlhotcopy exported data to the MySQL database file directory and restart the MySQL service. You need to specify the owner of the database file in Linux, using the Chown command.

If the restored database already exists, it needs to be deleted using the drop command and then resumed.

Database migration

Move data from one database system to another database system.

Same version of MySQL data migration

The major version number of the same database migration, the essence is the source database backup and the target database recovery process.

(Note: The Copy database file is only appropriate for the MyISAM type database and does not support the InnoDB type database.)

Example: Migrating the MySQL database on host A to host B

Mysqldump-h Www.a.com-uUser-pPassword DBName | Mysql-h Www.b.com-uUser-pPassword

"|" : Pipe connector.

If you want to migrate all, use the--all-databases parameter.

Migration of different versions

We recommend that you export SQL to execute SQL on the target database.

Export and import of tables

Data tables can be exported as SQL, XML, or HTML files while supporting import.

To export a table using Select

Syntax format

SELECT  field from table name WHERE condition into OUTFILE  ' file path '  [option]

　　

Note implementation: Need to modify the My.ini "secure-file-priv= pathname", MySQL only support export to the specified directory.

Exporting a text file using the MySQL command

Syntax format

Mysql-uuser-ppassword--execute= "SELECT statement" DBName >filename.txt

　　

Import a text file using load DATA infile

Syntax format

LOAD DATA INFILE ' filename.txt ' into TABLE TableName

　　

Import a text file using the Mysqlimport command

Syntax format

Mysqlimport-uuser-ppassword DBName FileName.txt

　　

Comprehensive exercises

Requirements Description

1. Export MySchool Database using mysqldump
2. Delete MySchool data
3. Recover myschool database using MySQL command

MySQL-06 Data Backup and recovery