MySQL is a popular open source database management system that I used in the development of various websites, including WordPress, magento, drupal and custom portals. For all the information to store in MySQL database, you want to ensure it is safely backed up for future retrieval, not forgetting, maintaining data security. Sometimes, you will need to backup MySQL database for either due to migration purposes or even due to self-assurance purposes. In this post we have discussed 8 great ways to perfectly backup MySQL databases using a command or with other ready-made tools without getting an error.
Browse by Sections
Backup MySQL database 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
Backup MySQL database 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
Backup MySQL database 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,
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.
Backup MySQL database 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.
So these were command line tools to backup MySQL databases. Let’s see what are quick & automatic backup options are available.
MySQL automatic backup with ottomatik
Ottomatik.io 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
*.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.