备份恢复
1. 本地备份 使用mysqldump进行备份非常简单,在备份数据库的时候,我们还可以同时使用管道gzip命令对备份文件进行压缩,可以采用Rsync的异地备份方式方式,将备份服务器的目录挂载到数据库服务器,将数据库文件备份打包后,通过crontab定时备份数据:
备份数据使用命令:
#!/bin/sh time=`date +"("%F")"%R`
$/usr/local/mysql/bin/mysqldump -u root -p111 database_backup | gzip > /home/zhenghan/mysql/mysql_backup.$time.gz # crontab -l # m h dom mon dow command00 00 * * * /home/zhenghan/mysql/backup.sh
恢复数据使用命令:
gzip -d mysql_backup.\(2014-06-17\)00\:00.gz mysql_backup.(2014-06-17)00:00
#mysql –u root -p111 < /home/zhenghan/mysql/mysql_backup.\(2014-06-17\)00\:00
3. MySQL本身自带的mysqldump备份 使用mysqldump可以把整个数据库装载到一个单独的文本文件中。这个文件包含有所有重建您的数据库所需要的SQL命令。这个命令取得所有的模式(schema)并且将其转换成DDL语法(CREATE语句,即数据库定义语句),取得所有的数据,并且从这些数据中创建INSERT语句。这个工具将您的数据库中所有的设计倒转。因为所有的东西都被包含到了一个文本文件中。这个文本文件可以用一个简单的批处理和一个合适SQL语句导回到MySQL中。
4. 直接复制数据库文件的备份形式 直接拷贝数据文件最为直接、快速、方便,但缺点是基本上不能实现增量备份。为了保证数据的一致性,需要在备份文件前,执行以下SQL语句:FLUSH TABLES WITH READ LOCK;也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可。
以root用户启动服务,则可以使用以下语句备份
select * from user into outfile '/user/local/mysql/backup/db1'
误删除数据后怎么办。误删数据分类:
1、delete
2、drop table 或truncate table
3、drop database
4、rm命令删除整个mysql实例
误删数据行通过flashback恢复数据,需要确保binlog_format=row和binlog_row_image=full
如果误删数据涉及到了多个事务需要将事务的顺序调过来再执行。
不建议在主库上执行,恢复出一个备份或是找一个从库作为临时库,在这个临时库操作,确认后恢复到主库。
不止要说误删数据的事后处理办法,更重要的是要做到事前预防
1、把sql_safe_updates参数设置为on,这样如果在忘记delete,update,语句中加where条件,或者where条件里面没有包含索引字段话这条语句执行就会报错。
2、代码上线前必须经过sql审计
使用truncate /drop table和drop database命令删除的数据无法通过flashback来恢复。即使配了binlog_format=row,执行这三个命令时,binlog还是statement格式,binlog只有一个trncate/drop语句。
这时需要全量备份及增量日志,要求定期的全量备份并且有实时备份的binlog。例如:
1、取最新一次全量备份,
2、用备份恢复出一个临时库
3、从日志里面取出需要的日志
4、把这些日志除了误删除数据的语解码器全部应用到临时库
为了加速恢复数据,可以在使用mysqlbinlog加上一个-database参数指定误删表所在的库。避免恢复数据时还要应用其他日志的情况。
在应用日志时,需要跳过误操作的那个语句binlog,
a:如果原实例没有使用gtid模式,只能在应用到包含12点binlog文件,先用-stop-position参数很执行到误操作之前的日志,然后再用-start-position从误操作之后的日志继续执行。
b:如果实例使用了gtid模式,假设误操作命令的gtid是gtid1,那么只需要执行set gtid_next=gtid1;beigin;commit; 先把这个gtid加到临时实例的gtid集合,之后按顺序执行binlg的时候就会自动跳过误操作的语句。
以上还不够快,可以这样将这个临时实例设置成线上备库的从库
1、在start slave之前,先通过执行change replication filter replicate_do_table=(tbl_name)命令就可以让临时库只同步误操作的表
2、这样也可以用上并行复制技术来加速整个数据恢复过程。
如发现当前实例需要binlog是从master.000005开始的,但是在备库上执行show binlogs显示最小的文件是binlog是master.000007,意味着少了2个文修的,就需要从binlog的备份系统中的找到这两个文件。
1、从备份系统下载master.00005和master000006这两个文件放到备库的日志目录下
2、打开日志目录下的master.index文件,在文件开头加入两行,内容分辊是./master.000005和 ./master000006
3、重启备库,目的是要让备库重新识别这两件日志文件
4、现在备库上就有了临时库需要的所有binlog了,建立主备关系,就可以正常同步了。
搭建延迟复制的备库,延迟复制的备库是一种特殊的备库,通过change master to master_delay=N命令,可以指定这个备库持续保持跟主库有N秒的延迟,在发现误删后,在备库上执行stop slave再通过之前说的方法跳过这个操作合邻,就可以恢复出这个需要的数据。
确保MySQL打开 log-bin选项
备份:
mysqldump --database db1 db2 db3 >test.sql
备份所有数据库
mysqldump --all-database
mysqldump -uroot -p test emp dept>emp_dept.sql #备份数据库emp、dept表
mysqldump -uroot -T /tmp test emp --fields-terminated-by ',' #备份数据库test下的所有表为逗号分割的文本,备份到/tmp emp.txt ,查看emp.txt
对于事务存储引擎innodb和BDB来说,可以采用--single-transaction 此选项将使得innodb存储引擎得到一个snapshot,使得备份的数据能够保证一致性。
完全恢复
mysql -uroot -p dbname<bakfile
将备份恢复后的数据并不完整,还需要将备份后执行的日志进行重做
mysqlbinlog binlog-file \ mysql -uroot -proot
例如:
mysqldump -uroot -p -l -F test>test.dmp
-l表示所有表加读锁,-F表示生成一个新的日志文件,此时,test中emp表的数据如下:
稍后恢复数据
mysql -uroot -p test<test.dmp
使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysqlbinlog localhost-bin.0000015 | mysql -uroot -p test
基于时间点恢复
在mysql中不完全恢复分为基于时间点恢复和基于位置的恢复。
基于时间点恢复操作步骤。
用以下语句恢复到故障前
mysqlbinlog --stop-date='2005-04-20 9:45:33' /var/log/mysql/bin.123456 | mysql -uroot -proot
跳过时间点,继续执行后面的binlog
mysqlbinlog --start-date='2005-04-20 10:02:00' /var/log/mysql/bin123456 | mysql -uroot -proot
基于位置恢复
mysqlbinlog --start-date='2005-04-20 9:45:33' --stop-date ='2005-04-20 10:04:33' /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
找到出错语句前后的位置号,
mysqlbinlog --stop-position ='38649' /var/log/mysql/bin.123456
| mysql -uroot -proot
mysqlbinlog --start-position= '38748' /var/log/mysql/bin.123456 | mysql -uroot -proot
冷备份,停数据库服务,cp数据文件
热备份
myisam引擎
mysqlhotcopy db_name /path/to/new_directory
flush tables with read lock,然后cp数据文件到备份目录即可
innodb引擎
ibbackup是热备份工具,收费。ibbackup不会覆盖任何重名文件,如果重名,则可能会失败。
需要编辑my.cnf和用于备份的配置backup-my.cnf文件
备份完成后,会有数据文件和日志文件。
进行日志重做
ibbackup --apply-log /home/pekka/backup-my.cnf
服务重启后,利用binlog日志将备份点与故障点之间的剩余数据进行恢复
mysqlbinlog binlog-file | mysql -uroot -proot
xtrabackup热备工具,开源免费,支持在线热备,备份速度快,占用磁盘空间小等特点
只能备份innodb和 xtradb两种数据表。
备份开始时开启一个后台检测进程,实时检测redo log的变化,一旦发现有redo log写入,立刻将日志记入后台日志文件xtrabackup log中,之后复制innodb 的数据文件和系统表空间文件ibdata1,待复制结束后,执行flush tables withd read lock,复制frm,myi,myd等文件(执行flush tables withd read lock的目的是为了防止数据表发生DDL操作,并且在这一时刻获得binlog位置),最后发生unlock tables把表设置为可写读状态,最终停止xtrabackup_log。
创建备份用户
create user 'backup'@'%' identified by 123456
grant reload ,lock tables, replication client, create tablespace,super on *.* to 'backup'@'%'
创建备份目录路径 mkdir -p /data/backup/hotbackup/
创建innobackupex的配置文件 /tmp/my.cnf
[mysqld]
datadir="/home/mysql_test/mysqlhome/data/"
innodb_data_home_dir="/home/mysql_test/mysqlhome/data1"
innodb_data_file_path="ibdata1:10M:autoextend"
innodb_log_group_home_dir="/home/mysql_test/ysqlhome/data/"
innodb_log_files_in_group=2
innodb_log_files_size=3223328877
进行全量备份
innobackupex --user=backup -password=12345 --socket=/tmp/mysql_test.sock --defaults-file=/tmp/my.cnf /data/backup/hotbackup/full --no-timestamp
恢复全备
innobackupex --apply-log --use-memory=20G /data/backup/hotbackup/full
恢复备份到mysql数据文件目录,要先关闭mysqlsql,重命名原数据文件目录,再创建一个新的数据文件,将备份数据复制到新的数据文件目录下,赋权重启
增量备份
innobackupex --user=back --password=123445 --socket=/tmp/mysql_test.sock --defaults-file= /tmp/my.cnf --incremental /data/backup/hotbackup/incremental_one --incremental-basedir=/data/backup/hotbackup/base --no-timestamp --paraller=2
innobackupex --user=back --password=123445 --socket=/tmp/mysql_test.sock --defaults-file= /tmp/my.cnf --incremental /data/backup/hotbackup/incremental_two --incremental-basedir=/data/backup/hotbackup/incremental_one --no-timestamp --paraller=2
增量备份恢复
1、恢复基础备份(全备)
2、恢复增量备份到基础备份(开始恢复的增量备份要添加 --redo-only,最后一次增量备份去掉--redo-only参数)
3、对整体的基础备份进行恢复,回滚那些未提交的数据。
innobackupex --apply-log --redo-only --use-memory=20G /data/backup/hotbackup/base
将增量备份incremental_one 应用到基础备份base
innobackupex --apply-log --redo-only --use-memory=20G /data/backup/hotbackup/base
--incremental-dir=/data/backup/hotbackup/incremental_one/
将增量备份incremental_two应用到基础备份base
innobackupex --apply-log --use-memory=20G /data/backup/hotbackup/base
--incremental-dir=/data/backup/hotbackup/incremental_two/
把所有合在一起的基础备份整体进行一次apply操作,回滚未提交的数据
innobackupex --apply-log --use-memory=20G /data/backup/hotbackup/base
把恢复完的备份复制到数据文件目录中,赋权,然后重启mysql数据库
--slave-infor 会将master的binary log的文件名和偏移位置保存到xtrabackup_slave_info文件中
--safe-slave-backup则会暂停slave的SQL线程,直到没有打开的临时表时候开始备份,待备份结束后SQL线程会自动启动,这样操作的目的主要是确保一致性的复制状态。
innobackupex --user=back --password=123456 --socket=/tmp/mysql_test.sock --defaults-file=/tmp/my.cnf --slave-info --safe-slave-backup /data/backup/hotback/cloneslave --notimestamp --parallel=2
在slave的主机上进行还原
innobackupex --apply-log --redo-only --use-memory=20G /data/backup/hotbackup/cloneslave
将还原的文件复制到新从库newslave上
在主机上添加对主机newslave的授权
grant replication slave on *.* to 'repl'@'salve2' identified by 123456
在主机newslave上拷贝主机slave的my.cnf文件,并且修改server-id参数,修改完毕后启动新的从库newslave
查找主机slave备份后生成的xtrabackup_slave_info文件 提取其中的master_log_file和master_log_pos然后在新的从库newslave上进行 change master to
change master to
master_host='master'
master_user='rep1'
master_password='1233455'
master_log_file='mysql-bin.0000004'
master_log_pos=12343
启动从库 start slave
导出表
select * from table inot outfile '/tmp/emp.txt' fields terminated by ',' (字段分隔符,默认制表符\t)(optinal 只加在char varchar text字符型字段上) enclosed by '"'(字段引用符 默认不使用引用符) escaped by '\'(转义字符默认\) lines starting by ''(每行前都加此字符串) terminated by '\b' (行结束符)
如果在目标目录下有重名文件,则不会创建成功。源文件不能被自动覆盖
mysqldump导出数据为文本
mysqldump -uroot -T target_dir dbname tablename [option]
--fields-terminated-by =name 字段分隔符
--fields-enclosed-by =''字段引用符
--fields-optionally-enclosed-by= '字段引用符,只有在char varchar text字段上'
--fields-escapted-by =转义字符
--fields-terminated-by=记录结束符
导入表
load data [local] infile 'filename' into table tablename[option]
load data infoe '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"';
ignore 3 lines(忽略前3行) (id,content,name 只想加载部分列)
set id=id+10(在id的内容后加上10)
mysqlimport -uroot -p [local] dbname order_tab.txt []
mysqlimport -uroot test /tmp/emp.txt --fields-terminated-by='' --fields-enclosed-by=''
如果导入和导出是跨平台操作,要注意设置参数line-terminated-by,windows上设置line-terminated-by='\r\n' linux上设置为line-terminated-by='\n';