MySQL的数据类型

一、MySQL的数据类型

主要包括以下五大类:

整数类型:BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型:FLOAT、DOUBLE、DECIMAL

字符串类型:CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型:Date、DateTime、TimeStamp、Time、Year

其他数据类型:BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

 

1、整型

MySQL数据类型 含义(有符号)
tinyint(m) 1个字节  范围(-128~127)
smallint(m) 2个字节  范围(-32768~32767)
mediumint(m) 3个字节  范围(-8388608~8388607)
int(m) 4个字节  范围(-2147483648~2147483647)
bigint(m) 8个字节  范围(+-9.22*10的18次方)

取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~256)。

int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个m有什么用。

 

2、浮点型(float和double)

MySQL数据类型 含义
float(m,d) 单精度浮点型    8位精度(4字节)     m总个数,d小数位
double(m,d) 双精度浮点型    16位精度(8字节)    m总个数,d小数位

设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位。整数部分最大是3位,如果插入数12.123456,存储的是12.1234,如果插入12.12,存储的是12.1200.

 

3、定点数

浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位。

 

4、字符串(char,varchar,_text)

MySQL数据类型 含义
char(n) 固定长度,最多255个字符
varchar(n) 固定长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
longtext 可变长度,最多2的32次方-1个字符

char和varchar:

1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。

2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),

所以varchar(4),存入3个字符将占用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。
varchar和text:

1.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),text是实际字符数+2个字

节。

2.text类型不能有默认值。

3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下,text的索引似乎不起作用。

 

5.二进制数据(_Blob)

1._BLOB和_text存储方式不同,_TEXT以文本方式存储,英文存储区分大小写,而_Blob是以二进制方式存储,不分大小写。

2._BLOB存储的数据只能整体读出。

3._TEXT可以指定字符集,_BLO不用指定字符集。

 

6.日期时间类型

MySQL数据类型 含义
date 日期 ‘2008-12-2’
time 时间 ’12:25:36′
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间

若定义一个字段为timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。

 

数据类型的属性

 

MySQL关键字 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增,适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集

 

二、MYSQL数据类型的长度和范围

各数据类型及字节长度一览表:

数据类型 字节长度 范围或用法
Bit 1 无符号[0,255],有符号[-128,127],天缘博客备注:BIT和BOOL布尔型都占用1字节
TinyInt 1 整数[0,255]
SmallInt 2 无符号[0,65535],有符号[-32768,32767]
MediumInt 3 无符号[0,2^24-1],有符号[-2^23,2^23-1]]
Int 4 无符号[0,2^32-1],有符号[-2^31,2^31-1]
BigInt 8 无符号[0,2^64-1],有符号[-2^63 ,2^63 -1]
Float(M,D) 4 单精度浮点数。天缘博客提醒这里的D是精度,如果D<=24则为默认的FLOAT,如果D>24则会自动被转换为DOUBLE型。
Double(M,D) 8  双精度浮点。
Decimal(M,D) M+1或M+2 未打包的浮点数,用法类似于FLOAT和DOUBLE,天缘博客提醒您如果在ASP中使用到Decimal数据类型,直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。
Date 3 以YYYY-MM-DD的格式显示,比如:2009-07-19
Date Time 8 以YYYY-MM-DD HH:MM:SS的格式显示,比如:2009-07-19 11:22:30
TimeStamp 4 以YYYY-MM-DD的格式显示,比如:2009-07-19
Time 3 以HH:MM:SS的格式显示。比如:11:22:30
Year 1 以YYYY的格式显示。比如:2009
Char(M) M
定长字符串。
VarChar(M) M 变长字符串,要求M<=255
Binary(M) M 类似Char的二进制存储,特点是插入定长不足补0
VarBinary(M) M 类似VarChar的变长二进制存储,特点是定长不补0
Tiny Text Max:255 大小写不敏感
Text Max:64K 大小写不敏感
Medium Text Max:16M 大小写不敏感
Long Text Max:4G 大小写不敏感
TinyBlob Max:255 大小写敏感
Blob Max:64K 大小写敏感
MediumBlob Max:16M 大小写敏感
LongBlob Max:4G 大小写敏感
Enum 1或2 最大可达65535个不同的枚举值
Set 可达8 最大可达64个不同的值
Geometry
Point
LineString
Polygon
MultiPoint
MultiLineString
MultiPolygon
GeometryCollection

三、使用建议

1、在指定数据类型的时候一般是采用从小原则,比如能用TINY INT的最好就不用INT,能用FLOAT类型的就不用DOUBLE类型,这样会对MYSQL在运行效率上提高很大,尤其是大数据量测试条件下。

2、不需要把数据表设计的太过复杂,功能模块上区分或许对于后期的维护更为方便,慎重出现大杂烩数据表

3、数据表和字段的起名字也是一门学问

4、设计数据表结构之前请先想象一下是你的房间,或许结果会更加合理、高效

5、数据库的最后设计结果一定是效率和可扩展性的折中,偏向任何一方都是欠妥的

 

选择数据类型的基本原则

前提:使用适合存储引擎。

选择原则:根据选定的存储引擎,确定如何选择合适的数据类型。

下面的选择方法按存储引擎分类:

  • MyISAM 数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。
  • MEMORY存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。
  • InnoDB 存储引擎和数据列:建议使用 VARCHAR类型。

对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),因此在本质上,使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而,主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR,因 此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

下面说一下固定长度数据列与可变长度的数据列。

char与varchar

CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果,说明了CHAR和VARCHAR之间的差别:

CHAR(4) 存储需求 VARCHAR(4) 存储需求
‘    ‘ 4个字节 1个字节
‘ab’ ‘ab  ‘ 4个字节 ‘ab ‘ 3个字节
‘abcd’ ‘abcd’ 4个字节 ‘abcd’ 5个字节
‘abcdefgh’ ‘abcd’ 4个字节 ‘abcd’ 5个字节

请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格模式,超过列长度不的值保存,并且会出现错误。

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同,因为检索时从CHAR列删除了尾部的空格。通过下面的例子说明该差别:
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO vc VALUES (‘ab  ‘, ‘ab  ‘);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT(v, ‘+’), CONCAT(c, ‘+’) FROM vc;
+—————-+—————-+
| CONCAT(v, ‘+’) | CONCAT(c, ‘+’) |
+—————-+—————-+
| ab  +          | ab+            |
+—————-+—————-+
1 row in set (0.00 sec)

text和blob

 

在使用text和blob字段类型时要注意以下几点,以便更好的发挥数据库的性能。

①BLOB和TEXT值也会引起自己的一些问题,特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的”空洞”,以后填入这些”空洞”的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理.

②使用合成的(synthetic)索引。合成的索引列在某些时候是有用的。一种办法是根据其它的列的内容建立一个散列值,并把这个值存储在单独的数据列中。接下来你就可以通过检索散列值找到数据行了。但是,我们要注意这种技术只能用于精确匹配的查询(散列值对于类似<或>=等范围搜索操作符 是没有用处的)。我们可以使用MD5()函数生成散列值,也可以使用SHA1()或CRC32(),或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样,如果散列算法生成的字符串带有尾部空格,就不要把它们存储在CHAR或VARCHAR列中,它们会受到尾部空格去除的影响。

合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。

③在不必要的时候避免检索大型的BLOB或TEXT值。例如,SELECT *查询就不是很好的想法,除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则,你可能毫无目的地在网络上传输大量的值。这也是 BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子。你可以搜索索引列,决定那些需要的数据行,然后从合格的数据行中检索BLOB或 TEXT值。

④把BLOB或TEXT列分离到单独的表中。在某些环境中,如果把这些数据列移动到第二张数据表中,可以让你把原数据表中 的数据列转换为固定长度的数据行格式,那么它就是有意义的。这会减少主表中的碎片,使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

浮点数与定点数

为了能够引起大家的重视,在介绍浮点数与定点数以前先让大家看一个例子:
mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.29 sec)

mysql> insert into test values(131072.32,131072.32);
Query OK, 1 row affected (0.07 sec)

mysql> select * from test;
+———–+———–+
| c1        | c2        |
+———–+———–+
| 131072.31 | 131072.32 |
+———–+———–+
1 row in set (0.00 sec)

从上面的例子中我们看到c1列的值由131072.32变成了131072.31,这就是浮点数的不精确性造成的。

在mysql中float、double(或real)是浮点数,decimal(或numberic)是定点数。

浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围;它的缺点是会引起精度问题。在今后关于浮点数和定点数的应用中,大家要记住以下几点:

  1. 浮点数存在误差问题;
  2. 对货币等对精度敏感的数据,应该用定点数表示或存储;
  3. 编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较;
  4. 要注意浮点数中一些特殊值的处理。

MySQL的binlog日志

binlog 基本认识
MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。

一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:
其一:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
其二:自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。

二进制日志包括两类文件:二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件,二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
一、开启binlog日志:
vi编辑打开mysql配置文件
# vi /usr/local/mysql/etc/my.cnf
在[mysqld] 区块
设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名);

重启mysqld服务使配置生效
# pkill mysqld
# /usr/local/mysql/bin/mysqld_safe –user=mysql &
二、也可登录mysql服务器,通过mysql的变量配置表,查看二进制日志是否已开启 单词:variable[ˈvɛriəbəl] 变量

登录服务器
# /usr/local/mysql/bin/mysql -uroot -p123456

mysql> show variables like ‘log_%’;
+—————————————-+—————————————+
| Variable_name | Value |
+—————————————-+—————————————+
| log_bin | ON | ——> ON表示已经开启binlog日志
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /usr/local/mysql/data/martin.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
+—————————————-+—————————————+

三、常用binlog日志操作命令
1.查看所有binlog日志列表
mysql> show master logs;

2.查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值
mysql> show master status;

3.刷新log日志,自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

4.重置(清空)所有binlog日志
mysql> reset master;
四、查看某个binlog日志内容,常用有两种方式:

1.使用mysqlbinlog自带查看命令法:
注: binlog是二进制文件,普通文件查看器cat more vi等都无法打开,必须使用自带的 mysqlbinlog 命令查看
binlog日志与数据库文件在同目录中(我的环境配置安装是选择在/usr/local/mysql/data中)
在MySQL5.5以下版本使用mysqlbinlog命令时如果报错,就加上 “–no-defaults”选项

# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013
下面截取一个片段分析:

…………………………………………………………………….
# at 552
#131128 17:50:46 server id 1 end_log_pos 665 Query thread_id=11 exec_time=0 error_code=0 —->执行时间:17:50:46;pos点:665
SET TIMESTAMP=1385632246/*!*/;
update zyyshop.stu set name=’李四’ where id=4 —->执行的SQL
/*!*/;
# at 665
#131128 17:50:46 server id 1 end_log_pos 692 Xid = 1454 —->执行时间:17:50:46;pos点:692
…………………………………………………………………….

注: server id 1 数据库主机的服务号;
end_log_pos 665 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 查询总条数(不指定就是所有行)

截取部分查询结果:
*************************** 20. row ***************************
Log_name: mysql-bin.000021 ———————————————-> 查询的binlog日志文件名
Pos: 11197 ———————————————————-> pos起始点:
Event_type: Query ———————————————————-> 事件类型:Query
Server_id: 1 ————————————————————–> 标识是由哪台服务器执行的
End_log_pos: 11308 ———————————————————-> pos结束点:11308(即:下行的pos起始点)
Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,’asdf8er5′) —> 执行的sql语句
*************************** 21. row ***************************
Log_name: mysql-bin.000021
Pos: 11308 ———————————————————-> pos起始点:11308(即:上行的pos结束点)
Event_type: Query
Server_id: 1
End_log_pos: 11417
Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */
*************************** 22. row ***************************
Log_name: mysql-bin.000021
Pos: 11417
Event_type: Query
Server_id: 1
End_log_pos: 11510
Info: use `zyyshop`; DROP TABLE IF EXISTS `type`

这条语句可以将指定的binlog日志文件,分成有效事件行的方式返回,并可使用limit指定pos点的起始偏移,查询条数;

A.查询第一个(最早)的binlog日志:
mysql> show binlog events\G;

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

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

D.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,查询10条
mysql> show binlog events in ‘mysql-bin.000021’ from 8224 limit 10\G;

E.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,偏移2行,查询10条
mysql> show binlog events in ‘mysql-bin.000021’ from 8224 limit 2,10\G;
五、恢复binlog日志实验(zyyshop是数据库)
1.假设现在是凌晨4:00,我的计划任务开始执行一次完整的数据库备份:

将zyyshop数据库备份到 /root/BAK.zyyshop.sql 文件中:
# /usr/local/mysql/bin/mysqldump -uroot -p123456 -lF –log-error=/root/myDump.err -B zyyshop > /root/BAK.zyyshop.sql
……

大约过了若干分钟,备份完成了,我不用担心数据丢失了,因为我有备份了,嘎嘎~~~

由于我使用了-F选项,当备份工作刚开始时系统会刷新log日志,产生新的binlog日志来记录备份之后的数据库“增删改”操作,查看一下:
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000023 | 120 | | |
+——————+———-+————–+——————+
也就是说, mysql-bin.000023 是用来记录4:00之后对数据库的所有“增删改”操作。
2.早9:00上班了,业务的需求会对数据库进行各种“增删改”操作~~~~~~~
@ 比如:创建一个学生表并插入、修改了数据等等:
CREATE TABLE IF NOT EXISTS `tt` (
`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;
导入实验数据
mysql> insert into zyyshop.tt(`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′);
查看数据
mysql> select * from zyyshop.tt;
+—-+———-+—–+—–+———+
| id | name | sex | age | classid |
+—-+———-+—–+—–+———+
| 1 | yiyi | w | 20 | cls1 |
| 2 | xiaoer | m | 22 | cls3 |
| 3 | zhangsan | w | 21 | cls5 |
| 4 | lisi | m | 20 | cls4 |
| 5 | wangwu | w | 26 | cls6 |
+—-+———-+—–+—–+———+
中午时分又执行了修改数据操作
mysql> update zyyshop.tt set name=’李四’ where id=4;
mysql> update zyyshop.tt set name=’小二’ where id=2;

修改后的结果:
mysql> select * from zyyshop.tt;
+—-+———-+—–+—–+———+
| id | name | sex | age | classid |
+—-+———-+—–+—–+———+
| 1 | yiyi | w | 20 | cls1 |
| 2 | 小二 | m | 22 | cls3 |
| 3 | zhangsan | w | 21 | cls5 |
| 4 | 李四 | m | 20 | cls4 |
| 5 | wangwu | w | 26 | cls6 |
+—-+———-+—–+—–+———+
假设此时是下午18:00,莫名地执行了一条悲催的SQL语句,整个数据库都没了:
mysql> drop database zyyshop;
3.此刻杯具了,别慌!先仔细查看最后一个binlog日志,并记录下关键的pos点,到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步);

备份一下最后一个binlog日志文件:
# ll /usr/local/mysql/data | grep mysql-bin
# cp -v /usr/local/mysql/data/mysql-bin.000023 /root/

此时执行一次刷新日志索引操作,重新开始新的binlog日志记录文件,理论说 mysql-bin.000023 这个文件不会再有后续写入了(便于我们分析原因及查找pos点),以后所有数据库操作都会写入到下一个日志文件;
mysql> flush logs;
mysql> show master status;

4.读取binlog日志,分析问题
方式一:使用mysqlbinlog读取binlog日志:
# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000023

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

以下为末尾片段:
+——————+——+————+———–+————-+————————————————————+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————+——+————+———–+————-+————————————————————+
| mysql-bin.000023 | 922 | Xid | 1 | 953 | COMMIT /* xid=3820 */ |
| mysql-bin.000023 | 953 | Query | 1 | 1038 | BEGIN |
| mysql-bin.000023 | 1038 | Query | 1 | 1164 | use `zyyshop`; update zyyshop.tt set name=’李四’ where id=4|
| mysql-bin.000023 | 1164 | Xid | 1 | 1195 | COMMIT /* xid=3822 */ |
| mysql-bin.000023 | 1195 | Query | 1 | 1280 | BEGIN |
| mysql-bin.000023 | 1280 | Query | 1 | 1406 | use `zyyshop`; update zyyshop.tt set name=’小二’ where id=2|
| mysql-bin.000023 | 1406 | Xid | 1 | 1437 | COMMIT /* xid=3823 */ |
| mysql-bin.000023 | 1437 | Query | 1 | 1538 | drop database zyyshop |
+——————+——+————+———–+————-+————————————————————+

通过分析,造成数据库破坏的pos点区间是介于 1437–1538 之间,只要恢复到1437前就可。
5.现在把凌晨备份的数据恢复:

# /usr/local/mysql/bin/mysql -uroot -p123456 -v < /root/BAK.zyyshop.sql;

注: 至此截至当日凌晨(4:00)前的备份数据都恢复了。
但今天一整天(4:00–18:00)的数据肿么办呢?就得从前文提到的 mysql-bin.000023 新日志做文章了……
6.从binlog日志恢复数据

恢复语法格式:
# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

常用选项:
–start-position=953 起始pos点
–stop-position=1437 结束pos点
–start-datetime=”2013-11-29 13:18:54″ 起始时间点
–stop-datetime=”2013-11-29 13:21:53″ 结束时间点
–database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库,只限本地log日志)

不常用选项:
-u –user=name Connect to the remote server as username.连接到远程主机的用户名
-p –password[=name] Password to connect to remote server.连接到远程主机的密码
-h –host=name Get the binlog from server.从远程主机上获取binlog日志
–read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志

小结:实际是将读出的binlog日志内容,通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径;

A.完全恢复(本例不靠谱,因为最后那条 drop database zyyshop 也在日志里,必须想办法把这条破坏语句排除掉,做部分恢复)
# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000021 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop

B.指定pos结束点恢复(部分恢复):
@ –stop-position=953 pos结束点
注:此pos结束点介于“导入实验数据”与更新“name=’李四’”之间,这样可以恢复到更改“name=’李四’”之前的“导入测试数据”
# /usr/local/mysql/bin/mysqlbinlog –stop-position=953 –database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop

在另一终端登录查看结果(成功恢复了):
mysql> select * from zyyshop.tt;
+—-+———-+—–+—–+———+
| id | name | sex | age | classid |
+—-+———-+—–+—–+———+
| 1 | yiyi | w | 20 | cls1 |
| 2 | xiaoer | m | 22 | cls3 |
| 3 | zhangsan | w | 21 | cls5 |
| 4 | lisi | m | 20 | cls4 |
| 5 | wangwu | w | 26 | cls6 |
+—-+———-+—–+—–+———+

C.指定pso点区间恢复(部分恢复):
更新 name=’李四’ 这条数据,日志区间是Pos[1038] –> End_log_pos[1164],按事务区间是:Pos[953] –> End_log_pos[1195];

更新 name=’小二’ 这条数据,日志区间是Pos[1280] –> End_log_pos[1406],按事务区间是:Pos[1195] –> End_log_pos[1437];

c1.单独恢复 name=’李四’ 这步操作,可这样:
# /usr/local/mysql/bin/mysqlbinlog –start-position=1038 –stop-position=1164 –database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop

也可以按事务区间单独恢复,如下:
# /usr/local/mysql/bin/mysqlbinlog –start-position=953 –stop-position=1195 –database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
c2.单独恢复 name=’小二’ 这步操作,可这样:
# /usr/local/mysql/bin/mysqlbinlog –start-position=1280 –stop-position=1406 –database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop

也可以按事务区间单独恢复,如下:
# /usr/local/mysql/bin/mysqlbinlog –start-position=1195 –stop-position=1437 –database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
c3.将 name=’李四’、name=’小二’ 多步操作一起恢复,需要按事务区间,可这样:
# /usr/local/mysql/bin/mysqlbinlog –start-position=953 –stop-position=1437 –database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
D.在另一终端登录查看目前结果(两名称也恢复了):
mysql> select * from zyyshop.tt;
+—-+———-+—–+—–+———+
| id | name | sex | age | classid |
+—-+———-+—–+—–+———+
| 1 | yiyi | w | 20 | cls1 |
| 2 | 小二 | m | 22 | cls3 |
| 3 | zhangsan | w | 21 | cls5 |
| 4 | 李四 | m | 20 | cls4 |
| 5 | wangwu | w | 26 | cls6 |
+—-+———-+—–+—–+———+

E.也可指定时间区间恢复(部分恢复):除了用pos点的办法进行恢复,也可以通过指定时间区间进行恢复,按时间恢复需要用mysqlbinlog命令读取binlog日志内容,找时间节点。
比如,我把刚恢复的tt表删除掉,再用时间区间点恢复
mysql> drop table tt;

@ –start-datetime=”2013-11-29 13:18:54″ 起始时间点
@ –stop-datetime=”2013-11-29 13:21:53″ 结束时间点

# /usr/local/mysql/bin/mysqlbinlog –start-datetime=”2013-11-29 13:18:54″ –stop-datetime=”2013-11-29 13:21:53″ –database=zyyshop /usr/local/mysql/data/mysql-bin.000021 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop

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

mysql5.7配置文件优化

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
#支持符号链接,就是可以通过软连接的方式,管理其他目录的数据库,最好不要开启,当一个磁盘或分区空间不够时,可以开启该参数将数据存储到其他的磁盘或分区。
#http://blog.csdn.net/moxiaomomo/article/details/17092871
symbolic-links=0

########basic settings########
server-id = 11
port = 3306
user = mysql
#设置autocommit=0,则用户将一直处于某个事务中,直到执行一条commit提交或rollback语句才会结束当前事务重新开始一个新的事务。set autocommit=0的好处是在频繁开启事务的场景下,减少一次begin的交互。
autocommit = 1
#utf8mb4编码是utf8编码的超集,兼容utf8,并且能存储4字节的表情字符。
#采用utf8mb4编码的好处是:存储与获取数据的时候,不用再考虑表情字符的编码与解码问题。
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
# 对于同一主机,如果有超出该参数值个数的中断错误连接,则该主机将被禁止连接。如需对该主机进行解禁,执行:FLUSH HOST。
max_connect_errors = 1000
#数据库隔离级别
transaction_isolation = READ-COMMITTED
#MySQL在完成某些join(连接)需求的时候,为了减少参与join的“被驱动表”的读取次数以提高性能,需要使用到join buffer来协助完成join操作当join buffer 太小,MySQL不会将该buffer存入磁盘文件而是先将join buffer中的结果与需求join的表进行操作,然后清空join buffer中的数据,继续将剩余的结果集写入次buffer中
join_buffer_size = 128M
tmp_table_size = 64M
tmpdir = /tmp
#该值设置过小将导致单个记录超过限制后写入数据库失败,且后续记录写入也将失败
max_allowed_packet = 64M
#mysql在关闭一个交互的连接之前所要等待的秒数
interactive_timeout = 1200
#mysql在关闭一个非交互的连接之前所要等待的秒数
wait_timeout = 600
#MySQL读入缓冲区的大小
read_buffer_size = 16M
#MySQL的随机读缓冲区大小
read_rnd_buffer_size = 8M
#MySQL的顺序读缓冲区大小
sort_buffer_size = 8M
########log settings########
log_error = /var/log/docker_log/mysql/error.log
#开启慢查询日志
slow_query_log = 1
#超出次设定值的SQL即被记录到慢查询日志
long_query_time = 6
slow_query_log_file = /var/log/docker_log/mysql/slow.log
#表示记录下没有使用索引的查询
log_queries_not_using_indexes = 1
#记录管理语句
log_slow_admin_statements = 1
#开启复制从库复制的慢查询的日志
log_slow_slave_statements = 1
#设置每分钟增长的没有使用索引查询的日志数量
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
########replication settings########
#将master.info和relay.info保存在表中
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
#当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。设置为零是让系统自行决定
sync_binlog = 5
#开启全局事务ID,GTID能够保证让一个从服务器到其他的从服务器那里实现数据复制而且能够实现数据整合的
gtid_mode = on
#开启gtid,必须主从全开
enforce_gtid_consistency = 1
#从服务器的更新是否写入二进制日志
log_slave_updates = 1
#三种模式 STATEMENT(有可能主从数据不一致,日质量小)、ROW(产生大量二进制日志)、MIXED
binlog_format = mixed
#relay-log日志记录的是从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件,然后SQL线程会读取relay-log日志的内容并应用到从服务器
relay_log = /var/log/docker_log/mysql/relay.log
relay_log_recovery = 1
#开启简单gtid,开启此项会提升mysql执行恢复的性能
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
#这个参数在一开始初始化时就要加入my.cnf里,如果已经创建了表,再修改,启动MySQL会报错。最好为8K
#innodb_page_size = 16K
innodb_page_size = 8K
#数据缓冲区buffer pool大小,建议使用物理内存的 75%
innodb_buffer_pool_size = 2G
#当buffer_pool的值较大的时候为1,较小的设置为8
innodb_buffer_pool_instances = 8
#运行时load缓冲池,快速预热缓冲池,将buffer pool的内容(文件页的索引)dump到文件中,然后快速load到buffer pool中。避免了数据库的预热过程,提高了应用访问的性能
innodb_buffer_pool_load_at_startup = 1
#运行时dump缓冲池
innodb_buffer_pool_dump_at_shutdown = 1
#在innodb中处理用户查询后,其结果在内存空间的缓冲池已经发生变化,但是还未记录到磁盘。这种页面称为脏页,将脏页记录到磁盘的过程称为刷脏
innodb_lru_scan_depth = 2000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
#事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用失败,默认50s
innodb_lock_wait_timeout = 30
#日志组所在的路径,默认为data的home目录;
innodb_log_group_home_dir = /data/mysql/
#innodb_undo_directory = /data/mysql/undolog/
#这个参数控制着innodb数据文件及redo log的打开、刷写模式,http://blog.csdn.net/gua___gua/article/details/44916207
#innodb_flush_method = O_DIRECT-不经过系统缓存直接存入磁盘,
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_strict_mode = 1
#innodb独享表空间,有点很多,缺点会导致单个表文件过大
#innodb_file_per_table = 1
#undo日志回滚段 默认为128
innodb_undo_logs = 128
#传统机械硬盘建议使用,而对于固态硬盘可以关闭
#innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
#控制是否使用独立purge线程
innodb_purge_threads = 1
#改为ON时,允许单列索引最大达到3072。否则最大为767
innodb_large_prefix = 1
innodb_thread_concurrency = 8
#开启后会将所有的死锁记录到error_log中
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 16M
########semi sync replication settings########
#半同步复制
plugin_load = “rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
#表示转储每个bp instance LRU上最热的page的百分比。通过设置该参数可以减少转储的page数。
innodb_buffer_pool_dump_pct = 40
#刷脏的进程N-1
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
#控制回收(收缩)undo log的频率.undo log空间在它的回滚段没有得到释放之前不会收缩,
innodb_purge_rseg_truncate_frequency = 128
log_timestamps=system
#该参数基于MySQL5.7 Group Replication组复制的,没有使用不要设置
#transaction_write_set_extraction=MURMUR32
#http://www.cnblogs.com/hzhida/archive/2012/08/08/2628826.html
show_compatibility_56=on

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

 

mysqldump给运维的25个小技巧

1、mysqldump 是文本备份还是二进制备份

它是文本备份,如果你打开备份文件你将看到所有的语句,可以用于重新创建表和对象。它也有 insert 语句来使用数据构成表。

 

2、mysqldump 的语法是什么?

mysqldump -u [uname] -p[pass] –databases[dbname][dbname2] > [backupfile.sql]

 

3、使用 mysqldump 怎样备份所有数据库?

mysqldump -u root -p –all-databases >backupfile.sql

 

4、使用 mysqldump 怎样备份指定的数据库?

mysqldump -u root -p –databases schoolhospital > backupfile.sql

 

5、使用 mysqldump 怎样备份指定的表?

mysqldump –user=root –password=mypassword-h localhost databasename table_name_to_dump table_name_to_dump_2 >dump_only_two_tables_file.sql

 

6、我不想要数据,怎样仅获取 DDL?

mysqldump -u root -p –all-databases–no-data > backupfile.sql

 

7、一次 mysqldump 备份花费多长时间?

这依赖于数据库大小,100GB 大小的数据库可能花费两小时或更长时间

 

8、怎样备份位于其他服务器的远程数据库?

mysqldump -h 172.16.25.126 -u root -ppassdbname > dbname.sql

 

9、–routines 选项的含义是什么?

通过使用 -routines产生的输出包含 CREATEPROCEDURE 和 CREATEFUNCTION 语句用于重新创建routines。如果你有procedures 或 functions 你需要使用这个选项

 

10、怎样列出 mysqldump 中的所有选项?

mysqldump –help

 

11、mysqldump 中常用的选项是?

All-databases

Databases

Routines

Single-transaction (它不会锁住表) – 一直在 innodb databases 中使用

Master-data – 复制 (现在忽略了)

No-data – 它将 dump 一个没有数据的空白数据库

 

12、默认所有的 triggers 都会备份吗?

是的

 

13、single transaction 选项的含义是什么?

–singletransaction 选项避免了 innodb databases 备份期间的任何锁,如果你使用这个选项,在备份期间,没有锁

 

14、使用 mysqldump 备份的常用命令是什么?

nohup mysqldump –socket=mysql.sock–user=user1 –password=pass –single-transaction –flush-logs –master-data=2–all-databases –extended-insert –quick –routines > market_dump.sql 2>market_dump.err &

 

15、使用 mysqldump 怎样压缩一个备份?

注意: 压缩会降低备份的速度

Mysqldump [options] | gzip >backup.sql.gz

 

16、mysqldump 备份大数据库是否是理想的?

依赖于你的硬件,包括可用的内存和硬盘驱动器速度,一个在 5GB 和 20GB 之间适当的数据库大小。 虽然有可能使用  mysqldump 备份 200GB 的数据库,这种单一线程的方法需要时间来执行。

 

17、怎样通过使用 mysqldump 来恢复备份?

使用来源数据的方法

Mysql –u root –p < backup.sql

 

18、在恢复期间我想记录错误到日志中,我也想看看恢复的执行时间?

Time Mysql –u root –p < backup.sql >backup.out 2>&1

 

19、怎样知道恢复是否正在进行?

显示完整的进程列表

 

20、如果数据库是巨大的,你不得不做的事情是?

使用 nohup 在后台运行它

 

21、我是否可以在 windows 上使用 mysqldump 备份然后在 linux 服务器上恢复?

是的

 

22、我怎么传输文件到目标服务器上去?

使用 scp

使用 sftp

使用 winscp

 

23、如果我使用一个巨大的备份文件来源来恢复会发生什么?

如果你的一个数据库备份文件来源,它可能需要很长时间运行。处理这种情况更好的方式是使用 nohup 来在后台运行。也可使用在 unix 中的 screen 代替

 

24、默认情况下,mysqldump 包含 drop 数据库吗?

你需要添加–add-drop-database 选项

 

25、怎样从一个多数据库备份中提取一个数据库备份(假设数据库名字是 test)?

sed -n ‘/^– Current Database: `test`/,/^–Current Database: `/p’ fulldump.sql > test.sql

mysql-binlog日志恢复数据库

binlog日志用于记录所有更新了数据或者已经潜在更新了数据的所有语句。语句以“事件”的形式保存,它描述数据更改。当我们因为某种原因导致数据库出现故障时,就可以利用binlog日志来挽回(前提是已经配置好了binlog),接下来我们来配置

一、开启mysql-binlog日志

在mysql配置文件my.cnf加上如下配置

[mysqld]
log-bin=mysql-bin

重启mysql

service mysqld restart

二、备份数据库

1)先查看一下当前数据库情况

复制代码
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
复制代码

2)备份数据到/tmp/test.sql

[root@localhost ~]# whereis mysqldump
mysqldump: /usr/bin/mysqldump /usr/share/man/man1/mysqldump.1.gz
[root@localhost ~]# /usr/bin/mysqldump -uroot -p123456 test > /tmp/test.sql

三、这时模拟误操作(插入3条数据,删除数据库)

复制代码
mysql> insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(5);
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;#关闭当前的二进制日志文件并创建一个新文件,新的二进制日志文件的名字在当前的二进制文件的编号上加1。
Query OK, 0 rows affected (0.05 sec)
复制代码
复制代码
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
复制代码

删除数据

复制代码
mysql> truncate t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
Empty set (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)
复制代码

此时突然数据库损坏或者人为删除

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

四、此时数据库已经被完全破坏

1)用已经备份的/tmp/test.sql来恢复数据

[root@localhost]# mysql -uroot -p123456 test </tmp/test.sql
[root@localhost]# mysql -uroot -p123456 test
复制代码
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
复制代码

2)还有三条数据没有恢复,怎么办。只能用bin-log来恢复

[root@localhost]# /usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001 | more
[root@localhost]# /usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -uroot -p123456 test
复制代码
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)
复制代码

3)恢复成功

【总结】:mysql备份和bin-log日志

备份数据:

mysqldump -uroot -p123456 test -l -F '/tmp/test.sql'
-l:读锁(只能读取,不能更新)
-F:即flush logs,可以重新生成新的日志文件,当然包括log-bin日志

查看binlog日志:

mysql>show master status

导入之前备份数据:

mysql -uroot -p123456 test -v -f </tmp/test.sql
-v查看导入的详细信息
-f是当中间遇到错误时,可以skip过去,继续执行下面的语句

恢复binlog-file二进制日志文件:

mysqlbinlog --no-defaults binlog-file | mysql -uroot -p123456

从某一(367)点开始恢复:

mysqlbinlog --no-defaults --stop-position="367" mysql-bin.000001| mysql -uroot -p123456 test

先查好那一点(用more来查看)

[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | more

然后恢复:

[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | /usr/bin/mysql -uroot -p123456 test

重置binlog日志

复制代码
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+
复制代码

mysql> flush logs;#关闭当前的二进制日志文件并创建一个新文件,新的二进制日志文件的名字在当前的二进制文件的编号上加1。

如何在CentOS/Redhat/Fedora中安装MariaDB Galera Cluster 10.0(数据库集群)

MariaDB Galera Cluster 10.0.12稳定已被释放,可供生产使用。MariaDB的是一个关系型数据库管理系统(RDBMS)。一般我们使用的小型应用程序,使用数据库服务器的单个节点服务。但是当有成千上万的用户不断在网上请求访问应用程序时,在这种情况下,我们需要一个架构,这将能够处理这种负载,并提供高可用性。因此,我们需要增加彼此互连的多个数据库服务器,并保持同步,因此在任何情况下,某一服务器出现故障,其他服务器可以继续向用户提供服务。 MariaDB的横幅 本文将帮助你建立MariaDB Galera Cluster10.0.12用CentOS 6.5运行3个节点。集群服务器具体如下。

    • 集群DB1:192.168.1.10(主机名: db1.howtoing.com )
    • 集群DB2:192.168.1.20(主机名: db2.howtoing.com )
    • 集群DB3:192.168.1.30(主机名: db3.howtoing.com )

注意: 第1步/2/3 将完成所有群集节点,操作过程和节点具体配置。

第1步:添加MariaDB存储库

创建一个MariaDB存储库/etc/yum.repos.d/mariadb.repo在你的系统中使用下面的内容。 下面存储库将在CentOS 6.x的系统工作,对于其他系统中使用存储库生产工具,并添加到您的系统。 在CentOS 6 – 64位

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

在CentOS 6 – 32位

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

第2步:安装MariaDB和Galera

安装MariaDB Galera群集程序包之前,删除系统上安装任何现有的MySQL或MariaDB的包。并在所有节点上使用以下命令安装。

# yum install MariaDB-Galera-server MariaDB-client galera

第3步:初始MariaDB配置

在上述步骤成功安装包之后,做一些初步MariaDB的配置。使用下列命令并按照群集的所有节点上的说明。它将提示设置root帐户密码。

# service mysql start
# mysql_secure_installation

之后,创建所有的节点用户,它可以从您的网络集群中的访问数据库中MariaDB的用户。

# mysql -u root -p

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

启动群集配置之前停止MariaDB服务

# service mysql stop

第4步:DB1设置MariaDB Galera Cluster

让我们安装MariaDB Galera Cluster从DB1服务器开始。编辑MariaDB服务器配置文件,并在[MariaDB]部分添加下面的值。

[root@db1 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.10,192.168.1.20,192.168.1.30"
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.1.10'
wsrep_node_name='db1'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:password

使用以下命令启动集群。

[root@db1 ~]# /etc/init.d/mysql bootstrap
Bootstrapping the clusterStarting MySQL.... SUCCESS!

如果你在启动过程中有任何问题,请检查MariaDB的错误日志文件,在 /var/lib/mysql/<hostname>.err

第5步:添加DB2,在MariaDB的集群中

DB1成功启动集群后。在DB2上开始配置。编辑MariaDB的服务器配置文件,并在[MariaDB]部分添加下面的值。所有设置都差不多,除了wsrep_node_address,wsrep_cluster_address和wsrep_node_name DB1 。

[root@db2 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.10,192.168.1.20,192.168.1.30"
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.1.20'
wsrep_node_name='db2'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:password

使用以下命令启动集群。

[root@db2 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

第6步:添加DB3,在MariaDB的集群中

此服务器是可选的,如果你想只有两个集群服务器,则可以忽略这一步,如果不配置DB3 你需要从DB1/DB2配置文件中删除第三个服务器IP。要添加此服务器进行修改和DB2一样。

[root@db3 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.10,192.168.1.20,192.168.1.30"
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.1.30'
wsrep_node_name='db2'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:password

使用以下命令启动集群。

[root@db3 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

第7步:测试MariaDB的Galera群集设置

在这个阶段,您的群集安装已经完成,并正常运行。现在,您可以测试通过在集群的任何服务器创建数据库和表群集安装,它会立即复制到群集中的所有服务器。

MySQ binlog三种模式及设置方法

MySQ binlog三种模式及设置方法

1.1 Row Level 行模式

  日志中会记录每一行数 据被修改的形式,然后在slave端再对相同的数据进行修改
优点:row level模式下,bin-log中可以不记录执行的sql语句的上下文相关的信息,仅仅只需要记录那一条被修改。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function,以及trigger的调用和触发无法被正确复制的问题

缺点:row level,所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,会产生大量的日志内容。

1.2 Statement Level(默认)

  每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行
**优点:**statement level下的优点首先就是解决了row level下的缺点,不需要记录每一行数据的变化,减少bin-log日志量,节约IO,提高性能,因为它只需要在Master上锁执行的语句的细节,以及执行语句的上下文的信息。

缺点:由于只记录语句,所以,在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题,主要是修改数据的时候使用了某些定的函数或者功能的时候会出现。

1.3 Mixed 自动模式

  在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式,也就是在Statement和Row之间选择一种。如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。

1.4 企业场景如何选择binlog模式

1、互联网公司,使用MySQL的功能相对少(存储过程、触发器、函数)
选择默认的语句模式,Statement Level(默认)
2、公司如果用到使用MySQL的特殊功能(存储过程、触发器、函数)
则选择Mixed模式
3、公司如果用到使用MySQL的特殊功能(存储过程、触发器、函数)又希望数据最大化一直,此时最好选择Row level模式

行模式和语句模式的区别

1.语句模式:
100万条记录
只需1条delete * from test;就可以删除100万条记录

2.row模式
100万条记录
记录100万条删除命令

1.5 如何配置binlog

在数据库中查看binlog模式

  1. root@oldboy 11:28:05->show global variables like '%binlog_format%';
  2. +---------------+-----------+
  3. | Variable_name | Value |
  4. +---------------+-----------+
  5. | binlog_format | STATEMENT |
  6. +---------------+-----------+
  7. 1 row in set (0.00 sec)

配置文件参数如下:

  1. [myslqd]
  2. log-bin=mysql-bin
  3. #binlog_format="STATEMENT"
  4. #binlog_format="ROW"
  5. #binlog_format="MIXED"

运行时在线修改临时

  1. mysql>SET GLOBAL binlog_format = 'ROW';
  2. mysql>show global variables like '%binlog_format%';
  3. 其他模式命令为:
  4. SET GLOBAL binlog_format = 'MIXED';
  5. SET GLOBAL binlog_format = 'STATEMENT';

解释:
GLOBAL=全局会话
SESSION=当前会话
检验ROW模式下binlog日志记录效果

  1. [root@db01 3306]# mysqlbinlog --base64-output="decode-rows" --verbose mysql-bin.000248
  2. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  3. /*!40019 SET @@session.max_insert_delayed_threads=0*/;
  4. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  5. DELIMITER /*!*/;
  6. # at 4
  7. #160628 11:06:52 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.49-log created 160628 11:06:52 at startup
  8. # Warning: this binlog is either in use or was not closed properly.
  9. ROLLBACK/*!*/;
  10. # at 107
  11. #160628 11:07:09 server id 1 end_log_pos 177 Query thread_id=1 exec_time=0 error_code=0
  12. SET TIMESTAMP=1467083229/*!*/;
  13. SET @@session.pseudo_thread_id=1/*!*/;
  14. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  15. SET @@session.sql_mode=0/*!*/;
  16. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;
  17. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
  18. SET @@session.lc_time_names=0/*!*/;
  19. SET @@session.collation_database=DEFAULT/*!*/;
  20. BEGIN
  21. /*!*/;
  22. # at 177
  23. # at 223
  24. #160628 11:07:09 server id 1 end_log_pos 223 Table_map: `oldboy`.`sc` mapped to number 33
  25. #160628 11:07:09 server id 1 end_log_pos 785 Update_rows: table id 33 flags: STMT_END_F
  26. ### UPDATE `oldboy`.`sc`
  27. ### WHERE
  28. ### @1=1
  29. ### @2=1001