mysql import sql file command and mysql remote login using detail

Direct import of *.sql script in MySQL Qurey Brower cannot execute more than one sql command at a time. Execute the command of sql file in mysql:

mysql> source   d:/myprogram/database/db.sql;

mysql common commands are attached:

1) Connection MYSQL:

Format: ES18en-ES19en host address -u username -p user password

1. Example 1: MYSQL connected to this machine

First open the DOS window, then enter the bin directory under the mysql installation directory, for example: D:/mysql/bin, and then type the command ES33en-ES34en-ES35en, and when you enter, you will be prompted to enter the password. If you have just installed MYSQL, the super user root does not have a password >

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

Suppose the remote host IP is 10.0.0.1, the user name is root, and the password is 123. Type the following command:

mysql -h10.0.0.1 -uroot -p123

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

3, exit MYSQL command

exit (Enter)

(2) Change password:

Format: ES66en-ES67en Username -p Old password password new password

1. Example 1: Add password 123 to root. First under DOS go to the directory C:/mysql/bin and type the following command:

mysqladmin -uroot -password 123

Note: Since root did not have a password at the beginning, one item of the old -ES82en password can be omitted.

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

mysqladmin -uroot -pab12 password 456

(3) Add new users: (Note: different from the above, the following commands in MYSQL environment are followed by a semicolon as the end of the command)

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

Example 1: Add a user test1 whose password is abc, so that he can log in on any host and have the permission to query, insert, modify and delete all databases. To root users connected to MYSQL first, 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 type another command to cancel the password. grant select insert, update, delete on mydb. * to test2 @ localhost identified by "";

(4) Display command

1. Display database list:

show databases; We started with two databases: mysql and test. The mysql library is very important because it has MYSQL's system information, and that's what we're actually using to change passwords and add new users.

2. Display the data table in the library:

use mysql ;  // Open the library show tables;

3. Structure of display data table:

describe  The name of the table ;

4. Library construction:

create database  The library ;

5. Table building:

use  The library;  create table  The name of the table  ( Field setting list ) ; 

6. Delete library and delete table:

drop database  The library ; drop table  The name of the table. 

7. Clear the records in the table:

delete from  The name of the table ;

8. Display the records in the table:

select * from  The name of the table ;

Export the sql script

mysqldump -u  The user name  -p  The database name  >  location 
mysqldump -u root -p test > c:/a.sql

Import the sql script

mysql -u  The user name  -p  The database name  <  location 
mysqljump -u root -p test < c:/a.sql

Note that the test database must already exist

MySQL exports the use case for the import command

1. Export the entire database

mysqldump -u  The user name  -p  The database name  >  Exported filename 
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2. Export a table

mysqldump -u  The user name  -p  Database table name >  Exported filename 
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql

3. Export 1 database structure

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

-d has no data -- ES208en-ES209en-ES210en adds one drop table before each create statement

4. Import database

source order is commonly used

Go to the mysql database console,

Such as ES226en-ES227en ES228en-ES229en

mysql > use database

Then use the source command, followed by a script file (as used here.sql)

mysql>source d:wcnc_db.sql