f Percona MySQL xtrabackup Hot Backup ~ 迪貝之家

Pages

Percona MySQL xtrabackup Hot Backup

 

備份帳號及權限設定

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cr%T';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

備份指令來囉~~
root@osboxes:~# xtrabackup --user=bkpuser --password=s3cr%T --backup --target-dir=/backup

它的output 
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --user=bkpuser --password=* --backup=1 --target-dir=/backup
xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7)
201210 02:04:23 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'bkpuser' (using password: YES).
201210 02:04:23 version_check Connected to MySQL server
201210 02:04:23 version_check Executing a version check against the server...
# A software update is available:
201210 02:04:24 version_check Done.
201210 02:04:24 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: not set, socket: not set
Using server version 8.0.21-12
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:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
Number of pools: 1
201210 02:04:25 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: not set, socket: not set
xtrabackup: Redo Log Archiving is not set up.
201210 02:04:25 >> log scanned up to (17992213)
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Completed space ID check of 2 files.
Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
201210 02:04:26 Finished backing up non-InnoDB tables and files
201210 02:04:26 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
201210 02:04:26 Selecting LSN and binary log position from p_s.log_status
201210 02:04:26 [00] Copying /var/lib/mysql/binlog.000010 to /backup/binlog.000010 up to position 156
201210 02:04:26 [00] ...done
201210 02:04:26 [00] Writing /backup/binlog.index
201210 02:04:26 [00] ...done
201210 02:04:26 [00] Writing /backup/xtrabackup_binlog_info
201210 02:04:26 [00] ...done
201210 02:04:26 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '17992213'
xtrabackup: Stopping log copying thread at LSN 17992223.
201210 02:04:26 >> log scanned up to (17992233)
Starting to parse redo log at lsn = 17992213
201210 02:04:27 All tables unlocked
201210 02:04:27 [00] Copying ib_buffer_pool to /backup/ib_buffer_pool
201210 02:04:27 [00] ...done
201210 02:04:27 Backup created in directory '/backup/'
MySQL binlog position: filename 'binlog.000010', position '156'
201210 02:04:27 [00] Writing /backup/backup-my.cnf
201210 02:04:27 [00] ...done
201210 02:04:27 [00] Writing /backup/xtrabackup_info
201210 02:04:27 [00] ...done
xtrabackup: Transaction log of lsn (17992213) to (17992243) was copied.
201210 02:04:28 completed OK!

繼續研究,待驗證是否可以restore......
--------------------------------------------------------
它的備份還原觀念很特殊
分三步驟
1. 備份
2. 讓備份image consistent
這一段應該是在做apply log的動作
3.把上述已consistent的備份copy回原datadir
好特殊的作法

拿剛才的備份為例好了
1.停掉既有的mysql instance
service mysql stop
2.砍掉資料庫
mv /var/lib/mysql /var/lib/mysql.bak
3. backup consistent
xtrabackup --user=bkpuser --password=s3cr%T --prepare --target-dir=/backup
非常有趣
4.把備份copy回原datadir
mkdir -p /var/lib/mysql
xtrabackup --user=bkpuser --password=s3cr%T --copy-back --target-dir=/backup
5.恢復權限,不然mysql 開不起來
chown -R mysql:mysql /var/lib/mysql
service mysql start

是真的開得起來喔.......
找機會來測試寫入當下的備份作業,是否能夠進行還原