Mysql用户管理、常用SQL语句及数据库的备份与恢复

2018-01-16 12:41:25来源:oschina作者:Lu_Castiel人点击

分享

本文索引:


MySQL用户管理
常用SQL语句
数据库的备份与恢复
MySQL用户管理
创建用户
指定具体ip
# 这里指定了具体的ip
# *.*:第一个*表示任意的数据库,第二个*表示任意表
mysql> grant all on *.* to 'test1'@'127.0.0.1' identified by '1234456';
Query OK, 0 rows affected (0.03 sec)

不指定ip无法登录


[root@localhost ~]# mysql -utest1 -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: YES)

-h指定ip后成功登录


[root@localhost ~]# mysql -utest1 -p123456 -h127.0.0.1
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 4
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> quit
Bye限制命令
# 对test2用户只给予SELECT,UPDATE,INSERT权限
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'test2'@'192.168.65.1' identified by '111';
Query OK, 0 rows affected (0.01 sec)
# 查看test2@'192.168.65.1'的权限
mysql> show grants for test2@'192.168.65.1';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for test2@192.168.65.1|
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test2'@'192.168.65.1' IDENTIFIED BY PASSWORD '*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'test2'@'192.168.65.1' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)任意ip
# %表示任意的意思
mysql> grant all on db1.* to 'test3'@'%' identified by '111';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for test3;
+------------------------------------------------------------------------------------------------------+
| Grants for test3@%|
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test3'@'%' IDENTIFIED BY PASSWORD '*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'test3'@'%'|
+------------------------------------------------------------------------------------------------------+同一个用户指定多个ip
# 这里以test2为例
# 通过show grants命令查看创建命令,其内的密码时加密后的,这里我们无法使用下列的命令直接创建
# grant SELECT,UPDATE,INSERT on db1.* to 'test2'@'192.168.65.2' identified by '*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB';
# 但是我们可以通过修改显示出的命令,实现创建目的
mysql> show grants for test2@'192.168.65.1';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for test2@192.168.65.1|
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test2'@'192.168.65.1' IDENTIFIED BY PASSWORD '*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'test2'@'192.168.65.1' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
# 将查看到的命令进行修改,如下所示
mysql> GRANT USAGE ON *.* TO 'test2'@'192.168.65.2' IDENTIFIED BYPASSWORD '*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'test2'@'192.168.65.2';
Query OK, 0 rows affected (0.00 sec)
# 可以查看密码并没有修改
mysql> show grants for test2@'192.168.65.2';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for test2@192.168.65.2|
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test2'@'192.168.65.2' IDENTIFIED BY PASSWORD '*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'test2'@'192.168.65.2' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


简单常用SQL语句
查看表内的行数
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
|11 |
+----------+
1 row in set (0.03 sec)获取表的内容
mysql> select * from mysql.db;
+--------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host| Db| User| Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+--------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | test|| Y| Y| Y| Y| Y| Y| N | Y | Y | Y | Y | Y| Y| Y| Y | N| N | Y | Y |
| % | test/_% || Y| Y| Y| Y| Y| Y| N | Y | Y | Y | Y | Y| Y| Y| Y | N| N | Y | Y |
| 192.168.65.1 | db1 | test2 | Y| Y| Y| N| N| N| N | N | N | N | N | N| N| N| N | N| N | N | N |
| 192.168.65.2 | db1 | test2 | Y| Y| Y| N| N| N| N | N | N | N | N | N| N| N| N | N| N | N | N |
| % | db1 | test3 | Y| Y| Y| Y| Y| Y| N | Y | Y | Y | Y | Y| Y| Y| Y | Y| Y | Y | Y |
+--------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
5 rows in set (0.00 sec)
# 使用/G代替;,使显示更规整
mysql> select * from mysql.db/G查看表的字段
# 查看表的某个字段
mysql> select db from mysql.db;
+---------+
| db|
+---------+
| db1 |
| test|
| test/_% |
| db1 |
| db1 |
+---------+
5 rows in set (0.00 sec)
# 查看表的多个字段,字段间使用,分割
mysql> select db,user from mysql.db;
+---------+-------+
| db| user|
+---------+-------+
| db1 | test3 |
| test||
| test/_% ||
| db1 | test2 |
| db1 | test2 |
+---------+-------+
5 rows in set (0.00 sec)查看指定ip/ip范围的字段
# like表示模糊匹配,这里%可以是1-255的任意一个
mysql> select * from mysql.db where host like '192.168.65.%';
+--------------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host| Db| User| Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+--------------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| 192.168.65.1 | db1 | test2 | Y| Y| Y| N| N| N| N | N | N | N | N | N| N| N| N | N| N | N | N |
| 192.168.65.2 | db1 | test2 | Y| Y| Y| N| N| N| N | N | N | N | N | N| N| N| N | N| N | N | N |
+--------------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.db where host like '192.168.65.%'/G
*************************** 1. row ***************************
Host: 192.168.65.1
Db: db1
User: test2
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
*************************** 2. row ***************************
Host: 192.168.65.2
Db: db1
User: test2
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
2 rows in set (0.00 sec)
mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.04 sec)插入内容: insert
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)修改内容:update
# 根据id字段修改name字段
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2Changed: 2Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
|1 | aaa|
|1 | aaa|
+------+------+
2 rows in set (0.00 sec)
# 根据name字段修改id字段
mysql> update db1.t1 set id=8 where name='abc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
|1 | 234|
|8 | abc|
+------+------+
2 rows 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> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id| int(4) | YES| | NULL||
| name| char(40) | YES| | NULL||
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)删除表内的某些数据
mysql> delete from db1.t1 where id=2;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from db1.t1 where id=1;
Query OK, 2 rows affected (0.01 sec)删除操作:drop
# 删除表
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 exist
# 删除数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.04 sec)
mysql> use db1;
ERROR 1049 (42000): Unknown database 'db1'

作为运维人员,truncate、drop、delete等删除命令不能随意使用,极易造成数据的丢失。





MySQL数据库的备份与恢复
备份库
mysqldump -uroot -p123456 mysql > /tmp/mysql.sql恢复库
mysql -uroot -p123456 mysql < /tmp/mysql.sql备份表
mysqldump -uroot -p123456 mysql user > /tmp/user.sql恢复表
mysql -uroot -p123456 mysql < /tmp/user.sql备份所有库
mysqldump -uroot -p -A > /tmp/123.sql只备份表结构
mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台