Jump to Navigation

Recently created

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:

MySQL: Backups with file system snapshots

This is a guide about creating MySQL backups by using file system snapshots.

Using snapshot backup has the following characteristics:

MySQL: Backups by copying files

This is a guide about creating MySQL backups by copying files.

Using a file copy backup has the following characteristics:

  • Offline backup;
  • Full backup;
  • All databases and tables are always backed up and restored;
  • For both MyISAM and InnoDB tables;
  • Can be run remotely by using a ssh connection.

Creating a backup

Before creating the full backup, shutdown the database as cleanly as possible.

MySQL: files

This is a list of files you are likely to find in you MySQL data directory (often /var/lib/mysql).

Database files

Within the data directory there will be one subdirectory for each defined database, containing the following files:

MySQL: Backups with mysqldump

This is a guide about creating MySQL backups using mysqldump.

Using mysqldump has the following characteristics:

  • Online backup;
  • Full backup;
  • Selection of databases and tables;
  • For both MyISAM and InnoDB tables;
  • Can be run remotely;
  • Read lock during the dump, unless only InnoDB tables are dumped.

Creating a backup

To create a consistent, full backup, on a database with MyISAM and/or InnoDB tables, use:

Pages

Subscribe to Recently created


by Dr. Radut