Fastest method for taking MySQL Backup and Restore

by

In this blog, you will know the Fastest method for taking MySQL Backup and Restore. Mydumper and Myloader are the utility that allows you to perform an extremely Fastest method for taking MySQL Backup and Restore and reliable multi-threaded MySQL backup and restore. These tools are written in the C programming language.

Mydumper is a tool for fast reliable logical backups. It is an alternative to mysqldump and has many advantages over mysqldump some of which are listed below:

  • A multi-threaded backup tool that makes it a lot faster than mysqldump, as mysqldump is single-threaded. It will be more advantageous if you have very fast storage such as SSDs which can be much better utilized with multiple threads.
  • The tool produces separate files for separate tables instead of one big monolithic file, making it easy to restore single tables. You can even break/chunk the table into multiple files which are super useful for cases where you have very large tables.
  • The tool allows for multi-threaded restores, making restores an order of magnitude faster in comparison to restoring from mysqldump produced backups. This is especially true for large datasets.
  • The Mydumper tool provides inbuilt compression so that the backup files are written in compressed form.
When it comes to backing up and restoring MySQL database, most people usually use the very popular mysqldump. You know that mysqldump is very easy to use for a smaller database, it doesn’t work well with larger databases. It’s very slow for huge databases and very error-prone when used for very big MySQL databases.Fastest method for taking MySQL Backup and Restore

Install mydumper on ubuntu

Contents Collapse
Install mydumper on ubuntu
Install mydumper for other linux:
Download mydumper
Syntax Mydumper:
Syntax Myloader 
# sudo apt-get install mydumper

Install mydumper for other linux:

Install Dependency:
# yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel make

Download mydumper 

https://launchpad.net/mydumper [OR] (Directly here https://launchpad.net/mydumper/0.6/0.6.2/+download/mydumper-0.6.2.tar.gz) 
tar -xvzf mydumper-0.6.2.tar.gz
cd mydumper-0.6.2
cmake .
make

Syntax Mydumper:

mydumper --database=$DB_NAME --host=$DB_HOST --user=$DB_USER --password=$DB_PASS --outputdir=$DB_DUMP --rows=500000 --compress --build-empty-files --threads=2 --compress-protocol

Example: mydumper -u USER -p PASSWORD -o /home/DESTINATION_DIR/DATABASE/ -B DATABASE 

Syntax Myloader

myloader --database=$DB_NAME --directory=$DB_DUMP --queries-per-transaction=50000 --threads=10 --compress-protocol --verbose=3
Example: 
myloader -u USER -p PASSWORD -B DATABASE -d /home/SOURCE_DIR/DATABASE/
End of the tutorial, the Fastest method for taking MySQL Backup and Restore.
Thanks for reading this article, you’ll also like to read below articles.




Related Posts