You are currently browsing the New Features In MySQL 6.x weblog archives for May, 2008.
Long ago I wrote a 100-page book on MySQL 5.0 stored procedures. It’s on our dev-zone pages. But it’s out of date. In 2006 I made some corrections for MySQL 5.1. It took me a long time to publish the revised book, but here it is: http://blogs.mysql.com/peterg/files/2008/05/book01.pdf.
Posted in MySQL 5.1 Features | 2 Comments »
Instructions for adding a new Unicode collation
By Alexander Barkov and Peter Gulutzan Copyright (c) 2007 by MySQL AB All rights reserved
[ Previously published as a note on a bug report.]
MySQL offers 42 built-in collations (sets of rules
for comparison and sorting) for the Unicode character
sets utf8 and ucs2. But you can add your own.
We will describe here how to add Vietnamese Unicode
collations. This is intended as an example, so
that you can try out all the steps and then modify
based on your own rules for some other language.
We chose Vietnamse because many people asked for it
via our "Feature Request" option in bugs.mysql.com,
http://bugs.mysql.com/bug.php?id=4745.
There are two ways to add a new collation:
* Change the source code. This is the trickier way,
but it's the only way for pre-August-2007 versions.
* Change the LDML "markup" file. This hasn't been
tested much, but it's simpler, it requires no
recompilation. (LDML stands for Locale Data Markup
Language, which provides a formal way to specify
ordering.)
We will describe both ways.
ADDING A NEW COLLATION BY CHANGING THE SOURCE CODE
In this section we will describe how to change
MySQL source code to add Vietnamese Unicode collations.
To understand this, you will have to know how to
download source code and how to build the server.
(The MySQL Reference Manual explains all that).
These instructions should work for versions 4.1,
5.0, and 5.1.
"(Note for MySQL 4.1 users only: the first file to edit will be
configure.in rather than config/ac-macros/character_sets.m4,
and in subsequent files the last defined language will probably
be Persian rather than Hungarian, and when editing ctype-uca.c
you shouldn't add the lines that have 'caseinfo' or
'caseup_multiply' or 'casedn_multiply' or pad char' in them.)"
1. Edit config/ac-macros/character_sets.m4
Look for lines approximately like this:
"
define(UCSC1, ucs2_general_ci ucs2_bin)
define(UCSC2, ucs2_czech_ci ucs2_danish_ci)
define(UCSC3, ucs2_esperanto_ci ucs2_estonian_ci ucs2_icelandic_ci)
define(UCSC4, ucs2_latvian_ci ucs2_lithuanian_ci)
define(UCSC5, ucs2_persian_ci ucs2_polish_ci ucs2_romanian_ci)
define(UCSC6, ucs2_slovak_ci ucs2_slovenian_ci)
define(UCSC7, ucs2_spanish2_ci ucs2_spanish_ci)
define(UCSC8, ucs2_swedish_ci ucs2_turkish_ci)
define(UCSC9, ucs2_unicode_ci)
"
Change the last line to
"
define(UCSC9, ucs2_unicode_ci ucs2_vietnamese_ci)
"
(Collations are in alphabetic order so "vietnamese" comes after
"unicode".)
Look for lines approximately like this:
"
define(UTFC1, utf8_general_ci utf8_bin)
define(UTFC2, utf8_czech_ci utf8_danish_ci)
define(UTFC3, utf8_esperanto_ci utf8_estonian_ci utf8_icelandic_ci)
define(UTFC4, utf8_latvian_ci utf8_lithuanian_ci)
define(UTFC5, utf8_persian_ci utf8_polish_ci utf8_romanian_ci)
define(UTFC6, utf8_slovak_ci utf8_slovenian_ci)
define(UTFC7, utf8_spanish2_ci utf8_spanish_ci)
define(UTFC8, utf8_swedish_ci utf8_turkish_ci)
define(UTFC9, utf8_unicode_ci)
"
Change the last line to
"
define(UTFC9, utf8_unicode_ci utf8_vietnamese_ci)
"
2. Edit mysys/charset-def.c
Look for lines approximately like this:
"
#ifdef HAVE_CHARSET_ucs2
extern CHARSET_INFO my_charset_ucs2_general_uca;
extern CHARSET_INFO my_charset_ucs2_icelandic_uca_ci;
extern CHARSET_INFO my_charset_ucs2_latvian_uca_ci;
... (etc.)
extern CHARSET_INFO my_charset_ucs2_hungarian_uca_ci;
#endif
"
Add this line before the #endif:
"
extern CHARSET_INFO my_charset_ucs2_vietnamese_uca_ci;
"
Look for lines approximately like this:
"
#ifdef HAVE_CHARSET_utf8
extern CHARSET_INFO my_charset_utf8_general_uca_ci;
extern CHARSET_INFO my_charset_utf8_icelandic_uca_ci;
extern CHARSET_INFO my_charset_utf8_latvian_uca_ci;
... (etc.)
extern CHARSET_INFO my_charset_utf8_hungarian_uca_ci;
#endif
"
Add this line before the #endif:
"
extern CHARSET_INFO my_charset_utf8_vietnamese_uca_ci;
"
Look for lines approximately like this:
"
#ifdef HAVE_UCA_COLLATIONS
add_compiled_collation(&my_charset_ucs2_general_uca);
add_compiled_collation(&my_charset_ucs2_icelandic_uca_ci);
add_compiled_collation(&my_charset_ucs2_latvian_uca_ci);
... (etc.)
add_compiled_collation(&my_charset_ucs2_hungarian_uca_ci);
#endif
"
Add this line before the #endif:
"
add_compiled_collation(&my_charset_ucs2_vietnamese_uca_ci);
"
Look for lines approximately like this:
"
#ifdef HAVE_UCA_COLLATIONS
add_compiled_collation(&my_charset_utf8_general_uca_ci);
add_compiled_collation(&my_charset_utf8_icelandic_uca_ci);
add_compiled_collation(&my_charset_utf8_latvian_uca_ci);
... (etc.)
add_compiled_collation(&my_charset_utf8_hungarian_uca_ci);
#endif
"
Add this line before the #endif:
"
add_compiled_collation(&my_charset_utf8_vietnamese_uca_ci);
"
3. Edit strings/ctype-uca.c
This is where you have to use your knowledge of Vietnamese.
We know nothing. We just believe what Vietnamese people said
in http://vietunicode.sourceforge.net/charset/vietalphabet.html.
Look for lines approximately like this:
"
/*
A simplified version of Hungarian, without consonant contractions.
*/
static const char hungarian[]=
"&O < \\u00F6 <<< \\u00D6 << \\u0151 <<< \\u0150"
"&U < \\u00FC <<< \\u00DC << \\u0171 <<< \\u0170";
"
This is an example of "tailoring" following a subset of
"ICU Collation Customization (tailorings)". It has rules
for expressing the difference from the usual UCA collation.
Add these lines:
(NB: each quoted string is on a single line, turn word wrapping off.)
"
/*
Vietnamese experiment. Not for use in production systems.
*/
static const char vietnamese[]=
"& A < \\u0103 <<< \\u0102 << \\u1EB1 <<< \\u1EB0 << \\u1EB3 <<< \\u1EB2 << \\u1EB5 <<< \\u1EB4 << \\u1EAF <<< \\u1EAE << \\u1EB7 <<< \\u1EB6"
" < \\u00E2 <<< \\u00C2 << \\u1EA7 <<< \\u1EA6 << \\u1EA9 <<< \\u1EA8 << \\u1EAB <<< \\u1EAA << \\u1EA5 <<< \\u1EA4 << \\u1EAD <<< \\u1EAC"
"& D < \\u0111 <<< \\u0110"
"& E < \\u00EA <<< \\u00CA << \\u1EC1 <<< \\u1EC0 << \\u1EC3 <<< \\u1EC2 << \\u1EC5 <<< \\u1EC4 << \\u1EBF <<< \\u1EBE << \\u1EC7 <<< \\u1EC6"
"& O < \\u00F4 <<< \\u00D4 << \\u1ED3 <<< \\u1ED2 << \\u1ED5 <<< \\u1ED4 << \\u1ED7 <<< \\u1ED6 << \\u1ED1 <<< \\u1ED0 << \\u1ED9 <<< \\u1ED8"
" < \\u01A1 <<< \\u01A0 << \\u1EDD <<< \\u1EDC << \\u1EDF <<< \\u1EDE << \\u1EE1 <<< \\u1EE0 << \\u1EDB <<< \\u1EDA << \\u1EE3 <<< \\u1EE2"
"& U < \\u01B0 <<< \\u01AF << \\u1EEB <<< \\u1EEA << \\u1EED <<< \\u1EEC << \\u1EEF <<< \\u1EEE << \\u1EE9 <<< \\u1EE8 << \\u1EF1 <<< \\u1EF0";
"
Look for lines approximately like this:
"
CHARSET_INFO my_charset_ucs2_hungarian_uca_ci=
{
146,0,0, /* number */
MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE,
"ucs2", /* cs name */
"ucs2_hungarian_ci",/* name */
"", /* comment */
hungarian, /* tailoring */
NULL, /* ctype */
NULL, /* to_lower */
NULL, /* to_upper */
NULL, /* sort_order */
NULL, /* contractions */
NULL, /* sort_order_big*/
NULL, /* tab_to_uni */
NULL, /* tab_from_uni */
my_unicase_default, /* caseinfo */
NULL, /* state_map */
NULL, /* ident_map */
8, /* strxfrm_multiply */
1, /* caseup_multiply */
1, /* casedn_multiply */
2, /* mbminlen */
2, /* mbmaxlen */
9, /* min_sort_char */
0xFFFF, /* max_sort_char */
' ', /* pad char */
0, /* escape_with_backslash_is_dangerous */
&my_charset_ucs2_handler,
&my_collation_ucs2_uca_handler
};
"
Add these lines:
"
CHARSET_INFO my_charset_ucs2_vietnamese_uca_ci=
{
147,0,0, /* number */
MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE,
"ucs2", /* cs name */
"ucs2_vietnamese_ci",/* name */
"", /* comment */
vietnamese, /* tailoring */
NULL, /* ctype */
NULL, /* to_lower */
NULL, /* to_upper */
NULL, /* sort_order */
NULL, /* contractions */
NULL, /* sort_order_big*/
NULL, /* tab_to_uni */
NULL, /* tab_from_uni */
my_unicase_default, /* caseinfo */
NULL, /* state_map */
NULL, /* ident_map */
8, /* strxfrm_multiply */
1, /* caseup_multiply */
1, /* casedn_multiply */
2, /* mbminlen */
2, /* mbmaxlen */
9, /* min_sort_char */
0xFFFF, /* max_sort_char */
' ', /* pad char */
0, /* escape_with_backslash_is_dangerous */
&my_charset_ucs2_handler,
&my_collation_ucs2_uca_handler
};
"
The "number" must be greater than the last
"number" for ucs2. At the time of writing, the
last ucs2 collation is "Hungarian" and its number is
146 so we're adding 147 here. But this is transient!
You'll see this number with SHOW COLLATION, illustrated later.
Look for lines approximately like this:
"
CHARSET_INFO my_charset_utf8_hungarian_uca_ci=
{
210,0,0, /* number */
MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE,
"utf8", /* cs name */
"utf8_hungarian_ci",/* name */
"", /* comment */
hungarian, /* tailoring */
ctype_utf8, /* ctype */
NULL, /* to_lower */
NULL, /* to_upper */
NULL, /* sort_order */
NULL, /* contractions */
NULL, /* sort_order_big*/
NULL, /* tab_to_uni */
NULL, /* tab_from_uni */
my_unicase_default, /* caseinfo */
NULL, /* state_map */
NULL, /* ident_map */
8, /* strxfrm_multiply */
1, /* caseup_multiply */
1, /* casedn_multiply */
1, /* mbminlen */
3, /* mbmaxlen */
9, /* min_sort_char */
0xFFFF, /* max_sort_char */
' ', /* pad char */
0, /* escape_with_backslash_is_dangerous */
&my_charset_utf8_handler,
&my_collation_any_uca_handler
};
"
Add these lines:
"
CHARSET_INFO my_charset_utf8_vietnamese_uca_ci=
{
211,0,0, /* number */
MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE,
"utf8", /* cs name */
"utf8_vietnamese_ci",/* name */
"", /* comment */
vietnamese, /* tailoring */
ctype_utf8, /* ctype */
NULL, /* to_lower */
NULL, /* to_upper */
NULL, /* sort_order */
NULL, /* contractions */
NULL, /* sort_order_big*/
NULL, /* tab_to_uni */
NULL, /* tab_from_uni */
my_unicase_default, /* caseinfo */
NULL, /* state_map */
NULL, /* ident_map */
8, /* strxfrm_multiply */
1, /* caseup_multiply */
1, /* casedn_multiply */
1, /* mbminlen */
3, /* mbmaxlen */
9, /* min_sort_char */
0xFFFF, /* max_sort_char */
' ', /* pad char */
0, /* escape_with_backslash_is_dangerous */
&my_charset_utf8_handler,
&my_collation_any_uca_handler
};
"
The "number" must be greater than the last
"number" for utf8. At the time of writing, the
last utf8 collation is "Hungarian" and its number is
210 so we're adding 211 here. But this is transient!
You'll see this number with SHOW COLLATION, illustrated later.
4. Build!
If all went well, after building you'll have two new
collations. They will be in information_schema.collations.
If all went well, after building you'll have two new
collations. They will be in the metadata. For example:
mysql> show collation like '%vietnamese%';
+--------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8_vietnamese_ci | utf8 | 209 | | Yes | 8 |
| ucs2_vietnamese_ci | ucs2 | 145 | | Yes | 8 |
+--------------------+---------+-----+---------+----------+---------+
2 rows in set (0.00 sec)
CHARACTER SET utf8 COLLATE utf8_vietnamese_ci will work.
CHARACTER SET ucs2 COLLATEE ucs2_vietnamese_ci will work.
ADDING A NEW COLLATION BY CHANGING THE MARKUP FILE
In this section we will describe how to change
an LDML file to add Vietnamese Unicode collations.
To understand this, you will have to know how what
an LDML file is. (The Unicode Consortium explains
that in their Locale Data Markup Language page,
http://unicode.org/reports/tr35/.)
These instructions apply to MySQL versions 5.0.44
or higher and 5.1.20 or higher. They might work
with earlier versions, but we don't recommend that
you try. They should work for most operating
systems including Windows.
1. Open MySQL character set definition file
(usually /usr/share/mysql/charsets/Index.xml,
but its location may vary depending on build flags).
2. Find this code:
<charset name="ucs2">
<family>Unicode</family>
<description>UCS-2 Unicode</description>
<collation name="ucs2_general_ci" id="35">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="ucs2_bin" id="90">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>
3. Change it to:
<charset name="ucs2">
<family>Unicode</family>
<description>UCS-2 Unicode</description>
<collation name="ucs2_general_ci" id="35">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="ucs2_bin" id="90">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
<collation name="ucs2_vietnamese_ci" id="147">
<!-- Vietnamese experimental collation -->
<rules>
<reset>A</reset>
<p>\u0103</p><t>\u0102</t>
<s>\u1EB1</s><t>\u1EB0</t>
<s>\u1EB3</s><t>\u1EB2</t>
<s>\u1EB5</s><t>\u1EB4</t>
<s>\u1EAF</s><t>\u1EAE</t>
<s>\u1EB7</s><t>\u1EB6</t>
<p>\u00E2</p><t>\u00C2</t>
<s>\u1EA7</s><t>\u1EA6</t>
<s>\u1EA9</s><t>\u1EA8</t>
<s>\u1EAB</s><t>\u1EAA</t>
<s>\u1EA5</s><t>\u1EA4</t>
<s>\u1EAD</s><t>\u1EAC</t>
<reset>D</reset>
<p>\u0111</p><t>\u0110</t>
<reset>E</reset>
<p>\u00EA</p><t>\u00CA</t>
<s>\u1EC1</s><t>\u1EC0</t>
<s>\u1EC3</s><t>\u1EC2</t>
<s>\u1EC5</s><t>\u1EC4</t>
<s>\u1EBF</s><t>\u1EBE</t>
<s>\u1EC7</s><t>\u1EC6</t>
<reset>O</reset>
<p>\u00F4</p><t>\u00D4</t>
<s>\u1ED3</s><t>\u1ED2</t>
<s>\u1ED5</s><t>\u1ED4</t>
<s>\u1ED7</s><t>\u1ED6</t>
<s>\u1ED1</s><t>\u1ED0</t>
<s>\u1ED9</s><t>\u1ED8</t>
<p>\u01A1</p><t>\u01A0</t>
<s>\u1EDD</s><t>\u1EDC</t>
<s>\u1EDF</s><t>\u1EDE</t>
<s>\u1EE1</s><t>\u1EE0</t>
<s>\u1EDB</s><t>\u1EDA</t>
<s>\u1EE3</s><t>\u1EE2</t>
<reset>U</reset>
<p>\u01B0</p><t>\u01AF</t>
<s>\u1EEB</s><t>\u1EEA</t>
<s>\u1EED</s><t>\u1EEC</t>
<s>\u1EEF</s><t>\u1EEE</t>
<s>\u1EE9</s><t>\u1EE8</t>
<s>\u1EF1</s><t>\u1EF0</t>
</rules>
</collation>
</charset>
The "id" must be greater than the last ID for ucs2.
At the time of writing, the last ucs2 collation is "Hungarian"
and its number is 146:
mysql> select collation_name, id from information_schema.collations
> where character_set_name='ucs2' order by id;
+--------------------+-----+
| collation_name | id |
+--------------------+-----+
| ucs2_general_ci | 35 |
.....
| ucs2_hungarian_ci | 146 |
+--------------------+-----+
21 rows in set (0.00 sec)
So we're adding 147 here.
4. Find this code:
<charset name="utf8">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8_general_ci" id="33">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_bin" id="83">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>
5. Change it to:
<charset name="utf8">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8_general_ci" id="33">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_bin" id="83">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_vietnamese_ci" id="211">
<!-- Vietnamese experimental collation -->
<rules>
<reset>A</reset>
<p>\u0103</p><t>\u0102</t>
<s>\u1EB1</s><t>\u1EB0</t>
<s>\u1EB3</s><t>\u1EB2</t>
<s>\u1EB5</s><t>\u1EB4</t>
<s>\u1EAF</s><t>\u1EAE</t>
<s>\u1EB7</s><t>\u1EB6</t>
<p>\u00E2</p><t>\u00C2</t>
<s>\u1EA7</s><t>\u1EA6</t>
<s>\u1EA9</s><t>\u1EA8</t>
<s>\u1EAB</s><t>\u1EAA</t>
<s>\u1EA5</s><t>\u1EA4</t>
<s>\u1EAD</s><t>\u1EAC</t>
<reset>D</reset>
<p>\u0111</p><t>\u0110</t>
<reset>E</reset>
<p>\u00EA</p><t>\u00CA</t>
<s>\u1EC1</s><t>\u1EC0</t>
<s>\u1EC3</s><t>\u1EC2</t>
<s>\u1EC5</s><t>\u1EC4</t>
<s>\u1EBF</s><t>\u1EBE</t>
<s>\u1EC7</s><t>\u1EC6</t>
<reset>O</reset>
<p>\u00F4</p><t>\u00D4</t>
<s>\u1ED3</s><t>\u1ED2</t>
<s>\u1ED5</s><t>\u1ED4</t>
<s>\u1ED7</s><t>\u1ED6</t>
<s>\u1ED1</s><t>\u1ED0</t>
<s>\u1ED9</s><t>\u1ED8</t>
<p>\u01A1</p><t>\u01A0</t>
<s>\u1EDD</s><t>\u1EDC</t>
<s>\u1EDF</s><t>\u1EDE</t>
<s>\u1EE1</s><t>\u1EE0</t>
<s>\u1EDB</s><t>\u1EDA</t>
<s>\u1EE3</s><t>\u1EE2</t>
<reset>U</reset>
<p>\u01B0</p><t>\u01AF</t>
<s>\u1EEB</s><t>\u1EEA</t>
<s>\u1EED</s><t>\u1EEC</t>
<s>\u1EEF</s><t>\u1EEE</t>
<s>\u1EE9</s><t>\u1EE8</t>
<s>\u1EF1</s><t>\u1EF0</t>
</rules>
</collation>
</charset>
The "id" must be greater than the last ID for ucs2.
At the time of writing, the last utf8 collation is
"Hungarian" and its number is 210:
mysql> select collation_name, id from information_schema.collations
-> where character_set_name='utf8' order by id;
+--------------------+-----+
| collation_name | id |
+--------------------+-----+
| utf8_general_ci | 33 |
...
| utf8_hungarian_ci | 210 |
+--------------------+-----+
21 rows in set (0.00 sec)
So we're adding 211 here.
4. Restart mysqld
If all went well, after restart you'll have two new
collations. They will be in information_schema.collations.
CHARACTER SET utf8 COLLATE utf8_vietnamese_ci will work.
CHARACTER SET ucs2 COLLATE ucs2_vietnamese_ci will work.
WHAT TO DO IF YOU HAVE A PROBLEM, OR HAVE NO PROBLEM
There is no warranty or liability for MySQL if you
change the product. MySQL will only accept bug reports
for unchanged versions. However, our instructions work
according to our tests at this time. If the instructions
have to change due other changes in the source code in
future MySQL versions, we intend to add notes in the
MySQL Internals Manual,
http://forge.mysql.com/wiki/MySQL_Internals.
Later, someday, after everyone is sure that these
changes are what Vietnamese people really want, somebody
might consider submitting a "patch" to MySQL so
that this might appear in a future version of
MySQL/community server.
ABOUT THE AUTHORS
Alexander Barkov and Peter Gulutzan work for MySQL AB.
Posted in News | 3 Comments »
MySQL 6.0 Feature #2: Online Backup
Online Backup
MySQL 6.0 New Features Document#2
2008-05-18
By Alexander Nozdrin, Chuck Bell, Lars Thalmann, Peter Gulutzan, Rafal Somla
High Executive Summary
Online backup is a new feature in MySQL 6.0.
It protects from database loss.
It does not block all other concurrent MySQL connections.
It will interest Database Administrators.
Database Administrators Summary
BACKUP DATABASE copies all data and metadata in one or more MySQL databases, into an “image file”. RESTORE reads an image file and rewrites all the data and metadata in one or more MySQL databases. So if you lose a database, you can recover all of it as of the time of the last BACKUP DATABASE statement.And then you can re-run the statements in MySQL’s binary log to recover “from the time of the last BACKUP DATABASE statement”, “to the time that the database loss occurred”. In other words … Careful Use of Online Backup will protect from database loss.
The word “online” means “without blocking”. So, unlike earlier backup solutions which could make all other jobs hang, the Online Backup statements will allow certain Database Manipulation Language statements to go on concurrently. Some locking still occurs, but it’s minimal.
To complete the package, there are new tools for monitoring and recording each online backup process or subprocess.
BACKUP DATABASE Syntax
BACKUP DATABASE database_name_list TO ‘image-file-name’;
It is possible to use SCHEMA as a synonym for DATABASE, that is, “BACKUP SCHEMA …” instead of “BACKUP DATABASE …”.
The database_name_list may be a list of databases separated by commas, or “*” meaning “all databases except mysql or information_schema”.
There is no predefined maximum number of databases.
It is illegal to use the same database name more than once.
All databases in the list must exist.
The user must have SELECT or equivalent privileges for all objects in all databases.
The use of file names is affected by the security startup option –secure-file-priv
http://dev.mysql.com/doc/refman/6.0/en/server-options.html#option_mysqld_secure-file-priv.
The ‘image-file-name’ is the name of the image file.
This must be specified as a string literal, not as a variable.
Only one file name is allowed.
The path may be absolute, or relative to ‘backupdir’ (see section titled ‘backupdir’).
The file is always on the server, there is no LOCAL option to force the image file to go to a client computer.
There must not already be a file with the same name.
The server must have the ability to create files and write on the directory.
Examples:
BACKUP DATABASE * TO ‘/usr/local/mysql/var/t1′;
BACKUP DATABASE `C#` TO ‘t2′;
BACKUP SCHEMA a,b,c TO ‘d’;
Effects:
* All data and metadata in all listed databases is copied to the image file.
* While BACKUP DATABASE is going on, it is possible to monitor progress with a new mysql table, mysql.online_backup_progress.
* After BACKUP DATABASE completes successfully, there is a record in a new mysql table, mysql.online_backup. (The record is created when the backup starts, is updated as the backup proceeds, and reaches its final state when the backup ends.)
Minor notes: BACKUP DATABASE is classed as a “Data Manipulation Language” statement because it reads.
It does not lock table files in a way that would prevent other jobs from reading and writing, but it does block other jobs from performing any “Database Definition Language” with any object in any database in the list.
Upon completion, BACKUP DATABASE returns a result set with the backup number.
It causes an automatic “commit”.
Terminology notes: MySQL uses the word “image” for the data that BACKUP DATABASE produces. At present the “image” is always a file so ‘image-file’ is the only possibility. This has no relation to the way that the word ‘image’ is used by other DBMS vendors.
RESTORE syntax
RESTORE FROM ‘image-file-name’;
The ‘image-file-name’ is the name of an image file that was made with BACKUP DATABASE. It must be specified as a string literal.
The user must have CREATE or equivalent privileges for all objects in all databases that were backed up.
Examples:
BACKUP DATABASE test TO ‘/usr/local/mysql/var/t3′;
RESTORE FROM ‘/usr/local/mysql/var/t3′;
Effects:
Everything that was backed up with BACKUP DATABASE is restored with RESTORE. At time of writing, existing databases are overwritten. Later, it will be necessary to drop databases before restoring. Upon completion, RESTORE returns a result set with the backup number.
Minor notes: RESTORE is classed as a “Data Definition Language” statement because it alters. It causes some table locking because it will write to each table in each database, so “online restore” does not exist.
backupdir
The ‘backupdir’ i.e. ‘backup directory’ variable is new. You can see its value with “SELECT @@backupdir” but it is read only so you may not set it while the server is up. You may specify –backupdir on the mysqld command line, for example:
mysqld –backupdir=’/’
If the ‘image-file-name’ in BACKUP or RESTORE has a relative path (for example ‘x’ instead of ‘/usr/local/mysql/var/x’), then the DBMS will put the backupdir value before image-file-name. For example, if backupdir is ‘/usr/local/mysql/var’, the statement
RESTORE FROM ‘x’
will open the file ‘/usr/local/mysql/var/x’.
By default, backupdir is the same as datadir, that is, if datadir = ‘/usr/local/mysql/var’ then backupdir = ‘/usr/local/mysql/var’. But it is not good policy to put the database and the backups on the same device! MySQL recommends that you change backupdir if you have another device that backups can go to.
What is backed up
BACKUP DATABASE backups the database and all objects that are within the database:
persistent base tables created with engine = memory, archive, myisam, innodb, or falcon (including indexes, foreign-key constraints, partitions)
and
views, stored procedures, stored functions (except UDFs), triggers, events.
What is not backed up
Some items are not backed up because they are not in databases, or are not persistent, or are not currently supportable by the backup system. MySQL recommends that users take other measures, such as file copy, to ensure that all parts of the system are restorable after RESTORE.
Not-backed-up items are:
Temporary tables
Session objects
Contents of INFORMATION_SCHEMA.GLOBAL_STATUS
Contents of INFORMATION_SCHEMA.GLOBAL_VARIABLES
Contents of INFORMATION_SCHEMA.PROCESSLIST
Contents of INFORMATION_SCHEMA.PROFILING (community only)
Contents of INFORMATION_SCHEMA.SESSION_STATUS
Contents of INFORMATION_SCHEMA.SESSION_VARIABLES
Contents of INFORMATION_SCHEMA.FALCON*
Tablespaces
Plugins
UDF files
Global objects:
- USER
- privileges which are not “per-database”
- PLUGIN (e.g. parser plugin) not including code
- plugin code
- UDF (i.e. function defined in shared libraries) not including code
- UDF code
- LOGFILE GROUP
- TABLESPACE
- SERVER i.e. connections (for federated tables)
- logs (general log, slow query log, storage-engine logs)
- contents of mysql.host
- time zone
- configuration files
- index.xml
Objects outside all databases:
- master.info
- relay-log.info
- Storage engine logs
(Some storage engines have their own log
files. MySQL will not back them up, and
will not restore them. The plan is that
BACKUP will take place ‘as of’ a moment
of validity. Uncommitted data at the
validity-moment time is not restorable.)
- binary log
Static objects:
- Static files that come as part of the MySQL installation,
for example (Windows) mysqld-nt.exe
- User programs
- Operating system shared libraries
- Scripts for starting MySQL server when booting operating system
- Environment initializations for $MYSQL_HOST or similar variables
- Option files (my.cnf, my.ini, mysql-service-agent.ini,
/etc/init.d, /var/lib/mysql-cluster/config.ini)
Plugins:
(The plugins are all the files that can be read in the directory that you see when you say SHOW VARIABLES LIKE ‘plugin_dir’; for example /usr/local/mysql/lib/mysql.)
Files in the basedir ’shared’ directory:
(These files only change if users customize, for example by adding a new collation or translating an error message.
Examples:
/usr/local/mysql/share/mysql/charsets/*.xml
/usr/local/mysql/share/mysql/charsets/*.conf
/usr/local/mysql/share/mysql/english/errmsg.txt)
mysql.online_backup
A new table in the mysql database, mysql.online_backup, has a record of the backups.
It has these columns:
backup_id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
This is the ‘Surrogate key for (near) duplicate entries’. This will correspond to the number that is returned by the BACKUP DATABASE statement.
process_id int unsigned NOT NULL
This is ‘The process id that operation ran as’.
binlog_pos int unsigned DEFAULT ‘0′
This is ‘The recorded binlog position of backup/restore’.
binlog_file char(64) DEFAULT NULL
This is ‘The recorded binlog filename at time of backup/restore’.
backup_state enum(’complete’,’starting’,'validity point’,'running’,'error’,'cancel’) NOT NULL
This is the ‘Status of current operation’.
operation enum(’backup’,'restore’) NOT NULL
This is the ‘Type of operation’.
error_num int(11) NOT NULL DEFAULT ‘0′
This is ‘The error from this run 0 == none’.
num_objects int unsigned NOT NULL DEFAULT ‘0′
This is ‘The number of objects in the backup’.
total_bytes bigint unsigned DEFAULT NULL
This is ‘The size of the backup repository in bytes’.
validity_point_time datetime DEFAULT NULL
This is ‘The time of the validity point.’. Times are in the system time zone, which may be UTC. Changes re time zones are happening as we write this.
start_time datetime DEFAULT NULL
This is ‘The date/time of start of operation’.
stop_time datetime DEFAULT NULL
This is ‘The date/time of end of operation’.
host_or_server_name char(30) DEFAULT NULL
This is ‘The server name where operation ran’.
username char(30) DEFAULT NULL
This is ‘The user name who ran the operation’.
backup_file char(100) DEFAULT NULL
This is ‘The name of the file’.
user_comment varchar(200) DEFAULT NULL
This is ‘The comment from user entered at command line’.
command varchar(512) DEFAULT NULL
This is ‘The command used to issue operation’.
engines varchar(100) DEFAULT NULL
This is ‘The name of the storage engines used in the operation’. Soon we’ll change it to ‘drivers’ instead of ‘engines’.
With this table, it’s possible to keep track of what backups exist. You need SELECT privilege to see it.
mysql.online_backup_progress
Another new table in the mysql database, mysql.online_backup_progress, has temporary information while BACKUP or RESTORE is happening.
It has these columns:
backup_id bigint unsigned NOT NULL
This is the ‘Key for online_backup table entries’.
object char(30) NOT NULL
This is ‘The object being operated on’.
start_time datetime DEFAULT NULL
This is ‘The date/time of start of operation’.
stop_time datetime DEFAULT NULL
This is ‘The date/time of end of operation’.
total_bytes bigint DEFAULT NULL
This is ‘The size of the object in bytes’.
progress bigint unsigned DEFAULT NULL
This is ‘The number of bytes processed’.
error_num int NOT NULL DEFAULT ‘0′
This is ‘The error from this run 0 == none’.
notes char(100) DEFAULT NULL
This is the ‘Commentary from the backup engine’.
With this table, it’s possible to monitor progress. Another way to monitor progress is with SHOW PROCESSLIST. You need SELECT privilege to see it.
A session
Let us walk through a session, using all the statements and tools that we have described so far. The following is an actual copy of the mysql client screen output, after clearing the database directory and running mysql_install_db. The server was started with a –log-bin switch.
pgulutzan@linux:~> /usr/local/mysql/bin/mysql --user=root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.5-alpha-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> /* Create database. Create table. Populate table. */
mysql> CREATE DATABASE dw;
Query OK, 1 row affected (0.05 sec)
mysql> USE dw
Database changed
mysql> CREATE TABLE t (s1 INT, s2 CHAR(5)) engine=falcon;
Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO t VALUES (1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> /* Backup the database to file 'dw.dmp' on data directory. */
mysql> BACKUP DATABASE dw TO 'dw.dmp';
+-----------+
| backup_id |
+-----------+
| 1 |
+-----------+
1 row in set (2.61 sec)
mysql> /* Pretend to monitor progress. In fact online_backup_progress
/*> will show nothing new, and SHOW PROCESSLIST will show nothing,
/*> because the BACKUP DATABASE statement has finished.
/*> But these statements would show interesting things
/*> if we were running them on another connection before
/*> BACKUP DATABASE finished. */
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | root | localhost | dw | Query | 0 | NULL | SHOW
PROCESSLIST |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.online_backup_progress\G
*************************** 1. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: starting
*************************** 2. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: running
*************************** 3. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: validity point
*************************** 4. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: running
*************************** 5. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: complete
5 rows in set (0.00 sec)
mysql> /* "Lose" the database. */
mysql> DROP DATABASE dw;
Query OK, 1 row affected (0.00 sec)
mysql> /* Restore the database from the image file 'x.dmp'. */
mysql> RESTORE FROM 'dw.dmp';
+-----------+
| backup_id |
+-----------+
| 2 |
+-----------+
1 row in set (1.77 sec)
mysql> /* Confirm that table t in databasee dw is restored. */
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
-> WHERE table_schema = 'dw' AND table_name = 't';
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.03 sec)
mysql> /* Confirm that table t has the data that it should have. */
mysql> SELECT * FROM dw.t;
+------+------+
| s1 | s2 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> /* See what it is in the new mysql.online_backup table. */
mysql> SELECT * FROM mysql.online_backup\G
*************************** 1. row ***************************
backup_id: 1
process_id: 0
binlog_pos: 520
binlog_file: ./linux-bin.000001
backup_state: complete
operation: backup
error_num: 0
num_objects: 1
total_bytes: 24
validity_point_time: 2008-04-10 19:12:00
start_time: 2008-04-10 19:12:00
stop_time: 2008-04-10 19:12:00
host_or_server_name: localhost
username: root
backup_file: dw.dmp
user_comment:
command: BACKUP DATABASE dw TO 'dw.dmp'
engines: Snapshot
*************************** 2. row ***************************
backup_id: 2
process_id: 0
binlog_pos: 0
binlog_file: NULL
backup_state: complete
operation: restore
error_num: 0
num_objects: 1
total_bytes: 24
validity_point_time: NULL
start_time: 2008-04-10 19:13:17
stop_time: 2008-04-10 19:13:17
host_or_server_name: localhost
username: root
backup_file: dw.dmp
user_comment:
command: RESTORE FROM 'dw.dmp'
engines: Snapshot
2 rows in set (0.00 sec)
mysql>
Consistent Read
WARNING: This section contains information about the Falcon engine which is not true at time of writing. Since it is an essential part of what makes online backup “online”, we include it anyway.
Suppose user#1 says “BACKUP DATABASE dw …” and then user#2 comes along and says “UPDATE dw.t …”. What happens?
With a “non-online backup”, we would expect that the UPDATE has to wait until the BACKUP DATABASE finishes (the database’s tables are locked), or we would expect that the image file has some rows from table that were changed, and some that were not — an “inconsistent backup”. Both these expectations are gloomy.
With an “online backup”, on the other hand, we would expect that the UPDATE does not have to wait, and yet the backup is guaranteed to be consistent. This depends on a feature that both InnoDB and Falcon storage engines provide: “consistent read”.
By asking for “consistent read” (or “snapshot”), BACKUP DATABASE ensures that everything the DBMS reads, in all tables, in all databases in the database list, is seen “as of” the same moment. Changes that happen during BACKUP DATABASE are ignored. With the InnoDB and Falcon engines, this happens due to their support for consistent read. With MyISAM and MEMORY engines, this cannot happen, so if there are tables created with those storage engines, any UPDATE or INSERT or REPLACE or DELETE or TRUNCATE statements are blocked during copy of those tables. For example:
* Suppose database d contains three tables: t1,t2,t3.
Table t1 is Falcon, t2 is MyISAM, t3 is MEMORY.
* User says “BACKUP DATABASE d;”.
* The DBMS does not need to lock t1 which has consistent read.
The DBMS locks t2, copies all its rows, and unlocks t2.
The DBMS locks t3, copies all its rows, and unlocks t3.
In any case, any DDL (CREATE/ALTER/DROP) statements are always blocked. The word “online” only means that DML statements can take place at the same time as the backup.
A plan
An effective backup plan would then look something like this:
1. Always start the mysqld server with –log-bin, so that there will be a log file. It’s this that allows for backups with log updates, which MySQL calls “incremental backups”. (Incremental backups are not planned for the beta version and may be available as enterprise features.)
2. Regularly back up with BACKUP DATABASE. Monitor progress with mysql.online_backup_progress table or SHOW PROCESSLIST.
3. Regularly audit that backups have happened, by looking at the mysql.online_backup table.
4. Copy image files to safe places.
Occasionally you should keep in shape by testing the RESTORE procedure. It could go like this — on a spare machine or with a set of test databases:
1. Re-read this document. You must be aware that this test can be destructive
2. Collect: your image file, and a copy of the binlog file(s).
3. Read the mysql.online_backup row that corresponds to this image file, with
SELECT * FROM mysql.online_backup
WHERE operation = ‘backup’
AND backup_file = ‘dw.dmp’; /* if ‘dw.dmp’ = image-file-name*/
Make sure that column `backup_state` says ‘complete’.
Make sure that column `command` has a list of the databases that you want to restore.
Make a note of the `binlog_pos` and `binlog_file` column values, which you will need for Step 8.
4. Drop the databases that were in the BACKUP DATABASE statement.
5. Stop the mysqld server. Restart with only one user (max-connections = 1, skip-networking).
Log in as a user with all the privileges needed for restoring (BACKUP, FILE, etc.).
6. RESTORE FROM ‘image-file’;
7. Re-run the binary log from the time of the destruction.
Use mysqlbinlog –start-position=(what you got in Step 3)
It might be possible to restore to a different place by changing @@datadir, but nobody has tried that yet.
Replacing mysqldump
The image file is also useful for copying databases from one server to another, as in mysqldump.
However, MySQL is not deprecating mysqldump and we continue to recommend it for basic import/export. In that environment, the advantages of mysqldump over Online Backup are:
It is readable by humans.
It is possible to be specific about the objects to backup/restore.
It might be faster — we haven’t done many speed tests yet.
New error messages
The following error messages are new.
ER_BACKUP_BACKUP_START 1617 HY000 Starting backup process
ER_BACKUP_BACKUP_DONE 1618 HY000 Backup completed
ER_BACKUP_RESTORE_START 1619 HY000 Starting restore process
ER_BACKUP_RESTORE_DONE 1620 HY000 Restore completed
ER_BACKUP_NOTHING_TO_BACKUP 1621 HY000 Nothing to backup
ER_BACKUP_CANNOT_INCLUDE_DB 1622 HY000 Database ‘%-.64s’ cannot be included in a backup
ER_BACKUP_BACKUP 1623 HY000 Error during backup operation - server’s error log contains more information about the error
ER_BACKUP_RESTORE 1624 HY000 Error during restore operation - server’s error log contains more information about the error
ER_BACKUP_RUNNING 1625 HY000 Can’t execute this command because another BACKUP/RESTORE operation is in progress
ER_BACKUP_BACKUP_PREPARE 1626 HY000 Error when preparing for backup operation
ER_BACKUP_RESTORE_PREPARE 1627 HY000 Error when preparing for restore operation
ER_BACKUP_INVALID_LOC 1628 HY000 Invalid backup location ‘%-.64s’
ER_BACKUP_READ_LOC 1629 HY000 Can’t read backup location ‘%-.64s’
ER_BACKUP_WRITE_LOC 1630 HY000 Can’t write to backup location ‘%-.64s’
ER_BACKUP_LIST_DBS 1631 HY000 Can’t enumerate server databases
ER_BACKUP_LIST_TABLES 1632 HY000 Can’t enumerate server tables
ER_BACKUP_LIST_DB_TABLES 1633 HY000 Can’t enumerate tables in database %-.64s
ER_BACKUP_SKIP_VIEW 1634 HY000 Skipping view %-.64s in database %-.64s
ER_BACKUP_NO_ENGINE 1635 HY000 Skipping table %-.64s since it has no valid storage engine
ER_BACKUP_TABLE_OPEN 1636 HY000 Can’t open table %-.64s
ER_BACKUP_READ_HEADER 1637 HY000 Can’t read backup archive preamble
ER_BACKUP_WRITE_HEADER 1638 HY000 Can’t write backup archive preamble
ER_BACKUP_NO_BACKUP_DRIVER 1639 HY000 Can’t find backup driver for table %-.64s
ER_BACKUP_NOT_ACCEPTED 1640 HY000 %-.64s backup driver was selected for table %-.64s but it rejects to handle this table
ER_BACKUP_CREATE_BACKUP_DRIVER 1641 HY000 Can’t create %-.64s backup driver
ER_BACKUP_CREATE_RESTORE_DRIVER 1642 HY000 Can’t create %-.64s restore driver
ER_BACKUP_TOO_MANY_IMAGES 1643 HY000 Found %d images in backup archive but maximum %d are supported
ER_BACKUP_WRITE_META 1644 HY000 Error when saving meta-data of %-.64s
ER_BACKUP_READ_META 1645 HY000 Error when reading meta-data list
ER_BACKUP_CREATE_META 1646 HY000 Can’t create %-.64s
ER_BACKUP_GET_BUF 1647 HY000 Can’t allocate buffer for image data transfer
ER_BACKUP_WRITE_DATA 1648 HY000 Error when writing %-.64s backup image data (for table #%d)
ER_BACKUP_READ_DATA 1649 HY000 Error when reading data from backup stream
ER_BACKUP_NEXT_CHUNK 1650 HY000 Can’t go to the next chunk in backup stream
ER_BACKUP_INIT_BACKUP_DRIVER 1651 HY000 Can’t initialize %-.64s backup driver
ER_BACKUP_INIT_RESTORE_DRIVER 1652 HY000 Can’t initialize %-.64s restore driver
ER_BACKUP_STOP_BACKUP_DRIVER 1653 HY000 Can’t shut down %-.64s backup driver
ER_BACKUP_STOP_RESTORE_DRIVERS 1654 HY000 Can’t shut down %-.64s backup driver(s)
ER_BACKUP_PREPARE_DRIVER 1655 HY000 %-.64s backup driver can’t prepare for synchronization
ER_BACKUP_CREATE_VP 1656 HY000 %-.64s backup driver can’t create its image validity point
ER_BACKUP_UNLOCK_DRIVER 1657 HY000 Can’t unlock %-.64s backup driver after creating the validity point
ER_BACKUP_CANCEL_BACKUP 1658 HY000 %-.64s backup driver can’t cancel its backup operation
ER_BACKUP_CANCEL_RESTORE 1659 HY000 %-.64s restore driver can’t cancel its restore operation
ER_BACKUP_GET_DATA 1660 HY000 Error when polling %-.64s backup driver for its image data
ER_BACKUP_SEND_DATA 1661 HY000 Error when sending image data (for table #%d) to %-.64s restore driver
ER_BACKUP_SEND_DATA_RETRY 1662 HY000 After %d attempts %-.64s restore driver still can’t accept next block of data
ER_BACKUP_OPEN_TABLES 1663 HY000 Open and lock tables failed in %-.64s
ER_BACKUP_THREAD_INIT 1664 HY000 Backup driver’s table locking thread can not be initialized
ER_BACKUP_PROGRESS_TABLES 1665 HY000 Can’t open the online backup progress tables
Known Bugs
This list excludes bugs which are ‘patch pending’ or ‘patch queued’. To be sure you get an up-to-date list, check the MySQL bugs database http://bugs.mysql.com and click Category = Backup.
32499 BACKUP DATABASE crashes server if database has 600 tables (any engine)
33019 Online backup process can consume too much processor time
33022 Online backup does not preserve charsets for object names
33173 Error log polluted by backup debug info
33352 Backup: crash if I use old set of mysql files.
33354 Backup: restore changes current database to null
33355 Backup: hang if database is mysql
33364 Online Backup: Purge statement missing
33414 Backup: DDL hangs indefinitely if ongoing backup
33564 Backup: deadlock during restore
33566 Backup: crash with partitions and Falcon
33567 Backup: duplicate database names allowed
33568 Backup: crash if many databases in list
33569 Backup: tablespace not restored
33570 Backup: restore failure if sql_mode change
33571 Backup: restore failure if sql_mode=ansi
33573 Backup: timestamp column gets current default
33574 Backup: restore failure if temporary table exists
33575 Backup: crash on shutdown if Falcon table
33836 Backup: tables have default character set latin1
34065 Backup: file names may be too short
34067 Backup: restore failure if directory is not empty
34171 Backup: ignoring –secure-file-priv
34180 Backup: restore fails if CREATE EVENT fails
34181 Backup: crash if @@max_heap_table_size is small
34202 Backup: RESTORE turns off the slow log
34205 Backup: restore fails if Falcon and no autocommit
34210 Backup: lost InnoDB rows if concurrent restore
34480 Backup: Backup to a file copies redundantly if database name is repeated.
34721 Backup driver can’t refuse to provide driver
34758 Server crashes if database with views backed up using CS driver
34759 Server crash when backing-up database with trigger
34766 No error message for Full disk while performing network or local backup
34767 Existing DB contents are lost, if I attempt to restore a corrupted backup file.
34867 Backup: crash if altered view
34868 Backup: restore failure if two procedures
34902 Backup: crash if view depends on dropped object
34903 Backup: restore failure if view and temporary
34915 Backup: Not able to backup view.
35079 It is not possible to interrupt long BACKUP or RESTORE operation
35117 Backup: Server crash for backup of CSV engine with CHAR data type
35118 Backup:Restore fails if previous create Procedure/Function/Trigger command
35230 Backup: no backupdir
35240 RESTORE command doesn’t write binlog position to online_backup table
35249 Mysql server crash for delete operation followed by backup in memory SE
35347 Mysql Server crash while doing restore with views for default driver
35585 Online backup: Apparent synonyms for binlog start_position
35595 Backup crash server
35659 Falcon crashes on shutdown after backup+restore+drop DB
35698 Trying to backup a Falcon database leads to assertion
35806 The current system time becomes stagnant after Restore operation is complete.
35951 Backup: restore fails after directory removal
35967 Backup: can’t delete from mysql.online_backup_progress
36213 Restore fails for a database that has views created using another database .
36257 Backup project missing dependencies
36265 Views not backed up if they are dependent on tables from different Database
36480 Backup: restore fails with changed Falcon tablespace
36530 Backup: Restore can fail due to enabled triggers, events
36531 Backup: Objects can be restored in wrong order
36533 Backup and replication don’t work together
36571 Restore hangs for database size of 33K and more
36572 During Restore, select operation from another connection hangs.
36585 Trigger hangs, if fired on database being restored.
36586 Online backup stream library can miss end of a stream.
36624 Backup stream library works wrong on 64bit platform
36635 Backup: errors should be warnings
36664 Server crash in backup_no_be test on Solaris
36749 Data Loss after Restore, if Trigger fired on the table that is being Restored.
36778 Read operation during RESTORE leads to data loss
36782 View and its related table data lost after Restore.
Comparison with Oracle
Some people, especially migrators, will wonder how MySQL’s “Online Backup” compares with Oracle 11g. This chart shows the essentials. Notice that we compare, not with Oracle Recovery Manager (RMAN), but with Oracle Data Pump export (expdp).
We do this because our backup facility has two drivers that use a table scan algorithm to read the rows thereby making backups taken with the default and consistent snapshot drivers a ‘logical’ backup. As native drivers that use a binary copy algorithm are added, our backup facility will give the user more freedom to choose the type of backup to use. However, for this release, only ‘logical’ backups are available.
Oracle 11g MySQL 6.0
Data Pump "Online Backup"
========== ===============
Type "logical database backup" "logical database backup"
with "physical" aspects
File is on server? on server
Monitoring "Status" after ctrl+c tables in mysql database
or DBA_DATAPUMP_JOBS view or SHOW PROCESSLIST
or V$SESSION_LONGOPS view
Preparable Yes, DBMS_DATAPUMP API no
Metadata yes database-level metadata
is saved except where noted
Compression yes, in Enterprise Edition no
Encryption yes, in Enterprise Edition no
Parallel yes, in Enterprise Edition no
Data Transforms yes (remap to obfuscate) no
XML "supported" sure
By Partition You can dump/restore single no
partitions.
Re-Use Yes, you can overwrite yes, unavoidably
Exclude/Include Yes, you can skip or add no
specified objects
Diff OSs Yes, you can change to a Yes, but mixed
different operating system. endianness has
not been tested
Stop+Restart Yes, you can stop an export No
and resume it later.
Methods File Copy and other ways. File Copy.
Privileges CREATE TABLE privilege (for See section titled
master table), and access "Coming Soon".
to a directory object.
Log File Optional. Always.
Remap Yes e.g. change names of No.
tables during re-import.
Flashback Yes, to a particular SCN. Yes, "as of" start time.
Read Access Yes, read-without-RESTORE No.
is possible by creating an
external table.
Example "expdp "BACKUP
system/password@db10g DATABASE
full=Y TO
directory=TEST_DIR 'DB10G.dmp'"
dumpfile=DB10G.dmp
logfile=expdpDB10G.log"
Feature#1 Multiple Image Files e.g. No.
"DUMPFILE=x1.dmp, x2.dmp".
Feature#2 "ESTIMATE_ONLY" No.
Feature#3 Picking what to dump or No.
restore:
"EXCLUDE=TABLE:EMP"
"INCLUDE=TABLE_DATA"
ALL|DATA_ONLY|METADATA_ONLY
QUERY='...' predicate clause
TABLES=...
TABLESPACES=...
Feature#4 FLASHBACK_SCN|FLASHBACK_TIME No.
Feature#5 parameter file, PARFILE=xxx No.
Feature#6 "version of objects to No.
export" =
COMPATIBLE | LATEST | or
"any database version"
Replication
What about Backup and Replication working together? This section contains only tentative suggestions, it is not useful for reference.
If BACKUP DATABASE is done on the master:
it will not be logged.
If RESTORE is done on a master:
it will not be logged.
In future version, RESTORE statement will (possibly) be logged together with the backup image provided that the user uses a special option.
If BACKUP DATABASE is done on a master:
Backup will not be synchronized with replication position. This means that it is hard to know what position one should use on the master after doing a restore. In later versions, the slave position will be synched with the backup image and thus the restore will set the replication state correctly so that it may continue replication. See MySQL’s WL#4105 worklog task description.
If RESTORE is done on a slave:
Replication shall not be allowed to execute while doing restore.
BACKUP DATABASE on a master does not affect a slave.
RESTORE on master will not affect slaves if no slaves are allowed during restore — that’s MySQL’s recommendation.
After RESTORE, the step of ‘replaying the binlog’ should normally be with binlogging off.
At the time of this writing, the work for synchronization with replication is being completed, for the first quarter of 2008 (using Sun’s system of quarters). Some of the strategies being considered include:
The BACKUP DATABASE statement is:
() replicated and the slave also makes an image file
() replicated but the slave does not make an image file
() not replicated
The RESTORE statement is:
() replicated and the slave also restores from an image file
() not replicated
() not even noted in bin log
The mysql database
There is a database named mysql which contains descriptions of objects and persistent information about all other databases. You cannot backup this database –
BACKUP DATABASE mysql TO ‘file’; /* This causes an error */
BACKUP DATABASE * TO ‘file’; /* This doesn’t back up mysql */
Therefore a backup image is not complete protection against a media crash, because the tables in mysql are necessary for reading tables in other databases. Therefore database administrators must take additional precautions with mysql database tables.
One possible set of additional precautions is:
1. LOCK TABLES for all tables in the mysql database.
2. Copy (with SQL or with operating system) all contents of mysql tables to a file, for example ‘mysql-image.bak’.
3. UNLOCK TABLES.
4. Preserve ‘mysql-image.bak’ along with the latest backup image.
Then, after a media crash and a RESTORE, a person with root privileges can add the data back with INSERT statements, using the data in the mysql-image.bak file for inspiration.
At time of writing the items in this category are:
Users and global-level privileges
Everything that’s in the table mysql.user.
Plugins
Everything that’s in the table mysql.plugin.
This does not include code. The code is in a different category, ‘Belonging but not exclusively belonging’.
Connections for federated tables
Everything that’s in the table mysql.servers.
Help
Everything that’s in the table help_category.
Everything that’s in the table help_keyword.
Everything that’s in the table help_relation.
Everything that’s in the table help_topic.
Time zone
Everything that’s in the table time_zone.
Everything that’s in the table time_zone_leap_second.
Everything that’s in the table time_zone_name.
Everything that’s in the table time_zone_transition.
Everything that’s in the table time_zone_transition_type.
Logs
general_log
slow_log
UDFs
LOGFILE GROUPS
mysql.host
mysql.db
mysql.host
mysql.ndb_binlog_index
mysql.online_backup
Known restrictions
Deliberate restrictions which will not change:
1. It is not possible to have BACKUP DATABASE or RESTORE in a function, stored procedure, event, or trigger.
2. It is not possible to PREPARE a ‘BACKUP DATABASE’ statement or a ‘RESTORE’ statement.
3. It is not possible to back up the mysql database (see section “The mysql database”) or the information_schema database.
Accidental restrictions which may someday change:
1. The maximum backup length may be restricted due to the maximum file length with some file systems. For example, file sizes may be limited for 4GB for the file system in certain releases of the Microsoft Windows operating system.
Coming Soon
Online Backup is not a completed feature. This document has only said what is actually here today. By the time that the the feature is “beta”, you should also see:
Syntax for BACKUP DATABASE statement changes:
1. There is an additional optional BACKUP DATABASE clause: [ COMMENT 'string' ]. For example:
BACKUP DATABASE d TO ‘file’ COMMENT ‘x’;
This is useful for putting a ‘tag’ on an image file, and for describing the contents.
2. There is an additional optional SKIP DEPENDENCY CHECKING clause: [ SKIP DEPENDENCY CHECKING ]. For example:
BACKUP DATABASE d to ‘file’ SKIP DEPENDENCY CHECKING;
This clause has the following effect …
If database X refers to database Y (for example because there is a view in X that selects from Y), and you are backing up database X but you are not backing up database Y, and MySQL determines that the dependency is serious enough, then you will see this error:
xxxx Database %s contains a reference to database %s
But if SKIP DEPENDENCY CHECKING is true, you will not see an error. Instead you will see this warning:
yyyy Warning: Database %s contains a reference to database %s
3. Privilege requirements for BACKUP DATABASE will be:
BACKUP, a new global privilege
FILE
SELECT or equivalent privilege on all objects in database.
4. Privilege requirements for RESTORE will be:
RESTORE, a new global privilege
FILE
CREATE USER
Further privileges depending on what you are restoring:
database: CREATE, DROP, GRANT OPTION, EVENT
table: CREATE, ALTER, DELETE, DROP, GRANT OPTION, INSERT, SELECT, UPDATE, TRIGGER
index: CREATE
routine: ALTER ROUTINE, CREATE ROUTINE, EXECUTE
view: CREATE VIEW, SHOW VIEW
You may ask: how can I be expected to have a privilege on table t if I haven’t yet restored table t’s database? Answer: your privileges are part of what is restored. Thus for RESTORE which restores database1, I need all the “databases” privileges on database1, I need all the “tables” privileges on every table in database1, I need all the “stored routines” privileges on every function in database1, I need all the “views” privileges on every view in database1, I need FILE privilege always. I need CREATE USER always.
5. Privilege checking must happen before the first database is backed up or restored. This will ensure that, if there is a privilege problem, you will find out at the start, before any data is backed up or restored.
6. A “native driver” for MyISAM will reduce blocking when a MyISAM table is being backed up.
Enterprise
Users of MySQL “Enterprise” will receive “native drivers” for major MySQL storage engines including Falcon. This will reduce blocking and put the “online” into “online backup for Falcon”.
There was talk at one time about arranging for some advance features (compression, encryption) to be deleivered only as part of the MySQL Enterprise subscription. That will not be the case, according to the current plan, as far as we know. But we’re writing the technical article not the explanation of how GPL works.
Further reading
Zmanda blog “MySQL Online Backup”
public worklog tasks containing “backup” in title/description
Posted in MySQL 6.0 New Features | No Comments »
I can find most of MySQL’s “timeout” variables with a SHOW statement:
mysql> show variables like '%timeout%';+----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | falcon_lock_wait_timeout | 50 | | innodb_lock_wait_timeout | 50 | | ...................................| | interactive_timeout | 28800 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | table_lock_wait_timeout | 50 | | wait_timeout | 28800 | +----------------------------+-------+ 11 rows in set (0.01 sec)
The straightforward list conceals several anomalies and buried controversies.
* Should we allow non-integer values like 0.55 seconds, or perhaps make all values milliseconds rather than seconds, in order to reflect the importance of blocking for as little time as possible?
* Should we allow the value to be zero? If so, does zero mean “there is no timeout” (i.e. zero = infinity), or does 0 mean “timeout should go on for zero seconds”?
* Should the default be 50 for more cases, or tailored to something that looks less arbitrary?
* Should it be possible to set timeouts per session, or should it always be done with GLOBAL?
It’s a set of questions that we had to revisit in January when falcon_lock_wait_timeout appeared, and we’ll see it again for an upcoming timeout variable in BACKUP. And my opinion is: I wish we had a magic formula for every question that ever arose at MySQL.
Posted in Personal Opinion | No Comments »
In the earlier article about upcoming features, “Roadmap”, there was no mention of materialized views. The explanation is simple: they’re not on the roadmap. Yet.
However, this is one of the things that might change. There is a strong desire for materialized views, and one of MySQL’s architects has a strong interest (he wrote his PhD thesis on the topic). So if their absence from the “Roadmap” article troubled you, despair not. Keep watching this space and maybe there will be further news someday.
Posted in News | 2 Comments »
For those who haven’t memorized the names of every symbol: the tilde is the wavy line that appears occasionally over specific characters, particularly over N in Spanish. But it can appear alone. If you’re North American and you look at the top left corner of your keyboard, you’ll probably see it:
~
Anyway, we ran into this bug during a comprehensive re-test of all the Chinese character sets:
mysql> create table tbig5 (s1 char(5) character set big5);
Query OK, 0 rows affected (0.11 sec)mysql> insert into tbig5 values ('Y'),('~');
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tbig5 where s1 = 'Y';
+------+
| s1 |
+------+
| Y |
| ~ |
+------+
2 rows in set (0.04 sec)
This is Bug#25420 “Tilde = ‘Y’ in Chinese”. It has been around for years, ever since the contribution of the BIG5 Chinese character set. No Chinese user has every complained — frankly I doubt that the Chinese have found much use for ~s yet. Alexander Barkov had no difficulty fixing it. It’s a trivial bug and a trivial fix. Um. Except that we must think about consequences even for trivia.
Indexes in MySQL are usually in order. (Don’t say “duh”, please, they don’t have to be thus, and in fact Falcon indexes can be a tiny bit disordered with no danger.) So it’s just possible that somewhere a Chinese database contains the index keys in order:
~ Y Z
and that somebody, after the fix for Bug#25420 is in, will add a new tilde, making the index look like
~ Y Z ~
which is no longer in order.
Call us finicky, but we can’t just put the bug fix into a production release, or even a release candidate like MySQL 5.1. The plan is to turn the fix on for MySQL 6.0, and encourage checks when users upgrade, perhaps with a warning during the mysql_upgrade script.
Posted in Tiny Tweaks | No Comments »
The verdict is in: DTrace will be on by default.
This is a wildly different thing from saying “DTrace will be there”. No right-minded person would want to eschew the benefits of DTrace, the performance-analysis tool par excellence from Sun Microsystems for all varieties of Solaris operating systems. Even if you aren’t a Solaris user, I’d suggest you have a quick glance at the literature about it because it’s the concept that non-Sun folk will surely try to imitate someday. And who knows, maybe you’ll become a Solaris user just because of this. I’ve heard of a consultant in California who does Oracle gigs thus: he goes into the shop where they’re running (say) some other Unix variant, asks them to copy their data to a Solaris machine that he handily brings with him, and re-runs their problem queries with DTrace to find the bottlenecks.
But the controversy (if I can call it that) was over whether the standard MySQL builds would have DTrace hooks on “by default”, so that performance analysts wouldn’t have to recompile. That’s now been decided: yes it’s on by default. This naturally leads to the question of how ordinary users will be able to remove it, and that’s been decided too: they can recompile with a new option –disable-dtrace.
I suppose, judging from comments that happened on another issue, that somebody will think that decisions like these are due to the recent merging of Sun and MySQL. Wrong. The decision was strongly supported by people who were part of MySQL long before the merger.
Posted in Tiny Tweaks | No Comments »
Occasionally I hear the worry that MySQL might plan a feature that’s “half baked”. The term’s users include some of the world’s top MySQL experts so I’ll avoid a technical argument.
“In future we should not release a version with half-baked features and call it enterprise-ready.”
– Konstantin Osipov
http://www.xaprb.com/blog/2007/08/12/what-would-make-me-buy-mysql-enterprise/
“will Falcon be pushed hard as Innodb replacement even if it is half baked?”
– Peter Zaitsev
http://www.mysqlperformanceblog.com/page/2/?s=falcon&search_posts=true
As for me, though, I believe in features that others call “half baked”. I just don’t use the term much myself because it seems to convey a wistful regret. While “we’re halfway there” would generally be taken as a report of good progress.
The analogy seems to be with a cake: if you take a cake out of the oven and let it cool, and then find it’s not done, and then put it back in the oven to bake again, it won’t be good. But I prefer to analogize with a journey: if you take one step then you have completed a useful fraction of it. Of course, that assumes that the step is in the right direction, which means you must decide in advance what your destination is, and it is that — the failure to decide the destination — that causes embarrassing steps in seemingly random directions. I call those “drunkard’s walk” features, rather than “half baked” features. A bad sign is if someone at MySQL says “let’s do this because it’s low hanging fruit”, but I’m happy to say that I’m hearing those dread words less frequently nowadays.
So when I hear about a “half baked MySQL feature”, I’d want proof, not that this is only part of a job, but that it’s not getting us toward the job we really want.
Posted in Personal Opinion | 1 Comment »
While we were batting forth ideas about the SIGNAL statement (MySQL 7.0, we appreciate your patience), the question arose: what do we call the thingabummie number that MySQL uses for errors and warnings? You know, this thing …
mysql> crete table t;
ERROR 1064 (42000): You have an error in your SQL syntax; …
What do we call that ‘1064′? An old-timer might say SQLCODE but actually the SQL standard committee decided a decade ago to dump SQLCODE in favour of SQLSTATE, which is a string rather than a number. Besides, MySQL’s number doesn’t follow the old standard conventions for SQLCODE.
There were 7 choices for the term for the numeric return, that we’ll use eventually in SIGNAL / RESIGNAL / GET DIAGNOSTICS syntax and descriptions.
1. SQLCODE.
SQLCODE does not appear in the standard, but it is the DB2 term
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzala/rzalaco.htm
and DB2 specifically has DB2_RETURNED_SQLCODE in GET DIAGNOSTICS
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_signalstatement4externalsqlpl.htm
(DB2 also has “error code” but that’s for a different thing.)
Oracle uses SQLCODE (as well as SQLERRM) for its exception handlers
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/13_elems049.htm
2. NativeError, “Native Error”, or NATIVE_ERROR.
The term NativeError is standard, but only for 9075-3 SQL/CLI.
It’s in SQLError() and SQLGetDiagRec().
It’s also called “ODBC Native Error”, and I suppose our connector people would recognize it.
3. “Error Message Number”.
I believe this term makes sense when there is a one-to-one relation for all messages and numbers, but with SIGNAL that won’t be the case.
4. “mysql_error_code”
This is a term that now appears in the manual page for DECLARE … CONDITION. That’s a change. When I wrote the original description I said “mysql-specific error code”.
5. “error_code” / “error code”
6. “errno”
7. “error_number” / “error number”
The MySQL Reference Manual varies. I found four different terms
(”error code”, “error number”, “error message number”, “mysql_errno”)
on the same manual page
http://dev.mysql.com/doc/refman/6.0/en/mysql-errno.html.
There’s no clear favourite.
Google search results within site:dev.mysql.com/doc: "error code" or "error codes" 1030 "mysql_errno" i.e. the API function name 817 "errno" 236 "error number" or "error numbers" 215 "mysql_error_code" 8 "error message number" or "error message numbers" 2 "nativeerror" or "native error" or "native errors" 1 "sqlcode" 0
Exactness is misleading, because terms are not always used in the sense that we’re discussing.
So we’re playing around with terminology voting now. The take-home for our end users should be: (a) gee, things must have reached an advanced stage if they’re putting in tacks rather than nails now, (b) it’s nice to know that all these things are the same.
Posted in Tiny Tweaks | No Comments »
A recent patch for 6.0 has given users the ability to delay SELECT statements in certain circumstances.
If there’s an INSERT DELAYED going on, then the SELECT can wait for it, or not, depending on the option that the user chooses. This is not the same as saying “there will be a SELECT DELAYED like INSERT DELAYED” — the conditions are not the same. But the new option (whatever it’s finally called) will help out in some debugging and synchronization scenarios.
Posted in Tiny Tweaks | No Comments »
New Features In MySQL 6.x is proudly powered by
WordPress MU running on Blogs.mysql.com.
Entries (RSS)
and Comments (RSS).