本文深入解析MySQL用户创建与授权技巧，涵盖SQL基础操作如增删改查，以及数据库备份恢复策略，适合初学者与进阶用户掌握核心技能。
13.4 mysql用户管理
13.5 常用sql语句
13.6 mysql数据库备份恢复
扩展
SQL语句教程 http://www.runoob.com/sql/sql-tutorial.html
什么是事务？事务的特性有哪些？ http://blog.youkuaiyun.com/yenange/article/details/7556094
根据binlog恢复指定时间段的数据 https://blog.youkuaiyun.com/lilongsy/article/details/74726002
相关扩展 https://blog.youkuaiyun.com/linuxheik/article/details/71480882
mysql字符集调整 http://xjsunjie.blog.51cto.com/999372/1355013
使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份 http://zhangguangzhi.top/2017/08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql
相关视频
链接：http://pan.baidu.com/s/1miFpS9M 密码：86dx
链接：http://pan.baidu.com/s/1o7GXBBW 密码：ue2f
13.4 mysql用户管理：
MySQL创建用户以及授权：
~1.grant all on *.* to 'user1' @'127.0.0.1' identified by 'passwd';
grant表示授权的意思
all表示所有的权限（查看、创建、删除等等）
*.*表示对所有的库都有权限。第一个*表示库名，例如写成mysql.*即表示对mysql库里的所有的表
to 'user1'创建user1这个用户
@'127.0.0.1'可以表示指定客户端的IP（来源IP）。也就是只能通过我们指定的这个IP来登录。（也可以写成%即表示所有来源IP）但是，登录的时候要-h指定这个IP。。也可以写localhost，默认sock登录，登录时不必-h登录
identified by表示设置他的密码后面的'passwd'是设置他的密码
~2.grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd'; #针对具体的权限去授权
SELECT,UPDATE,INSERT针对这些权限去授权
on db1针对db1这个库
.* db1这个库里的所有的表
to 'user2'以上条件给user2用户
@'192.168.133.1'来源IP或是'localhost'
identified by 'passwd'设置他的密码
~3.grant all on db1.* to 'user3'@'%' identified by 'passwd'; #针对所有的IP去授权
@'%'其中%表示所有IP
~4.show grants; #查看所有授权
~5.show grants for user2@192.168.133.1;
！！root下忘记某一用户的密码，可执行这一命令。复制输出的两行配置修改即可
实例：
~1.
MySQL [(none)]> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a';
Query OK, 0 rows affected (0.00 sec)[root@localhost ~]# mysql -uuser1 -p123456a 报错，因为默认连接是sock。要加-h登录
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)[root@localhost ~]# mysql -uuser1 -p123456a -h127.0.0.1 因为设置了'127.0.0.1'这个IP需要-h127.0.0.1登录才可以
Welcome to the MariaDB monitor. Commands end with ; or \g.MySQL [(none)]> grant all on *.* to 'user1'@'localhost' identified by '123456a'; 指定IP为localhost，也就是默认sock登录，登录时不必-h
Query OK, 0 rows affected (0.00 sec)
报错
ERROR 1045 (28000): Access denied for user 'user1'@'127.0.0.1' (using password: YES)
解决办法
查找MySQL的安装目录找到mysql_upgrade ：
- [root@localhost ~]# whereis mysql_upgrade
-
mysql_upgrade: /usr/local/mysql/bin/mysql_upgrade - cd /usr/bin/
- ./mysql_upgrade -uroot -p123 (123为密码）
- [root@localhost bin]# mysql -uroot -p
- [root@localhost bin]#grant all on *.* to 'user1'@'localhost' identified by '123456a';
MySQL [(none)]> quit 退出测试一下
Bye[root@localhost ~]# mysql -uuser1 -p123456a 测试成功，不必-h登录
Welcome to the MariaDB monitor. Commands end with ; or \g.
~4.
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.30.1' identified by 'passwd';
报错
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
解决办法：先切换到数据库root目录下，再执行上面的命令。
[root@localhost bin]# mysql --user=root -p
输入密码
mysql> grant SELECT,UPDATE,INSERT on db2.* to 'user2'@'192.168.133.1' identified by '123456a';
Query OK, 0 rows affected (0.00 sec)
！！假如我们在root用户下更改user2的登录指定IP。但是忘记了user2的密码，一下：
MySQL [(none)]> show grants for user2@192.168.133.1; 查看user2的权限
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.133.1 |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*B012E8731FF1DF44F3D8B26837708985278C3CED' |
| GRANT SELECT, INSERT, UPDATE ON `db2`.* TO 'user2'@'192.168.133.1' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
这两行是user2的权限。假如我们忘了user2的密码，想要修改他的IP。直接可以在root用户下分别复制这两行到命令行，直接修改他的IP即可。如下：
MySQL [(none)]> GRANT USAGE ON *.* TO 'user2'@'127.0.0.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0'; #分别复制这两行修改他的IP为127.0.0.1
Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec) #第二行也要复制，并修改他的IP为127.0.0.1
[root@axinlinux-01 ~]# mysql -uuser2 -ppasswd -h127.0.0.1 #测试一下，成功
Welcome to the MariaDB monitor. Commands end with ; or \g.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
13.5 常用sql语句：
select查看
insert插入
update更改
关于数据库的引擎目前两种：
myisam（mysql库里默认的都是myisam。自动的统计有多少行。这种引擎select * 会很快）
innodb（用的时候才会去统计行数。对于innodb的话用select * 去查看行数，会比较费时以及浪费资源。特别是比较大的表，万不得已不要去执行）
综上所以，select * 这种操作尽量减少这种执行
~1.select count(*) from mysql.user; 查看这个表的行数
mysql.user表示mysql库的user表。表与库以 . 作为分隔符
------
~2.select * from mysql.db; 查看这个表里所有的内容
select * from mysql.db\G 比较好看
~3.select db from mysql.db; 这个db是字段。就是查看这个表里db字段的
select db from mysql.db\G
~4.select db,user from mysql.db; 查看两个字段的。以，分割。查看这个表里这两个字段的
select db,user from mysql.db\G
~5.select * from mysql.db where host like '192.168.%'; 模糊搜索查看（使用like）。查看这个表里所有内容，host为192.168.开头的
select * from mysql.db where host like '192.168.%'\G
------
~6.insert into db1.t1 values (1, 'abc'); 字符串要加上单引号。数字可以不加
括号里的1与abc分别代表。输出结果的id与name
~7.update db1.t1 set name='aaa' where id=1; 更新这个表里的数据name为aaa，id为1
delete from db1.t1 where id=2; 删除这个表里条件为id=2的
------
~8.truncate table db1.t1; 清空这个表
仅仅清空这个表里的内容。表结构还留着
~9.drop table db1.t1; drop会把这个表里的数据以及结构一起删除
~10drop database db1; 把这个库里的所有删掉
！！truncate与drop这种删除语法尽量少用，避免删错
实例：
~1.
MySQL [db1]> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 11 | 即11行
+----------+
1 row in set (0.00 sec)
~6.
MySQL [db1]> select * from db1.t1; 这个表示空的
Empty set (0.03 sec)
MySQL [db1]> insert into db1.t1 values (1, 'abc'); 括号里分别对应下面输出的id和name
Query OK, 1 row affected (0.00 sec)
MySQL [db1]> select * from db1.t1; 查看就有了
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
MySQL [db1]> insert into db1.t1 values (1, 234); 再插入数字，不加单引号）
Query OK, 1 row affected (0.00 sec)
MySQL [db1]> 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)
~7.
MySQL [db1]> 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 [db1]> select * from db1.t1; 所有的id都是1，所有的name都是aaa
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 1 | aaa |
+------+------+
2 rows in set (0.00 sec)
MySQL [db1]> delete from db1.t1 where id=1; 删除这个表里条件为id=1的
Query OK, 2 rows affected (0.00 sec)
MySQL [db1]> select * from db1.t1; id都为1。所以删完了
Empty set (0.00 sec)
~8.
MySQL [db1]> truncate table db1.t1; 清空这个表
Query OK, 0 rows affected (0.06 sec)
MySQL [db1]> select * from db1.t1; 查看已经没有内容了
Query OK, 0 rows affected (0.03 sec)
MySQL [db1]> desc db1.t1; desc看一下。表的结构还在
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
13.6 mysql数据库备份恢复：
mysqldump是备份的一个命令。可以备份一个库，也可以备份一个表
恢复的时候用mysql命令
~1.
备份库 mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
备份root用户的mysql库，重定向到/tmp/mysql.sql
mysql -uroot -pwangxin789 -e "create database mysql2"
mysql -uroot -pwangxin789 mysql2
恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql
~2.
备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql
在库的后面加上表，以空格分割
恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql
恢复表的时候只写库的名字即可，不用写表的名字
~3.
备份所有库 mysqldump -uroot -p -A >/tmp/123.sql
使用-A（大A）即备份所有的数据库
~4.
只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
使用-d（小d）及备份库里面表的结构，不备份数据
如果数据量太大，几个T的话。再用mysqldump就会特别的慢
~1.
[root@localhost ~]# mysqldump -uroot -pwangxin789 mysql > /tmp/mysql.sql
直接在linux中mysqldump 这个mysql库，重定向到tmp/mysql.sql
[root@localhost ~]# ls /tmp/mysql.sql
/tmp/mysql.sql 这个mqsql.sql就是我们备份的文件
[root@localhost ~]# mysql -uroot -pwangxin789 -e "create database mysql2"
我们可以在linux下直接-e（创建）一个mysql2的库
[root@localhost ~]# mysql -uroot -pwangxin789 mysql2 < /tmp/mysql.sql
把我们刚备份的mysql库恢复到mysql2这个库里。使用反重定向
[root@localhost ~]# mysql -uroot -pwangxin789 mysql2
在进入mysql的时候，在后面加上库的名字就能直接进入到mysql2库里去
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 MariaDB monitor. Commands end with ; or \g.MySQL [mysql2]> select database(); 查看mysql2这个库
+------------+
| database() |
+------------+
| mysql2 |
+------------+
1 row in set (0.00 sec)
MySQL [mysql2]> show tables; 查看mysql2里有没我们刚刚恢复的这个库
+---------------------------+
| Tables_in_mysql2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
~2.
[root@localhost ~]# mysqldump -uroot -pwangxin789 mysql user > /tmp/user.sql
[root@localhost ~]# mysql -uroot -pwangxin789 mysql2 < /tmp/user.sql
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
