Jump to Navigation

MySQL

MySQL: Check table engines in use

To determine whether tables in your MySQL database use MyISAM or InnoDB, you can use the following query:

SELECT TABLE_SCHEMA DB,ENGINE,COUNT(1)
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA,ENGINE
ORDER BY
TABLE_SCHEMA,ENGINE;

This shows all engines used for each database. Most tables will use either MyISAM or INNODB, but there are other engines, like MEMORY, CSV and PERFORMANCE_SCHEMA.

MySQL: Administering replication

Replication threads

Replication uses two slave threads. One is the I/O thread: it connects to the master server, downloads transactions and writes them to the relay logs. The other is the SQL thread: it reads transaction from the relay logs and executes them.

Provided the network connection between master and slave is stable, any bottlenecks are in the SQL thread. On a very busy system it can lag behind the I/O thread.

MySQL: Binlogs

You have to use binlogs for three reasons: when this server is a master to a slave, when this server is part of a replication structure using GTIDs or when you want to be able to do point-in-time recoveries.

A slave uses the binlogs of the master for replication. If you are not using GTIDs, the master binlogs are read sequentially and each non-filtered transaction is applied. The position in the master binlogs of the slave is thus very important.

MySQL: Restores, binlogs and GTIDs

Part of a backup strategy is deciding how to handle the binlogs. When using GTIDs this is even more important. This page assumes you have enabled the binlogs in the server you backup.

Binlog handling by MySQL

When MySQL starts, it looks for existing binlogs.

Whether it finds existing binlogs or not, it will start a new binlog file. It will get number 000001 if no binlogs were found, or the next highest number if there are already binlogs.

MySQL: Roll forward (PITR)

After restoring a backup it is possible to roll forward your databases by applying the binlogs. This means that all committed transactions between two points in time are executed again. A roll forward  can happen on the original server the binlogs were generated or on some other server.

A roll forward is executed by translating the binlogs to plain SQL statements and executing them. This means that if you try to apply binlogs to a database some statements may fail, if the start situation was not entirely correct.

MySQL: Backups using xtrabackup

This is a guide about creating MySQL backups by using Percona xtrabackup.

Using xtrabackup backup has the following characteristics:

Pages

Subscribe to RSS - MySQL


by Dr. Radut