MySQL Command Line Export and Import Database Example Detailed Explanation

MySQL command line export database:

1, enter the bin folder under the MySQL directory: cd To the directory of the bin folder under MySQL

As I entered the command line: cd C:\Program Files\MySQL\MySQL Server 5.5\bin
(Or directly add the directory to the environment variable path of windows)

　2, export database: mysqldump -u Username -p Database name > Exported file name

As I entered the command line: mysqldump -u lmapp -p lmapp -P 3308 > lmapp.sql (you will be prompted to enter the password to enter MySQL after entering it)
(If you export a single table, you can enter the table name after the database name)

　　　　　　 mysql export a database table
　　mysqldump -u Username -p Database name Table name> Exported file name
　　mysqldump -u lmapp -p lmapp users> test_users.sql (no semicolon at the end)

3, you will see the file news.sql automatically generated to the bin file　　

　Encountered the above problem during export.

Solution: Add a parameter-P 3308(mysql service port) can solve the problem.

Command line import database:

1, move the .sql file to be imported to the bin file, which is a more convenient path
2, the same as the exported above1Step
3, enter MySQL: mysql -u Username -p
As I entered the command line:mysql -u root -p (you will be prompted to enter the MySQL password after entering the same)
4, in MySQL-In Front, create the database you want to create, which is an empty database at this time, such as creating a target database named news
5, input: mysql>use Target database name
As I entered the command line:mysql>use news;
6, import file: mysql>source The name of the imported file;

As I entered the command line: mysql>source news.sql;　

　　　　　　 Note: Due to the different scale and data structure of the imported database, the time required for import will vary greatly. The database I imported has123M, costing nearly5hours. During this period, I thought the machine had failed, but after careful observation of the command line interface, I found that the import was proceeding normally. The interface after successful import is as follows:

　　　　　　

　　　　　 Note: When importing a single data table, use SQL statements similar to 'use lmapp lm_area, source lm_area.sql'. The original data table can exist, and the imported data table will overwrite the existing data table with the same name.

　　　　　 MySQL backup and restore are both completed using the mysqldump, mysql, and source commands.

1.Win32.Backup and Restore of MySQL

1.1 Backup

Start Menu | Run | cmd | Use "cd \Program Files\MySQL\MySQL Server 5.0\bin” command to enter the bin folder | Use "mysqldump　 -u Username -p databasename >exportfilename” to export the database to a file, such as mysqldump -u root -p voice>voice.sql, then input the password to start exporting.

1.2 Restore

Enter the MySQL Command Line Client, input the password, enter "mysql>", input the command "show databases;", enter, see what databases are available; create the database you want to restore, input "create database voice;", enter; switch to the newly created database, input "use voice;", enter; import data, input "source voice.sql;", enter, start importing, when you see "mysql>" again and there are no error prompts, the restore is successful.

2.Backup and Restore of MySQL under Linux

2.1 Backup

[root@localhost ~]# cd /var/lib/mysql (Enter the MySQL library directory, adjust the directory according to your MySQL installation)
[root@localhost mysql]# mysqldump -u root -p voice>voice.sql, enter the password to start exporting.

2.2 Restore

Method 1:

[root@localhost ~]# mysql -u root -p Enter, input the password, enter the MySQL console "mysql>", same1.2Restore.

Method 2:

[root@localhost ~]# cd /var/lib/mysql (Enter the MySQL library directory, adjust the directory according to your MySQL installation)
[root@localhost mysql]# mysql -u root -p voice<voice.sql, enter the password to access.

Thank you for reading, I hope it can help everyone, thank you for your support to this site!