In previous posts, we covered Oracle and SQL in great detail. In this post, we are going to put on a clean t-shirt, give ourselves a shave (I could use one with all this WFH) and become a little more modern by jumping into MySQL. Much like SQL and Oracle it is common to use native tools such as mysqldump to protect databases. These dumps can target a local file on disk, attached disk or even AWS S3. First, let's chat about how to backup/restore with native tools for MySQL.
Backup and Restore of MySQL with Native Tools (mysqldump)
Protecting databases in MySQL is a common and familiar task to a DBA. The capability is built into MySQL via mysqldumps. The below command will backup"testdb" database to a local file named "dump.sql" in /var/lib/mysql. This directory location is the default location for MySQL database files, but it is common to place database files in a different location or attached disk for larger environments.
Backup database: mysqldump -u [user] -p [database_name] < /var/lib/mysql/dump_file.sqlNow that we have a backup, let's up the stakes and actually drop (delete) the database "testdb." Before we delete it though, take note of the two tables inside "testdb."
Above we can confirm "testdb" no longer exists in our list of databases on the server. There is no need to fear though. Simply recreate the database, point it to the backup file, and all the database contents will be restored.
Create database: create database [database_name]
Restore database: mysql -u [user] -p [database_name] < /var/lib/mysql/dump_file.sql
To prove it worked, we can connect to MySQL and dive into "testdb" to confirm the two tables have been restored.
MySQL dumps are an easy and effective way to ensure databases are protected and easily recoverable, but what if we want to protect more than just the database? What if we want to protect both the server and the database together? Or what if we don't have a dedicated DBA to spend the cycles on this? As you may have guessed by now, Veeam has a solution for this.
Backup and Restore MySQL with Veeam
Veeam Agent for Linux integrates with Oracle, MySQL and PostgreSQL. Today, we are focused on MySQL, but previous posts have focused on Oracle, and PostgreSQL may find its way into future posts. Not only does Veeam protect the server, but also protects the underlying database regardless if the storage engine is MyISAM or InnoDB.
What is MyISAM and InnoDB though? Both are the underlying storage engines for databases that come pre-built with MySQL that serve their own purposes. Choosing one over the other isn't as easy as you might think or hope, but at a high-level it comes down to what MySQL refers to as "Locking." MySQL locks a table (MyISAM) or row (InnoDB) for data integrity purposes during queries on the database. Typically, InnoDB is leveraged for high IO workloads and MyISAM is leveraged for heavy read workloads.
When creating a backup job simply check, "Enable-application-aware processing" and specify credentials with privileges to SELECT and LOCK TABLES (for MyISAM only). If you prefer to create the job via CLI refer to this guide.
And what is the point of backups? To do a restore! Similar to mysqldump we could restore files if a database was dropped, but let's use a more common scenario where you want to clone/refresh the database from a backup. First, we open the Veeam Explorer to search for our database files.
In Veeam Explorer, we can easily find our "testdb" database files in the default database location (/var/lib/mysql). In addition, we can see the tables inside "testdb."
Once you find the directory for the database (testdb) that you want to restore right-click on the folder and choose which restore option you like. If we were trying to restore a dropped database "Overwrite" would be the option to choose. Since we are cloning/refreshing a database copy we are going to "Keep" a copy of the database files.
Below we can see Veeam adds a ".Restored" suffix to the kept restored files, and we can confirm that the underlying database and tables were restored with the directory.
Most likely this isn't what you want your database to be named though. The below command will change the name of the directory and the database to "clonetestdb."
mv /var/lib/mysql/testdb.RESTORED-20200901180536 /var/lib/mysql/clonetestdbNow, you can connect to MySQL and confirm "clonetestdb" is available and see the underlying tables in the database.
In summary, Veeam offers a great solution to protect both the server and the MySQL databases within the server. Protecting both empowers users to not only restore singular databases in the case of an accidental deletion or clone/refresh, but also opens a plethora of options in a true disaster scenario.