How to import and export with MySQL dump

How to import and export with MySQL dumpCategory: Database 2009-12-08 00:04 6825 People read comments (0) favorite reports MySQL database deleteinsertinternetdatabase

MySQL Utility commands
Wikipedia, the free encyclopedia
A) connect to 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 into the MySQL installation directory under the bin directory, for example: D:/mysql/bin, and then type the command mysql-uroot-p, enter after the prompt to lose the password, if just installed MySQL, superuser root is no password, So the direct return to enter the MySQL, MySQL, the prompt is:mysql>
2. Example 2: Connect to MySQL on a remote host
Assume that the remote host IP is: 10.0.0.1, the user name is root, the password is 123. Type the following command:
Mysql-h10.0.0.1-uroot-p123
(Note: You and root can be used without spaces, others are the same)
3. Quit MySQL Command
Exit (Enter)

(b) Change the password:
Format: Mysqladmin-u username-P Old password password new password
1, Example 1: Add a password to root 123. First enter directory C:/mysql/bin under DOS, and then type the following command:
Mysqladmin-uroot-password 123
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
2, Example 2: Then change the root password to 456
MYSQLADMIN-UROOT-PAB12 Password 456
(c) Add new users: (Note: Unlike the above, because it is a command in the MySQL environment, it is followed by a semicolon as a command terminator)
Format: Grant Select on database. * To User name @ login host identified by "password"
Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:
Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC";

But example 1 increases the user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log on your MySQL database and to your data can do whatever, workaround see Example 2.
Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";
(iv) Display of commands
1. Display the database list:
show databases;
Just started with two databases: MySQL and test. MySQL Library is very important it has the MySQL system information, we change the password and the new user, is actually using this library to operate.
2. Display the data table in the library:
use MySQL;//Open Library
Show tables;
3, display the structure of the data table:
describe table name;
4, build the library:
Create database name;
5, build the table:
Use library name;
CREATE TABLE table name (field settings list);
6. Deleting the library and deleting the table:
drop database name;
drop table name;
7. Empty the records in the table:
Delete from table name;
8. Display the records in the table:
SELECT * from table name;



MySQL Import Export command
1. Export the entire database
Mysqldump-u user name-p database name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql
2. Export a table
Mysqldump-u user name-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
Go to MySQL Database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (for example, the. SQL used here)
Mysql>source D:wcnc_db.sql (Note: syntax error will be reported if written as source D:/wcnc_db.sql)
Note: Database files using the Import Database command must be in the bin directory of MySQL

Methods for importing and exporting with MySQL dump