mysql 三种灾备方式
转自: http://tchuairen.blog.51cto.com/3848118/1432606/ 目标:1.使用mysqldump实现从逻辑角度完全备份mysql,配合二进制日志备份实现增量备份2.使用lvm照从物理角度实现几乎热备的完全备份,配合二进制日志备份实现增量备份3.使用percona公司的xrabackup实现完全热备份与增量
-
mkdir /home/mybinlog
-
chown mysql:mysql /home/mybinlog
-
vim /etc/my.cnf
-
log-bin=/home/mybinlog/mysql-bin ##二进制日志目录及文件名前缀
-
innodb_file_per_table = 1 ##启用InnoDB表每表一文件,默认所有库使用一个表空间
-
然后启动mysql
-
mysql <~/Desktop/hellodb.sql
-
use hellodb
-
create table study (id tinyint auto_increment primary key,name char(10));
-
insert into study (name) values ('redhat'),('php'),('asp'),('CCNA'),('MSIC'),('CCNP'),('HCSE');
-
mkdir /home/mysqlbackup \\用于存放备份文件
-
mkdir /home/mysqlbackup/mylogbin \\用于存放备份的二进制日志
-
mkdir /home/mysqlbackup/mylogstatus \\用于存放二进制日志起始位置
-
chown -R mysql:mysql /home/mysqlbackup
-
mysqldump -uroot -predhat --single-transaction --master-data=2 --all-databases --routines --events > /home/mysqlbackup/mydb_all_`date +%F`.sql
-
flush logs
-
mysql -uroot -predhat -e 'show master status' > /home/mysqlbackup/mylogstatus/`date +%F-%H`.txt
-
--databases \\指定数据库名
-
--lock-all-tables \\为所有表加读锁,如果使用的是非事务型表,推荐使用这个选项备份。
-
--master-data=2 \\在备份文件中记录当前二进制日志的位置,并且为注释的,1是不注释掉在主从复制中才有意义
-
--single-transaction \\(使得转储的数据为事务开始前的数据)启动一个大事物,并为每张表创建快照,基于此项能实现热备InnoDB表,由此,不需要同时使用--lock-all-tables
-
--routines \\同时,备份存储过程和存储函数
-
--events \\同时,备份事件调度器代码
-
--all-databases \\备份服务器上的所有库
-
create table system(id tinyint auto_increment primary key,Name char(10));
-
insert into system (Name) values ('windows'),('Centos'),('fedora');
-
cat /home/mysqlbackup/mylogstatus/2014-06-30-15.txt
-
show master status;
-
# mysqlbinlog --start-position=107 --stop-position=504 /home/mybinlog/mysql-bin.000007 >/home/mysqlbackup/incremental-`date +%F-%H`.sql
-
mysql -uroot -predhat -e 'show master status' > /home/mysqlbackup/mylogstatus/`date +%F-%H`.txt
-
insert into system (Name) values ('rlel'),('suselinux'),('VMX');
-
create table firm(id tinyint auto_increment primary key ,name char(10))
-
insert into firm (name) values ('baidu'),('huabang'),('sohu');
-
service mysqld stop
-
rm -rf /mydata/data/*
-
./scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
-
# service mysqld start
-
mysql < /home/mysqlbackup/mydb_all_2014-06-30.sql
-
mysql < /home/mysqlbackup/incremental-2014-06-30-16.sql
-
cat /home/mysqlbackup/mylogstatus/2014-06-30-16.txt
-
mysqlbinlog --start-position=504 /home/mybinlog/mysql-bin.000007 >/home/mysqlbackup/huifu-2014-06-30-17.sql
-
mysql -uroot -predhat < /home/mysqlbackup/huifu-2014-06-30-17.sql
-
flush tables with read lock;
-
flush logs;
-
mysql -uroot -p -e 'show master status'>/var/mybackup/logstatus/`date +%F-+%H`.txt
-
lvcreate -L 200M -n mydata-snap -p r -s /dev/mapper/vg0-lv0
-
unlock tables;
-
mkdir /mysnap
-
mount /dev/vg0/mydata-snap /mysnap/
-
cp /mysnap/* /var/mybackup/2014-06-30/ -a
-
umount /mysnap/
-
lvremove /dev/vg0/mydata-snap
-
这样备份就完成了
-
create table uu(id int primary key,yy int);
-
insert into uu (id,yy) values (123,111),(223,121);
-
/etc/init.d/mysqld stop
-
rm -rf /mydata/data/*
-
cp /var/mybackup/2014-06-30/* /mydata/data/ -a
-
service mysqld start
-
cat /var/mybackup/logstatus/2014-06-30-+22.txt
-
mysqlbinlog --start-position=107 /var/mybinlog/mysql-bin.000002 >/var/mybackup/incremental-2014-06-30-23.sql
-
mysql -uroot -predhat < /var/mybackup/incremental-2014-06-30-23.sql
-
yum install percona-xtrabackup-2.1.4-656.rhel6.i686.rpm
-
innobackupex --user=root --password=redhat /var/mybackup/
-
service mysqld stop
-
rm -rf /mydata/data/*
-
innobackupex --apply-log /var/mybackup/2014-07-01_13-49-38/
-
innobackupex --copy-back /var/mybackup/2014-07-01_13-49-38/
-
chown -R mysql.mysql /mydata/
-
service mysqld start
-
innobackupex --user=root --password=redhat /var/mybackup/
-
-
create database redhat;
-
use redhat
-
create table study(id tinyint auto_increment primary key,students char(10),course char(10));
-
insert into study (students,course) values ('tuchao','linux'),('fangchao','bashshell'),('tyz','lvm'),('yujiaqing','auto china');
-
select * from study;
-
innobackupex --user=root --password=redhat --incremental /var/mybackup/incrbak/ --incremental-basedir=/var/mybackup/2014-07-01_14-41-41/
-
--incremental-basedir //这里要指定上一次最近的备份为基准,进行增量备份。
-
cat incrbak/2014-07-01_14-51-28/xtrabackup_checkpoints
-
insert into study (students,course) values ('wujihe','Java'),('qiulin','Ios kaifa');
-
innobackupex --user=root --password=redhat --incremental /var/mybackup/incrbak/ --incremental-basedir=/var/mybackup/incrbak/2014-07-01_14-51-28/
-
cat incrbak/2014-07-01_15-11-57/xtrabackup_checkpoints
-
create table test(id tinyint auto_increment primary key ,name char(10));
-
insert into test (name) values ('aaa'),('bbb'),('redhat'),('hello world'),('centos');
-
select * from test;
-
service mysqld stop
-
rm -rf /mydata/data/*
-
innobackupex --apply-log --redo-only /var/mybackup/2014-07-01_14-41-41/
-
--redo-only //只做提交处理,不做回滚。
-
innobackupex --apply-log --redo-only /var/mybackup/2014-07-01_14-41-41/ --incremental-dir=/var/mybackup/incrbak/2014-07-01_14-51-28/
-
innobackupex --apply-log --redo-only /var/mybackup/2014-07-01_14-41-41/ --incremental-dir=/var/mybackup/incrbak/2014-07-01_15-11-57/
-
cd /var/mybackup/2014-07-01_14-41-41/
-
cat xtrabackup_checkpoints
-
innobackupex --copy-back /var/mybackup/2014-07-01_14-41-41/
-
chown -R mysql.mysql /mydata/
-
service mysqld start
-
cat /var/mybackup/incrbak/2014-07-01_15-11-57/xtrabackup_binlog_info
-
mysqlbinlog --start-position=941 /home/mybinlog/mysql-bin.000009 >/tmp/huifu-15-56.sql
-
source /tmp/huifu-15-56.sql
更多推荐
所有评论(0)