Backup, Restore and Transactions

Logical Backups With mysqldump

mysqldump -u root -p shop > /backups/shop.sql

mysqldump produces a text file of SQL statements that recreate the schema and data.

Restoring a Backup

mysql -u root -p shop < /backups/shop.sql

Backing Up All Databases and Only Structure

mysqldump -u root -p --all-databases > /backups/all.sql
mysqldump -u root -p --no-data shop > /backups/shop-schema.sql

Transactions: Atomicity in Action

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;      -- both changes saved permanently

If anything goes wrong before COMMIT, ROLLBACK undoes every change made inside the transaction. InnoDB gives you ACID guarantees: Atomicity, Consistency, Isolation, Durability.

Isolation Levels

• READ COMMITTED: only committed changes are visible.
• REPEATABLE READ (default): the same row looks identical within a transaction.
• SERIALIZABLE: strongest isolation, lowest concurrency.

Key Points

• mysqldump backs up schema and data to a SQL file.
• Restore by piping the file back into mysql.
• Transactions group changes into an all-or-nothing unit.
• COMMIT saves; ROLLBACK undoes; InnoDB is the transactional engine.