-
mkdir /mybinlog
-
chown mysql:mysql /mybinlog
-
vim /etc/my.cnf
-
log-bin=/mybinlog/mysql-bin ##二进制日志目录及文件名前缀
-
innodb_file_per_table = 1 ##启用InnoDB表每表一文件,默认所有库使用一个表空间
-
service mysqld start
-
mysql> create database laoguang;
-
mysql> use laoguang;
-
mysql> create table linux (id tinyint auto_increment primary key,name char(10));
-
mysql> insert into linux (name) values ('apache'),('nginx'),('php');
-
mkdir /myback
-
chown -R mysql:mysql /myback
-
mysqldump --all-databases --lock-all-tables --routines --triggers --master-data=2 \
-
--flush-logs > /myback/2012-12-3.19-23.full.sql
-
-
--all-databases 备份所有库
-
--lock-all-tables 为所有表加读锁
-
--routines 存储过程与函数
-
--triggers 触发器
-
--master-data=2 在备份文件中记录当前二进制日志的位置,并且为注释的,1是不注释掉在主从复制中才有意义
-
--flush-logs 日志滚动一次
-
cp /mybinlog/mysql-bin.000001 /myback/2012-12-3.19-23.full.00001
-
rm -rf /data/mydata/*
-
rm -rf
-
/mybinlog/*
-
cd /usr/local/mysql
-
./scripts/mysql_install_db --user=mysql--datadir=/data/mydata
-
rm -rf /mybinlog/* ##因为我们不是全新初始化的,可能会有报错的二进制日志,我们不需要
-
service mysqld start ##启动时会重新生成新的二进制日志的
恢复到备份状态,备份前先关闭对恢复过程的二进制日志记录,因为记录恢复语句是毫无意义的
-
mysql> set global sql_log_bin=0;
-
mysql < /myback/2012-12-3.19-23.full.sql ##如果有账号密码记的-u -h哦
-
打开记录并查看恢复状况
-
mysql> set global sql_log_bin=1;
-
mysql> show databases;
-
mysql> set global sql_log_bin=1;
-
mysql> show databases;
-
mysql> use laoguang;
-
mysql> insert into linux (name) values ('haddop'), ('mysql');
-
mysql> drop table linux;
-
mysql> show master status; ##查看当前所在二进制日志中的位置
-
+------------------+----------+--------------+------------------+
-
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
-
+------------------+----------+--------------+------------------+
-
| mysql-bin.000001 | 9005 | | |
-
+------------------+----------+--------------+------------------+
-
mysql > set global sql_log_bin=0;
-
mysql < /myback/2012-12-3.19-23.full.sql
-
mysqlbinlog /mybinlog/mysql-bin.000001
-
# at 8893
-
#121202 14:14:07 server id 1 end_log_pos 9005 Query thread_id=5exec_time=0error_code=0
-
SET TIMESTAMP=1354428847/*!*/;
-
DROP TABLE `linux` /* generated by server */
-
/*!*/;
-
DELIMITER ;
-
# End of log file
-
mysqlbinlog --stop-position=8893 /mybinlog/mysql-bin.000001 > /tmp/change.sql
-
--start-position 指定从哪开始导出二进制日志
-
--stop-position 指定到哪结束
-
--start-datetime 从哪个时间开始格式如"2005-12-25 11:25:56"
-
--stop-datetime 到哪个时间结束
-
mysql < /tmp/change.sql
-
mysql> select * from linux;
-
mysql> flush tables with read lock;
-
mysql> flush logs;
-
mysql> show master status;
-
+------------------+----------+--------------+------------------+
-
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
-
+------------------+----------+--------------+------------------+
-
| mysql-bin.000002 | 107 | | |
-
+------------------+----------+--------------+------------------+
-
lvcreate -L 200M -n mysql-snap -s -p r /dev/myvg/mydata
-
mysql> unlock tables;
-
mount /dev/myvg/mysql-snap /mnt
-
mkdir /myback/lvm
-
cp -pR /mnt/* /myback/lvm
-
umount /mnt
-
lvremove /dev/myvg/mysql-snap
-
servivce mysqld stop
-
rm -R /data/mydata/*
-
cp -Rp /myback/lvm/* /data/mydata
-
service mysqld start ##如果能正常启动代表没有问题,起不来请看数据目录权限
-
yum install perl-DBD-MySQL
-
rpm -ivh percona-xtrabackup-2.0.3-470.rhel5.i386.rpm
-
mysql> create user 'percona'@'localhost' identified by 'redhat';
-
mysql> revoke all privileges,grant option from 'percona'@'localhost';
-
mysql> grant reload,lock tables,replication client on *.* to 'percona'@'localhost';
-
mysql> flush privileges;
-
innobackupex --host=locahost --user=percona --password=redhat --defaults-file=/usr/local/mysql/my.cnf /myback/
-
service mysqld stop
-
rm -Rf /data/mydata
-
innobackupex --apply-log /myback/2012-12-02_20-06-12/
-
--apply-log 的意义在于把备份时没commit的事务撤销,已经commit的但还在事务日志中的应用到数据库
-
innobackupex --copy-back /myback/2012-12-02_20-06-12/
-
--copy-back数据库恢复,后面跟上备份目录的位置
-
chown -R mysql:mysql /data/mydata
-
service mysqld start ##如果能启动代表恢复正常
-
mysql> insert into linux (name) values ('tomcat'), ('memcache'), ('varnish');
-
innobackupex --user=percona--password=redhat --incremental \
-
--incremental-basedir=/myback/2012-12-02_20-06-12/ /myback/
-
-
--incremental 指定是增量备份
-
--incremental-basedir 指定基于哪个备份做增量备份,最后是增量备份保存的目录
-
service mysqld stop
-
rm -Rf /data/mydata/*
-
innobackupex --apply-log --redo-only /myback/2012-12-02_20-06-12/
-
-
--redo-only 指的是把备份时commit的但还在事务日志中的应用到时数据,但是还没提交的不撤消,
-
因为这个事务可能在增量备份中提交,假如的撤消了增量备份中就提交不,因为事务已经不完整
-
innobackupex --apply-log /myback/2012-12-02_20-06-12/ \
-
--incremental-dir=/myback/2012-12-02_20-28-49/
-
-
/myback/2012-12-02_20-06-12/ 这个是完整备份的目录
-
--incremental-dir 后跟的是增量备份的目录
-
这个会使增量备份中的的数据合并到完整备份中,如果还有增量备份,继续合并,恢复时恢复完整备份即可
-
innobackupex --copy-back /myback/2012-12-02_20-06-12/
-
chown -R mysql:mysql /data/mydata
-
service mysqld start