Online full backup of MySQL server 5.7 using the MySQL Enterprise Backup

We are going to make an Online full backup of my MySQL server 5.7 using the MySQL Enterprise Backup tool on Oracle Linux 7.

1. Table of Contents:

• What is MySQL Enterprise Backup.
• Enable The Bin Log
• Make a full backup

2.What is MySQL Enterprise Backup.

a. MySQL Enterprise Backup delivers:

• “Hot” Online Backups – Backups take place entirely online, without interrupting MySQL transactions
• Incremental Backup – Backup only data that has changed since the last backup
• Fast Recovery – Get servers back online and create replicated servers
• Point-in-Time Recovery (PITR) – Recover to a specific transaction
• and More.

b. Install MySQL Enterprise Backup:

# rpm -Uvh meb-4.1.0-el7.x86_64.rpm 

2. Enable The Bin Log:

MySQL Entreprise Backup use the binary log files
a. Edit the mysql configuration file: /etc/my.cnf

log-bin=/var/lib/mysql/mysql-bin
server-id=1

b. Restart MySQL server:

# systemctl restart mysqld

3. Make a full backup:

a. Create a destination backup:

# mkdir /backup-mysql/09-05-2019

b. Make an Online full backup:

# /opt/mysql/meb-4.1/bin/mysqlbackup -uroot -pORANUX --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql --backup_dir=/backup-mysql/09-05-2019 backup-and-apply-log

MySQL Enterprise Backup version 4.1.0 Linux-4.1.12-61.1.16.el7uek.x86_64-x86_64 [2017/03/01] 
 Copyright (c) 2003, 2017, Oracle and/or its affiliates. All Rights Reserved.
190509 02:35:54 MAIN    INFO: A thread created with Id '140453410719552' 
 190509 02:35:54 MAIN    INFO: Starting with following command line …
  /opt/mysql/meb-4.1/bin/mysqlbackup -uroot -pxxxxx 
         --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql 
         --backup_dir=/backup-mysql/09-05-2019 backup-and-apply-log 
190509 02:35:54 MAIN    INFO: 
 190509 02:35:54 MAIN    INFO: MySQL server version is '5.7.26-log'
 190509 02:35:54 MAIN    INFO: MySQL server compile os version is 'Linux'
 190509 02:35:54 MAIN    INFO: Got some server configuration information from running server.
190509 02:35:54 MAIN    INFO: Server system variable 'old_alter_table' was set to '0'. Setting it to '1'.
 IMPORTANT: Please check that mysqlbackup run completes successfully.
            At the end of a successful 'backup-and-apply-log' run mysqlbackup
            prints "mysqlbackup completed OK!".
190509 02:35:54 MAIN    INFO: MEB logfile created at /backup-mysql/09-05-2019/meta/MEB_2019-05-09.02-35-54_backup_apply_log.log
                       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        = 0
   innodb_undo_logs               = 128
   innodb_buffer_pool_filename    = ib_buffer_pool
   innodb_page_size               = 16384
   innodb_checksum_algorithm      = crc32
                       Backup Config Options:
datadir                        = /backup-mysql/09-05-2019/datadir
   innodb_data_home_dir           = /backup-mysql/09-05-2019/datadir
   innodb_data_file_path          = ibdata1:12M:autoextend
   innodb_log_group_home_dir      = /backup-mysql/09-05-2019/datadir
   innodb_log_files_in_group      = 2
   innodb_log_file_size           = 50331648
   innodb_undo_directory          = /backup-mysql/09-05-2019/datadir
   innodb_undo_tablespaces        = 0
   innodb_undo_logs               = 128
   innodb_buffer_pool_filename    = ib_buffer_pool
   innodb_page_size               = 16384
   innodb_checksum_algorithm      = crc32
190509 02:35:54 MAIN    INFO: Unique generated backup id for this is 15573657542074496
190509 02:35:54 MAIN    INFO: Creating 14 buffers each of size 16777216.
 190509 02:35:54 MAIN    INFO: Full Backup operation starts with following threads
         1 read-threads    6 process-threads    1 write-threads
 190509 02:35:54 MAIN    INFO: Found checkpoint at lsn 3939235.
 190509 02:35:54 MAIN    INFO: Starting log scan from lsn = 3938816 at offset = 1044480 and checkpoint = 3939235 in file /var/lib/mysql/ib_logfile0.
 190509 02:35:54 MAIN    INFO: System tablespace file format is Antelope.
 190509 02:35:54 MAIN    INFO: Starting to copy all innodb files…
 190509 02:35:54 RDR1    INFO: A thread created with Id '140453026162432' 
 190509 02:35:54 RLW1    INFO: A thread created with Id '140452879660800' 
 190509 02:35:54 RLP1    INFO: A thread created with Id '140452871268096' 
 190509 02:35:54 PCR3    INFO: A thread created with Id '140453000984320' 
 190509 02:35:54 PCR5    INFO: A thread created with Id '140452984198912' 
 190509 02:35:54 PCR4    INFO: A thread created with Id '140452992591616' 
 190509 02:35:54 PCR6    INFO: A thread created with Id '140452975806208' 
 190509 02:35:54 WTR1    INFO: A thread created with Id '140452967413504' 
 190509 02:35:54 PCR1    INFO: A thread created with Id '140453017769728' 
 190509 02:35:54 RLR1    INFO: A thread created with Id '140452959020800' 
 190509 02:35:54 PCR2    INFO: A thread created with Id '140453009377024' 
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/ibdata1 (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/engine_cost.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/gtid_executed.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/help_category.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/help_keyword.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/help_relation.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/help_topic.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/innodb_index_stats.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/innodb_table_stats.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/plugin.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/server_cost.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/servers.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/slave_master_info.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/slave_relay_log_info.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/slave_worker_info.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone_leap_second.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone_name.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone_transition.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone_transition_type.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/sys/sys_config.ibd (Barracuda file format).
 190509 02:35:54 RDR1    INFO: Completing the copy of innodb files.
 190509 02:35:54 RDR1    INFO: Requesting a dump of the InnoDB buffer pool
 190509 02:35:54 RDR1    INFO: Waiting for the dump of the InnoDB buffer pool to complete
 190509 02:35:54 RDR1    INFO: The dump of the InnoDB buffer pool completed
 190509 02:35:54 RDR1    INFO: Starting to copy Binlog files…
 190509 02:35:54 RDR1    INFO: Preparing to lock tables: Connected to mysqld server.
 190509 02:35:54 RDR1    INFO: Starting to lock all the tables…
 190509 02:35:54 RDR1    INFO: Flushing the default MyISAM key cache.
 190509 02:35:54 RDR1    INFO: All tables are locked and flushed to disk
 190509 02:35:54 RDR1    INFO: Copying /var/lib/mysql/mysql-bin.000001.
 190509 02:35:54 RDR1    INFO: Completed the copy of binlog files…
 190509 02:35:54 RDR1    INFO: Opening backup source directory '/var/lib/mysql'
 190509 02:35:54 RDR1    INFO: Starting to backup all non-innodb files in 
     subdirectories of '/var/lib/mysql'
 190509 02:35:54 RDR1    INFO: Copying the database directory 'mysql'
 190509 02:35:54 RDR1    INFO: Copying the database directory 'performance_schema'
 190509 02:35:55 RDR1    INFO: Copying the database directory 'sys'
 190509 02:35:55 RDR1    INFO: Copying the database directory 'test'
 190509 02:35:55 RDR1    INFO: Copying the database directory 'world_innodb'
 190509 02:35:55 RDR1    INFO: Completing the copy of all non-innodb files.
 190509 02:35:55 RDR1    INFO: Requesting completion of redo log copy. Rapid: 0
 190509 02:35:55 RLR1    INFO: Signaled '1' to complete log copy.
 190509 02:35:55 RLR1    INFO: Signaled '1' to complete log copy.
 190509 02:35:55 RLR1    INFO: Signaled '1' to complete log copy.
 190509 02:35:55 RLR1    INFO: Redo log reader waited = 630.00 ms for logs to generate.
 190509 02:35:55 RLW1    INFO: A copied database page was modified at 3939235.
           (This is the highest lsn found on page)
           Scanned log up to lsn 3939244.
           Was able to parse the log up to lsn 3939244.
 190509 02:35:56 RDR1    INFO: All tables unlocked
 190509 02:35:56 RDR1    INFO: All MySQL tables were locked for 1.521 seconds.
 190509 02:35:56 RDR1    INFO: Setting server system variable 'old_alter_table' back to '0'.
 190509 02:35:56 RDR1    INFO: Reading all global variables from the server.
 190509 02:35:56 RDR1    INFO: Completed reading of all global variables from the server.
 190509 02:35:56 RDR1    INFO: Writing config file for server '5.7.26-log'.
 190509 02:35:56 RDR1    INFO: Creating server config files server-my.cnf and server-all.cnf in /backup-mysql/09-05-2019
 190509 02:35:56 MAIN    INFO: Full Backup operation completed successfully.
 190509 02:35:56 MAIN    INFO: Backup created in directory '/backup-mysql/09-05-2019'
 190509 02:35:56 MAIN    INFO: MySQL binlog position: filename mysql-bin.000001, position 154
   Parameters Summary         
Start LSN                  : 3938816
End LSN                    : 3939244
190509 02:35:56 MAIN    INFO: Creating 14 buffers each of size 65536.
 190509 02:35:56 MAIN    INFO: Apply-log operation starts with following threads
         1 read-threads    1 process-threads    6 apply-threads
 190509 02:35:56 MAIN    INFO: Using up to 100 MB of memory.
 190509 02:35:56 MAIN    INFO: ibbackup_logfile's creation parameters:
           start lsn 3938816, end lsn 3939244,
           start checkpoint 3939235.
 190509 02:35:56 ALW4    INFO: A thread created with Id '140452967413504' 
 190509 02:35:56 ALW1    INFO: A thread created with Id '140452871268096' 
 190509 02:35:56 ALW5    INFO: A thread created with Id '140453279201024' 
 190509 02:35:56 PCR1    INFO: A thread created with Id '140453254022912' 
 190509 02:35:56 ALW3    INFO: A thread created with Id '140452959020800' 
 190509 02:35:56 ALW2    INFO: A thread created with Id '140452879660800' 
 190509 02:35:56 ALW6    INFO: A thread created with Id '140453270808320' 
 190509 02:35:56 RDR1    INFO: A thread created with Id '140453262415616' 
 190509 02:35:56 PCR1    INFO: InnoDB: Doing recovery: scanned up to log sequence number 3939244.
 190509 02:35:56 PCR1    INFO: InnoDB: Starting an apply batch of log records to the database…
 .
 InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
 190509 02:35:56 PCR1    INFO: InnoDB: Setting log file size to 50331648.
 190509 02:35:56 PCR1    INFO: InnoDB: Setting log file size to 50331648.
 190509 02:35:56 PCR1    INFO: We were able to parse ibbackup_logfile up to
           lsn 3939244.
 190509 02:35:56 PCR1    INFO: Last MySQL binlog file position 0 154, file name mysql-bin.000001
 190509 02:35:56 PCR1    INFO: The first data file is '/backup-mysql/09-05-2019/datadir/ibdata1'
                               and the new created log files are at '/backup-mysql/09-05-2019/datadir'
 190509 02:35:56 MAIN    INFO: Apply-log operation completed successfully.
 190509 02:35:56 MAIN    INFO: Full backup prepared for recovery successfully.
mysqlbackup completed OK!
 

Conclusion:

Making an Online Full backup of MySQL server using the MySQL Enterprise Backup on Oracle Linux 7.

---