MySQL Tutorial
MySQL Installation and Configuration
MySQL Database Operations
Database Design
MySQL Data Types
MySQL Storage Engines
MySQL Basic Operations of Tables
MySQL Constraints
MySQL Operators
MySQL Function
MySQL Manipulate Table Data
MySQL View
MySQL Indexes
MySQL Stored Procedure
MySQL Trigger
MySQL Transactions
MySQL Character Set
MySQL User Management
MySQL Database Backup and Recovery
MySQL Log
MySQL Performance Optimization
A cold backup, also known as an offline backup, is a backup of a MySQL database while the database is not running. This tutorial will walk you through the process of performing a cold backup and recovery for a MySQL database.
Cold Backup
Step 1: Stop the MySQL server Before performing a cold backup, ensure that the MySQL server is stopped to avoid inconsistencies.
On Linux, run the following command in the terminal:
sudo systemctl stop mysqld
On Windows, open the Services app, locate the MySQL service, right-click on it, and choose "Stop".
Step 2: Locate the data directory Find the location of the MySQL data directory, which contains the files for databases and tables. The default location is:
You can also find it in the my.cnf (Linux) or my.ini (Windows) configuration file, with the "datadir" variable.
Step 3: Perform the backup
Create a backup of the entire data directory. Use the cp command on Linux or the xcopy command on Windows.
On Linux:
sudo cp -R /var/lib/mysql /backup/location/
On Windows (run this in the Command Prompt as administrator):
xcopy /E /I "C:\ProgramData\MySQL\MySQL Server X.Y\data" "C:\backup\location"
Replace the paths with the correct data directory and backup location paths.
Cold Recovery
Step 1: Stop the MySQL server If the MySQL server is running, stop it using the steps mentioned earlier in this tutorial.
Step 2: Remove the existing data directory Before recovering from the cold backup, you need to remove the existing data directory. This ensures that the recovery process starts from a clean state.
On Linux:
sudo rm -rf /var/lib/mysql
On Windows (run this in the Command Prompt as administrator):
rmdir /S /Q "C:\ProgramData\MySQL\MySQL Server X.Y\data"
Replace the paths with the correct data directory paths.
Step 3: Restore the backup Copy the backed-up data directory to the original location.
On Linux:
sudo cp -R /backup/location/mysql /var/lib/
On Windows (run this in the Command Prompt as administrator):
xcopy /E /I "C:\backup\location" "C:\ProgramData\MySQL\MySQL Server X.Y\data"
Replace the paths with the correct backup location and data directory paths.
Step 4: Restart the MySQL server After restoring the backup, start the MySQL server.
On Linux:
sudo systemctl start mysqld
On Windows, open the Services app, locate the MySQL service, right-click on it, and choose "Start".
Your MySQL database is now restored from the cold backup, and the server should be running with the recovered data.
Remember that this tutorial assumes you have full access to the server and are using the default settings. Adjust the commands and paths according to your specific MySQL configuration and permissions if needed.
How to perform cold backup in MySQL:
# Stop MySQL server sudo service mysql stop # Copy data directory sudo cp -r /var/lib/mysql /backup/location # Start MySQL server sudo service mysql start
Cold backup script for MySQL:
#!/bin/bash service mysql stop cp -r /var/lib/mysql /backup/location service mysql start
MySQL backup compression for cold backups:
tar -czvf backup.tar.gz /backup/location