In this sql tutorial I’ll walk you through physical backup in MySQL. The debate of physical backup vs logical backup is frequently one of the FAQs related to databases, and today we’ll explore the mystery.

MySQL offers two ways to take a physical backup of its mysql server: one can either backup InnoDB or MyISAM. Both InnoDB and MyISAM are storage engines unique to MySQL server and they are backed up differently.

To back up the InnoDB storage engine in MySQL server:

1) Perform a slow shutdown of InnoDB by setting the innodb_fast_shutdown variable to 0.
2) Back up the following files:
– ibdata1 (the filesystem tablespace containing data, indexes, and metadata related to InnoDB tables within the MySQL server)
– ib_logfile0 and ib_logfile1 (undo and redo logs)
– .ibd files storing the data within a database.
– .frm files describing the format of the tables.
– The my.cnf or the my.ini file containing all of the configuration information pertaining to your MySQL server.

3) Copy the data directory: it can be backed up by running the query SHOW GLOBAL VARIABLES LIKE ‘datadir’; to figure out its location, then copying the data.

You’ve now taken a physical backup of InnoDB.

To back up the MyISAM storage engine in your MySQL server:

1) Lock all tables within your sql database.
2) Run a SLEEP statement on your sql database for a couple of minutes (it will be terminated later on.)
3) Copy the .frm (table definition), .MYD (.MyData), and .MYI (.MYIndex) files.
4) Terminate the SLEEP statement.

You’ve now taken a physical backup of MyISAM.

Now take a look into some of the sql interview questions and answers given below:

Q: What are some frequent database backup and recovery techniques?
A: As far as a database backup in dbms is concerned, a database backup can be either logical (backing up SQL queries reconstructing data within a table) or physical (backing up files within a database instance.) Different DBMS vendors offer different tools to backup data – make sure to subscribe to our channel to find out how everything works in detail.

Q: What are sql joins?
A: sql joins are used to combine rows from a couple of tables in a database server.

Q: Is sql injection very dangerous? How to avoid it?
A: Yes, sql injection is a very dangerous security issue. To avoid sql injection, avoid passing user input straight to a database.

Q: How does a sql index improve sql query performance?
A: By using a sql index your database knows how to find your data quickly. If you’re searching for a sql indexes explained video we advise you watch one of our videos on a sql index and its performance.

Q: Explain sql in 100 seconds.
A: SQL is a programming language allowing for work with various RDBMS including mysql server, mariadb server, percona server, and others.

Q: What can I expect in my sql interview?
A: Expect questions related to database availability, security, capacity, and performance. Make sure to watch a couple of our videos to know proper answers to these sql interview questions!

Enjoy the video!

Music:
Skysurf – Pold: https://www.youtube.com/watch?v=I8N4sN2kYYg
Licensed under a Creative Commons Attribution license: https://creativecommons.org/licenses/by/3.0/
Free Download / Stream: https://bit.ly/3qiqwKX
Music promoted by Audio Library: https://www.youtube.com/watch?v=I8N4sN2kYYg

#database #mysql #web #webdevelopment #reels #shorts #programming

Please take the opportunity to connect and share this video with your friends and family if you find it useful.