mysql 用户管理
创建用户并授权登录
grant all on *.* to 'user1'@'127.0.0.1' identified by '123456';
第一个 * 表示 db_name; 第二个 * 表示 tb_name
指定其来源 IP127.0.0.1(只可通过此 IP 登录) 也可以使用通配符 %, 代表所有 IP
identified by 设置密码
- [root@akuilinux01 ~]# mysql -uuser1 -p123456 -h'127.0.0.1'
- mysql>
- grant all on . to 'user2'@'localhost' identified by '123456';
- [root@akuilinux01 ~]# mysql -uuser2 -p123456
- mysql>
通过本机登录, 必须使用 localhost
对具体权限进行授权
- mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.21.128' identified by '123456';
- Query OK, 0 rows affected (0.00 sec)
- # 创建 user2 用户, 并授予其针对 db1 库 SELECT,UPDATE,INSERT 权限
- mysql> grant all on db1.* to 'user'@'%' identified by '123456';
- Query OK, 0 rows affected (0.01 sec)
- # 创建 user 用户, 并针对所有 IP 授予其 db1 库所有权限
查看权限命令
- mysql> show grants;
- # 查看当前用户的权限
- mysql> show grants for user2@192.168.21.128;
- # 查看指定用户的权限
不知道密码的时候更改用于的权限
- mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.21.128' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE44568DDA
- -> ;
- Query OK, 0 rows affected (0.00 sec)
- mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.21.128';
- Query OK, 0 rows affected (0.00 sec)
- mysql> show grants for user2@192.168.21.128;
- +-------------------------------------------------------------------------------------------------------------------+
- | Grants for user2@192.168.21.128 |
- +-------------------------------------------------------------------------------------------------------------------+
- | GRANT USAGE ON *.* TO 'user2'@'192.168.21.128' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
- | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.21.128' |
- +-------------------------------------------------------------------------------------------------------------------+
- 2 rows in set (0.00 sec)
常用 sql 语句
- mysql> use db1;
- Database changed
- # 选择库
- mysql> select count(*) from mysql.user;
- +----------+
- | count(*) |
- +----------+
- | 12 |
- +----------+
- 1 row in set (0.04 sec)
- # 查看指定库的内容的行数
- mysql> select * from mysql.db\G;
- # 查看库的所有内容
- mysql> select db,user from mysql.db;
- # 查看库指定内容
- mysql> select * from mysql.db where host like '192.168.%'\G;
- # 查看某些 IP 对应的库内容, like 表示匹配
- mysql> create table t1(`id` int(4),`name` char(40));
- Query OK, 0 rows affected (0.39 sec)
- # 在 db1 库下创建表 t1
- mysql> select * from db1.t1;
- Empty set (0.03 sec)
- # 查看表中信息: 空表
- mysql> insert into db1.t1 values(1,'abc');
- Query OK, 1 row affected (0.09 sec)
- # 向表中插入内容
- mysql> select * from db1.t1;
- +------+------+
- | id | name |
- +------+------+
- | 1 | abc |
- +------+------+
- 1 row in set (0.00 sec)
- mysql> update db1.t1 set name='aaa' where id=1;
- Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- mysql> select * from db1.t1;
- +------+------+
- | id | name |
- +------+------+
- | 1 | aaa |
- +------+------+
- 1 row in set (0.00 sec)
- # 更改表中指定内容
- mysql> delete from db1.t1 where id=1;
- Query OK, 2 rows affected (0.10 sec)
- # 删除表中指定内容
- mysql> select * from db1.t1;
- Empty set (0.00 sec)
- mysql> truncate db1.t1;
- Query OK, 0 rows affected (0.09 sec)
- # 清空一个表中内容
- mysql> drop table t1;
- Query OK, 0 rows affected (0.04 sec)
- # 删除表
- mysql> drop database db1;
- Query OK, 0 rows affected (0.13 sec)
- # 删除库
- mysql> use mysql;
- mysql> delete from user where User='user1' and Host='127.0.0.1';
- Query OK, 1 row affected (0.06 sec)
- # 删除用户, 在删除用户前需要先指定表
MySQL 数据库备份恢复
备份库
备份指定库:
[root@akuilinux01 ~]# mysqldump -uroot -p123456 mysql> /tmp/mysqlbak.sql
备份所有库:
[root@akuilinux01 ~]# mysqldump -uroot -p123456 -A> /tmp/mysql_all.sql
恢复库
[root@akuilinux01 ~]# mysql -uroot -p123456 </tmp/mysqlbak.sql
备份表
备份指定表:
[root@akuilinux01 ~]# mysql -uroot -p123456 mysql user> /tmp/user.sql
只备份表结构:
[root@akuilinux01 ~]# mysqldump -uroot -p123456 -d mysql> /tmp/mysql_tb.sql
恢复表
[root@akuilinux01 ~]# mysql -uroot -p123456 mysql user < /tmp/user.sql
扩展
SQL 语句教程 http://www.runoob.com/sql/sql-tutorial.html
什么是事务? 事务的特性有哪些? http://blog.csdn.net/yenange/article/details/7556094
根据 binlog 恢复指定时间段的数据 https://blog.csdn.net/lilongsy/article/details/74726002
mysql 字符集调整 http://xjsunjie.blog.51cto.com/999372/1355013
使用 xtrabackup 备份 innodb 引擎的数据库 innobackupex 备份 Xtrabackup 增量备份 http://zhangguangzhi.top/2017/08/23/innobackex工具备份mysql数据/#三、开始恢复mysql
相关视频
链接: http://pan.baidu.com/s/1miFpS9M 密码: 86dx
链接: http://pan.baidu.com/s/1o7GXBBW 密码: ue2f
来源: http://www.bubuko.com/infodetail-2653029.html