Every organization needs a good database back-up plan, to include plans for data restoration. Such a plan should include, of course making proper back-ups and doing in a way that won’t disturb user traffic. It should also include making sure that you’re ready to restore back-ups quickly, so that your organization can operate with minimal downtime and without loss of data when a problem occurs. There are many things such as these to consider for developing a good back-up and restoration plan.
It’s strongly recommended to make a back-up of all of the databases in the same dump. This will allow for a consistent view of the global state of the databases. Plus, a full back-up can be used for provisioning a new replication when needed. A full back-up will save the system tables and stay consistent with multiple database queries and transactions. There are certainly some advantages to making back-ups based on single databases, but a full back-up is preferred. If you’re concerned by the size of the resulting dump file, it’s possible to split a global dump between multiple files. If you search the web, you will find some back-up scripts that will help you to do this.
It is possible to make a so called, incremental backup. This is a back-up in which a full back-up is made not so often—perhaps only once a week—and copy is made of the binary logs every day. You might want to make full back-ups soon depending on the size of the binary logs generated by your database. Otherwise, the restore process will take longer using the binary logs.
The most simple back-up is a blocking back-up. This is a back-up in which all of the databases and their tables are locked for writes during the back-up process—or at least all of the tables when making a back-up of a single database. This is ideal for consistentcy of data between tables, but it can be inconvenient with high-traffic, large databases. For those situations, you may want to make some changes to the types of storage engines you use.
The nature of MariaDB and MySQL allows transactional and non-transactional tables to be shared in the same database. This is a nice feature. However, storage engines like MyISAM require the blocking method, whereas ones like InnoDB don’t. For a good back-up policy, you may want to make some compromises related to storage engines and switch all of your tables to InnoDB or other tables that will allow you to make non-blocking back-ups.
You could store all of your data tables using transactional storage engines such as, InnoDB, XtraDB, and TokuDB. These storage engines can be mixed to make a consistent point-in-time, non-blocking back-ups.
When making a dump using a version prior to 5.3, or when making a physical back-ups, one needed to do a FTWRL (Flush Table With Read Lock ), to be able to flush the server and reset the binary log, before starting a transactional repeatable read snapshot. This generally isn’t good, if you make the back-up on a loaded server. It causes a metadata lock until all of the currently running transactions are finished processing. Note, this may not be a problem if you have only small transactions—even plenty of them. Fortunately, MariaDB 5.3 introduced some new sessions variables that provide non-stalling positioning of the binary logs for every transaction. The utility mysqldump makes good use of such variables to avoid stalling on the master that FTWRL introduced previously.
Another possible back-up method is to perform physical back-ups using tools such as LVM, XtraBackup, TokuDB Hot Back-up. These will greatly improve recovery time as restoration is a matter of moving files around. Compression tends to defeat this strategy, unless you use uncompressed tools like, Pigz. Physical back-ups can be useful to shorten the restoration time. The most widely used is prossibly the LVM snapshot, which uses an excellent Perl script as a wrapper (http://www.lenzg.net/mylvmbackup/).
Keep in mind that when making a physical back-up, you may copy physical corruption, as well. When restoring a physical back-up that contains corruption, although the back-up will look fine, you will encounter problems—either during the restoration or later. The server may stop on a checksum error. If that happens, a time machine back-up will be needed to go back incrementally until the corruption is eliminated.
You should experiment with restoring from a logical back-up to improve your restore time. Try to restore a back-up just after making a logical back-up so that you can improve your routine and your restoration plan. If the test restore takes too long, consider how to improve your time—with better hardware like SSD or a flash card for your back-up node, or use a concurrent back-up/restore tool like the mydumper or myloader.
The mysqldump utility doesn’t necessarily consume much CPU resources on modern hardware. This is because it uses by default a single thread, consuming one core for dumping a database. This method is good for a heavily loaded server. The disk input/outputs per second (IOPS), though, can increase for multiple reasons. When you make a back-up on the same device as the database, this cause problems, producing unnecessary random IOPS. The dump is done sequentially, on a per table basis. Because it causes a full table scan so that a lot buffer page miss on tables that are not fully cached in memory, producing again read IO. For InnoDB tables, data is usually read in disk order when using auto-increment primary key or when optimized on a regular basis. In this case, disk read-ahead can increase the back-up throughput. For MyISAM or Aria tables, the IO scheduler has a better chance to merge IOPS on sequential, table per table dumps.
As a good practice, it’s recommend that you make a back-up from a network location to remove disk IOPS on the database server. However, it is vital to use a separate network card to keep network bandwidth available for regular traffic.
Although mysqldump will by default preserve your resources for regular spindle disks and low-core hardware, this doesn’t mean that concurrent dumps cannot benefit from hardware architecture like SAN, flash storage, low write workload. The back-up time would benefits from a tool such as MyDumper.
Including More in Back-Ups
When making a back-up with mysqldump, triggers are dumped together with tables, since they’re part of the table definition. However, stored procedures, views, and events need extra parameters to be recreated explicitly. Procedures and functions are also part of system tables, but may want to add –routines –event to the command.
Another thing to consider when creating a back-up and restoration policy is where you will store back-ups. You should store your back-up files in a secure, separate physical location, or off-line. Just be sure they’re readily available if the database server is compromised or suffers damage that might also affect files located in the same facility.
There are other things to consider regarding developing a back-up and restoration policy, regarding making better back-ups. I’ll cover some of them in another article. These are just a few that you may not have considered.