備份帳號及權限設定 |
---|
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 是真的開得起來喔....... 找機會來測試寫入當下的備份作業,是否能夠進行還原 |