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