Chapter 5: Tracking down load issues Up Part II: Advanced MySQL Administration and Management Chapter 7: Migrating a SiteWorx account to a different MySQL server 

6 Data backup, repair, recovery

Databases are often the most valuable part of an application on the internet. Unfortunately, databases and the data they maintain are subject to failure. The hardware backing the filesystem your database run on could die, power could be cut and the hard disk cache might not be able to write cached data back to the magnetic platters, the filesystem might be unable to figure out what is missing and make entire files “disappear”, the raid controller might die, and you get the idea. Even though newer MySQL datastores are more reliable, things can still go awry. This chapter deals primarily with 2 things:
  1. How you can have InterWorx backup MySQL data for you
  2. How to try and recover when your database becomes corrupted

6.1 Backup inside InterWorx

The interface that InterWorx provides allows you to generate backups of SiteWorx accounts, which by default contain a dump of all the databases tied to this account. The SiteWorx interface also allows you to make partial backups that contain only MySQL database dumps. These partial backups can be used to take snapshots of valid database states. It is important to keep in mind that if your database is corrupted, the process which dumps your database will be unable to make a database dump and the backup process will fail. This is both to your advantage (if the database is corrupted you aren’t taking snapshots of bad data) and disadvantage (if you aren’t paying attention to the alert emails, you might miss that automated backups are failing).

6.1.1 From NodeWorx

figure images/nodeworx/siteworx/backup-restore.png
Figure 6.1 NodeWorx Backup/Restore Screen
Inside NodeWorx under SiteWorx ▷ Backup / Restore, you have the ability to do bulk backups of multiple SiteWorx accounts. This is shown in figure 6.1↑. This is good if your clients aren’t very prone to backing up their content regularly. Select the SiteWorx accounts you wish to backup, and on the “With Selected:” drop down, select “Full-Backup”. “Structure-only” will not backup the databases and thus is not helpful when trying to preserve MySQL data.

6.1.2 From SiteWorx

From inside SiteWorx accounts, you are given much more control of the backups and are able to create database-only backups. In additional you can schedule backups to occur regularly which allows for a hands-free approach to backing up. Backup Now

Inside a SiteWorx account, under Backups ▷ Backup Now, you can create a backup on demand for your database, with the additional ability to have it sent to a remote server when the backup process completes. (Backups are tar’d and gzip’d which might take some time depending on the size of the database). As you can see in figure 6.2↓, you are asked for 2 things when you Backup Now.
figure images/siteworx/backups/backup-now-step1.png
Figure 6.2 The Backup Now starting prompt
Backup Type is how complete of a backup do you want for the SiteWorx account. In this case, you want to choose is “Partial Backup”. Don’t worry, you will be asked next what you want to backup.
Backup Where is where you want the completed file to be stored. The default location is /home/[SiteWorx linux user]/[active domain]/iworx-backup. Backups placed in this directory are recognized by InterWorx when attempting a restore from NodeWorx. The alternatives to default location are:

Local File
: You don’t want InterWorx to name and place the file for you, you want to dictate filename and where the backup ends up.
FTP: The file will be FTP’d to another server on completion. You need to know FTP login credentials and the path structure of the remote server.
SFTP/SCP: The SCP command will be used to send the file to another server. You need to know a linux user login on the remote server to use this.
figure images/siteworx/backups/backup-now-step2-partial.png
Figure 6.3 Backup Now with Partial Backup as Backup Type and FTP for Backup Where
The next step of the process is shown in figure 6.3↑. As you selected partial backup in the previous step, you are now prompted for what specifically you want to backup from the site. Since we are interested in preserving databases, “Backup your databases” should be checked. Depending on what you selected for “Backup Where” with the exception of Default Location, you will be prompted for paths and login credentials for sending the completed backup file to it’s storage location. Clicking the Backup button will begin the backup process and you will be notified via email (based on what you put in “Email Status To”) when the backup completes successfully or if there’s an issue of some sort. Schedule

figure images/siteworx/backups/backup-schedule-step1.png
Figure 6.4 Scheduling a Backup Screen
Setting up a scheduled backup is similar to Backup Now, as discussed in the previous section, with the additional responsibilities of electing backup frequency, and how many backups to store at once (old ones are removed). Scheduling backups is done in SiteWorx under Backups ▷ Schedule. A screen shot of the dialog that appears when you click [Create Scheduled Backup] is shown in figure 6.4↑. Once you select your desired backup frequency, a dialog similar to what you saw in Backup Now appears. This is shown in figure 6.5↓.
figure images/siteworx/backups/backup-schedule-step2-partial.png
Figure 6.5 Setting up a scheduled backup
As you can see, you are able to set the maximum number of backups to store at a given time. As a NodeWorx admin, you are able to control the max limit in NodeWorx, and this is covered in depth in the Backup guide. Setting this value to something reasonable will ensure that you don’t run out of disk space on the storage medium that will hold the backup. Using phpMyAdmin to backup

The nice thing about phpMyAdmin is that is has some great capabilities for generating backups on the fly through an interface instead of command-line. If you just want a raw MySQL dump of your database, you may elect to take this option for convenience. Under Hosting Features ▷ MySQL ▷ PhpMyAdmin, you can get access to the phpMyAdmin for the current SiteWorx account.
figure images/siteworx/mysql/mysql-php-my-admin-export-link.png
Figure 6.6 phpMyAdmin’s Export Link
Figure 6.6↑ shows where you can find the “Export” link in phpMyAdmin. From the export page you can determine the exact characteristics of the SQL dump of your database(s).
figure images/siteworx/mysql/mysql-php-my-admin-export.png
Figure 6.7 phpMyAdmin’s export settings screen
The export page is shown in figure 6.7↑. Let’s walk through all the settings on this page, in case you aren’t feeling comfortable with all that is there.
This box contains 2 choices, the databases you want to export, and what format the export should be in. For database selection, you probably want to backup everything with the exception of the information_schema table. The information_schema is a meta-data table that MySQL maintains to make statistics and information about databases available to the user. It is not actually stored on disk anywhere - any SELECTS on this table have results generated on-demand by the MySQL server. Thus, backing it up is somewhat silly.
For export type, you will probably want to go with SQL for backup purposes. These are the raw SQL commands which would re-generate your database and data on the fly. Other formats might be really convenient for examining data by human eye or exporting your data to excel sheets for statistics analysis.
Let’s go down the list of options one-by-one:
Add custom comment into header allows you to add a special comment to your SQL output. It is possible to make comments - i.e. non-executable SQL statements - inside SQL files and the export function will gratuitously add comments to make the SQL dump easier to read for humans. If you wish, you can add a special comment at the top with some information important to you.
Enclose export in a transaction only matters if you are using InnoDB as your storage engine for your database tables. By default, tables are created using MyISAM as the storage engine which does not support the transaction model. Transactions allow you to guarantee that the data you get is in a “balanced” state. For example, if when you add a new user to your web application, you need to do INSERTs on 5 different tables, those are each independent INSERTs that occur independent of each other. If you happen to do a dump while the INSERTs are occuring, you might not get all the data needed for that new user. Transactions are a way of grouping queries together so they occur as a single functional unit - and any changes that are going to be done to the database have to wait until the dump has completed to occur. This ensures you don’t get any non-consistent states in your dump.
Disable foreign key checks also only matters if you are using InnoDB tables. Foreign keys are a way of telling the database that one column of one table references the exact same data in another table. For example a user table and a purchase record might have the userid of the user who purchased something in the purchase record table. The foreign key tells the database that userid in purchase record refers to userid in user table, and that if for some reason the user is deleted, that the purchase records relating to that user either need to be deleted or changed to a different userid. Disabling the foreign key checks would make it so when re-creating the database, foreign keys aren’t enforced.
Add DROP DATABASE will add a DROP DATABASE for the database being re-created at the beginning of the import. This guarantees that any bad data is wiped away before the import process begins. This also guarantees that any data you have in your database currently is gone, so use with caution.
Add DROP TABLE / VIEW / PROCEDURE / FUNCTION is similar to Add DROP DATABASE. This will delete each table being re-created before it is imported again.
Add IF NOT EXISTS will only re-create tables if they don’t exist already. This might be helpful if you are trying to make a backup that isn’t destructive to the current data set.
Add AUTO_INCREMENT value is useful if you have columns which rely on auto_increment (many id number columns use this). It’s probably best to have this on.
Enclose table and field names with backquotes ensures that names are properly enclosed in backquotes - this isn’t 100% necessary but it explicitly defines a table and field name to the SQL system which is important if your column or field name is a MySQL reserved word.[H][H]Reserved words can be seen here:
Add CREATE PROCEDURE / FUNCTION any procedures or functions you have will attempt to be recreated. Procedures and functions allow you to push some of the data manipulation tasks to the database management system instead of having PHP or whatever web language handle it.
Creation/Update/Check dates will add to every table when it created/updated/checked in the comments surrounding it.
Complete inserts adds the column names to the SQL dump. This parameter improves the readability and reliability of the dump. Adding the column names increases the size of the dump, but when combined with Extended inserts it’s negligible.
Extended inserts combines multiple rows of data into a single INSERT query. This will significantly decrease filesize for large SQL dumps, increases the INSERT speed when imported, and is generally recommended.
Maximal length of created query the maximum length in characters of an extended query
Use delayed inserts will increase the speed of the import, which might be helpful if you are going to use this SQL file on a very busy server. Normally an insert will return “OK” when the data base has safely stored the data. A delayed insert will return OK immediately, and then queue the insert for when there is a free thread available to service the insert. On busy MySQL servers, you may want this to avoid having a giant import take a long time while it waits for free threads to service each insert.
Use ignore inserts means that errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.
Use hexadecimal for BLOB will use hexadecimal[I][I] representation for BLOB (Binary Large Object) columns. These are typically used when storing files, images, data in the database. This is recommended if you have blob columns.
Export type controls how data is put into tables. INSERT is your generic table insert - it will fail if the primary key of the row you are inserting matches an existing primary key. UPDATE will only add the data if there is an existing row with a primary key that matches the one in the row you are updating. REPLACE behaves a lot like INSERT, but instead will delete a row if its primary key matches the row you are REPLACE-ing.
File Name Template will control what the filename will be when downloading the dump from the server. Keep in mind that you can always save the SQL file as what ever you choose from your browser.
Compression controls whether the dump will be compressed before making it available for you to download. This may be desirable if the file is extremely large ( over 100 MB ).
The default selections should work fine in most cases, but you may wish to tweak the options if you are trying to produce an SQL backup that is non-destructive to existing data.

6.2 Backup on Command Line

If you are an advanced user who wants a bit more control over the backup process on your system, it might be better to do backups via the command-line instead of the interface. While the interface will handle most needs just fine, you may want to increase the rate of how often backups are done, or elect to go around InterWorx’s backup system entirely to just have raw MySQL dumps as seen in section↑.

6.2.1 Using the command-line backup script

The command line backup script is located at /home/interworx/bin/backup.pex. This is actually the script that gets executed by the panel on your behalf when doing any sort of backup operation. The command line backup interface is less user-friendly, but you have a lot more options at your disposal. Using the command line interface is most useful when you want to automate (e.g., via CRON) the creation of SiteWorx account backups.
In order to use the command-line script, SSH into the server and su to the iworx system user. You may need to switch to the root user first (if you’re not already logged in as the root user). Alternatively, you can also switch to the iworx user (once you have enabled the iworx user account and set a shell). The options related to backing up a database are presented in table 6.1↓. Using this table, you should be able to create a backup command that can be either scripted or added to crontab for automated backup. Here’s an example backup command that you could add to the root user or iworx user’s crontab[J][J]Backslashes ’\’ at the end of the line are used to allow the command to span multiple lines in the BASH shell:
~iworx/bin/backup.pex --domains all \
                      --backup-options db \
                      --output-dir /var/db-backups/ \
                      --quiet \
Parameter Description
--domains [ SiteWorx domain list ] Space-seperated list of SiteWorx account domains to backup. Simple regular expressions are also allowed here (see examples below).
--domains all Backup ALL domains on the server
--backup-options db Backup databases only
--output-dir [path] Optional - set where you want the final backup file to reside
--tmp-dir [path] Optional - When the backup is being created, InterWorx needs to create temporary files somewhere. By default it’s /tmp. You can set the tmp dir with this parameter
--xfer-method [method] Optional (requires --xfer-ini) - If you want IWorx to send the backup(s) to another server, you can specify either ftp or scp as the method.
--xfer-ini [/path/to/xfer.ini] Optional - In order to use remote transfer, you need to specify a file with transfer settings. This is described later.
--reseller-id [reseller-id] Optional - Limits the list of possible backup domains to the domains belonging to the reseller-id. If --domains parameter is not set, but --reseller-id is set, all domains under the given reseller are backed up. If --domains parameter is also set, the list will only match domains belonging to the given reseller.
--compression [1-9] Optional - Set the compression level for the final backup file. This option is identical to the gzip compression parameter, where 1 is the "quickest" and 9 is the "slowest". If not set, it defaults to the gzip default compression level.
--quiet Optional - Causes the script to run silently, and not print any text to the screen. Useful for cron jobs.
--email [email] Optional - Email address to send backup results to.
--filename-format [format-string] Optional - Sets the format of the final backup filename given the format-string provided. If this option is omitted, the default format used is set in the iworx.ini, under the [iworx.backup][filename_format] setting (default %D-%t-%b.%d.%Y-%H.%M.%S). %D = domain name, %T = unix timestamp, %U = unix username, %R = reseller id, %t = backup type (full or partial), %H = hour, %M = minute, %S = second, %m = month (1..12), %d = day of month (1..31), %a = 3-letter day of week (Sun..Sat), %Y = 4-digit year"
Table 6.1 backup.pex Available Options
If you want to use the remote transfer options of the backup system, you need to remove the --output-dir option, and add --xfer-method [ftp|scp] and --xfer-ini [/path/to/xfer.ini]. The xfer.ini is a small text file somewhere on your filesystem that looks like the following for SCP transfers:
password="your password"
host name="host name.domain.tld"
Alternatively, if using FTP to transfer the final backup, you want an xfer.ini that looks like this:
password="your password"
host name="ftp.domain.tld"

6.2.2 Using mysqldump

You may also elect to use the mysql-provided utility to backup your databases, mysqldump. This section will in general be brief since the official MySQL documentation already covers this utility well. It should be known that ultimately when doing a backup of a database using the InterWorx backup system, this is the utility that is called to generate the database backup. In order to get login credentials for the database, you can either use the MySQL username and password that has access to the database as specified in SiteWorx, or you can use the root MySQL user. If you missed on how to get the root MySQL password, it needs to be set manually in the control panel as described in section 2.2.1↑. Here’s an example of how to use mysqldump:
mysqldump -S /var/lib/mysql/mysql.sock -u root --all-databases -p
Which would do a full dump of every database on the server. There’s also:
mysqldump -S /var/lib/mysql/mysql.sock -u root -p [database name]
Which would allow you to dump a specific database. Note that by default, mysqldump dumps the SQL backup to screen. In order to capture it, you will want to redirect STDOUT to a file like so:
mysqldump -S /var/lib/mysql/mysql.sock -u root -p [database name] > /root/backup.sql
You will still see the prompt for the user’s password and be able to enter it, but the output from the dump will appear in the file after the bash redirection symbol >. If the file is large you may wish to gzip it to reduce its size.

6.3 Checking and Repairing corrupted data

Corruption is unfortunately a reality of dealing with database management systems in hosting environments. Luckily, MySQL and the under-lying layers of the filesystem are designed to be able to recover from this sort trouble often by trying to keep a history of all data manipulation stored safely on magnetic or flash media. That way, a loss of power or hard crash allows the software to recover based on the stored list of operations. In addition, corruption can occur when the partition MySQL data is stored on runs out of free space. MySQL may only be able to write part of the data it needs to write for an INSERT or UPDATE to disk before the filesystem cuts off write permission. This means MySQL is stuck running with data in memory that it can’t flush to disk, which can lead to corruption. It’s important to ensure your disk partitions always have enough space. InterWorx has built in low-space detection and will email the NodeWorx administrator when it detects low on disk space, as well as notify with a warning when logging into NodeWorx.
Often in MySQL, corruption doesn’t rear its head until you attempt a backup. Since a backup typically requires stepping through an entire database and pulling out data, it will fail if any sort of corruption is detected. This also means automated backups will not be generating backups until the corruption is fixed. That’s why it’s important to stay on top of your backup status messages being sent from the system to your email so that you don’t risk getting caught with an old state of a SiteWorx account.

6.3.1 Checking for corruption

You can check for corruption using the mysqlcheck utility, included by default with most MySQL packages. The MySQL check can give you a definitive report on the status of a table. Again, check section 2.2.1↑ if you are not aware of how to get the root MySQL password. Then you can run: mysqlcheck -S /var/lib/mysql/mysql.sock -u root -p --all-databases to check all databases for corruption. The output for mysqlcheck looks like:
examplec_WordPress.test                            OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
Error    : You can’t use locks with log tables.
status   : OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.servers                                      OK
Error    : You can’t use locks with log tables.
status   : OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
On the other hand if there’s corruption you might see that one of your tables has a corrupted message:
error    : Size of datafile is: 162         Should be: 220
error    : Corrupt
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
Occasionally, your corruption will be unrecoverable and you will have to repair the database by importing a backed up state of it. On the other hand, occasionally you can try to repair the database using MySQL’s automatic repair capabilities with option --auto-repair appended to the mysqlcheck command. This will cause MySQL to go through and try and repair the instances of corruption it finds. Keep in mind that repair is not a guaranteed “safe” operation - before attempting the repair you should try to preserve the current database state. One technique is to stop the MySQL server and make a copy of the database’s directory in /var/lib/mysql before starting the server backup to attempt the repair operation.
 Chapter 5: Tracking down load issues Up Part II: Advanced MySQL Administration and Management Chapter 7: Migrating a SiteWorx account to a different MySQL server 

(C) 2017 by InterWorx LLC