MySQL Database Backup: A Guide with MySQLDumper
The Increasing Importance of Database Backups
The expansion and rising popularity of my blog is a relatively new experience for me. I have consistently admired Aibek and his technical team, noting their preparedness with a readily available backup of MUO in case of any potential issue.
As my own Wordpress blog increases in both content and user engagement, I'm beginning to view my expanding database with increasing concern.
What measures should be taken if the server experiences downtime, a security breach, or a more serious problem? Proactive preparation, with a consistent and accessible backup of all posts and comments, seems essential.
Previous MUO Coverage of Database Backups
The topic of database backups has been addressed by several MUO contributors in the past.
Aibek previously detailed 4 tools available for database backups some time ago.
More recently, Jeffry explored the Updraft Wordpress plugin as a backup solution, while Tim outlined several manual backup methods, including utilizing FTP.
Discovering MySQLDumper
I readily admit to occasionally prioritizing convenience; if a process isn't straightforward, I often postpone it.
Furthermore, I manage MySQL databases that are not associated with Wordpress, meaning a plugin designed specifically for Wordpress would be insufficient.
After some investigation, I discovered a remarkably effective, free MySQL backup utility called MySQLDumper.
This tool provides a comprehensive solution for safeguarding MySQL databases, regardless of their association with Wordpress.
MySQLDumper Installation Process
The process of deploying MySQLDumper is generally straightforward, often simpler than installing many other web applications. Unlike platforms like Wordpress that require a dedicated installation utility, MySQLDumper only necessitates extracting the contents of the Zip archive into a designated subdirectory within your web server's public directory.
Following extraction, the application is accessed via your web browser by navigating to the index.html file within that subdirectory. In this instance, all files were transferred to a folder named "sqlbackup" on the server.
To access the application on a local web server, the URL would be "http://localhost/sqlbackup/". When deploying to a live web hosting account, simply substitute "localhost" with your registered domain name.
Upon accessing the index.html file, the "MySQLDumper Installation" screen will appear. This stage is more accurately described as a configuration process rather than a traditional installation.
Select your preferred language and then proceed by clicking the "Installation" button.
The subsequent steps involve configuring the application's database access credentials. If the intention is to utilize MySQLDumper for backing up all MySQL databases hosted on the server, a user account with appropriate permissions across all tables is required.
This can be achieved by either creating a dedicated user for backup operations on each table or by granting the web application root-level access.
Clicking "Connect to MySQL" initiates a connection attempt to each database on the server. The system will then display the connection status at the bottom of the screen.
The setup is now complete! Selecting Next will redirect you to the main control panel of MySQLDumper.
Leveraging MySQLDumper for Database Backups
The primary dashboard provides a comprehensive overview of your databases, detailing their storage capacity and current utilization levels. Database selection is facilitated through a dropdown menu located in the screen's lower-left corner.
Selecting "Configuration" grants access to a complete listing of all MySQL databases within your system. This section also allows for the customization of SQL commands applied during individual database backup dumps.
Within the configuration area, the Email tab enables the setup of backup file delivery via email. This option is viable for smaller databases and email accounts with sufficient attachment size allowances. However, it often proves impractical for larger, continually expanding databases, such as those powering WordPress installations.
For substantial databases, or when server storage is limited, backups can be transferred via FTP to a remote location. This could be a free online storage service or a dedicated FTP server established on a personal computer. Each backup execution will automatically transmit the file to the designated FTP destination.
To initiate a backup process, simply click "Backup" on the main screen. Ensure the desired database is selected in the lower-left corner before clicking "Start New Backup."
Progress is visually displayed through status bars, indicating the backup's advancement. Details such as table names being backed up, the backup file's size, and the percentage completed for each table and the overall process are shown.
Upon completion, the compressed backup files are stored within the /work/backup/ directory, located inside your MySQLDumper installation folder. This web application also provides rapid database restoration capabilities from any of your stored backups.
Furthermore, it includes a database browser that surpasses the functionality offered by PhpMyAdmin.
A key differentiator for MySQLDumper is its specialization in managing large databases. The tool circumvents PHP script timeout restrictions by dividing the backup into smaller segments. It then recursively invokes the script until the entire backup is finalized, regardless of database size.
Have you had the opportunity to utilize MySQLDumper? What are your impressions? What tools do you prefer for backing up and restoring your MySQL databases? Please share your experiences and insights in the comments below.
