Backup Types in MySQL Explained

In this mysql server guide we'll go through the various backup types in MySQL.

There are two main backup types in mysql server:

1) Physical Backup - a physical backup of mysql server backs up the physical files within the database (the sql database directories and files related to them.) Such sql backups are restored by downloading the backup file, then using a tool to back up data.
2) Logical Backup - a logical database backup is a copy of the SQL statements that can be used (run) by the database to restore data.

A logical backup in mysql server looks like this:

1) We take a logical database backup with a tool (mysqldump, XtraBackup, etc.)
2) We verify that the database backup is restoreable (restore it locally.)
3) We restore the database backup with a tool (phpMyAdmin, XtraBackup, import it into our DBMS via the CLI, etc.)
4) Our database runs the SQL statements within the database backup.
5) Our data is restored!

To take a physical data backup in mysql server and many other database management systems (mysql vs postgresql, mongodb, sql server, timescaledb, etc.), we take a physical copy of our data files and directories, then restore it using Percona XtraBackup or similar tools: this sql tutorial will tell you more about that. And boom - our data is restored!

A couple of database interview questions can also be found below:

Q: What is a SQL index?
A: A SQL index is a database modeling structure used to quickly find rows.

Q: How dangerous is sql injection?
A: SQL injection is one of the most dangerous web application security flaws of the present day. SQL injection is frequently the #1 threat according to owasp top 10 too.

Q: What are sql joins?
A: SQL joins are SQL queries that are used to combine rows from two or more tables.

Music:
Creative Commons — Attribution-NoDerivs 3.0 Unported — CC BY-ND 3.0

#database #mysql #web #webdevelopment #developer #sql #dev