MySQL Backups

You can physically or logically back up (MySQL) databases. You can physically(?) copy the db files and paste them elsewhere. To do this, I copied ?:/ProgramData/MySQL/MySQL Server 8.0 into My Documents. This is fast but hardware-dependent(?). You can logically back up with dumps (mysldump). A dump (lol) is a set of SQL commands and data to bring a database back to what it looked like at the time of the backup. I show how I did this below. This is slower but robust to setup differences.

I protected my in-use databases by practicing MySQL backups using mysqldump in a Debian 10 Docker container with MySQL*.

Container

# Filename: Dockerfile 
FROM mysql
ENV MYSQL_ROOT_PASSWORD=example
ENV MYSQL_DATABASE=boop
WORKDIR /usr/src/app

Steps taken

1.  mysql -u root -p 
2.  <Entered the password>
3.  USE boop;
4.  CREATE TABLE lol (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255));
5.  INSERT INTO lol (name) VALUES ('haha'), ('hoho'), ('heehee');
6.  SELECT * FROM lol; // To make sure there were three files
7.  EXIT;
8.  mysqldump -u root -p --all-databases > mysqlbackup.sql 
9.  <Entered the password>
10. mysql -u root -p
11. USE boop;
12. DELETE FROM boop WHERE id = 2; // Delete 'hoho'
13. SELECT * FROM boop // Verified that Only id's 1 and 3, 'haha' and 'heehee' remained
14. EXIT;
15. mysql < mysqlbackup.sql
16. mysql -u root -p
17. <Entered the password>
18. USE boop;
19. SELECT * FROM lol; // Id 2, 'heehee', showed

This logically backed up my MySQL databases in the container. Feeling secure, I backed up my actual dbs. Bash for Windows froze with mysqldump -u root -p --all-databases > mysqlbackup.sql and only worked when typing the password at p -p<password>. cmd and Powershell allowed secure password entry.

*Docker lets me creates “imaginary, Linux-based* computers” called containers within my computer.