1、ENGINE=InnoDB 数据仓库储存款和储蓄引擎,DEFAULT 私下认可,CHAENCORESET=utf8
数据库字符编码

 

一.概述

  前边讲了备份的一些理论知识,那篇开首讲在简练苏醒格局下的备份与回复。在轻便形式下是不能够做日志备份的,发生横祸后,数据库最终一次备份之后做的数码修改将是百分百不胫而走的,所以在生育境遇下,数据又很关键,一般不建议利用这种情势。
举个例子对四个数据库有5次完整数据备份,时间是t5,  之后发生祸患,就能够部遗失。

图片 1

  当数据库更加大,完整备份时间会愈来愈长,为了缩短遗失危机,引进差距备份。举个例子下图演示:在率先次建构数据库完整备份后,创设了一回差别备份,之后再次创下立完整备份,从而建设构造新的不相同基准。不管是欧洲经济共同体备份依然距离备份,一般只好在晚间进展。借使数量相比壮大又不容许长日子数额错失,那简单恢复生机情势是无法满意的。

图片 2

2、数据库的蕴藏引擎,

Preface

二.备份演示

  在简易复苏格局下重要的备份是总体备份和不相同备份。小编那边有TestLog库,Curry有三个表。假设星期六做贰回完整备份,星期一到周日夜晚每天做贰次差距备份,到第二周的周日时初始新的基准线。如下所示

use test
exec sp_addumpdevice 'disk', 'BackupTestDevice','F:\SqlService\backup\BackupTestBackup.bak'

  图片 3

--设置恢复模式为简单恢复
 ALTER DATABASE TestLog SET RECOVERY simple
go
-- 做一次完整备份到备份设备中(备份基准) 假设在周日晚上
backup database  TestLog to BackupTestDevice
go

图片 4

go
--差异备份 周一晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周二晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周三晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周四晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周五晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周六晚
backup database TestLog to BackupTestDevice with differential 
go
--完整备份 周日晚(新基准)
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周一晚
backup database TestLog to BackupTestDevice with differential 

 在备份设备中查看备份集
  图片 5

-- 通过脚本查看

select distinct
s.first_lsn,s.last_lsn,s.database_backup_lsn,s.position,
s.backup_finish_date,s.type,y.physical_device_name,s.backup_size
from msdb..backupset as s inner join
msdb..backupfile as f on f.backup_set_id=s.backup_set_id inner
join
msdb..backupmediaset as m on s.media_set_id=m.media_set_id inner
join
msdb..backupmediafamily as y on m.media_set_id=y.media_set_id
where s.database_name=’TestLog’
order by s.position asc

图片 6

 

mysql中engine=innodb和engine=myisam的区别

 

三. 还原演示

   将八个数据库还原,必要组织叁个不易的余烬复起顺序。在还原经过中,备份文件结尾使用norecovery事务不恢复生机(正在恢复生机。。)不可读写,在最后贰个备份文件结尾使用recovery事务恢复。数据库恢复生机平常。

-- 切换到master库
use master

--设置单用户模式(否则执行下面报错:“因为数据库正在使用,所以无法获得对数据库的独占访问权”)
ALTER DATABASE TestLog SET OFFLINE WITH ROLLBACK IMMEDIATE

-- 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestLog from BackupTestDevice with file=1, norecovery 

  图片 7图片 8

-- 恢复到差异备份文件3,跳过差异备份2 ,看是否备份成功
restore database TestLog from BackupTestDevice  with file=3, recovery

  图片 9图片 10

-- 备份结束之后,结束单用户模式
ALTER  database  TestLog  set   online  

 下边在来演示还原差别文件,使用旧准则。还原看会怎样

-- 从旧基准中恢复一个全备份 ,norecovery(正在还原...)不可读写. file是1
restore database TestLog from BackupTestDevice with file=1, norecovery 

--新基准file是8, 恢复到差异备份文件9 
restore database TestLog from BackupTestDevice  with file=9, recovery

图片 11

 

 计算:对于简易复苏情势,未有日记备份,复苏只要求八个完好无缺数据库备份,以及尾声二个异样备份。
对于七个差别备份文件,在还原时不需求LSN的延续性(在同贰个尺度内)。

myisam:读取速度极快,不占用大量能源,但是又多个毛病,1、不帮助事物,2、容错倒霉。硬盘崩溃了,数据就没了,就算说坚定不移要用在极度关键程序,要经过其复制特性实时的去备份数据,,MySQL能够接济这样的备份应用程序。MyISAM类型的二进制数据文件能够在不相同操作系统中迁移。也正是足以平昔从Windows系统拷贝到linux系统中央银行使。

    In my previous blogs,I’ve demonstrated
several mothods of how to rescue a dropped table(or truncated table as
well).

Innodb:它提供了事情调整技术功能,它确定保障一组命令全部实行成功,或然当别的五个下令出现错误时怀有命令的结果都被回落,在电子银行山西中华南理理高校程公司作调控技艺是极度首要的。帮忙COMMIT、ROLLBACK和其它作业性情。目前多少库表结构划虚拟计的时候一般都选取这种存款和储蓄引擎。但是速度慢,占用磁盘空间相当多。

  •     full mysqldump backup + binlog on
    master(master was normally running)
  •     full Xtrabackup backup + binlog on
    master
    (master was normally
    running)**
  •     full Xtrabackup backup + binlog on
    binlog server(
     binlog server had acquired binlogs
    before master crashed.)

详尽分解转发于:

 

   
Les’t see another way to achieve the goal more simply.

 

Procedure

 

***Step

  1. Destroy***

 

Check
the table on master.

 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    4
 5 Current database: sysbench
 6 
 7 +--------------------+
 8 | Tables_in_sysbench |
 9 +--------------------+
10 | sbtest1            |
11 | sbtest2            |
12 | sbtest3            |
13 | sbtest4            |
14 | sbtest5            |
15 +--------------------+
16 5 rows in set (0.00 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
19 +----------+
20 | count(*) |
21 +----------+
22 |    10000 |
23 +----------+
24 1 row in set (0.01 sec)

 

**Generate
a full Xtrabackup of master.**

 1 [root@zlm2 06:28:44 /data/mysql/mysql3306/data]
 2 #innobackupex --default-file=/data/mysql/mysql3306/my.cnf --host=localhost -uroot -pPassw0rd /data/backup
 3 xtrabackup: recognized server arguments: 
 4 xtrabackup: recognized client arguments: 
 5 180731 06:29:03 innobackupex: Starting the backup operation
 6 ... //Omitted.
 7 
 8 180731 06:29:31 Executing UNLOCK TABLES
 9 180731 06:29:31 All tables unlocked
10 180731 06:29:31 [00] Copying ib_buffer_pool to /data/backup/2018-07-31_06-29-03/ib_buffer_pool
11 180731 06:29:31 [00]        ...done
12 180731 06:29:31 Backup created in directory '/data/backup/2018-07-31_06-29-03/'
13 MySQL binlog position: filename 'mysql-bin.000043', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229'
14 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/backup-my.cnf
15 180731 06:29:31 [00]        ...done
16 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/xtrabackup_info
17 180731 06:29:31 [00]        ...done
18 xtrabackup: Transaction log of lsn (10141400402) to (10141400411) was copied.
19 180731 06:29:31 completed OK!

 

**Continue
to executing some dml operations on the target table and then kill the
mysqld.**

 1 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 2000;
 2 Query OK, 2000 rows affected (0.10 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 3000;
 5 Query OK, 3000 rows affected (0.07 sec)
 6 
 7 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
 8 +----------+
 9 | count(*) |
10 +----------+
11 |     5000 |
12 +----------+
13 1 row in set (0.00 sec)
14 
15 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest5;
16 Query OK, 0 rows affected (0.01 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
19 Query OK, 0 rows affected (0.02 sec)
20 
21 (zlm@192.168.1.101 3306)[sysbench]>show master status;
22 +------------------+----------+--------------+------------------+------------------------------------------------+
23 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
24 +------------------+----------+--------------+------------------+------------------------------------------------+
25 | mysql-bin.000044 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730232 |
26 +------------------+----------+--------------+------------------+------------------------------------------------+
27 1 row in set (0.00 sec)
28 
29 [root@zlm2 06:38:14 ~]
30 #pkill mysqld
31 
32 [root@zlm2 06:38:18 ~]
33 #ps aux|grep mysqld
34 root      4050  0.0  0.0 112640   956 pts/0    R+   06:38   0:00 grep --color=auto mysqld

 

**Scp the
Xtrabackup backup to another server zlm3 with newly initialized
instance**

1 [root@zlm2 06:43:42 ~]
2 #scp -r /data/backup/2018-07-31_06-29-03/ zlm3:/data/backup
3 root@zlm3's password: 
4 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
5 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02    
6 plugin.ibd                                                                                                         100%   96KB  96.0KB/s   00:00    
7 servers.ibd                                                                                                        100%   96KB  96.0KB/s   00:00
8 ... //Omitted.

 

**Step 2.
Rescue***


 

Restore the
backup on zlm3.

发表评论

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