MySql Import and Export Database (Including Remote)

1. Import and export local database
Export:
1. Run cmd first, cd to the bin folder in the mysql installation directory
2. mysqldump -u root -p database name> export file name.sql

In other cases:
1. Export the entire database
mysqldump -u username -p database name> exported file name
mysqldump -u wcnc -p smgp_apps_wcnc> wcnc.sql
2. Export a table
mysqldump -u username -p database name table name> exported file name
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3. Export a database structure
mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:\wcnc_db.sql
-d no data --add-drop-table add a drop table before each create statement

Import:
1. Enter sql under the dos command: first create database database name;
2. Use database;
3. Source c:\....\file name.sql, no need to add a semicolon after it

Two, remote import and export databases (or tables)
The remote export syntax is:
mysqldump -h[hosname] -u[user_name] -p[password] --default-character-set=[char_set_name] [db_name]> [save_path]
Example: Then enter: mysqldump -h119.12.12.11 -umysql-pmysql123--default-character-set=utf8 aspchina --skip-lock-tables> d:\aspchina_net.sql

You can also: mysqldump -h119.12.12.11 -u mysql> d:\aspchina_net.sql
119.12.12.11 is the remote server IP, -umysql mysql is the database user name, -pmysql123 (no space interval) mysql123 is the user password, set=utf8 is the encoding format for exporting MYSQL, aspchina is the name of the database to be exported, >d:\ aspchina_net.sql is the storage path imported to your local, aspchina_net.sql you can name it freely!

Remote database import:
If the MYSQL database is less than 2MB, you can use the mysqldump management tool to import it. If the size is 2MB, it will not work, because the PHPMYADMIN management tool provided by the space provider can only import data less than 2MB, which makes some webmasters who use the MYSQL database depressed!
Now I will teach you how to use the mysqldump management tool to import data larger than 2MB.
1) Start menu in the lower left corner -> Run -> CMD enter the DOS command line state
2) D:\Program Files\MySQL\MySQL Server 5.0\bin> is the MYSQL installation directory you installed, \bin is the directory where the mysqldump management tool is located;
3) Then enter: mysql -h119.12.12.11 -uaspchina -paspchina123456 aspchina< d:\aspchina_net.sql
Note: aspchina_net.sql, if the user does not have the permission to create a database, he will not be able to import the aspchina_net.sql database, otherwise spchina_net.sql can only be multiple tables, otherwise an error will occur, remember this!

MySQL import .sql files and common commands
Importing *.sql scripts directly into MySQL Qurey Brower cannot execute multiple sql commands at one time, and execute sql file commands in MySQL:
mysql> source d:/myprogram/database/db.sql;

Attached mysql commonly used commands:

One) Connect MYSQL:

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

1. Example 1: Connect to MYSQL on this machine

First, open the DOS window, and then enter the bin directory under the mysql installation directory, for example: D:/mysql/bin, and then type the command mysql -uroot -p, press Enter and you will be prompted to enter the password. If MYSQL has just been installed, super The user root does not have a password, so you can enter MYSQL directly by pressing Enter. The prompt of MYSQL is: mysql>

2. Example 2: Connect to MYSQL on the remote host (remote: IP address)

Assume that the IP of the remote host is 10.0.0.1, the username is root, and the password is 123. Then type the following command:

mysql -h10.0.0.1 -uroot -p123

(Note: u and root do not need to add spaces, the other is the same)

3. Exit the MYSQL command

exit (Enter)

(2) Change password:

Format: mysqladmin -u username -p old password password new password

1. Example 1: Add a password of 123 to root. First enter the directory C:/mysql/bin under DOS, and then type the following command:

mysqladmin -uroot -password 123

Note: Because root has no password at the beginning, the -p old password can be omitted.

2. Example 2: Change the password of root to 456

mysqladmin -uroot -pab12 password 456

(3) Add a new user: (Note: Unlike the above, the following is a command in the MYSQL environment, so there is a semicolon as the end of the command)

Format: grant select on database.* to username@Login host identified by "password"

Example 1. Add the password of a user test1 to abc, so that he can log in on any host and have query, insert, modify, and delete permissions for all databases. First connect to MYSQL as the root user, and then type the following command: grant select,insert,update,delete on *.* to test2@localhost identified by "abc";

If you don't want test2 to have a password, you can delete the password by typing another command. grant select,insert,update,delete on mydb.* to test2@localhost identified by "";

(4) Display commands

1. Display the database list:

show databases; There were only two databases at the beginning: mysql and test. The mysql library is very important. It contains MYSQL system information. When we change the password and add users, we actually use this library for operations.

2. Display the data table in the library:

use mysql; //Open the library show tables;

3. Display the structure of the data table:

describe table name;

4. Build a library:

create database library name;

5. Build a table:

use library name; create table table name (list of field settings);

6. Delete database and delete table:

drop database library name; drop table table name;

7. Clear the records in the table:

delete from table name;

8. Display the records in the table:

select * from table name;

Export sql script

mysqldump -u username -p database name> storage location

mysqldump -u root -p test> c:/a.sql

Import sql script

mysql -u username -p database name <storage location

mysqljump -u root -p test <c:/a.sql

Note that the test database must already exist

Use cases of MySQL export and import commands

1. Export the entire database

mysqldump -u username -p database name> exported file name

mysqldump -u wcnc -p smgp_apps_wcnc> wcnc.sql

2. Export a table

mysqldump -u username -p database name table name> exported file name

mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql

3. Export a database structure

mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:wcnc_db.sql

-d no data --add-drop-table add a drop table before each create statement

4. Import the database

Common source commands

Enter the mysql database console,

Such as mysql -u root -p

mysql>use database

Then use the source command, the following parameters are script files (such as the .sql used here)

mysql>source d:wcnc_db.sql