Thesqldump vs thesqlpump

backupexportMySQLmysqldump

I just heard about mysqlpump – it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? Are there scenarios where one of them might work better? Is mysqldump going to be phased out for mysqlpump?

Best Answer

Since I happened to be researching this myself, here's a summary of what I found.

According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple threads in parallel to speed up the dumping and that it doesn't share mysqldump's backwards compatibility requirements, which should open the door for further improvements.

However, the post cautions that mysqlpump is "currently unsafe as a general purpose backup replacement" because individual threads may generate their sections of the dump from different states of the database. It doesn't mention any plans to deprecate mysqldump.

Giuseppe Maxia played around with mysqlpump in 2015 and found the dumping speed-up to be fairly small. He appreciated the new ability to select precisely which objects to dump, but noted that loading the dump back in can only be done single-threaded and that (unlike mysqldump) dumps can only be loaded back into databases with the same name.

Related Solutions

Are there performance benefits to turning off archivelogging? Is it wise

The normal answer is: "Use archivelog mode". Unless you are taking cold backups you will not be able to recover from them without the log files archived during the backup.

If you are taking hourly cold backups and are running in noarchivelog mode, you risk losing an hours worth of committed transactions (which you don't mind). The purpose of archiving the logs is so that you can recover all (or nearly all) of the committed transactions after a media failure.

Whichever mode you choose Oracle still writes logs to the online redo log files - in archivelog mode you enable the archiving of those files as they fill up.

Performance benefits will not normally be significant - here is a list of special cases where there is a difference in redo generation.

Seeing as your 'backups' are ZFS snapshots, you do not need log files for restoring the database - reverting to a snapshot will be similar to recovery after a power loss and will use the snapshotted online redo logs to roll forward to a consistent state. In that specific case, and seeing that you do not mind losing recent data, noarchivelog mode may be acceptable to you.

Not having the standby will still increase your recovery time in the case of hardware failure on the primary however - you may wish to consider that.

Mysql – Multiple instance of Mysql realtime backup

"My current mysql backup strategy is replication." Replication cannot be a backup strategy but you can setup backups on slave! I guess you're talking about HA "if master fails then use slave"...

Anyways... MySQL 5.7 comes with Multi-source replication. You can have single slave machine replicating from two different masters.

You may write your own script to loop around CHANGE-MASTER-TO switching masters and replicate.

Idea for you:

while(true) {

change master to master1 from noted co-ordinates
monitor until sec_behind_master=0
   Note coordinates: relay_master_log_file, exec_master_log_pos

change master to master2 from noted co-ordinates
monitor until sec_behind_master=0
   Note coordinates: relay_master_log_file, exec_master_log_pos

}

Finally you can setup backup on this slave. (This is little complex than wht's written below)

You may also have individual slaves for both masters and setup backups on them. You can later push those backup to remote location / network drive/ tape / upload to s3 to retain longer.

master1--> slave1

master2--> slave2

(Consider backing up binary-logs if you want point in time restores.)

For backups you can use traditional mysqldump or mydumper/loader. If your data size is large, it'd be better to go /w physical-backups, follow settingup xtrabackup for mysql with Holland framework.

Best Answer

Related Solutions

Are there performance benefits to turning off archivelogging? Is it wise

Mysql – Multiple instance of Mysql realtime backup

Related Question