Video: Building a MySQL Slave and Keeping it in Sync At the Boston MySQL User Group Sheeri Cabral 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. This link contains the slides and the videos

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 OKmysql.time_zone_leap_second OKmysql.time_zone_name OKmysql.time_zone_transition OKmysql.time_zone_transition_type OKmysql.user OKRunning '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 failedThe 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  []

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  []

If you are using InnoDB Hot Backup and a recent version of mysqld (at least 5.0.67 or higher, including 5.1.30, though it may be later versions), your backup will run fine and output OK! at the end, as it should.Except for one thing.The binary log file and position do not appear in their rightful place. Here's a snippet of the output from the backup:innobackup: MySQL binlog position: filename 'Warning', position (Code 1287):'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead090210 03:55:04 innobackup: innobackup completed OK!That's pretty misleading -- looks like the backup completed OK, but it did not show us the binary log position. What about the "ibbackup_binlog_info" file?[mysql@db3:~] more ibbackup_binlog_info Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead[mysql@db3:~]Yikes! What happened to the binary log position and file information?Have no fear, it's actually in another file.

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

If you are using InnoDB Hot Backup utility and the innobackup.pl wrapper script, be very careful if you are not running backups under the system "mysql" user. There is a bug which causes InnoDB Hot Backup to sometimes report a successful backup when it actually failed.