学习linux第五十五天

mysql用户管理

 

[root@hanlin ~]# mysql -uroot -p123456a. （先用root登入）
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 31
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456' ;（创建user1账号，指定密码123456，只针对于本机服务器127.0.0.1，创建用户的命令时不会记录在mysql_history中的，安全因素）
Query OK, 0 rows affected (0.00 sec)

mysql> q
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'q' at line 1
mysql> mysql -uuser1 -p123456 （出错是因为默认通过本地socket链接的，需要指定ip才行，或者@‘localhost’就是通过socket链接的，localhot：是不经网卡传输的，它不受网络防火墙和网卡相关的的限制。
127.0.0.1：是通过网卡传输的，它依赖网卡，并受到网络防火墙和网卡相关的限制。）
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql -uuser1 -p123456' at line 1
mysql> mysql -uuser1 -p123456 -P127.0.0.1
-> mysql -uuser1 -p123456 -P127.0.0.1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql -uuser1 -p123456 -P127.0.0.1
mysql -uuser1 -p123456 -P127.0.0.1' at line 1
mysql> mysql -uuser1 -p123456 -P127.0.0.1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql -uuser1 -p123456 -P127.0.0.1' at line 1
mysql>

mysql> Ctrl-C -- exit! （成功登入，指定-P127.0.0.1）
Aborted

[root@hanlin ~]# mysql -uroot -p123456a. （）
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 31
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on *.* to 'user1'@'localhost' identified by '123456' ;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@hanlin ~]# mysql -uuser1 -p123456
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 32
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on *.* to 'user1'@'localhost' identified by '123456' ; （指定本地socket链接）
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@hanlin ~]# mysql -uuser1 -p123456 （不需要-P就可以链接了）
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 32
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user1'@'192.168.0.12' identified by '123' （针对具体动作和库还有ip做授权）

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 '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants; （显示当前用户的授权信息）
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*E80A41360319BEA0CF1C238A481E087AF70C15BC' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | （这组授权信息可以直接改ip重新授权，针对于多ip，如果不知道密码直接改ip复制授权即可）
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD  '*E80A41360319BEA0CF1C238A481E087AF70C15BC' ; （可以直接改ip授权）
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

 

 

mysql常用语句

[root@hanlin ~]# mysql -uroot -p123456a. （用root账号登入mysql库）
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 35
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 

mysql> select count(*) from mysql.user; （查看mysql库的user表有多少行）
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
 

mysql> select * from mysql.db \G; （查看mysql表里的所有信息）
*************************** 1. row ***************************
Host: %
Db: test
User: 
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User: 
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 3. row ***************************
Host: 192.168.133.1
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
3 rows in set (0.00 sec)

ERROR: 
No query specified
mysql> select db,user from mysql.db; （多种条件查询mysql库里面的db表）
+---------+-------+
| db | user |
+---------+-------+
| test | |
| test\_% | |
| db1 | user2 |
+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from mysql.db where host like '192.168.%'\G; （模糊查询）
*************************** 1. row ***************************
Host: 192.168.133.1
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)

ERROR: 
No query specified
 

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> insert into db1.t1 values (1,'abc'); （字段值插入，id=1， name=abc）
Query OK, 1 row affected (0.00 sec)
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 1 | abc |
+------+------+
2 rows in set (0.00 sec)
mysql> update db1.t1 set id=2 where name='abc'; （找出name=abc的，把id设置成2）
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 2 | abc |
| 2 | abc |
+------+------+
2 rows in set (0.00 sec)
 

mysql> delete from db1.t1 where id=2; （根据条件删除）
Query OK, 2 rows affected (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.01 sec)

mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
mysql> truncate db1.t1; （清空db1库t1表，仅仅清空内容，表结构还在）
Query OK, 0 rows affected (0.01 sec)

mysql> select * from db1.t1;
Empty set (0.01 sec)

 

mysql> drop db1.t1； （连表结构带数据一起清空）

mysql> drop database db1; （彻底删除数据库）
 

mysql数据恢复

只针对于mysql再数百G以下的数据

 

[root@hanlin ~]# mysql -uroot -p123456a. -e "create database mysql" （新建mysql库）
Warning: Using a password on the command line interface can be insecure.
[root@hanlin ~]# mysql -uroot -p123456a.
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 43
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
Database changed
mysql> create table t1(`id` int(4), `name` char(40)); （创建t1表id name字段）

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)

mysql> insert into mysql.t1 values (1,'abc'); （对t1表里面的字段进行赋值）
Query OK, 1 row affected (0.01 sec)


mysql> select * from mysql.t1
-> ;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
[root@hanlin ~]# mysqldump -uroot -p123456a. mysql > /tmp/mysql.bak.sql （备份mysql数据库重定向到备份路径）

mysql> drop database mysql; （删库）
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| test |
+--------------------+
3 rows in set (0.00 sec)

mysql> select * from mysql （mysql库什么数据都没了）
-> ;
ERROR 1046 (3D000): No database selected

开始恢复数据库

 

[root@hanlin ~]# mysql -uroot -p123456a.
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 51
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database mysql;
Query OK, 1 row affected (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted
[root@hanlin ~]# mysql -uroot -p123456a. mysql < /tmp/mysql.bak.sql （逆向追加）
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from mysql.t1; （数据库恢复成功）
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
 

备份还原数据库里的表

[root@hanlin ~]# mysqldump -uroot -p123456a. mysql2 t1 > /tmp/t1.bak （备份t1表）
Warning: Using a password on the command line interface can be insecure.

 

mysql> drop table t1; （彻底删除t1表）
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql2.t1; （查勘表信息）
ERROR 1146 (42S02): Table 'mysql2.t1' doesn't exist
mysql> Ctrl-C -- exit!
Aborted
 

 

 

还原表

[root@hanlin ~]# mysql -uroot -p123456a. mysql2 < /tmp/t1.bak （逆向还原）
Warning: Using a password on the command line interface can be insecure.
[root@hanlin ~]# mysql -uroot -p123456a. mysql2 （登入到mysql2库中，就不用进去之后再use mysql2了）

Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from mysql2.t1 （查找t1表顺利还原）
-> ;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
备份所有库
[root@hanlin ~]# mysqldump -uroot -p123456a. -A > /tmp/mysql.bak
[root@hanlin ~]# mysqldump -uroot -p123456a. -d mysql2  > /tmp/t1.bak （只备份数据库里的表的结构不备份数据）

 

转载于:https://my.oschina.net/u/3867255/blog/2980654