Section 22.1. MySQL Architecture


22.1. MySQL Architecture

MySQL architecture, especially the storage and backup part, is driven by the storage engine that you choose, but all storage engines share certain architectural elements.

22.1.1. Shared Architectural Elements

The architectural elements in the following sections are the same regardless of which storage engine you choose.

22.1.1.1. The power user's view

From the power user's standpoint, MySQL is like any other database. The following terms mean the same in MySQL as they do in any other relational database:

Database
Table
Index
Row
Attribute
Partition
22.1.1.2. Instances

A MySQL instance is the same as any other database instance. It controls access to one or more MySQL databases. A MySQL instance is available after the mysqld process is started. Multiple instances of MySQL are possible as long as each has separate data directories and listens on different TCP ports and sockets. The MySQL instance manager can be used to monitor and manage these instances.

22.1.1.3. Startup scripts

MySQL can be started via any script, but MySQL does provide a few scripts for you. The included mysqld_safe script has historically been the recommended way to start mysqld. As the name of the script implies, it adds some safety features such as restarting the server when an error occurs and logging runtime information to an error logfile. There is also now the mysql.server script that's automatically installed in the appropriate place in Linux systems and the Instance Manager that can be used to manage multiple instances. (The Instance Manager is scheduled to replace mysqld_safe at some point.)

Also important is the my.cnf file, which is typically stored in /etc on Unix/Linux systems. It is an optional, although often used, file that stores a number of startup options for MySQL databases.

22.1.1.4. Databases and tablespaces

The show databases command can be used to obtain a list of all databases within the instance. This information can then be used for backup purposes.

This command gets its information from a directory listing, not a query of a table in the mysql database.


If you're using the InnoDB storage engine, you can also query InnoDB for a list of tablespaces:

> show variables like 'innodb_data_file_path%' +-----------------------+------------------------+ | Variable_name         | Value                  | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:10M:autoextend | +-----------------------+------------------------+

22.1.1.5. Large objects

MySQL has special datatypes for storing large objects such as image files and large text data. The four datatypes for storing binary large objects, or BLOBs, are tinyblob, blob, mediumblob, and longblob, differing only in the maximum length of the values they can hold. The four datatypes for storing large text objects are tinytext, text, mediumtext, and longtext. All large object data is stored inside the database, so it is included in any database dumps.

22.1.1.6. Binary log

The binary log contains a history of SQL statements that changed data, and its primary purposes are point-in-time recovery and replication. The binary log can be applied against a consistent backup of a MySQL database to provide up-to-the-minute recovery of a database, and it is also used to send changes from a replication master to its slave(s).

The binary log contains only SQL statements, and SQL statements can be applied only against a consistent database. The binary log therefore cannot be used to bring the database back to a consistent state after a crash. It also cannot be used to create a consistent backup from an inconsistent backup of a running database, the way Oracle's redo logs can be used with the begin backup and end backup commands. This is why MySQL administrators do not refer to the binary log as a transaction log; they see crash recovery as the primary purpose of a transaction log.

The binary log is started by default in some systems and not in others. To use the binary log, you should specify a value for the log-bin= base_name option in the my.cnf file:

[mysqld] log-bin[=base_name]

It's considered best practice to specify a full pathname for base_name (for example, /backups/binarylog) so that you can send the logs to a directory owned by the user running mysqld. In this example, /backups should be writeable by the user running mysqld.


MySQL takes the base name you supplied, appends a number to it (for example, .001), and makes a series of binary logs with unique numeric extensions. For example, if you specify /backups/binarylog as base_name, it creates /backups/binarylog.001, then / backups/binarylog.002, and so on. You should keep these logs for point-in-time recovery. Make sure that they are backed up with your normal filesystem backups.

Make sure to investigate the max_binlog_cache_size variable, which is set to 4 GB by default. If this size is not large enough to contain all the statements from a given transaction, that transaction fails and is rolled back.

22.1.2. MyISAM Storage Engine

The MyISAM storage engine is the default storage engine in MySQL, and it is the required storage engine for system tables. The MyISAM storage engine, while very fast, is not ACID-compliant (atomicity, consistency, independence, durability). It has no concept of transactions, so only changes made in a single SQL statement are atomic. It does not support foreign keys and other features necessary to ensure referential integrity, so it doesn't pass the consistency test. It supports only table-level locking, so independence is also hard to achieve. Finally, if the server were to crash in the middle of a number of changes, it's highly possible that the database would be left in an inconsistent state, requiring a full recovery to continuemaking it not very durable. A good backup and the use of the binary log would allow you to recover up to the point of failure, but this would take a lot longer than the crash recovery process of an ACID-compliant database.

Each MyISAM table is stored in a series of three files. A file with a .FRM extension stores the table format. Data is stored in a file with an extension of .MYD (MYData), and the index file has a .MYI (MYIndex) extension. (You may find these filenames in upper- or lowercase.) Since tables cannot be distributed across multiple .MYD files, each table is limited to the maximum size of a file in the operating system. This used to be 2 GB, but that limitation has been removed from every major operating system.

MyISAM files are stored underneath the datadir. Each database creates a subdirectory underneath datadir and stores its files there. datadir can be determined using the following command:

> show variables like 'datadir'; +---------------+-----------------+ | Variable_name | Value           | +---------------+-----------------+ | datadir       | /var/lib/mysql/ | +---------------+-----------------+

If you wish to perform a backup of a MyISAM table, you should really obtain a read lock on it before doing so. This lock prevents any update, insert, or delete statements during the backup.

22.1.3. InnoDB Storage Engine

If your application calls for ACID-compliance, InnoDB is the storage engine for you. It is fully ACID-compliant, with full support for transactions, tablespaces, rollbacks, foreign keys, and hot backups. It is not as fast as the MyISAM storage engine, but for good reason. It takes time to perform all the checks necessary for ACID-compliance.

If you're currently using MyISAM tables and you wish to migrate them to InnoDB for integrity purposes, it's a relatively simple process. You can use alter table ... engine=innodb, or create an empty InnoDB table with identical definitions and insert the rows with insert into ... select * from .... Please consult the MySQL documentation for ways to speed up large tables importation.

For data integrity reasons, it is important on Linux to disable the write cache. The command hdparm -wO /dev/hda should work on most ATAPI disks.


If you'd like to use InnoDB for all nonsystem tables, simply add the line default-storage-engine=innodb to the [mysqld] section of your server option file.

Do not convert mysql system tables to the InnoDB storage engine. The system tables must always use the MyISAM storage engine.


Like MyISAM tables, InnoDB files are typically stored underneath the datadir. Each database creates a subdirectory underneath datadir and stores its files there. A default tablespace called ibdata1 is stored directly in datadir. If you are performing file-level backups, that file must be backed up as well. datadir can be determined using the following command:

> show variables like 'datadir'; +---------------+-----------------+ | Variable_name | Value           | +---------------+-----------------+ | datadir       | /var/lib/mysql/ | +---------------+-----------------+

Do not forget to back up the default tablespace file <datadir>/ibdata1. It is very common for people to back up the files in the database directories (e.g., <datadir>/<database>) and forget the default tablespace file in <datadir>. Also, do not assume that all datafiles are stored underneath datadir .


22.1.3.1. Concerns about InnoDB

InnoDB is a commercial product that was being licensed from Innobase, a Finland-based company. Oracle purchased Innobase in 2005, causing concerns that Oracle would stop development of the InnoDB storage engine. Those fears appear unfounded, especially if you read Oracle's official statement about the matter (http://www.oracle.com/innodb/index.html):

"Oracle has long been a supporter of open source software such as Linux and Apache," said Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology. "Innobase is an innovative small company that develops open source database technology. Oracle intends to continue developing the InnoDB technology and expand our commitment to open-source software. Oracle has already developed and contributed an open source clustered file system to Linux. We expect to make additional contributions in the future."

22.1.3.2. Transactions

A transaction in a MySQL database that's using the InnoDB storage engine is essentially the same as a transaction in any other database, except that InnoDB does not support all the same isolation levels that other databases do. You can create simple and complex transactions, and all updates are logged to the InnoDB transaction log and/or the rollback segment. The SQL statements are also recorded to the MySQL binary log. The transaction log and rollback segment are used for crash recovery of the database, and the binary log allows you to reply SQL statements against a consistent database backup to redo those SQL statements.

22.1.3.3. Tablespace

A tablespace in the InnoDB or NDB storage engines is similar to an Oracle tablespace; it consists of one or more datafiles. NDB tablespaces can be added via a create tablespace or alter tablespace command. (These commands were added in MySQL 5.1.) Currently, InnoDB tablespaces are added by editing the my.cnf file.

22.1.3.4. Datafile

A datafile is a part of an InnoDB tablespace. It is added to the tablespace via a create tablespace or an alter tablespace command.

22.1.3.5. Rollback segment or log group

The InnoDB rollback segment behaves similarly to the rollback segment in Oracle. It uses this information to perform undo operations that are needed in the rollback of a transaction. It also is used to build earlier versions of a row for a consistent read. Information is kept in the rollback segment until it is no longer needed for rollback. A log group performs this function in the NDB storage engine.

22.1.3.6. Transaction log

InnoDB has its own transaction log that records transactions for InnoDB tables, and is used with the rollback segment to ensure database consistency. However, it is not used to replay transactions after a consistent database backup, so InnoDB transaction logs are not archived. Therefore, innodb_log_archive is set to 0, which disables log archiving in InnoDB.

22.1.4. Other Storage Engines

There are a number of other storage engines available in MySQL. Some are available as of this writing, and others are in development.


NDB

NDB is a storage engine that typically resides entirely in RAM and is used by MySQL cluster. MySQL 5.1 introduced the ability to store nonindexed tables on columns using a log group for undo and a tablespace for storage. Other objects reside only in RAM and are written to disk during a checkpoint and during shutdown. The NDB storage engine also supports hot backups, which are covered later in the section "MySQL Cluster Hot Backup and Recovery." NDB cluster supports only the read committed transaction isolation level.


PBXT

PBXT is an independently developed storage engine. It will be available as a plug-in for MySQL at some point. You can read about it at http://forge.mysql. com/projects/view.php?id=43.


SolidDB

SolidDB is another pluggable storage engine, developed by Solid Information Technology. You can read about it at http://forge.mysql.com/projects/view.php?id=139.


Falcon

Falcon is the code name for a new transactional storage engine from MySQL. It is developed by Jim Starkey. You can read about it at http://forge.mysql.com/wiki/Falcon.




Backup & Recovery
Backup & Recovery: Inexpensive Backup Solutions for Open Systems
ISBN: 0596102461
EAN: 2147483647
Year: 2006
Pages: 237

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net