Shulou(Shulou.com)06/01 Report--

This article mainly introduces what commonly used commands mysql has, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand what commonly used mysql commands bar.

One authorized login

Referenc

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

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

GRANT ALL PRIVILEGES ON cacti.* TO 'hnf@'localhost' IDENTIFIED BY' hnf@2014'; # # only grants grant all on to the cacti database *. * to 'root'@'localhost' identified by' huningfei'; # # only allows local connection to the database grant all on *. * to 'root'@'%'identified by' password'; # allows any host to connect to the database grant all on *. * to dba@'localhost' # # dba user to manage all databases

Second, set the 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 root users are changed) include; root@% root@localhost, 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

3. Cancel authorization and delete users

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

View the permissions of a specific user in the database

Mysql > show grants for 'cactiuser'@'%'

2 cancellation of authorization

MySQL revokes privileges and deletes users as administrators, and now that you can create users and authorizations, you can also unauthorize and delete users. To revoke the permissions of a user, you can 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 to be revoked. Example: the following code implements the function of revoking 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 deleting a user

The REVOKE statement can only revoke the user's permissions, not delete the user. Even if all permissions are removed, 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"

Delete the user sss using DELETE, as follows:

Mysql > use mysqlDatabase changedmysql > delete from user where user='sss' and host='localhost'; mysql > flush privileges; Query OK, 1 row affected (0.02 sec)

4 modify the search engine of mysql

1 check the mysql storage engine command and enter the show engines; field under the mysql > prompt. Support: Default indicates the default storage engine.

2. Set InnoDB as the default engine: add the sentence 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

5 check the number of ip of mysql links

1 netstat-an | grep ESTABLISHED | grep 3306 | awk {'print $5'} | sed's Univer.

2 mysql-uroot-p-e "show processlist\ G;" | egrep "Host\:" | awk-F:'{print $2}'| sort | uniq-c # the user name that requires mysql input

Six additions, deletions, alterations and investigations

View database show databases

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

Toggle database use dbname

View the version of the database: select version ()

View table: show tables

View table details desc table name

View the show create table table name of the statement that created the table

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)

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

Update update db1.t1 set name='aaa' where id=1

Clear table truncate table db1.t1

Delete table drop table db1.t1

Delete database drop database db1

Repair table repair table tb1; (discuz.user repair user table in discuz library)

View mysql status show status

After reading the above commonly used commands about mysql, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.