Mysql Architecture

Application Layer

The MySQL application layer is where the clients and users interact with the MySQL RDBMS. There are three components in this layer as can be seen in the layered MySQL architecture diagram in Figure These components illustrate the different kinds of users that can interact with the MySQL RDBMS, which are the administrators, clients and query users. The administrators use the administrative interface and utilities. In MySQL, some of these utilities are mysqladmin which performs tasks like shutting down the server and creating or dropping databases, isamchk and myisamchk which help to perform table analysis and optimization as well as crash recovery if the tables become damaged, and mysqldump for backing up the database or copying databases to another server. Clients communicate to the MySQL RDBMS through the interface or utilities. The client interface uses MySQL APIs for various different programming languages such as the C API, DBI API for Perl, PHP API, Java API, Python API, MySQL C++ API and Tcl. Query users interact with the MySQL RDBMS through a query interface that is mysql. mysql is a monitor (interactive program) that allows the query users to issue SQL statements to the server and view the results.

Logical Layer

The MySQL documentation gave an indication as to precisely how these modules could be further broken down into subsystems arranged in a layered hierarchy corresponding to the layered architecture in Garlan and Shaw. The following section details these subsystems and the interactions within them.

Query Processor

The vast majority of interactions in the system occur when a user wishes to view or manipulate the underlying data in storage. These queries, which are specified using a data-manipulation language (ie SQL), are parsed and optimized by a query processor. This processor, depicted in Figure 3 above, can be represented as pipeline and filter architecture in the sense of Garlan and Shaw where the result of the previous component becomes an input or requirement to the next component. The component architecture of the query processor will be explained below.

Embedded DML Precompiler

When a request is received from a client in the application layer, it is the responsibility of the embedded DML (Data Manipulation Language) precompiler to extract the relevant SQL statements embedded in the client API commands, or to translate the client commands into the corresponding SQL statements. This is the first step in the actual processing of a client application written in a programming language such as C++ or Perl, before compiling the SQL query. The client request could come from commands executed from an application interface (API), or an application program. This is prevalent in all general RDBMS's. MySQL has this component in order to process the MySQL client application request into the format that MySQL understands.

DDL Compiler

Requests to access the MySQL databases received from an administrator are processed by the DDL (Data Definition Language) compiler. The DDL compiler compiles the commands (which are SQL statements) to interact directly with the database. The administrator and administrative utilities do not expose an interface, and hence execute directly to the MySQL server. Therefore, the embedded DML precompiler does not process it, and this explains the need for a DDL compiler.

Query Parser

After the relevant SQL query statements are obtained from deciphering the client request or the administrative request, the next step involves parsing the MySQL query. In this stage, the objective of the query parser is to create a parse tree structure based on the query so that it can be easily understood by the other components later in the pipeline.

Query Preprocessor

The query parse tree, as obtained from the query parser, is then used by the query preprocessor to check the SQL syntax and check the semantics of the MySQL query to determine if the query is valid. If it is a valid query, then the query progresses down the pipeline. If not, then the query does not proceed and the client is notified of the query processing error.

Security/Integration Manager

Once the MySQL query is deemed to be valid, the MySQL server needs to check the access control list for the client. This is the role of the security integration manager which checks to see if the client has access to connecting to that particular MySQL database and whether he/she has table and record privileges. In this case,this prevents malicious users from accessing particular tables and records in the database and causing havoc inthe process.

Query Optimizer

After determining that the client has the proper permissions to access the specific table in the database, the query is then subjected to optimization. MySQL uses the query optimizer for executing SQL queries as fast as possible. As a result, this is the reason why the performance of MySQL is fast compared to other RDBMS's. The task of the MySQL query optimizer is to analyze the processed query to see if it can take advantage of any optimizations that will allow it to process the query more quickly. MySQL query optimizer uses indexes whenever possible and uses the most restrictive index in order to first eliminate as many rows as possible as soon as possible. Queries can be processed more quickly if the most restrictive test can be done first.

Execution Engine

Once the MySQL query optimizer has optimized the MySQL query, the query can then be executed against the database. This is performed by the query execution engine, which then proceeds to execute the SQL statements and access the physical layer of the MySQL database from Figure. As well the database administrator can execute commands on the database to perform specific tasks such as repair, recovery, copying and backup, which it receives from the DDL compiler.

Scalability/Evolvability

The layered architecture of the logical layer of the MySQL RDBMS supports the evolvability of the system. If the underlying pipeline of the query processor changes, the other layers in the RDBMS are not affected. This is because the architecture has minimal sub-component interactions to the layers above and below it, as can be seen from the architecture diagram. The only sub-components in the query processor that interact with other layers is the embedded DML preprocessor, DDL compiler and query parser (which are at the beginning stages of the pipeline) and the execution engine (end of the pipeline). Hence, if the query preprocessor security/integration manager and/or query optimizer is replaced, this does not affect the outcome of the query processor.

Transaction Management

Transaction Manager

As of version MySQL 4.0.x, support was added for transactions in MySQL. A transaction is a single unit of work that has one or more MySQL commands in it. The transaction manager is responsible for making sure that the transaction is logged and executed atomically. It does so through the aid of the log manager and the concurrency-control manager. Moreover, the transaction manager is also responsible for resolving any deadlock situations that occur. This situation can occur when two transactions cannot continue because they each have some data that the other needs to proceed. Furthermore, the transaction manager is responsible for issuing the COMMIT and the ROLLBACK SQL commands. The COMMIT command commits to performing a transaction. Thus, a transaction is incomplete until it is committed to. The ROLLBACK command is used when a crash occurs during the execution of a transaction. If a transaction were left incomplete, the ROLLBACK command would undo all changes made by that transaction. The result of executing this command is restoring the database to its last stable state.

Concurrency- Control Manager

The concurrency-control manager is responsible for making sure that transactions are executed separately and independently. It does so by acquiring locks, from the locking table that is stored in memory, on appropriate pieces of data in the database from the resource manager. Once the lock is acquired, only the operations in one transaction can manipulate the data. If a different transaction tries to manipulate the same locked data, the concurrency-control manager rejects the request until the first transaction is complete.

Recovery Management

Log Manager

The log manager is responsible for logging e very operation executed in the database. It does so by storing the log on disk through the buffer manager. The operations in the log are stored as MySQL commands. Thus, in the case of a system crash, executing every command in the log will bring back the database to its last stable state.

Recovery Manager

The recovery manager is responsible for restoring the database to its last stable state. It does so by using the log for the database, which is acquired from the buffer manager, and executing each operation in the log.Since the log manager logs all operations performed on the database (from the beginning of the database’s life), executing each command in the log file would recover the database to its last stable state.

Storage Management

Storage is physically done on some type of secondary storage, however dynamic access of this medium is not practical. Thus, all work is done through a number of buffers. The buffers reside in main and virtual memory and are managed by a Buffer Manager. This manager works in conjunction with two other manager entities related to storage: the Resource Manager and the Storage Manager.

Storage Manager

At the lowest level exists the Storage Manager. The role of the Storage Manager is to mediate requests between the Buffer Manager and secondary storage. The Storage Manager makes requests through the underlying disk controller (and sometimes the operating system) to retrieve data from the physical disk and reports them back to the Buffer Manager.

Buffer Manager

The role of the Buffer Manager is to allocate memory resources for the use of viewing and manipulating data. The Buffer Manager takes in formatted requests and decides how much memory to allocate per buffer and how many buffers to allocate per request. All requests are made from the Resource Manager.

Resource Manager

The purpose of the Resource Manager is to accept requests from the execution engine, put them into table requests, and request the tables from the Buffer Manager. The Resource Manager receives references to data within memory from the Buffer Manager and returns this data to the upper layers.

Crash Scenario

One scenario involves the crashing of a MySQL database with the database administrator having to recover this database.

1) The database administrator, at the application layer, uses the isamchk or myisamchk utilities that have a component to perform crash recovery.

2) This crash recovery utility is interpreted in the logical layer of the MySQL RDBMS by the query processor. Specifically, the command to execute a recovery is translated by the DDL compiler and is executed by the execution engine.

3) The execution engine passes the control that a recovery needs to be done on a particular database to the recovery manager of the recovery management sublayer.

4) The recovery manager finds out that it needs to retrieve the log file for that particular crashed database and it tells the resource manager in the storage management sublayer to grab the log file.

5) The log file pertaining to the crashed database from the physical storage is passed to the buffer manager.

6) The resource manager passes the log over to the recovery manager

7) The recovery manager starts from the beginning of the log and interprets each SQL command in the log.

8) Each SQL command in the log from beginning to end is executed on the database by the execution engine in the query processor.

Installing mysql on solaris

Downloads

Please obtain a copy of MySQL 5.0 from http://dev.mysql.com/downloads/mysql/5.0.html. For the Solaris 10 MySQL packages, please scroll the page down the section of “Solaris (pkgadd package) downloads”. Choose the appropriate processor architecture of the package (either x86 or SPARC). Download the packages (both Standard and Max) and save them in an appropriate directory. I will use “/usr/files” as the directory where the mysql-xxx.pkg.gz files were placed through out the tutorial (Please take note that the xxx is the version number and is to be replaced by the actual text in the file name).

Preparation

Please perform the following as the root user. We have to make sure that any other previously running copies of MySQL are to be uninstalled from the system.

1. login as “root”
2. To list all the packages, type: "pkginfo | grep mysql" at the shell.
3. If you see any listed packages, you may remove them by typing “pkgrm ” The names of the packages are list at the second column of after executing pkginfo.
4. Change the directory to the place where you’ve downloaded the mysql-xxx.pkg.gz files. (e.g. “cd /usr/files”). If the files were compressed by gzip and you can see the .gz extension at the end of the files, you may decompress them by typing “gzip –d mysql-xxx.pkg.gz”. Decompress the downloaded mysql-xxx.pkg.gz files.

Installation

Follow these steps to perform the installation (perform as "root"):

1. Create the mysql group by typing “groupadd mysql”.
2. Create the mysql user by typing “useradd -g mysql mysql”.
3. Change the directory where the MySQL packages were placed. “cd /usr/files”.
4. First install the “Standard” package by typing: “pkgadd -d mysql-standard-xxx.pkg”. Just accept the default install directory (/opt/mysql) when prompted and go through the installation process.
5. Next, install the “Max” package by typing: “pkgadd –d mysql-max-xxx.pkg”. Just accept the default directory when prompted and go through the installation process.
6. The MySQL should have been installed in “/opt/mysql/mysql”.
7. Change the directory to /etc/init.d and edit the "mysql" file with any text editor. Locate the line which states: "datadir=". Change the line to "datadir=/opt/mysql/mysql/data". Save the changes.
   
8. After the installation, change the path to "/opt" and type this: “chown -R mysql:mysql mysql”. This is to change the ownership of the whole mysql directory.

Initiallizing the Database

1. Change the operating user from “root” to “mysql” by tying: “su mysql”.
2. Change the working directory to “/opt/mysql/mysql/scripts” by typing: “cd /opt/mysql/mysql/scripts”.
3. Execute the mysql_db_install script by typing: “./mysql_install_db --user=mysql --ldata=/opt/mysql/mysql/data”.
4. Change the working directory to "/opt/mysql/mysql/bin”.
5. Start the database by typing: “./mysqld_safe --datadir=/opt/mysql/mysql/data --user=mysql &”.

Connecting to MySQL

Try to connect to the MySQL database by typing “mysql” as the root user. You should be able to see the “mysql>” prompt for the successful connection. The only user which could connect to the database now is the root user and it doesn't require a password. For more information on user account management, please visit http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html.

Mysql Backup and Recovery

Logical Versus Physical (Raw) Backups

Logical backups save information represented as logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements or delimited-text files). Physical backups consist of raw copies of the directories and files that store database contents.

Logical backup methods have these characteristics:

• The backup is done by querying the MySQL server to obtain database structure and content information.
• Backup is slower than physical methods because the server must access database information and convert it to logical format. If the output is written on the client side, the server must also send it to the backup program.
• Output is larger than for physical backup, particularly when saved in text format.
• Backup and restore granularity is available at the server level (all databases), database level (all tables in a particular database), or table level. This is true regardless of storage engine.
• The backup does not include log or configuration files, or other database-related files that are not part of databases.
• Backups stored in logical format are machine independent and highly portable.
• Logical backups are performed with the MySQL server running. The server is not taken offline.
• Logical backup tools include the mysqldump program and the SELECT ... INTO OUTFILE statement. These work for any storage engine, even MEMORY.
• To restore logical backups, SQL-format dump files can be processed using the mysql client. To load delimited-text files, use the LOAD DATA INFILE statement or the mysqlimport client.

Physical backup methods have these characteristics:

• The backup consists of exact copies of database directories and files. Typically this is a copy of all or part of the MySQL data directory. Data from MEMORY tables cannot be backed up this way because their contents are not stored on disk.
• Physical backup methods are faster than logical because they involve only file copying without conversion.
• Output is more compact than for logical backup.
• Backup and restore granularity ranges from the level of the entire data directory down to the level of individual files. This may or may not provide for table-level granularity, depending on storage engine. (Each MyISAM table corresponds uniquely to a set of files, but an InnoDB table shares file storage with other InnoDB tables.)
• In addition to databases, the backup can include any related files such as log or configuration files.
• Backups are portable only to other machines that have identical or similar hardware characteristics.
• Backups can be performed while the MySQL server is not running. If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup.
• Physical backup tools include file system-level commands (such as cp, scp, tar, rsync), mysqlhotcopy for MyISAM tables, ibbackup for InnoDB tables, or START BACKUP for NDB tables.
• For restore, files copied at the file system level or with mysqlhotcopy can be copied back to their original locations with file system commands; ibbackup restores InnoDB tables, and ndb_restore restores NDB tables.

Online Versus Offline Backups

Online backups take place while the MySQL server is running so that the database information can be obtained from the server. Offline backups take place while the server is stopped. This distinction can also be described as “hot” versus “cold” backups; a “warm” backup is one where the server remains running but locked against modifying data while you access database files externally.

Online backup methods have these characteristics:

• The backup is less intrusive to other clients, which can connect to the MySQL server during the backup and may be able to access data depending on what operations they need to perform.
• Care must be taken to impose appropriate locking so that data modifications do not take place that would compromise backup integrity.

Offline backup methods have these characteristics:

• Clients can be affected adversely because the server is unavailable during backup.
• The backup procedure is simpler because there is no possibility of interference from client activity.

A similar distinction between online and offline applies for recovery operations, and similar characteristics apply. However, it is more likely that clients will be affected for online recovery than for online backup because recovery requires stronger locking. During backup, clients might be able to read data while it is being backed up. Recovery modifies data and does not just read it, so clients must be prevented from accessing data while it is being restored.

Local Versus Remote Backups

A local backup is performed on the same host where the MySQL server runs, whereas a remote backup is done from a different host. For some types of backups, the backup can be initiated from a remote host even if the output is written locally on the server. host.

• mysqldump can connect to local or remote servers. For SQL output (CREATE and INSERT statements), local or remote dumps can be done and generate output on the client. For delimited-text output (with the --tab option), data files are created on the server host.
• mysqlhotcopy performs only local backups: It connects to the server to lock it against data modifications and then copies local table files.
• SELECT ... INTO OUTFILE can be initiated from a local or remote client host, but the output file is created on the server host.
• Physical backup methods typically are initiated locally on the MySQL server host so that the server can be taken offline, although the destination for copied files might be remote.

Full Versus Incremental Backups

• A full backup includes all data managed by a MySQL server at a given point in time. An incremental backup consists of the changes made to the data during a given time span (from one point in time to another). MySQL has different ways to perform full backups, such as those described earlier in this section. Incremental backups are made possible by enabling the server's binary log, which the server uses to record data changes.

Full Versus Point-in-Time (Incremental) Recovery

A full recovery restores all data from a full backup. This restores the server instance to the state that it had when the backup was made. If that state is not sufficiently current, a full recovery can be followed by recovery of incremental backups made since the full backup, to bring the server to a more up-to-date state.

Incremental recovery is recovery of changes made during a given time span. This is also called point-in-time recovery because it makes a server's state current up to a given time. Point-in-time recovery is based on the binary log and typically follows a full recovery from the backup files that restores the server to its state when the backup was made. Then the data changes written in the binary log files are applied as incremental recovery to redo data modifications and bring the server up to the desired point in time

Making Backups by Copying Table Files

For storage engines that represent each table using its own files, tables can be backed up by copying those files. For example, MyISAM tables are stored as files, so it is easy to do a backup by copying files (*.frm, *.MYD, and *.MYI files). To get a consistent backup, stop the server or do a LOCK TABLES on the relevant tables followed by FLUSH TABLES for the tables. See Section 12.4.5, “LOCK TABLES and UNLOCK TABLES Syntax”, and Section 12.5.6.2, “FLUSH Syntax”. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

Making Delimited-Text File Backups

To create a text file containing a table's data, you can use SELECT * INTO OUTFILE 'file_name' FROM tbl_name. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because allowing files to be overwritten constitutes a security risk. See Section 12.2.8, “SELECT Syntax”. This method works for any kind of data file, but saves only table data, not the table structure.

Another way to create text data files (along with files containing CREATE TABLE statements for the backed up tables) is to use mysqldump with the --tab option.

To reload the output data file, use LOAD DATA INFILE or mysqlimport.

Making Backups with mysqldump or mysqlhotcopy

The mysqldump program and the mysqlhotcopy script can make backups. mysqldump is more general because it can back up all kinds of tables. mysqlhotcopy works only with some storage engines.

Create a full backup of your database using mysqldump:

shell> mysqldump db_name > dump_file

shell> mysqldump --tab=/path/to/some/dir db_name

The first command dumps the database to the named file as CREATE TABLE and INSERT statements. The second command creates two files per table in the named output directory. One file contains the table contents as tab-delimited text. Other other contains a CREATE TABLE statement for the table.

Create a full backup of your database using mysqlhotcopy:

shell> mysqlhotcopy db_name /path/to/some/dir

You can also create a binary backup simply by copying all table files, as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that table file copying methods do not work if your database contains InnoDB tables. mysqlhotcopy does not work for InnoDB tables because InnoDB does not necessarily store table contents in database directories. Also, even if the server is not actively updating data, InnoDB may still have modified data cached in memory and not flushed to disk.

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables using the --single-transaction option to mysqldump.

Making Incremental Backups by Enabling the Binary Log

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 6.4, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog..

Making Backups Using Replication Slaves

If you are backing up a slave replication server, you should back up its master.info and relay-log.info files when you back up the slave's databases, regardless of the backup method you choose. These information files are always needed to resume replication after you restore the slave's data. If your slave is replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the --slave-load-tmpdir option. (This location defaults to the value of the tmpdir system variable if not specified.) The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations.

If you have performance problems with your master server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master.

Recovering Corrupt Tables

If you have to restore MyISAM tables that have become corrupt, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure. It is assumed that you have enabled binary logging by starting MySQL with the --log-bin option.

1. Restore the table from a mysqldump backup or binary backup.
2. Execute the following command to re-run the updates in the binary logs:
3. shell> mysqlbinlog binlog.[0-9]* | mysql

In some cases, you may want to re-run only certain binary logs, from certain positions (usually you want to re-run all binary logs from the date of the restored backup, excepting possibly some incorrect statements).

Making Backups Using a File System Snapshot

If you are using a Veritas file system, you can make a backup like this:

1. From a client program, execute FLUSH TABLES WITH READ LOCK.
2. From another shell, execute mount vxfs snapshot.
3. From the first client, execute UNLOCK TABLES.
4. Copy files from the snapshot.
5. Unmount the snapshot.

Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS.

Backup Policy

Assume that we make a full backup of all our InnoDB tables in all databases using the following command on Sunday at 1 p.m., when load is low:

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

The resulting .sql file produced by mysqldump contains a set of SQL INSERT statements that can be used to reload the dumped tables at a later time.

Assume that we make a full backup of all our InnoDB tables in all databases using the following command on Sunday at 1 p.m., when load is low:

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

The resulting .sql file produced by mysqldump contains a set of SQL INSERT statements that can be used to reload the dumped tables at a later time.

This backup operation acquires a global read lock on all tables at the beginning of the dump (using FLUSH TABLES WITH READ LOCK). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables.

It was assumed earlier that the tables to back up are InnoDB tables, so --single-transaction uses a consistent read and guarantees that data seen by mysqldump does not change. (Changes made by other clients to InnoDB tables are not seen by the mysqldump process.) If the backup operation includes nontransactional tables, consistency requires that they do not change during the backup. For example, for the MyISAM tables in the mysql database, there must be no administrative changes to MySQL accounts during the backup.

Full backups are necessary, but it is not always convenient to create them. They produce large backup files and take time to generate. They are not optimal in the sense that each successive full backup includes all data, even that part that has not changed since the previous full backup. It is more efficient to make an initial full backup, and then to make incremental backups. The incremental backups are smaller and take less time to produce. The tradeoff is that, at recovery time, you cannot restore your data just by reloading the full backup. You must also process the incremental backups to recover the incremental changes.

To make incremental backups, we need to save the incremental changes. In MySQL, these changes are represented in the binary log, so the MySQL server should always be started with the --log-bin option to enable that log. With binary logging enabled, the server writes each data change into a file while it updates data. Looking at the data directory of a MySQL server that was started with the --log-bin option and that has been running for some days, we find these MySQL binary log files:

-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001

-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002

-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003

-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004

-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005

-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006

-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index

Each time it restarts, the MySQL server creates a new binary log file using the next number in the sequence. While the server is running, you can also tell it to close the current binary log file and begin a new one manually by issuing a FLUSH LOGS SQL statement or with a mysqladmin flush-logs command. mysqldump also has an option to flush the logs. The .index file in the data directory contains the list of all MySQL binary logs in the directory.

The MySQL binary logs are important for recovery because they form the set of incremental backups. If you make sure to flush the logs when you make your full backup, the binary log files created afterward contain all the data changes made since the backup. Let's modify the previous mysqldump command a bit so that it flushes the MySQL binary logs at the moment of the full backup, and so that the dump file contains the name of the new current binary log:

shell> mysqldump --single-transaction --flush-logs --master-data=2 \

--all-databases > backup_sunday_1_PM.sql

After executing this command, the data directory contains a new binary log file, gbichot2-bin.000007, because the --flush-logs option causes the server to flush its logs. The --master-data option causes mysqldump to write binary log information to its output, so the resulting .sql dump file includes these lines:

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

Because the mysqldump command made a full backup, those lines mean two things:

Using Backups for Recovery

Now, suppose that we have a catastrophic crash on Wednesday at 8 a.m. that requires recovery from backups. To recover, first we restore the last full backup we have (the one from Sunday 1 p.m.). The full backup file is just a set of SQL statements, so restoring it is very easy:

shell> mysql <>

At this point, the data is restored to its state as of Sunday 1 p.m.. To restore the changes made since then, we must use the incremental backups; that is, the gbichot2-bin.000007 and gbichot2-bin.000008 binary log files. Fetch the files if necessary from where they were backed up, and then process their contents like this:

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

We now have recovered the data to its state as of Tuesday 1 p.m., but still are missing the changes from that date to the date of the crash. To not lose them, we would have needed to have the MySQL server store its MySQL binary logs into a safe location (RAID disks, SAN, ...) different from the place where it stores its data files, so that these logs were not on the destroyed disk. (That is, we can start the server with a --log-bin option that specifies a location on a different physical device from the one on which the data directory resides. That way, the logs are safe even if the device containing the directory is lost.) If we had done this, we would have the gbichot2-bin.000009 file (and any subsequent files) at hand, and we could apply them using mysqlbinlog and mysql to restore the most recent data changes with no loss up to the moment of the crash