MySQL– Backups

Different types of Backups for MySQL databases

Logical Backup

It is done by dumping the contents of database into text files containing SQL statements which can be used to rebuild the database. They can be used even to build it on a host with different architecture and with different engine. But they are generally slower than the raw kind, for both backup and recovery operations. And they can be larger than the actual data too. It can be performed using the mysqldump utility.

Raw (Binary) Backup

It involves only file copy operations, so can be very fast to perform backup or recoveries. The engine type of tables cannot be changed in this method, as it preserves the actual data format. Depending on engine type, it can be used to take Cold, Hot or Warm backups. Various tools are available for this operation, which will be covered in a slide ahead.

Snapshot Based Backup

This kind of backup uses external utilities to take file system snapshot of MySQL. For InnoDB, a hot backup can be performed using this, and for other engines, a warm backup.

Replication-Based Backup

A copy of the Primary installation can be maintained on a separate server using a Replication setup. Using this method, an exact replica of the databases can be maintained separately, and can be used for backup purposes. But it is comparatively an expensive option.

Incremental Backup

The binary logs, which contain a record of all changes done on the database can be backed up for the  purpose of doing point-in-time-recovery. Using tools like mysqlbinlog, databases can be recovered from Binary logs till a certain point in time, or certain log position also. Binary logging should be enabled on MySQL servers

Backup Tools

Though backups can be taken without the use of any tool, there are some tools designed specifically to make the operation easier. Some of them have been described here

mysqlhotcopy

Provided with the MySQL distribution, this is useful to perform raw backup of MyISAM type tables only. Contrary to its name, it is not exactly a “hot” backup, as database is not fully available during the operation, but is available only for read. It is locked and cannot be altered. So, it is also called a “warm” backup.

mysqldump

It is provided with the MySQL distribution. It helps in taking logical backups for any kind of engine. Our  current masters are designed to automatically enable this backup in cron at the time of installation.

MySQL Administrator

It is available as a separate GUI download from MySQL, and can be used to perform logical backups. It can also be used with any database engine, and has some tracking capabilities also.

InnoDB Hot Backup (ibbackup)

It is a commercial product available from InnoDB. It performs raw backups of MySQL databases using InnoDB engine only. It is in fact a real “hot backup” as databases are completely available for read and update during the operation.

Third Party Tools

Among the many commercially available third party tools available, there is one called Zmanda Recovery Manager, that can perform both logical and raw backups for all engine types. It can do both warm and hot backups, and can drive Replication and Snapshot based backups too. It takes consistent backups, and has extensive reporting and Tracking capabilities

MySQL–InnoDB Storage Engine

• Developed by Innobase, which is a subsidiary of Oracle.

• Each table is represented by a .frm (containing table structure) file inside database directory.

• The InnoDB tablespace, which is a set of one or more files, is used for storing table contents. It is usually a shared tablespace for InnoDB tables common for all databases, but can also be configured to have each table with own tablespace. The shared tablespace is created by default, in the data directory (eg by the name ibdata1). It is defined in the [mysqld] section of the file /etc/my.cnf using parameter innodb_data_file_path

e.g. innodb_data_file_path = ibdata1:10M:autoextend

where filename is ibdata1, its size being 10M and in autoextend ON mode.

• There are InnoDB log files that record transaction activity, located in the logs directory with default names as ib_logfile0, ib_logfile1 etc. Some innodb related parameters are

innodb_data_home_dir = /data/mysql/5.0.75/data

innodb_log_group_home_dir = /data/mysql/5.0.75/logs

innodb_buffer_pool_size = 16M

innodb_log_file_size = 5M

innodb_log_buffer_size = 8M

• Supports transactions with COMMIT, SAVEPOINT and ROLLBACK.

• InnoDB also maintains a Buffer Pool and Log Buffer which help to improve performance and enable crash recovery of tables

 

PROS

• ACID compliant

• Support for crash recovery

• Row level locking

• Support for foreign keys

• B+Tree and Hash Indexes support

• Adcanced memory cache mechanism

CONS

• No Full Text index support

• Requires more disk and memory resources

MySQL–MyISAM Storage Engine

MyISAM is the default in MySQL. Each table in it is represented by three files:

• format file – which stores the table definition (eg mytable.frm)

• data file – which stores the table row contentcs (eg mytable.MYD)

• index file – which stores the indexes of that table (eg mytable.MYI )

It has the capability to store rows in three formats

• Fixed Row format – Constant row size for all rows.

• Dynamic Row format – Rows take varying amounts of space

• Compressed format – Tables are packed and made read-only. Optimized for quick retrieval.

PROS

• Low Storage Costs

• Support for BTree, Fulltext Indexes

• Very fast insert and query performance

CONS

• Table level locking

• No support for foreign key constraints

• Does not support transactions

 

Further Reference: http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html

MySQL – Storage Engine

A Storage Engine is the underlying component that an RDBMS uses to create, retrieve, update and delete data from a databases. It is a low level engine inside the database Server that takes care of storing and retrieving data from tables. A client does retrieving or changing of data in tables by sending requests in the form of SQL statements, which are processed by server using a two-tier model. MySQL provides and maintains several Storage engines, and is also compatible with Many third party engines.

The SQL Tier (tier-1) is mostly free of any storage engine dependencies, but there are some exceptions to that, like

• The CREATE TABLE and ALTER TABLE commands have an option of specifying ENGINE.

• Some Index types are available only for certain engine types, for example, only MyISAM supports full-text indexes

• COMMIT and ROLLBACK have an effect only for tables managed by transactional storage engines, like InnoDB

 

Examples of MySQL developed Storage Engines

• MyISAM

• Falcon

• NDB/Cluster

• Archive

• Memory

 

Examples of third party Storage Engines

• InnoDB

• solidDB

• Nitro

 

The most common Storage Engines are

• InnoDB

• MyISAM

• Memory

MySQL– Log Generation

MySQL server can write information to several type of log files. The logs record various types of  Information about the server. None of them are enabled by default.

General Query Logs

Contain record of every SQL statement executed on the server, and client connection details, because of which, they can grow up to enormous sizes, and should only be enabled for troubleshooting purposes. It can be turned on either by using the –log & –log-output option during startup, or dynamically using general_log and general_log_file options (v5.1 onwards). If enabled, its default location is in the data directory. It can also be optionally set to output to a table inside database. To see if it is enabled, use command show global variables like ‘general_log’; or check the config file (/etc/my.cnf) or check the parameters in the process running.

Slow Query Logs

Contain the text of queries that take longer than a particular time (set by parameter long_query_time in secs) to complete. The server writes to this log after the execution is complete. To enable it, use the parameter –log-slow-queries during startup. If enabled, its default location is in the data directory. To log queries that are not using indexes, use the parameter –log-queries-not-using-indexes. To see if it is enabled, use command show global variables like ‘general_log’; or check the config file (/etc/my.cnf) or check the parameters in the process running.

Error Logs

Some diagnostic messages produced by the server, that include unexpected shutdown of mysqld, or unrecognized startup options, Storage Engine initialization failure, or problems related to replication etc. are logged in this file. It can be enabled using the –log-error option during startup. The default error log name is host_name.err created in its data directory.

Binary Logs

Contain a record of all DML and DDL statements executed on the server data. These logs are written only after they are executed, and if they are part of a multiple-statement transaction, they are written as a group after the transaction has been committed. It can be enabled using the –log-bin option during startup. The contents of binary logs can be viewed using a program called mysqlbinlog. Binary Logging can be of two kinds:
- Statement Based Binary Logging – that contain the SQL statements that change data, and not the row values. Thus, making it smaller in size as compared to the other type.
- Row Based Binary Logging – contain the actual row values that are updated by a DML, because of which, it is much bigger in size. But it can be helpful in cases of Replication where non-deterministic functions are used, and which can cause problems in case of Statement Based Logging.
- Mixed Option – This will generally perform the Statement Based Binary logging, but would automatically switch to Row Based logging whenever appropriate. This can be enabled using the parameter binlog_format even dynamically while the server is running.

MySQL–Files Layout

The MySQL server uses Disk Space for storing various kinds of files:

1. Database directories – Each database corresponds to a single directory under the data directory

2. Format files (.frm files) – Every table and view has its own .frm file located in the appropriate database directory, and containing its description (definition)

3. Log Files – Used for monitoring, recovery etc and stored in Logs and Data folder (depending on log type)

4. Triggers – Stored in database directory along with affected table

5. System database – called mysql, containing details like user information, grants etc..

An example layout of standard Architecture:

MySQL–Client / Server Architecture

MySQL operates in a networked environment using a Client/Server Architecture. A MySQL installation

has the following major components

• MySQL Server

• Client Programs

• Non-Client Utilities

MySQL Server

Or mysqld is the database server program that manages access to the actual databases on disk and in memory by supporting multiple simultaneous client connections through  various protocols (TCP/IP, Unix socket. Shared memory, Named pipes). A host can have multiple mysqld instances running simultaneously.

Client Programs

Are used for communicating with the server to manipulate databases that the server manages. For example, mysqldump, mysqlimport, mysqladmin etc.

Non-Client Utilities

Are programs that act independently of the server, mysqld, to perform various operations like checking, repairing and compressing tables. For example, myisamcheck and myisampack.