Mysqldump backup and recovery database in CentOS
Back up the database first
Use
Mysqldump-uroot-plvtao database>/home/bak. SQL
Restore the backup database.
Common source commands for Database Import
Go to the mysql Database Console,
For example, mysql-uroot-p
Mysql> use Database Name
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source/home/bak. SQL
Common options:
-- All-databases,-A: Back up all databases
-- Databases,-B: used to back up multiple databases. If this option is not available, mysqldump uses the first name parameter as the database name, followed by the table name. With this option, mysqldum treats each name as the database name.
-- Force,-f: continues the backup even if an SQL error is found.
-- Host = host_name,-h host_name: Backup host name. The default value is localhost.
-- No-data,-d: only export the table structure
-- Password [= password],-p [password]: password
-- Port = port_num,-P port_num: Specifies the port number for TCP/IP connection.
-- Quick,-q: quick export
-- Tables: overwrite the -- databases or-B option. The following parameters are treated as table names.
-- User = user_name,-u user_name: user Name
-- Xml,-X: export as an xml file
1. Back up the data and structure of all databases
Mysqldump-uroot-p123456-A> F: \ all. SQL
2. Back up the structure of all databases (with the-d parameter added)
Mysqldump-uroot-p123456-A-d> F: \ all_struct. SQL
3. Back up data in all databases (with the-t parameter added)
Mysqldump-uroot-p123456-A-t> F: \ all_data. SQL
4. Back up the data and structure of a single database (the database name is mydb)
Mysqldump-uroot-p123456 mydb> F: \ mydb. SQL
5. Back up the structure of a single database
Mysqldump-uroot-p123456 mydb-d> F: \ mydb. SQL
6. Back up data in a single database
Mysqldump-uroot-p123456 mydb-t> F: \ mydb. SQL
7. Back up the data and structure of multiple tables (the separate backup method of the data and structure is the same as that of the previous one)
Mysqldump-uroot-p123456 mydb t1 t2> f: \ multables. SQL
8. Back up multiple databases at a time
Mysqldump-uroot-p123456 -- databases db1 db2> f: \ muldbs. SQL
Restore part (1) mysql command line source method and (2) System Command Line Method
1. Restore all databases:
(1) mysql command line: mysql> source f: \ all. SQL
(2) system command line: mysql-uroot-p123456 <f: \ all. SQL
2. Restore a single database (you must specify a database)
(1) mysql> use mydb
Mysql> source f: \ mydb. SQL
(2) mysql-uroot-p123456 mydb <f: \ mydb. SQL
3. Restore multiple tables of a single database (you must specify a database)
(1) mysql> use mydb
Mysql> source f: \ multables. SQL
(2) mysql-uroot-p123456 mydb <f: \ multables. SQL
4. Restore multiple databases. (A backup file contains backups of multiple databases. You do not need to specify a database)
(1) mysql command line: mysql> source f: \ muldbs. SQL
(2) system command line: mysql-uroot-p123456 <f: \ muldbs. SQL