Some commonly used commands in mysql

One authorized login

GRANT ALL PRIVILEGES ON cacti.* TO 'hnf@'localhost' IDENTIFIED BY  'hnf@2014';  ##只给cacti这个数据库授权
grant all on *.* to 'root'@'localhost' identified by 'huningfei';   ##只允许本地连接数据库
grant all on *.* to 'root'@'%'identified by 'password';  ##允许任何主机连接数据库
grant all on *.* to dba@'localhost'  ##dba用户管理所有数据库的权限

Second set mysql login password

The first method:

mysqladmin -uroot password 'huningfei' (not run after logging in to mysql)

The second method: (As long as all the passwords of the root user are changed) including; root@% root@localhost, etc.

Use UPDATE to edit the user table directly

    mysql -u root
　　mysql> use mysql;
　　mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
　　mysql> FLUSH PRIVILEGES;

Three Cancel authorization and delete users

1 How to check authorization All users

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

View specific details in the database Permissions of a certain user

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

2 Cancel authorization

MySQL cancel permissions and delete users As an administrator, since you can create users and authorize, you can also cancel authorization and delete users. To revoke a user's permissions, use the REVOKE statement. The syntax format of this statement is as follows:

Revoke privileges (columns) on what from user ;

where privileges is the permission to be revoked and user is the user name whose permission is to be revoked. Example: The following code implements the function of canceling all permissions of the sss user on the localhost machine.

> revoke all on *.* from sss@localhost ;
Query OK, 0 rows affected (0.00 sec)

3 Delete user

The REVOKE statement can only revoke the user's permissions, but cannot delete the user. Even if all permissions are revoked, users can still connect to the server. To completely delete a user, you must use the DELETE statement to delete the user's record from the user table in the MySQL database. The syntax format of 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 user sss, the code is as follows:

mysql> use mysql
Database changed
mysql> delete from user where user='s ss' and host= 'localhost' ;
mysql>flush privileges ;
Query OK, 1 row affected (0.02 sec)

Four Modify the mysql search engine

1 View the mysql storage engine command and enter it at the mysql> prompt show engines; the field Support is: Default means the default storage engine
2. Set InnoDB as the default engine: Add default-storage-engine=INNODB under [mysqld] in the configuration file my.cnf

3. Restart the mysql server: mysqladmin -u root -p shutdown or service mysqld restart Log in to the mysql database,

五 View the number of IPs connected to mysql

1 netstat -an | grep ESTABLISHED | grep 3306 | awk {'print $5'}|sed ' s/:.*$//g'

2 mysql -uroot -p -e"show processlistG;"| egrep "Host:" | awk -F: '{ print $2 }'| sort | uniq - c ##This requires entering the user name of mysql

Six additions, deletions, modifications and checks

View databases show databases

Check which database you are in now: select database();

Switch database use dbname

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

View the table: show tables

View the details of the table desc table name

View the statement to create the table show create table table name

Create a database: create database hu;

Create table: create table tb1 (`id` int(4),`name` char(40))

Insert data into the table: insert into tb1 values ​​(2,'hu');

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

update update db1.t1 set name='aaa' where id=1;
Clear the table truncate table db1.t1;
delete table drop table db1.t1;
delete database drop database db1;
Repair table repair table tb1; (discuz.user repairs the user table in the discuz library)

View mysql status show status;