How to export and import MySQL data (data migration)

Source: Internet
Author: User

  This article goes from   How to export and import MySQL data (data migration)   This is one of the methods, very simple  1. Export database Data    mysqldump-uroot-p dp_db > dumpout.sql    where root is the account name          dp_db is the database name that needs to be exported         dumpout.sql stores the exported data  2. Put the exported data dumpout.sql on your target machine (if it is a remote target machine, copy the Dunmpout.sql to the target machine directly). This assumes the CP to the main directory under ~/ 3. Create a new database in the target machine's database, and create a database named Dp_db_bak    mysql> Create db dp_db_bak 4. Import data to the Dp_db_bak database    mysql-uroot-p Dp_db_bak < ~/dumpout.sql   where Root is the account name    & Nbsp;    db_db_bak is a new database         ~/ Dumpout.sql is the first step to export the data  p.s. If you encounter a file that is too large to exit unexpectedly when importing and exporting data, you can use Split/cat to split and merge files    mysqldump-uroot-p DP _db | Split-b 10m-tempfile   cat all_tempfile > targetfile  Other methods:

The Mysql Source command is very useful when importing big data. Although MySQL administrator software is also good, but in terms of stability and efficiency is still source dominant.
How to use:
Copy the SQL database backup to the disk directory under Windows, such as: D:\db.sql,cmd into the command line.
1, first use the command mysql-uroot-p login MySQL server
2. Then execute the relevant command to import the data.

mysql> use dbtest; Sets the Dbtest database for which data is currently being imported
mysql> set names UTF8; Set encoding
Mysql> source D:\db.sql; Import data

OK, finish. Of course the database backup with MySQL administrator is very convenient

MySQL export table structure and table Data mysqldump usage command-line use the following: Mysqldump-u user name-p password-d the name of the script name of the database name table;

1, export the database as DBNAME table structure (where the username is root, the password is DBPASSWD, the generated script is named Db.sql)
Mysqldump-uroot-pdbpasswd-d dbname >db.sql;

2. Export the database as dbname a table (test) structure
Mysqldump-uroot-pdbpasswd-d dbname test>db.sql;

3. Export the database to dbname all table structures and table numbers (without-D)
MYSQLDUMP-UROOT-PDBPASSWD dbname >db.sql;

4. Export the database as dbname a table (test) structure and table numbers (without-D)
MYSQLDUMP-UROOT-PDBPASSWD dbname test>db.sql;


Specify the delimiter to export the data (seemingly must be performed natively in order to perform properly), step:
1. cd/tmp
2. mkdir Mysqldata
3. Chown Mysql:mysql Mysqldata
4. mysqldump-h127.0.0.1-uroot-p123456-t/mysqldata/[email protected]@, dbname tablename

How to export and import MySQL data (data migration)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.