Backup and Restore MariaDB SQL

  1. Open a terminal or putty ssh for windows and type the “mysql -u root -p” followed by your password.

[[email protected] ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4803
Server version: 5.5.44-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

MariaDB_SQL

2. list all database to show the filename of the DB by typing “show databases;”

MariaDB [(none)]> show databases;
+————————-+
| Database                |
+————————-+
| information_schema      |
| mysql                   |
| performance_schema      |
+————————-+

3. Create folder called “backupdb” and save it to that directory

Desktop # mkdir backupdb
Desktop # cd backupdb/
Desktop backupdb # mysqldump database_name > database_name.sql

Selection_001

or to save multiple databases you can use this below command

mysqldump –databases db_one db_two > two_databases.sql

and if you want to backup all the available databases in your MariaDB SQL then use this below command

mysqldump –all-databases > all_databases.sql

 

4. Restore the SQL Database

mysql db_name < database_file.sql

In the code command, db_name is the name of database you want to restore, and database_file.sql is the name of the backup file to be restored..

And if you are trying to restore a single database from dump of all the databases, you have to let mysql know like this:

mysql –one-database database_name < all_databases.sql

5. Now create script to run in the crontab job later on

vim sql-backup.sh

then copy and paste this script

#!/bin/bash
#!/bin/sh
timestamp=$(date ‘+%Y-%m-%d-%H’)
DB_USER=’user admin’
DB_PASS=’password’
DB_NAME1=’db name’
## To Backup Single Database
/bin/mysqldump –user=”$DB_USER” –password=”$DB_PASS” “$DB_NAME1″ | gzip > /home/user/BACKUP/backup_database/”$DB_NAME1″_”$timestamp”.sql.tar.gz

## To backup all the Databases
/bin/mysqldump –user=”$DB_USER” –password=”$DB_PASS” –all-databases | gzip > /home/user/BACKUP/backup_database/all_databases_”$timestamp”.sql.tar.gz

Selection_005

Now save the file pressing key esc  then type :wq then change the properties of the file to executable

chmod +x sql-backup.sh

6. Create a job in the crontab and run this as root

crontab -e

# Backup SQL Databases everyday @01:00AM

* 1 * * * /home/fsmendoza/script/sql_backup.sh

cron

And that’s it the scheduled backup and script is done. If you want to copy or backup the database to USB drive you need to do the next step

7. Install the rpm package “ntfs-3g and fuse” to support the NTFS partitions

yum install ntfs-3g fuse

Selection_003

Selection_004

And now try to mount the USB partition to backup the files in external storage

mkdir mount_usb

mount /dev/sdd1 mount_usb

And you can copy the files over

 

 

Be the first to comment on "Backup and Restore MariaDB SQL"

Leave a comment

Your email address will not be published.