Use the mysql command to implement the database backup restore method

This article introduces some data import and export commands that are commonly used by mysql. As long as we hold these commands, we can quickly backup and restore our database

mysql data to import and export 500MB data

Problem: the library table data of 500M needs to be imported into the new database.

Enter mysql first 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, in which file.sql is in bin directory of mysql and can also be modified to its own path

Problem: Chinese characters appear after import...

To come 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 about mysql data export import database

export
mysqldump method
mysqldump-u user name -p password name database [table] > The target file
The import
mysql -uroot -proot
use database
source target file;
PS: this method exports the entire table data with the build table information, and if the imported database has a table with the same name, it will be replaced
PS: you can add conditions
mysql -uroot -proot [-n] [-t] [-d] database [table] > name
-t does not contain information to create a table
-d does not contain data information
-- screening conditions for w or-w

Example 1: enter dos cmd command mode Ctrl next to the key +R
2 export mysqldump-uroot-proot test es1064en es1010en-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';
The 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" > Method a. xml [2]

Import the xml file contents into 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
Export the entire database
mysqldump-u username -p -- default-character-set =latin1 database name > Exported file name (database default code is latin1)
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
Export 1 table
mysqldump-u user name -p database name table name > Exported file name
mysqldump -u wcnc -p smgp_apps_wcnc users > wcnc_users.sql
3. Export a database structure
mysqldump -- wcnc -- p -- d -- add -- drop -- table -- smgp_apps_wcnc > d:wcnc_db.sql
Add 1 drop table before each create statement
4. Import database
A: the source command is commonly used
Go to the mysql database console,
For example, mysql-u root-p
mysql > use database
Then use the source command, followed by the script file (as used here.sql)
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