本文索引：
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 BY PASSWORD '*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: 2 Changed: 2 Warnings: 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: 1 Changed: 1 Warnings: 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
