Skip to content

Backup/Restore MySQL database

Backup and restore MySQL database easy

Today, a quick post on automatic MySQL database backup. I will describe how easy and fast it is to automate MySQL database backup.

Backup MySQL database:

1) Manual Backup MySQL database:

At the beginning, let’s start with a manual backup. First, you need to install mysql-client on the computer that will be doing database backup. Once we have it, we define the connection parameters, give the IP address of the mysql server and the port if it is non-standard. We will also need username and password. In the example I used root and its password stored in a variable. Now we can define what database we want to backup, or enter ‘–all-databases‘ which will select all. Finally, we save the whole thing in the indicated location, for me it is /mnt/cifs/backup_alldb_$DATA1.sql. The location contains the $ DATA1 variable which adds the current date to the file.

apt-get install mysql-client
DATA1=$(date +"%Y%m%d")
mysqldump --host=10.10.10.10 --port=30006 --user=root --password=$MYSQL_PASS --all-databases  > /mnt/cifs/backup_alldb_$DATA1.sql

2) Automating Backup MySQL database – Method 1:

Automating this process is simple, but we only started doing it after we checked that manual backup works. Automatic backup can be installed, for example, in the GitLab runner, which can perform a backup according to the scheduler. Here I am using the mysql: 5.7.28 image which is compatible with the one on the server that will be backed up. Then the network drive is mounted, the backups will be saved on it. The backup itself looks almost identical to the manual operation. In addition, at the end I added a fragment that removes files older than 30 days. What I have not written here, and what else needs to be done is to define variables, logins and passwords and add a scheduler.

backup_mysql_X :
   stage: deploy
   image: mysql:5.7.28
   script:
     - DATA1=$(date +"%Y%m%d")
       # tworzenie folderów i montowanie dysków
     - mkdir -p /mnt/cifs
     - mount -t cifs -o username=$backup_user,password=$backup_pass //11.11.11.11/backup/db_mysql_nazwa1 /mnt/cifs
     - ls /mnt/cifs/
     
     #all database backup
     - mysqldump --host=10.10.10.10 --port=30006 --user=$MYSQL_USER --password=$MYSQL_PASS --all-databases > /mnt/cifs/backup_alldb_NAZWA1_$DATA1.sql
     
     #delete file older then 30 days
     - find /mnt/cifs/ -maxdepth 1 -type f -mtime +30 -name 'backup_alldb_NAZWA1*'
     - find /mnt/cifs/ -maxdepth 1 -type f -mtime +30 -name 'backup_alldb_NAZWA1*' -exec rm {} \;
   only:
     refs:
       - schedules
   only:
     variables: 
       - $ACTION == "backup"

3) Automating Backup MySQL database – Method 2:

Instead of creating Gitlab runners, you can backup databases on the postgresql server using cron. Just login as postgres and add the following command to the crontab (crontab -e):

0 2 * * * /usr/bin/mysqldump -u dbuser mydb > /mnt/cifs/db_backups/mysql-db-$(date +\%Y\%m\%d).sql
0 3 * * * find /mnt/cifs/db_backups/ -type f -name "*.sql" -mtime +30 -delete

And if we want to save some space, the files can be additionally packed:

0 2 * * * pg_dump -U postgres dbname > | gzip > /mnt/cifs/db_backups/dbname-$(date +\%Y\%m\%d).dump.gz
0 3 * * * find /mnt/cifs/db_backups/ -type f -name "*.dump.gz" -mtime +30 -delete

Restore MySQL database:

Often useful in the event of a failure is the restore option to restore the database from a previously performed backup. The easiest way to do this is by using the command:

# locally:
mysql  database_name < file.sql

# remote:
mysql -h 10.10.10.10 -u root --port=30543 -p NAZWA_BAZY < /mnt/db/file.sql

Below are some simple extra commands for mysql, which are also sometimes useful to check if the database or user is on the server. Log in to the server locally or, for example, using ‘mysql -h 10.10.10.10 -u root –port = 30006 -p‘

# display database:
SHOW DATABASES;

# delete database:
# DROP DATABASE database_name;
# displays tables from the database 'database_name':
USE database_name;
SHOW TABLES;
# displays users:
SELECT user FROM mysql.user;
SELECT DISTINCT user FROM mysql.user;
SELECT user,host FROM mysql.user;
# displays permissions:
SHOW GRANTS;
SHOW GRANTS FOR 'user_name';
# we leave by simply typing quit
quit

More information on mysql itself can be found at https://dev.mysql.com/doc/refman/8.0/en/programs-client.html

Soon, another article on how to make and automate it Backup PostgreSQL database.

Backup/Restore PostgreSQL database
SSH login without password -how to use keys
Tags: