MySQL import. sql files and Common commands

Source: Internet
Author: User
Tags mysql host mysql import

MySQL import. sql files and Common commands

To import *.sql scripts directly in MySQL Qurey Brower, is the command to execute SQL files in MySQL without executing multiple SQL commands at once:

Mysql> source D:/myprogram/database/db.sql;

Also attached to MySQL common commands:

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 (remote: IP address) on the 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 [email 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;

Export SQL Script

Mysqldump-u user name-p database name > storage location

Mysqldump-u root-p Test > C:/a.sql

Import SQL Script

Mysql-u user name-p database name < storage location

Mysqljump-u root-p Test < C:/a.sql

Note that the test database must already exist

MySQL export use case for import 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

1. Log in to MySQL:
Mysql-u root-p
Password: Enter password
2. View user Information
Select User,host,password from Mysql.user;
Select User,host from Mysql.user;
3. Set the password
Set password for [email Protected]=password (' Enter root password here ');
4. Change the password
Method 1:mysqladmin-u root-p Password NewPassword
Method 2: #mysql-u root-p MySQL
Mysql>update user SET Password=password ("new") WHERE user= ' root ';
Mysql>flush privileges;
5. Delete anonymous Users
Delete from mysql.user where user= ';
6. View the database that the system already exists
show databases;
7. Delete the empty database named Test
drop database test;
8. Build MySQL User
Example A: Establish a user named Centospub that has full operational permissions on the test database
Mysql>grant all privileges in test.* to [e-mail protected] identified by ' password ';
Example B: Add a user test1 password to 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:
Mysql>grant select,insert,update,delete On * * to [e-mail protected] "%" identified by "ABC";
But example B added to 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 into your MySQL database and your data can do whatever you like.
Example C: Add a user test2 password for ABC, so that he can only login on localhost, and can query, insert, modify, delete the database mydb (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.
Mysql>grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "ABC";
9. Find and confirm the presence or absence of centospub users
Select User from Mysql.user where user= ' centospub ';
10. Build a database named Test
Create DATABASE test;
11, cancel the Centospub user to the database operation permission
Revoke all privileges on * * FROM [email protected];
12. Delete Centospub Users
Delete from Mysql.user where user= ' centospub ' and host= ' localhost ';
13. Refresh to make the changes take effect
Flush privileges;
14, forget the root password of MySQL, how to modify


If MySQL is running, first kill it: Killall-term mysqld.
Start Mysql:path_to_mysql/bin/mysqld--skip-grant-tables &
You can go to MySQL without a password.
Then there is
Mysql>use MySQL
Mysql>update User Set Password=password ("New_pass") where user= "root";
Mysql>flush privileges;
Kill MySQL again and start MySQL in a normal way
Note: Many novices do not use Password=password ("..."), but direct password= "..." so getting rid of the code is bad.

MySQL import. sql files and Common commands

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.