SQL BACKUP DATABASE

Creating a backup of a SQL database on a Linux server involves using specific SQL commands and utilities. Below are the steps and examples to back up a database using MySQL and PostgreSQL, two popular database management systems.

MySQL

Step 1: Log into the MySQL Server

mysql -u username -p

Step 2: Use the mysqldump utility to back up the database

mysqldump -u username -p database_name > backup_file.sql

Example:

mysqldump -u root -p my_database > my_database_backup.sql

Output: This command creates a file named my_database_backup.sql containing the SQL commands needed to recreate the database.

Step 3: Verify the backup file

ls -lh my_database_backup.sql

PostgreSQL

Step 1: Log into the PostgreSQL Server

psql -U username -d database_name

Step 2: Use the pg_dump utility to back up the database

pg_dump -U username -d database_name -F c -f backup_file.dump

Example:

pg_dump -U postgres -d my_database -F c -f my_database_backup.dump

Output: This command creates a file named my_database_backup.dump containing the custom-format backup of the database.

Step 3: Verify the backup file

ls -lh my_database_backup.dump

Automating Backups with Cron Jobs

You can automate the backup process using cron jobs.

Step 1: Open the crontab editor

crontab -e

Step 2: Add a cron job for the backup

MySQL Example:

0 2 * * * /usr/bin/mysqldump -u root -p'your_password' my_database > /path/to/backup/my_database_$(date +\%F).sql

PostgreSQL Example:

0 2 * * * /usr/bin/pg_dump -U postgres -d my_database -F c -f /path/to/backup/my_database_$(date +\%F).dump

Explanation:

• 0 2 * * * schedules the job to run every day at 2 AM.
• $(date +\%F) appends the current date to the backup file name to keep backups organized.

Restoring from Backup

MySQL:

mysql -u username -p database_name < backup_file.sql

Example:

mysql -u root -p my_database < my_database_backup.sql

PostgreSQL:

pg_restore -U username -d database_name backup_file.dump

Example:

pg_restore -U postgres -d my_database my_database_backup.dump

These steps should help you back up and restore your databases on a Linux server.