Effective MySQL: Backup and Recovery (2012)

Chapter 6. MySQL Configuration Options

MySQL 5.5 supports over 300 configurable system variables. A number of these variables have a direct effect on how MySQL will operate when dealing with a database backup and crash recovery situation. Understanding what system variables do and how they change the behavior of the MySQL server will help define how your backup and recovery system will act when you need it the most.

In this chapter we will discuss:

• Data management system variables

• Replication system variables

• Recovery system variables

Data Management

In this section we will cover data locations, consistency, and binary logging system variables. Having a homogeneous system setup is the preferred way to run an environment, especially at scale. Knowing where your data is located on a file system is a tremendous help when troubleshooting a system along with automation and other tasks. Enabling the right system variables for data consistency driven by your Service Level Agreement (SLA) and knowing when to use certain binary logging options are all part of system design, and, in the end, how your system will recover from disaster.

Data Locations

The following options define the physical filesystem locations of important data stored in MySQL. It is important to note that these locations are often overlooked when running recoveries from server to server. Keeping your data locations homogeneous throughout your system will lessen confusion during recovery especially if your system is sharded. This means keeping all of the MySQL data, tmp, and base directories the same throughout your environment. Other considerations would be keeping the InnoDB data file path and InnoDB log file sizes the same throughout your environment. Here you will find a more detailed list of system variables that need to be the same from master to slave and from shard to shard:

• datadir The datadir is, by default, the directory where all databases, tables, InnoDB data, server logs, and binary log files are located on the filesystem. Directories within the datadir represent databases. Tables within the databases are represented by files, which can differ depending on storage engine and server configuration. InnoDB tablespace(s) and transaction logs will also be stored here unless defined by other variables. The same can be said about server logs like the slow query log and error log along with binary and relay log files. For Linux distributions this, by default, is /var/lib/mysql.

• basedir The basedir is the filesystem location of the MySQL installation directory. It is a good idea to have this directory located in your PATH for easy access to the MySQL server and client utilities. Keep in mind that the basedir, by default, is /usr on Linux and will probably be different from company to company. In this case you should put the $basedir/bin inside your PATH so you do not require the full path to the MySQL server and client utilities.

• innodb_data_file_path This variable defines the location to individual InnoDB data files, also known as tablespaces, along with their sizes and behavior. The size limit of individual files will be determined by your operating system; however, the sum of the files, by default, has to be a minimum of 10MB. InnoDB files can also be set to autoextend. In this case, these InnoDB files grow, if the data exceeds the initially defined size and “auto-extend” is enabled. Please note that these files are currently required by InnoDB to function and are considered system tablespace(s). This includes when InnoDB is set to run with innodb_file_per_table.

• innodb_data_home_dir If you are not using absolute paths to define your shared tablespaces in the innodb_data_file_path system variable you can use innodb_data_home_dir to specify where all common InnoDB data files will be located on the filesystem. Like innodb_data_file_path, this variable does not affect the location of per-file tablespaces when innodb_file_per_table is enabled. The default value for this variable is the MySQL datadir.

• innodb_file_per_table When innodb_file_per_table is enabled all tables that are created with the InnoDB storage engine will create their own tablespace. The per-table tablespace created is represented as tablename.ibd in the corresponding database directory. The .ibd file is where data and indexes are stored. If innodb_file_per_table is disabled, the default, all data and indexes will be stored in the system tablespace. Innodb_file_per_table must be enabled if you choose to use newer InnoDB file formats starting with Barracuda.

CAUTION The innodb_file_per_table variable should be set before creating any database objects. It is not possible to have a hybrid model. The only means to safely convert from a system tablespace to a per-table tablespace is to dump all data, drop all objects, and re-create database objects and reload all data.

Data Consistency

These configuration options affect how MySQL writes and flushes data to disk. MySQL provides options that produce a tradeoff between write performance and durability (i.e., D of ACID) for all transactions. The combination of varying disk hardware configurations such as Battery Backed Write Cache (BBWC) RAID controllers also can affect consistency.

• sync_binlog When the value of sync_binlog is set to one (1), the safest setting, events sync to the binary log after every commit, which provides, at most, one statement lost in the event of a mysqld crash if auto-commit is enabled. Setting sync_binlog to a value greater than the default, zero (0), allows MySQL to sync events at a much slower rate (allowing the disk to not work as much). Although setting sync_binlog to 1 is the slowest setting it can also be sped up with the use of a Battery Backed Write Cache.

As stated earlier, setting sync_binlog to a value of 1 is the slowest but safest setting. This is because InnoDB will sync to the log files after every commit, which, in turn will increase the amount of I/O on your system. SSD (Solid State Drives) drives are becoming less expensive and more prevalent in many installations. When using SSDs on your system you may not notice any performance degradation when setting sync_binlog to a value of 1.

• innodb_flush_log_at_trx_commit By default the value of innodb_flush_log_at_trx_commit is 1, meaning that the log buffer is written out to the InnoDB log files after every commit and a flush disk operation is performed on the log file. Setting the value ofinnodb_flush_log_at_trx_commit to 2 will flush the log buffer to the InnoDB log file at a loose interval of once per second. It is not recommended you use the value of 0.

CAUTION Setting innodb_flush_log_at_trx_commit to 1 does not ensure full ACID compliance. Also, setting this variable to 1 is one of the most performance hindering aspects of replication. For more information please see, http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit.

• innodb_support_xa This option enables InnoDB to run two-phase commits for XA transactions and is enabled by default. This variable is essential for systems that are using binary logging and have more than one thread changing InnoDB data in XA transactions. Although enablinginnodb_support_xa causes an extra disk flush for transaction preparation it is necessary to ensure that transactions are placed into the binary log in the correct order. The only times you should disable this variable is when your system only uses one (1) thread to add and modify data or you are not using replication.

TIP Disabling or setting innodb_support_xa to 0 could be beneficial, performance wise, when restoring a SQL backup and log-bin is enabled. This will remove the need for an extra fsync(). For faster overall performance you can disable innodb_support_xa; however, you must gauge the risk of possibly having inconsistent data with performance.

• innodb_doublewrite InnoDB performs a sequential write and sync of all data pages that are being flushed by the InnoDB I/O thread before writing the data pages to the appropriate random data file positions. If a MySQL crash occurs, the buffer can be used during crash recovery to obtain a correct copy of data pages. This variable is enabled by default (value of 1). The InnoDB doublewrite buffer guarantees page recoverability and reduces the amount of fsync to disk.

When this variable is enabled a chunk of pages is written to the doublewrite buffer followed by an fsync, then pages are written to the tablespace followed by an fsync. If innodb_doublewrite is disabled each page that is written would need to be fsync’ed.

• innodb_flush_method There are three valid values, O_DSYNC and O_DIRECT, along with the default value of fdatasync. The overall goal for this variable is to modify the behavior of synchronizing I/O. POSIX offers different variants to synchronize I/O, which are O_SYNC, O_DYSNC, O_RSYNC, and O_DIRECT.

Linux implements O_SYNC, but glibc maps O_DSYNC and O_RSYNC to the same value as O_SYNC. O_SYNC semantics require all meta-data updates of a write to on disk when returning to userspace. O_DSYNC requires only the file data and meta-data necessary to access it again to be on disk by the time the system call returns. O_DIRECT minimizes cache effects of the I/O to and from a file and makes an effort to transfer data synchronously but gives no guarantees that data and necessary meta-data are transferred.

If the value of innodb_flush_method is set to O_DSYNC InnoDB will use O_SYNC for the logs files and fsync to flush data files. When innodb_flush_method is set to O_DIRECT InnoDB uses O_DIRECT to open data files and fsync to flush both data and log files. By default, InnoDB uses fsync to flush both data and log files.

Given that InnoDB has its own caching, i.e., innodb_buffer_pool, setting innodb_flush_method to O_DIRECT may help avoid double buffering between the buffer pool and the filesystem cache when you are running with hardware RAID and Battery Backed Write Cache. In any case you will need to benchmark your particular environment to see what setting works best with your load.

• innodb_fast_shutdown Changing the behavior of InnoDB upon shutdown is nice to have but can be dangerous. innodb_fast_shutdown has three valid values, 0, 1 (the default), and 2. When the value is set to 0, InnoDB performs a slow shutdown, meaning a full purge and an insert buffer merge before shutdown. With a value of 1, InnoDB skips the purge and merge and does a fast shutdown, making the shutdown process faster but still safe. When the value is set to 2, the most dangerous, InnoDB flushes its logs and shuts down cold, like a crash. Although no committed transactions are lost, an InnoDB crash recovery will occur during the next startup and may take more time for the instance to come online.

• default_storage_engine Sometimes the ENGINE of the table can differ from environment to environment (i.e., Dev, Test, QA, and Production). It is important to set the default_storage_engine through all parts of the environment identically to ensure the correct behavior of the table throughout all points of the product life cycle. In MySQL versions from 3.23 to 5.5.4 the default value is MyISAM. In version 5.5.5 and higher the default value is now InnoDB.

Binary Logging

These initial options are mandatory settings for the configuration of MySQL binary logging. These options are necessary to ensure that a point in time recovery (PITR) is possible. These settings are also necessary to enable replication on a master.

• server_id With the current implementation of replication, MySQL needs to know that it is executing statements on unique servers so as to not duplicate work. Setting the server_id to a unique value for every slave in your MySQL topology will ensure that replicated data is applied correctly. If duplicate server ids are noticed in a MySQL topology a few errors can occur. Replication will not start and the slave will throw an error stating the master and slave hosts have the same server_id. If there is more than one slave host your error may fill up with the following note:

A good practice is to set the server_id to an integer based off of the IP address of the server. The integer address of the IP 192.168.0.1 is 3232235521 and can be set as the value of server_id as long as the integer is less than or equal to 4294967295. For example:

• log_bin This enables the binary log and is absolutely necessary if you want to replicate data and/or have point in time backups. It is a good practice to set an absolute path and basename for the value of log_bin to control the name of the binary logs. If no basename is given MySQL will place the binlogs in the datadir with “host_name-bin” as the basename.

• log_bin_index The log_bin_index file holds the names of binary logs acting as an index. Again, if you do not specify the filename and omit the basename in log_bin MySQL will use “host_name-bin.index” as the default filename.

• binlog_format With this variable you will be able to control the type of binary logging that MySQL uses. Setting the value to STATEMENT, the default, will cause MySQL to use pure statement based replication where all statements are recorded to the binlog. Setting the value to ROW will cause MySQL to use pure row based replication and log changed blocks to the binary log. Finally, if you set binlog_format to MIXED, both statements and blocks can be inserted into the binary log.

• binlog_do_db & binlog_ignore_db These variables are used on the master host. When binlog_do_db is specified and using statement based replication, the only statements that will be logged to the binary log are those that are preceded by the USE database_name statement. If more than one database is needed you will need to use multiple lines in the my.cnf file because database names can contain commas. Keep in mind that cross-database statements will not be logged while a different database is set as default or no database is selected. When using row based replication only changes belonging to the database name are made regardless if the USE statement is used. Adversely, you can use binlong_ignore_db to exclude databases from the binary log on the master host.

If you do choose to use these variables it can change the way backups and recovery are performed. There are certain situations or setups that these variables are good for; however, you should be aware that it is possible to have data inconsistencies when used.

• binlog_cache_size If a MySQL host has binary logging enabled, transactional storage engines are being used (i.e., InnoDB) and you are using large transactions, you can increase the value of binlog_cache_size to possibly increase performance. This cache is used to hold changes to the binary log during a transaction.

By checking the value of the server status variable, binlog_cache_use, you can determine the number of transactions that used the binary log cache. The binlong_cache_disk_use is another server status variable to check that indicates the number of transactions that used the binary log cache but exceeded the value of binlog_cache_size and used temporary files to store changes.

• binlog_stmt_cache_size This variable specifies the size of the cache for the binary log to hold non-transactional statements during transactions on a per client basis. Again, if you are using large non-transactional statements within transactions you may benefit from increasing the value onbinlog_stmt_cache_size. Also, this variable only matters if binary logging is enabled.

The server status variable, binlog_stmt_cache_use, specifies the number of non-transactional statements that used the binary log statement cache.

• binlog_row_event_max_size The value is represented in bytes with a default value of 1024 and should be a multiple of 256. This variable represents the maximum size of a row based binary log event.

MySQL Replication

MySQL replication is crucial in systems that have a good backup and recovery plan. There are a few variables that affect the way MySQL replication behaves. Whether a slave host is set to only replicate certain databases, skip certain errors, and or is set up in a unique chain topology, it is important to know how the following will affect your setup.

• relay_log MySQL uses a numbered set of files called relay logs to hold replicated database changes before the SQL thread applies them to the slave. These files are located on the slave host directly and are only active on the “master” host when log_slave_updates is active. The relay log files are numbered in sequence starting from 000001 and are accompanied by what is referred to as the relay index file, which contains the names of all relay files currently being used. Relay log files are in the same format as MySQL binary logs, making them easy to read using themysqlbinlog client utility.

Like the binary log, relay log positions are represented by byte offsets, so if the Relay_Log_Pos is 671 and the Relay_Log_File is mysqld-relay-bin.000002 then MySQL has read up to 671 bytes of the corresponding file. The naming conventions for the relay log file can be altered with therelay-log=[file_name] and relay-log-index=[file_name] options in the my.cnf file. If either of the preceding is absent in the my.cnf file the relay logs will take their naming convention from the pid-file option, if specified. For example, when a PID is specified in the my.cnf and therelay-log and relay-log-index are omitted, the relay logs will be mysql_3306-relay-bin.index and mysql_3306-relay-bin.000001. If relay-log, relay-log-index, and pid-file are not specified, the relay logs will default to host_name-relay-bin. nnnnnn and host_name-relay-bin.index, where host_name is the server host and nnnnnn represents the sequential file numbering.

• relay_log_index The relay_log_index system variable holds the names of all the relay logs for quick lookup.

• replicate_do_db & replicate_ignore_db These variables are used on the slave host and act much like binlog_do_db and binlog_ignore_db do on the master host. If a slave is set up using replicate_do_db and using statement based replication only, statements that have a preceding USE database_name statement will be applied to the slave host. If row based replication is used, a statement with a qualifying database_name.table_name will be applied to the slave host. Adversely, if you specify replicate_ignore_db, all transactions relating to the database specified will not be applied to the slave host.

• slave_skip_errors Replication error codes can be skipped automatically when slave_skip_errors is specified. Normally replication will stop when the SQL thread encounters an error; however, this variable will cause the SQL thread to skip those errors listed in the variable value.

It is important to mention that it is not always a good idea to specify a value for slave_skip_errors, given the implications of data drift and/or data integrity.

• slave_exec_mode There are two valid values for slave_exec_mode, IDEMPOTENT and STRICT. This variable is used for replication conflict resolution and error checking. If the value is set to IDEMPOTENT (default for NDB), the slave will not error out during duplicate key or no key found errors. The IDEMPOTENT value is useful with a system that is set up in a multi-master or circular replication fashion. When the value is set to STRICT, the default, replication will stop on duplicate key and no key found errors.

• log_slave_updates When log_slave_updates is set to true and binary logging is turned on, the slave host will write all replicated changes to its own binary log. This option is used to chain multiple nodes together through replication. For example, if you have three servers (A, B, and C) and want to connect them in a chain you would use log_slave_updates on B. B would replicate from A, and C from B, forming a chain, (A -> B -> C).

Recovery

InnoDB has provided the ability to auto recover after a crash or detecting errors. In later releases, version 5.0, MyISAM is now able to run check and recover automatically when an error or crash is detected. There are four system variables that need to be in every administrator#x2019s arsenal when dealing with InnoDB or MyISAM corruption.

• innodb_force_recovery One would use innodb_force_recovery to recover InnoDB tables that have been corrupted on the page level. Setting this variable to a value greater than 0 (the default) will allow an administrator to start the MySQL server and run a SELECT … INTO OUTFILE or mysqldump. Corruption may cause InnoDB to crash, assert, or roll forward recovery to crash from InnoDB background threads or when issuing a SELECT * FROM table_name statement. Innodb_force_recovery is used to prevent InnoDB background operations from running so you are able to start the server and dump out your data.

There are seven different levels of innodb_force_recovery, 0–6; however, it should be noted that when setting the value past 4, most of your data might be irrecoverable. InnoDB prevents INSERT, UPDATE, and DELETE operations when the value of innodb_force_recovery is greater than 0. The following is a list of what the different levels accomplish.

• (DEFAULT) A normal startup without a forced recovery.

• (SRV_FORCE_IGNORE_CORRUPT) Allows the server to run even if it detects a corrupt page. This will allow InnoDB to jump over corrupt index records and pages when running SELECT * FROM table_name.

• (SRV_FORCE_NO_BACKGROUND) If a crash occurs during the purge operation, setting the value to 2 would prevent the main thread from running.

• (SRV_FORCE_NO_TRX_UNDO) Ensures that transaction rollbacks do not occur after recovery.

• (SRV_FORCE_NO_IBUF_MERGE) Table statistics are not calculated and no insert buffer merge operations occur.

• (SRV_FORCE_NO_UNDO_LOG_SCAN) Skips looking at InnoDB undo logs upon startup and treats incomplete transactions as committed.

• (SRV_FORCE_NO_LOG_REDO) Ensures that the log roll-forward is not run in connection with recovery.

• myisam_recover There are four values you can use for myisam_recover: DEFAULT, BACKUP, FORCE, and QUICK. Furthermore you can use any combination of the preceding values if you separate them by commas. If myisam_recover is enabled MySQL will check if a MyISAM table is marked as crashed or was not closed properly every time the table is opened. The following is a list describing what each of the values does:

• DEFAULT Recover without backup, forcing, or quick checking.

• BACKUP Run a backup of the table if the data file was changed during recovery. In this case a backup of table_name.MYD will be saved as table_name-datetime.BAK.

• FORCE Runs a recovery even if more than one row of data would be lost from the .MYD file.

• QUICK If there are no deleted blocks, rows in the table will not be checked.

When using MyISAM and you want to recover from most problems automatically, you should use the options BACKUP, FORCE as the values for myisam_recover. It is important to note that the server will write a note to the error log before the server automatically repairs the table.

• myisam_max_sort_file_size The default setting for myisam_max_sort_file_size is 2GB and represents the max allowed file size when re-creating a MyISAM index during a REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE. If the size of the index is greater than the value ofmyisam_max_sort_file_size the key cache is used instead. Keep in mind that the space must be available on the filesystem where the original index file is located.

• myisam_sort_buffer_size This setting specifies the size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes. The max value on 32 bit systems is 4GB but can be greater on 64 bit systems.

Conclusion

As stated at the beginning of the chapter, there are over 300 different configuration variables. It is very important to know how a MySQL server has been configured in order to streamline backups and recovery; furthermore, it is important to know how the MySQL server will act in the event of a crash given the storage engine(s) used. Given all the aforementioned variables the correct settings can only be determined by the load of your unique system. That said, please make sure to benchmark with your particular load and make sure to practice disaster recovery on a regular basis so, when the time comes, you and your business know what to expect.