Introduction to MySQL Backup RESTORE command under Windows/linux server

Set MySQL installed in C: disk, MySQL database username is root, the password is 123456, the database name is database_name, in D: Packing directory to store the backup database, the backup database name is Backup20070713.sql ( 20070713.sql for backup date)

Back up the database:

Mysqldump is a SQL-level backup mechanism that guides data tables into SQL script files and is relatively appropriate for upgrades between different versions of MySQL, which is also the most common method of backup.

The code is as follows	Copy Code
mysqldump-uroot-p123456 Database_name>d:/backup20070713.sql

The top windows/below for Linux

I usually use the following SQL to back up the MyISAM table:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysqldump-uyejr-pyejr --default-character-set=utf8--opt--extended-insert=false --triggers-r--hex-blob-x db_name > Db_name.sql

Use the following SQL to back up the Innodb table:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysqldump-uyejr-pyejr --default-character-set=utf8--opt--extended-insert=false --triggers-r--hex-blob--single-transaction db_name > Db_name.sql

In addition, if you want to implement an online backup, you can also use the--master-data parameter to implement the following:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysqldump-uyejr-pyejr --default-character-set=utf8--opt--master-data=1 --single-transaction--flush-logs db_name > Db_name.sql

To recover a database :

Delete the original database, set up a database, and import the backup database.

The code is as follows	Copy Code
mysqladmin-uroot-p123456 drop database_name MYSQLADMIN-UROOT-P123456 Create database_name mysql-uroot-p123456 database_name

A file backed up with Mysqldump is a SQL script that can be poured directly, and there are two ways to import the data.

Directly with MySQL client
For example:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysql-uyejr-pyejr Db_name < Db_name.sql

Using the SOURCE syntax
In fact, this is not a standard SQL syntax, but the functionality provided by the MySQL client, for example:

The code is as follows	Copy Code
Source/tmp/db_name.sql;

You need to specify the absolute path of the file, and it must be a file that the Mysqld run user (for example, nobody) has permission to read.

Note: Before you import a backup database, database_name if not, you need to create it, and you can import it as a database name in Backup20070713.sql.

Mysqldump (Data Export tool)

Mysqldump options db_name[table_name]//back up a single database
mysqldump option--database database-name1 [databases-name2]....//backup the specified database one or more
mysqldump option--all-database//Back up all databases
Link Options:
-u: Specify user name
-P: Specify password
-H: Specify server IP or domain name
-P (UPPERCASE): Specify port
Eg:/usr/bin/mysqldump-u root-h 202.194.132.237-p 3306-p BBS user>/home/wuxiaoxiao/user.txt
Output Content options:
--add-drop-database: Add drop DATABASE statement before each database creation statement
--add-drop-table: Add a drop TABLE statement before each table creates a statement
-N: Create statements that do not contain databases
-T: Create statements that do not contain data tables
-D: does not contain data
Output format options:
--compact: Make the output simple
-C--compact-insert: Make INSERT statements in output file contain field names
-T: Back up data in a database table to simple data text and to create a table SQL two files
--fields-terminated-by=name (Field separator)
--fields-enclosed-by=name (field reference character)
--fields-optionally-enclosed-by=name (field-selectable reference character)
--fields-escaped-by=name (transfer character)
Eg:/usr/bin/mysqldump-u root-h 202.194.132.237-p 3306-p BBS user-t./bak
Character Set options:
--default-character-set=name: Set the exported client character set
Eg:mysql-u root-p--compact--default-character-set=utf8 BBS user > Test.txt