Jump to Navigation


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:

MySQL: Stored programs quick reference

This is a quick reference guide for writing MySQL stored programs. It is assumed you have some experience with similar programming languages (for example, Oracle PL/SQL). This has been written for MySQL 5.6.

Defining stored programs

There are four kinds of stored programs:

  • Procedures
  • Functions
  • Triggers
  • Events

Functions can be called from SQL queries.

MySQL limitations

I am writing some queries and stored procedure on MySQL these days. Coming from an Oracle background that can be somewhat frustrating at times. Some query constructs are not available, but also the procedural language is quite limited compared to PL/SQL.

I must say, most limitations are well documented. That means that at least you have some closure when things do not work out. Examples I encountered are:


Subscribe to RSS - MySQL

by Dr. Radut