MariaDB Backup Database

In this MariaDB tutorial, we will learn about the “MariaDB Backup Database” which is how to get back up of database on different systems Additionally, we will cover the following topics.

• MariaDB backup database
• MariaDB backup database command line
• MariaDB backup database windows
• MariaDB backup database script Linux
• MariaDB backup database script windows
• MariaDB backup database cron
• MariaDB backup database PHPMyAdmin
• MariaDB backup database docker
• MariaDB backup database ubuntu
• MariaDB backup database centos

MariaDB Backup Database

It is often crucial to backup the database in MariaDB because the database store some potential information about the company and its sales, profit, and employees. Having a backup of the database, the administrator can recover the lost information due to some uncertain events like system crashes, data corruption, hardware failure, etc.

There are two methods for backing up the database in MariaDB.

1. Logical Backup
2. Physical Backup

The difference between logical and physical backup is given below.

Logical Backup	Physical Backup
It backups the data into plain text files like CSV, etc.	It backups the raw data like files and database directories.
Logical backup can be restored on other hardware configurations and databases like MySQL and Postgres, etc.	It cannot be restored on other hardware and database versions. To restore the database the hardware and database must be similar to where it was backed up.
It doesn’t back up the log and configuration files.	It also backup the log and configuration files.
Logical backup takes lots of time to backup and restore the database.	Generally, it takes less time to backup and restore the database.
This kind of backup can be larger in size but preferred for small datasets.	It can be large in size but smaller than logical backup.
It can’t back up the database while the MariaDB server is offline.	It can backup the database while the MariaDB server is offline.

Some MariaDB tools are given below.

• Mariabackup: It is a tool for physical backup that online backup the data of MyISAM, InnoDB and Aria tables.
• Mysqldump: It is a tool for logical backup that is more flexible in restoring the database on different hardware and database.

Read: MariaDB Truncate Table

MariaDB backup database command line

In this section of MariaDB, we will use the mysqldump command to get the logical backup of the database.

The syntax is given below.

mysqldum -u user_name -p -A > path/database.sql

Where,

• mysqldump -u user_name: It is a statement that instructs MariaDB that is the command mysqldum must be executed by the user user_name where the option -u is used to specify the user. The option -p means if it asks for password then enter the password for that user.
• -A > path/database.sql: It is a statement that instructs MariaDB to backup all databases which are specified using the option -A. The greater-than symbol (>) means redirect of the standard output to the specified path and file. Where database.sql is the the name of the backup file.

Open command-line on your system and type the below code to backup the database.

mysqldump -u root -p -A > C:/database/backup.sql

The output of the above code is given below.

In the above code, the command mysqldump is executed by the user root and if it asks for a password that is input using the option -p then enter the password. -A means exporting all the databases to the directory C:/database/ as file backup.sql.

Below is the given output showing the location of the backup file where it is stored after running the above code.

Read: MariaDB Transaction

MariaDB Backup Database Windows

To get the MariaDB backup, here we will use the GUI tool, HeidiSQL which is an interface to manage the different databases like MariaDB, MySQL, etc. Before proceeding to backup, please install the tool HeidiSQL on your windows machine. Follow the below steps to get a backup of the database in MariaDB using the HeidiSQL on windows.

Open the HeidiSQL tool select the Session name and enter the user name and password for that session. As given in the below output.

After entering the username and password for the session, click on the Open button to connect to that host or session. A window will appear for that host with databases and query tabs as given in the below output.

After the window appears, first select the database from the left side panel where all the database for that user exists. As we have selected the mysql database and right-click on that for options, from the options, select the option Export database as SQL.

A Table tools a window appears after selecting the option Export database as SQL. Follow the steps as specified in the below output.

1. Click on the SQL export tab with Table tools.
2. Checkmark the option Create of field Database(s).
3. Checkmark the option Create of field Table(s).
4. Specify the filename and path by clicking on the icon Folder in front of the field Filename.
5. Click on Export button at the right bottom corner to backup the database.
6. At last, click on Close button at the right bottom corner beside the Export button to close the current window.

The output of the above steps is given below.

Go to the specified path in step 4 to check the created database file.

From the above output, we have successfully created the backup of the database as Windows_database.sql.

Read: MariaDB Order By Clause

MariaDB Backup database script Linux

The Linux Script can also be used for backup of the database in MariaDB, the script is called Shell Script in Linux. The extension of the shell script file is (.sh) like XYZ.sh.

Let’s get the backup of the database using shell script in Linux by following the below steps:

First, open the terminal on a Linux machine and type the below code to open the script.

sudo nano database.sh

The above code creates and opens a new script file database.sh. Type the file code in that file to create the backup of the database.

#!/bin/bash
echo 'Starting backup of the database'
mysqldump -u root -p12345 -A > /home/saurabh/Database/backup.sql
echo 'Backup completed successfully'

Where #!/bin/bash indicate the bash shell where the command is executed. The command echo is used to display the text in the terminal and mysqldump -u root -p12345 -A > /home/saurabh/Database/backup.sql is the command to create the backup of the database.

Now save the file and come back to the terminal, then run the below code to make the database.sh file as an executable file.

sudo chmod +x database.sh     

Then execute the file to create the backup of the database.

./database.sh -- This is how script file is executed in Linux

Go to the location where the backup file is created using the above code.

Read: MariaDB DATEDIFF Function

MariaDB backup database script windows

The backup of the database in MariaDB can be taken using the script on a Windows machine, this script contains the command to execute any task in windows automatically, here we will use the most commonly used script on windows which is a Bash shell script.

Whatever the command is written in command-line for execution, that command can be written within the simple text file as a script. The bash script file has an extension of (.bat), which means the file that contains command is saved with the extension .bat like xyz.bat.

To create the script to backup the database, open any text editor like Notepad and type the following command.

ECHO OFF
mysqldump -u root -p -A > C:/database/backup.sql
ECHO Successfully backup the database
PAUSE

After writing the above code in that file, save the file with extension .bat, as here we have saved like MariaDB_Backup.bat.

Wherever the file is saved, go to that location and double click on that file to execute the script.

After clicking on that file a command prompt appears, if it asks for a password, enter the password of the MariaDB user. Then press any key exit from the command prompt.

In the above output, we can see that the message Successfully backup the database. Let’s go to the location where the backup file is saved.

Now, here is our backup file that we have created using the script MariaDB_Backup.bat.

Read: MariaDB Create Sequence + Examples

MariaDB backup database cron

The CRON in Linux is a program that runs the tasks at a scheduled time automatically. The cron interpreter the cron table and also called crontab that contains predefined commands and tasks needed to execute at a specific time.

Open the corn table using the below code.

sudo crontab -e

The above code opens the cron table where commands are defined that are executed at a scheduled time. After running the above code, it asks for which text editor we want to use simply enter the 1 to open the crontab file in a nano text editor.

After opening the crontab, write the below code to get the backup of the database.

--m h dom mon dow command
  * * * * * mysqldump -u root -p12345 -A > /home/saurabh/Database/backup.sql

Where,

• m: It is used to specify the minute of the time.
• h: It is used to specify the hour of the time.
• dom: It is used to specify the day of the month.
• mon: It is used to specify the month of the year.
• dow: It is used to sepcify the day of the week.
• command : It is used to spcfify the command that is executed on specified time.

if we use the asterisk symbol (*) in place of these times then it mean every minute, hour, day of the month, month, and day of the week.

Running the above code creates the backup of the database at every minute.

Read: MariaDB Primary Key

MariaDB backup database PHPMyAdmin

The PHPMyAdmin is a tool to handle the databases like MariaDB and MySQL for servers and is also called the administration tool. The backup can also be taken on PHPMyAdmin for MariaDB.

Follow the below steps to get the backup the database on PHPMyAdmin :

Login into PHPMyAdmin using the default user and password and select the database as MariaDB from the Server choice options, Then click on the button GO to log in that are given below in the picture.

After login into PHPMyAdmin, the PHPMyAdmin home page opens as given in the below picture.

Now, we are going to create a new database as United_States and please follow the below steps to create a new database in PHPMyadmin by looking at the above output.

1. Click on the button New to create a new database in PHPMyadmin.
2. Provide the name of the database as United States.
3. Then click on the button Create to create a database.
4. At last click on Server: MariaDB:3307 what exists at top of the current page to go to the home page of PHPMyadmin.

After these steps, on the home page of PHPMyadmin, click on the tab Database to show all the databases. Then select the database that we have created in the above steps from the existing databases and click on the export Export Look in the below picture.

At last, the export section appears for the configuration of backup files like name, format, etc.

In the export section,

1. Select the Export Method, there are two methods Quick and Custom. In the custom method, we can set the database name, character set, etc.
2. After selecting the Export Method, select the format of backup from the Format section like SQL, CSV, etc.
3. Then click on the button GO to download the backup file.

The output of the above steps is given below.

This is how to backup the MariaDB database using the PHPMyadmin.

Read: MariaDB Delete Row + Examples

MariaDB backup database docker

The docker is used to manage the container on the server, it uses the OS-Level Virtualization to run the software in a separate environment as containers. The container is a package of software in standard units.

Open docker and run the container, then click on the CLI button that is indicated using the red arrow in the below output to open the docker command-line interface for MariaDB.

Type the below command to backup the database.

mysqldum -u root -p -A > '/home/backup.sql'

In the above code, the command mysqldump is executed by the user root and if it asks for a password that is input using the option -p then enter the password. -A means exporting all the databases to the directory '/home/backup.sql'

Move to the directory where the file backup.sql is saved.

cd home --change the current directory to home

View the file using the below command.

ls --It is used to view all the file and folder in current directory

Read: MariaDB Full Outer Join

MariaDB backup database ubuntu

The same command mysqldum is used to backup the MariaDB in Ubuntu as we have used in the above sub-section “MariaDB backup database command line”.

Run the below code to backup the database in the Ubuntu terminal.

sudo mysqldum -u root -p -A > '/home/saurabh/Database/backup.sql'

In the above code, the command mysqldump is executed by the user root and if it asks for a password that is input using the option -p then enter the password. -A means exporting all the databases to the directory '/home/saurabh/Database/backup.sql'.

Below is the given output showing the location of the backup file where it is stored after running the above code.

Read: MariaDB Cast with Examples

MariaDB backup database centos

Here we will backup the database of MariaDB running on CENTOS. We will use the same code that we have used in the above sub-section “MariaDB backup database Ubuntu” because CentOS and Ubuntu are Linux distribution-based operating systems.

mysqldum -u root -p -A > '/home/osboxes/Documents/backup.sql'

In the above code, the command mysqldump is executed by the user root and if it asks for a password that is input using the option -p then enter the password. -A means exporting all the databases to the directory '/home/osboxes/Documents/backup.sql'.

The output of the above below is the given output showing the location of the backup file where it is stored after running the above code

You may also like to read the following MariaDB tutorials.

• MariaDB create procedure
• Change Column in MariaDB
• MariaDB Update Statement
• MariaDB Insert Into + Examples
• MariaDB Delete From Statement
• MariaDB Reset Root Password
• MariaDB Enable Remote Access

So, in this tutorial, we will learn about the “MariaDB backup database” and covered the following topics.

• MariaDB backup database
• MariaDB backup database command line
• MariaDB backup database windows
• MariaDB backup database script Linux
• MariaDB backup database script windows
• MariaDB backup database cron
• MariaDB backup database PHPMyAdmin
• MariaDB backup database docker
• MariaDB backup database ubuntu
• MariaDB backup database centos