0.目录

图片 1

    MyFlash is an
open source tool released by Meituan-Dianping which can be used to
flashback MySQL DML data.here’s the github link: 

1.定义

  • 1.1
    什么是主键和外键
  • 1.2
    主键和外键的作用
  • 1.3
    主键、外键和索引的区别

 

    after downloaded the tool and
extracted the zip package,i got this structure:

2.主键(primary key)

  • 2.1
    通过SSMS设置主键
  • 2.2
    通过SQL语句设置主键

图片 2

 

3.外键(foreign key)

  • 3.1
    通过SSMS设置外键
  • 3.2
    通过SQL语句设置外键
 1 [root@zlm1 16:25:26 /vagrant/MyFlash-master]
 2 #ls -l
 3 total 8
 4 drwxrwxrwx 1 vagrant vagrant 0 Jun 1 16:17 binary
 5 -rwxrwxrwx 1 vagrant vagrant 95 Oct 25 2017 build.sh
 6 drwxrwxrwx 1 vagrant vagrant 0 Jun 1 16:17 doc
 7 -rwxrwxrwx 1 vagrant vagrant 1103 Oct 25 2017 License.md
 8 -rwxrwxrwx 1 vagrant vagrant 1273 Oct 25 2017 README.md
 9 drwxrwxrwx 1 vagrant vagrant 0 Jun 1 16:17 source
10 drwxrwxrwx 1 vagrant vagrant 4096 Jun 1 16:17 testbinlog

4.SQL语句向已存在表设置主键和外键

  • 4.1
    已存在表设置主键
  • 4.2
    已存在表设置外键

 

1.定义

    the
official document recommend to install the tool by dynamic compliling
link way to install,but i prefer to use the binary way instead.let’s see
the parameter and usage of the command:

1.1 什么是主键和外键

关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键。
比如:
学生表(学号,姓名,性别,班级)
其中每个学生的学号是唯一的,学号就是一个主键

课程表(课程编号,课程名,学分)
其中课程编号是唯一的,课程编号就是一个主键

成绩表(学号,课程号,成绩)
成绩表中单一一个属性无法唯一标识一条记录,学号和课程号的组合才可以唯一标识一条记录,所以
学号和课程号的属性组是一个主键

成绩表中的学号不是成绩表的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则称成绩表中的学号是学生表的外键

 

1.2 主键和外键的作用

1.2.1为了维护关系数据库的完整性:
主键是能确定一条记录的唯一标识,比如,一条记录包括身份正号,姓名,年龄。身份证号是唯一能确定你这个人的,其他都可能有重复,所以,身份证号是主键。

外键用于与另一张表的关联。是能确定另一张表记录的字段,用于保持数据的一致性。比如,A表中的一个字段,是B表的主键,那他就可以是A表的外键。

1.2.2起约束作用:
外键取值规则:空值或参照的主键值。
(1)插入非空值时,如果主键表中没有这个值,则不能插入。
(2)更新时,不能改为主键表中没有的值。
(3)删除主键表记录时,你可以在建外键时选定外键记录一起级联删除还是拒绝删除。
(4)更新主键记录时,同样有级联更新和拒绝执行的选择。

图片 3图片 4

1.3 主键、外键和索引的区别

区别 主键 外键 索引
定义 唯一标识一条记录,不能有重复的,不允许为空 表的外键是另一表的主键, 外键可以有重复的, 可以是空值 该字段没有重复值,但可以有一个空值
作用 用来保证数据完整性 用来和其他表建立联系用的 是提高查询排序的速度
个数 主键只能有一个 一个表可以有多个外键 一个表可以有多个唯一索引

参考网站:SQL的主键和外键的作用

 1 [root@zlm1 16:27:20 /vagrant/MyFlash-master/binary]
 2 #./flashback --help
 3 Usage:
 4   flashback [OPTION...]
 5 Help Options:
 6   -h, --help Show help options
 7 Application Options:
 8   --databaseNames databaseName to apply. if multiple, seperate by comma(,)
 9   --tableNames tableName to apply. if multiple, seperate by comma(,)
10   --start-position start position
11   --stop-position stop position
12   --start-datetime start time (format %Y-%m-%d %H:%M:%S)
13   --stop-datetime stop time (format %Y-%m-%d %H:%M:%S)
14   --sqlTypes sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
15   --maxSplitSize max file size after split, the uint is M
16   --binlogFileNames binlog files to process. if multiple, seperate by comma(,)
17   --outBinlogFileNameBase output binlog file name base
18   --logLevel log level, available option is debug,warning,error
19   --include-gtids gtids to process
20   --exclude-gtids gtids to skip

2.主键(primary key)

View Code

2.1 通过SSMS设置主键

2.1.1打开之前建立的表,即选择 数据库->SSMSTest->表->dbo.student
右键单击dbo.student选择设计。
图片 5
2.1.2在sno处右键单击选择设置主键。
图片 6
2.1.3这时可以看到sno前面出现了一个钥匙一样的图标,这就代表着sno已经被设置为主键了。点击一下保存或者按Ctrl+S就可以保存了。
2.1.4按照以上步骤打开dbo.course,右键单击cno选择设置主键。保存。
图片 7
2.1.5按照以上步骤打开dbo.sc,按住shift依次点击sno和cno前面的小框按钮,就可以同时选中这两行。然后再右键单击选择设置主键。保存。
图片 8
2.1.6至此,已经完成了三张表的主键设置。

 

2.2 通过SQL语句设置主键

2.2.1选择
数据库->SQLTest,点击新建查询,注意左上角的框里是SQLTest,不是的话手动选择一下。
图片 9
2.2.2在点击新建查询出来的界面中输入以下代码:

--这是SQL中的注释信息,使用两个减号来注释。
drop table student    --删除表student
create table student  --创建表student
(sno char(4) primary key,  --设置sno为主键
sname char(8),
sage int,
ssex char(2),
sdept char(20)
)

drop table course    --删除表course
create table course  --创建表course
(cno char(4) primary key,  --设置cno为主键
cname char(8),
cpno char(4),
ccredit int
)

drop table sc    --删除表sc
create table sc  --创建表sc
(sno char(4),
cno char(4),
grade int,
primary key(sno, cno)  --设置sno和cno的属性组为主键
)

2.2.3点击上面的执行,或者按F5之后出来了如下画面。
图片 10
2.2.4右键SQLTest点击刷新或者按F5,然后选择表,右键单击选择设计,能看到三张表都已经设置主键了。
图片 11
图片 12
图片 13
2.2.5至此,已经完成了三张表的主键设置。

    first of all,create the test
environment as below:

3.外键(foreign key)

 

3.1 通过SSMS设置外键

3.1.1选择 数据库->SSMSTest->表->dbo.sc
右键单击dbo.sc选择设计。
图片 14
3.1.2右键单击sno或者cno,选择关系。
图片 15
3.1.3点击左下角的添加。
图片 16
3.1.4在 表和列规范
后面的框中点一下,就会出现后面的三个点的按钮,点击三个点图标的那个按钮。
图片 17
3.1.5因为sc中的sno引用了student中的sno,所以主键表那里选择student,然后选择对应的sno。而sc中的cno因为引用的是course中的sc,所以在这里没法设置外键,需要等下再设置,在这里把它选择为无。点击确定。
图片 18
图片 19
3.1.6再次点击左下角的添加。按照上述步骤将主键表选择为course,选择对应sc中的cno的course中的cno,并点击确定。
图片 20
图片 21
图片 22
3.1.7点击关闭,然后保存,在出现的警告中选择是。
图片 23
图片 24
3.1.8选择 数据库->SSMSTest->数据库关系图
右键单击选择新建数据库关系图,在弹窗中选择是,然后将三张表都添加,然后就可以看到三张表之间的关系了。
图片 25
图片 26
图片 27
图片 28

 1 root@localhost:mysql3306.sock [zlm]05:35:42>create table test_flashbk(
 2     -> id bigint not null auto_increment,
 3     -> name varchar(20) not null default '',
 4     -> primary key(id)
 5     -> ) engine=innodb default charset=utf8mb4;
 6 Query OK, 0 rows affected (0.04 sec)
 7  
 8 root@localhost:mysql3306.sock [zlm]05:35:53>delimiter //
 9 root@localhost:mysql3306.sock [zlm]05:36:10>create procedure pro_flashbk()
10     -> begin
11     -> declare id int;
12     -> set id = 100000;
13     -> while id>0 do
14     -> insert into test_flashbk(name) values ('aaron8219');  
15     -> set id=id-1;
16     -> end while;
17     -> end //
18 Query OK, 0 rows affected (0.04 sec)
19  
20 root@localhost:mysql3306.sock [zlm]05:36:11>delimiter ;
21 root@localhost:mysql3306.sock [zlm]05:36:23>call pro_flashbk();
22 Query OK, 1 row affected (11.06 sec)
23  
24 root@localhost:mysql3306.sock [zlm]05:36:41>select count(*) from test_flashbk;
25 +----------+
26 | count(*) |
27 +----------+
28 |   100000 |
29 +----------+
30 1 row in set (0.02 sec)
31  
32 root@localhost:mysql3306.sock [zlm]05:37:17>select id,name from test_flashbk limit 0,5;
33 +----+-----------+
34 | id | name      |
35 +----+-----------+
36 |  1 | aaron8219 |
37 |  2 | aaron8219 |
38 |  3 | aaron8219 |
39 |  4 | aaron8219 |
40 |  5 | aaron8219 |
41 +----+-----------+
42 5 rows in set (0.00 sec)
43  
44 root@localhost:mysql3306.sock [zlm]05:38:04>select @@autocommit;
45 +--------------+
46 | @@autocommit |
47 +--------------+
48 |            1 |
49 +--------------+
50 1 row in set (0.03 sec)
51  
52 root@localhost:mysql3306.sock [zlm]05:38:12>

3.2 通过SQL语句设置外键

3.2.1选择
数据库->SQLTest,然后选择新建查询。注意左上角的那个框中要选中SQLTest,如果不是的话就手动选择一下。
3.2.2在新建查询出来的界面中输入以下代码:

--这是SQL中的注释信息,使用两个减号来注释。
drop table sc    --删除表sc
create table sc  --创建表sc
(sno char(4) foreign key references student(sno),  --加外键约束
cno char(4) foreign key references course(cno),  --加外键约束
grade int,
primary key(sno, cno)  --设置sno和cno的属性组为主键
)

3.2.3点击执行。
图片 29
3.2.4选择 数据库->SSMSTest->数据库关系图
右键单击选择新建数据库关系图,在弹窗中选择是,然后将三张表都添加,然后就可以看到三张表之间的关系了。
图片 30

 

4.SQL语句向已存在表设置主键和外键

    secondly,let’s mimic the situation
about updating records without using “where clause”,after
that

4.1 已存在表设置主键

格式为:

alter table 表名
add constraint
PK_字段名–“PK”为主键的缩写,字段名为要在其上创建主键的字段名,’PK__字段名’就为约束名
primary key (字段名) –字段名同上

如:

alter table student
add constraint PK_sno
primary key (sno)

PS:如果执行以上代码有以下警告,说明在建表的时候没有给sno添加非空约束,即需要在建表的时候使用以下代码。
图片 31

drop table student    --删除表student
create table student  --创建表student
(sno char(4) not null,  --加非空约束,不加"not null" 默认为:可以为空
sname char(8),
sage int,
ssex char(2),
sdept char(20)
)

the operation will update all the records
in the test table “test_flashbk”:

4.2 已存在表设置外键

格式为:

alter table 表名
add constraint FK_字段名–“FK”为外键的缩写
foreign key (字段名) references 关联的表名(关联的字段名)
–注意’关联的表名’和’关联的字段名’

如:

alter table sc add constraint FK_sno foreign key (sno) references student(sno)

 

 1 root@localhost:mysql3306.sock [zlm]05:38:12>update test_flashbk set name='zlm';
 2 Query OK, 100000 rows affected (2.29 sec)
 3 Rows matched: 100000  Changed: 100000  Warnings: 0
 4  
 5 root@localhost:mysql3306.sock [zlm]05:39:00>select id,name from test_flashbk limit 0,5;
 6 +----+------+
 7 | id | name |
 8 +----+------+
 9 |  1 | zlm  |
10 |  2 | zlm  |
11 |  3 | zlm  |
12 |  4 | zlm  |
13 |  5 | zlm  |
14 +----+------+
15 5 rows in set (0.00 sec)
16  
17 root@localhost:mysql3306.sock [zlm]05:39:09>show binary logs;
18 +------------------+-----------+
19 | Log_name         | File_size |
20 +------------------+-----------+
21 | mysql-bin.000013 |       217 |
22 | mysql-bin.000014 |      1341 |
23 | mysql-bin.000015 |       217 |
24 | mysql-bin.000016 |       680 |
25 | mysql-bin.000017 | 268435617 |
26 | mysql-bin.000018 |  72724124 |
27 +------------------+-----------+
28 8 rows in set (0.04 sec)
29  
30 root@localhost:mysql3306.sock [zlm]05:39:26>exit
31 Bye
32  
33 [root@zlm1 17:40:34 ~]
34 #cd /data/mysql/mysql3306/logs
35  
36 [root@zlm1 17:40:50 /data/mysql/mysql3306/logs]
37 #ls -l
38 total 368408
39 -rw-r----- 1 mysql mysql       217 May 26 15:37 mysql-bin.000013
40 -rw-r----- 1 mysql mysql      1341 May 26 22:24 mysql-bin.000014
41 -rw-r----- 1 mysql mysql       217 May 26 22:24 mysql-bin.000015
42 -rw-r----- 1 mysql mysql       680 May 30 21:22 mysql-bin.000016
43 -rw-r----- 1 mysql mysql 268435617 Jun  1 16:57 mysql-bin.000017
44 -rw-r----- 1 mysql mysql  72724124 Jun  1 17:39 mysql-bin.000018
45 -rw-r----- 1 mysql mysql       264 Jun  1 16:57 mysql-bin.index
46  
47 [root@zlm1 17:40:53 /data/mysql/mysql3306/logs]
50 #

 

   
now,let’s using the MyFlash tool to flashback the correct data.you should notice that only one binary log
can be put in the parameter “binlogFileNames”.it cannot be too big up to 256M,’cauze in
my early case,i put 100w records into the test table at the begining,the
operation was killed by OS automatically twice,it’s amazing…sometime
i’ll test it again to find out the real reason for that.

 

 1 [root@zlm1 18:01:21 ~]
 2 #cd /vagrant/MyFlash-master/binary/
 3  
 4 [root@zlm1 18:02:50 /vagrant/MyFlash-master/binary]
 5 #ls -l
 6 total 7366
 7 -rwxrwxrwx 1 vagrant vagrant   78542 Oct 25  2017 flashback
 8 -rwxrwxrwx 1 vagrant vagrant 7463125 Oct 25  2017 mysqlbinlog20160408
 9  
10 [root@zlm1 18:02:51 /vagrant/MyFlash-master/binary]
11 #./flashback --databaseNames zlm --tableNames test_flashbk --sqlTypes update  --maxSplitSize=10 --binlogFileNames=/data/mysql/mysql3306/logs/mysql-bin.000018
12  
13 [root@zlm1 18:03:15 /vagrant/MyFlash-master/binary]
14 #ls -l
15 total 78628
16 -rwxrwxrwx 1 vagrant vagrant 10491131 Jun  1 18:03 binlog_output_base.000001
17 -rwxrwxrwx 1 vagrant vagrant 10489686 Jun  1 18:03 binlog_output_base.000002
18 -rwxrwxrwx 1 vagrant vagrant 10489686 Jun  1 18:03 binlog_output_base.000003
19 -rwxrwxrwx 1 vagrant vagrant 10485809 Jun  1 18:03 binlog_output_base.000004
20 -rwxrwxrwx 1 vagrant vagrant 10486005 Jun  1 18:03 binlog_output_base.000005
21 -rwxrwxrwx 1 vagrant vagrant 10486005 Jun  1 18:03 binlog_output_base.000006
22 -rwxrwxrwx 1 vagrant vagrant 10042310 Jun  1 18:03 binlog_output_base.000007
23 -rwxrwxrwx 1 vagrant vagrant    78542 Oct 25  2017 flashback
24 -rwxrwxrwx 1 vagrant vagrant  7463125 Oct 25  2017 mysqlbinlog20160408
25  
26 [root@zlm1 18:03:19 /vagrant/MyFlash-master/binary]
27 #

 

    here,i used the parameter
“maxSplitSize” to split the output flashback file into 7 files,each one
became 10M around.after that,i try to recover the data by the shell
script below,unfortunately it failed:

 

 1 [root@zlm1 18:37:00 ~]
 2 #cat > recover.sh <<aaron8219
 3 > #!/bin/bash
 4 > BASEDIR=/vagrant/MyFlash-master/binary
 5 > FILE=\`find \${BASEDIR} -name "binlog_output_base.00000*"|sort -n\`
 6 > for i in \${FILE}
 7 > do
 8 >   mysqlbinlog \${i} | mysql
 9 > done
10 > aaron8219
11  
12 [root@zlm1 18:37:03 ~]
13 #cat recover.sh 
14 #!/bin/bash
15 BASEDIR=/vagrant/MyFlash-master/binary
16 FILE=`find ${BASEDIR} -name "binlog_output_base.00000*"|sort -n`
17 for i in ${FILE}
18 do
19   mysqlbinlog ${i} | mysql
20 done
21  
22 [root@zlm1 18:37:09 ~]
23 #ls -l
24 total 16
25 -rw-------. 1 root root 1431 Jul 16  2015 anaconda-ks.cfg
26 -rwxr-xr-x  1 root root   59 Apr  2 14:29 mysqld.sh
27 -rwxr-xr-x  1 root root   40 Jun  1 16:13 mysql.sh
28 -rw-r--r--  1 root root  168 Jun  1 18:37 recover.sh
29 -rw-r--r--  1 root root    0 May 30 20:33 rename_tb.sql
30  
31 [root@zlm1 18:37:12 ~]
32 #sh recover.sh
33 ERROR 1782 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
34 ERROR 1782 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
35 ERROR 1782 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
36 ERROR 1782 (HY000) at line 14: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
37 ERROR 1782 (HY000) at line 14: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
38 ERROR 1782 (HY000) at line 14: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

 

发表评论

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