MySQL之快照读

快照读 即： snapshot read ，官方叫法是： Consistent Nonlocking Reads ，即： 一致性非锁定读 ，官方的解释是：

即：

即 快照读 的问题在于：在同一个事务中，能够读取到之前提交的数据。表现为：

字面意思：在事务中，为查询创建的快照，并不适用与 DML 语句。

也就是说：如果事务 A 开始时创建的快照，查询不到数据 col1=1 ，但此时事务 B 刚刚提交 insert col1=1 和 insert col1=1 ，此时如果事务 A 执行， delete col1=1 ，是能将事务 B 生成的数据删除的。

字面意思：即使事务 A 的快照是在事务 B 提交之前创建的，但事务 A 也只有在事务 A 和事务 B 都提交后，才能看到事务 B 新增的数据。

新建一个lvm磁盘，这里我建的lv为mydatalv，挂载到了/data下

[root@localhost

~]#

lvs

LV

VG

Attr

LSize

Pool

Origin

Data%

Meta%

Move

Log

Cpy%Sync

Convert

mydatalv

mydata

-wi-ao----

1.00g

[root@localhost

~]#

df

-h

文件系统

容量

已用

可用

已用%

挂载点

/dev/mapper/mydata-mydatalv

976M

2.6M

907M

1%

/data

将原数据库文件复制到/data目录下

[root@localhost

~]#

cp

-a

/var/lib/mysql

/data/

修改配置文件，将mysql数据库文件放在lvm盘中，二进制文件放在非lvm盘的/var/lib/mysql/目录下

[root@ns1

~]#

vim

/etc/my.cnf

[mysqld]

log_bin=/var/lib/mysql/mysql-bin

datadir=/data/mysql

[root@localhost

~]#

service

mariadb

restart

[root@localhost

~]#

ls

/data/mysql

aria_log.00000001

ibdata1

ib_logfile1

mysql-bin.000001

mysql-bin.000003

performance_schema

aria_log_control

ib_logfile0

mysql

mysql-bin.000002

mysql-bin.index

test

可以看到重启后数据库文件已存放在了/data/mysql目录中了

对mysql进行锁表备份

[root@localhost

~]#

mysql

-e

'flush

tables

with

read

lock'

锁表

[root@localhost

~]#

mysql

-e

'flush

logs'

对日志进行滚动，

[root@localhost

~]#

mysql

-e

'show

master

status'

>

/root/back.$(date

+%F+%T)

[root@localhost

~]#

ls

back.2016-07-13+10:14:29

对lv创建快照

[root@localhost

~]#

lvcreate

-L

1G

-n

mysqlback

-p

r

-s

/dev/mydata/mydatalv

释放锁

[root@localhost

~]#

mysql

-e

'unlock

tables'

在别的磁盘上创建备份目录，只读挂载快照后备份至备份目录

[root@localhost

~]#

mkdir

/myback

[root@localhost

~]#

mount

-r

/dev/mydata/mysqlback

/mnt

[root@localhost

~]#

cp

-a

/mnt/mysql

/myback

修改表内容，然后删除掉数据库文件内容即/data/mysql中的内容

[root@localhost

~]#

mysql

MariaDB

[hellodb]>

use

hellodb

MariaDB

[hellodb]>

insert

into

classes

(class,numofstu)

values

('xxoo',39)

[root@localhost

~]#

rm

-rf

/data/*

修改配置文件中二进制日志和数据库文件的位置

[root@localhost

~]#

vim

/etc/my.cnf

[mysqld]

log_bin=/data/mysql/mysql-bin

datadir=/data/mysql

利用/myback/中的内容还原

[root@localhost

~]#

cp

-a

/myback/*

/data/

[root@localhost

~]#

service

mariadb

restart

利用二进制日志还原快照后的 *** 作，由下面这个文件来查看快照执行时二进制日志的位置

[root@localhost

~]#

cat

back.2016-07-13+10\:14\:29

File

Position

Binlog_Do_DB

Binlog_Ignore_DB

mysql-bin.000014

245

将000014中245之后的 *** 作做成sql文件，进行还原

[root@localhost

~]#

mysqlbinlog

--start-position=245

/var/lib/mysql/mysql-bin.000014

>

binlog.sql

[root@localhost

~]#

mysql

<

/root/binlog.sql

查看恢复情况

[root@localhost

~]#

mysql

MariaDB

[(none)]>

use

hellodb

MariaDB

[hellodb]>

select

*

from

classes

+---------+----------------+----------+

|

ClassID

|

Class

|

NumOfStu

|

+---------+----------------+----------+

|

1

|

Shaolin

Pai

|

10

|

|

2

|

Emei

Pai

|

7

|

|

3

|

QingCheng

Pai

|

11

|

|

4

|

Wudang

Pai

|

12

|

|

5

|

Riyue

Shenjiao

|

31

|

|

6

|

Lianshan

Pai

|

27

|

|

7

|

Ming

Jiao

|

27

|

|

8

|

Xiaoyao

Pai

|

15

|

|

9

|

xxoo

|

39

|

+---------+----------------+----------+

9

rows

in

set

(0.00

sec)

用数据库快照就能实现啊.

Oracle快照原理及实现总结

Oracle数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。对于中大型数据库，业务数据库里所有的数据同步到另外一个处理服务器上最佳的选择还是使用SnapShot方式，即快照的方式。

由于工作需要，今天需要将业务数据库里所有的数据同步到另外一个处理服务器上。在做方案的时候，想了很多方法，当然最快的办法还是使用物理热备的方式。

但是我个人认为如果对于中大型数据库(我们的数据库有300G左右)最佳的选择还是使用SnapShot方式，即快照的方式。

Oracle数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。也就是说快照根本的原理就是将本地或远程数据库上的一个查询结果保存在一个表中。

以下是我建立的Snapshot，目的是从业务数据库上将数据Copy到处理数据库上，是不同的两个服务器之间对数据copy。

第一步：在处理服务器上的Oracle终端，建立database link，业务数据库服务器SID为TEST

create database link TEST_DBLINK.US.ORACLE.COM

connect to AMICOS identified by AMICOS

using 'test'

第二步：在业务数据库上对应的表建立快照日志

Create snapshot log on A_Table

第三步：建立Snapshot 快照名称为：Test_SnapShot

Create snapshot Test_SnapShot

REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/24

as select * from A_Table@TEST_DBLINK

说明：REFRESH是刷新方法

刷新方式有：COMPLETE和FAST两种，而START WITH是说明开始执行的时间。

Next是下次执行的时间

而AS以后是构成快照的查询方法。

相关的方法：

更改快照

ALTER SNAPSHOT Test_SnapShot

REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/2

手动刷新快照 在命令界面执行：

EXEC DBMS_SNAPSHOT.REFRESH('Test_SnapShot ','C')

第一个参数是要刷新的快照名

第二个参数是刷新的方式，F----FAST, C---COMPLETE

查看快照最后刷新的日期

SELECT NAME,LAST_REFRESH

FROM ALL_SNAPSHOT_REFRESH_TIMES

最后非常的方案：

1：为需要做Snapshot的表建立Snapshot日志

create snapshot log on t1 with rowid这里使用ROWID建立日记的参数

2：采用Fast的方式建立快照，使用rowid做为参考参数

create snapshot fb_test_b refresh fast with rowid start with sysdate next sysdate+1/1440 as select * from fb_test_b@my_dblink

最好能按照rowid来建立快照。要不然就必须要为表建立Primary Key。

---