My Web Training

Backup and Restore a Database

Your backup strategy should include full backups made on a regular schedule using mysqldump, as well as enabling the binary log for incremental backups.

• Restoring a backup involves reversing the process: recreate the database from the full backup, then apply incremental changes from the log files.
• Always store backup files on a different device or location than your MySQL installation to minimize risk of data loss.

Creating a Backup Using mysqldump

mysqldump is a command-line utility provided with MySQL that creates text-based backups of one or more databases. This must be run outside of MySQL Workbench.

Start the MySQL Server

Use MySQL Workbench or start the mysql80 service manually.

Open Windows Terminal in Administrator Mode

Right-click Terminal and select Run as Administrator.

Navigate to the MySQL Bin Directory

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

Run mysqldump Command

.\mysqldump --user=<username> --password --result-file=<filename.sql> --databases <database_name>

You will be prompted for the password after running the command. Do not include it inline.

Verify Backup File

Locate the newly created .sql file in the current directory.

Restoring a Backup from a mysqldump Script

Start the MySQL Server

Use MySQL Workbench or start the mysql80 service manually.

Open Windows Terminal in Administrator Mode

Right-click Terminal and select Run as Administrator.

Navigate to the MySQL Bin Directory

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

Log In to MySQL

.\mysql -u <username> -p

You'll be prompted for your password.

Execute the Backup Script

mysql> source filename.sql;

After completion, run SHOW DATABASES; to confirm restoration.

Importing and Exporting Data Only

MySQL supports lightweight data transfer operations when schema changes aren't required. This is useful for sharing table data with other tools or systems.

Importing Data into a Table

To import a tab-delimited text file:

LOAD DATA INFILE 'filename.txt'

Specifies the file to import.

INTO TABLE tableName

Target table for the imported data.

FIELDS TERMINATED BY '\t'

Fields are separated by tabs.

LINES TERMINATED BY '\n'

Each row ends with a newline character.

Use SHOW VARIABLES LIKE "secure_file_priv"; to find the permitted import/export directory on your system.

Exporting Data from a Table

To export data from a MySQL table to a text file:

SELECT col1, col2 INTO OUTFILE 'filename.txt'

Exports selected columns to the file. You may use * to export all columns.

FROM tablename

Specifies the source table.

Backup and Restore with MySQL Workbench

If you're working in MySQL Workbench, you can back up and restore databases without using the command line. These tools provide visual workflows for exporting schema and data, and for running scripts to recreate database objects.

📄 Save Script: Lightweight Version Control (Not a Full Backup)

When you write SQL statements in Workbench — such as CREATE TABLE or INSERT commands — you can save them as script files. These files are like blueprints of your changes — they describe what you want the database to become, not what it currently is.

• Use File → Save Script As… to create a .sql file
• Save different versions as your work evolves (e.g., products-v1.sql, products-v2.sql)
• Use File → Open Script or Run SQL Script… to apply them later

🛠️ Running a Saved Script

If you've saved your SQL work as a script file, you can re-run it using:

• File → Run SQL Script…
• Or open the file in a new SQL tab and click Execute

This is ideal for restoring a structure-only script, applying insert statements, or re-creating a specific object like a table or view.

🧠 Best Practice: Save major schema and data changes as scripts, but also use the Export and Import tools when you need a true backup of the database's current state.

📤 Exporting a Database with Workbench

MySQL Workbench provides two export methods for backing up your database. Both options generate .sql files that can later be restored using the Data Import tool or by running the script manually.

To access the export options:

• Go to Server → Data Export
• Select the schema (database) and tables you want to export
• Choose one of the export methods below

🗃️ Option 1: Export to Self-Contained File

This creates a single .sql file that contains both the structure and data of the selected database or tables. It's a snapshot of the entire schema in one file.

Best for:

• Complete backups of a schema at a point in time
• Sharing a portable version of a full database
• Simple restore workflows using Run SQL Script or Data Import

Limitations:

• Harder to restore only one table — requires editing the script
• No option to skip structure or data per table

💡 Tip: Use the Self-Contained File option for fast full-schema backups. Use the Dump Folder method when you want control over what gets backed up or restored — especially if you're updating only one part of the database.

🗂️ Option 2: Export to Dump Folder

This creates a folder with one SQL file per table. For each table, you can choose to export:

• Structure only — just the CREATE TABLE statements
• Data only — just the INSERT INTO statements
• Structure and Data — both definitions and content

Best for:

• Selective backups (e.g., only some tables)
• Partial restores — restoring one table without affecting others
• Version control: keeping structure and data scripts separate

Limitations:

• More complex to manage (many files)
• Requires choosing the right options during import

⚠️ Warning: If you're restoring individual tables from separate SQL files, be careful about foreign key constraints. Dependent tables must be restored in the correct order. You can also temporarily disable foreign key checks during import, but be sure your data remains consistent.

📥 Importing a Database with Workbench

You can restore a database using:

• Run SQL Script — best for manual scripts, like table creation or inserts
• Data Import — best for full or partial restores from Workbench Export or mysqldump

While both options can process .sql files, the Data Import tool offers more automation and flexibility for larger backups. It can automatically create schemas, import multiple tables, and provide logs for the process — making it the better choice when restoring a full database from a backup file.

💡 Tip: Use Run SQL Script for code you've written yourself. Use Data Import when working with system-generated backups or when recreating a full database schema and its data.

To restore a database using the exported file:

• Go to Server → Data Import
• Select the Self-Contained File you previously exported
• Choose to create a new schema or overwrite an existing one
• Click Start Import to restore the database

🧩 Selective Table Restore from a Dump Folder

If your backup was created using the Dump Folder (or Dump Project Folder) option, you can restore specific tables individually. This is useful for partial recoveries or targeted testing.

• Select Import from Dump Project Folder
• Choose the folder containing your exported SQL files
• Use the checkboxes to select specific tables to import
• Choose whether to import structure, data, or both for each table

You can also skip unrelated tables entirely — useful if you're only restoring a damaged or missing object.

🧠 Reminder: Be mindful of foreign key relationships. Restore parent tables (like orders) before child tables (like order_items) to avoid constraint errors.

Whether you're working with a full export or a simple script, MySQL Workbench gives you the flexibility to back up and restore exactly what you need — without leaving the GUI.