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

Mysql user Management 1. Create a regular user and authorize [root@gary-tao ~] # mysql-uroot-p'szyino-123'Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 24Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'c'to clear the current input statement.mysql > grant all on *. * to 'user1'@'127.0.0.1' identified by' szyino-123'; / / create a normal user and authorize Query OK, 0 rows affected (0.00 sec) usage explanation: grant: authorization; all: all permissions (such as read, write, query, delete, etc.);.: the former represents all databases, the latter represents all tables Identified by: followed by a password, enclosed in single quotation marks; 'user1'@'127.0.0.1': specifies IP to allow this user to log in, and this IP can be replaced by%, which means that all hosts are allowed to log in using this user; 2. Test login [root@gary-tao ~] # mysql-uuser1-pszyino-123 / / unable to log in to Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES) [root@gary-tao ~] # mysql-uuser1-pszyino-123-h227.0.0.1 / / add-h specified IP login due to the specified IP, normal Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 26Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'c'to clear the current input statement.mysql > mysql > grant all on *. * to 'user1'@'localhost' identified by' szyino-123'; / / authorizes localhost, so the user uses (listens) local mysql.socket files by default and can log in to Query OK without specifying IP. 0 rows affected (0.00 sec) mysql > ^ DBye [root@gary-tao ~] # mysql-uuser1-pszyino-123 / / normal login Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 28Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > 3. View all authorizations mysql > show grants +- -+ | Grants for root@localhost | +-- -+ | GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' IDENTIFIED BY PASSWORD' * B1E761CAD4A61F6FD6B02848B5973BC05DE1C315' WITH GRANT OPTION | | GRANT PROXY ON'@''TO 'root'@'localhost' WITH GRANT OPTION | +-- -+ 2 rows in set (0.00 sec) 4. Specify the user to view authorization mysql > show grants for user1@'127.0.0.1' +- + | Grants for user1@127.0.0.1 | + -- + | GRANT ALL PRIVILEGES ON *. * TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD' * B1E761CAD4A61F6FD6B02848B5973BC05DE1C315' | +-- -+ 1 row in set (0.00 sec) Note: suppose you want to authorize the same user to add a computer IP access authorization You can directly copy the query user authorization file, execute one command before the second, and change the IP when you execute it, so that you can log in on another computer with the same user password. Commonly used SQL statement 1. The most common query statement

The first form:

Mysql > use db1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select count (*) from mysql.user; +-+ | count (*) | +-+ | 8 | +-+ 1 row in set (0.00 sec) / / comment: mysql.user represents the user table of mysql, and count (*) indicates how many rows there are in the table.

The second form:

Mysql > select * from mysql.db;// it means to query all data in the db table of mysql library mysql > select db from mysql.db;+-+ | db | +-+ | test | | test\ _% | +-+ 2 rows in set (0.00 sec) / / query db single field mysql > select db,user from mysql.db in db table +-+-+ | db | user | +-+-+ | test | test\ _% | | +-+-+ 2 rows in set (0.00 sec) / / check the db,user fields in the db table mysql > select * from mysql.db where host like '192.168%'\ Gripple / query the db table about 192.168. Ip information of the segment 2. Insert a row 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.00 sec) mysql > insert into db1.t1 values (1, 'abc'); / / insert a row of data Query OK, 1 row affected (0.01 sec) mysql > select * from db1.t1;+-+-+ | id | name | +-+ | 1 | abc | +-+-+ 1 row in set (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 | +-+-+ 2 rows in set (0.00 sec) 3. Change the row of the table. Mysql > update db1.t1 set name='aaa' where id=1;Query OK, 2 rows affected Rows matched: 2 Changed: 2 Warnings: 0mysql > select * from db1.t1;+-+-+ | id | name | +-+-+ | 1 | aaa | | 1 | aaa | +-+-+ 2 rows in set (0.00 sec) 4. Clear a table's data mysql > truncate table db1.t1; / / clear table Query OK, 0 rows affected (0.03 sec) mysql > select * from db1.t1;Empty set (0.00 sec) 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) 5. Delete the table mysql > drop table db1.t1;Query OK, 0 rows affected (0.01 sec) mysql > select * from db1.t1;ERROR 1146 (42S02): Table 'db1.t1' doesn't exist6. Delete database mysql > drop database db1;Query OK, 0 rows affected (0.00 sec) mysql database backup and restore 1. Backup recovery library [root@gary-tao ~] # mysqldump-uroot-pszyino-123 mysql > / tmp/mysql.sql / / backup library Warning: Using a password on the command line interface can be insecure. [root@gary-tao ~] # mysql-uroot-pszyino-123-e "create database mysql2" / / create a new library Warning: Using a password on the command line interface can be insecure. [root@gary-tao ~] # mysql-uroot-pszyino-123 mysql2

< /tmp/mysql.sql //恢复一个库Warning: Using a password on the command line interface can be insecure.[root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2Warning: Using a password on the command line interface can be insecure.Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 38Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Select database (); +-+ | database () | +-+ | mysql2 | +-+ 1 row in set (0.00 sec) 2. Backup recovery table [root@gary-tao ~] # mysqldump-uroot-pszyino-123 mysql user > / tmp/user.sql / / backup table Warning: Using a password on the command line interface can be insecure. [root@gary-tao ~] # mysql-uroot-pszyino-123 mysql2

< /tmp/user.sql //恢复表Warning: Using a password on the command line interface can be insecure.3.备份所有库[root@gary-tao ~]# mysqldump -uroot -pszyino-123 -A >

/ tmp/mysql_all.sqlWarning: Using a password on the command line interface can be insecure. [root@gary-tao ~] # less / tmp/mysql_all.sql4. Only the backup table structure [root@gary-tao ~] # mysqldump-uroot-pszyino-123-d mysql > / tmp/mysql.sqlWarning: Using a password on the command line interface can be insecure.

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.