MySQL command line backup/restoration or Import/Export Database

Because of historical and Encoding Problems (garbled characters ). Garbled characters are found in the generated database SQL file, which isolates some single quotes, leading to syntax errors. Client software such as SQLYog cannot be restored. You can use the command line method. This article mainly introduces a tool named mysqldump under the bin directory of the mysql installation directory:

1. Export data

1. D:/Program Files/MySQL Server5.1/Bin> mysqldump-u root-p user_test> user_test.txt

-U {user name}-p {database name}> {exported file name} after executing the command, a prompt will be displayed for entering the password. After the command is successful, the user_test.txt file will be generated under the bindirectory, or the directory can be specified.

2. Restore the database to another location

Here, we use a mysql command instead of mysqlimport. Or in the bin directory, directly execute the following command:

1. D:/Program Files/MySQL Server5.1/Bin> mysql-u root-p user_test <user_test.txt

You will also be prompted to enter the password.

Refer:

HOw do I import data stored in a. SQL file (created by mysqldump command) under UNIX or Linux operating systems?

The syntax is as follows to import the data created by mysqldump command:

1. Mysql-u {DB-USER-NAME}-p {DB-NAME} <{db. file. SQL}
2. Mysql-u {DB-USER-NAME}-h {MySQL-SERVER-HOST-NAME}-p {DB-NAME} <{db. file. SQL}

In this example import a file called sales. SQL for salesdb1 user and sales db, enter:

1. $ Mysql-u sales-p salesdb1 <sales. SQL

If database salesdb1 does not exists, first create it and than import it as follows:

1. $ Mysql-u root-p-e'Create database salesdb1'
2. $ Mysql-u sales-p salesdb1 <sales. SQL