The method of implementing database backup and restore with the command of Mysql with self _mysql

This article describes all the common MySQL commands some data import Export command, as long as we hold these commands can be a quick way to backup our database restore the

MySQL data to import export 500MB data

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

First access 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 import data, which file.sql in the MySQL bin directory, can also be modified to its own path

Problem: After import, there are garbled characters in Chinese ....

Come back again.
Drop DATABASE Demo

C utf8;//Switching code

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]> target file
Import
Mysql-uroot-proot
Use database
source target file;
PS: This method is to export the entire table data, and with the build table information, if the imported database has the same name table, will be replaced
PS: You can add conditions
Mysql-uroot-proot [-n] [-t] [d] Database [Table]>name
-T does not contain information to create tables
-D does not contain data information
--w or-w Filter Criteria

1 cases: First into the DOS CMD command mode ctrl next to the key +r
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 Two

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;

Exporting 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 contents to 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 user name-p--default-character-set=latin1 Database name > exported file name (database default encoding is latin1)
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql
2. Export a 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-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 Database
A: Common source commands
Enter the MySQL database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (such as the. SQL used here)
Mysql>source Wcnc_db.sql

B: Use the mysqldump command

Mysqldump-u Username-p dbname < Filename.sql

C: Use MySQL command

Mysql-u Username-p-D dbname < Filename.sql