Data loss can be a common experience of computer users; a 2008 survey found that 66% of respondents had lost files on their home PC.
A backup refers to the copying into an archive file of computer data so it may be used to restore the original after a data loss event.
If a computer system is not having active IOs to the backup directory and we copy the data to archival location then we have a good copy of backup ( consistent backup ) . If a computer system is in use while it is being backed up, the possibility of files being open for reading or writing is real. If a file is open, the contents on disk may not correctly represent what the owner of the file intends. This is especially true for database files of all kinds. The term fuzzy backup can be used to describe a backup of live data that looks like it ran correctly, but does not represent the state of the data at any single point in time. This is because the data being backed up changed in the period of time between when the backup started and when it finished.
Read my earlier post about the Backup Types.
Running MySQL consistent backup ways
If you have actively running MySQL server and to take a backup of the running machine there are two approaches widely used
- Physical Backup
- Logical Backup
MySQL Physical Backup
MySQL Physical backup is raw copies of directories and files, suitable for large databases and quickly recoverable. A MySQL Raw Backup is performed quicker than a logical backup, because it does not translate the contents of the database into human readable SQL queries. It simply copies data files from one storage to another and the output is more compact than a logical backup. In addition to databases, the backup can include any related files such as log or configuration files.
MySQL Logical Backup
MySQL Logical backup saves information represented as logical database structure and content, usually achieved using mysqldump. A logical backup is slower than a physical backup, because the server must access the database and convert the physical data into a logical format. If the output of a logical backup is written on the client side, the server must also send the logical format to the backup program, hence the overall performance is affected by this aspect.
Backups in logical format are large, particularly when saved in text format, and often slow to create and restore.
To create a consistent MySQL Hot backup, we have to hold the writes to the database before taking the backup, Physical Backup is recommended as it is fast comparing the Logical backup so we don’t need to hold the writes for long time in the production databases.
