2018-05-08 Linux Learning

13.4 MySQL User management

MySQL Create user and authorize

grant all on *.* to ‘user1‘ identified by ‘passwd‘;grant SELECT,UPDATE,INSERT on db1.* to ‘user2‘@‘192.168.133.1‘ identified by ‘passwd‘;grant all on db1.* to ‘user3‘@‘%‘ identified by ‘passwd‘;show grants;show grants for [email protected];

Grant will not be logged in the command history, not secure.

Operation Process

Mysql> Grant All on * * to ' user1 ' @ ' 127.0.0.1 ' identified by ' 123456a '; Query OK, 0 rows Affected (0.00 sec) [[email protected] ~]# mysql-uuser1-p123456a-h127.0.0.1warning:using a passwo Rd on the command line interface can insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.mysql> grant all on * * to ' user1 ' @ ' localhost ' identified by ' 123456a '; Query OK, 0 rows Affected (0.00 sec) [[email protected] ~]# mysql-uuser1-p123456awarning:using a password on the CO Mmand line interface can is insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.mysql> Grant Select,update,insert on db1.* to ' user2 ' @ ' 192.168.106.1 ' identified by ' passwd '; Query OK, 0 rows Affected (0.00 sec) Mysql> Grant all on db1.* to ' user3 ' @ '% ' identified by ' passwd '; Query OK, 0 rows affected (0.01 sec) mysql> Show grants for [email protected] ' 192.168.106.1 '; +------------------- -----------------------------------------------------------------------------------------------+| Grants for [email protected] |+---- --------------------------------------------------------------------------------------------------------------+ | GRANT USAGE on *. user2 ' @ ' 192.168.106.1 ' identified by PASSWORD ' *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 ' | | GRANT SELECT, INSERT, UPDATE on ' db1 '. * to ' user2 ' @ ' 192.168.106.1 ' |+------- -----------------------------------------------------------------------------------------------------------+2 Rows in Set (0.01 sec)

Same user, same password, login on two different IP terminals

mysql> GRANT USAGE ON *.* TO ‘user2‘@‘192.168.106.2‘ IDENTIFIED BY PASSWORD ‘*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0‘;Query OK, 0 rows affected (0.00 sec)mysql> show grants for [email protected]‘192.168.106.2‘;

13.5 Common SQL statements

Select COUNT () from Mysql.user;
Select from Mysql.db;
Select db from Mysql.db;
Select Db,user from Mysql.db;
SELECT * from mysql.db where host like ' 192.168.% ';
INSERT into DB1.T1 values (1, ' abc ');
Update db1.t1 set name= ' AAA ' where id=1;
TRUNCATE TABLE db1.t1;
drop table db1.t1;
Drop database db1;

Operation Process

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(*) |+----------+|       12 |+----------+1 row in set (0.00 sec)mysql> select * from mysql.db;     //耗费资源大，不建议这样操作mysql> select db from mysql.db;+---------+| db      |+---------+| db1     || test    || test\_% || db1     |+---------+4 rows in set (0.00 sec)mysql> select db,user from mysql.db;+---------+-------+| db      | user  |+---------+-------+| db1     | user3 || test    |       || test\_% |       || db1     | user2 |+---------+-------+4 rows in set (0.00 sec)mysql> select * from mysql.db where host like ‘192.168.%‘;

Insert data: Insert data can be the same ID when no restrictions are made on the name

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‘);Query OK, 1 row affected (0.00 sec)mysql> insert into db1.t1 values (1, ‘123‘);Query OK, 1 row affected (0.00 sec)mysql> insert into db1.t1 values (1, ‘123‘);Query OK, 1 row affected (0.00 sec)mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | abc  ||    1 | abc  ||    1 | 123  |+------+------+3 rows in set (0.00 sec)

Update table

mysql> update db1.t1 set name=‘aaa‘ where id=1;Query OK, 3 rows affected (0.01 sec)Rows matched: 3  Changed: 3  Warnings: 0mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | aaa  ||    1 | aaa  ||    1 | aaa  |+------+------+3 rows in set (0.00 sec)mysql> update db1.t1 set id=2 where name=‘aaa‘;Query OK, 3 rows affected (0.00 sec)Rows matched: 3  Changed: 3  Warnings: 0mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    2 | aaa  ||    2 | aaa  ||    2 | aaa  |+------+------+3 rows in set (0.00 sec)

Delete

mysql> delete from db1.t1 where id=2;Query OK, 3 rows affected (0.00 sec)mysql> select * from db1.t1;Empty set (0.00 sec)

Delete Empty table, truncate empty information, table structure is preserved; drop entire purge

Do not operate as much as possible in your work

mysql> insert into db1.t1 values (1, ‘123‘);Query OK, 1 row affected (0.00 sec)mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | 123  |+------+------+1 row in set (0.00 sec)mysql> truncate db1.t1;Query OK, 0 rows affected (0.01 sec)mysql> select * from db1.t1;Empty set (0.00 sec)mysql> drop table t1;Query OK, 0 rows affected (0.00 sec)mysql> select * from db1.t1;ERROR 1146 (42S02): Table ‘db1.t1‘ doesn‘t existmysql> drop database db1;Query OK, 0 rows affected (0.00 sec)

13.6 MySQL Database backup recovery

Backup library mysqldump-uroot-p123456 mysql >/tmp/mysql.sql
Recovery library mysql-uroot-p123456 MySQL </tmp/mysql.sql
Backup table mysqldump-uroot-p123456 mysql user >/tmp/user.sql
Recovery table mysql-uroot-p123456 MySQL </tmp/user.sql
Back up all libraries mysqldump-uroot-p-A >/tmp/123.sql
Back up table structure only mysqldump-uroot-p123456-d mysql >/tmp/mysql.sql

Operation Process

  [[email protected] ~]# mysqldump-uroot-paminglinux mysql>/tmp/mysqlbak.sqlwarning:using A Password on the command line interface can is insecure. [[email protected] ~]# mysql-uroot-paminglinux-e "CREATE Database Mysql2" warning:using a password on the command Line interface can insecure. [[email protected] ~]# mysql-uroot-paminglinux mysql2 </tmp/mysqlbak.sql warning:using a password on the comma ND line interface can insecure. [[email protected] ~]# mysqldump-uroot-paminglinux mysql user >/tmp/mysqluser.sqlwarning:using a password on t He command line interface can insecure. [[email protected] ~]# mysql-uroot-paminglinux mysql2 </tmp/mysqluser.sql warning:using a password on the Comm and line interface can insecure.  [[email protected] ~]# mysqldump-uroot-paminglinux mysql-a >/tmp/mysql-all.sqlwarning:using A password on the Command line interface can insecure.  

2018-05-08 Linux Learning