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