MySQL Common commands

1, Connection MySQL format: mysql-h host address-u user name-P user Password

1. Connect to MySQL on this machine.
First open the DOS window, then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after the prompt you to lose the password. Note that the user name can have a space or no space, but before the password must have no space, or let you re-enter the password.

If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt is: mysql>

2. Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:
Mysql-h110.110.110.110-u Root-p 123; (Note: You can not add a space between the root and the other)

3. Quit MySQL command: Exit (Enter) 2, change password format: mysqladmin-u user Name----old password password new password

1, add a password to root ab12.
First enter directory Mysql\bin under DOS, and then type the following command
Mysqladmin-u Root-password AB12
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

2, then change the root password to djg345.
Mysqladmin-u root-p ab12 Password djg345 2, database backup

Command executed in DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] Directory

1. Export the entire database
The export file is present in the Mysql\bin directory by default
Mysqldump-u user name-p database name > exported file name
Mysqldump-u user_name-p123456 database_name > Outfile_name.sql

2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
MYSQLDUMP-U USER_NAME-P database_name table_name > OUTFILE_NAME.SQL

3. Export a database structure
Mysqldump-u user_name-p-d–add-drop-table database_name > Outfile_name.sql
-D No data –add-drop-table add a drop table before each CREATE statement

4. Export with language parameters
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql

For example, back up the AAA library to file Back_aaa:
[Email protected] root]# Cd/home/data/mysql
[Email protected] mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

5. Importing database Tables
(1) Create a. sql file
(2) First generate a library such as Auction.c:mysqlbin>mysqladmin-u root-p creat auction, you will be prompted to enter a password, and then successfully created.
(2) Import Auction.sql file
C:mysqlbin>mysql-u Root-p Auction < Auction.sql.
By doing this, you can create a database auction and one of the table auction.
6. Modify Database
(1) Add a field to the MySQL table:
ALTER TABLE dbname add column userid int (one) NOT null primary key auto_increment;
In this way, a field userid, type int (11), is added to table dbname.
7. Authorization for MySQL database
Mysql>grant Select,insert,delete,create,drop
On *. * (or test.*/user.*/.)
To User name @localhost
Identified by ' password ';
Such as: Create a new user account so that you can access the database, you need to do the following:
Mysql> Grant Usage
-On test.*
→[email protected];
Query OK, 0 rows affected (0.15 sec)
Since then, a new user has been created called: TestUser, the user can only connect to the database from localhost and connect to the test database. Next, we must specify what testuser this user can do:
mysql> GRANT Select, INSERT, Delete,update
-On test.*
→[email protected];
Query OK, 0 rows Affected (0.00 sec)
This operation enables TestUser to perform Select,insert and delete and update query operations on the tables in each test database. Now we end the operation and exit the MySQL client program:
Mysql> exit
bye9!

5. Importing database Tables
(1) Create a. sql file
(2) first generate a library such as Auction.c:mysqlbin>mysqladmin-u root-p creat auction, you will be prompted to enter a password, and then successfully created.
(2) Import Auction.sql file
c:mysqlbin>mysql-u root-p Auction < Auction.sql.
by doing this, you can create a database auction and one of the table auction.
6. Modify Database
(1) Add a field to the MySQL table:
ALTER TABLE dbname add column userid int (one) NOT null primary key auto_increment;
in this way, a field userid, type int (11), is added to table dbname.
7. Authorization for MySQL database
mysql>grant Select,insert,delete,create,drop
On *. * (or test.*/user.*/.)
To user name @localhost
identified by ' password ';
such as: Create a new user account so that you can access the database, you need to do the following:
mysql> Grant Usage
-On test.*
→[email protected]; 
Query OK, 0 rows affected (0.15 sec)
Since then, a new user has been created called: TestUser, the user can only connect to the database from localhost and connect to the test database. Next, we must specify what testuser this user can do:
mysql> GRANT Select, insert, Delete,update
-On test.*
→[email protected]; 
Query OK, 0 rows Affected (0.00 sec)
This operation enables TestUser to perform Select,insert and delete and update query operations on the tables in each test database. Now we end the operation and exit the MySQL client program:
mysql> Exit
bye9!

MySQL Common commands