# Backup & Restore — MySQL Fundamentals: SQL, Joins and Transactions

Source: https://www.skillbyai.com/en/mysql/sql-backup-restore

> Back up and restore a MySQL database with mysqldump and schedule regular backups with cron.

## Backing up

`mysqldump` writes the schema and data as SQL statements to a file.

```bash
mysqldump -u root -p shop > shop_backup.sql
```

## Restoring

Feed the dump file back into `mysql` to recreate the database exactly.

```bash
mysql -u root -p shop < shop_backup.sql
```

## Automate it

Schedule `mysqldump` with `cron` for regular backups — a single lost server shouldn't mean lost data.

## Final quiz 1 of 8

Final quiz

**Quiz:** Which key uniquely identifies each row?

- [ ] Foreign key
- [ ] Index only
- [x] Primary key
- [ ] View

*Answer:* Primary key. A primary key has no duplicates and no NULLs.

## Final quiz 2 of 8

Final quiz

**Quiz:** Which clause filters groups after aggregation?

- [ ] WHERE
- [ ] LIMIT
- [ ] ORDER BY
- [x] HAVING

*Answer:* HAVING. WHERE runs before grouping; HAVING runs after.

## Final quiz 3 of 8

Final quiz

**Quiz:** Which join keeps all rows from the left table?

- [x] LEFT JOIN
- [ ] INNER JOIN
- [ ] CROSS JOIN only
- [ ] UNION

*Answer:* LEFT JOIN. Unmatched right-side columns become NULL.

## Final quiz 4 of 8

Final quiz

**Quiz:** Which data type is best for money?

- [ ] FLOAT
- [x] DECIMAL
- [ ] TEXT
- [ ] BOOLEAN

*Answer:* DECIMAL. DECIMAL stores exact values without rounding errors.

## Final quiz 5 of 8

Final quiz

**Quiz:** What does ROLLBACK do?

- [ ] Commits changes
- [ ] Creates an index
- [x] Undoes changes since the transaction started
- [ ] Deletes the table

*Answer:* Undoes changes since the transaction started. It restores the state before START TRANSACTION.

## Final quiz 6 of 8

Final quiz

**Quiz:** What does EXPLAIN type: ALL usually signal?

- [ ] A perfect index use
- [ ] An empty table
- [ ] A syntax error
- [x] A full table scan

*Answer:* A full table scan. It often means a useful index is missing.

## Final quiz 7 of 8

Final quiz

**Quiz:** Why avoid using root for an application?

- [x] It has far more privileges than needed
- [ ] It is slower
- [ ] It cannot connect remotely
- [ ] It lacks SELECT

*Answer:* It has far more privileges than needed. Least privilege limits the damage of a compromise.

## Final quiz 8 of 8

Final quiz

**Quiz:** Which tool creates a logical backup of a MySQL database?

- [ ] mysqlcheck only
- [x] mysqldump
- [ ] ls
- [ ] git

*Answer:* mysqldump. mysqldump writes schema and data as SQL.