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 name of the database 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, just copy dumpout.sql directly to the target machine). Here we assume cp to the home directory ~/
3. Create a new database in the database of the target machine, here create database named dp_db_bak
mysql> create database dp_db_bak
4. Import data to dp_db_bak database
mysql -uroot -p dp_db_bak < ~/dumpout.sql
where root is the account name
db_db_bak is the new database
~/dumpout.sql is the first step of the data export
P.S. If you encounter a file too large for the export data, it will not be possible for you to export the data. If the file is too large and exits abnormally, you can use split/cat to split and merge the file
mysqldump -uroot -p dp_db | split -b 10m - tempfile
cat all_tempfile > targetfile
Other methods:
The Mysql source command seems to be very useful when importing large data. Although the Mysql Administrator software is also good, source is superior in terms of stability and efficiency.
How to use:
Windows copy sql database backup to disk directory such as: D:\db.sql, cmd into the command line.
1, first use the command mysql -uroot -p to log in to the mysql server
2, and then execute the relevant commands to import data.
mysql>usedbtest;//set the current dbtest database where you want to import the data
mysql>setnamesutf8;//set the encoding
mysql>sourceD:\db.sql;//import data
ok. Done. Of course, database backup with Mysql Administrator is very convenient
Mysql export table structure and table data mysqldump usage
The specific use of the command line is as follows: mysqldump -u with the _ name -p password -d ___ library name table name script name;
1, export ___ library ___ dbname table structure (in which ___ library ___ dbname table name);
1. dbname table structure (which uses _name _root, password _dbpasswd, generated script name _db.sql)
mysqldump -uroot -pdbpasswd -d dbname >db.sql;
2, to export the __ library __ dbname a table (test) structure
mysqldump -uroot -pdbpasswd -d dbname test>db.sql;
3, export __ library __ dbname all table structure and table __ (without -d)
mysqldump -uroot -pdbpasswd dbname >db.sql;
4, export __ library __ dbname a table (test) structure and table __ (without -d)
mysqldump -uroot -pdbpasswd dbname test >db.sql;
Specify the delimiter to export data ( It looks like it must be executed locally to work properly), steps:
1. cd /tmp
2. mkdir mysqldata
3. chown mysql:mysql mysqldata
4. mysqldump -h127.0.0.1 -uroot - p123456 -T/mysqldata/ --fields-terminated-by=@@, dbname tablename
How to Export and Import mysql Data (Data Migration)
Tagged with: generate nbsp127.0.0.1 table data server pfilenmp situation roo