Other aop/dbrepair/tools

Table Of Contents

Previous topic

MySQL Table Repair

Next topic

Corruption incident tmp_ligs_offline_db : timeline and next steps

This Page

Daya Bay Links

Content Skeleton

MySQL Backup/Recovery Tools

mysqldump is convenient as it works remotely, but for large DBs when you have access to the server mysqlhotcopy will be many orders of magnitude faster. As we are using mostly MyISAM tables the only way to create a consistent set of tables without locking the DB for a very long time will be mysqlhotcopy

mysqldump

The –single-transaction option is useful only with transactional tables such as InnoDB and BDB, not MyISAM tables. For MyISAM using –lock-tables seems neccessary for a consistent set, but that demands privileges I probably dont have for IHEP servers.

dbdumpload.py --tables "ChannelQuality ChannelQualityVld DqChannel DqChannelVld DqChannelStatus DqChannelStatusVld LOCALSEQNO"   tmp_ligs_offline_db dump /tmp/out.sql
time /data1/env/local/dyb/external/mysql/5.0.67/i686-slc5-gcc41-dbg/bin/mysqldump
    --no-defaults
    --skip-opt
    --extended-insert
    --quick
    --host=dybdb1.ihep.ac.cn --user=ligs --password=***
             tmp_ligs_offline_db ChannelQuality ChannelQualityVld DqChannel DqChannelVld DqChannelStatus DqChannelStatusVld --where "1=1" > /tmp/out.sql

Warning

Looks like this needs to be changed to lock

mysqlhotcopy

[blyth@belle7 DybPython]$ sudo ls -l  /var/lib/mysql/tmp_offline_db/
total 157200
-rw-rw---- 1 mysql mysql     8684 Aug 17  2012 CableMap.frm
-rw-rw---- 1 mysql mysql  2191045 Aug 17  2012 CableMap.MYD
-rw-rw---- 1 mysql mysql  1858560 Aug 18  2012 CableMap.MYI
-rw-rw---- 1 mysql mysql     8908 Aug 17  2012 CableMapVld.frm
-rw-rw---- 1 mysql mysql    25959 Aug 17  2012 CableMapVld.MYD
-rw-rw---- 1 mysql mysql     7168 Aug 18  2012 CableMapVld.MYI
...

Usage

Create a mysqlhotcopy section in ~/.my.cnf with localhost server coordinates and socket specified, the appropriate socket path can be found in /etc/my.cnf

[mysqlhotcopy]
socket    = /var/lib/mysql/mysql.sock
host      = localhost
user      = root
password  = ***

mysqlhotcopy.py

A python wrapper script using the mysqlhotcopy command and the python tarfile module and MySQL-python extension to provide sanity checking, file management, remote node transfers.