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

Mysql user Management create users and authorize

Specify login ip

Create an authorized new user using the root user login:

Mysql > grant all on. To 'user1'@'127.0.0.1' identified by' 123456'

/ / all all operations (add, delete, check and modify)

/ / the first one matches all the library names, and the second one matches all table names.

/ / user1 is the user name

/ / 127.0.0.1 specifies the login ip, and the wildcard% is available for all ip.

/ / '123456' is the login password of the user1 user

Query OK, 0 rows affected (0.00 sec)

Log in using the user1 user and verify:

[root@localhost] # mysql-uuser1-p

Enter password:

ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

/ / login failed because mysql uses socket login by default. An address needs to be specified.

[root@localhost] # mysql-uuser1-h227.0.0.1-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

.

Mysql >

/ / login succeeded. Where the grant statement is not recorded in the command history.

Specify login socket

Use the root user to re-authorize the user1 user:

Mysql > grant all on. To 'user1'@'localhost' identified by' 123456'

Log out of root user and log in as user1 user:

[root@localhost] # mysql-uuser1-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

.

Mysql >

/ / because the specified login host is localhost, the user uses (listens) the local mysql.socket file by default and does not need to specify IP to log in.

Log in with the root user for specific authorization: mysql > grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.159.132' identified by' 123456 create the user2 user and authorize it to have SELECT,UPDATE,INSERT permissions for all tables in the db1 library. / / user2 users can only log in through ip 192.168.159.132. Query OK, 0 rows affected (0.00 sec) View user authorization information: mysql > show grants;// view current user authorization information mysql > show grants for user1@127.0.0.1;// view specified user authorization information change authorization

Usage: user2 users need to log in not only on 192.168.159.132, but also on 192.168.159.133. At this time, all authorized commands need to be executed again.

Check what permissions user2 has: mysql > show grants for user2@'192.168.159.132' +-+ | Grants for User2@192.168.159.132 | + -- + | GRANT USAGE ON *. * TO 'user2'@'192.168.159.132' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | GRANT SELECT INSERT UPDATE ON `db1`. * TO 'user2'@'192.168.159.132' +- -+ 2 rows in set (0.00 sec) execute the authorization command of user2: / / change ip to 192.168.159.133mysql > GRANT USAGE ON *. * TO 'user2'@'192.168.159.132' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'mysql > GRANT SELECT INSERT, UPDATE ON `db1`. * TO 'user2'@'192.168.159.132' Check the authorization of user2@192.168.159.133: mysql > show grants for user2@'192.168.159.133' +-+ | Grants for User2@192.168.159.133 | + -- + | GRANT USAGE ON *. * TO 'user2'@'192.168.159.133' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | GRANT SELECT INSERT UPDATE ON `db1`. * TO 'user2'@'192.168.159.133' +- -+ 2 rows in set (0.00 sec) / / after successful authorization User3 users can operate on db1 at 192.168.159.133. (if you cannot link remotely, check the following firewall rules. ) / / user2 logs in on both ip using a password. / / when re-authorization, the contents of the permission line and the library table of the operation can be modified. The commonly used SQL statement first uses the mysql user to log in to mysqlmysql > use db1; / / to switch to the db1 library Database changedmysql > show tables; / / to see which tables are in the current library. +-+ | Tables_in_db1 | +-+ | T1 | +-+ 1 row in set (0.00 sec) mysql > select count (*) from mysql.user / / check the user table in the mysql library with several rows +-+ | count (*) | +-+ | 15 | +-+ 1 row in set (0.00 sec) mysql > select * from mysql.db\ G / / View all the contents in the db table of the mysql library / / * indicates all the contents \ G is to make the output content neat * * 1. Row * * Host:% Db: test User: Select_priv: y Insert_priv : Y Update_priv: Y Delete_priv: YCreate_ priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: YCreate_ view_priv: Y Show_view_priv: YCreate_ routine_priv: Y Alter_routine_priv: n Execute_priv: n Event_priv: y Trigger_priv: Y.Universe / try not to use this operation at ordinary times When there is a lot of content, select* will take up more resources. Mysql > select db from mysql.db;// check the db field. In the db table of mysql library, +-+ | db | +-+ | test | | test\ _% | | db1 | | db1 | db1 | +-+ 8 rows in set (0.00 sec) mysql > select db,user from mysql.db / / View the db and user fields Separate the fields in the db table of mysql library with commas +-+-+ | db | user | +-+-+ | test | test\ _% | db1 | user2 | | db1 | db1 | | db1 | user4 | db1 | user2 | | db1 | user3 | | db1 | user2 | +-| -+-+ 8 rows in set (0.00 sec) mysql > select * from mysql.db where host like '192.168%'\ G / / View all IP matches 192.168.%, from the db table of the mysql library / / like match * * 1. Row * * Host: 192.168.159.131 Db: db1 User: user2 Select_priv: y Insert_priv: y Update _ priv: Y Delete_priv: NCreate_ priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: NCreate_tmp_table_priv: N Lock_tables_priv: NCreate_ view_priv: N Show_view_priv: NCreate_ routine_priv: N Alter_routine_priv: N Execute_priv: n Event_priv: n Trigger_priv: N.mysql > create table T1 (`id` int (4) `name` char (40)) / / create a table under the db1 library: ti// has two fields, Query OK, id and name, 0 rows affected (0.39 sec) mysql > insert into db1.t1 values (1 Query OK); / / add a row of data Query OK to T1 table, 1 row affected (0.00 sec) mysql > select * from db1.t1 / / View the contents of T1 table +-+-+ | id | name | +-+-+ | 1 | abc | +-+-+ 1 row in set (0.00 sec) / / when adding data, try to add single quotation marks to the string. Mysql > update db1.t1 set name='aaa' where id=1;// updates the contents of the row name in the table where id is 1. Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from db1.t1;+-+-+ | id | name | +-+-+ | 1 | aaa | +-+-+ 1 row in set (0.00 sec) mysql > delete from db1.t1 where id=1 / delete the contents of the row whose id is 1 in the table Query OK, 1 row affected (0.00 sec) mysql > select * from db1.t1;Empty set (0.00 sec) mysql > truncate db1.t1; / / clear the contents of the table. The table structure is also in Query OK, 0 rows affected (0 09 sec) mysql > drop table T1 impact / delete the table, including content and table structure Query OK, 0 rows affected (0 04 sec) mysql > drop database db1 / / delete the library, delete the entire library, including table Query OK, 0 rows affected (0.13 sec) / / use truncate and drop as little as possible. Mysql > use mysql;mysql > delete from user where User='user1' and Host='127.0.0.1';// delete users. Before deleting users, you need to specify the user table Query OK, 1 row affected (0.06 sec) mysql database backup and restore

Backup recovery library

[root@localhost] # mysqldump-uroot-p123456 mysql > / tmp/mysqlbak.sql

/ / back up the specified library (mysql library) to the / tmp/mysqlbak.sql file

[root@localhost] # mysqldump-uroot-p123456-A > / rmp/mysqlbak_all.sql

/ / back up all libraries to / tmp/mysqbak_all.sql file

[root@localhost] # mysql-uroot-p123456 mysql

/ tmp/user.sql

/ / back up the specified table. Just follow the table name with the space after the library name.

[root@localhost] # mysqldump-uroot-p123456-d mysql > / tmp/mysql_tb.sql

/ / backup list structure only

[root@localhost] # mysql-uroot-p123456 mysql < / tmp/user.sql

/ / No table name is required during recovery

/ / it is OK to back up a small amount of data with mysqldump, but a large amount of backup is not suitable.

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.