一、备份的目的

复制代码 代码如下: $sql=”SELECT SUM sum
from buylist where mid = ‘1’”; $ress=mysql_query; echo
$rowxiaofei=mysql_fetch_assoc;

复制代码 代码如下:mysql> select binary
‘ABCD’=’abcd’ COM1, ‘ABCD’=’abcd’ COM2;+——–+———–+| COM1 |
COM2 |+——–+———–+| 0 | 1 |+———+———–+1 row in set

做灾难恢复:对损坏的数据进行恢复和还原需求改变:因需求改变而需要把数据还原到改变以前测试:测试新功能是否可用

因为有的MySQL特别是4.*以前的对于中文检索会有不准确的问题,可以在检索的时候加上binary。建表:复制代码 代码如下:create TABLE usertest
unsigned NOT NULL auto_increment,username varchar NOT NULL default
”,primary key 插入数据:复制代码
代码如下:insert into usertest VALUES;insert into usertest VALUES;insert
into usertest VALUES;insert into usertest VALUES;insert into usertest
VALUES;insert into usertest VALUES;insert into usertest ;例如:select *
from usertest where username like ‘%夏%’
,结果七条记录都出来了,比较郁闷。如果使用=而不是like的时候,select *
from usertest where username = ‘夏’ ,只出现一个结果。因为mysql
的LIKE操作是按照ASCII
操作的,所以LIKE的时候是可能有问题的。问题继续:如果再加上:复制代码 代码如下:insert into usertest ;insert
into usertest ;还是使用select * from usertest where username = ‘夏’
,结果还是出现3条记录,又郁闷了。解决办法如下:1.在create的时候就使用binary,而不是在query的时候加。复制代码 代码如下:username varchar BINARY NOT
NULL default ”, 如果表已经建好了,使用:alter table usertest modify
username varchar binary;
来就该表的属性。2.在query的时候加上binary,select * from usertest where
username like binary ‘%夏%’
,就可以准确的查询出一条记录来。char使用固定长度的空间进行存储,char存储4个字符,根据编码方式的不同占用不同的字节,gbk编码方式,不论是中文还是英文,每个字符占用2个字节的空间,utf8编码方式,每个字符占用3个字节的空间。如果需要存储的字符串的长度跟所有值的平均长度相差不大,适合用char,如MD5。对于经常改变的值,char优于varchar,原因是固定长度的行不容易产生碎片。对于很短的列,char优于varchar,原因是varchar需要额外一个或两个字节存储字符串的长度。varchar保存可变长度的字符串,使用额外的一个或两个字节存储字符串长度,varchar,除了需要存储10个字符,还需要1个字节存储长度信息,超过255的长度需要2个字节来存储例外:Myisam引擎中使用ROW_FORMAT=FIXED时,每行使用相同的空间,造成浪费char和varchar后面如果有空格,char会自动去掉空格后存储,varchar虽然不会去掉空格,但在进行字符串比较时,会去掉空格进行比较复制代码
代码如下:+——-+————–+——+—–+———+—————-+|
Field | Type | Null | Key | Default | Extra
|+——-+————–+——+—–+———+—————-+| id |
int | NO | PRI | NULL | auto_increment || name | varchar | YES | | NULL
| || addr | char | YES | | NULL | || bn | varbinary | YES | | NULL | ||
b | binary | YES | | NULL |
|+——-+————–+——+—–+———+—————-++———————-+———————-+|
concat | concat |+———————-+———————-+| $asdf$
| $a$ || $asdf$ | $a$ || $a $ | $a$ || $a$ | $a$ || $t a$ | $a$
|+———————-+———————-+mysql> select * from
zcy where name=’a ‘; //由于name是varchar,比较时,’a
‘自动转换为’a’+—-+——+——+——+———-+| id | name | addr |
bn | b |+—-+——+——+——+———-+| 3 | a | a | ab | ab || 4
| a | a | ab | a |+—-+——+——+——+———-+2 rows in set
mysql> select * from zcy where
name=’a’;+—-+——+——+——+———-+| id | name | addr | bn |
b |+—-+——+——+——+———-+| 3 | a | a | ab | ab || 4 | a
| a | ab | a |+—-+——+——+——+———-+2 rows in set
+——-+————–+——+—–+———+—————-+| Field
| Type | Null | Key | Default | Extra
|+——-+————–+——+—–+———+—————-+| id |
int | NO | PRI | NULL | auto_increment || name | varchar | YES | | NULL
| || addr | char | YES | | NULL | || bn | varbinary | YES | | NULL | ||
b | binary | YES | | NULL |
|+——-+————–+——+—–+———+—————-++——————–+——————-+|
concat | concat |+——————–+——————-+| $ab a$ |
NULL || $ab $ | $ab $ || $ab$ | $ab $ || $ab $ | $a $ || NULL | $a $ ||
NULL | $abcde $ || NULL | $abcd1234$
|+——————–+——————-+binary保存二进制字符串,它保存的是字节而不是字符,没有字符集限制binary可以保存8个字符,每个字符占1个字节,共占8个字节进行比较时是按字节进行比较,而不是按字符,按字节比较比字符简单快速按字符比较不区分大小写,而binary区分大小写,结尾使用\0填充,而不是空格复制代码 代码如下:mysql> select * from zcy
where b=’a\0\0\0\0\0\0\0′;+—-+——+——+——+———-+|
id | name | addr | bn | b |+—-+——+——+——+———-+| 5 | t
a | a | NULL | a |+—-+——+——+——+———-+mysql> select
* from zcy where b=’a \0\0\0\0\0\0′;
+—-+——+——+——+———-+| id | name | addr | bn | b
|+—-+——+——+——+———-+| 4 | a | a | ab | a
|+—-+——+——+——+———-+varbinary保存变长的字符串,后面不会补\0mysql>
select * from zcy where bn=’ab’;+—-+——+——+——+———-+|
id | name | addr | bn | b |+—-+——+——+——+———-+| 3 | a
| a | ab | ab |+—-+——+——+——+———-+1 row in set
mysql> select * from zcy where bn=’ab
‘;+—-+——+——+——+———-+| id | name | addr | bn | b
|+—-+——+——+——+———-+| 2 | asdf | a | ab | ab
|+—-+——+——+——+———-+1 row in set mysql> select *
from zcy where bn=’ab ‘;+—-+——+——+——+———-+| id |
name | addr | bn | b |+—-+——+——+——+———-+| 4 | a | a
| ab | a |+—-+——+——+——+———-+1 row in set

二、备份需要考虑的问题

MySql中Blob与Text的区别BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求。BLOB
列被视为二进制字符串。TEXT列被视为非二进制字符串。BLOB列没有字符集,并且排序和比较基于列值字节的数值值。TEXT列有一个字符集,并且根据字符集的
校对规则对值进行排序和比较。在TEXT或BLOB列的存储或检索过程中,不存在大小写转换。当未运行在严格模式时,如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值,值被截取以保证适合。如果截掉的字符不是空格,将会产生一条警告。使用严格SQL模式,会产生错误,并且值将被拒绝而不是截取并给出警告。在大多数方面,可以将BLOB列视为能够足够大的VARBINARY列。同样,可以将TEXT列视为VARCHAR列。BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR:·当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同)。请注意比较时将用空格对TEXT进行扩充以适合比较的对象,正如CHAR和VARCHAR。·对于BLOB和TEXT列的索引,必须指定索引前缀的长度。对于CHAR和VARCHAR,前缀长度是可选的。·BLOB和TEXT列不能有
默认值。LONG和LONG
VARCHAR对应MEDIUMTEXT数据类型。这是为了保证兼容性。如果TEXT列类型使用BINARY属性,将为列分配列字符集的二元
校对规则。MySQL连接程序/ODBC将BLOB值定义为LONGVARBINARY,将TEXT值定义为LONGVARCHAR。由于BLOB和TEXT值可能会非常长,使用它们时可能遇到一些约束:·当排序时只使用该列的前max_sort_length个字节。max_sort_length的
默认值是1024;该值可以在启动mysqld服务器时使用–max_sort_length选项进行更改。运行时增加max_sort_length的值可以在排序或组合时使更多的字节有意义。任何客户端可以更改其会话max_sort_length变量的值:复制代码 代码如下:mysql> SET
max_sort_length = 2000;mysql> SELECT id, comment FROM tbl_name
-> ORDER BY
comment;当你想要使超过max_sort_length的字节有意义,对含长值的BLOB或TEXT列使用GROUP
BY或ORDER
BY的另一种方式是将列值转换为固定长度的对象。标准方法是使用SUBSTRING函数。例如,下面的语句对comment列的2000个字节进行排序:复制代码 代码如下:mysql> SELECT id,
SUBSTRING FROM tbl_name -> ORDER BY
SUBSTRING;·BLOB或TEXT对象的最大大小由其类型确定,但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小,但必须同时修改服务器和客户端程序。例如,可以使用mysql和mysqldump来更改客户端的max_allowed_packet值。每个BLOB或TEXT值分别由内部分配的对象表示。这与其它列类型形成对比,后者是当打开表时为每1列分配存储引擎。

可以容忍丢失多长时间的数据;恢复数据要在多长时间内完;
恢复的时候是否需要持续提供服务;恢复的对象,是整个库,多个表,还是单个库,单个表。

三、备份的类型

1、根据是否需要数据库离线

冷备:需要关mysql服务,读写请求均不允许状态下进行;温备:
服务在线,但仅支持读请求,不允许写请求;热备:备份的同时,业务不受影响。

注:

1、这种类型的备份,取决于业务的需求,而不是备份工具2、MyISAM不支持热备,InnoDB支持热备,但是需要专门的工具

2、根据要备份的数据集合的范围完全备份:full
backup,备份全部字符集。增量备份: incremental backup
上次完全备份或增量备份以来改变了的数据,不能单独使用,要借助完全备份,备份的频率取决于数据的更新频率。差异备份:differential
backup
上次完全备份以来改变了的数据。建议的恢复策略:完全+增量+二进制日志完全+差异+二进制日志

3、根据备份数据或文件

物理备份:直接备份数据文件

优点:

备份和恢复操作都比较简单,能够跨mysql的版本,恢复速度快,属于文件系统级别的

建议:

不要假设备份一定可用,要测试mysql>check
tables;检测表是否可用逻辑备份: 备份表中的数据和代码

优点:

恢复简单、备份的结果为ASCII文件,可以编辑与存储引擎无关可以通过网络备份和恢复

缺点:

备份或恢复都需要mysql服务器进程参与备份结果占据更多的空间,浮点数可能会丢失精度还原之后,缩影需要重建

四:备份的对象

1、
数据;2、配置文件;3、代码:存储过程、存储函数、触发器4、os相关的配置文件5、复制相关的配置6、二进制日志

五、备份和恢复的实现

1、利用select into
outfile实现数据的备份与还原1.1把需要备份的数据备份出来

复制代码 代码如下:mysql> use hellodb;
//打开hellodb库 mysql> select * from students; 查看students的属性
mysql> select * from students where Age > 30 into outfile
‘/tmp/stud.txt’ ; //将年龄大于三十的同学的信息备份出来

注意:

备份的目录路径必须让当前运行mysql服务器的用户mysql具有访问权限

备份完成之后需要把备份的文件从tmp目录复制走,要不就失去备份的目的了

回到tmp目录下查看刚才备份的文件

[root@www ~]# cd /tmp

[root@www tmp]# cat stud.txt

3Xie Yanke53M216

4Ding Dian32M44

6Shi Qing46M5\N

13Tian Boguang33M2\N

25Sun Dasheng100M\N\N

[root@www tmp]#

你会发现是个文本文件。所以不能直接导入数据库了。需要使用load data infile
恢复

回到mysql服务器端,删除年龄大于30的用户,模拟数据被破坏

mysql> delete from students where Age > 30;

mysql> load data infile ‘/tmp/stud.txt’ into table students;

2、利用mysqldump工具对数据进行备份和还原

mysqldump 常用来做温备,所以我们首先需要对想备份的数据施加读锁,

2.1 施加读锁的方式:

1.直接在备份的时候添加选项

–lock-all-tables 是对要备份的数据库的所有表施加读锁

–lock-table
仅对单张表施加读锁,即使是备份整个数据库,它也是在我们备份某张表的时候才对该表施加读锁,因此适用于备份单张表

2、在服务器端书写命令,

mysql> flush tables with read lock;
施加锁,表示把位于内存上的表统统都同步到磁盘上去,然后施加读锁

mysql> flush tables with read lock;释放读锁

但这对于InnoDB存储引擎来讲,虽然你也能够请求道读锁,但是不代表它的所有数据都已经同步到磁盘上,因此当面对InnoDB的时候,我们要使用mysql>
show engine innodb status;
看看InnoDB所有的数据都已经同步到磁盘上去了,才进行备份操作。

2.2备份的策略:

发表评论

电子邮件地址不会被公开。 必填项已用*标注