• Approaching a MySQL Backup

    There is more than one strategy to backup a MySQL environment:

    1) Hot backup (stop your MySQL instance and take a full file-system cold backup,then start MYSql instance)

    2) Use a client tool included with the standard MySQL installation (mysqldump).

    Before running mysqldump, you must consider several important factors in order to make an informed decision about the best options to use. These factors are:

    A) What is the size of the backup?
    B) What locking strategy is necessary?
    C) How long will the backup take?

    Determining Your Database Size:

    The following SQL statement provides the total size in MB of your current data and indexes.

     mysql> SELECT ROUND(SUM(data_length+index_length)/1024/1024)
 ->        AS total_mb,
 ->        ROUND(SUM(data_length)/1024/1024) AS data_mb,
 ->        ROUND(SUM(index_length)/1024/1024) AS index_mb
 -> FROM   INFORMATION_SCHEMA.tables;
 +----------+---------+----------+
 | total_mb | data_mb | index_mb |
 +----------+---------+----------+
 |      927 |     847 |       80 |
 +----------+---------+----------+

    Choosing a Locking Strategy :

    The locking strategy chosen will determine whether your application can perform write  operations with your database during the execution of a backup. By default, mysqldump performs a table level lock to ensure a consistent version of all data using the LOCK TABLES command. This occurs with the –lock-tables command line option, which is not enabled by default. This option is part of the –opt option, which is enabled by default. You can elect to not lock tables; however, this may not ensure a consistent backup. When you are using the MyISAM storage engine, –lock-tables is necessary to ensure a consistent backup. Alternatively, mysqldump provides the –single-transaction option, which creates a consistent version snapshot of all tables in a single transaction. This option is only applicable when using a storage engine that supports multiversioning (MVCC). InnoDB is the only storage engine included in a default MySQL installation that is applicable. When specified, this option automatically turns off –lock- tables.

    The following SQL statement will confirm the storage engines in use for your MySQL instance.

    mysql> SELECT  table_schema, engine, COUNT(*) AS tables
    -> FROM    information_schema.tables 
    -> WHERE    table_schema NOT IN 
    ->         ('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA') 
    -> GROUP BY table_schema, engine 
    -> ORDER BY 3 DESC; 
+--------------------+--------+--------+ 
| table_schema       | engine | tables | 
+--------------------+--------+--------+ 
| shopping_cart      | MyISAM |    109 | 
| cust_db            | InnoDB |     48 | 
| mysql              | MyISAM |     21 | 
| analytics          | InnoDB |     20 | 
| phpmyadmin         | MyISAM |      8 | 
| newsletter         | MyISAM |      8 | 
| cust_db            | MyISAM |      3 | 
| mysql              | CSV    |      2 | 
+--------------------+--------+--------+

    Execution Time :

    The most important requirement is to determine how long your backup will take. There is no calculation that can give an accurate answer. The size of your database, the amount of system RAM, the storage engine(s) in use, the MySQL configuration, the hard drive speed, and current workload all contribute to the calculation. What is important when performing a backup is to gather this information for future reference. The execution time is important, as this is an effective maintenance window for your database. During a database backup there may be a limitation of application functionality, there may be a performance overhead in performing the backup, and your backup may limit other operations, including batch processing or software maintenance.

    Combining Information:

    The following is a recommended SQL statement that combines all information for an audit of your database size.

    cat storage_engines.sql
SELECT  table_schema, engine, 
       ROUND(SUM(data_length+index_length)/1024/1024) AS total_mb, 
       ROUND(SUM(data_length)/1024/1024) AS data_mb, 
       ROUND(SUM(index_length)/1024/1024) AS index_mb, 
       COUNT(*) AS tables 
FROM  information_schema.tables 
GROUP BY table_schema, engine 
ORDER BY 3 DESC; 

mysql> source storage_engines.sql 
+--------------------+--------+----------+---------+----------+--------+ 
| table_schema       | engine | total_mb | data_mb | index_mb | tables | 
+--------------------+--------+----------+---------+----------+--------+ 
| analytics          | InnoDB |    10903 |   10525 |      378 |     20 | 
| cust_db            | InnoDB |     1155 |     962 |      194 |     48 | 
| newsletter         | InnoDB |      514 |     278 |      237 |      7 | 
| shopping_cart      | MyISAM |       27 |      19 |        8 |    109 | 
| cust_db            | MyISAM |        9 |       3 |        7 |      3 | 
| mysql              | MyISAM |        1 |       0 |        0 |     21 | 
| information_schema | MyISAM |        0 |       0 |        0 |      8 | 
| information_schema | MEMORY |        0 |       0 |        0 |     20 | 
| mysql              | CSV    |        0 |       0 |        0 |      2 | 
+--------------------+--------+----------+---------+----------+--------+

    Performing a MySQL Backup :

    Now that you have gathered the prerequisite information, you have the details necessary to make an informed decision. The choice of how to perform a backup, when to perform it, and how you monitor and verify is a more complex process. One additional consideration during a backup process is to disable any cron or batch processes during the backup to minimize additional workload. This can minimize database contention and shorten the window of time needed. Running mysqldump : In the simplest form, you can perform a backup using mysqldump with the following syntax.

    $ time mysqldump -uroot -p[password] --all-databases > backup.sql
$ echo $? 
$ ls -lh backup.sql
    • The first command runs the mysqldump for all databases, producing an ASCII dump in the backup.sql file.
    • The second command confirms the exit status of the first command. A non-zero result is an indication of a problem during the backup process. If any errors occur, these are generally shown in the screen output.
    • The third command shows the size of your backup file for later reference.
    For example:

$ time mysqldump -uroot -p[password] --all-databases > backup.sql
real    0m35.493s
user    0m9.808s 
sys     0m3.021s 
$ echo $? 
0
$ ls -lh backup.sql 
-rw-rw-r-- 1 usr grp 818M Aug 10 21:37 backup.sql

    This is a successful backup file totaling 818M that took 35 seconds to execute. The original size of the database data as shown previously for this MySQL instance was 847M.

    An example of an error condition may look like this:

    $ time mysqldump -uroot -p[password] --all-databases > backup.sql
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 
'root'@'localhost' for table 'cond_instances' when using LOCK TABLES 
real    0m7.692s 
user    0m1.780s 
sys     0m0.313s 
$ echo $? 
2
$ ls -lh backup.sql 
-rw-rw-r-- 1 usr grp 94M Aug 10 21:28 backup.sql

    Securing Your Backup:

    The final step in a minimal backup approach is to ensure the security of your data. The backup is currently on the same system as your data. A loss of this system would include the data and your backup. The minimum you should undertake is to copy your backup to a secondary location:

    For example:

    $ time gzip backup.sql 
$ scp backup.sql.gz  another-server:backup-dir

    Benefits with mysqldump :

    The mysqldump command provides a SQL-based backup file. This can be ideal for creating a backup that can be executed on different versions of MySQL, and on different operating systems.

    Categories: Mysql

    Comments are currently closed.