Backups with mysqldump and restoring
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A backup is a copy of your database kept somewhere else, so that after a wrong DELETE, a dropped table or a dead disk you can bring the data back. A backup only counts once you have restored it at least once and checked the result.
Why backups matter
A backup is a saved copy of a database's tables and rows that you can load again later. After COMMIT there is no undo button, so the copy is the only way back.
An UPDATE without WHERE changes every row. A copy made a minute earlier brings the old values back.
Example 1 · A safety copy before a risky UPDATE
-- Before a risky change: keep a copy of the tableCREATE TABLE products_backup AS SELECT * FROM products;
-- The mistake: the WHERE clause was forgottenUPDATE products SET price = 99.00;SELECT product_id, name, price FROM products WHERE product_id <= 4 ORDER BY product_id;
-- The fix: copy the old prices back from the backupUPDATE products pJOIN products_backup b ON b.product_id = p.product_idSET p.price = b.price;SELECT product_id, name, price FROM products WHERE product_id <= 4 ORDER BY product_id;+------------+-----------------------+-------+ | product_id | name | price | +------------+-----------------------+-------+ | 1 | Redmi Note 13 | 99.00 | | 2 | Samsung Galaxy M35 | 99.00 | | 3 | iPhone 15 | 99.00 | | 4 | Lenovo IdeaPad Slim 3 | 99.00 | +------------+-----------------------+-------+ +------------+-----------------------+----------+ | product_id | name | price | +------------+-----------------------+----------+ | 1 | Redmi Note 13 | 17999.00 | | 2 | Samsung Galaxy M35 | 19999.00 | | 3 | iPhone 15 | 69900.00 | | 4 | Lenovo IdeaPad Slim 3 | 42990.00 | +------------+-----------------------+----------+
What this code does
- The first SELECT shows the damage: every product now costs 99.00, not only one.
- The UPDATE with a JOIN matches each product to its own row in the copy by
product_idand puts the old price back. - This only works because the copy was made first. Make it a habit before any UPDATE or DELETE that touches many rows.
The quickest safety net is a copy of one table inside the database, made just before a risky change. Example 1 copies products, makes the classic mistake (an UPDATE without WHERE) and repairs it from the copy with an UPDATE that joins the two tables.
A copy in the same database does not survive a dropped database or a broken disk, so real backups are files kept elsewhere. A common rule is 3-2-1: three copies of the data, on two different kinds of storage, one of them away from the computer (another disk or cloud storage).
Predict the output
Predict the damage
The WHERE clause was forgotten. How many products does the SELECT count?
mysqldump: back up a database or one table
mysqldump is a command-line tool that comes with MySQL and writes a database out as a text file of SQL: a CREATE TABLE for each table and INSERT statements for its rows.
mysqldump runs in a terminal, not at the mysql> prompt. Three common forms are below: the whole database, one table, and the schema only.
# the whole shoplite databasemysqldump -u root -p --single-transaction --routines shoplite > shoplite_backup.sql# one tablemysqldump -u root -p shoplite products > products_backup.sql# schema only, no rowsmysqldump -u root -p --no-data shoplite > shoplite_schema.sqlThe pattern is mysqldump -u USER -p DATABASE [TABLE ...] > FILE. With no table names you get every table and view of the database; with names you get only those tables. -p asks for the password, so it never appears on screen or in your command history.
Useful options:
--single-transactiontakes a consistent snapshot of InnoDB tables without locking them, so the shop keeps selling while the backup runs.--no-datawrites only the CREATE statements (the schema), no rows.--routinesadds stored procedures and functions; triggers and views are included by default.--databases shoplitealso writesCREATE DATABASEandUSE, so the file restores into a database of the same name.
On Windows, run these commands in Command Prompt; the tools are in C:\Program Files\MySQL\MySQL Server 8.4\bin, so add that folder to PATH if mysqldump is "not recognized". On macOS, use Terminal; the tools are in /usr/local/mysql/bin. In Windows PowerShell, > saves the file as UTF-16 text that the mysql client cannot read back, so use --result-file=shoplite_backup.sql there instead.
Example 2 · What a dump file contains
mysqldump -u root -p shoplite categoriesEnter password: **** -- MySQL dump 10.13 Distrib 9.4.0, for Win64 (x86_64) -- -- Host: 127.0.0.1 Database: shoplite -- ------------------------------------------------------ -- Server version 9.4.0 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `categories` -- DROP TABLE IF EXISTS `categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `categories` ( `category_id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `parent_id` int DEFAULT NULL, PRIMARY KEY (`category_id`), UNIQUE KEY `uq_categories_name` (`name`), KEY `fk_categories_parent` (`parent_id`), CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`category_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `categories` -- LOCK TABLES `categories` WRITE; /*!40000 ALTER TABLE `categories` DISABLE KEYS */; INSERT INTO `categories` VALUES (1,'Electronics',NULL),(2,'Mobiles',1),(3,'Laptops',1),(4,'Accessories',1),(5,'Books',NULL),(6,'Fashion',NULL),(7,'Home & Kitchen',NULL); /*!40000 ALTER TABLE `categories` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2026-10-16 7:25:43
What this code does
- Without
> filethe dump goes to the screen, which is a quick way to look inside one. The version, host and date lines show your own values. - For each table:
DROP TABLE IF EXISTS, the fullCREATE TABLEwith keys and constraints, then one longINSERTwith every row. - The
/*!40014 ... FOREIGN_KEY_CHECKS=0 */line near the top switches foreign key checks off while loading, and the last lines switch them back on.
Restoring with the mysql client and SOURCE
Restoring means running the SQL in a dump file so the tables and rows come back. The mysql client does it from the terminal with mysql -u root -p DATABASE < FILE, or at the mysql> prompt with SOURCE FILE.
Test a backup by restoring it into a new database, shoplite_copy, so the real data is never touched.
# in a terminal: restore into a new databasemysql -u root -p -e "CREATE DATABASE shoplite_copy"mysql -u root -p shoplite_copy < shoplite_backup.sql# or at the mysql> promptUSE shoplite_copy;SOURCE shoplite_backup.sqlSOURCE is a command of the mysql client itself, not SQL, so Workbench does not understand it. On Windows, PowerShell does not support <: use Command Prompt, or type SOURCE C:/backups/shoplite_backup.sql with forward slashes. On macOS, < works in Terminal; give SOURCE the full path when the file is not in the folder where you started the client.
A dump made without --databases has no USE line, so you must name the target database, or MySQL stops with ERROR 1046 (3D000): No database selected. Restoring into a new database such as shoplite_copy is the safe way to test a backup: the real database is not touched. Each table in the dump starts with DROP TABLE IF EXISTS, so a restore replaces those tables completely, and rows added after the backup are lost. The dump also switches off foreign key checks while it loads, which is why the order of the tables in the file does not matter.
Example 3 · Back up, restore into a copy and check it
mysqldump -u root -p --single-transaction --routines shoplite > shoplite_backup.sqlmysql -u root -p -e "CREATE DATABASE shoplite_copy"mysql -u root -p shoplite_copy < shoplite_backup.sqlmysql -u root -p shoplite_copy -e "SELECT COUNT(*) AS orders, MAX(ordered_at) AS last_order FROM orders"Enter password: **** Enter password: **** Enter password: **** Enter password: **** +--------+---------------------+ | orders | last_order | +--------+---------------------+ | 16 | 2026-09-29 16:30:00 | +--------+---------------------+
What this code does
- Each command asks for the password; the backup and the two restore commands print nothing when they succeed.
- The copy has the same 16 orders and the same last order time as
shoplite, so the backup really holds the data. - When the check is done,
DROP DATABASE shoplite_copy;removes the test copy.
Example 4 · Check a copy with counts and sums
CREATE TABLE order_items_copy LIKE order_items;INSERT INTO order_items_copy SELECT * FROM order_items;
SELECT 'order_items' AS source, COUNT(*) AS row_count, SUM(quantity * unit_price) AS total_valueFROM order_itemsUNION ALLSELECT 'order_items_copy', COUNT(*), SUM(quantity * unit_price)FROM order_items_copy;+------------------+-----------+-------------+ | source | row_count | total_value | +------------------+-----------+-------------+ | order_items | 25 | 309343.00 | | order_items_copy | 25 | 309343.00 | +------------------+-----------+-------------+
What this code does
- Two numbers per table: how many rows, and the total value of all items. If either differs, rows were lost or changed.
UNION ALLstacks the two one-row results so they sit side by side for comparison.- Run the same check after any restore: on the original before the backup, and on the restored copy after.
Order of execution
Back up and test the backup
Put the steps of a tested backup in the order you run them.
SOURCE at the mysql> prompt. The MySQL 9.4 client does not run client commands such as SOURCE from piped input unless it is started with --commands: it sends the line to the server, which fails with error 1064. So do not put SOURCE inside a file that you pipe in.Quick copies inside MySQL: LIKE versus AS SELECT
CREATE TABLE copy LIKE original makes an empty table with the same columns and keys. CREATE TABLE copy AS SELECT ... copies columns and rows in one step, but no keys or constraints.
A quick copy inside the database is the safety net before a risky change. The two ways keep different things.
Example 5 · LIKE keeps the keys, AS SELECT does not
CREATE TABLE products_as AS SELECT * FROM products;CREATE TABLE products_like LIKE products;INSERT INTO products_like SELECT * FROM products;
SELECT table_name AS table_name, constraint_type AS type, COUNT(*) AS how_manyFROM information_schema.table_constraintsWHERE table_schema = DATABASE() AND table_name IN ('products', 'products_as', 'products_like')GROUP BY table_name, constraint_typeORDER BY table_name, constraint_type;+---------------+-------------+----------+ | table_name | type | how_many | +---------------+-------------+----------+ | products | CHECK | 1 | | products | FOREIGN KEY | 1 | | products | PRIMARY KEY | 1 | | products_like | CHECK | 1 | | products_like | PRIMARY KEY | 1 | +---------------+-------------+----------+
What this code does
products_asdoes not appear at all: it has no primary key and no constraints, only columns and rows.products_likehas the primary key and the CHECK on price, but not the foreign key tocategories.DATABASE()returns the name of the current database, so the query works whatever you called it.
INSERT INTO copy SELECT * FROM original fills a table made with LIKE. Neither copies foreign keys, which is what you want for a throw-away backup: the copy should not block deletes in the real tables. For a copy you will query or restore from, prefer LIKE, because the primary key keeps lookups fast and stops duplicate rows.
Check a copy, or a restored database, with numbers rather than a quick glance: the row count and a sum such as the total value must match the original.
Fix the error
Copy with the primary key
The copy must keep the primary key of products. Pick the line that does it.
Back up products before a price change.
Error
No error, but SHOW CREATE TABLE products_copy has no PRIMARY KEY: duplicate product_id rows are accepted.
Exporting a table to CSV from Workbench
A CSV file (comma-separated values) is plain text with one row per line and the columns split by commas. Excel and Google Sheets open it directly.
The sales team wants the product list in a spreadsheet. The result grid of any query can be saved as a CSV file.
- Open a query tab and run
SELECT * FROM products ORDER BY product_id;. - In the toolbar above the result grid, click the Export button ("Export recordset to an external file").
- Choose the folder, type a file name such as
products.csvand pick CSV as the format. - Click Save, then open the file in a spreadsheet: the first row holds the column names.
Example 6 · INTO OUTFILE and secure_file_priv
SELECT product_id, name, priceFROM productsORDER BY product_idINTO OUTFILE 'C:/temp/products.csv'FIELDS TERMINATED BY ',' ENCLOSED BY '"';ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
What this code does
- The server refuses to write a file outside the folder that
secure_file_privallows, which protects the server's disk. - When
secure_file_privis NULL, as on this server,INTO OUTFILEis switched off completely; Workbench's Export button works either way. - If you do get a file this way, it is on the database server's computer, which is not always your laptop.
A CSV file is the usual way to hand data to someone who does not use SQL, but it is not a backup: it has no table definition, keys or types. For a full backup, Workbench has Server → Data Export, which runs mysqldump for you, and Server → Data Import to restore the file.
SQL also has SELECT ... INTO OUTFILE, but it writes the file on the server's own computer and only into the folder named by the secure_file_priv setting. Anywhere else it fails with error 1290, as Example 6 shows; SHOW VARIABLES LIKE 'secure_file_priv'; tells you the allowed folder.
Next step
Check your understanding
11 quick questions