数据迁移方案
英语错题本-通告和通知的区别
2023年2月21日发(作者：中医美容学)数据备份数据迁移⽅案
数据备份⽅式
物理备份
冷备:cp、tar、…
逻辑备份
mysqldump//备份
mysql//恢复
物理备份及恢复
备份
]cp-r/var/lib/mysql⽬录/
]tar-zcvf/root//var/lib/mysql/*
恢复
]cp-r⽬录//var/lib/mysql/
]tar-xvf/root/-C/var/lib/mysql/
]chown-Rmysql:mysql/var/lib/mysql
逻辑备份
数据备份策略
完全备份:备份所有数据源
增量备份:备份上次备份后,所有新产⽣的数据
差异备份:备份上次完全备份后,所有新产⽣的数据
完全备份和恢复
]mysqldump-uroot-p密码库名>⽬录/⽂件.sql#备份
]mysqldump-uroot-p密码-A|--all-databases>⽬录/⽂件.sql#备份所有库
]mysqldump-uroot-p密码库名表名>⽬录/⽂件.sql#备份表
]mysqldump-uroot-p密码-B数据库1数据库2>⽬录/⽂件.sql#备份多个数据库
]mysql-uroot-p密码库<⽬录/⽂件.sql#恢复
增量备份
⽇志⽂件
1)什么是binlog⽇志?
⼆进制⽇志
MySQL服务⽇志⽂件的⼀种
记录除查询之外所有的SQL命令
可⽤于数据备份和恢复
配置mysql主从同步的必要条件
2)启⽤binlog⽇志
配置项⽤途
server_id=数字指定id值(1-255)
log_bin[=⽬录/⽂件]启⽤binlog⽇志
max_binlog_size=数值m指定⽇志⽂件容量,默认1G
配置项⽤途
]vim/etc/
[mysqld]
log_bin
server_id=50#启⽤binlog必须指定server_id
3)binlog相关⽂件
主机名-bin’.index//索引⽂件
主机名-bin.000001//第⼀个⼆进制⽇志
主机名-bin.000002//第⼆个⼆进制⽇志
…
4)⼿动⽣成新的⽇志⽂件
四个⽅法:
]systemctlrestartmysqld
mysql>flushlogs;
]mysql-uroot-p密码-e'flushlogs'
]mysqldump-uroot-p密码--flush-logs库>⽬录/⽂件
5)清理⽇志
删除指定编号之前的binlog⽇志⽂件:
mysql>purgemasterlogsto"binlog⽂件名";
删除所有的binlog⽇志,重建新⽇志:
mysql>resetmaster;
6)相关命令
mysql>showmasterstatus;#查看⽇志信息
2.使⽤binlog⽇志⽂件恢复数据
命令格式
]mysqlbinlog[选项]binlog⽇志⽂件#将⽂件内容输出到屏幕
]mysqlbinlog[选项]binlog⽇志⽂件|mysql-uroot-ppassword#恢复数据,不加选项时恢复全部数据
选项:
--start-position=数字#指定起始偏移量
--stop-position=数字#指定结束偏移量
--start-datetime="yyyy-mm-ddhh:mm:ss"#起始时间
--stop-datetime="yyyy-mm-ddhh:mm:ss"#结束时间
binlog⽇志记录格式
row⾏模式
statement报表模式
mixed混合模式
mysql>showvariableslike"binlog_format";#查看⽇志⽂件记录格式
vim/etc/#修改⽇志⽂件记录格式,mixed模式可以通过命令查看偏移量和时间
binlog_format="mixed"
使⽤binlog⽇志⽂件恢复指定范围内的数据
查看要恢复的数据所在的偏移量和时间:
mysqlbinlogbinlog⽇志⽂件
内容解析:
at数字#数字为偏移量
commit#代表’回车’,查找结束偏移量或时间需要包括⼀个commit
恢复数据:
mysqlbinlog选项binlog⽇志⽂件|mysql-uroot-ppassword
例:
mysqlbinlog--start-position=300--stop-position=1185dzh.000002|mysql-uroot-p123456
a
常⽤备份⼯具的缺点:
物理备份缺点:跨平台性差,备份时间长,冗余备份,浪费存储空间
mysqldump缺点:效率低,备份和还原速度慢,锁表
XtraBackup:
在线热备不锁库表,适合⽣产环境
由专业组织Percona提供(改进MySQL分⽀)
主要包括两个组件:xtrabackup(C程序,⽀持innodb和xtradb),innobackupex(perl脚本封装的xtrabackup,额外⽀持MyISAM)
7.x86_
whichinnobackupex
常⽤选项含义
--host主机名
--user⽤户名
--port端⼝号
--password密码
--databases数据库名
--no-timestamp不⽤⽇期命名存储备份⽂件的⼦⽬录名
--redo-only⽇志合并
--apply-log准备恢复数据
--copy-back拷贝数据
--incremental⽬录名增量备份
--incremental-basedir=⽬录名增量备份时,指定上⼀次备份数据存储的⽬录名
--incremental-dir=⽬录名准备恢复数据时,指定增量备份数据存储的⽬录名
--export导出表信息
--databases="库"
--databases="库1库2"
--databases="库1.表"
1)innobackupex完全备份和恢复
备份:(备份后数据在指定的⽬录下)
innobackupex选项⽬录
例:
innobackupex--host地址--port端⼝号--userroot--passwordPASSWORD--databases=库⽬录名--no-timestamp#备份所有数据,不指定库时为备份所有
恢复:
1.停⽌数据库服务
2.清空数据⽬录下的数据
3.准备恢复数据
4.恢复数据
5.修改数据⽬录下⽂件的所有者/组为mysql
6.启动数据库服务
7.管理员登录查看数据
systemctlstopmysqld#关闭服务
rm-rf/var/lib/mysql/*#清空数据
7.x86_#安装⼯具
innobackupex--apply-log备份⽂件路径#准备恢复数据
innobackupex--copy-back备份⽂件路径#拷贝数据到数据库⽬录下
chown-Rmysql:mysql/var/lib/mysql/*#修改权限
2)innobackupex在完全备份恢复某表的所有数据
表空间
存储表记录的⽂件
1.删除表空间
2.导出表信息
3.拷贝表信息⽂件到数据库⽬录下
4.修改表信息⽂件的所有者及组⽤户为mysql
5.导⼊表空间
6.删除数据库⽬录下的表信息⽂件
7.查看表记录
mysql>altertable库.表discardtablespace;#删除表空间
innobackupex--apply-log--export数据备份⽬录#导出表信息
cp数据备份⽬录/数据库名⽬录/表名.{ibd,exp,cfg}/var/lib/mysql/库名⽬录/#cp表信息⽂件到数据库⽬录下
chown-Rmysql:mysql/var/lib/mysql/库名⽬录/#修改权限
mysql>altertable库.表importtablespace;#导⼊表空间
rm-rf/var/lib/mysql/库名⽬录/表.{exp,cfg}#删除表信息⽂件
3)innobackupex增量备份和恢复
增量备份:
1.判断是否有新数据产⽣
2.有新数据时判断哪些数据是新数据
备份⽬录/xtrabackup_checkpoints⽂件:
lsn:⽇志序列号
backup_type含义
full-backuped完全备份
incremental增量备份
log-applied已应⽤(已准备好恢复数据)
应⽤实例:
周⼀:完全备份
]innobackupex--userroot--passwordPASSWORD/allbak--no-timestamp#备份所有数据
]cat/allbak/xtrabackup_checkpoints
#lsn:⽇志序列号
backup_type=full-backuped
from_lsn=0#备份数据开始序列号
to_lsn=2880259#备份数据结尾序列号
last_lsn=2880268#增量备份时,使⽤此序列号与数据库⽬录下的ib_logfile进⾏⽐对,确定是否有新数据
compact=0
recover_binlog_info=0
周⼆:增量备份
]innobackupex--userroot--passwordPASSWORD--incremental/new1dir--incremental-basedir=/allbak--no-timestamp
]cat/new1dir/xtrabackup_checkpoints
backup_type=incremental
from_lsn=2880259#通过上⼀次备份的结尾确定这⼀次备份的开始
to_lsn=2882360
last_lsn=2882369#再次进⾏增量备份时,同样进⾏⽐对
compact=0
recover_binlog_info=0
周三:增量备份
]innobackupex--userroot--passwordPASSWORD--incremental/new2dir--incremental-basedir=/new1dir--no-timestamp
]cat/new2dir/xtrabackup_checkpoints
backup_type=incremental
from_lsn=2882360
to_lsn=2884453
last_lsn=2884462
compact=0
recover_binlog_info=0
增量恢复:
1.停⽌数据库服务
2.清空数据库⽬录下内容
3.准备恢复数据(合并⽇志)
4.拷贝数据到数据库⽬录下
5.修改⽂件所有者/组为mysql
6.启动数据库服务
systemctlstopmysqld
rm-rf/var/lib/mysql/*
innobackupex--apply-log--redo-only/allbak#合并⽇志
innobackupex--apply-log--redo-only/allbak--incremental-dir=/new1dir#合并增量⽇志
innobackupex--apply-log--redo-only/allbak--incremental-dir=/new2dir#合并增量⽇志
innobackupex--copy-back/allbak#拷贝⽂件
chown-Rmysql:mysql/var/lib/mysql/*
systemctlstartmysqld
4)数据迁移⽅案
1.从服务器安装mysql,启动服务完成初始化，然后stop服务，删除/var/lib/mysql/下的所有⽂件，为恢复数据做准备
2.使⽤innobackupex⼯具备份所有数据，并记录当前的binlog⽇志名和偏移量
innobackupex--userroot--passwordPASSWORD--slave-info/allbak--no-timestamp#使⽤--slave-info参数，备份所有数据，并记录备份数据对应的binlo
g⽇志名
3.将备份⽂档发送到从服务器
scp-r/allbakroot@slave:
4.从服务器使⽤备份⽂件恢复数据
5.启动服务
6.查看备份⽂件中记录的binlog⽇志信息，配置主从changemasterto
]cat/root/allbak/xtrabackup_info|grepmaster11#master11为当前主服务器的binlog⽇志前缀
binlog_pos=filename'master11.000001',position'7700'
从服务器的mysqld服务，卸载，安装pxc，启动pxc，此时该服务器依然为线上服务器的从服务器，数据保证了同步，继续配置
其余pxc服务器即可