Overview
Hot backup with InnODB, without affecting the end users is possible only with the Enterprise edition or Percona using mysqlbackup or Xtrabackup respectively. The mysqlbackup can be downloaded as trial in order to be tested of course. Once the executable is available can be used as follows
[root@mysqlslave ~]# ./mysqlbackup --port=3306 --protocol=tcp --user=root --password=password --with-timestamp --backup-dir=/backup 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. 180211 14:33:35 MAIN INFO: A thread created with Id '139810546444096' 180211 14:33:35 MAIN INFO: Starting with following command line ... ./mysqlbackup --port=3306 --protocol=tcp --user=root --password=xxxxxxxxxxxx --with-timestamp --backup-dir=/backup backup-and-apply-log 180211 14:33:35 MAIN INFO: 180211 14:33:35 MAIN INFO: MySQL server version is '5.7.21-log' 180211 14:33:35 MAIN INFO: MySQL server compile os version is 'Linux' 180211 14:33:35 MAIN INFO: Got some server configuration information from running server. 180211 14:33:35 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!". 180211 14:33:35 MAIN INFO: MEB logfile created at /backup/2018-02-11_14-33-35/meta/MEB_2018-02-11.14-33-35_backup_apply_log.log
Mysqlbackup doesn't do magic :) Datafiles cannot be changed while the backup is being done, that's why any change which is performed is written to the binary log instead to the disk, after the backup is done, the log files are applied with the: “backup-and-apply-log” option.
Once the backup is done, you can see the files below the output:
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 180211 14:33:40 PCR1 INFO: InnoDB: Setting log file size to 50331648. 180211 14:33:41 PCR1 INFO: InnoDB: Setting log file size to 50331648. 180211 14:33:42 PCR1 INFO: We were able to parse ibbackup_logfile up to lsn 2596922. 180211 14:33:42 PCR1 INFO: Last MySQL binlog file position 0 154, file name mysqlslave-bin.000003 180211 14:33:42 PCR1 INFO: The first data file is '/backup/2018-02-11_14-33-35/datadir/ibdata1' and the new created log files are at '/backup/2018-02-11_14-33-35/datadir' 180211 14:33:42 MAIN INFO: Apply-log operation completed successfully. 180211 14:33:42 MAIN INFO: Full backup prepared for recovery successfully. mysqlbackup completed OK! with 60 warnings