MySQL Backup Types (Logical vs. Physical Backups)
In this mysql tutorial we’re walking you through mysql backup types – we’ll walk you through mysql logical backups as well as mysql physical backups.
This sql tutorial will tell you that database backup can either be considered a logical database backup or a physical database backup.
A logical backup is used the most frequently and it essentially recreates all of the necessary SQL query statements to populate data inside of tables.
When taking logical backups in MySQL server, DBAs employ either mysqldump, mydumper, or Percona XtraBackup. mysqlpump was also an option, but the tool has gotten deprecated as of MySQL server 8.0.34.
Physical database backups mean database backups of physical files – meaning files related to our mysql storage engine of choice – the two of the most frequent MySQL storage engines would be InnoDB or Percona XtraDB and MyISAM.
To take a physical backup of InnoDB or XtraDB within your MySQL server, you need to backup:
– The ibdata1 file storing information vital for the storage engine to function correctly;
– ib_logfile* files containing the InnoDB redo logs;
– All files having an .ibd extension – those files store tables within your databases;
– All files having a .frm extension – these files describe the format of the tables;
– And last but not least, take a backup of the configuration file.
If you’re considering what to use – mysql myisam vs innodb – and want to use the myisam storage engine instead, keep in mind that in MyISAM, taking physical backups within your MySQL server is a little easier. All you need to do is lock all tables, then issue a SLEEP statement on them until you complete database backup operations. Once done, copy the .frm, .MYD, and .MYI files somewhere else and kill your SLEEP SQL queries.
The .frm file, as previously noted, stores data related to the format of the tables.
.MYD files store table data.
.MYI files store indexes.
Such files are only applicable to the MyISAM storage engine within MySQL server, which is now obsolete.
We hope that you’ve found this mysql tutorial interesting, subscribe for more database tutorials, and until next time.
Below you will also find some of the most frequent sql interview questions and answers for experienced software engineers and DBAs:
Q: I’ve heard that MyISAM performs row level locking?
A: No, that’s a common misconception.
Q: MyISAM vs InnoDB performance – which one is better and why?
A: InnoDB – all of the features that were available in MyISAM are now available in InnoDB.
Q: What is the most frequently used type of SQL index?
A: A B-tree SQL index is used the most frequently in mysql server as well as other RDBMS systems.
Q: What types of SQL indexes exist?
A: There are B-Tree indexes, R-Tree indexes, hash indexes, covering indexes, clustered indexes, multicolumn or composite SQL indexes, and prefix indexes.
Q: How to protect against SQL injection?
A: Don’t pass user input into a SQL query. You really don’t need to pass a mysql advanced tutorial to know this – it’s that simple!
Q: What is the file to adjust mysql server configuration?
A: The mysql my.cnf file on Linux or the mysql my.ini file on Windows is what you should look into. If needed, we’ll make a separate mysql tutorial for beginners on how it works. A mysql tutorial covering which storage engine – myisam vs innodb – to choose is available here: https://youtube.com/shorts/MpVWcYsJqn4
Q: Is there a sql full course or any mysql tutorial that would cover mysql storage engines in more detail in all complex aspects?
A: Not that we know of, but if needed, we’ll make a sql course talking about everything: sql basics, sql queries, sql vs nosql, sql for data analysis, etc.
Q: Is there a big data tutorial for those looking to work with big data on mysql server?
A: Yes, it’s available here: https://youtube.com/shorts/GurccdgcjL0
If you’ve enjoyed this mysql course, explore some of our mysql tutorial videos below:
1) mysql vs postgresql – which database to choose? https://youtube.com/shorts/ohyqV7KgqYk
2) database slowness reasons – why are sql queries slow? https://youtube.com/shorts/beLkSf0_Ms4
3) one of the most frequent sql interview questions and answers – sql subqueries: https://youtube.com/shorts/wUQmZJxMZRI
4) sql indexes explained – this sql tutorial video will go through sql indexes having a prefix type: https://youtube.com/shorts/SS-r1H7-i-s
5) big data tutorial – how to run big data on a mysql server (mysql, mariadb or percona server?) https://youtube.com/shorts/GurccdgcjL0
Enjoy the sql tutorial!
Music:
“Alex Productions – Startup” is under a Creative Commons (CC BY 3.0) license: https://creativecommons.org/licenses/by/3.0/
Artist: https://www.youtube.com/@alexproductionsnocopyright
Music powered by BreakingCopyright: https://www.youtube.com/watch?v=JpuhuTkOQgw
#database #mysql #sql #web #webdevelopment #developer #webdeveloper