admin 管理员组文章数量: 1086019
8.mysql备份恢复
1.mysql备份方式分类:
2.冷备:
/usr/local/mysql/bin/mysqladmin -uroot -ppassword shutdown
scp -r /data/mysql/* root@远程主机ip:/tmp
cp -r /data/mysql /backup
3.热备(逻辑备):
mysqldump -help
/usr/local/mysql/bin/mysqldump –help
--single-transaction:确保备份时一致性,配合rr隔离级使用;
--all-databases (-A):备份所有数据库;
--master-data: 1表示在备出来文件中添加一个change master语句,2表示在备份文件中添加一个一个change master语句,并在语句前面添加注释符号;
--dump-slave:用于在从库端备份数据,在线搭建时使用;
--no-create-info (-t):备份过程中只备份表数据,并不备份表结构;
--no-data(-d):备份过程中只备份表结构,并不备份表数据;
--complete-insert(-c):使用完整的insert语句会包含表中列信息,可以提高插入效率;
--databases (-B):备份多个数据库:mysqldump –uroot –ppassword –database db1 db2;
--default-character-set:字符集,默认为UTF8,要与备份出的表的字符集一致;
--quick(-q):相当于加sql_no_query,不读取缓存中数据;
--where=name(w):按条件备份出想要的数据;
例:全库备份:
/usr/local/mysql/bin/mysqldump --single-transation -uroot -pjiradbt -A >all-20180820.sql
恢复全库:/usr/local/mysql/bin/mysql -uroot -ppassword < all_2018.sql
备份单库:/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot123 dbname >dbname-2018.sql
恢复单库:/usr/local/mysql/bin/mysql -uroot -proot123 dbname <dbname2018.sql (db若存在名字,可以直接恢复,若没有dbname,则需要先创建一个dbname:create database dbname)
备份dbname库下tablename单表:/usr/local/mysql/bin/mysqldump -single-transaction –uroot –proot123 dbname tablename >tablename-2018.sql
恢复单表:/usr/local/mysql/bin/mysql -uroot -proot123 dbname <tablename-2018.sql(恢复单表前,导入符号前面不需要写表名,只需要写上库名)
备份dbname库中表tablename的表结构信息(-d参数):
/usr/local/mysql/bin/myqldump --single-transaction -uroot -proot123 dbname tablename -d >tablename.sql
备份dbname库中表tablename的表数据信息(-t参数):
/usr/local/mysql/bin/mysqldump –single-transaction -uroot -proot123 dbname tablename -t >tablename.sql
按条件备份:备份dbname中t表id大于3的记录:
/usr/local/mysql/bin/mysql/mysqldump --single-transaction -uroot -proot123 dbname t --where="id>3" >t.sql
Mysql5.7后新增innodb_buffer_pool_dump_pct参数,用来控制每个innodb buffer中转储活跃使用的innodb buffer pages比例,只有当数据在1秒内再次被访问时,才能放到热区域内,避免热数据被冲走,默认值为25%:show variables like '%innodb_buffer_pool_dump_pct%';
4.select ....into outfile逻辑备份:
恢复数据非常快,比insert的插入速度快很多;只能备表中数据,且不包括表结构;恢复时必须有表结构存在:
常用语法:select col1,col2...from tablename into outfile '/path/备份名';
例:导出表test数据:
select * from test;
select * from test into outfile '/tmp/test.sql';
恢复过程:
delete from test;
load data infile '/tmp/test.sql' into table mysql.test;
帮助:?load data或mysql> help load data
5.load data与insert插入速度对比:
select count(*) from test1;(10万条数据)
导出数据:select * from test1 into outfile >'/tmp/test1.sql';
删除数据:truncate table test1;
导入数据:time mysql -uroot -proot123 -e "LOAD DATA INFILE '/tmp/test1.sql' INTO TABLE mysql.test1";
(mysql -e可以调用数据库命令行命令)
insert 插入数据:
用sqlyog工具把test1表中数据全部备份出来,
begin;
insert into 'test1'('name','address') values('name1','address1');
..........
commit;
删除数据:truncate table test1;
导入数据:root#time mysql -uroot -proot123 dbname <test1.sql (load data为insert 的12倍速度)
6.mydumper是mysql自带单线程工具,只能逐个导出表,速度高于mysqldump,属性为逻辑备份,DBA必备份界工具:
安装:/+download
安装前提:yum install cmake* ; yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel
tar -zxvf mydumper-0.6.2.tar.gz
cd mydumper-0.6.3
cmake &&make&&make install
全库备份:mydumper -uroot -proot123 -o /backup/ (输出文件的目录)
备份test库:mydumper -uroot -proot123 -B test -o /backukp/ (-B:需要备份的数据库)
还原test库:myloader -uroot -proot123 -B test -d /backup/
备份test库下表tt:mydumper -uroot -proot123 -B test -T tt -o /backup/
还原tt表:myloader -uroot -proot123 -B test -o tt -d /backup/
备份test下多张表:mydumper -uroot -proot123 -B test -T tt,dd -o /backup/
备份test库下tt表的数据,不备份表结构:mydumper -uroot -proot123 -B test -T tt -m -o /backup/
备份test库下表tt,并进行压缩:mydumper -uroot -proot123 -B test -T tt -c -o /backup
mysqldump和mydumper速度对比:
time /usr/local/mysql/bin/mysqldump --set-gtid-purged=OFF --single-transaction -uroot -proot123 -A >all.sql
time mydumper -uroot -proot123 -o /backup/ (速度快)
mydumper优点总结:多线程备份工具;支持压缩备,支持多线程,保证数据一致性;比mysqldump备份速度和恢复速度快。
7.裸文件备份:XtraBackup
热备份两种方式:逻辑备份和裸文件备份,裸文件比逻辑备份更快;备份过程不锁表
/
tar -zxvf percona-xtrabackup-2.4.12-Linux-x86_64.tar.gz
cd percona-xtrabackup-2.4.12-Linux-x86_64/bin
查看参数:./innobackup -help
全备过程:创建备份所需要的用户名和密码:
create user 'backup'@'192.168.1.%' identified by 'backup';
grant reload,lock tables,replication client,process,super on *.* to 'backup'@'192.168.1.%';
mkdir /backup/;
不采用系统默认创建的备份集文件名称:--no-timestamp参数:
全备:innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup --host 192.168.1.100 --password backup /backup/all.bak
全备的恢复:drop table test;
innobackupex --defaults-file=/etc/my.cnf --user backup --host 192.168.1.100 --password backup --apply-log /backup/all.bak
(--apply-log:通过回滚未提交的事务及同步已经提交的事务到数据文件,使数据文件处于一致性状态)
恢复后还得改名:
mysqladmin -uroot -proot123 shutdown
mv /data/mysq /data/mysql_bak
mv /backup/all.bak /data/
mv /data/all.bak/ mysql
chown mysql:mysql -R mysql
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &
show database;
use zs;
select * from tt;
例:XtraBackup增量备份:
先全备:innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup --host 192.168.1.100 --password backup /bacup/all2018.bak
查看备份完成后的lsn;xtrabackup_checkpoints文件记录内容。
插入数据:insert into tt(name,score) values('dd',90);
增备参数:--incremental参数,增备目录文件:/backup/all2018.bak
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup --host 192.168.1.100 --password backup --incremental /backup/all2018-incr01 --incremental-basedir=/backup/all2018.bak
insert into tt(name,score) values('ee',100);
再次执行增备:
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup --host 192.168.1.100 --password backup --incremental /backup/all2018-incr02 --incremental-basedir=/backup/all2018-incr01
增备恢复:
drop table tt;
先恢复全备,然后再进行增备文件恢复到全备,前两个增备文件过程需要使用--redo-only参数,只前滚XtraBackup日志中已经提交的事务,并不回滚还没有提交的事务,最后再对整体全备进行恢复,去掉--redo-only参数,回滚还没有提交的事务。
恢复过程:
innobackupex --defaults-file=/etc/my.cnf --user backup --host 192.168.1.100 --password backup --apply-log --redo-only /backup/all2018.bak
增备恢复:
innobackupex --defaults-file=/etc/my.cnf --user backup --host 192.168.1.100 --password backup --apply-log --redo-only /backup/all2018.bak -incremental-dir=/backup/all2018-incr01
innobackupex --defaults-file=/etc/my.cnf --user backup --host 192.168.1.100 --password backup --apply-log --redo-only /backup/all2018.bak -incremental-dir=/backup/all2018-incr02
最后把新的全备进行一次恢复:
innobackupex --defaults-file=/etc/my.cnf --user backup --host 192.168.1.100 --password backup --apply-log /backup/all2018.bak 即可
跟之前备备恢复,需要先停掉mysql实例,重命名原来数据库目录,把新备份集mv到/data下,改名为mysql,并授权,再重启实例,即可。
mysqladmin -uroot -proot123 shutdown
mv /data/mysq /data/mysql_bak
mv /backup/all.bak /data/
mv /data/all.bak/ mysql
chown mysql:mysql -R mysql
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &
8.流式化备份:
非压缩模式,参数:--stream (基于tar格式),备份目录:/backup
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup --host 192.168.1.100 --password backup --stream=tar /tmp >/backup/all.tar
压缩模式的备份:
./innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup --host 192.168.1.100 --password backup --stream=tar /tmp |gzip >/backup/all.tar.gz
远程备份:
流式化备份可以把备份文件传到远程服务器上,例:把192.168.1.100上的备份传到192.168.2.100的/backup目录上:
./innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup --host 192.168.1.100 --password backup --stream=tar |gzip |ssh root@192.168.2.100 "cat - >/backup/2018bak.tar.gz"
注:两主机要互信,不能输入密码,且在192.168.1.100上创建好备份目录
表空间传输:mysql5.6版本后支持。把一张表从一个库移到别一个数据库上。比mysqldump更快:
条件:(1)mysql为5.6及以后版本;
(2)使用独立表空间方式,默认开启innodb_file_per_table;
(3)目标与源库pagesize一致,导出操作时,表只能读操作。
例:把zz库下表tt传到yy库上tt表中。
(a).use yy
create table tt(id int(11) not null auto_increment,name varchar(20) not null,score tinyint(1) not null default '0',primary key ('id'))
engine=innodb auto_increment=1 default charset=utf8mb4;
(b).卸载yy库下tt表的表空间:
alter table tt discard tablespace;
(c).在zz库下执行导出:
flush table tt for export;
将导出的tt.ibd和tt.cfg文件复制到yy库下,修改为mysql权限
chown mysql:mysql *
use zz
unlock tables;
use yy
alter table tt import tablespace;
利用binLog2sql进行闪回:
比较好用工具,可以使用mysqlbinlog进行基于时间点或位置的数据恢复操作,binlog2sql可以实现数据的快速回滚,从binlog中提取SQL,并生成回滚sql语句。只能使用在binlog格式为row的模式下。
安装前提:python-pip,pymysql,python-mysql-replication,wheelargparse
解压:binlog2sql-master.zip
cd binlog2sql-master
pip install -r requirements.txt
帮助:python binlog2sql.py -help
-B,--flashback :生成回滚语句
--start-file:需要解析的binlog文件
--start-position:解析binlog的起始位置
--stop-position:解析binlog的结束位置
--start-datetime从哪个时间点开始解析,格式为datetime
--stop-datetime到哪个时间点的binlog停止解析,格式为datetime
-d,--databases只输出目标db的sql
-t,--tables只输出目标tables的SQL
例:grant select,replication slave,replication client on *.* to 'zz-test'@'%' identified by 'zz-test';
flush priviliges;
show master status; 确定当前binlog文件是什么
python binlog2sql.py -h192.168.1.100 -P3306 -uzz-test -pzz-test -dzz -ttt --start-file='mysql-binlog.000001' --start-file ='mysql-binlog.0001' --start-datetime='2018-0828 16:00:00' --stop-datetime='2018-0828 17:00:00'
解析出上面误操作数据,就可以用flashback模式生成回滚SQL。
python binlog2sql.py -h192.168.1.100 -P3306 -uzz-test -pzz-test -dzz -ttt --start-file='mysql-binlog.000001' --start-file ='mysql-binlog.0001' --start-position=796 --stop-position=999 -B >rollback.sql
mysql -uzz-test -pzz-test
use zz
source /backup/rollback.sql 就可以回滚回去了。
binlog server:可以利用mysqlbinlog命令把远程机器的日志备份到本地目录:
例:192.168.1.101为备份服务器,192.168.1.100是正在运行的mysql数据库。
重要参数:
-R --read-from-remote-server:代表从远程mysql服务器上读取binlog
-raw:以binlog格式存储日志,方便后面使用。
--stop-never:连接到远程mysql读日志,直接远程DB服务被关闭
mysql-bin.***:代表从那个日志开始备份。
--stop-never-slave-server-id mysqlbinlog:相当于从库取主库的日志
例:在1.100备份服务器上建立目录 /backup/binbak
mkdir -p /backup/binbak
在1.100上执行远程备份:/usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=192.168.1.101 --port=3306 --user=zz-test --passwork=zz-test mysql-binlog.000001
本文标签: 8mysql备份恢复
版权声明:本文标题:8.mysql备份恢复 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.roclinux.cn/b/1686557901a10063.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论