MySQL is a popular open source database management system that I used in the development of interactive websites. For every information to store in MySQL database, you want to ensure it is safely backed up for future retrieval, not forgetting, maintaining data security.

Several methods are available that different businesses adapt to backup their data. However, for the backup process, it helps to master both automatic and manual backups to ensure you are conveniently able to guarantee a smooth and sure backup process.

MySQL backup with MySQLDump

The MySQLDump utility is an executable that comes with MySQL and allows you to specify different options to backup your database to a file, another server or even a compressed gzip file. The mysqldump utility is flexible, fast, not to mention, can perform advanced backups, and accepts a variety of command lines arguments, from which you can rely on to change how you backup your database.

You can find the mysqldump utility on Windows operating system in c:\mysqlbin and on Unix/Linux systems in /usr/local/mysql/bin directory. You can pass different arguments to the mysqldump utility to be used in the form of

mysqldump ---user [user name] ---password= [password]

[database name] > [dump file]

For manual backups, all you need is the password to the MySQL database, then use the p option, for the password, to send a command to the mysqldump. To back up all your databases, use the all databases option, for example

Mysqldump –u USER –p –all-databases> /mnt/backups/all

MySQL backup with MySQLpump

The MySQLpump is similar to the mysqldump utility in the way it also generates logical backups with support for parallelization. The MySQLpump executes all dumping related operations for multiple databases in a parallel manner, which drastically reduces the time spent on performing a logical backup.

However, the MySQLpump does not backup performance_schema, sys schema and ndbinfo, so you must name them with – databases, neither does it dump user account definitions so that you can suppress all database dumping like so:

Shell> mysqlpump –exclude-databases=% --users

MySQL backup with AutoMySQLBackup

AutoMySQLBackup can back up your databases on a server, whether a single, multiple or all the databases you have. Each of the databases is saved in a separate file, which you can compress. All you need is to download a file bash script, save it then customize it to your liking, making it executable.

To run the script manually,

./automysqlbackup.sh.2.5

Since this script does not support saving the backup folders to remote locations, you may need to mount a remote space or use a different technique to upload the files remotely.

MySQL backup with mydumper

Mydumper is a utility software program that guarantees you a fast and reliable multi-threaded MySQL backup process. Mydumper is known to work well with large databases, offering parallelism. You need to create a backup like shown below, then replace the variables ($) with real variables, then you can zipup the folder and place it in a location of preference.

Mydumper \

--database=$DB_NAME \

--host=$DB_HOST \

--user=$DB_PASS \

--rows=500000 \

--compress \

--build-empty-files \

--compress-protocol

Quick & Automatic Backups

MySQL automatic backup with ottomatik

Ottomatik offers a terrific backup solution for your MySQL database using a backup parachute. Ottomatik supports cloud or local server, and by installing the script, it automatically installs your SSH key connecting to your Linux server, and you can also select several servers for your backup jobs.

MySQL backup with PHPMyAdmin

PHPMyAdmin is a popular application used to manage MySQL databases and is free to use. A lot of service providers use PHPMyAdmin; therefore, it is highly likely that you have already installed it.

All you need to do to backup your database is to open the PHPMyAdmin, select the files or folders, then click the Export link. The Save As option and compress option should lead you into a prompt for you to save the data locally.

MySQL backup using a file system snapshot

System snapshots are possible with file systems like Veritas, LVM or ZFS, among others. To use a file system snapshot, execute flush tables with read lock, from a client program, then from another shell, run mount vxfs snapshot. From the first client, unlock tables, then copy the files from the snapshots to a destination folder, and later unmount the snapshot.

Making backups by copying table files

It is possible to backup MyISAM tables by copying table files as *.MYD,*.MYI files. To acquire a consistent backup, stop the server or lock and flush the relevant tables, as in below:

FLUSH TABLES tbl_list WITH READ LOCK;

The read lock allows other clients to proceed with the query tables as you make copies of the files in the database, while the flush is there to ensure that all the active index pages are written to the disk before the backup process.

Whether full or incremental, backups are necessary for your MySQL databases, and as the size of the database increases, you may need to change your backup strategy for adequate results.


Feel free to ask questions, send feedback and even point out mistakes. Great conversations start with just a single word. How to write better comments?
Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

You May Also Like
general minify
Read More

How to optimize speed of your WordPress site for free

Who doesn’t like a fast loading website/blog under his name? But to attain a beautiful yet fast loading webpage is one of the toughest tasks of web development. It costs great effort and money. You buy overpriced web-hosting plans hoping to get a speedy website, but still you feel something is wrong. Your website doesn’t load as fast it was expected. Slow page…
Here’sHowthePayperClickWorksGauravTiwari
Read More

Here’s How the Pay per Click Works

The pay per click is an online advertising tool that helps the advertisers display the ads for their goods or services. When people are searching for certain products or services online, they type in their queries in the search engines. The advertising agency only charges the advertiser if someone clicks on the links that contain important information about the various…

Cloud Computing

Intro The much talked about Cloud Computing promises to change the way Information Technology People look at delivering IT and the way business people perceive it. This one is now a general and important part of IT. Why is “Cloud” connected to Computing?$ The name cloud computing was inspired by the cloud symbol that’s often used to represent the Internet…
l fiszk luca bravo.jpg
Read More

Things you need to know before buying a laptop

Buying a new laptop is a life changing decision. It can turn your life around as it has done for many people. But the first timers overlook the basic requirements and they fail to understand what is necessary to have and what not to. They fall prey to false visuals & unnecessary opinions. They are pulled by the hidden complicity…
Read More

The impact of technology on the fashion industry

In recent times, technology has had a strong influence on the dynamics of the fashion world. With the shift from brick-and-mortar stores to online shopping, e-commerce being a formidable force is a huge example of how technology has been impacting the fashion industry. As in all sectors, technology is revolutionizing the way people perceive and incorporate fashion in their own…
Google PageRank Update
Read More

Google PageRank Updated – We became better

Google PageRank (PR), the most popular base 10 website popularity algorithm, has received an unexpected update on this December 6 –exactly 10 months and 2 days after the last update was released. My blog has regained its PR4 rating, which was gone to PR1 after I upgraded the site-domain from subdomain wpgaurav.wordpress.com to gauravtiwari.org in November 2011. In last PR Update (February…