网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
初始化数据库:
mysql> create database xtra_test default charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use xtra_test
Database changed
mysql> create table M(id int,name varchar(10))engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> create table I(id int,name varchar(10))engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into M values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into I values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from M;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+------+------+
rows in set (0.00 sec)
mysql> select * from I;
+------+------+
| id | name |
+------+------+
| 11 | A |
| 22 | B |
| 33 | C |
| 44 | D |
| 55 | E |
+------+------+
rows in set (0.00 sec)
测试备份
root@ha1:~# innobackupex --user=root --password=123456 --host=127.0.0.1 --defaults-file=/etc/my.cnf --databases=xtra_test /home/lgc/xtrabackup/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
11:56:58 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup;host=192.168.200.51' as 'zjy' (using password: YES).
11:56:58 innobackupex: Connected to MySQL server
11:56:58 innobackupex: Executing a version check against the server...
11:56:58 innobackupex: Done.
11:56:58 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using server version 5.5.38-0ubuntu0.12.04.1-log
innobackupex: Created backup directory /home/zhoujy/xtrabackup/2014-11-11_11-56-58
11:56:58 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/etc/mysql/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58 --innodb_log_file_size="5242880" --innodb_data_file_path="ibdata1:10M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --databases='xtra_test'
innobackupex: Waiting for ibbackup (pid=10539) to suspend
innobackupex: Suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_suspended_2'
xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
>> log scanned up to (7363097930)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /home/zhoujy/xtrabackup/2014-11-11_11-56-58/ibdata1
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
[01] ...done
>> log scanned up to (7363097930)
xtrabackup: Creating suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_suspended_2' with pid '10540'
11:57:11 innobackupex: Continuing after ibbackup has suspended
11:57:11 innobackupex: Executing FLUSH TABLES WITH READ LOCK...
11:57:11 innobackupex: All tables locked and flushed to disk
11:57:11 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/db.opt'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.MYI'
>> log scanned up to (7363097930)
innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.frm'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/I.frm'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.MYD'
11:57:11 innobackupex: Finished backing up non-InnoDB tables and files
11:57:11 innobackupex: Executing FLUSH ENGINE LOGS...
11:57:11 innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '7363097930'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (7363097930)
xtrabackup: Creating suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_log_copied' with pid '10540'
xtrabackup: Transaction log of lsn (7363097930) to (7363097930) was copied.
11:57:12 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/zhoujy/xtrabackup/2014-11-11_11-56-58'
innobackupex: MySQL binlog position: filename 'mysql-bin51.000001', position 967
11:57:12 innobackupex: Connection to database server closed
11:57:12 innobackupex: completed OK!
查看备份文件:
ls -lh 2014-11-11_11-56-58/
total 1.2G
-rw-r--r-- 1 root root 188 11月 11 11:56 backup-my.cnf
-rw-r----- 1 root root 1.2G 11月 11 11:57 ibdata1
-rw-r--r-- 1 root root 25 11月 11 11:57 xtrabackup_binlog_info
-rw-r----- 1 root root 95 11月 11 11:57 xtrabackup_checkpoints
-rw-r--r-- 1 root root 666 11月 11 11:57 xtrabackup_info
-rw-r----- 1 root root 2.5K 11月 11 11:57 xtrabackup_logfile
drwxr-xr-x 2 root root 4.0K 11月 11 11:57 xtra_test
/home/zhoujy/xtrabackup/ 备份存放的位置,备份会在该目录下生成一个按照时间命名的文件夹。用**–no-timestamp**参数可以指定到自己想要的备份文件夹,不受时间命名的文件夹限制。
利用 --apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
root@localhost:/home/lgc/xtrabackup# innobackupex --apply-log /home/lgc/xtrabackup/2014-11-11_11-56-58/
应用完之后再查看备份文件看是否有变化:
root@localhost:/home/lgc/xtrabackup# ls -lh 2014-11-11_11-56-58/
total 1.2G
-rw-r--r-- 1 root root 188 11月 11 11:56 backup-my.cnf
-rw-r----- 1 root root 1.2G 11月 11 12:02 ibdata1
-rw-r--r-- 1 root root 5.0M 11月 11 12:02 ib_logfile0
-rw-r--r-- 1 root root 5.0M 11月 11 12:02 ib_logfile1
-rw-r--r-- 1 root root 25 11月 11 11:57 xtrabackup_binlog_info
-rw-r--r-- 1 root root 38 11月 11 12:02 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 95 11月 11 12:02 xtrabackup_checkpoints
-rw-r--r-- 1 root root 666 11月 11 11:57 xtrabackup_info
-rw-r----- 1 root root 2.0M 11月 11 12:02 xtrabackup_logfile
drwxr-xr-x 2 root root 4.0K 11月 11 11:57 xtra_test
看到redo log已经出现。xtrabackup_开头的几个文件记录了一些日志偏移量的信息和日志名和时间等信息。在xtrabackup_checkpoints中记录了备份的模式:
backup_type = full-backuped。
xtrabackup_binlog_info:记录当前最新的LOG Position
xtrabackup_binlog_pos_innodb:innodb log postion
xtrabackup_checkpoints: 存放备份的起始位置beginlsn和结束位置endlsn,增量备份需要这个lsn[增量备份可以在这里面看from和to两个值的变化
xtrabackup_info:备份的一些具体信息日志
增量备份
在做增量备份的时候需要做一次全量备份,只是针对所有库的备份:
root@localhost:/home/lgc/xtrabackup# innobackupex --apply-log /home/lgc/xtrabackup/2014-11-11_11-56-58/
操作数据库,新增数据或删除数据,保证测试有效性。
mysql> insert into X values('dxy');
Query OK, 1 row affected (0.01 sec)
mysql> insert into X values('浙江');
Query OK, 1 row affected (0.01 sec)
![img](https://img-blog.csdnimg.cn/img_convert/cf2222380cc5b4e245093d6a46750e2f.png)
![img](https://img-blog.csdnimg.cn/img_convert/d6d2d360b5267f13c79ca664809970f5.png)
**网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。**
**[需要这份系统化的资料的朋友,可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**
**一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!**
不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。**
**[需要这份系统化的资料的朋友,可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**
**一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!**