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
Zmanda Recovery Manager (ZRM) for My
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
mysqld: ready for connections