How To Backup and Restore MySQL Database Using Command Line

In this tutorial, we will show you how to backup and restore the MySQL database using Command Line. Database backups are an essential part of any system administrator’s routine maintenance tasks. For MySQL databases, which power countless websites and applications worldwide, having reliable backup and restoration procedures can mean the difference between minor inconvenience and catastrophic data loss. While graphical tools offer user-friendly interfaces, command-line methods provide superior flexibility, performance, and automation capabilities that serious database administrators require.

This comprehensive guide will walk you through the entire process of backing up and restoring MySQL databases using command-line tools, focusing primarily on the powerful mysqldump utility. Whether you’re managing a small personal database or enterprise-level systems, these techniques will help you implement robust backup strategies to protect your valuable data.

Understanding MySQL Backup Fundamentals

Before diving into specific commands, it’s crucial to understand the fundamentals of MySQL backups and the tools involved. This knowledge forms the foundation for implementing effective backup strategies.

What is mysqldump?

The mysqldump utility is a client program that creates logical backups of your MySQL databases. Unlike physical backups that directly copy database files, logical backups consist of SQL statements that can recreate your database structure and data when executed. This approach offers several advantages:

• Cross-version compatibility for database migration
• Selective backups at the database, table, or row level
• Human-readable backup files that can be modified if needed
• Storage efficiency through compression options
• Platform independence for maximum flexibility

The mysqldump utility generates a sequence of SQL statements that can recreate the original database objects and populate them with data. This includes CREATE TABLE statements for the structure and INSERT statements for the data.

Why Command Line Backups are Essential

While graphical tools might seem more approachable, command-line backups offer significant advantages:

• Server accessibility without GUI requirements, perfect for headless servers
• Seamless automation through scripts, cron jobs, and schedulers
• Direct server execution without network overhead
• Greater control through numerous command parameters
• Better performance, particularly for large databases
• Integration with other command-line tools for processing
• Easier troubleshooting through standardized error messages

Command-line interface (CLI) tools are the preferred method for database administrators who need reliability, efficiency, and automation capabilities. Learning these methods provides transferable skills across different environments and use cases.

Prerequisites for MySQL Backup and Restore

Before executing any backup or restore commands, ensure you have the necessary access, information, and system resources available.

Required Access and Permissions

To successfully backup and restore MySQL databases, you’ll need:

• A MySQL user account with sufficient privileges (BACKUP_ADMIN, SELECT, and other privileges depending on operations)
• Command-line/SSH access to the server where MySQL is running
• Read/write permissions for the directories where backups will be stored
• For remote operations, appropriate network access through firewalls

You can verify your MySQL user privileges with the following command after logging in:

SHOW GRANTS FOR CURRENT_USER;

For backup operations, at minimum, your user needs SELECT privileges on the databases you’re backing up. For restoration, you’ll need more extensive privileges including CREATE, DROP, INSERT, and others depending on the operations.

Necessary Information

Gather these details before proceeding:

• Database username and password
• Database name(s) to backup or restore
• Server hostname (if connecting remotely)
• Target location for backup storage with sufficient space
• Database character set and collation (for proper restoration)

Keep this information secure, particularly authentication credentials. Consider using a password manager or secure configuration file rather than plaintext storage.

System Requirements

Ensure your system meets these requirements:

• MySQL client tools installed (particularly mysql and mysqldump utilities)
• Sufficient storage space for uncompressed backups (which can be several times larger than the actual database size)
• Adequate system resources (CPU, memory) for large database operations
• Compression utilities if planning to compress backups (gzip, xz, etc.)
• Basic understanding of MySQL command syntax and database concepts

You can verify the availability of required tools by checking their versions:

mysqldump --version
mysql --version

Basic MySQL Backup Using mysqldump

Let’s start with fundamental backup operations using mysqldump, covering different scopes from single tables to entire server backups.

Single Table Backup Syntax

When you need to backup just one table from a database, the syntax is straightforward:

mysqldump -u username -p database_name table_name > backup_file.sql

For example, to backup just the ‘users’ table from a database called ‘webapp‘:

mysqldump -u dbadmin -p webapp users > users_backup.sql

After executing this command, you’ll be prompted for the password. The output file (users_backup.sql) will contain all the SQL statements needed to recreate the table structure and insert the data.

To verify the backup was successful, you can examine the file:

head -n 20 users_backup.sql    # View the beginning of the file
grep "INSERT INTO" users_backup.sql | wc -l    # Count the number of insert statements

Single Database Backup

For backing up an entire database with all its tables:

mysqldump -u username -p database_name > database_backup.sql

For example:

mysqldump -u dbadmin -p wordpress > wordpress_backup.sql

This command creates a comprehensive backup file containing all tables, views, stored procedures, triggers, and other database objects. The resulting file will include:

• Comments indicating version and host information
• DROP TABLE statements (if not disabled) to ensure clean recreation
• CREATE TABLE statements with complete structure definitions
• INSERT statements with all table data
• Definitions for views, stored procedures, and other database objects

Multiple Database Backup

To backup several databases at once:

mysqldump -u username -p --databases db1 db2 db3 > multiple_dbs.sql

For example:

mysqldump -u dbadmin -p --databases wordpress joomla drupal > cms_backups.sql

The --databases flag is crucial here as it includes the necessary USE statements in the output file to switch context between databases during restoration.

Complete MySQL Server Backup

To backup all databases on a MySQL server:

mysqldump -u username -p --all-databases > full_server_backup.sql

This comprehensive approach backs up everything in one file, including system databases like mysql, information_schema, and performance_schema. The resulting file will be large but provides a complete snapshot of the entire MySQL server.

For very large installations, consider whether a full backup is practical, as it may consume excessive storage and take considerable time to complete. In such cases, selective backups or incremental approaches might be more appropriate.

Advanced Backup Options

Once you’re comfortable with basic backups, you can enhance your backup strategy with advanced mysqldump parameters and techniques.

Essential mysqldump Parameters

These parameters give you greater control over your backups:

• --no-data: Backup only the table structures without any data

  mysqldump -u username -p --no-data database_name > structure_only.sql

• --no-create-info: Backup only data without table structures

  mysqldump -u username -p --no-create-info database_name > data_only.sql

• --add-drop-table: Include DROP TABLE statements before CREATE TABLE (enabled by default)
• --single-transaction: Create a consistent snapshot for InnoDB tables without locking tables

  mysqldump -u username -p --single-transaction database_name > consistent_backup.sql

• --routines: Include stored procedures and functions in the backup

  mysqldump -u username -p --routines database_name > with_procedures.sql

• --triggers: Include triggers in the backup (enabled by default)
• --where="condition": Backup only rows that match a specific condition

  mysqldump -u username -p database_name table_name --where="date_column > '2023-01-01'" > filtered_backup.sql

Backup Compression Techniques

Compressing backups saves storage space and reduces network transfer time:

Using gzip compression:

mysqldump -u username -p database_name | gzip > database_backup.sql.gz

Using xz for higher compression (slower but smaller files):

mysqldump -u username -p database_name | xz -z > database_backup.sql.xz

Using bzip2 for a balance of speed and compression ratio:

mysqldump -u username -p database_name | bzip2 > database_backup.sql.bz2

Compression ratios vary depending on your data, but typically you can expect 70-90% reduction in file size, which is significant for large databases.

Remote Database Backups

To backup a database on a remote server:

mysqldump -h hostname -u username -p database_name > remote_backup.sql

For example:

mysqldump -h db.example.com -u dbadmin -p wordpress > remote_wordpress_backup.sql

For secure remote backups, consider using SSH tunneling:

ssh user@remote_server "mysqldump -u username -p database_name" > local_backup.sql

This approach tunnels the MySQL traffic through SSH, providing encryption and authentication benefits.

Database Restoration Fundamentals

Backing up databases is only half the equation; knowing how to properly restore them is equally important.

Preparing for Database Restore

Before restoring a database, perform these preparatory steps:

1. Create an empty database if restoring to a new database:

   CREATE DATABASE new_database;

2. Ensure the MySQL user has sufficient privileges:

   GRANT ALL PRIVILEGES ON new_database.* TO 'username'@'localhost';
   FLUSH PRIVILEGES;

3. Verify the backup file’s integrity, especially if it’s been compressed:

   # For gzipped files
   gzip -t backup_file.sql.gz
   
   # Basic check for uncompressed files
   head -n 20 backup_file.sql
   tail -n 20 backup_file.sql

4. Consider creating a test restoration in a separate database first before overwriting production data.

Basic Restore Syntax

For uncompressed backup files, the basic restore command is:

mysql -u username -p database_name < backup_file.sql

For example:

mysql -u dbadmin -p wordpress < wordpress_backup.sql

When prompted, enter your password. The command will execute all SQL statements in the backup file to recreate the database structure and data.

Unlike the backup process, restoration doesn’t show progress indicators. For large databases, you might need to monitor system resource usage to confirm that the restoration is progressing.

Common Restoration Scenarios

Restoring to a different database name:

mysql -u username -p new_database_name < backup_file.sql

Restoring from a backup with multiple databases:

mysql -u username -p < multiple_dbs_backup.sql

Selective table restoration requires some preparation:

1. Extract the desired table definition and data from the backup file:

   sed -n '/CREATE TABLE.*specific_table/,/UNLOCK TABLES/p' database_backup.sql > table_restore.sql

2. Restore just that table:

   mysql -u username -p database_name < table_restore.sql

Advanced Restoration Techniques

For more complex scenarios, advanced restoration techniques provide greater flexibility and control.

Restoring from Compressed Backups

For gzip-compressed backups:

gunzip < backup_file.sql.gz | mysql -u username -p database_name

Alternatively:

zcat backup_file.sql.gz | mysql -u username -p database_name

For bzip2-compressed backups:

bzcat backup_file.sql.bz2 | mysql -u username -p database_name

For xz-compressed backups:

xzcat backup_file.sql.xz | mysql -u username -p database_name

Restoring Single Database from Full Backup

When you have a backup containing multiple databases but need to restore just one:

mysql -u username -p --one-database database_name < full_backup.sql

The --one-database parameter filters the SQL statements to affect only the specified database, ignoring statements for other databases in the backup file.

Handling Errors During Restoration

Common errors during restoration include:

1. Insufficient privileges:

   ERROR 1044 (42000): Access denied for user 'username'@'localhost' to database 'database_name'

   Solution: Grant appropriate privileges to the user.

2. Database already exists:

   ERROR 1007 (HY000): Can't create database 'database_name'; database exists

   Solution: Drop the existing database or restore to a different name.

3. Table already exists:

   ERROR 1050 (42S01): Table 'table_name' already exists

   Solution: Ensure the backup file includes DROP TABLE statements (use --add-drop-table during backup).

4. Storage engine issues:

   ERROR 1286 (42000): Unknown storage engine 'engine_name'

   Solution: Ensure the required storage engines are available on the target server.

To continue restoration despite errors, you can use the --force option:

mysql -u username -p --force database_name < backup_file.sql

However, use this with caution as it can lead to incomplete or inconsistent data.

Export and Import in Single Command

To transfer a database directly from one server to another:

mysqldump -h source_host -u source_user -p source_database | mysql -h target_host -u target_user -p target_database

This technique eliminates the need for intermediate storage of the backup file, making it efficient for migrations and clones. For large databases, consider adding compression to the pipeline:

mysqldump -h source_host -u source_user -p source_database | gzip -c | ssh user@target_server "gunzip -c | mysql -u target_user -p target_database"

Automation and Scheduling

Regular, automated backups are the cornerstone of a solid data protection strategy.

Creating Backup Scripts

Here’s a basic shell script for automated MySQL backups:

#!/bin/bash

# Configuration
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"
BACKUP_DIR="/path/to/backup/directory"

# Create date-stamped filename
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql.gz"

# Create backup
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_FILE

# Check if backup was successful
if [ $? -eq 0 ]; then
    echo "Backup created successfully: $BACKUP_FILE"
else
    echo "Error creating backup" >&2
    exit 1
fi

# Optional: keep only the last 7 backups
find $BACKUP_DIR -name "$DB_NAME-*.sql.gz" -mtime +7 -delete

Save this as mysql_backup.sh, make it executable with chmod +x mysql_backup.sh, and test it before scheduling.

For better security, store credentials in a MySQL configuration file:

[client]
user=username
password=password

Save this as ~/.my.cnf with permissions chmod 600 ~/.my.cnf, then modify your script to use:

mysqldump --defaults-file=~/.my.cnf $DB_NAME | gzip > $BACKUP_FILE

Scheduling with Cron

To schedule regular backups using cron:

1. Open your crontab: crontab -e
2. Add a schedule for your backup script:

# Daily backup at 3:00 AM
0 3 * * * /path/to/mysql_backup.sh

# Weekly backup on Sunday at 2:00 AM
0 2 * * 0 /path/to/weekly_backup.sh

# Monthly backup on the 1st at 1:00 AM
0 1 1 * * /path/to/monthly_backup.sh

After saving your crontab, verify that it’s properly installed:

crontab -l

To receive notifications about backup status, modify your script to send emails:

# Add to your script
if [ $? -eq 0 ]; then
    echo "Backup created successfully: $BACKUP_FILE" | mail -s "MySQL Backup Success" your@email.com
else
    echo "Error creating backup" | mail -s "MySQL Backup FAILED" your@email.com
    exit 1
fi

Backup Rotation Strategies

Implement retention policies to manage backup storage efficiently:

• Grandfather-Father-Son (GFS): Keep daily backups for a week, weekly backups for a month, and monthly backups for a year.
• Tower of Hanoi: Complex rotation scheme that balances backup frequency with storage efficiency.
• Simple N-day retention: Keep the last N daily backups, deleting older ones.

Example script for implementing a GFS strategy:

#!/bin/bash

# Configuration
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"
BACKUP_DIR="/path/to/backup/directory"

# Determine backup type
DOW=$(date +%u)  # Day of week (1-7)
DOM=$(date +%d)  # Day of month (01-31)

if [ $DOM -eq 1 ]; then
    # Monthly backup (1st of month)
    BACKUP_TYPE="monthly"
elif [ $DOW -eq 7 ]; then
    # Weekly backup (Sunday)
    BACKUP_TYPE="weekly"
else
    # Daily backup
    BACKUP_TYPE="daily"
fi

# Create backup filename
DATE=$(date +%Y-%m-%d)
BACKUP_FILE="$BACKUP_DIR/$BACKUP_TYPE/$DB_NAME-$DATE.sql.gz"

# Create backup
mkdir -p "$BACKUP_DIR/$BACKUP_TYPE"
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_FILE

# Implement retention policies
# Keep 7 daily backups
find $BACKUP_DIR/daily -name "*.sql.gz" -mtime +7 -delete
# Keep 4 weekly backups
find $BACKUP_DIR/weekly -name "*.sql.gz" -mtime +28 -delete
# Keep 12 monthly backups
find $BACKUP_DIR/monthly -name "*.sql.gz" -mtime +365 -delete

Best Practices and Security Considerations

Implementing these best practices ensures your backups are secure, reliable, and available when needed.

Secure Password Handling

Never include passwords directly in command lines or scripts, as they appear in process listings and logs. Instead:

• Use MySQL option files (like ~/.my.cnf) with restricted permissions (chmod 600)
• Utilize environment variables for scripts
• Consider MySQL’s --login-path feature:

  mysql_config_editor set --login-path=backup --host=localhost --user=backup_user --password
  mysqldump --login-path=backup database_name > backup.sql

For backup files, restrict permissions:

chmod 600 backup_file.sql

For highly sensitive data, consider encrypting backups:

mysqldump -u username -p database_name | gpg -e -r "Your Name" > database_backup.sql.gpg

Backup Verification

Regularly verify your backups to ensure they are usable:

1. Perform test restores to a separate test environment
2. Check backup file integrity after creation:

   mysqlcheck -u username -p database_name

3. Implement automated verification in your backup scripts:

   # After creating backup
   mysql -u username -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='database_name'" | grep -v "COUNT" > original_count.txt
   
   # Create test database
   mysql -u username -p -e "CREATE DATABASE test_restore"
   
   # Restore to test database
   zcat backup_file.sql.gz | mysql -u username -p test_restore
   
   # Compare table counts
   mysql -u username -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='test_restore'" | grep -v "COUNT" > restored_count.txt
   
   if diff original_count.txt restored_count.txt > /dev/null; then
       echo "Backup verification successful"
   else
       echo "Backup verification FAILED" >&2
   fi
   
   # Cleanup
   mysql -u username -p -e "DROP DATABASE test_restore"

Performance Optimization

For large databases, optimize your backup process:

• Schedule backups during low-traffic periods
• Use --single-transaction for InnoDB tables to avoid locking
• Consider using --quick option to process rows one at a time:

  mysqldump -u username -p --single-transaction --quick database_name > backup.sql

• For very large tables, explore alternatives like Percona XtraBackup or MySQL Enterprise Backup
• Use compression with multi-core tools for efficiency:

  mysqldump -u username -p database_name | pigz -9 > backup.sql.gz

• If possible, run backups on a replica server to reduce load on production

Thanks for using this tutorial to back up and restore MySQL/MariaDB database using command line. For additional help or useful information, we recommend you check the official MySQL website.

Save