Mani's Blog

January 7, 2013

InnoDB: table %s/%s: unknown table type 33

Filed under: MySQL,Xtrabackup — mani @ 11:21 pm

Ran into issue while restoring and recovering using xtrabackup.  innobackupex-1.5.1 –apply-log ./

130107 20:32:49 InnoDB: table bac002/_user: unknown table type 33
130107 20:32:49 InnoDB: table bac002/_user: unknown table type 33
130107 20:32:49InnoDB: Assertion failure in thread 1230002496 in file ibuf0ibuf.c line 1309
InnoDB: Failing assertion: len % DATA_NEW_ORDER_NULL_TYPE_BUF_SIZE <= 1
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
innobackupex-1.5.1: Error:
innobackupex-1.5.1: ibbackup failed at /usr/bin/innobackupex-1.5.1 line 464.
root@@xnomdb912n2:/local/mysql/data#

Solution:

I was using incorrect my.cnf file,  in my case I had incorrect binlog-format, and few other parameter.  Once I fixed them then restore/recovery worked just fine.

Advertisements

March 30, 2012

xtrabackup: Fatal error: cannot find ./xtrabackup_logfile

Filed under: MySQL,Xtrabackup — mani @ 4:37 pm

We have a process to validate our backup every quarter.  I ran into following errors on one of the databases.  Did enough search to figure out the solution, initially thought it could be an xtrabackup bug.  Then turn out to be we were holding incomplete xtrabackup.  Backup failed quietly since there was another etl process running at the same time, due to heavy load and lock on the tables backup process died.

We are really lucky that we were able to catch it during the validation.   We have rescheduled the ETL job and added more checks to the backup monitoring.

Errors:

120228 19:25:55 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup –prepare –target-dir=/local/mysql/data

xtrabackup Ver 1.0 Rev 113 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: cd to /local/mysql/data
xtrabackup: Error: cannot open ./xtrabackup_checkpoints
xtrabackup: error: xtrabackup_read_metadata()
xtrabackup: This target seems not to have correct metadata…
120228 19:25:55 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
120228 19:25:55 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Fatal error: cannot find ./xtrabackup_logfile.
xtrabackup: Error: xtrabackup_init_temp_log() failed.
innobackupex-1.5.1: Error:
innobackupex-1.5.1: ibbackup failed at /usr/bin/innobackupex-1.5.1 line 464.

November 10, 2011

MySQL: Point_In_Time Recovery using Xtrabackup

Filed under: Backup,MySQL,Xtrabackup — mani @ 6:11 am

Point_In_Time Recovery

This blog would be helpful If you are using XtraBackup to backup MySQL databases and try to restore and recover databases.  This recovery would be needed when user/admin commits errors like running any statements(truncate table, drop table, etc.) mistakenly. In this case you may need to take your DB to Point-in-Time before the incident.

  1. Restore the database from selected backup. Backup file location may be different for everyone. /local/mysql/data is datadir location.
    cd /local/mysql/data
    tar -ixvzf /local/mysql/backups/xnomdb001n2/2011-10-26_01-00-02_35569.tar.gz
  2. Apply the log
    cd /local/mysql/data
    innobackupex-1.5.1 --apply-log ./
  3. Fix the privileges
    chown -R mysql:mysql *
  4. Start MySQL
    /etc/init.d/mysql start
  5. Identify the binlog file and position
    root@@xnomdb912n2:/local/mysql/data#cat xtrabackup_binlog_info
    binary-logs.000138      36567332
  6. Capture binlog files info from current production server
    (root@localhost) [(none)]> show binary logs;
    +--------------------+------------+
    | Log_name           | File_size  |
    +--------------------+------------+
    | binary-logs.000131 |    9774400 |
    | binary-logs.000132 | 1073742520 |
    | binary-logs.000133 | 1073741997 |
    | binary-logs.000134 | 1073743592 |
    | binary-logs.000135 | 1073745994 |
    | binary-logs.000136 | 1073742312 |
    | binary-logs.000137 | 1073742367 |
    | binary-logs.000138 | 1073742549 |
    | binary-logs.000139 | 1073760891 |
    | binary-logs.000140 550608024 |
    +--------------------+------------+
    10 rows in set (0.00 sec)
  7. Identify the current binlog position from the production.
    (root@localhost) [(none)]> show master status;
    +--------------------+-----------+--------------+------------------+
    | File               | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
    +--------------------+-----------+--------------+------------------+
    | binary-logs.000140 | 550917898 |              |                  |
    +--------------------+-----------+--------------+------------------+
    1 row in set (0.00 sec)
    (root@localhost) [(none)]>
  8. scp the required binlogs files to recovery server
    Example:
    scp binary-logs.000138 root@xnomdb912n2:/local/mysql/mojo
    scp binary-logs.000139 root@xnomdb912n2:/local/mysql/mojo
  9. Generate SQL file off of binlog, which generates all transaction statements into a file. Look at the file and find position and/or time, so the DB can be recovered to that point-in-time.
    mysqlbinlog /local/mysql/mojo/binary-logs.000138   /local/mysql/mojo/binary-logs.000139 --start-position=36567332 > /local/mysql/mojo/mybinlog.sql
  10. Apply transactions to MySQL engine.
    mysqlbinlog /local/mysql/mojo/binary-logs.000138 /local/mysql/mojo/binary-logs.000139 --start-position=36567332 --stop-datetime="11-10-26 3:13:51" | mysql -u root -p

Create a free website or blog at WordPress.com.