多執行個體部署、開機自啟、xtrabackup備份與恢複
一、MySQL多執行個體部署
1 下載下傳二進制包
wget http://https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
[[email protected] ~]# ls
anaconda-ks.cfg mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
2 建立使用者群組并解壓二進制包至/usr/local/下
//建立使用者群組
[[email protected] src]# groupadd -r mysql
[[email protected] src]# useradd -M -s /sbin/nologin -g mysql mysql
解壓軟體至/usr/local/
ar -xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[[email protected] ~]# ls /usr/local/
bin etc games include lib lib64 libexec mysql-5.7.35-linux-glibc2.12-x86_64 sbin share src
[[email protected] ~]# cd /usr/local/
[[email protected] local]# ln -sv mysql-5.7.35-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.35-linux-glibc2.12-x86_64/"
[[email protected] local]# ll
總用量 0
drwxr-xr-x. 2 root root 6 5月 19 2020 bin
drwxr-xr-x. 2 root root 6 5月 19 2020 etc
drwxr-xr-x. 2 root root 6 5月 19 2020 games
drwxr-xr-x. 2 root root 6 5月 19 2020 include
drwxr-xr-x. 2 root root 6 5月 19 2020 lib
drwxr-xr-x. 3 root root 17 8月 27 01:37 lib64
drwxr-xr-x. 2 root root 6 5月 19 2020 libexec
lrwxrwxrwx. 1 root root 36 8月 29 20:53 mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 8月 29 20:44 mysql-5.7.35-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 5月 19 2020 sbin
drwxr-xr-x. 5 root root 49 8月 27 01:37 share
drwxr-xr-x. 2 root root 6 8月 29 20:46 src
[[email protected] local]#
3 修改目錄/usr/local/mysql的屬主屬組
[[email protected] ~]# chown -R mysql.mysql /usr/local/mysql
[[email protected] ~]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 8月 29 20:53 /usr/local/mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/
[[email protected] ~]#
4 添加環境變量
[[email protected] ~]# chown -R mysql.mysql /usr/local/mysql
[[email protected] ~]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 8月 29 20:53 /usr/local/mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/
[[email protected] ~]# ls /usr/local/mysql
bin docs include lib LICENSE man README share support-files
[[email protected] ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
[[email protected] ~]# cat /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[[email protected] ~]# source /etc/profile.d/mysql.sh
[[email protected] ~]#
[[email protected] ~]# which mysql
/usr/local/mysql/bin/mysql
[[email protected] ~]#
5 建立資料存放目錄
[[email protected] opt]# mkdir -p /opt/data/{3306,3307,3308}
[[email protected] opt]# ls /opt/data/
3306 3307 3308
6 初始化資料庫
初始化3306
[[email protected] ]# mysqld --initialize --user mysql --datadir /opt/data/3306
2021-08-29T14:01:45.531940Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T14:01:45.669941Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T14:01:45.697912Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T14:01:45.754188Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a562bead-08d1-11ec-a320-000c29e1a0bf.
2021-08-29T14:01:45.756362Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T14:01:46.418009Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T14:01:46.418045Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T14:01:46.418442Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T14:01:46.576722Z 1 [Note] A temporary password is generated for [email protected]: 7c+Da<sYv)&+
[[email protected] ]# echo '7c+Da<sYv)&+' > 3306_pass
[[email protected] ]#
[[email protected] ]# cat 3306_pass
7c+Da<sYv)&+
[[email protected] ]#
初始化3307
[[email protected] ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2021-08-29T14:03:59.340281Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T14:03:59.484181Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T14:03:59.507460Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T14:03:59.512090Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f51c94c9-08d1-11ec-bf7f-000c29e1a0bf.
2021-08-29T14:03:59.512810Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T14:04:00.047825Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T14:04:00.047860Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T14:04:00.048234Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T14:04:00.190426Z 1 [Note] A temporary password is generated for [email protected]: NhD7s_6H1Sax
[[email protected] ~]# echo 'NhD7s_6H1Sax' > 3307_pass
初始化3308
[[email protected] ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2021-08-29T14:06:18.603231Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T14:06:18.734764Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T14:06:18.760598Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T14:06:18.818730Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 48251658-08d2-11ec-a152-000c29e1a0bf.
2021-08-29T14:06:18.819814Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T14:06:19.505573Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T14:06:19.505601Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T14:06:19.505955Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T14:06:19.530284Z 1 [Note] A temporary password is generated for [email protected]: NKErt?h+>8lz
[[email protected] ~]# echo 'NKErt?h+>8lz' > 3308_pass
[[email protected] ~]# ls
3306_pass 3307_pass 3308_pass
7 安裝perl并檢查是否還有依賴庫沒有安裝
[[email protected] ~]# yum -y install perl
上次中繼資料過期檢查：1:08:48 前，執行于 2021年08月29日 星期日 20時59分52秒。
依賴關系解決。
==========================================================================
軟體包 架構 版本 倉庫 大小
==========================================================================
安裝:
perl x86_64 4:5.26.3-420.el8 appstream 73 k
安裝依賴關系:
dwz x86_64 0.12-10.el8 appstream 109 k
efi-srpm-macros noarch 3-3.el8 appstream 22 k
ghc-srpm-macros noarch 1.4.2-7.el8 appstream 9.3 k
go-srpm-macros noarch 2-17.el8 appstream 13 k
make x86_64 1:4.2.1-10.el8 baseos 498 k
ocaml-srpm-macros noarch 5-4.el8 appstream 9.4 k
openblas-srpm-macros noarch 2-2.el8 appstream 7.9 k
perl-Algorithm-Diff noarch 1.1903-9.el8 baseos 52 k
perl-Archive-Tar noarch 2.30-1.el8 baseos 79 k
perl-Archive-Zip noarch 1.60-3.el8 appstream 108 k
perl-Attribute-Handlers noarch 0.99-420.el8 appstream 89 k
perl-B-Debug noarch 1.26-2.el8 appstream 26 k
perl-CPAN noarch 2.18-397.el8 appstream 554 k
perl-CPAN-Meta noarch 2.150010-396.el8 appstream 191 k
perl-CPAN-Meta-Requirements noarch 2.140-396.el8 appstream 37 k
perl-CPAN-Meta-YAML noarch 0.018-397.el8 appstream 34 k
perl-Carp noarch 1.42-396.el8 baseos 30 k
perl-Compress-Bzip2 x86_64 2.26-6.el8 appstream 72 k
perl-Compress-Raw-Bzip2 x86_64 2.081-1.el8 baseos 40 k
perl-Compress-Raw-Zlib x86_64 2.081-1.el8 baseos 68 k
perl-Config-Perl-V noarch 0.30-1.el8 appstream 22 k
perl-DB_File x86_64 1.842-1.el8 appstream 83 k
perl-Data-Dumper x86_64 2.167-399.el8 baseos 58 k
perl-Data-OptList noarch 0.110-6.el8 appstream 31 k
perl-Data-Section noarch 0.200007-3.el8 appstream 30 k
8 生成配置檔案
[[email protected] ~]# vi /etc/my.cnf
[[email protected] ~]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/3306.sock
pid-file = /opt/date/3306/mysql.pid
log-error = /var/log/mysql_3306.log
[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/3307.sock
pid-file = /opt/date/3307/mysql.pid
log-error = /var/log/mysql_3307.log
[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/3308.sock
pid-file = /opt/date/3308/mysql.pid
log-error = /var/log/mysql_3308.log
[[email protected] ~]#
9 啟動各執行個體
[[email protected] ~]# mysqld_multi start 3336
[[email protected] ~]# ss -anlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 80 :::3307 :::*
LISTEN 0 80 :::3308 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 80 :::3306
10 修改密碼
[[email protected] ~]# ls
3306_pass 3307_pass 3308_pass anaconda-ks.cfg
[[email protected] ~]# cat 3306_pass
tdX-*ey)Y1_a
[[email protected] ~]# mysql -uroot -p'tdX-*ey)Y1_a' -h127.0.0.1 -h3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = password('123')
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> quit
Bye
[[email protected] ~]# cat 3307_pass
NhD7s_6H1Sax
[[email protected] ~]# mysql -uroot -p'NhD7s_6H1Sax' -h127.0.0.1 -p3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = password('123
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> quit
Bye
[[email protected] ~]# cat 3308_pass
NKErt?h+>8lz
[[email protected] ~]# mysql -uroot -p'NKErt?h+>8lz' -h127.0.0.1 -P3308
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = password('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
11 驗證
[[email protected] ~]# mysql -uroot -p123 -h127.0.0.1 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
12 開機自啟動
在你的安裝目錄裡找到mysqld_multi.server檔案。
[[email protected] support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
然後将它複制到/etc/init.d/目錄下：
[[email protected] ~]# ls /etc/init.d/
functions mysqld_multi netconsole network README
在mysqld_multi中的配置添加一個環境變量
export PATH=/usr/local/mysql/bin:$PATH
重新開機reboot，重置環境,開機之後開啟服務
[[email protected] ~]# service mysqld_multi start
[[email protected] ~]# ss - antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 80 :::3307 :::*
LISTEN 0 80 :::3308 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306
最後使用 chkconfig --add mysql_multi設定
chkconfig --list檢視
[[email protected] ~]# chkconfig --list
[[email protected] ~]# chkconfig --list
注：該輸出結果隻顯示 SysV 服務，并不包含
原生 systemd 服務。SysV 配置資料
可能被原生 systemd 配置覆寫。
要列出 systemd 服務，請執行 'systemctl list-unit-files'。
檢視在具體 target 啟用的服務請執行
'systemctl list-dependencies [target]'。
network 0:關 1:關 2:開 3:開 4:開 5:開 6:關
二、xtrabackup備份與恢複
1.1 xtrabackup 介紹
MySQL冷備、mysqldump、MySQL熱拷貝都無法實作對資料庫進行增量備份。在實際生産環境中增量備份是非常實用的，如果資料大于50G或100G，存儲空間足夠的情況下，可以每天進行完整備份，如果每天産生的資料量較大，需要定制資料備份政策。例如每周實用完整備份，周一到周六實用增量備份。而Percona-Xtrabackup就是為了實作增量備份而出現的一款主流備份工具，xtrabakackup有2個工具，分别是xtrabakup、innobakupe。
ercona-xtrabackup是 Percona公司開發的一個用于MySQL資料庫實體熱備的備份工具，支援MySQL、Percona server和MariaDB，開源免費，是目前較為受歡迎的主流備份工具。xtrabackup隻能備份innoDB和xtraDB兩種資料引擎的表，而不能備份MyISAM資料表。
1.2 xtrabackup 優點
- 備份速度快，實體備份可靠
- 備份過程不會打斷正在執行的事務（無需鎖表）
- 能夠基于壓縮等功能節約磁盤空間和流量
- 自動備份校驗
- 還原速度快
- 可以流傳将備份傳輸到另外一台機器上
- 在不增加伺服器負載的情況備份資料
1.3 xtrabackup備份原理
準備和恢複資料階段
過程如圖:
首先應用xtrabackup日志送出事務應用到InnoDB,然後復原未送出事務。
增量備份過程
對于增量備份隻對InnoDB,MyISAM和其它引擎仍然是完整備份的方式,增量備份主要是處理InnoDB中有變更的頁(頁的LSN).LSN資訊在xtrabackup_checkpoints中。
增量應用
恢複過程
流備份過程圖
InnoDB表空間的結構
- innobackupex啟動後，會先fork一個程序，用于啟動xtrabackup，然後等待xtrabackup備份ibd資料檔案；
- xtrabackup在備份innoDB資料是，有2種線程：redo拷貝線程和ibd資料拷貝線程。xtrabackup程序開始執行後，會啟動一個redo拷貝的線程，用于從最新的checkpoint點開始順序拷貝redo.log；
- 再啟動ibd資料拷貝線程，進行拷貝ibd資料。這裡是先啟動redo拷貝線程的。在此階段，innobackupex進行處于等待狀态（等待檔案被建立)
- xtrabackup拷貝完成ibd資料檔案後，會通知innobackupex（通過建立檔案），同時xtrabackup進入等待狀态（redo線程依舊在拷貝redo.log）
- innobackupex收到xtrabackup通知後哦，執行FLUSH TABLES WITH READ LOCK（FTWRL），取得一緻性位點，然後開始備份非InnoDB檔案（如frm、MYD、MYI、CSV、opt、par等格式的檔案），在拷貝非InnoDB檔案的過程當中，資料庫處于全局隻讀狀态。
-
當innobackup拷貝完所有的非InnoDB檔案後，會通知xtrabackup，通知完成後，進入等待狀态；
xtrabackup收到innobackupex備份完成的通知後，會停止redo拷貝線程，然後通知innobackupex，redo.log檔案拷貝完成；
- innobackupex收到redo.log備份完成後，就進行解鎖操作，執行：UNLOCK TABLES；
- 最後innbackupex和xtrabackup程序各自釋放資源，寫備份中繼資料資訊等，innobackupex等xtrabackup子程序結束後退出
四、xtrabackup的安裝部署以及備份恢複實作
1.xtrabackup的安裝
[[email protected] ~]# wget https://repo.percona.com/yum/release/7/RPMS/x86_64/percona-xtrabackup-2.3.10-1.el7.x86_64.rpm
--2021-08-29 23:08:03-- https://repo.percona.com/yum/release/7/RPMS/x86_64/percona-xtrabackup-2.3.10-1.el7.x86_64.rpm
正在解析主機 repo.percona.com (repo.percona.com)... 157.245.119.64, 167.71.118.3, 167.99.233.229
正在連接配接 repo.percona.com (repo.percona.com)|157.245.119.64|:443... 已連接配接。
已發出 HTTP 請求，正在等待回應... 200 OK
長度：5235696 (5.0M) [application/x-redhat-package-manager]
正在儲存至: “percona-xtrabackup-2.3.10-1.el7.x86_64.rpm”
percona-xtrabackup 100%[==============>] 4.99M 36.1KB/s 用時 8m 55s
2021-08-29 23:17:03 (9.56 KB/s) - 已儲存 “percona-xtrabackup-2.3.10-1.el7.x86_64.rpm” [5235696/5235696])
[[email protected] ~]#
2.Xtrabackup中主要包含的兩個工具：
xtrabackup：是用于熱備innodb，xtradb表中資料的工具，不能備份其他類型的表，也不能備份資料表結構；
innobackupex：是将xtrabackup進行封裝的perl腳本，提供了備份myisam表的能力。
常用選項:
一般情況下，在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。“準備”的主要作用正是通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。
| 選項 | 注釋 |
|---|---|
| -host | 指定主機 |
| -user | 指定使用者名 |
| -password | 指定密碼 |
| -port | 指定端口 |
| -databases | 指定資料庫 |
| -incremental | 建立增量備份 |
| -incremental-basedir | 指定包含完全備份的目錄 |
| -incremental-dir | 指定包含增量備份的目錄 |
| -apply-log | 對備份進行預處理操作 |
| –redo-only | 不復原未送出事務 |
| –copy-back | 恢複備份目錄 |
一般情況下，在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。“準備”的主要作用正是通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。
使用innobackupex備份時，其會調用xtrabackup備份所有的InnoDB表，複制所有關于表結構定義的相關檔案(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關檔案，同時還會備份觸發器和資料庫配置資訊相關的檔案，這些檔案會被儲存到一個以時間命名的目錄當中。在備份的同時，innobackupex還會在備份目錄中建立如下檔案：
-
備份類型(如完全或增量)、備份狀态(如是否已經為prepared狀态)和LSN(日志序列号)範圍資訊：每個InnoDB頁(通常為16k大小)，都會包含一個日志序列号，即LSN，LSN是整個資料庫系統的系統版本号，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的。
xtrabackup_binlog_info – mysql伺服器目前正在使用的二進制日志檔案及備份這一刻位置二進制日志時間的位置
-
xtrabackup_binlog_pos_innodb – 二進制日志檔案及用于InnoDB或XtraDB表的二進制日志檔案的目前position
xtrabackup_binary – 備份中用到的xtrabackup的可執行檔案；
backup-my.cnf – 備份指令用到的配置選項資訊：
在使用innobackupex進行備份時，還可以使用–no-timestamp選項來阻止指令自動建立一個以時間命名的目錄：如此一來，innobackupex指令将會建立一個BACKUP-DIR目錄來存儲備份資料。
如果要使用一個最小權限的使用者進行備份，則可基于如下指令建立此類使用者：如果要使用一個最小權限的使用者進行備份，則可基于如下指令建立此類使用者：
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456'; #建立使用者
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser'; #回收此使用者所有權限
mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; #授權重新整理、鎖定表、使用者檢視伺服器狀态
mysql> FLUSH PRIVILEGES; #重新整理授權表
注意：備份時需啟動MySQL,恢複時需關閉MySQL,清空mysql資料目錄且不能重新初始化,恢複資料後應該立即進行一次完全備份
3. 執行個體
全量備份+增量備份
基本文法:innobackupex --user=root(使用者) --password=密碼 /path/to/BACKUP
- –defaults-file=/etc/my.cnf 指定mysql的配置檔案my.cfg，如果指定則必須是第一個參數。
- –defaults-file=/etc/my.cnf 指定mysql的配置檔案my.cfg，如果指定則必須是第一個參數。
建立備份目錄，full為完全備份目錄，inc為增量備份目錄
[[email protected] ~]# mkdir -p /opt/mysqlbackup/{full,inc}
[[email protected] ~]#
進行全量備份并把它放在/opt/mysqlbackup/full/下
[[email protected] ~]# innobackupex --user=root--password=xu1 /opt/mysqlbackup/full/
MySQL binlog position:filename 'mysql-bin.000004', position '107'
210827 21:03:23 [00]Writing backup-my.cnf
210827 23:23:23 [00] ...done
210827 23:23:23 [00]Writing xtrabackup_info
210827 23:23:23 [00] ...done
xtrabackup: Transactionlog of lsn (1595675) to (1595675) was copied.
210827 23:23:24 completedOK!
檢視全量備份的檔案
[[email protected] ~]# ls /opt/mysqlbackup/full
2021-08-27_23-23-48
[[email protected] ~]# ls /opt/mysqlbackup/full/2021-08-27_23-23-48/
backup-my.cnf mysql xtrabackup_binlog_info xtrabackup_info
ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile
備份資料庫的使用者需要具有相應權限
[[email protected] ~]#mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
進行增量之前的準備 檢視日志位置
[[email protected] ~]# cat/opt/mysqlbackup/full/2021-08-27_23-23-52/xtrabackup_binlog_info
mysql-bin.000004 107
登入mysql資料庫，進行操作
[[email protected] ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database abc;
Query OK, 1 row affected(0.00 sec)
mysql> use abc;
Database changed
mysql> create table cai (id int,name char(16) not null);
Query OK, 0 rows affected(0.05 sec)
mysql> insert into cai values(1,'tom');
Query OK, 1 row affected(0.02 sec)
mysql> insert into cai values(2,'jerry');
Query OK, 1 row affected(0.02 sec)
mysql> select * from cai;
+------+----------+
| id | name |
+------+----------+
| 1 | tom |
| 2 | jerry |
+------+----------+
2 rows in set (0.00 sec)
mysql> quit
Bye
增量備份二進制檔案
[[email protected] ~]# mysqlbinlog --start-position=107 /usr/local/mysql/data/mysql-bin.000004> /opt/mysqlbackup/inc/`date +%F`.sql
[[email protected] ~]# ls/opt/mysqlbackup/inc/
2021-08-27.sql
模拟資料庫損壞
[[email protected] ~]# mv /usr/local/mysql/data/* /tmp/
一般情況下，在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處于不一緻狀态。“準備”的主要作用正是通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。
在準備（prepare）過程結束後，InnoDB表資料已經前滾到整個備份結束的點，而不是復原到xtrabackup剛開始時的點。
innobakupex指令的–apply-log選項可用于實作上述功能。如下面的指令：
[[email protected] ~]# innobackupex --apply-log/opt/mysqlbackup/full/2021-08-27_23-33-18/
.........
InnoDB: File './ibtmp1'size is now 12 MB.
InnoDB: 96 redo rollbacksegment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redorollback segment(s) are active.
InnoDB: 5.7.13 started;log sequence number 1595925
xtrabackup: startingshutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimizethread exiting.
InnoDB: Startingshutdown...
InnoDB: Shutdowncompleted; log sequence number 1595944
210827 23:23:42 completedOK!
使用innobakupex指令的 --copy-back 進行拷貝
[[email protected] ~]# innobackupex --copy-back/opt/mysqlbackup/full/2021-08-27_23-23-16/
...........
210827 23:36:07 [01] ...done
210827 21:36:07 [01]Copying ./performance_schema/events_waits_current.frm to/usr/local/mysql/data/performance_schema/events_waits_current.frm
210827 23:35:17 [01] ...done
210827 23:36:24 completedOK!
檢視結果
[[email protected] ~]# ll /usr/local/mysql/data/
總用量 40972
-rw-r-----. 1 root root18874368 8月 27 21:36 ibdata1
-rw-r-----. 1 rootroot 5242880 8月 27 23:36 ib_logfile0
-rw-r-----. 1 rootroot 5242880 8月 2721:36 ib_logfile1
-rw-r-----. 1 root root12582912 8月 27 23:36 ibtmp1
drwxr-x---. 2 rootroot 4096 8月 27 23:36 mysql
drwxr-x---. 2 rootroot 4096 8月 27 23:36 performance_schema
-rw-r-----. 1 rootroot 478 8月 27 23:36 xtrabackup_info
修改屬主屬組
[[email protected] ~]# chown -R mysql:mysql/usr/local/mysql/data/
殺死 掉mysql 的程序
[[email protected] ~]# killall mysqld
重新開機服務
[[email protected] ~]# service mysqld start
Starting MySQL..
登入檢視
[[email protected] ~]# mysql -uroot -p
Enter password:
Welcome to the MySQLmonitor. Commands end with ; or \g.
Your MySQL connection idis 1
Server version: 5.5.22-logSource distribution
Copyright (c) 2000, 2011,Oracle and/or its affiliates. All rights reserved.
Oracle is a registeredtrademark of Oracle Corporation and/or its
affiliates. Other namesmay be trademarks of their respective
owners.
Type 'help;' or '\h' forhelp. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)
我們可以看到在完全備份後建立的資料并沒有恢複，這時就需要用增量備份來還原
為了避免産生大量的二進制日志，可以把二進制日志關掉
mysql> set sql_log_bin=0;
Query OK, 0 rows affected(0.00 sec)
讀取增量備份檔案
mysql> source/opt/mysqlbackup/inc/2023-08-27.sql
Query OK, 0 rows affected(0.00 sec)
Query OK, 0 rows affected(0.00 sec)
Query OK, 0 rows affected(0.00 sec)
Query OK, 0 rows affected (0.00sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected(0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cai |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use benet
Database changed
mysql> select * from cai;
+------+----------+
| id | name |
+------+----------+
| 1 | tom |
| 2 | jerry |
+------+----------+
2 rows in set (0.01 sec)
ry OK, 0 rows affected (0.00sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected(0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cai |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use benet
Database changed
mysql> select * from cai;
+------+----------+
| id | name |
+------+----------+
| 1 | tom |
| 2 | jerry |
+------+----------+
2 rows in set (0.01 sec)