percona軟件介紹
MySQL備份工具
常用的MySQL備份工具
- 效率較低、備份和還原速度慢、鎖表
- 備份過程中,數據插入和更新操作被阻塞
XtraBackup工具
- 備份過程中不鎖庫表,適合生產環境
- 由專業組織Percona提供(改進MySQL分支)
- xtrabackup:C程序,支持InnoDB/XtraDB
- innobackupex:以Perl腳本封裝xtrabackup,還支持MyISAM
安裝percona
libev軟件
提取碼:mrt8
percona軟件包
提取碼:wnmo
libev軟件包可以直接使用rpm命令安裝
percona軟件要到下載軟件的目錄進行yum安裝,解決依賴
[root@host51 ~
]
[root@host51 ~
]
[root@host51 ~
]
/usr/bin/innobackupex
[root@host51 ~
]
/usr/bin/innobackupex //備份innodb、xtrdb、myisam引擎的表
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup //備份innodb、xtrdb引擎的表
/usr/share/doc/percona-xtrabackup-24-2.4.7
/usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/ usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
[root@host51 ~
]
[root@host51 ~
]
innobackupex命令
常用選項含義
| –host | 主機名 |
| –user | 用戶名 |
| –port | 端口號 |
| –password | 密碼 |
| –databases | 數據庫名 |
| –no-timestamp | 不用日期命名備份文件存儲的子目錄名 |
| –redo-only | 日志合并 |
| –apply-log | 準備恢復數據 |
| –copy-log | 拷貝數據 |
| –incremental 目錄名 | 增量備份 |
| –increment-basedir=目錄名 | 增量備份時,制定上一次備份數據存儲的目錄名 |
| –increment-dir=目錄名 | 準備恢復數據時,指定增量備份數據存儲的目錄名 |
| –export | 導出表信息 |
| import | 導入表空間 |
--databases
="庫名" //1個庫
--databases
="庫1 庫2" //多個庫
--databases
="庫1.表" //1張表
[root@host51 ~
]
[root@host51 ~
]
[root@host51 ~
]
[root@host51 ~
]
[root@host51 ~
]
[root@host51 ~
]
innobackupex備份與恢復
完全備份與恢復
應用示例:在host51主機將所有信息備份,在host52主機做完全恢復,在備份的過程中不會對備份的表加鎖,但是要求必須在空庫下進行備份
準備環境:
首先!實驗的所有主機都一定要有percona軟件以及libev軟件
實驗將host51主機的庫還原成初始狀態的庫
創建一個新的用于實驗的庫
創建兩個存儲數據的表,分別瘋狂的寫入數據
在51主機上
mysql
> drop database db1
; drop database db2
; drop database db3
;
mysql
> show databases
;
+--------------------+
| Database
|
+--------------------+
| information_schema
|
| mysql
|
| performance_schema
|
| sys
|
+--------------------+
4 rows
in set (0.00 sec
)
mysql
> create database gamedb
;
mysql
> create table gamedb.a
(id int
);
mysql
> create table gamedb.b
(name char
(10
));
mysql
> insert into gamedb.a values
(10
);
mysql
> insert into gamedb.b values
("bob");
mysql
> select count
(*
) from gamedb.a
;
+----------+
| count
(*
) |
+----------+
| 10
|
+----------+
1 row
in set (0.01 sec
)mysql
> select count
(*
) from gamedb.b
;
+----------+
| count
(*
) |
+----------+
| 11
|
+----------+
1 row
in set (0.00 sec
)mysql
> select * from gamedb.a
;
+------+
| id |
+------+
| 10
|
| 11
|
| 12
|
| 100
|
| 500
|
| 566
|
| 43
|
| 4455
|
| 4457
|
| 3552
|
+------+
10 rows
in set (0.00 sec
)mysql
> select * from gamedb.b
;
+--------+
| name
|
+--------+
| tian
|
| bob
|
| aaa
|
| bbb
|
| ccc
|
| sss
|
| ddd
|
| eee
|
| ffff
|
| gjkgjk
|
| fffghh
|
+--------+
11 rows
in set (0.00 sec
)innobackupex --user root --password 123456 /fullbak
rm -rf /fullbak/
innobackupex --user root --password 123456 /fullbak --no-timestamp
ls /fullbak
[root@host51 ~
]
backup_type
= full-backuped
from_lsn
= 0
to_lsn
= 3010832
last_lsn
= 3010841
compact
= 0
recover_binlog_info
= 0
[root@host51 ~
]在52主機上進行數據恢復,在52主機上也要有兩個軟件包
恢復數據的步驟:
1.關閉mysqld服務
2.刪除數據庫目錄下的所有,恢復數據時要求目錄為空
3.準備恢復數據
4.恢復數據
5.修改當前數據庫目錄的擁有者和所屬組為mysql
6.重啟服務
7.以root用戶登錄數據庫查看數據是否恢復成功
[root@host52 ~
]
[root@host52 ~
]
[root@host52 ~
]
[root@host52 ~
]
backup_type
= full-backuped
from_lsn
= 0
to_lsn
= 3010832
last_lsn
= 3010841
compact
= 0
recover_binlog_info
= 0
[root@host52 ~
]
backup-my.cnf ib_logfile0 performance_schema xtrabackup_checkpoints
gamedb ib_logfile1 sys xtrabackup_info
ib_buffer_pool ibtmp1 xtrabackup_binlog_info xtrabackup_logfile
ibdata1 mysql xtrabackup_binlog_pos_innodb
[root@host52 ~
]
[root@host52 ~
]
backup_type
= full-prepared
from_lsn
= 0
to_lsn
= 3010832
last_lsn
= 3010841
compact
= 0
recover_binlog_info
= 0
[root@host52 ~
]
[root@host52 ~
]
[root@host52 ~
]
gamedb ib_logfile0 mysql xtrabackup_binlog_pos_innodb
ib_buffer_pool ib_logfile1 performance_schema xtrabackup_info
ibdata1 ibtmp1 sys
[root@host52 ~
]
總用量 122920
drwxr-x---. 2 root root 72 2月 19 17:24 gamedb
-rw-r-----. 1 root root 480 2月 19 17:24 ib_buffer_pool
-rw-r-----. 1 root root 12582912 2月 19 17:24 ibdata1
-rw-r-----. 1 root root 50331648 2月 19 17:24 ib_logfile0
-rw-r-----. 1 root root 50331648 2月 19 17:24 ib_logfile1
-rw-r-----. 1 root root 12582912 2月 19 17:24 ibtmp1
drwxr-x---. 2 root root 4096 2月 19 17:24 mysql
drwxr-x---. 2 root root 8192 2月 19 17:24 performance_schema
drwxr-x---. 2 root root 8192 2月 19 17:24 sys
-rw-r-----. 1 root root 17 2月 19 17:24 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root 482 2月 19 17:24 xtrabackup_info
[root@host52 ~
]
[root@host52 ~
]
總用量 122920
drwxr-x---. 2 mysql mysql 72 2月 19 02:46 gamedb
-rw-r-----. 1 mysql mysql 480 2月 19 02:46 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 2月 19 02:46 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月 19 02:46 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月 19 02:46 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月 19 02:46 ibtmp1
drwxr-x---. 2 mysql mysql 4096 2月 19 02:46 mysql
drwxr-x---. 2 mysql mysql 8192 2月 19 02:46 performance_schema
drwxr-x---. 2 mysql mysql 8192 2月 19 02:46 sys
-rw-r-----. 1 mysql mysql 17 2月 19 02:46 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql 482 2月 19 02:46 xtrabackup_info
[root@host52 ~
]
[root@host52 ~
]
mysql
>show databases
;
+--------------------+
| Database
|
+--------------------+
| information_schema
|
| gamedb
|
| mysql
|
| performance_schema
|
| sys
|
+--------------------+
5 rows
in set (0.01 sec
)mysql
>select * from gamedb.a
;
+------+
| id |
+------+
| 10
|
| 11
|
| 12
|
| 100
|
| 500
|
| 566
|
| 43
|
| 4455
|
| 4457
|
| 3552
|
+------+
10 rows
in set (0.07 sec
)mysql
>select * from gamedb.a
;
+--------+
| name
|
+--------+
| tian
|
| bob
|
| aaa
|
| bbb
|
| ccc
|
| sss
|
| ddd
|
| eee
|
| ffff
|
| gjkgjk
|
| fffghh
|
+--------+
11 rows
in set (0.01 sec
)
mysql
> select count
(*
) from gamedb.b
;
+----------+
| count
(*
) |
+----------+
| 11
|
+----------+
1 row
in set (0.00 sec
)mysql
> select count
(*
) from gamedb.a
;
+----------+
| count
(*
) |
+----------+
| 10
|
+----------+
1 row
in set (0.00 sec
)
恢復單張表
- 操作步驟:
1.刪除表空間
2.導出表信息
3.拷貝表信息文件到數據庫目錄下
4.修改表信息文件的所有者以及組用戶為mysql
5.導入表空間, 表空間:存儲數據的表文件(表名.ibd)
6.刪除數據庫目錄下的表信息文件
7.查看表記錄
mysql
> alter table 庫名.表名 discard tablespace
; //刪除表空間
]
]
]
mysql
> alter table 庫名.表名
import tablespace
; //導入表空間
mysql
> select * from 庫名.表名
; //查看表記錄
]
在host52主機上模擬不小心刪除了gamedb庫下的b表,在host51主機上備份還原回數據
[root@host52 ~
]mysql
> select * from gamedb.b
;
+--------+
| name
|
+--------+
| tian
|
| bob
|
| aaa
|
| bbb
|
| ccc
|
| sss
|
| ddd
|
| eee
|
| ffff
|
| gjkgjk
|
| fffghh
|
+--------+
11 rows
in set (0.00 sec
)mysql
> delete from gamedb.b
;
Query OK, 11 rows affected
(0.06 sec
)mysql
> select * from gamedb.b
;
Empty
set (0.00 sec
)mysql
> show tables
;
+------------------+
| Tables_in_gamedb
|
+------------------+
| a
|
| b
|
| c
|
+------------------+
3 rows
in set (0.00 sec
)[root@host52 ~
]
mysql
> delete from gamedb.b
;
Query OK, 11 rows affected
(0.06 sec
)mysql
> select * from gamedb.b
;
Empty
set (0.00 sec
)mysql
> create table gamedb.c
(name char
(10
) ,sex enum
("boy",
"girl"));
Query OK, 0 rows affected
(0.02 sec
)[root@host52 ~
]
a.frm a.ibd b.frm b.ibd c.frm c.ibd db.opt
[root@host52 ~
]
backup-my.cnf ib_logfile0 performance_schema xtrabackup_checkpoints
gamedb ib_logfile1 sys xtrabackup_info
ib_buffer_pool ibtmp1 xtrabackup_binlog_info xtrabackup_logfile
ibdata1 mysql xtrabackup_binlog_pos_innodb
[root@host52 ~
]
mysql
> alter table gamedb.b discard tablespace
;
Query OK, 0 rows affected
(0.00 sec
)mysql
> select * from gamedb.b
;
ERROR 1814
(HY000
): Tablespace has been discarded
for table
'b'[root@host52 ~
]
[root@host52 fullbak
]
backup-my.cnf ib_logfile0 performance_schema xtrabackup_checkpoints
gamedb ib_logfile1 sys xtrabackup_info
ib_buffer_pool ibtmp1 xtrabackup_binlog_info xtrabackup_logfile
ibdata1 mysql xtrabackup_binlog_pos_innodb
[root@host52 fullbak
]
a.frm a.ibd b.frm b.ibd db.opt
[root@host52 fullbak
][root@host52 fullbak
]
a.cfg a.exp a.frm a.ibd b.cfg b.exp b.frm b.ibd db.opt
[root@host52 fullbak
]
auto.cnf ibdata1 ibtmp1 mysql.sock.lock xtrabackup_binlog_pos_innodb
gamedb ib_logfile0 mysql performance_schema xtrabackup_info
ib_buffer_pool ib_logfile1 mysql.sock sys
[root@host52 fullbak
][root@host52 fullbak
]
-rw-r--r--. 1 root root 372 2月 19 18:16 /var/lib/mysql/gamedb/b.cfg
-rw-r-----. 1 root root 16384 2月 19 18:16 /var/lib/mysql/gamedb/b.exp
-rw-r-----. 1 mysql mysql 8560 2月 19 17:24 /var/lib/mysql/gamedb/b.frm
-rw-r-----. 1 root root 98304 2月 19 18:16 /var/lib/mysql/gamedb/b.ibd
[root@host52 fullbak
][root@host52 fullbak
]
-rw-r--r--. 1 mysql mysql 372 2月 19 18:16 /var/lib/mysql/gamedb/b.cfg
-rw-r-----. 1 mysql mysql 16384 2月 19 18:16 /var/lib/mysql/gamedb/b.exp
-rw-r-----. 1 mysql mysql 8560 2月 19 17:24 /var/lib/mysql/gamedb/b.frm
-rw-r-----. 1 mysql mysql 98304 2月 19 18:16 /var/lib/mysql/gamedb/b.ibd
[root@host52 ~
]
mysql
> select * from gamedb.b
;
ERROR 1814
(HY000
): Tablespace has been discarded
for table
'b'mysql
> alter table gamedb.b
import tablespace
;
Query OK, 0 rows affected
(0.11 sec
)mysql
> select * from gamedb.b
;
+--------+
| name
|
+--------+
| tian
|
| bob
|
| aaa
|
| bbb
|
| ccc
|
| sss
|
| ddd
|
| eee
|
| ffff
|
| gjkgjk
|
| fffghh
|
+--------+
11 rows
in set (0.00 sec
)[root@host52 fullbak
]
[root@host52 fullbak
]
數據庫目錄下有.cfg、.frm、.ibd、
.ibd 記錄數據的文件,存儲表記錄
.frm 存儲表結構
.exp、.cfg 存儲數據的備份信息,是二進制文件,是執行innobackupex生成的文件
每張表都一定會有表結構以及表記錄文件
增量備份與恢復
- 1.如何判斷所有的數據里,哪些是新產生的數據?
- 2.從哪里開始備份新產生的數據?
- 原理:
- 每一個備份目錄下都會有一個xtrabackup_checkpoints記錄備份的類型和范圍,innobackupex在執行備份時,會對比完全備份后備份目錄中的last_lsn和當前的last_lsn進行對比,如果數據變大,則是新產生的數據
假如我們每天都會產生新的數據,可以在周一的時候做完全備份,周二至周日可以用mysqldump做增量備份或者差異備份,但mysqldump備份時會鎖表,不建議進行此操作,我們可以用innobackupex做增量備份,不會鎖表,但如果數據丟失,在進行恢復時一定要確保數據庫目錄是空的
模擬實驗環境,在host51上面做完全備份以及增量備份,在host54(192.168.4.54)上面做數據恢復做完全備份:
[root@host51 ~
][root@host51 ~
]
backup-my.cnf ibdata1 sys xtrabackup_info
gamedb mysql xtrabackup_binlog_info xtrabackup_logfile
ib_buffer_pool performance_schema xtrabackup_checkpoints
[root@host51 ~
]
backup_type
= full-backuped
from_lsn
= 0
to_lsn
= 3013326
last_lsn
= 3013335
compact
= 0
recover_binlog_info
= 0
在host51上多寫入些記錄
mysql
> select count
(*
) from gamedb.b
;
+----------+
| count
(*
) |
+----------+
| 11
|
+----------+
1 row
in set (0.00 sec
)mysql
> select count
(*
) from gamedb.a
;
+----------+
| count
(*
) |
+----------+
| 10
|
+----------+
1 row
in set (0.00 sec
)mysql
> insert into gamedb.a values
(113
);
Query OK, 1 row affected
(0.00 sec
)
mysql
> insert into gamedb.b values
("xxxx");
Query OK, 1 row affected
(0.00 sec
)
mysql
> select count
(*
) from gamedb.a
;
+----------+
| count
(*
) |
+----------+
| 20
|
+----------+
1 row
in set (0.00 sec
)mysql
> select count
(*
) from gamedb.b
;
+----------+
| count
(*
) |
+----------+
| 22
|
+----------+
1 row
in set (0.00 sec
)mysql
> show create table gamedb.b \G
*************************** 1. row ***************************Table: b
Create Table: CREATE TABLE
`b` (`name` char
(10
) DEFAULT NULL
) ENGINE
=InnoDB DEFAULT CHARSET
=latin1
1 row
in set (0.00 sec
)在host51上開始做增量備份:
[root@host51 ~
]
[root@host51 ~
]
backup-my.cnf ibdata1 sys xtrabackup_info
gamedb mysql xtrabackup_binlog_info xtrabackup_logfile
ib_buffer_pool performance_schema xtrabackup_checkpoints
[root@host51 ~
]
backup-my.cnf ibdata1.delta performance_schema xtrabackup_checkpoints
gamedb ibdata1.meta sys xtrabackup_info
ib_buffer_pool mysql xtrabackup_binlog_info xtrabackup_logfile
[root@host51 ~
]
backup_type
= full-backuped
from_lsn
= 0
to_lsn
= 3013326
last_lsn
= 3013335
compact
= 0
recover_binlog_info
= 0
[root@host51 ~
]
backup_type
= incremental
from_lsn
= 3013326
to_lsn
= 3025984
last_lsn
= 3025993
compact
= 0
recover_binlog_info
= 0
[root@host51 ~
]
mysql
> select count
(*
) from gamedb.a
;
+----------+
| count
(*
) |
+----------+
| 30
|
+----------+
1 row
in set (0.00 sec
)mysql
> select count
(*
) from gamedb.b
;
+----------+
| count
(*
) |
+----------+
| 33
|
+----------+
1 row
in set (0.00 sec
)[root@host51 ~
]
backup_type
= incremental
from_lsn
= 3013326
to_lsn
= 3025984
last_lsn
= 3025993
compact
= 0
recover_binlog_info
= 0
[root@host51 ~
]
backup_type
= incremental
from_lsn
= 3025984
to_lsn
= 3038542
last_lsn
= 3038551
compact
= 0
recover_binlog_info
= 0增量恢復,在host54上做恢復
此時host54主機擁有allback+onedir+twodir首先停止數據庫服務
清空數據庫目錄
準備恢復數據
合并數據
把數據拷貝到數據庫目錄下
修改文件所有者用戶為mysql
啟動服務
管理員登錄查看數據
[root@host51 ~
]
[root@host51 ~
]
[root@host51 ~
]
[root@host54 ~
]
allbak onedir twodir
[root@host54 ~
]
tcp LISTEN 0 80 :::3306 :::* users:
(("mysqld",pid
=1095,fd
=30
))
[root@host54 ~
][root@host54 ~
][root@host54 ~
][root@host54 ~
][root@host54 ~
]
backup_type
= log-applied
from_lsn
= 0
to_lsn
= 3013326
last_lsn
= 3013335
compact
= 0
recover_binlog_info
= 0
[root@host54 ~
][root@host54 ~
]
backup_type
= log-applied
from_lsn
= 0
to_lsn
= 3025984
last_lsn
= 3025993
compact
= 0
recover_binlog_info
= 0
[root@host54 ~
]
backup_type
= incremental
from_lsn
= 3013326
to_lsn
= 3025984
last_lsn
= 3025993
compact
= 0
recover_binlog_info
= 0
[root@host54 ~
][root@host54 ~
]
backup_type
= log-applied
from_lsn
= 0
to_lsn
= 3038542
last_lsn
= 3038551
compact
= 0
recover_binlog_info
= 0
[root@host54 ~
]
backup_type
= incremental
from_lsn
= 3025984
to_lsn
= 3038542
last_lsn
= 3038551
compact
= 0
recover_binlog_info
= 0
[root@host54 ~
][root@host54 ~
]
gamedb ib_logfile0 performance_schema xtrabackup_info
ib_buffer_pool ib_logfile1 sys
ibdata1 mysql xtrabackup_binlog_pos_innodb
[root@host54 ~
]
總用量 110632
drwxr-x---. 2 root root 72 2月 19 21:33 gamedb
-rw-r-----. 1 root root 587 2月 19 21:33 ib_buffer_pool
-rw-r-----. 1 root root 12582912 2月 19 21:33 ibdata1
-rw-r-----. 1 root root 50331648 2月 19 21:33 ib_logfile0
-rw-r-----. 1 root root 50331648 2月 19 21:33 ib_logfile1
drwxr-x---. 2 root root 4096 2月 19 21:33 mysql
drwxr-x---. 2 root root 8192 2月 19 21:33 performance_schema
drwxr-x---. 2 root root 8192 2月 19 21:33 sys
-rw-r-----. 1 root root 18 2月 19 21:33 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root 532 2月 19 21:33 xtrabackup_info
[root@host54 ~
][root@host54 ~
]
總用量 110632
drwxr-x---. 2 mysql mysql 72 2月 19 21:33 gamedb
-rw-r-----. 1 mysql mysql 587 2月 19 21:33 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 2月 19 21:33 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月 19 21:33 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月 19 21:33 ib_logfile1
drwxr-x---. 2 mysql mysql 4096 2月 19 21:33 mysql
drwxr-x---. 2 mysql mysql 8192 2月 19 21:33 performance_schema
drwxr-x---. 2 mysql mysql 8192 2月 19 21:33 sys
-rw-r-----. 1 mysql mysql 18 2月 19 21:33 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql 532 2月 19 21:33 xtrabackup_info
[root@host54 ~
]
[root@host54 ~
]
mysql
> select count
(*
) from gamedb.a
;
+----------+
| count
(*
) |
+----------+
| 30
|
+----------+
1 row
in set (0.02 sec
)mysql
> select count
(*
) from gamedb.b
;
+----------+
| count
(*
) |
+----------+
| 33
|
+----------+
1 row
in set (0.00 sec
)
總結
以上是生活随笔為你收集整理的DBA(二):percona软件、innobackupex备份与恢复的全部內容,希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯,歡迎將生活随笔推薦給好友。