Home > mysql > Backup methods and tools for MySQL

Backup methods and tools for MySQL

MySQL needs to have different backup recovery system, which will prevent loss of files generated into the system. This mostly happens during upgrading of systems or integration of different networks.

Causes of crashes

There are different causes that lead crashing of the system and this makes it harder for one to recover. With a number of different solutions to use, it is wise to understand the methods that cause hitches and try different ways to prevent. Some of the common causes include

Crashing of the operation system

Abrupt and constant power failure

Crashing of the file system

Where there are issues with hardware, motherboard, or hard drive

These are the possible areas ,that lead to constant crashing of the system and this means all data stored have higher chances of getting lost but there are different methods one can use to restore the files.

How to manage using a file snapshot system

It is important for the programmer to take into account different methods that make it easier for one to get the data files in the system. This systematic process needs attention to detail in order to get recommendable results.

-  create a client program, and execute FLUSH TABLES WITH READ LOCK.

- create another shell, execute mount vxfs snapshot

-    choose first client, execute UNLOCK TABLES.

-    Copy files from the snapshot

-    Unmount the snapshot.

 

Tools used in the backup and recovery process

XtraBackup

mydumper

mysqlhotcopy

Zmanda Recovery Manager (ZRM) for My

mysqldump

mylvmbackup

ZRM with XtraBackup (and streaming)

 

MySQL backup methods and characteristics

Backup uses querying the MySQL server to get database construction and gratified information

Backup is gentler than physical approaches because the server should access database material and adapt it to logical format. If the production is written on the client side, the server must also send it to the holdup program.

Output is particularly bigger than physical backup, predominantly when saved in text setup

Backup and reinstate granularity is obtainable at the server level), database level (all tables in a, or table level. this happens regardless of the type of server of backup device.

The backup should not compose log or configuration files, or database-related files that are not part of databases.

Process of data backup and recovery process sign MySQL

InnoDB: Database not shut down normally.

InnoDB: Starting recovery from log files…

InnoDB: Starting log scan based on checkpoint at

InnoDB: log sequence number 0 13674004

InnoDB: Doing recovery: scanned up to log sequence number 0 13739520

InnoDB: Doing recovery: scanned up to log sequence number 0 13805056

InnoDB: Doing recovery: scanned up to log sequence number 0 13870592

InnoDB: Doing recovery: scanned up to log sequence number 0 13936128

InnoDB: Doing recovery: scanned up to log sequence number 0 20555264

InnoDB: Doing recovery: scanned up to log sequence number 0 20620800

InnoDB: Doing recovery: scanned up to log sequence number 0 20664692

InnoDB: 1 uncommitted transaction(s) which must be rolled back

InnoDB: Starting rollback of uncommitted transactions

InnoDB: Rolling back trx no 16745

InnoDB: Rolling back of trx no 16745 completed

InnoDB: Rollback of uncommitted transactions completed

InnoDB: Starting an apply batch of log records to the database…

InnoDB: Apply batch completed

InnoDB: Started

mysqld: ready for connections

 

Categories: mysql Tags:
  1. mansi
    January 28th, 2013 at 23:29 | #1

    I am not aware about myvlmbackup tool. Can you explain it a little bit more? As I too have written an article on backup methods of MySQL, but haven’t mention about this tool.

  1. No trackbacks yet.