Example of MySQL import and export command

In the process of website migration, apart from packaging and transferring website files, the biggest headache is MySQL export and import. If the database is not large, it is very convenient to import and export PHPMyAdmin. Once the database is large, if the upload size and execution time exceed the PHP setting, it will be very troublesome to import and export data. In addition, the bandwidth is limited, and the upload speed is slow, and it will time out. If you have the SSH permission, you can directly use the SSH command line on the server to operate the 70-80 MB Database. With the wget command, it takes several seconds to complete and the speed is very fast.

MySQL export

Mysqldump -- add-drop-table-h mysql.ezloo.com-u username-p dbname> dbname. SQL

After the MySQL User password is entered correctly, the database will be exported to the current directory. If you are using a local machine, do not add the "-h mysql.ezloo.com" parameter. The mysqldump command has many other functions. For our website to be migrated, it is OK to export the command.

Example

1. Export the full database backup to the local directory
Mysqldump-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- routines -- default-character-set = utf8 -- lock-all-tables -- add-drop-database-A> db. all. SQL

2. Export the specified database to a local directory (for example, mysql database)
Mysqldump-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- routines -- default-character-set = utf8 -- databases mysql> db. SQL

3. Export the table of a database to a local directory (for example, the user table of the mysql database)
Mysqldump-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- routines -- default-character-set = utf8 -- tables mysql user> db. table. SQL

4. Export the table (data only) of the specified database to the local directory (for example, the user table of the mysql database with filtering conditions)
Mysqldump-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- routines -- default-character-set = utf8 -- no-create-db -- no-create-info -- tables mysql user -- where = "host = 'localhost'"> db. table. SQL

5. Export all table structures of a database
Mysqldump-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- routines -- default-character-set = utf8 -- no-data -- databases mysql> db. nodata. SQL

6. Export the data of a query SQL statement to a local directory in txt format (the data values are separated by "tabs)
For example, the SQL statement is 'Select user, host, password from mysql. user ;'
Mysql-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- default-character-set = utf8 -- skip-column-names-B-e' select user, host, password from mysql. user; '> mysql_user.txt

7. Export the data of a query SQL to a txt file on the MySQL server.
Log on to MySQL and replace the default tab with a comma (csv format file ).
Mysql must have the write permission for the specified path. It is best to use the tmp Directory. After the file is used up, delete it!
SELECT user, host, password FROM mysql. user into outfile '/tmp/mysql_user.csv 'Fields TERMINATED ',';

MySQL import

Mysql-h mysql.ezloo.com-u username-p dbname <dbname. SQL

The import syntax is similar to the export syntax. If you import data to the local machine, do not add the "-h mysql.ezloo.com" parameter. When the MySQL database is large enough, it is much more efficient to import than PHPMyAdmin.

Example
1. Restore the full database data to MySQL. Because the permission table of the mysql database is contained, run the flush privileges command to complete the import.
Method 1:
Mysql-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- default-character-set = utf8 <db. all. SQL

Method 2:
Log on to MySQL and run the source Command. The file name following the command must use the absolute path.
......
Mysql> source/tmp/db. all. SQL;
2. Restore the data of a database (mysql database user table)
Method 1:
Mysql-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- default-character-set = utf8 mysql <db. table. SQL

Method 2:
Log on to MySQL and run the source Command. The file name following the command must use the absolute path.
Mysql-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- default-character-set = utf8
......
Mysql> use mysql;
Mysql> source/tmp/db. table. SQL;
3. Restore the txt files on the MySQL server (FILE permission is required, and data values are separated by "tabs)
Mysql-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- default-character-set = utf8
......
Mysql> use mysql;
Mysql> load data infile '/tmp/mysql_user.txt' into table user;
4. Restore csv files on the MySQL server (FILE permission is required, and data values are separated by commas)
Mysql-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- default-character-set = utf8
......
Mysql> use mysql;
Mysql> load data infile '/tmp/mysql_user.csv' into table user fields terminated ',';
5. Restore local txt or csv files to MySQL
Mysql-u $ USER-p $ PASSWD-h127.0.0.1-P3306 -- default-character-set = utf8
......
Mysql> use mysql;
# Txt
Mysql> load data local infile '/tmp/mysql_user.csv' into table user;
# Csv
Mysql> load data local infile '/tmp/mysql_user.csv' into table user fields terminated ',';

MySQL backup and restoration in Linux

2.1 Backup

[Root @ localhost ~] # Cd/var/lib/mysql (go to the MySQL database directory and adjust the directory based on your MySQL installation)
[Root @ localhost mysql] # mysqldump-u root-p voice> voice. SQL. Enter the password.
2.2 restore

Method 1:

[Root @ localhost ~] # Mysql-u root-p Press enter and enter the password to go to the MySQL console "mysql>", which is restored in the same way as 1.2.

Method 2:

[Root @ localhost ~] # Cd/var/lib/mysql (go to the MySQL database directory and adjust the directory based on your MySQL installation)
[Root @ localhost mysql] # mysql-u root-p voice <voice. SQL, enter the password.