Summary of MySql import and export methods using mysqldump

Export database data:

First open cmd and enter the bin folder of MySQL

1. Export the table data and table structure of the users table in the education database (the users table is taken as an example below)

mysqldump -u[user name] -h[ip] -p[password] -P[port number] database name table name>exported file name.sql

mysqldump -uroot -h127.0.0.1 -proot -P3306 education users>d:/user.sql

Export multiple tables:

mysqldump -uroot -proot --databases test --tables t1 t2>two.sql 

2. To export only the table structure but not the table data, add the "-d" command parameter

mysqldump -uroot -h127.0.0.1 -proot -P3306 -d education users>d:/user.sql

3. Export only table data without table structure, add "-t" command parameter

mysqldump -uroot -h127.0.0.1 -proot -P3306 -t education users>d:/user.sql

4. Export the table structure and table data of the database (you need to specify the database when importing to ensure that the specified database exists)

Export command: mysqldump -h127.0.0.1 -P3306 -uroot -proot education >d:/database.sql

Import command: mysql -uroot -proot -h127.0.0.1 -P3306 education<d:/database.sql

5. Export the table structure and data of all tables in a specific database, add the "--databases" command parameter (just execute the export sql directly when importing)

Export command: mysqldump -h127.0.0.1 -P3306 -uroot -proot education --databases education> database.sql

Import command: mysql -uroot -proot -h127.0.0.1 -P3306 <d:/all_database.sql

6. Export all databases

This command will export all databases including the system database (all.sql is saved in the bin folder by default)

mysqldump -uroot -proot --all-databases>all.sql

7. Export all data of the two databases db1 and db2

mysqldump -uroot -proot --databases db1 db2>dbs.sql

8. Only export the table structure of the test database

Export command: mysqldump -uroot -proot --no-data --databases test>s1.sql

Import command: mysql -uroot -proot -h127.0.0.1 -P3306 test<s1.sql

Restore and import database data:

System command line

Format: mysql -h[ip] -P[(uppercase) port] -u[user name] -p[password] [database name] < d:XX.sql(path)

Import command: mysql -uroot -proot -h127.0.0.1 -P3306 education<d:/database.sql

Import command: mysql -uroot -proot -h127.0.0.1 -P3306 <d:/all_database.sql

The above two situations have explained when to use

mysql command line

First, connect to mysql. The command line format is as follows:

Format: mysql -h host address -u username -p user password

The following figure is to link the local mysql:mysql -uroot -proot

Link remotely: mysql -h10.0.0.10 -uroot -proot

mysql> use test;

mysql> source /home/test/database.sql 

Reference Links:

https://www.jb51.net/article/73230.htm

https://blog.csdn.net/helloxiaozhe/article/details/77680255

This is the end of this article summarizing the MySql import and export methods using mysqldump. For more relevant mysqldump import and export content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!