Posted by Sheeri Cabral on Dec 1, 2009
OpenSQLCamp was a huge success! I took videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded). Unfortunately, I was busy doing administrative stuff for opensqlcamp for the opening keynote and first 15 minutes of the session organizing, and when I got to the planning board, it was already full….so I was not able to give a session.
Posted by Sheeri Cabral on Nov 10, 2009
Last night at the Boston MySQL User Group I presented on how to get a consistent snapshot to build a slave, how to use mk-table-checksum to check for differences between masters and slaves on an ongoing basis, and how to use tools such as mk-table-sync and mysqldump to sync the data if there are any discrepancies.
The slides are online at http://technocation.org/files/doc/slave_sync.pdf.
The video can be watched on youtube at http://www.youtube.com/watch?v=Un0wqYKmbWY or directly in your browser with the embedded player below:
Read the rest of this entry . . .
Posted by Sheeri Cabral on Oct 1, 2009
Firstly, I have to thank my co-workers Singer Wang and Gerry Narvaja for doing a lot of the work that resulted in this comparison.
After running both InnoDB Hot Backup and Xtrabackup, we have found that there is a measurable but not large difference between the resources that Xtrabackup and InnoDB Hot Backup consume.
Xtrabackup:
Posted by Singer Wang on Aug 31, 2009
Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran mysql_upgrade:
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Running 'mysql_fix_privilege_tables'...
ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
FATAL ERROR: Upgrade failed
The problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the mysql schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are created by default as CSV tables for performance reasons, and are created even if MySQL is set not to log to tables.
CSV tables, however, are not copied as part of the InnoDB Hot Backup process (by the wrapper script innobackupex.pl), thus creating this error. The fix to get the slave working Read the rest of this entry . . .
Posted by Sheeri Cabral on Jun 30, 2009
A short time ago I posted how I was Using XtraDB Backup to backup InnoDB. Overall, the blog post was positive, but experiences that others have had (and commented to on that blog post) have made me want to put out another short article about using XtraDB backup.
The first few points remain the same — the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl.
However, we did figure out why Xtrabackup had to be run as the mysql user:
Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs:
xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.
InnoDB by default requires O_RDWR option on ibdata1 files at start, and xtrabackup therefore also did that. In the latest push to trunk it was fixed, now xtrabackup opens file with O_RDONLY flag.
When the new version is released, we will be sure to test it so that we can run the backup as a read-only user, and report back.]
On the one hand, Xtrabackup is a free tool. On the other hand, modifying InnoDB’s underlying files risks corrupting all the InnoDB tables in the system. Which is a tricky situation when it is your backup tool that might cause corruption that is beyond repair, as you do not know if you can trust your backups.
Read the rest of this entry . . .
Posted by Sheeri Cabral on Jun 9, 2009
As Baron points out, Percona’s Xtrabackup tool can be used just like InnoDB Hot Backup.
Many are wondering, “is it good enough?” In fact, I wondered the same thing, and after a few weeks of using and testing Xtrabackup (on machines that have MyISAM and InnoDB tables), I can say:
0) We have not run into any problems with backing up InnoDB tables; on the machines we’ve run it on (RHEL 5.3 and Debian) it has been stable.
1) We were able to use one of the binaries provided by Percona — we did not have to compile anything. Binaries are provided for 64-bit versions of Linux
2) Innobackupex is the script analogous to Innobackup. Basically, this is a wrapper script for Xtrabackup that will copy the non-InnoDB tables.
3) Usually we run InnoDB Hot Backup from a non-privileged user. We had to run Xtrabackup as the mysql user; we think this is because it writes a state file in the datadir, but we did not dig too hard — once we saw that running it from the mysql user worked, we just left it like that.
4) Xtrabackup does not have a –compress option like InnoDB hot backup does. Having the option would be useful, but I believe this option will come soon.
Currently, for clients who want a hot backup solution but do not want to pay for InnoDB Hot Backup, Pythian recommends Xtrabackup.
