22.1. MySQL ArchitectureMySQL 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 ElementsThe architectural elements in the following sections are the same regardless of which storage engine you choose. 22.1.1.1. The power user's viewFrom 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:
22.1.1.2. InstancesA 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 scriptsMySQL 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 tablespacesThe 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.
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 objectsMySQL 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 logThe 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]
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 EngineThe 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 EngineIf 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.
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.
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/ | +---------------+-----------------+
22.1.3.1. Concerns about InnoDBInnoDB 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. TransactionsA 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. TablespaceA 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. DatafileA 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 groupThe 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 logInnoDB 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 EnginesThere are a number of other storage engines available in MySQL. Some are available as of this writing, and others are in development.
|