Physical Backups in MySQL (Article -20).
In this article we can cover Physical backups in MySQL.
Physical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur.
Physical backup methods have these characteristics :
- The backup consists of exact copies of database directories and files. Typically this is a copy of all or part of the MySQL data directory.
- Physical backup methods are faster than logical because they involve only file copying without conversion.
- Output is more compact than for logical backup.
- Physical backups is also known as offline backups because it requires downtime.
- Because backup speed and compactness are important for busy, important databases, the MySQL Enterprise Backup product performs physical backups.
- Backup and restore granularity ranges from the level of the entire data directory down to the level of individual files. This may or may not provide for table-level granularity, depending on storage engine.
For example, InnoDB tables can each be in a separate file, or share file storage with other InnoDB tables; each MyISAM table corresponds uniquely to a set of files.
In addition to databases, the backup can include any related files such as log or configuration files.
- Data from
MEMORYtables is tricky to back up this way because their contents are not stored on disk. (The MySQL Enterprise Backup product has a feature where you can retrieve data fromMEMORYtables during a backup.) - Backups are portable only to other machines that have identical or similar hardware characteristics.
- Backups can be performed while the MySQL server is not running. If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup. MySQL Enterprise Backup does this locking automatically for tables that require it.
- Physical backup tools include the mysqlbackup of MySQL Enterprise Backup for
InnoDBor any other tables, or file system-level commands (such as cp,scp, tar, rsync) forMyISAMtables. - Restore must be same database engine
- For example , Cannot restore MyISAM backup file into InnoDB.
- Faster than logical backups and recoveries.
- Database files must not change during backups
–> Methods to achieve that depends on storage engines.
–> For InnoDb : MySQL Server shutdown required.
–> For MyISAM : Lock tables to allow reads but not changes
–> Can use snapshot,replication , or proprietary methods.
Procedure for Physical backup(Binary copy of data files) for InnoDB Storage Engines :
1.Shutdown the MySQL Server
2.Confirm proper Shutdown
3.Copy all necessary components
4.Restart the MySQL Server.
–> we can also use InnoDB Hotbackup program.
Backup Method summary :
Tools for physical backups :
1.Percona XtraBackup
2.MyLVMsnapshot
3.MySQL Enterprise backup(Commercially Available).
Percona XtraBackup :
–> Percona Backups are very faster and have only limited I/O.
–>Almost no locks inside the server
–> On the fly files will be compressed.
–> Best option for full server backup and restore or setting up new slaves.
How to install percona xtrabackup ?
Use below article link to get the steps to install Percona xtrabackup.
How to take Physical or Raw backups using Percona backups ?
Step by step process to take backup of full MySQL server.
Step 1 :
Check the lists of Schemas and Tables in MySQL Server.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sakila | | sys | | world | +--------------------+ 6 rows in set (0.00 sec) |
Check the tables under SAKILA Schema.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
mysql> use sakila Database changed mysql> show tables; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.00 sec) |
Choose one table and count records in it (Ex : FILM table).
|
1 2 3 4 5 6 7 |
mysql> select count(*) from film; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec) |
In table film the total records are 1000.
If we want know wore about xtrabackup the issue following command.
|
1 |
[root@ip-172-31-33-50 ~]# innobackupex --help |
The Actual command to perform xtrabackup is innobackupex.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
