Backup và Restore MySQL bản Community với mysqlbackup

1. Giới thiệu

MySQL Community có 1 hạn chế là chỉ có 1 công cụ backup là mysqldump.

Công cụ này thì chỉ backup database ở cấp độ logical. Hiểu nôm na là backup database ra thành 1 file có nội dung là các câu lệnh SQL để khôi phục lại nguyên trạng database. Nó không có các tùy chọn backup như incremental hay apply binlog.

Tuy nhiên, ở phiên bản MySQL Enterprise lại có công cụ mysqlbackup khá ổn, có thể đáp ứng các nhu cầu backup database của DBA. Thật may là công cụ này cho phép download và sử dụng được cho bản Community.

Ở bài viết này mình sẽ hướng dẫn các bạn sử dụng công cụ này nhé.

Đọc thêm Cài đặt MySQL 8 trên CentOS Linux 7 bằng rpm

2. Download Mysqlbackup

Đầu tiên bạn vào trang web sau:

https://edelivery.oracle.com

Đâu là trang web chính thức, lưu các phần mềm của Oracle. Bạn có thể đăng ký 1 tài khoản miễn phí tại đây.

Sau đó bạn đăng nhập vào edelivery. Nhập vào từ khóa MySQL Enterprise Backup và ấn vào kết quả tìm kiếm như hình bên dưới và làm theo các bước mà trang web yêu cầu.

Bạn hãy download file như ảnh bên trên về máy tính của mình.

Sau đó, bạn upload file lên server, ta sẽ được kết quả như sau:

3. Cài đặt Mysqlbackup

1. Giải nén file:

# unzip V1018020-01.zip

Archive: V1018020-01.zip
extracting: mysql-commercial-backup-8.0.27-1.1.el7.x86_64.rpm
extracting: README.txt

2. Cài đặt

rpm -Uvh mysql-commercial-backup-8.0.27-1.1.el7.x86_64.rpm
warning: mysql-commercial-backup-8.0.27-1.1.el7.x86_64.rpm: Header V3 DSA/SHA256 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-commercial-backup-8.0.27-1.################################# [100%]
[root@oracle12c ~]#

4. Backup MySQL với Mysqlbackup

1. Backup Full

mysqlbackup --port=3306 --protocol=tcp --user=root --password=mat_khau --with-timestamp --backup-dir=/backup/full backup

Ý nghĩa các tham số:

–port: Chỉ ra port mà MySQL đang sử dụng.

–protocol: Chỉ ra giao thức mà MySQL đang sử dụng.

–user: User sử dụng để backup (có thể là root)

–password: Mật khẩu của user backup.

–backup-dir: Chỉ ra đường dẫn đến thư mục lưu bản backup

–with-timestamp: Tạo ra thư mục lưu bản backup với tên là thời gian bản backup được tạo ra

Kết quả như sau:

MySQL Enterprise Backup  Ver 8.0.27-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 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.

Starting with following command line ...
mysqlbackup
--port=3306
--protocol=tcp
--user=root
--password=xxxxxxxxx
--with-timestamp
--backup-dir=/backup/full
backup

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup' run mysqlbackup
           prints "mysqlbackup completed OK!".

211125 17:02:45 MAIN     INFO: Establishing connection to server.
211125 17:02:45 MAIN     INFO: SSL: attempting to use an SSL connection.
211125 17:02:45 MAIN     INFO: MySQL server version is '8.0.27'
211125 17:02:45 MAIN     INFO: MySQL server compile os version is 'Linux'
211125 17:02:45 MAIN     INFO: SSL/TLS version used for connection is TLSv1.2
211125 17:02:45 MAIN     INFO: Got some server configuration information from running server.

211125 17:02:45 MAIN     INFO: Establishing connection to server for locking.
211125 17:02:45 MAIN     INFO: SSL: attempting to use an SSL connection.
211125 17:02:46 MAIN     INFO: Backup directory created: '/backup/2021-11-25_17-02-46'
211125 17:02:46 MAIN     INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
211125 17:02:46 MAIN     INFO: Mysqlbackup component not installed.
211125 17:02:46 MAIN     INFO: MEB logfile created at /backup/2021-11-25_17-02-46/meta/MEB_2021-11-25.17-02-46_backup.log

211125 17:02:46 MAIN     INFO: The MySQL server has no active keyring.
--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                        = /var/lib/mysql/
  innodb_data_home_dir           =
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /var/lib/mysql/
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = /var/lib/mysql/
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                        = /backup/full/2021-11-25_17-02-46/datadir
  innodb_data_home_dir           = /backup/full/2021-11-25_17-02-46/datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /backup/full/2021-11-25_17-02-46/datadir
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = /backup/full/2021-11-25_17-02-46/datadir
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

211125 17:02:46 MAIN     INFO: Unique generated backup id for this is 16378345658365665

211125 17:02:46 MAIN     INFO: Copying the server config file '/var/lib/mysql/auto.cnf'
211125 17:02:46 MAIN     INFO: Creating 14 buffers each of size 16777216.
211125 17:02:46 MAIN     INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
211125 17:02:46 MAIN     INFO: Found checkpoint at lsn 18095012.
211125 17:02:46 MAIN     INFO: Starting log scan from lsn = 18094592 at offset = 18088448 and checkpoint = 18095012 in file /var/lib/mysql/ib_logfile0.
211125 17:02:46 MAIN     INFO: Full Backup operation starts with following threads
                               1 read-threads    6 process-threads    1 write-threads
211125 17:02:46 MAIN     INFO: Starting to copy all innodb files...
211125 17:02:46 RDR1     INFO: Copying /var/lib/mysql/ibdata1.
211125 17:02:46 RLP1     INFO: Starting to parse redo log at lsn = 18094627, whereas checkpoint_lsn = 18095012 and start_lsn = 18094592.
211125 17:02:46 RDR1     INFO: Starting to copy all undo files...
211125 17:02:46 RDR1     INFO: Copying /var/lib/mysql/undo_002.
211125 17:02:46 RDR1     INFO: Copying /var/lib/mysql/undo_001.
211125 17:02:47 RDR1     INFO: Starting to lock instance for backup...
211125 17:02:47 RDR1     INFO: The server instance is locked for backup.
211125 17:02:47 RDR1     INFO: The server instance is unlocked after 0.005 seconds.
211125 17:02:47 RDR1     INFO: Copying /var/lib/mysql/sys/sys_config.ibd.
211125 17:02:47 RDR1     INFO: Copying /var/lib/mysql/mysql/backup_progress.ibd.
211125 17:02:47 RDR1     INFO: Copying /var/lib/mysql/mysql.ibd.
211125 17:02:47 RDR1     INFO: Completing the copy of innodb files.
211125 17:02:47 RDR1     INFO: Requesting a dump of the InnoDB buffer pool
211125 17:02:47 RDR1     INFO: Waiting for the dump of the InnoDB buffer pool to complete
211125 17:02:47 RDR1     INFO: The dump of the InnoDB buffer pool completed
211125 17:02:47 RDR1     INFO: Binary Log Basename: '/var/lib/mysql/binlog'
211125 17:02:47 RDR1     INFO: Binary Log Index:    '/var/lib/mysql/binlog.index'
211125 17:02:47 RDR1     INFO: Relay Channel:      'group_replication_applier'
211125 17:02:47 RDR1     INFO: Relay Log Basename: '/var/lib/mysql/oracle12c-relay-bin-group_replication_applier'
211125 17:02:47 RDR1     INFO: Relay Channel:      'group_replication_recovery'
211125 17:02:47 RDR1     INFO: Relay Log Basename: '/var/lib/mysql/oracle12c-relay-bin-group_replication_recovery'
211125 17:02:47 RDR1     INFO: Starting to copy Binlog files.
211125 17:02:47 RDR1     INFO: Starting to lock instance for backup...
211125 17:02:47 RDR1     INFO: The server instance is locked for backup.
211125 17:02:47 RDR1     INFO: The MySQL server has no active keyring.
211125 17:02:47 RDR1     INFO: Requesting flush of redo log reading after LSN 18165288.
211125 17:02:47 RDR1     INFO: Requesting flush of redo log processing after LSN 18165288.
211125 17:02:47 RDR1     INFO: Completed flush of redo log reading after LSN 18165288.
211125 17:02:47 RDR1     INFO: Completed flush of redo log processing after LSN 18165288.
211125 17:02:47 RDR1     INFO: Starting to read-lock tables...
211125 17:02:47 RDR1     INFO: No tables to read-lock.
211125 17:02:47 RDR1     INFO: Opening backup source directory '/var/lib/mysql'
211125 17:02:47 RDR1     INFO: Starting to copy non-innodb files in subdirs of '/var/lib/mysql'
211125 17:02:47 RDR1     INFO: Copying the database directory 'mysql'
211125 17:02:47 RDR1     INFO: Copying the database directory 'performance_schema'
211125 17:02:47 RDR1     INFO: Copying the database directory 'sys'
211125 17:02:47 RDR1     INFO: Completing the copy of all non-innodb files.
211125 17:02:47 RDR1     INFO: Requesting consistency information...
211125 17:02:47 RDR1     INFO: Locked the consistency point for 2329 microseconds.
211125 17:02:47 RDR1     INFO: Consistency point server_uuid 'c2910cb5-4d9c-11ec-b0d4-0050568ff50b'.
211125 17:02:47 RDR1     INFO: Consistency point gtid_executed ''.
211125 17:02:47 RDR1     INFO: Consistency point binary_log_file 'binlog.000001'.
211125 17:02:47 RDR1     INFO: Consistency point binary_log_position 1147.
211125 17:02:47 RDR1     INFO: Consistency point InnoDB lsn 18166348.
211125 17:02:47 RDR1     INFO: Consistency point InnoDB lsn_checkpoint 18095012.
211125 17:02:47 RDR1     INFO: Requesting completion of redo log copy after LSN 18166348.
211125 17:02:47 RLW1     INFO: A copied database page was modified at 18159572. (This is the highest lsn found on a page)
211125 17:02:47 RLW1     INFO: Scanned log up to lsn 18166348.
211125 17:02:47 RLW1     INFO: Was able to parse the log up to lsn 18166348.
211125 17:02:47 RLR1     INFO: Redo log reader waited 26 times for a total of 130.00 ms for logs to generate.
211125 17:02:47 RLW1     INFO: Copied redo log
                               log_start_lsn    18094592
                               start_checkpoint 18095012
                               start_lsn        18095012
                               last_checkpoint  18095012
                               consistency_lsn  18166348
                               log_end_lsn      18166348
211125 17:02:47 RDR1     INFO: Truncating binary log index '/backup/2021-11-25_17-02-46/datadir/binlog.index' to 16.
211125 17:02:47 RDR1     INFO: Truncating binary log 'binlog.000001' to 1147.
211125 17:02:47 RDR1     INFO: Copying /var/lib/mysql/binlog.000001.
211125 17:02:47 RDR1     INFO: Completed the copy of binlog files...
211125 17:02:47 RDR1     INFO: The server instance is unlocked after 0.193 seconds.
211125 17:02:47 RDR1     INFO: Reading all global variables from the server.
211125 17:02:47 RDR1     INFO: Completed reading of all 625 global variables from the server.
211125 17:02:47 RDR1     INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.27' in '/backup/2021-11-25_17-02-46'.
211125 17:02:47 MAIN     INFO: Full Backup operation completed successfully.
211125 17:02:47 MAIN     INFO: Backup created in directory '/backup/full/2021-11-25_17-02-46'
211125 17:02:47 MAIN     INFO: MySQL binlog position: filename binlog.000001, position 1147

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 18094592
   Last Checkpoint LSN        : 18095012
   End LSN                    : 18166348
-------------------------------------------------------------

mysqlbackup completed OK!

[wp-svg-icons icon=”point-right” wrap=”i”] Backup Incremental

mysqlbackup --defaults-file=/etc/my.cnf --port=3306 --protocol=tcp --user=root --password=mat_khau --incremental \
--incremental-base=dir:/backup/full/2021-10-16_20-01-38/ \
--incremental-backup-dir=/backup/incremental \
backup

Ý nghĩa các tham số:

–defaults-file: Đường dẫn đến file config của MySQL, mặc định là /etc/my.cnf

–incremental-base: Đường dẫn đến bản backup full gần nhất. Bản backup incremental sẽ dựa vào bản backup full này làm cơ sở.

–incremental-backup-dir: Đường dẫn lưu bản backup incremental

Kết quả:

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup' run mysqlbackup
           prints "mysqlbackup completed OK!".

211125 17:34:11 MAIN     INFO: Establishing connection to server.
211125 17:34:11 MAIN     INFO: SSL: attempting to use an SSL connection.
211125 17:34:11 MAIN     INFO: MySQL server version is '8.0.27'
211125 17:34:11 MAIN     INFO: MySQL server compile os version is 'Linux'
211125 17:34:11 MAIN     INFO: SSL/TLS version used for connection is TLSv1.2
211125 17:34:11 MAIN     INFO: Got some server configuration information from running server.

211125 17:34:11 MAIN     INFO: Establishing connection to server for locking.
211125 17:34:11 MAIN     INFO: SSL: attempting to use an SSL connection.
211125 17:34:11 MAIN     INFO: Backup directory exists: '/incremental'
211125 17:34:11 MAIN     INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
211125 17:34:11 MAIN     INFO: Mysqlbackup component not installed.
211125 17:34:11 MAIN     INFO: read_backup_variables_txt_file: '/backup/2021-11-25_17-02-46/meta/backup_variables.txt'
211125 17:34:11 MAIN     INFO: backup variable mysql_version=8.0.27
211125 17:34:11 MAIN     INFO: MySQL server version is '8.0.27'
211125 17:34:11 MAIN     INFO: Restoring ...8.0.27 version
211125 17:34:11 MAIN     INFO: backup variable meb_version=8.0.27
211125 17:34:11 MAIN     INFO: backup variable start_lsn=18094592
211125 17:34:11 MAIN     INFO: backup variable last_checkpoint=18095012
211125 17:34:11 MAIN     INFO: backup variable end_lsn=18166348
211125 17:34:11 MAIN     INFO: backup variable apply_log_done=0
211125 17:34:11 MAIN     INFO: backup variable is_incremental=0
211125 17:34:11 MAIN     INFO: backup variable is_incremental_with_redo_log_only=0
211125 17:34:11 MAIN     INFO: backup variable is_partial=0
211125 17:34:11 MAIN     INFO: backup variable is_compressed=0
211125 17:34:11 MAIN     INFO: backup variable is_skip_binlog=0
211125 17:34:11 MAIN     INFO: backup variable is_skip_relaylog=0
211125 17:34:11 MAIN     INFO: backup variable is_skip_unused_pages=0
211125 17:34:11 MAIN     INFO: backup variable is_onlyinnodb=0
211125 17:34:11 MAIN     INFO: backup variable binlog_position=binlog.000001:1147
211125 17:34:11 MAIN     INFO: backup variable binlog_index=binlog.index
211125 17:34:11 MAIN     INFO: backup variable has_tde_tables=0
211125 17:34:11 MAIN     INFO: backup variable start_time_utc=1637834565767503
211125 17:34:11 MAIN     INFO: backup variable end_time_utc=1637834567686905
211125 17:34:11 MAIN     INFO: backup variable consistency_time_utc=1637834567520491
211125 17:34:11 MAIN     INFO: backup variable mysql_version_comment=MySQL Community Server - GPL
211125 17:34:11 MAIN     INFO: backup variable log_bin_name=binlog
211125 17:34:11 MAIN     INFO: backup variable log_bin_index_name=binlog
211125 17:34:11 MAIN     INFO: backup variable innodb_undo_files_count=2
211125 17:34:11 MAIN     INFO: Using start_lsn=18166348, calculated from backup_variables.txt file of incremental-base backup.
211125 17:34:11 MAIN     INFO: Using "full-scan" algorithm for this incremental backup.
211125 17:34:11 MAIN     INFO: MEB logfile created at /incremental/meta/MEB_2021-11-25.17-34-11_backup.log

211125 17:34:11 MAIN     INFO: The MySQL server has no active keyring.
--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                        = /var/lib/mysql/
  innodb_data_home_dir           =
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /var/lib/mysql/
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = /var/lib/mysql/
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                        = /backup/incremental/datadir
  innodb_data_home_dir           = /backup/incremental/datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /backup/incremental/datadir
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = /backup/incremental/datadir
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

211125 17:34:11 MAIN     INFO: Unique generated backup id for this is 16378364513673451

211125 17:34:11 MAIN     INFO: Copying the server config file '/var/lib/mysql/auto.cnf'
211125 17:34:11 MAIN     INFO: Creating 14 buffers each of size 17301504.
211125 17:34:11 MAIN     INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
211125 17:34:11 MAIN     INFO: Found checkpoint at lsn 18320982.
211125 17:34:11 MAIN     INFO: Starting log scan from lsn = 18320896 at offset = 18314752 and checkpoint = 18320982 in file /var/lib/mysql/ib_logfile0.
211125 17:34:11 MAIN     INFO: Incremental Backup operation starts with following threads
                               1 read-threads    6 process-threads    1 write-threads
211125 17:34:11 MAIN     INFO: Starting to copy all innodb files...
211125 17:34:11 RDR1     INFO: Checking on /var/lib/mysql/ibdata1.
211125 17:34:11 RLP1     INFO: Starting to parse redo log at lsn = 18320922, whereas checkpoint_lsn = 18320982 and start_lsn = 18320896.
211125 17:34:11 RDR1     INFO: Starting to copy all undo files...
211125 17:34:11 RDR1     INFO: Copying /var/lib/mysql/undo_002.
211125 17:34:11 RDR1     INFO: Copying /var/lib/mysql/undo_001.
211125 17:34:11 RDR1     INFO: Starting to lock instance for backup...
211125 17:34:11 RDR1     INFO: The server instance is locked for backup.
211125 17:34:11 RDR1     INFO: The server instance is unlocked after 0.004 seconds.
211125 17:34:11 RDR1     INFO: Checking on /var/lib/mysql/sys/sys_config.ibd.
211125 17:34:11 RDR1     INFO: Checking on /var/lib/mysql/mysql/backup_progress.ibd.
211125 17:34:11 RDR1     INFO: Checking on /var/lib/mysql/mysql/backup_history.ibd.
211125 17:34:11 RDR1     INFO: Checking on /var/lib/mysql/mysql.ibd.
211125 17:34:11 RDR1     INFO: Completing the copy of innodb files.
211125 17:34:11 RDR1     INFO: Requesting a dump of the InnoDB buffer pool
211125 17:34:11 RDR1     INFO: Waiting for the dump of the InnoDB buffer pool to complete
211125 17:34:11 RDR1     INFO: The dump of the InnoDB buffer pool completed
211125 17:34:11 RDR1     INFO: Binary Log Basename: '/var/lib/mysql/binlog'
211125 17:34:11 RDR1     INFO: Binary Log Index:    '/var/lib/mysql/binlog.index'
211125 17:34:11 RDR1     INFO: Relay Channel:      'group_replication_applier'
211125 17:34:11 RDR1     INFO: Relay Log Basename: '/var/lib/mysql/oracle12c-relay-bin-group_replication_applier'
211125 17:34:11 RDR1     INFO: Relay Channel:      'group_replication_recovery'
211125 17:34:11 RDR1     INFO: Relay Log Basename: '/var/lib/mysql/oracle12c-relay-bin-group_replication_recovery'
211125 17:34:11 RDR1     INFO: Starting to copy Binlog files.
211125 17:34:11 RDR1     INFO: Starting to lock instance for backup...
211125 17:34:11 RDR1     INFO: The server instance is locked for backup.
211125 17:34:11 RDR1     INFO: The MySQL server has no active keyring.
211125 17:34:11 RDR1     INFO: Requesting flush of redo log reading after LSN 18327440.
211125 17:34:11 RDR1     INFO: Requesting flush of redo log processing after LSN 18327440.
211125 17:34:11 RDR1     INFO: Completed flush of redo log reading after LSN 18327440.
211125 17:34:11 RDR1     INFO: Completed flush of redo log processing after LSN 18327440.
211125 17:34:11 RDR1     INFO: Starting to read-lock tables...
211125 17:34:11 RDR1     INFO: No tables to read-lock.
211125 17:34:11 RDR1     INFO: Opening backup source directory '/var/lib/mysql'
211125 17:34:11 RDR1     INFO: Starting to copy non-innodb files in subdirs of '/var/lib/mysql'
211125 17:34:11 RDR1     INFO: Copying the database directory 'mysql'
211125 17:34:11 RDR1     INFO: Copying the database directory 'performance_schema'
211125 17:34:11 RDR1     INFO: Copying the database directory 'sys'
211125 17:34:11 RDR1     INFO: Completing the copy of all non-innodb files.
211125 17:34:11 RDR1     INFO: Requesting consistency information...
211125 17:34:11 RDR1     INFO: Locked the consistency point for 697 microseconds.
211125 17:34:11 RDR1     INFO: Consistency point server_uuid 'c2910cb5-4d9c-11ec-b0d4-0050568ff50b'.
211125 17:34:11 RDR1     INFO: Consistency point gtid_executed ''.
211125 17:34:11 RDR1     INFO: Consistency point binary_log_file 'binlog.000001'.
211125 17:34:11 RDR1     INFO: Consistency point binary_log_position 1147.
211125 17:34:11 RDR1     INFO: Consistency point InnoDB lsn 18327921.
211125 17:34:11 RDR1     INFO: Consistency point InnoDB lsn_checkpoint 18320982.
211125 17:34:11 RDR1     INFO: Requesting completion of redo log copy after LSN 18327921.
211125 17:34:11 RLW1     INFO: A copied database page was modified at 18320982. (This is the highest lsn found on a page)
211125 17:34:11 RLW1     INFO: Scanned log up to lsn 18327921.
211125 17:34:11 RLW1     INFO: Was able to parse the log up to lsn 18327921.
211125 17:34:11 RLW1     INFO: Copied redo log
                               log_start_lsn    18320896
                               start_checkpoint 18320982
                               start_lsn        18320982
                               last_checkpoint  18320982
                               consistency_lsn  18327921
                               log_end_lsn      18327921
211125 17:34:11 RLR1     INFO: Redo log reader waited 32 times for a total of 255.00 ms for logs to generate.
211125 17:34:11 RDR1     INFO: Truncating binary log index '/incremental/datadir/binlog.index' to 16.
211125 17:34:11 RDR1     INFO: Truncating binary log 'binlog.000001' to 1147.
211125 17:34:11 RDR1     INFO: Copying /var/lib/mysql/binlog.000001.
211125 17:34:11 RDR1     INFO: Completed the copy of binlog files...
211125 17:34:11 RDR1     INFO: The server instance is unlocked after 0.217 seconds.
211125 17:34:11 RDR1     INFO: Reading all global variables from the server.
211125 17:34:11 RDR1     INFO: Completed reading of all 625 global variables from the server.
211125 17:34:11 RDR1     INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.27' in '/incremental'.
211125 17:34:12 MAIN     INFO: Incremental Backup operation completed successfully.
211125 17:34:12 MAIN     INFO: Backup created in directory '/backup/incremental'
211125 17:34:12 MAIN     INFO: Backup contains changes from lsn 18166349 to lsn 18327921
211125 17:34:12 MAIN     INFO: MySQL binlog position: filename binlog.000001, position 1147

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 18166349
   Last Checkpoint LSN        : 18320982
   End LSN                    : 18327921
-------------------------------------------------------------

mysqlbackup completed OK!

5. Restore MySQL với mysqlbackup

Khi bạn đã có bản backup trong tay, bạn có thể thực hiện các bước sau để restore

Đầu tiên cần giả lập tình huống MySQL bị lỗi đã. Ở đây tôi sẽ giả lập tình huống, thư mục data dir của MySQL bị xóa mất

1. Stop MySQL

systemctl stop mysqld.service

2. Xóa bỏ data dir của MySQL

rm -rf /var/lib/mysql

3. Restore lại:

mysqlbackup --defaults-file=/root/backup/2021-11-16_20-01-38/server-my.cnf --backup-dir=/root/backup/2021-11-16_20-01-38 copy-back-and-apply-log

Do sau khi restore, thư mục data dir sẽ bị thay đổi owner thành root, mà quyền chính xác phải thuộc về user mysql. Do đó chúng ta cần thêm bước sau:

chown -R mysql:mysql /var/lib/mysql

Sau đó mới khởi động MySQL lên

systemctl start mysqld

4. Kiểm tra lại

 systemctl status mysqld

● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2021-11-25 17:43:40 +07; 23s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 29114 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 29140 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─29140 /usr/sbin/mysqld

Nov 25 17:43:37 oracle12c systemd[1]: Starting MySQL Server...
Nov 25 17:43:40 oracle12c systemd[1]: Started MySQL Server.

Bài lab này chỉ là 1 trong các tình huống backup, restore đơn giản nhất. Các bạn hãy tìm hiểu thêm hoặc khi nào có thời gian mình sẽ nghiên cứu tiếp để chia sẻ cùng các bạn nhé.

Hẹn gặp lại các bạn ở các bài viết tiếp theo.

Nguồn: https://dangxuanduy.com/

Hiện tại, tôi có tổ chức đều đặn các khóa học về quản trị Oracle Database, tôi sẽ để thông tin ở đây, để bạn nào quan tâm về lịch học cũng như chương trình học có thể theo dõi nhé.

KHOÁ DÀNH CHO NGƯỜI MỚI

KHÓA HỌC: QUẢN TRỊ ORACLE DATABASE THẬT LÀ ĐƠN GIẢN (ADMIN 1)

CÁC KHOÁ NÂNG CAO:

KHÓA HỌC ORACLE NÂNG CAO: QUẢN TRỊ KIẾN TRÚC MULTITENANT 12c

KHÓA HỌC ORACLE NÂNG CAO: QUẢN TRỊ HỆ THỐNG DATA GUARD

CÁC KHOÁ COMBO:

COMBO 1: ADMIN 1 + MULTITENANT 12c

COMBO 2: ADMIN 1 + DATA GUARD

COMBO 3: ADMIN 1 + MULTITENANT 12c + DATA GUARD

LỊCH HỌC:

Mời bạn xem tại đây: LỊCH HỌC CÁC LỚP ORACLE

ĐĂNG KÝ: