MySQL Full and Incremental Backups

Backup Basics in MySQL – Part 3

MySQL Course

Foreword: In this part of the series, we look at the meaning of Full and Incremental Backups and how they are achieved in MySQL, from a simplified point of view, using simple commands and SQL statements.

By: Chrysanthus Date Published: 16 Jun 2015

Introduction

This is part 3 of my series, Backup Basics in MySQL. In this part of the series, we look at the meaning of Full and Incremental Backups and how they are achieved in MySQL, from a simplified point of view, using simple commands and SQL statements. I assume you have read the previous parts of the series before reaching here. This is a continuation. Backup means you keep the database information in a safe place (device or drive), which is not part of the computer that has the server. In that way, when the server goes bad, the destroyed information in the database in the server would be recovered.

Database Recovery
In this series, when the server goes bad, it is re-installed in the repaired or new computer. After that the backup (copy of the) database is copied back to the server. The users of the database will continue to use the database, with all their information intact. That is recovery in simple terms. Note that the server (software) itself is purchased in a Compact Disk. In the case of MySQL, you can download it free from the Internet and then burn it in a CD. So, it will always be there, for you to re-install.

Full Backup
Full Backup is when you copy all the database information from the server to the backup device. In an average size company, this should be done every day, say at close of work.

Incremental Backup
Incremental backup is what is saved in the binary log (files) between the last full backup and the next full backup. It is advisable to have the binary log in a device (drive) that is separated from the host server computer.

Full Backup in MySQL
Assume that you have installed the MySQL server in a computer and it is running fine, with one database that you have created. In that case, you actually have two databases in the MySQL server. One of the databases is called, mysql, created during installation automatically. It is also managed by the server automatically as the server operates. The mysql database has tables. The mysql database keeps sensitive information like user names, passwords and user privileges. The root (DBA) user has the privilege by default to access this database and all its tables. So during full backup the root has to backup this database. The second database is the one you have created and you probably have all the privileges of the database (and its tables). During full backup, this one is also backup; in fact this second one is the database of interest. For simplicity, it is good for the root to backup the mysql database and your database; after all, he has all the privileges of your database and its content. If you are the one who installed the server, then most likely, you are the root.

The names of the tables in the mysql database are: columns_priv, db, event, func, general_log, help_category, help_keyword, help_relation, help_topic, host, ndb_binlog_index, plugin, proc, procs_priv, servers, slow_log, tables_priv, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, user

These tables have to be backup. The names of these tables indicate their contents; however, I will not go into that.

What do you have to backup? You have to backup the database creation statements, the table creation statements, and the data the tables contain. As, for the mysql database, you do not have to backup its database creation statements and its table creation statements; you have to backup only the data of its tables. This is because the mysql database and its tables are automatically created each time the server is installed or reinstalled; but the data (e.g. passwords and user names) in it, created by the users, cannot be reinstalled, and so this data has to be backup.

You also have to backup the stored procedures and triggers that you created for a database. For this, as you create the procedures and triggers during the design phase of the database (and its contents), you should keep copies of the procedures and triggers in a safe place; that is how you backup stored procedures and triggers. However, if you have the latest version of MySQL server (with MySQL extensions), then you can backup the procedures and triggers, similarly to the way you backup the database and tables in the full backup process. I will not go into that now.

Backup Statements and Commands
The following statements and commands are used in the PurePerl MySQL API Command Line Tool window program (Mysqlcom.pl):

The show databases Statement: This statement lists (displays) the databases on the MySQL server host. The syntax is:

    show databases;

The show tables Statement: This statement lists (displays) the non-temporary tables in a given database. The syntax is:

    show tables db_name;

The show create database Statement: This statement displays the statement used in creating the database. The syntax is:

    show create database db_name;

The show create table Statement: This statement displays the statement used in creating a table. You need the SELECT privilege in order to use this statement. However, if you are the root user, then there is no problem, as you have all the privileges. The syntax is:

    show create table tbl_name

What do you do with these show statements? You can use the first one to show all the databases that the server has. From the list, you will decide on which databases to backup. The mysql database must always be backup. You can use the second one to show all the tables in a particular database. From the list, you will decide on which tables to backup. The third one shows the statement used in creating a particular database. You can backup (copy) the statement. The fourth one shows the statement used to create a particular table. You can backup (copy) the statement. These backup statements are used in re-creating the databases and the tables during recovery.

If you do not want to use the show statements, when you are creating your database and tables, you should be saving the data definition statements (create database, create table) in a file outside the computer. During backup, you will use these data definition statements to re-create the database and tables.

The select ... into outfile statement. This statement is the select statement, which would copy the data of a table into a file. The file created is saved in the host computer. However, you can always use the DOS or Windows commands to copy the file from the host into a device (drive), which is not part of the host. The syntax is:

    select ... into outfile 'file_name'

You need the SELECT and FILE privilege to use this statement. However, if you are the root user, then you can use the statement without any problem, since you have all the privileges. You will see examples of the use of these statements soon. file_name should not already exist. The file created is a text file.

The lock tables Statement with the READ option: When doing full backup, it is not good to shutdown the server, so that you can allow users to continue to use the tables in the databases. However, all users should only be allowed to read and not write to the tables. In this way you will have a clear cut between what you have backup at a particular time and what will be added after backup. The syntax to archive this for a particular table is:

    lock tables tbl_name READ

In some situations you may have to precede the tbl_name with a database name and a dot. The root user can lock all the tables in all the databases with the READ option at the global level (without selecting any database) using the following statement:

    flush tables with read lock;

At the end of the backup, you type the following to unlock tables (global level):

    unlock tables;

The flush logs Statement: By the time you want to make full backup, the binary log file might not have reached its maximum size in order to close for the next one to start. So you have to close it forcefully. The hope here is that the next binary log will start immediately after full backup, and your incremental backup and incremental recovery will be consistent. To force all log files to close, you need to use the flush logs statement. You need to have the RELOAD privilege, in order to use the statement. The syntax is:

    flush logs

This flushes all logs including general log and error log. It closes and starts new log files. If binary logging is enabled, the sequence number of the new binary log file is incremented by one relative to the previous file. The hope is that the new binary logging will take effect after the full backup is complete as the tables are locked during full backup.

When you flush logs during full backup, note the number of the last binary file. This is because if recovery is to come up after the full backup as expected, you will know the number of the binary log file to start with (see details later).

There is a problem with the Flush Log Statement and binary files describe above, depending on how you look at things. When you flush logs and start using the statement “select ... into outfile 'file_name'” to backup, a new binary will start and the Select statements will be logged. That may not be what you want; you may not want the backup statements to be locked. To solve this problem, you should not use the binary file developed during full backup for recovery. This means after full backup, you have to flush logs again. You will then use the binary log files created after this second flush, for incremental recovery (see below).

You now know how to full backup creations statements and data. Anything backup should be kept in a device (drive) that is separated from the host computer. You need to backup only the data for the tables of the mysql database and not its creation statements. This is because the mysql database and its tables are created automatically, when a server is installed.

Incremental Backup Revisited
Incremental backup is what is saved in the binary log (files) between the last full backup and the next full backup or recovery. In order to achieve this, start the server with the option, --log-bin[=base_name] . With that the binary log files will be created for you automatically in the directory you specify in the optional parameter. This directory should be in a device (drive) that is not part of the host computer.

Recovery
When there is a crash, you have to recover the database. The simplest way to do this is to re-install the MySQL server in the repaired computer or a new computer. The installation process will automatically create the mysql database, which will store user names, passwords, privileges and other sensitive information. Then you copy the data of the tables of the mysql database you backed up, to the mysql database of the newly installed MySQL server. From the file that has the creation statements, you create the backup databases and their tables in the newly installed MySQL server. From the backup files you created with the “select ... into outfile” statement you “copy” the data to their respective tables in the newly installed server. I indicated above how to backup and restore stored procedures and triggers.

Use the incremental backup binary log files for incremental recovery - see later.

Time to take a break. We stop here and continue in the next part of the series with a simple backup project.

Chrys

Broad Network

Related Articles

MySQL Full and Incremental Backups

Backup Basics in MySQL – Part 3

MySQL Course

Introduction

Related Links

Comments