13.4-13.6 MySQL user management, common statements

13.4 MySQL User management

1 Creating User1 Users

Mysql> Grant All on * * to ' user1 ' @ ' 127.0.0.1 ' identified by ' 123456a ';

Query OK, 0 rows affected (0.01 sec)

Explain:

Grant all permissions

** XX library xx table, if it is MySQL library table can be represented as mysql.*

The @ ' 127.0.0.1 ' authorization specifies the source IP connection. All IP wildcard representations are @ '% ',% means all IP

2 connecting MySQL with User1

If you do not specify an IP connection, the following error occurs because the MySQL default socket connection, and User1 authorization specifies an IP connection.

[Email protected] ~]# mysql-uuser1-p123456a

Warning:using a password on the command line interface can is insecure.

ERROR 1045 (28000): Access denied for user ' user ' @ ' localhost ' (using Password:yes)

Correct connection method,

[Email protected] ~]# mysql-uuser1-p123456a-h127.0.0.1

Mysql>

2.1 Authorize localhost connection

Using root to enter MySQL changes,

[Email protected] ~]# Mysql-uroot-paminglinux

Mysql> Grant All on * * to ' user1 ' @ ' localhost ' identified by ' 123456a ';

2.2 localhost is equivalent to socket, so in this machine with User1 connection MySQL, direct connection

[Email protected] ~]# mysql-uuser1-p123456a

Mysql>

2.3 can also be targeted to set the user to give permission

Mysql> Grant Select,update,insert on db1.* to ' user2 ' @ ' 192.168.133.1 ' identified by ' passwd ';

3 Viewing permissions for a user

View permissions for the current user

Mysql> Show Grants

To view User1 permissions

Mysql> Show grants for [email protected] ' 127.0.0.1 ';

+-------------------------------------------------------------------------------------------------------------- ---------+

| Grants for [email protected] |

+-------------------------------------------------------------------------------------------------------------- ---------+

| GRANT all privileges on * * to ' user1 ' @ ' 127.0.0.1 ' identified by PASSWORD ' *b012e8731ff1df44f3d8b26837708985278c3ced ' |

+-------------------------------------------------------------------------------------------------------------- ---------+

3.1 Add User2, and for specified permissions

Mysql> Grant Select,update,insert on db1.* to ' user2 ' @ ' 172.18.171.157 ' identified by ' passwd ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show grants for [email protected] ' 172.18.171.157 '

;

+-------------------------------------------------------------------------------------------------------------- -----+

| Grants for [email protected] |

+-------------------------------------------------------------------------------------------------------------- -----+

| GRANT USAGE on *. user2 ' @ ' 172.18.171.157 ' identified by PASSWORD ' *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 ' |

|                                               GRANT SELECT, INSERT, UPDATE on ' db1 '. * to ' user2 ' @ ' 172.18.171.157 ' |

+-------------------------------------------------------------------------------------------------------------- -----+

2 rows in Set (0.00 sec)

3.2 Give the same permission when you do not know the user's password, for example to give 172.18.171.158 user2.

We can copy the information after show grants, change the IP address 172.18.171.158, and then execute it separately to give the same permission.

Mysql> GRANT USAGE on *. * to ' user2 ' @ ' 172.18.171.158 ' identified by PASSWORD ' * 59C70DA2F3E3A5BDDF46B68F5C8B8F25762BCCEF0 '

;

Query OK, 0 rows Affected (0.00 sec)

Mysql> GRANT SELECT, INSERT, UPDATE on ' db1 '. * to ' user2 ' @ ' 172.18.171.158 ';

Query OK, 0 rows Affected (0.00 sec)

3.3 See 158 of user rights information.

Mysql> Show grants for User2 @ ' 172.18.171.158 '

;

+-------------------------------------------------------------------------------------------------------------- -----+

| Grants for [email protected] |

+-------------------------------------------------------------------------------------------------------------- -----+

| GRANT USAGE on *. user2 ' @ ' 172.18.171.158 ' identified by PASSWORD ' *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 ' |

|                                               GRANT SELECT, INSERT, UPDATE on ' db1 '. * to ' user2 ' @ ' 172.18.171.158 ' |

+-------------------------------------------------------------------------------------------------------------- -----+

2 rows in Set (0.00 sec)

13.5 Common SQL statements

1 See how many rows are in the user table in the MySQL library

Select COUNT (*) from Mysql.user;

Mysql> Select COUNT (*) from Mysql.user;

+----------+

| COUNT (*) |

+----------+

| 11 |

+----------+

1 row in Set (0.00 sec)

COUNT (*) indicates how many rows are in the table, here is 11 rows.

This is with MyISAM engine, relative InnoDB will be relatively fast

2 View all the contents of the table;

Mysql> select * from Mysql.db\g;

Here's * This is used InnoDB engine will be relatively slow, so be careful to use

3 Querying data for single or multiple fields

Single

Select db from Mysql.db;

Mysql> Select db from Mysql.db;

+---------+

| db |

+---------+

| Test |

| test\_% |

| DB1 |

| DB1 |

| DB1 |

+---------+

5 rows in Set (0.00 sec)

Multiple

Select Db,user from Mysql.db;

Mysql> select Db,user from Mysql.db;

+---------+-------+

| db | user |

+---------+-------+

|       Test | |

|       test\_% | |

| DB1 | User2 |

| DB1 | User2 |

| DB1 | User2 |

+---------+-------+

5 rows in Set (0.00 sec)

4 Fuzzy Query

Mysql> SELECT * from mysql.db where host like ' 172.%.% ';

Can be displayed neatly with \g

Mysql> SELECT * from mysql.db where host like ' 172.%.% ' \g;

Where like is the role of fuzzy matching

5 INSERT statement

DB1.T1 is an empty table that uses this table to test.

mysql> desc DB1.T1;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| ID | Int (4) |     YES | |       NULL | |

| name | CHAR (40) |     YES | |       NULL | |

+-------+----------+------+-----+---------+-------+

2 rows in Set (0.00 sec)

Mysql> select * from Db1.t1;

Empty Set (0.01 sec)

Insert data in Db1.t1: Two fields (first field is ID, second field is name)

mysql> INSERT INTO DB1.T1 values (1, ' abc ');

Mysql> select * from Db1.t1;

+------+------+

| ID | name |

+------+------+

| 1 | ABC |

+------+------+

1 row in Set (0.00 sec)

You can see that 2 fields have been inserted, 1 and ABC, respectively.

When inserting data, it is important to note that the insertion string is best added ', the number can not be added '.

mysql> INSERT INTO DB1.T1 values (1, ' 234 ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into DB1.T1 values (1,234);

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from Db1.t1;

+------+------+

| ID | name |

+------+------+

| 1 | ABC |

| 1 | 234 |

| 1 | 234 |

+------+------+

3 Rows in Set (0.00 sec)

5.1 Changing the data of a row in a table

Update db1.t1 set name= ' AAA ' where id=1;

Mysql> select * from Db1.t1;

+------+------+

| ID | name |

+------+------+

| 1 | AAA |

| 1 | AAA |

| 1 | AAA |

+------+------+

3 rows in Set (0.01 sec)

Change db1.t1 inside match id=1 change name to AAA value

5.2 can also match the name change ID

mysql> Update db1.t1 set id=2 where name= ' AAA ';

Query OK, 3 rows affected (0.01 sec)

Rows Matched:3 Changed:3 warnings:0

Mysql> select * from Db1.t1;

+------+------+

| ID | name |

+------+------+

| 2 | AAA |

| 2 | AAA |

| 2 | AAA |

+------+------+

3 Rows in Set (0.00 sec)

6 Empty fields

Mysql> Delete from db1.t1 where id=2;

Mysql> select * from Db1.t1;

Empty Set (0.00 sec)

6.1 Empty table contents, table structure reserved

Mysql>truncate table db1.t1;

mysql> desc DB1.T1;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| ID | Int (4) |     YES | |       NULL | |

| name | CHAR (40) |     YES | |       NULL | |

+-------+----------+------+-----+---------+-------+

2 rows in Set (0.00 sec)

6.2 Completely delete a table

Mysql>drop table db1.t1;

mysql> desc DB1.T1;

ERROR 1146 (42S02): Table ' db1.t1 ' doesn ' t exist

6.3 Kill the Database

Drop database db1;

mysql> drop Database db1;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Use DB1

ERROR 1049 (42000): Unknown database ' DB1 '

Summarize:

Try to view content in a large database, with less than * in a table.

Use the delete function with caution.

13.4-13.6 MySQL user management, common statements