Nginx URL重写(rewrite)

Nginx URL重写(rewrite)介绍

和apache等web服务软件一样,rewrite的组要功能是实现RUL地址的重定向。Nginx的rewrite功能需要PCRE软件的支持,即通过perl兼容正则表达式语句进行规则匹配的。默认参数编译nginx就会支持rewrite的模块,但是也必须要PCRE的支持

rewrite是实现URL重写的关键指令,根据regex(正则表达式)部分内容,重定向到replacement,结尾是flag标记。

rewrite语法格式及参数语法说明如下:

    rewrite    <regex>    <replacement>    [flag];

关键字      正则        替代内容          flag标记

 

关键字:其中关键字error_log不能改变

正则:perl兼容正则表达式语句进行规则匹配

替代内容:将正则匹配的内容替换成replacement

flag标记:rewrite支持的flag标记

 

flag标记说明:

last  #本条规则匹配完成后,继续向下匹配新的location URI规则

break  #本条规则匹配完成即终止,不再匹配后面的任何规则

redirect  #返回302临时重定向,浏览器地址会显示跳转后的URL地址

permanent  #返回301永久重定向,浏览器地址栏会显示跳转后的URL地址

rewrite参数的标签段位置:

server,location,if

例子:

rewrite ^/(.*) http://www.czlun.com/$1 permanent;

说明:

rewrite为固定关键字,表示开始进行rewrite匹配规则

regex部分是 ^/(.*) ,这是一个正则表达式,匹配完整的域名和后面的路径地址

replacement部分是http://www.czlun.com/$1 $1,是取自regex部分()里的内容。匹配成功后跳转到的URL。

flag部分 permanent表示永久301重定向标记,即跳转到新的 http://www.czlun.com/$1 地址上

regex 常用正则表达式说明

字符 描述
\ 将后面接着的字符标记为一个特殊字符或一个原义字符或一个向后引用。如“\n”匹配一个换行符,而“\$”则匹配“$”
^ 匹配输入字符串的起始位置
$ 匹配输入字符串的结束位置
* 匹配前面的字符零次或多次。如“ol*”能匹配“o”及“ol”、“oll”
+ 匹配前面的字符一次或多次。如“ol+”能匹配“ol”及“oll”、“oll”,但不能匹配“o”
? 匹配前面的字符零次或一次,例如“do(es)?”能匹配“do”或者“does”,”?”等效于”{0,1}”
. 匹配除“\n”之外的任何单个字符,若要匹配包括“\n”在内的任意字符,请使用诸如“[.\n]”之类的模式。
(pattern) 匹配括号内pattern并可以在后面获取对应的匹配,常用$0…$9属性获取小括号中的匹配内容,要匹配圆括号字符需要\(Content\)

rewrite 企业应用场景

Nginx的rewrite功能在企业里应用非常广泛:

u 可以调整用户浏览的URL,看起来更规范,合乎开发及产品人员的需求。

u 为了让搜索引擎搜录网站内容及用户体验更好,企业会将动态URL地址伪装成静态地址提供服务。

u 网址换新域名后,让旧的访问跳转到新的域名上。例如,访问京东的360buy.com会跳转到jd.com

u 根据特殊变量、目录、客户端的信息进行URL调整等

Nginx配置rewrite过程介绍

(1)创建rewrite语句

vi conf/vhost/www.abc.com.conf

#vi编辑虚拟主机配置文件

文件内容

server {

listen 80;

server_name abc.com;

rewrite ^/(.*) http://www.abc.com/$1 permanent;

}

 

 

server {

listen 80;

server_name www.abc.com;

location / {

root /data/www/www;

index index.html index.htm;

}

error_log    logs/error_www.abc.com.log error;

access_log    logs/access_www.abc.com.log    main;

}

或者

server {

listen 80;

server_name abc.com www.abc.com;

if ( $host != ‘www.abc.com’  ) {

rewrite ^/(.*) http://www.abc.com/$1 permanent;

}

location / {

root /data/www/www;

index index.html index.htm;

}

error_log    logs/error_www.abc.com.log error;

access_log    logs/access_www.abc.com.log    main;

}

(2)重启服务

确认无误便可重启,操作如下:

nginx -t

#结果显示ok和success没问题便可重启

nginx -s reload

(3)查看跳转效果

打开浏览器访问abc.com

页面打开后,URL地址栏的abc.com变成了www.abc.com说明URL重写成功。

mysqlbinlog日志数据恢复

众所周知,binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlog日志恢复增量数据部分),化险为夷!

废话不多说,下面是梳理的binlog日志操作解说:

一、初步了解binlog
MySQL的二进制日志binlog可以说是MySQL最重要的日志,它记录了所有的DDL和DML语句(除了数据查询语句select),以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。
———————————————————————————————————————————————-
DDL
—-Data Definition Language 数据库定义语言
主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用。

DML
—-Data Manipulation Language 数据操纵语言
主要的命令是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
———————————————————————————————————————————————-

mysqlbinlog常见的选项有以下几个:
–start-datetime:从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
–stop-datetime:从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
–start-position:从二进制日志中读取指定position 事件位置作为开始。
–stop-position:从二进制日志中读取指定position 事件位置作为事件截至

*********************************************************************

一般来说开启binlog日志大概会有1%的性能损耗。
binlog日志有两个最重要的使用场景:
1)MySQL主从复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到
master-slave数据一致的目的。
2)自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。
binlog日志包括两类文件:
1)二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件
2)二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

二、开启binlog日志:
1)编辑打开mysql配置文件/etc/mys.cnf
[root@vm-002 ~]# vim /etc/my.cnf
在[mysqld] 区块添加
log-bin=mysql-bin 确认是打开状态(mysql-bin 是日志的基本名或前缀名);

2)重启mysqld服务使配置生效
[root@vm-002 ~]# /etc/init.d/mysqld stop
[root@vm-002 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

3)查看binlog日志是否开启
mysql> show variables like ‘log_%’;
+———————————+———————+
| Variable_name | Value |
+———————————+———————+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
+———————————+———————+
9 rows in set (0.00 sec)

三、常用的binlog日志操作命令
1)查看所有binlog日志列表
mysql> show master logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001 | 149 |
| mysql-bin.000002 | 4102 |
+——————+———–+
2 rows in set (0.00 sec)

2)查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000002 | 4102 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

3)flush刷新log日志,自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)

mysql> show master logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001 | 149 |
| mysql-bin.000002 | 4145 |
| mysql-bin.000003 | 106 |
+——————+———–+
3 rows in set (0.00 sec)

注意:
每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

4)重置(清空)所有binlog日志
mysql> reset master;
Query OK, 0 rows affected (0.12 sec)

mysql> show master logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001 | 106 |
+——————+———–+
1 row in set (0.00 sec)

四、查看binlog日志内容,常用有两种方式:
1)使用mysqlbinlog自带查看命令法:
注意:
–>binlog是二进制文件,普通文件查看器cat、more、vim等都无法打开,必须使用自带的mysqlbinlog命令查看
–>binlog日志与数据库文件在同目录中
–>在MySQL5.5以下版本使用mysqlbinlog命令时如果报错,就加上 “–no-defaults”选项

查看mysql的数据存放目录,从下面结果可知是/var/lib//mysql
[root@vm-002 ~]# ps -ef|grep mysql
root 9791 1 0 21:18 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe –datadir=/var/lib/mysql –socket=/var/lib/mysql/mysql.sock –pid-file=/var/run/mysqld/mysqld.pid –basedir=/usr –user=mysql
mysql 9896 9791 0 21:18 pts/0 00:00:00 /usr/libexec/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –log-error=/var/log/mysqld.log –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/lib/mysql/mysql.sock
root 9916 9699 0 21:18 pts/0 00:00:00 mysql -px xxxx
root 9919 9715 0 21:23 pts/1 00:00:00 grep –color mysql

[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock ops test

使用mysqlbinlog命令查看binlog日志内容,下面截取其中的一个片段分析:
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000002
…………..
# at 624
#160925 21:29:53 server id 1 end_log_pos 796 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1474810193/*!*/;
insert into member(`name`,`sex`,`age`,`classid`) values(‘wangshibo’,’m’,27,’cls1′),(‘guohuihui’,’w’,27,’cls2′)        #执行的sql语句
/*!*/;
# at 796
#160925 21:29:53 server id 1 end_log_pos 823 Xid = 17                  #执行的时间
………….

解释:
server id 1 : 数据库主机的服务号;
end_log_pos 796: sql结束时的pos节点
thread_id=11: 线程号

2)上面这种办法读取出binlog日志的全文内容比较多,不容易分辨查看到pos点信息
下面介绍一种更为方便的查询命令:
命令格式:
mysql> show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count];
参数解释:
IN ‘log_name’ :指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] :偏移量(不指定就是0)
row_count :查询总条数(不指定就是所有行)

mysql> show master logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001 | 125 |
| mysql-bin.000002 | 823 |
+——————+———–+
2 rows in set (0.00 sec)

mysql> show binlog events in ‘mysql-bin.000002’\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000002
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.73-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000002
Pos: 106
Event_type: Query
Server_id: 1
End_log_pos: 188
Info: use `ops`; drop table customers
*************************** 3. row ***************************
Log_name: mysql-bin.000002
Pos: 188
Event_type: Query
Server_id: 1
End_log_pos: 529
Info: use `ops`; CREATE TABLE IF NOT EXISTS `member` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`sex` enum(‘m’,’w’) NOT NULL DEFAULT ‘m’,
`age` tinyint(3) unsigned NOT NULL,
`classid` char(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 4. row ***************************
Log_name: mysql-bin.000002
Pos: 529
Event_type: Query
Server_id: 1
End_log_pos: 596
Info: BEGIN
*************************** 5. row ***************************
Log_name: mysql-bin.000002
Pos: 596
Event_type: Intvar
Server_id: 1
End_log_pos: 624
Info: INSERT_ID=1
*************************** 6. row ***************************
Log_name: mysql-bin.000002
Pos: 624
Event_type: Query
Server_id: 1
End_log_pos: 796
Info: use `ops`; insert into member(`name`,`sex`,`age`,`classid`) values(‘wangshibo’,’m’,27,’cls1′),(‘guohuihui’,’w’,27,’cls2′)
*************************** 7. row ***************************
Log_name: mysql-bin.000002
Pos: 796
Event_type: Xid
Server_id: 1
End_log_pos: 823
Info: COMMIT /* xid=17 */
7 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

上面这条语句可以将指定的binlog日志文件,分成有效事件行的方式返回,并可使用limit指定pos点的起始偏移,查询条数!
如下操作示例:
a)查询第一个(最早)的binlog日志:
mysql> show binlog events\G;

b)指定查询 mysql-bin.000002这个文件:
mysql> show binlog events in ‘mysql-bin.000002’\G;

c)指定查询 mysql-bin.000002这个文件,从pos点:624开始查起:
mysql> show binlog events in ‘mysql-bin.000002’ from 624\G;

d)指定查询 mysql-bin.000002这个文件,从pos点:624开始查起,查询10条(即10条语句)
mysql> show binlog events in ‘mysql-bin.000002’ from 624 limit 10\G;

e)指定查询 mysql-bin.000002这个文件,从pos点:624开始查起,偏移2行(即中间跳过2个),查询10条
mysql> show binlog events in ‘mysql-bin.000002’ from 624 limit 2,10\G;

五、利用binlog日志恢复mysql数据

以下对ops库的member表进行操作
mysql> use ops;
mysql> CREATE TABLE IF NOT EXISTS `member` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(16) NOT NULL,
-> `sex` enum(‘m’,’w’) NOT NULL DEFAULT ‘m’,
-> `age` tinyint(3) unsigned NOT NULL,
-> `classid` char(6) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+—————+
| Tables_in_ops |
+—————+
| member |
+—————+
1 row in set (0.00 sec)

mysql> desc member;
+———+———————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———+———————+——+—–+———+—————-+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | | NULL | |
| sex | enum(‘m’,’w’) | NO | | m | |
| age | tinyint(3) unsigned | NO | | NULL | |
| classid | char(6) | YES | | NULL | |
+———+———————+——+—–+———+—————-+
5 rows in set (0.00 sec)

事先插入两条数据
mysql> insert into member(`name`,`sex`,`age`,`classid`) values(‘wangshibo’,’m’,27,’cls1′),(‘guohuihui’,’w’,27,’cls2′);
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+—-+———–+—–+—–+———+
2 rows in set (0.00 sec)

下面开始进行场景模拟:
1)
ops库会在每天凌晨4点进行一次完全备份的定时计划任务,如下:
[root@vm-002 ~]# crontab -l
0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x –master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz

这里手动执行下,将ops数据库备份到/opt/backup/ops_$(date +%F).sql.gz文件中:
[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x –master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz
Enter password:
[root@vm-002 ~]# ls /opt/backup/
ops_2016-09-25.sql.gz
—————–
参数说明:
-B:指定数据库
-F:刷新日志
-R:备份存储过程等
-x:锁表
–master-data:在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
—————–
待到数据库备份完成,就不用担心数据丢失了,因为有完全备份数据在!!

由于上面在全备份的时候使用了-F选项,那么当数据备份操作刚开始的时候系统就会自动刷新log,这样就会自动产生
一个新的binlog日志,这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作
查看一下:
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 106 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

也就是说, mysql-bin.000003 是用来记录4:00之后对数据库的所有“增删改”操作。

2)
早上9点上班了,由于业务的需求会对数据库进行各种“增删改”操作。
比如:在ops库下member表内插入、修改了数据等等:

先是早上进行插入数据:
mysql> insert into ops.member(`name`,`sex`,`age`,`classid`) values(‘yiyi’,’w’,20,’cls1′),(‘xiaoer’,’m’,22,’cls3′),(‘zhangsan’,’w’,21,’cls5′),(‘lisi’,’m’,20,’cls4′),(‘wangwu’,’w’,26,’cls6′);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+—-+———–+—–+—–+———+
7 rows in set (0.00 sec)

3)
中午又执行了修改数据操作:
mysql> update ops.member set name=’李四’ where id=4;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update ops.member set name=’小二’ where id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | 小二 | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | 李四 | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+—-+———–+—–+—–+———+
7 rows in set (0.00 sec)

4)
在下午18:00的时候,悲剧莫名其妙的出现了!
手贱执行了drop语句,直接删除了ops库!吓尿!
mysql> drop database ops;
Query OK, 1 row affected (0.02 sec)

5)
这种时候,一定不要慌张!!!
先仔细查看最后一个binlog日志,并记录下关键的pos点,到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步);

先备份一下最后一个binlog日志文件:
[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# cp -v mysql-bin.000003 /opt/backup/
`mysql-bin.000003′ -> `/opt/backup/mysql-bin.000003′
[root@vm-002 mysql]# ls /opt/backup/
mysql-bin.000003 ops_2016-09-25.sql.gz

接着执行一次刷新日志索引操作,重新开始新的binlog日志记录文件。按理说mysql-bin.000003
这个文件不会再有后续写入了,因为便于我们分析原因及查找ops节点,以后所有数据库操作都会写入到下一个日志文件。
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000004 | 106 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

6)
读取binlog日志,分析问题。
读取binlog日志的方法上面已经说到。
方法一:使用mysqlbinlog读取binlog日志:
[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000003

方法二:登录服务器,并查看(推荐此种方法)
mysql> show binlog events in ‘mysql-bin.000003′;

+——————+—–+————-+———–+————-+—————————————————————————————————————————-+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————+—–+————-+———–+————-+—————————————————————————————————————————-+
| mysql-bin.000003 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000003 | 106 | Query | 1 | 173 | BEGIN |
| mysql-bin.000003 | 173 | Intvar | 1 | 201 | INSERT_ID=3 |
| mysql-bin.000003 | 201 | Query | 1 | 444 | use `ops`; insert into ops.member(`name`,`sex`,`age`,`gsan’,’w’,21,’cls5′),(‘lisi’,’m’,20,’cls4′),(‘wangwu’,’w’,26,’cls6′) |
| mysql-bin.000003 | 444 | Xid | 1 | 471 | COMMIT /* xid=66 */ |
| mysql-bin.000003 | 471 | Query | 1 | 538 | BEGIN |
| mysql-bin.000003 | 538 | Query | 1 | 646 | use `ops`; update ops.member set name=’李四’ where id= |
| mysql-bin.000003 | 646 | Xid | 1 | 673 | COMMIT /* xid=68 */ |
| mysql-bin.000003 | 673 | Query | 1 | 740 | BEGIN |
| mysql-bin.000003 | 740 | Query | 1 | 848 | use `ops`; update ops.member set name=’小二’ where id= |
| mysql-bin.000003 | 848 | Xid | 1 | 875 | COMMIT /* xid=69 */ |
| mysql-bin.000003 | 875 | Query | 1 | 954 | drop database ops |
| mysql-bin.000003 | 954 | Rotate | 1 | 997 | mysql-bin.000004;pos=4 |
+——————+—–+————-+———–+————-+—————————————————————————————————————————-+
13 rows in set (0.00 sec)

或者:

mysql> show binlog events in ‘mysql-bin.000003’\G;
………
………
*************************** 12. row ***************************
Log_name: mysql-bin.000003
Pos: 875
Event_type: Query
Server_id: 1
End_log_pos: 954
Info: drop database ops
*************************** 13. row ***************************
Log_name: mysql-bin.000003
Pos: 954
Event_type: Rotate
Server_id: 1
End_log_pos: 997
Info: mysql-bin.000004;pos=4
13 rows in set (0.00 sec)

通过分析,造成数据库破坏的pos点区间是介于 875–954 之间(这是按照日志区间的pos节点算的),只要恢复到875前就可。

7)
先把凌晨4点全备份的数据恢复:
[root@vm-002 ~]# cd /opt/backup/
[root@vm-002 backup]# ls
mysql-bin.000003 ops_2016-09-25.sql.gz
[root@vm-002 backup]# gzip -d ops_2016-09-25.sql.gz
[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql
Enter password:
————–
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
————–

————–
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
————–

………….
………….

————–
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
————–

这样就恢复了截至当日凌晨(4:00)前的备份数据都恢复了。

mysql> show databases;                        #发现ops库已经恢复回来了
mysql> use ops;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+—————+
| Tables_in_ops |
+—————+
| member |
+—————+
1 row in set (0.00 sec)

mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+—-+———–+—–+—–+———+
2 rows in set (0.00 sec)

mysql>

但是这仅仅只是恢复了当天凌晨4点之前的数据,在4:00–18:00之间的数据还没有恢复回来!!
怎么办呢?
莫慌!这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复。

8)
从binlog日志恢复数据
恢复命令的语法格式:
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

——————————————————–
常用参数选项解释:
–start-position=875 起始pos点
–stop-position=954 结束pos点
–start-datetime=”2016-9-25 22:01:08″ 起始时间点
–stop-datetime=”2019-9-25 22:09:46″ 结束时间点
–database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库,只限本地log日志)
——————————————————–
不常用选项:
-u –user=name 连接到远程主机的用户名
-p –password[=name] 连接到远程主机的密码
-h –host=name 从远程主机上获取binlog日志
–read-from-remote-server 从某个MySQL服务器上读取binlog日志
——————————————————–
小结:实际是将读出的binlog日志内容,通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径;

a)完全恢复(需要手动vim编辑mysql-bin.000003,将那条drop语句剔除掉)
[root@vm-002 backup]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

b)指定pos结束点恢复(部分恢复):
–stop-position=471 pos结束节点(按照事务区间算,是471)
注意:
此pos结束节点介于“member表原始数据”与更新“name=’李四’”之前的数据,这样就可以恢复到更改“name=’李四’”之前的数据了。
操作如下:
[root@vm-002 ~]# /usr/bin/mysqlbinlog –stop-position=471 –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+—-+———–+—–+—–+———+
7 rows in set (0.00 sec)

恢复截止到更改“name=’李四’”之间的数据(按照事务区间算,是673)
[root@vm-002 ~]# /usr/bin/mysqlbinlog –stop-position=673 –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | 李四 | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+—-+———–+—–+—–+———+
7 rows in set (0.00 sec)

c)指定pso点区间恢复(部分恢复):
更新 name=’李四’ 这条数据,日志区间是Pos[538] –> End_log_pos[646],按事务区间是:Pos[471] –> End_log_pos[673]

更新 name=’小二’ 这条数据,日志区间是Pos[740] –> End_log_pos[848],按事务区间是:Pos[673] –> End_log_pos[875]

c1)
单独恢复 name=’李四’ 这步操作,可这样:
按照binlog日志区间单独恢复:
[root@vm-002 ~]# /usr/bin/mysqlbinlog –start-position=538 –stop-position=646 –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

按照事务区间单独恢复
[root@vm-002 ~]# /usr/bin/mysqlbinlog –start-position=471 –stop-position=673 –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

c2)
单独恢复 name=’小二’ 这步操作,可这样:
按照binlog日志区间单独恢复:
[root@vm-002 ~]# /usr/bin/mysqlbinlog –start-position=740 –stop-position=848 –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

按照事务区间单独恢复
[root@vm-002 ~]# /usr/bin/mysqlbinlog –start-position=673 –stop-position=875 –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

c3)
将 name=’李四’、name=’小二’ 多步操作一起恢复,需要按事务区间,可这样:
[root@vm-002 ~]# /usr/bin/mysqlbinlog –start-position=471 –stop-position=875 –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

查看数据库:
mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | 小二 | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | 李四 | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+—-+———–+—–+—–+———+
7 rows in set (0.00 sec)

这样,就恢复了删除前的数据状态了!!

—————–
另外:
也可指定时间节点区间恢复(部分恢复):
除了用pos节点的办法进行恢复,也可以通过指定时间节点区间进行恢复,按时间恢复需要用mysqlbinlog命令读取binlog日志内容,找时间节点。

如上,误删除ops库后:
先进行全备份恢复
[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql

查看ops数据库
mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+—-+———–+—–+—–+———+
2 rows in set (0.00 sec)

mysql>

查看mysq-bin00003日志,找出时间节点
[root@vm-002 ~]# cd /var/lib/mysql
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000003
………….
………….
BEGIN
/*!*/;
# at 173
#160925 21:57:19 server id 1 end_log_pos 201 Intvar
SET INSERT_ID=3/*!*/;
# at 201
#160925 21:57:19 server id 1 end_log_pos 444 Query thread_id=3 exec_time=0 error_code=0
use `ops`/*!*/;
SET TIMESTAMP=1474811839/*!*/;
insert into ops.member(`name`,`sex`,`age`,`classid`) values(‘yiyi’,’w’,20,’cls1′),(‘xiaoer’,’m’,22,’cls3′),(‘zhangsan’,’w’,21,’cls5′),(‘lisi’,’m’,20,’cls4′),(‘wangwu’,’w’,26,’cls6′)                               #执行的sql语句
/*!*/;
# at 444
#160925 21:57:19 server id 1 end_log_pos 471 Xid = 66    #开始执行的时间
COMMIT/*!*/;
# at 471
#160925 21:58:41 server id 1 end_log_pos 538 Query thread_id=3 exec_time=0 error_code=0    #结束时间
SET TIMESTAMP=1474811921/*!*/;
BEGIN
/*!*/;
# at 538
#160925 21:58:41 server id 1 end_log_pos 646 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1474811921/*!*/;
update ops.member set name=’李四’ where id=4     #执行的sql语句
/*!*/;
# at 646
#160925 21:58:41 server id 1 end_log_pos 673 Xid = 68    #开始执行的时间
COMMIT/*!*/;
# at 673
#160925 21:58:56 server id 1 end_log_pos 740 Query thread_id=3 exec_time=0 error_code=0   #结束时间
SET TIMESTAMP=1474811936/*!*/;
BEGIN
/*!*/;
# at 740
#160925 21:58:56 server id 1 end_log_pos 848 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1474811936/*!*/;
update ops.member set name=’小二’ where id=2      #执行的sql语句
/*!*/;
# at 848
#160925 21:58:56 server id 1 end_log_pos 875 Xid = 69   #开始执行的时间
COMMIT/*!*/;
# at 875
#160925 22:01:08 server id 1 end_log_pos 954 Query thread_id=3 exec_time=0 error_code=0    #结束时间
SET TIMESTAMP=1474812068/*!*/;
drop database ops
/*!*/;
# at 954
#160925 22:09:46 server id 1 end_log_pos 997 Rotate to mysql-bin.000004 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

恢复到更改“name=’李四’”之前的数据
[root@vm-002 ~]# /usr/bin/mysqlbinlog –start-datetime=”2016-09-25 21:57:19″ –stop-datetime=”2016-09-25 21:58:41″ –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+—-+———–+—–+—–+———+
7 rows in set (0.00 sec)

[root@vm-002 ~]# /usr/bin/mysqlbinlog –start-datetime=”2016-09-25 21:58:41″ –stop-datetime=”2016-09-25 21:58:56″ –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | 李四 | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+—-+———–+—–+—–+———+
7 rows in set (0.00 sec)

[root@vm-002 ~]# /usr/bin/mysqlbinlog –start-datetime=”2016-09-25 21:58:56″ –stop-datetime=”2016-09-25 22:01:08″ –database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
mysql> select * from member;
+—-+———–+—–+—–+———+
| id | name | sex | age | classid |
+—-+———–+—–+—–+———+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | 小二 | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | 李四 | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+—-+———–+—–+—–+———+
7 rows in set (0.00 sec)

这样,就恢复了删除前的状态了!

总结:
所谓恢复,就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。

***************当你发现自己的才华撑不起野心时,就请安静下来学习吧***************

mysql主从复制故障一则

给数据库用户配置
groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
rm -rf /home/mysql
mkdir -p /opt/mysql
chmod -R 755 /opt/mysql

chown -R mysql:mysql /opt/mysql

准备工作:安装基本依赖包,先用yum安装cmake、automake 、autoconf ,另MySQL 5.5.x需要最少安装的包有:bison,gcc、gcc-c++、ncurses-devel

yum -y install gcc gcc-c++ autoconf bison automake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel*

安装cmake(mysql5.5以后是通过cmake来编译的)

axel -n  10 http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
tar zxvf cmake-2.8.4.tar.gz
./bootstrap
gmake install
下载解压mysql 5.5.11
axel  -n 10 http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.30.tar.gz
tar zxfv  mysql-5.5.30.tar.gz

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DMYSQL_DATADIR=/opt/mysql \

-DSYSCONFDIR=/etc \

-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \

-DMYSQL_USER=mysql \

-DMYSQL_TCP_PORT=3306 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=0 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_SSL=no \

-DEXTRA_CHARSETS=all \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_READLINE=1 \

-DWITH_ZLIB=system \

-DINSTALL_PLUGINDIR=lib64/mysql/plugin \

-DINSTALL_LIBDIR=lib64/mysql \

-DINSTALL_INCLUDEDIR=include/mysql \

-DWITH_DEBUG=0

碰到了错误:
— Could NOT find Curses (missing:  CURSES_LIBRARY CURSES_INCLUDE_PATH)
CMake Error at cmake/readline.cmake:83 (MESSAGE):
 Curses library not found.  Please install appropriate package,
     remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
 cmake/readline.cmake:127 (FIND_CURSES)
 cmake/readline.cmake:217 (MYSQL_USE_BUNDLED_LIBEDIT)
 CMakeLists.txt:269 (MYSQL_CHECK_READLINE)
— Configuring incomplete, errors occurred!
是因为少了ncurses-devel这个包。
用yum安装后,然后把目录下的CMakeCache.txt 删除,否则还是报之前的错误
然后就是make  && make  install
cp support-files/my-medium.cnf /etc/my.cnf
chown -R mysql:mysql mysql/
opt]# /opt/mysql/scripts/mysql_install_db –user=mysql  –defaults-file=/etc/my.cnf –basedir=/usr/local/mysql –datadir=/opt/data
/usr/local/mysql/bin/mysqld_safe –user=mysql &  启动mysql
cp  mysql-5.5.30/support-files/mysql.server /etc/init.d/mysqld
chmod +x  /etc/init.d/mysqld
chown .mysql  /etc/init.d/mysqld

chkconfig –add  mysqld

chkconfig mysqld  on

接下来开始部署主从环境

在主服务器上配置mysql的配置文件

确保/etc/my.cnf中有如下参数,没有的话需手工添加,并重启mysql服务。
[mysqld]
log-bin=mysql-bin 启动二进制文件
server-id=1 服务器ID

然后进入到mysql数据库里进行授权

grant replication slave on *.* to ‘backup’@’x.x.x.x’ identified by ‘backup’;

查询主数据库状态,并记下FILE及Position的值,这个在后面配置从服务器的时候要用到

show master status;

+——————+———-+————–+——————+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000010 |      264 |              |                  |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

主数据库的操作就到这里,下面的操作在从服务器上操作

确保/etc/my.cnf中有log-bin=mysql-bin和server-id=1参数,并把server-id=1修改为server-id=10。修改之后如下所示:
[mysqld]
log-bin=mysql-bin 启动二进制文件
server-id=10 服务器ID
重启mysql服务。登录mysql,执行如下语句
change master to master_host=’o.o.o.o’,master_user=’backup’,master_password=’backup’,master_log_file=’mysql-bin.000010′,master_log_pos=264;
启动slave同步。
start slave;
检查主从同步,如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes,则主从复制连接正常。
show slave status\G

注: 删除mysql日志文件后报I/O error reading the header from the binary log, errno=175, io cache

data目录下日志文件很多,很烦,把mysql-bin.000001~mysql-bin.0000018都删掉了。结果报以下错误:

120914 10:23:31 [ERROR] I/O error reading the header from the binary log, errno=175, io cache code=0

120914 10:23:31 [ERROR] I/O error reading the header from the binary log

120914 10:23:31 [ERROR] Can’t init tc log

120914 10:23:31 [ERROR] Aborting

解决办法:

把剩下的有关日志的文件都删掉。把ibdata1,ib_logfile0,ib_logfile1,mysql-bin.index,再启动就可以了

mysql权限管理

MySQL分为普通用户与root用户。这两种用户的权限不一样。

一、权限表

安装MySQL时会自动安装一个名为mysql的数据库。mysql数据库里面存储的都是权限表。

1、User表

user表有39个字段。这些字段可以分为4类:

  • 用户列;
  • 权限列;
  • 安全列;
  • 资源控制列;

1、用户列

用户列包括Host、User、Password,分别表示主机名、用户名和密码。登录用的就是这个用户名与密码;

2、权限列

user表的权限列包括Select_priv、Insert_priv等以priv结尾的字段。

这些字段的值只有Y和N。Y表示该权限可以用到所有数据库上;N表示该权限不能用到所有数据库上;

通常,可以使用GRANT语句Wie用户赋予一些权限,也可以通过Update语句更新user表的方式来设置权限;

不过,修改user表之后,一定要执行一下FLUSH PRIVILEGES,否则可能会出现如下错误:

Error (1133): Can’t find any matching row in the user table

说明
Select_priv 确定用户是否可以通过SELECT命令选择数据
Insert_priv 确定用户是否可以通过INSERT命令插入数据
Update_priv 确定用户是否可以通过UPDATE命令修改现有数据
Delete_priv 确定用户是否可以通过DELETE命令删除现有数据
Create_priv 确定用户是否可以创建新的数据库和表
Drop_priv 确定用户是否可以删除现有数据库和表
Reload_priv 确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令,包括日志、权限、主机、查询和表
Shutdown_priv 确定用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时,都应当非常谨慎
Process_priv 确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv 确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv 确定用户是否可以将已经授予给该用户自己的权限再授予其他用户
References_priv 目前只是某些未来功能的占位符;现在没有作用
Index_priv 确定用户是否可以创建和删除表索引
Alter_priv 确定用户是否可以重命名和修改表结构
Show_db_priv 确定用户是否可以查看服务器上所有数据库的名字,包括用户拥有足够访问权限的数据库
Super_priv 确定用户是否可以执行某些强大的管理功能,例如通过KILL命令删除用户进程,使用SET GLOBAL修改全局MySQL变量,执行关于复制和日志的各种命令
Create_tmp_table_priv 确定用户是否可以创建临时表
Lock_tables_priv 确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改
Execute_priv 确定用户是否可以执行存储过程
Repl_slave_priv 确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中,有利于主机和客户机之间的通信
Repl_client_priv 确定用户是否可以确定复制从服务器和主服务器的位置
Create_view_priv 确定用户是否可以创建视图
Show_view_priv 确定用户是否可以查看视图或了解视图如何执行
Create_routine_priv 确定用户是否可以更改或放弃存储过程和函数
Alter_routine_priv 确定用户是否可以修改或删除存储函数及函数
Create_user_priv 确定用户是否可以执行CREATE USER命令,这个命令用于创建新的MySQL账户
Event_priv 确定用户能否创建、修改和删除事件
Trigger_priv 确定用户能否创建和删除触发器

Update修改权限:

UPDATE user SET Select_priv = 'N'
WHERE Host = '%'

GRANT赋予权限

3、安全列

user表的安全列有4个字段:

  • ssl_type;
  • ssl_cipher;
  • x509_issuer;
  • x509_subject;

ssl用于加密;x509标准可以用来标识用户。普通的发行版都没有加密功能。可以使用SHOW VARIABLES LIKE ‘have_openssl’语句来查看是否具有ssl功能。如果取值为DISABLED,那么则没有ssl加密功能。

4、资源控制列

user表的4个资源控制列是:

  • max_questions:每小时可以允许执行多少次查询;
  • max_updates:每小时可以允许执行多少次更新;
  • max_connections:每小时可以建立多少连接;
  • max_user_connections:单个用户可以同时具有的连接数。

默认值为0,表示无限制。

2、db表和host表

db表存储了某个用户对一个数据库的权限。

host表中存储了某个主机对数据库的操作权限,配合db表对给定主机上数据库级操作权限做更细致的控制;但是很少用,新版本已经取消了host表;

1、用户列

db表的用户列有3个字段:

  • Host:主机名;
  • Db:数据库名;
  • User:用户名;

2、权限列

db表:

  • Create_routine_priv:是否具有创建存储过程权限;
  • Alter_routine_priv:是否具有修改存储过程权限;

user表中的权限是针对所有数据库的,如果user表中的Select_priv字段取值为Y,那么该用户可以查询所有数据库中的表;

如果为某个用户值设置了查询test表的权限,那么user表的Select_priv字段的取值为N。而这个SELECT权限则记录在db表中。db表中的Select_priv字段的取值将会是Y。

用户先根据user表的内容获取权限,然后再根据db表的内容获取权限。

3、tables_priv表和columns_priv表

   tables_priv:可以对单个表进行权限设置:

  • tables_priv表包含8个字段:
  • Host:主机名;
  • DB:数据库名;
  • User:用户名;
  • Table_name:表名
  • Table_priv:对表进行操作的权限(Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter)
  • Column_priv:对表中的数据列进行操作的权限(Select,Insert,Update,Rederences);
  • Timestamp:修改权限的事件
  • Grantor:权限的设置者

columns_priv:可以对单个数据列进行权限设置,有7个列,作用同上:

Host、Db、User、Table_name、Column_name、Column_priv、Timestamp。

  MySQL权限分配是按照user表-> db表 -> table_priv表 -> columns_priv表的顺序进行分配的。

  在数据库系统中,先判断user表中的值是否为’Y’,如果user表中的值是’Y’,就不需要检查后面的表。如果user表为N,则一次检查后面的表。

4、procs_priv表

  • procs_priv表可以对存储过程和存储函数进行权限设置。
  • procs_priv表包含8个字段,分别是:
  • Host:主机名;
  • Db:数据库名;
  • User:用户名;
  • Routine_name:存储过程或函数名称;
  • Routine_type:类型(取值有:FUNCTION或PROCEDURE);
  • Proc_priv:拥有的权限(Execute:执行;Alter Routine:修改;Grant:权限赋予);
  • Timestamp:字段存储更新的时间;
  • Grantor:字段设置者;

二、账户管理

账户管理是MySQL用户管理最基本的内容。包括登录,退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。

其中登录方式非常简单,在这个地址有:http://www.cnblogs.com/kissdodog/p/4154068.html

1、新建普通用户

在MySQL数据库中,建立用户有3种方式:

  • 使用CREATE USER语句来创建新的用户;
  • 直接在mysql.user表中INSERT用户;
  • 使用GRANT语句来新建用户;

1、CREATE USER

使用CREATE USER语句创建用户,必须要拥有CREATE USER权限。其格式如下:

CREATE USER user[IDENTIFIED BY [PASSWORD] 'password'],
[user[IDENTIFIED BY [PASSWORD] 'password']]...

其中,user参数表示新建用户的账户,user由用户名(User)和主机名(Host)构成;IDENTIFIED BY关键字用来设置用户的密码;password参数表示用户的密码;如果密码是一个普通的字符串,就不需要使用PASSWORD关键字。可以没有初始密码。

示例:

CREATE USER 'admin'@'%' IDENTIFIED BY 'admin'

执行之后user表会增加一行记录,但权限暂时全部为‘N’。

2、用INSERT语句新建普通用户

可以使用INSERT语句直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。

另外,ssl_cipher、x509_issuer、x509_subject没有值,必须要设置值,否则INSERT语句无法执行。

示例:

INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES('%','newuser1',PASSWORD('123456'),'','','')

执行INSERT之后,要使用命令:

FLUSH PRIVILEGES

命令来使用户生效。

3、用GRANT语句来新建普通用户

用GRANT来创建新的用户时,能够在创建用户时为用户授权。但需要拥有GRANT权限。

语法如下:

GRANT priv_type ON database.table
TO user[IDENTIFIED BY [PASSWORD] 'password']
[,user [IDENTIFIED BY [PASSWORD] 'password']...]
  • priv_type:参数表示新yoghurt的权限;
  • databse.table:参数表示新用户的权限范围;
  • user:参数新用户的账户,由用户名和主机构成;
  • IDENTIFIED BY关键字用来设置密码;
  • password:新用户密码;

GRANT语句可以同时创建多个用户。

GRANT SELECT ON *.* TO netUser@'%'

*.*与db.*的区别在于。*.*对所有数据库生效,所以user表的SELECT会变为Y。而db.*user表为’N’,更改的是Db表。

2、删除普通用户

1、DROP USER语句删除普通用户

需要拥有DROP USER权限。语法如下:

DROP USER user[,user]...

user是需要删除的用户,由用户名(User)和主机名(Host)构成。

DROP USER 'newuser1'@'%'

2、DELETE语句删除普通用户

可以使用DELETE语句直接将用户的信息从mysql.user表中删除。但必须拥有对mysql.user表的DELETE权限。

DELETE FROM mysql.user WHERE Host = '%' AND User = 'admin'

删除完成后,一样要FLUSH PRIVILEGES才生效。

3、修改密码

1、使用mysqladmin命令来修改root用户的密码

语法:

mysqladmin -u -username -p password "new_password"

新密码(new_password)必须用括号括起来,单引号会报错。

示例:

(注:这个待验证,我怎么执行都不成功!)

2、修改user表

UPDATE user表的passwor字段的值,也可以达到修改密码的目的;

UPDATE user SET Password = PASSWORD('123') WHERE USER = 'myuser'

FLUSH PRIVILEGES后生效。

3、使用SET语句来修改密码

使用root用户登录到MySQL服务器后,可以使用SET语句来修改密码:

修改自己的密码,不需要用户名

SET PASSWORD = PASSWORD("123");

修改其他用户密码:

SET PASSWORD FOR 'myuser'@'%'=PASSWORD("123456")

FOR 用户名@主机名

4、GRANT语句来修改普通用户的密码

使用GRANT语句修改普通用户的密码,必须拥有GRANT权限。

GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']

示例:

GRANT SELECT ON *.* TO 'test3'@'%' IDENTIFIED BY '123'

4、忘记密码的解决方案

如果root用户密码丢失了,会给用户造成很大的麻烦。但是,可以通过某种特殊方法登录到root用户下,然后修改密码。

1、使用–skip-grant-tables选项启动MySQL服务

该选项将使MySQL服务器停止权限判断,任何用户都能够访问数据库。

在Windows操作系统下,使用mysqlid或mysqlid-nt来启动MySQL服务。

mysqld命令:

mysqld –skip-grant-tables

mysqld-nt命令:

mysqld-nt –skip-grant-tables

net start mysql命令:

net start mysql –skip-grant-tables

2、登录root用户,并且设置新密码

mysql -u root

必须使用UPDATE语句更新mysql数据库下的user表,而不能使用SET语句,因为–skip-grant-tables不能使用SET语句。

修改完密码以后,FLUSH PRIVILEGES语句加载权限表,新密码即生效。

三、权限管理

1、MySQL的各种权限

权限名称 对应user表中的列 权限的范围
CREATE Create_priv 数据库、表或索引
DROP Drop_priv 数据库或表
GRANT OPTION Grant_priv 数据库、表、存储过程或函数
REFERENCES References_priv 数据库或表
ALTER Alter_priv 修改表
DELETE Delete_priv 删除表
INDEX Index_priv 用索引查询表
INSERT Insert_priv 插入表
SELECT Select_priv 查询表
UPDATE Update_priv 更新表
CREATE VIEW Create_view_priv 创建视图
SHOW VIEW Show_view_priv 查看视图
ALTER ROUTINE Alter_routine 修改存储过程或存储函数
CREATE ROUTINE Create_routine_priv 创建存储过程或存储函数
EXECUTE Execute_priv 执行存储过程或存储函数
FILE File_priv 加载服务器主机上的文件
CREATE TEMPORARY TABLES Create_temp_table_priv 创建临时表
LOCK TABLES Lock_tables_priv 锁定表
CREATE USER Create_user_priv 创建用户
PROCESS Process_priv 服务器管理
RELOAD Reload_priv 重新加载权限表
REPLICATION CLIENT Repl_client_priv 服务器管理
REPLICATION SLAVE Repl_slave_priv 服务器管理
SHOW DATABASES Show_db_priv 查看数据库
SHUTDOWN Shutdown_priv 关闭服务器
SUPER Super_priv 超级权限

 

2、授权

GRANT语法如下:

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[,user [IDENTIFIED BY [PASSWORD] 'password']]...
WITH with_option[with_option]
  • priv_type参数表示权限类型;
  • column_list:参数表示权限作用于哪些列上,没设置则位于整个表上;
  • user参数由用户名和主机名构成;形式是”‘username’@’hostname'”;
  • IDENTIFIED BY参数用于为用户设置密码;
  • password:用户新密码;

WITH关键字后面带有一个或多个with_option参数。有5个选项:

  • GRANT OPTION:被授权的用户可以将这些权限赋予给别的用户;
  • MAX_QUERIES_PER_HOUR count:设置没消失可以允许执行count次查询;
  • MAX_UPDATES_PER_HOUR count:设置每个消失可以允许执行count次更新;
  • MAX_CONNECTIONS_PER_HOUR count:设置每小时可以建立count个连接;
  • MAX_USER_CONNECTIONS count:设置单个用户可以同时具有的count个连接数;

示例:

GRANT SELECT,UPDATE ON *.*
    TO 'myuser'@'%'
WITH GRANT OPTION;

3、收回权限

收回权限,就是取消某个用户的某些权限。MySQL中使用REVOKE关键字来为用户设置权限。

语法如下:

REVOKE priv_type[(column_list)]
ON database.table
FROM user[,user]

收回全部权限的REVOKE语句的基本语法如下:

REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]...

示例:回收用户myuser的SELECT权限

REVOKE SELECT ON *.*FROM 'myuser'@'%'

收回myuser的所有权限:

REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'myuser'@'%'

4、查看权限

SHOW GRANTS语句用于查看权限。同时mysql数据库下的user表中存储着用户的基本权限。

SELECT * FROM mysql.user

SHOW GRANTS