Some common commands for MySQL

An authorized Login

Reference

http://blog.csdn.net/andy_yf/article/details/7487519

Http://www.2cto.com/database/201303/195876.html

GRANT all privileges on cacti.* to ' [e-mail protected] ' localhost ' identified by ' [email protected] ';   # #只给cacti这个数据库授权grant All on * * to ' root ' @ ' localhost ' identified by ' Huningfei ';  # #只允许本地连接数据库grant All on * * to ' root ' @ '% ' identified by ' password '; # #允许任何主机连接数据库grant All on * * to [email protected] ' localhost ' # #dba用户管理所有数据库的权限

Two set MySQL login password

The first method:

Mysqladmin-uroot password ' Huningfei ' (not running after logging in to MySQL)

The second method: (as long as the root user's password changes all) include; [Email protected]% [email protected], etc.

Edit the user table directly with update

Mysql-u root mysql> use MySQL;　　mysql> UPDATE user SET Password = Password (' newpass ') WHERE user = ' root '; mysql> FLUSH privileges;

Three cancel the authorization and delete the user

Reference http://www.cnblogs.com/wanghetao/p/3806888.html

1 How to view all authorized users

SELECT DISTINCT CONCAT (' User: ', user, ' @ ', host, '; ') as query from Mysql.user;

To view the permissions of a specific user in a database

mysql> Show grants for ' cactiuser ' @ '% ' ; 

2 Cancellation of Authorization

MySQL removes the permissions and deletes the user as an administrator, since the ability to create users and authorizations can also be used to cancel the authorization and deletion of users. To cancel a user's permissions, you can use the REVOKE statement. The syntax format for this statement is as follows:

Revoke Privileges (columns) on how from user;

Where privileges is the permission to be canceled, user is the username to be canceled. Example: The following code implements the ability to cancel all of the permissions of the SSS user on the localhost machine.

> Revoke all on * * from [email protected]; Query OK, 0 rows Affected (0.00 sec)

3 Deleting a user

The REVOKE statement can only cancel the user's permissions and not delete the user. Even if all permissions are canceled, the user can still connect to the server. To completely delete a user, you must use the DELETE statement to remove the user's records from the users table in the MySQL database. The syntax format for this statement is as follows:

Delete from user where user = "user_name" and host = "HOST_NAME"; Delete from user where user= "" and host= "localhost";

Use Delete to delete the user SSS with the following code:  

mysql> use mysqldatabase changedmysql> Delete from user where user= ' sss ' and host= ' localhost '; Mysql>flush privil Eges; Query OK, 1 row affected (0.02 sec)

Four changes to MySQL search engine

1 view MySQL Storage Engine command, enter show engines at mysql> prompt; Field Support: Default
2, set INNODB as the default engine: In the configuration file my.cnf [mysqld] under the Add DEFAULT-STORAGE-ENGINE=INNODB sentence

3. Restart MySQL server: mysqladmin-u root-p shutdown or service mysqld restart log in to MySQL database,

Five view MySQL link IP number

1 Netstat-an | grep established |grep 3306 | awk {' Print $ '}|sed ' s/:.*$//g '

2 Mysql-uroot-p-E "show processlist\g;" | Egrep "Host\:" | Awk-f: ' {print $} ' | Sort | UNIQ-C # #这个需要输入mysql的用户名

Six additions and deletions to search

View Database Show databases

View under which database now: Select Database();

Switch database Use dbname

View the version of the database: Select version ();

View table: Show tables

View the details of a table DESC table name

View the statement for the build table name of the Show create table table

Creating a database: Create databases Hu;

Creating tables: Create TABLE TB1 (' id ' int (4), ' name ' char (40))

Table Insert data: INSERT INTO TB1 values (2, ' Hu ');

Delete a piece of data: Delete from db1 where name= ' 55 ';

updated update db1.t1 set name= ' AAA ' where id=1;
Empty tables truncate TABLE db1.t1;
Delete tables drop table db1.t1;
Delete database drop databases db1;
Fix Tables Repair table tb1 (discuz.user fix user table in Discuz library)

View MySQL status show status;

This article is from the "Drifting Away" blog, make sure to keep this source http://825536458.blog.51cto.com/4417836/1878134

Some common commands for MySQL