How to use mysql commands to restore a Database Backup

This article introduces some commonly used mysql commands for data import and export. As long as we hold these commands, we can quickly back up and restore the database.

Import and Export mysql Data to MB

Problem: the database and table data of MB must be imported to the new database.

First, go to mysql through the console

Mysql-u root-p 12345

Create database 'Demo' default character set utf8 COLLATE utf8_general_ci;

Use demo; Switch Database

Source file. SQL imports data, where file. SQL is in the bin directory of mysql and can be changed to its own path.

Problem: Chinese characters are garbled after the import ....

Try again
Drop database demo

C utf8; // switch Encoding

Use demo;

Source file. SQL;
--------------------------------------------
Mysqldump database name-u root-p-h IP address> file. SQL


Other information about mysql Data export and import to the database

Export
Mysqldump Method
Mysqldump-u user name-p password name database [table]> target file
Import
Mysql-uroot-proot
Use database
Source Target file;
PS: This method exports the entire table data and carries the table creation information. If the imported database has a table with the same name, it will be replaced.
PS: conditions can be added.
Mysql-uroot-proot [-n] [-t] [-d] database [table]> name
-T does not contain information about the table to be created.
-D does not contain data information
-- W or-w filtering Conditions

Example 1: Enter the key + R next to Ctrl in dos cmd command mode
2 Export mysqldump-uroot-proot test student-t-w studentno = 10101> stu
3. Import mysql-uroot-proot
4 mysql> use test
5 mysql> source stu

Method 2

Into outfile
Load data infile

Example
Export
Mysql-uroot-proot
Mysql> use test
Mysql> select * from student where studentno = 10101 into outfile './stu ';
Import
Mysql-uroot-proot
Mysql> use test
Mysql> load data infile './stu' into table student;

Export xml
Mysqldump -- xml-uroot-proot [database] [table]> name. xml
[Test]
Mysqldump -- xml-uroot-proot test> a. xml
Mysqldump -- xml-uroot-proot test dept> a. xml

Mysql-X-uroot-proot-e "use test; select * from dept"> a. xml [method 2]

Import xml file content to the database
Mysql> create table xmlt (
-> Id int,
-> Doc blob
-> );

Mysql> insert into xmlt values (1, load_file ('/home/a. xml '));
Mysql> select * from xmlt;

MYSQL Common commands
1. Export the entire database
Mysqldump-u username-p -- default-character-set = latin1 Database Name> exported file name (the default database encoding is latin1)
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL
2. Export a table
Mysqldump-u user name-p database name Table Name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL
3. Export a database structure
Mysqldump-u wcnc-p-d-add-drop-table smgp_apps_wcnc> d: wcnc_db. SQL
-D no data-add-drop-table add a drop table before each create statement
4. Import the database
A: common source commands
Go to the mysql Database Console,
For example, mysql-u root-p
Mysql> use Database
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source wcnc_db. SQL

B: Use the mysqldump command.

Mysqldump-u username-p dbname <filename. SQL

C: Use the mysql Command

Mysql-u username-p-D dbname <filename. SQL