112 lines
3.5 KiB
Markdown
112 lines
3.5 KiB
Markdown
![Snel.com](https://static.snel.com/logo/Snel.com-Logo.png)
|
|
|
|
# MySQL / MariaDB backup script #
|
|
|
|
Allows you to create and restore local MySQL / MariaDB backups.
|
|
|
|
# Requirements #
|
|
|
|
Method 1:
|
|
Enter username and password in configuration section and uncomment.
|
|
|
|
Example:
|
|
```
|
|
...
|
|
# MySQL administrative user, uncomment if you want to use this instead of
|
|
# the value in /root/.my.cnf
|
|
MYSQL_USER='root'
|
|
# MySQL administrative user password, uncomment if you want to use this instead of
|
|
# the value in /root/.my.cnf
|
|
MYSQL_PW='ThisIsMyMySQLRootPassword'
|
|
...
|
|
```
|
|
|
|
Method 2:
|
|
Requires a /root/.my.cnf file which contains credentials so the user root can login to the MySQL / MariaDB server with sufficient privileges
|
|
without entering a password. Example /root/.my.cnf contents:
|
|
|
|
```
|
|
[client]
|
|
user=root
|
|
password=mysecretpassword
|
|
```
|
|
|
|
Don't forget to change the permissions:
|
|
```
|
|
sudo chmod 600 /root/.my.cnf
|
|
```
|
|
|
|
Test if it works with:
|
|
```
|
|
sudo mysqladmin status >> /dev/null 2>&1
|
|
if [ $? -ne 0 ]; then
|
|
clear && echo 'Error: Unable to connect to MySQL Server!'
|
|
else
|
|
clear && echo 'Successfully connected to the MySQL server!'
|
|
fi
|
|
```
|
|
|
|
Method 3: Plesk server use the admin user
|
|
```
|
|
sed -i "s|^#MYSQL_USER='root'|MYSQL_USER='admin'|g" /usr/local/sbin/backup_mysql.sh
|
|
sed -i "s|^#MYSQL_PW='ThisIsMyMySQLRootPassword'|MYSQL_PW=\$\(cat /etc/psa/.psa.shadow\)|g" /usr/local/sbin/backup_mysql.sh
|
|
```
|
|
|
|
## Install script ##
|
|
|
|
```
|
|
sudo wget -O /usr/local/sbin/backup_mysql.sh https://git.snel.com/snelcom/backup-mysql/raw/branch/master/backup_mysql.sh
|
|
sudo chmod 700 /usr/local/sbin/backup_mysql.sh
|
|
```
|
|
|
|
## Install cronjob ##
|
|
|
|
This will add a cronjob to root which will run this script daily at 0:10 am. Adjust as necessary.
|
|
|
|
```
|
|
(sudo crontab -l 2>/dev/null; sudo echo '10 0 * * * test -x /usr/local/sbin/backup_mysql.sh && /usr/local/sbin/backup_mysql.sh') | sudo crontab -
|
|
```
|
|
|
|
## Recover database ##
|
|
Method 1:
|
|
Assuming you want to restore my-testdb from your backups made on 20211012-0010, define variables:
|
|
```
|
|
DB='my-testdb'
|
|
NEWDB='my-testdb'
|
|
BACKUPDIR='/var/backup/mysql/20211012-0010'
|
|
```
|
|
|
|
TIP: You can change NEWDB to any other name if you wish to restore to an alternate database name!
|
|
|
|
Make sure you have a backup of the current database which will be replaced:
|
|
```
|
|
sudo mysqldump ${NEWDB} > /root/${NEWDB}-$(date +%Y%m%d).sql
|
|
```
|
|
|
|
And now the actual recovery (Need to be in the same session! Do not enter this in another SSH window / terminal):
|
|
```
|
|
sudo mysql -e 'DROP DATABASE IF EXISTS `'${NEWDB}'`'
|
|
sudo mysql -e 'CREATE DATABASE `'${NEWDB}'`'
|
|
for table in ${BACKUPDIR}/${DB}/*; do gunzip -c $table | sudo mysql ${NEWDB}; done
|
|
```
|
|
Method 2 - Plesk Servers:
|
|
Assuming you want to restore my-testdb from your backups made on 20211012-0010, define variables:
|
|
```
|
|
DB='my-testdb'
|
|
NEWDB='my-testdb'
|
|
BACKUPDIR='/var/backup/mysql/20211012-0010'
|
|
```
|
|
|
|
TIP: You can change NEWDB to any other name if you wish to restore to an alternate database name!
|
|
|
|
Make sure you have a backup of the current database which will be replaced:
|
|
```
|
|
sudo mysqldump -u admin -p$(cat /etc/psa/.psa.shadow) ${NEWDB} > /root/${NEWDB}-$(date +%Y%m%d).sql
|
|
```
|
|
|
|
And now the actual recovery (Need to be in the same session! Do not enter this in another SSH window / terminal):
|
|
```
|
|
sudo mysql -u admin -p$(cat /etc/psa/.psa.shadow) -e 'DROP DATABASE IF EXISTS `'${NEWDB}'`'
|
|
sudo mysql -u admin -p$(cat /etc/psa/.psa.shadow) -e 'CREATE DATABASE `'${NEWDB}'`'
|
|
for table in ${BACKUPDIR}/${DB}/*; do gunzip -c $table | sudo mysql -u admin -p$(cat /etc/psa/.psa.shadow) ${NEWDB}; done
|
|
``` |