MySQL Maintenance, Backup & Recovery

Binary Log

Binary log files (aka Oracle redo logs) record all SQL statements that update the database data. These files are for incremental backup and critical for system recovery.

Find binary log information:

mysql> SHOW MASTER STATUS

Enable MyISAM Recovery Capabilities when Starting mysqld

Startup options recommended for recovery

% mysqld -u root -p --log-bin=log-bin --myisam-recover

--login-bin: Turn on binary logging. (Required for production system)
             During recovery, past binary logs are replayed for database recovery.
--myisam-recover: Execute automatic MyISAM recovery when system restart

Routine Table Maintenance for MySQL

Using mysql
mysql> (mysql command) Description Example
CHECK TABLE Check table(s) for error

Perform this regularly using cron job
CHECK TABLE tbl FAST QUICK
REPAIR TABLE Perform a backup before repair a table

Repeat it if REPAIR TABLE failed
REPAIR TABLE tbl
OPTIMIZE TABLE Remove un-used space and defragment data

Optimize table when large amount of data is deleted or changed
OPTIMIZE TALE tbl
ANALYZE TABLE Collect statistic data. The table will be locked ANALYZE TABLE tbl
ALTER TABLE tbl ENGINE=INNODB Rebuild and defragment an InnoDB table  
Using mysqlcheck

Note:

  • mysqlcheck locks the table automcatically through mysqld and prevents access by other clients.
  • mysqlcheck can be time consuming
  • mysqlcheck needs mysqld running
% mysqlcheck [options] my_db [tabl]
% mysqlcheck [options] --databases my_db1 [my_db2 ... ]
% mysqlcheck [options] --all-databases

Options:
--check
--repair
--analyze
--optimize
Routine Cron Job

Use myisamchk for regular MyISAM table check

Sample cron entry on Unix:
30 2 * * 0 /path/to/mysqlcheck --all-databases --check-only-changed --silent

Logical Database Backup/Recovery using mysqldump/mysql on MySQL

Dump partial MySQL data

Dump table(s)

% mysqldump my_db my_tbl1 my_tbl2 > /archive/mysql/tbl1_tbl2_backup_YYYY_MM_DD_HH_MM.sql

Dump a MySQL DB

% mysqldump my_db > /archive/mysql/my_db_backup_YYYY_MM_DD_HH_MM.sql

On Unix, dump a DB and Zip it

% mysqldump my_db | gzip > /archive/mysql/my_db_backup_YYYY_MM_DD_HH_MM.gz

Dump multiple DBs

% mysqldump --databases my_db1 my_db2 > /archive/mysql/my_db_backup_YYYY_MM_DD_HH_MM.sql

Other options:

-routines Also backup stored procedures
--events  Also backup events
Dump all DB
% mysqldump --single-transaction --flush-logs --master-data=2
               --all-databases > /archive/mysql/backup_YYYY_MM_DD_HH_MM.sql

--single-transaction:
              Dump the tables in an transaction.
              Later changes by other sessions will not be seen.
              It ensure tables are not modified and therefore remain consistence during the dump.
--flush-logs: Flushes and rotates the binary logs at the moment of the full backup (mysqldump).
              ls c:\mysql\data\log-bin.*

              Before FLUSH LOGS:
              log-bin.000001 log-bin.000002 log-bin.000003

              After FLUSH LOGS:
              log-bin.000001 log-bin.000002 log-bin.000003 log-bin.000004
--master-data=2: Record binary log coordinates (File name & position) when full backup starts.
              (This information is needed for data replication or point-in-time recovery)

It performs a full DB backup.  Any data changes made after it is logged in log-bin.000004 or later.

 

% mysqldump --single-transaction --flush-logs --master-data=2
               --delete-master-logs --all-databases > /archive/mysql/backup_YYYY_MM_DD_HH.sql

--delete-master-logs: Delete all binary logs before the full backup.
              (log-bin.000001 ... log-bin.000003)

NOTE: By default, all tables involved will be LOCK READ during mysqldump.

% mysqldump --opt my_db | mysql --host=remote_host -C my_db
Copy data from one MySQL server to another:

The --opt option is enabled by default. Use --skip-opt to disable it. See the discussion at the beginning of this section for information about selectively enabling or disabling a subset of the options affected by –

% mysqldump [options] my_db tbl1 tbl2
Backup tables

--optis a short hand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.

Recover from dump file
  1. If mysql DB is also restored, stop and restart mysqld with addition options
    Disable grant privilege checking so the grant table in the mysql DB can be deleted/reloaded
    --skip-grant-tables
    --skip-networking (Do not allow remote client to connect to the server)
  2. Apply full backup
    % mysql -u root -p < backup_YYYY_MM_DD_HH.sql;
  3. Apply incremental changes after the full backup
    % mysqlbinlog c:\mysql\data\log-bin.000004 c:\mysql\data\log-bin.000005 | mysql
    NOTE: All binary log file MUST be applied in one single mysqlbinlog command.
  4. If server starts with skip-grant-tables and skip-networking, remove those options and restart the DB server.

Recover individual MySQL table

  1. Do a full database recovery in another DB instance

    OR
  2. Do a full database recovery to another DB on the same DB instance with mysqldump & mysqlbinlog

    NOTE:

    mysqldump's dump file may contain USE statement(s). Manually replace it with the new DB name

    mysqlbinlog may also contain USE statement. Dump the log into a text file and replace it with the new DB name
    mysqlbinlog c:\mysql\data\log-bin.000004 c:\mysql\data\log-bin.000005 > out.txt
  3. Use mysqldump or SELECT INTO OUTFILE to export a table
  4. Drop the table
  5. Use mysql or LOAD DATA to import the table
Cautious in recovery the DB mysql
  • Use option -skip-grant-tables to start MySQL to skip privillege check
  • Recover Db "mysql"
  • FLUSH PRIVILEGES

Physical Files backup using mysqlhotcopy (MyISAM tables)

Benefits of mysqlhotcopy

  • Faster than mysqldump by using direct file copy
  • Internal lock done automatically
  • Flush binary log automaticatlly

But:

  • Can only run on the same server as the DB instance
  • Only for MyISAM and ARCHIVE tables
  • Only on Unix and NetWare.
mysqlhotcopy db_name /path/copy_to/

Physical InnoDB Files backup

  1. Shut down the MySQL instance gracefully
    % mysqladmin -u root -p shutdown
  2. Backup the following file:
    File Description File name(s) Path Example
    Datafiles (for shared tablespace configuration) /var/lib/mysql/ibdata* /path/to/data c:\mysql\data\ibdata1
    Datafiles (for individual tablespace configuration) *.ibd /var/lib/mysql/mydb c:\mysql\data\webdb*.ibd
    Format files *.frm /var/lib/mysql/mydb c:\mysql\data\webdb\tbl1.frm
    Log files ib_logfile* /var/lib/mysql/ib_logfile1 ib_logfile* c:\mysql\data\ib_logfile*
    Option file my.ini/my.cnf /etc/mysql/my.cnf c:\mysql\mysql-5.1\my.ini

MySQL Configureation & Log Files Backup

For physical file backup, also backup:

  • Option file c:\mysql\mysql-5.1\my.ini
  • binary log file C:\mysql\data\bindata.*, C:\mysql\data\bindata.index

For a slave instance

  • master.info: Info on how to connect to the master
  • relay-log.info
  • relay log file C:\mysql\data\relay-bin.*, C:\mysql\data\relay-bin.index
  • SQL_LOAD-* stored in --slave-load-tmpdir variables (if not defined, it is stored in location defined by System variable tmpdir)

MySQL Backup using Slave Server

Instead of backup the primary DB server. Data can be replicated to a slave server and backup is done on the slave server. Instruction is located at Using Slave for DB Backup

MySQL Point-in-time Recovery

  • Recovery system to a specific time (say 2009-08-11 14:00:00)
  • Recovery data but by-pass some events, say a table is deleted by mistake around 14:01
  1. Examine corresponding bin log to determine the time/log position when the problem starts and end
    % mysqlbinlog --start-datetime="2009-08-11 13:55:00" --stop-datetime="2009-08-11 14:05:00"
            c:\mysql\data\bindata.000005 > out.sql
  2. Apply the last Full DB backup before the event
  3. Apply the incremental changes based on time
    % mysqlbinlog --stop-datetime="2009-08-11 14:00:00" c:\mysql\data\log-bin.000004
                c:\mysql\data\log-bin.000005 | mysql -u root -p

    (If needed) Apply binary log after the event

    % mysqlbinlog --start-datetime="2009-08-11 14:01:00" c:\mysql\data\log-bin.000005 | mysql -u root -p
  4. (Alternatively) Apply the incremental changes based on log position
    % mysqlbinlog --stop-position="34555" c:\mysql\data\log-bin.000004
                c:\mysql\data\log-bin.000005 | mysql -u root -p
    % mysqlbinlog --start-position="34586" c:\mysql\data\log-bin.000005 | mysql -u root -p

Check, Recover & Optimize MyISAM Table

NOTE:

  • myisamchk manipulates data externally to the DB instance and hence
    • It is required to lock the table first to avoid race condition.
    • Or MySQL instance (mysqld) is down
  • Work with MyISAM table only.
  1. Login to mysql
  2. Lock the table

    Lock table(s)
    mysql> LOCK TABLE tbl1,tbl2 READ;   # All tables MUST be locked in one single LOCK TABLE command
    mysql> FLUSH TABLE my_table;        # Flush changes on my_table to the disk

    Lock other session from read/write on my_table. (Required for recovery operation)

    mysql> LOCK TABLE my_table WRITE;   # Read/Write for this session and none for other sessions
    mysql> FLUSH TABLE my_table;        # Flush changes on my_table to the disk

    Lock all tables and make it read only

    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> SET GLOBAL read_only = ON;

    NOTE: Do NOT quit the mysql session. Quitting a session will automatically un-lock the table

  3. Open another command window and check the table. Sample command:
    % myisamchk C:\mysql\data\mysql\my_table
    % myisamchk C:\mysql\data\mysql\*
    % myisamchk --silent --fast C:\mysql\data\mysql\*
  4. Unlock table(s)

    Unlock table
    mysql> UNLOCK TABLE
    mysql> UNLOCK TABLES

    Unlock tables when SET GLOBAL read_only = ON is used

    mysql> SET GLOBAL read_only = OFF;
    mysql> UNLOCK TABLES;
myisamchk (Checking)

Most common

% myisamchk c:\mysql\data\mysql\my_table

Most thorough check

% myisamchk -e c:\mysql\data\mysql\my_table

Reduce log message

% myisamchk -s ...
Explore error code

To find more info on the error code

perror code1 code2

Note Error 135 (no more room in record file) and error 136 (no more room in index file) are related with table configuration.

Use ALTER TABLE to increase the MAX_ROWS and AVG_ROW_LENGTH table option values:

ALTER TABLE tbl_name MAX_ROWS=nnn AVG_ROW_LENGTH=mmm;

Show original configuration:

mysql> SHOW CREATE TABLE tbl
myisamchk recovery

You should first use CHECK TABLE and REPAIR TABLE statements in mysql to repair MyISAM tables.

  1. STOP the MySQL database instance - mysqld
  2. If any, stop replication
  3. Locate the corrupted table(s)
    % myisamchk -e --update-state c:\mysql\data\mysql\*
  4. Make a backup of the corrupted table files

    tbl.frm: Definiation file

    tbl.MYD: Data file

    tbl.MYI: Index file
  5. Quick recovery
    % myisamchk -r -q c:\mysql\data\mysql\corrupt_tbl

    For faster operation,

    set --sort_buffer_size and --key_buffer_size option each to 25% of available memory

    E.g. --sort_buffer_size=256M

  6. If failed
    % myisamchk --safe-recover c:\mysql\data\mysql\tbl
  7. If failed, rebuild table
    % mysql -u root -p mydb
    mysql> SET autocommit=1;
    mysql> TRUNCATE TABLE tbl_name;
    mysql> quit
  8. Copy the data file back from the backup in the previous step
  9. Go back to step (5). But if the recovery procedure fail again, go to next step.
  10. Restore the tbl.frm from your routine backup and go back to step (5)
Optimize MyISAM table
% myisamchk -r -s --sort-index --sort_buffer_size=16M /path/to/*

Getting MyISAM table information

Basic command in getting MyISAM table information

Getting information in the table user
% myisamchk -d c:\mysql\data\mysql\user
% myisamchk -d -v c:\mysql\data\mysql\user

Output:
MyISAM file:         c:\mysql\data\mysql\user
Record format:       Packed
Character set:       utf8_bin (83)
File-version:        1
Creation time:       2009-07-13 16:47:07
Recover time:        2009-08-07 10:53:22
Status:              checked,analyzed,optimized keys
Data records:                    2  Deleted blocks:                 0
Datafile parts:                  2  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:               184  Keyfile length:              2048
Max datafile length: 281474976710654  Max keyfile length: 288230376151710719
Recordlength:                  346

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   1     180 unique  char packed stripped           1         1024       1024
    181   48          char stripped                  1

Extensive but slow command in getting MyISAM table information

% myisamchk -eis tbl_name
% myisamchk -eiv tbl_name

Copy DB to another instance

  1. Follow instruction from previous section to create a dump file from mysqldump
  2. Copy the dump file to the remote host
  3. Load the remote database using mysql

Or

  1. Follow instruction from previous section to backup DB physical files
  2. Copy the files to remote server

Export & Import data with SELECT INTO OUTFILE

Export to text file
  1. Export table data into a text file
    mysql> SELECT * INTO OUTFILE 'c:\\somepath\\tbl.txt' FROM tbl
Import from text file

NOTE: Any changes in tbl after the export will be lost

  1. Login to mySQL
  2. Delete data
    mysql> TRUNCATE TABLE tbl;

    If there are too many rows in the table

    mysql> DROP TABLE tbl;
    mysql> CREATE TABLE ...
  3. Load data
    mysql> LOAD DATA INFILE 'c:\\somepath\\tbl.txt' INTO TABLE tbl

    OR in a Windows command window

    % mysqlimport -u root -p mydb c:\mysql\tbl.txt