Improved Backup Progress in MySQL Enterprise Backup(MEB) 3.8.2

Nowadays huge amount of data is stored in databases and it is very inevitable for business needs. With parallel backup, MEB has best in class performance and continues to improve. With terabytes of data, it can take hours to backup the data. As a DBA you'd like to know how much of the backup is done, how much data in MB still remains and also what's currently happening in backup.

So MEB 3.8.2 has an enhanced progress indicator which will help the DBA in many ways like

  • You can analyze current and expected space requirements so that you can release some space by removing files on a file system or stop the backup if the space isn't there,to try other options like compressed backup.
  • You can continuously know what's currently going on in backup with current status,that helps DBA to debug/analyze if anything unexpected has happened(if backup is blocked waiting for locks then, state: Waiting for locks,Hint: check who has the locks and release them so that MEB resumes the backup).
  • If backup failed then last progress report message will tell you what was the last operation which is failed or caused the issue.
  • By getting to know time taken and space required, you can plan better next tasks related to this backup or tasks depend on it.

Backup Progress Reporting in earlier MEB versions:

Progress indicator would display the progress in the form of the amount of data(in MB) copied, for every 200MB data during backup.

Example: 130605 17:59:59 mysqlbackup: INFO: Copying /56db/largedb/t4.ibd(Antelope file format).

   mysqlbackup: Progress in MB: 200 400 600 800

130605 18:00:08 mysqlbackup: INFO: Copying /56db/mysql/innodb_index_stats.ibd (Antelope file format).

   mysqlbackup: Progress in MB: 1000 1200

As shown in above example, for every 200MB copied will be shown by appending that number (200,400,600,800,1000,1200 in above case) to already existing progress message (mysqlbackup: Progress in MB:).

Progress Reporting Improvements in MEB 3.8.2:

The progress report is a (short) string that indicates how far the execution of a time-consuming MEB operation has progressed. It consists of one or more "meters" separated by semicolon(';') that measures the progress of the operation. Currently we show Progress, state meters for all MEB operations and compression meter for compressed backup. With new progress report,you will have more information like total estimated size,data processed,current status and will have the flexibility of getting progress messages to many destinations like FIFO,file,table,stdout etc.

Example:

"mysqlbackup: INFO: Progress: 845 of 846 MB; state: Waiting for locks; compression: 92%"

In above progress status report it says compressed backup,845 MB copied of total 846 MB with 92% compression ratio and currently waiting for locks.

You should use “--show-progress” option with/without possible arguments in any of the MEB operations to get these progress reports. There is a great flexibility of getting these progress reports with a wide range of arguments(stderr,stdout,file:FILENAME,fifo:FIFONAME,table,variable) for --show-progress. You can specify different combinations of the arguments to have more progress reporting destinations.

The frequency of the progress reporting is controlled by --progress-interval=SECONDS option regardless of data copied, where SECONDS is a positive integer specifying the number of seconds between successive progress reports. The minimum value is 1 and the default value is 2. Use bigger value for --progress-interval to avoid too many reports in predictable cases and use a smaller value to get more progress reports to analyze if you expect any issues during backup.

NOTE: By default,new progress indicator is 'OFF',so you can still get those old progress messages if you don't use --show-progress option, and those old messages will be turned off if user gives --show-progress. The arguments table,variable for --show-progress works only in case of hot backup as they are related to MySQL server instance.

Using --show-progress option with example :

1. --show-progress=stdout

This specifies mysqlbackup to report progress to stdout

Example:

mysqlbackup -uroot --backup-dir=/backups --compress -- compress-level=5 --show-progress=stdout backup

Output from stdout:

Progress: 0 of 846 MB; state: Copying system tablespace; compression: 92%

Progress: 385 of 846 MB; state: Copying .ibd files; compression: 93%

Progress: 577 of 846 MB; state: Copying .ibd files; compression: 92%

Progress: 769 of 846 MB; state: Copying .ibd files; compression: 93%

Progress: 845 of 846 MB; state: Copying non-InnoDB data; compression: 92%

Progress: 845 of 846 MB; state: Copying non-InnoDB data; compression: 92%

Progress: 846 of 846 MB; state: Completed; compression: 92%

2. --show-progress=file:FILENAME

This specifies mysqlbackup to report progress by updating the given file. Only one line of progress report will be present at any given time

Example:

mysqlbackup -uroot --backup-dir=/backups --show-progress=file:/backups/progress.txt backup

Snippet from file(cat /backups/progress.txt):

Progress: 846 of 846 MB; state: Completed

3. --show-progress=fifo:NAMEDFIFO

This specifies mysqlbackup to report progress to the given file system FIFO

Example:

mysqlbackup -uroot --backup-dir=/backups --show-progress=fifo:/meb/fifo backup | cat /meb/fifo > /meb/showprogfifo.txt

Output of FIFO written to file(cat /meb/showprogfifo.txt):

Progress: 0 of 846 MB; state: Copying system tablespace

Progress: 385 of 846 MB; state: Copying .ibd files

Progress: 577 of 846 MB; state: Copying .ibd files

Progress: 769 of 846 MB; state: Copying .ibd files

Progress: 845 of 846 MB; state: Copying non-InnoDB data

Progress: 845 of 846 MB; state: Copying non-InnoDB data

Progress: 846 of 846 MB; state: Completed

Note : FIFO is created using mkfifo [mkfifo /meb/fifo will create a named pipe].

4. --show-progress=table

This specifies mysqlbackup to report progress by updating the "backup_progress" table of the MySQL instance to be backed up

Example:

mysqlbackup -uroot --backup-dir=/backups --show-progress=table backup

Data from backup_progress table(select tool_name,current_time,current_state from mysql.backup_progress limit 3):

mysqlbackup | 16:51:26 | Progress: 846 of 846 MB; state: Completed|

mysqlbackup | 16:51:26 | Started mysqlbackup.|

mysqlbackup | 16:51:26 | mysqlbackup locking tables and copying .frm + other engines data. |

5. --show-progress

This is equivalent to "--show-progress=stderr",that is report progress to stderror

Example:

mysqlbackup -uroot --backup-dir=/backups --show-progress --show-progress=backup.txt --progress-interval=8 backup

Snippet from stderror:

130604 17:54:03 mysqlbackup: INFO: Progress: 641 of 846 MB; state: Copying .ibd files

130604 17:54:11 mysqlbackup: INFO: Starting to lock all the tables...

130604 17:54:11 mysqlbackup: INFO: Progress: 845 of 846 MB; state: Waiting for locks

130604 17:54:13 mysqlbackup: INFO: All MySQL tables were locked for 1.226 seconds.

Note: Here progress reporting will be done for every 8 seconds as given by --progress-interval option for both stderror(snippet shown above) and also to backup.txt file in present working directory(PWD).
6. --show-progress=variable
This specifies mysqlbackup to report progress by updating the "backup_progress" system variable of the MySQL instance to be backed up. The system variable backup_progress is not yet defined for the MySQL Server. Users need to create their own plugin to define the variable. See The MySQL Plugin API for more information on user plugins.

With the introduction of --show-progress feature in MEB 3.8.2, we will be able to track progress of any MEB operation. This will help us to know space, time required prior to any MEB operation, so that we can plan backup tasks accordingly.

For more details about progress reports please check http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/backup-progress-report-options.html.

For more details about MEB 3.8.2, visit http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/index.html.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

MySQL MEB Team Blog

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
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
   
       
Today