How To Backup and Restore MySQL Database

MySQL Backup and Restore Commands for Database Administration

In this article, we will learn how to perform backup operations on MySQL databases using mysqldump command and how to restore them in Linux.

To Install the Package

Install the package and execute the following command to setup mariadb.

[root@linuxhelp ~]# yum install mariadb-server mariadb -y
Loaded plugins: fastestmirror, langpacks
base                                                                                                                 | 3.6 kB  00:00:00     
Loading mirror speeds from cached hostfile
 * base: mirror.nbrc.ac.in
 * extras: mirror.nbrc.ac.in
 * updates: mirror.nbrc.ac.in
Resolving Dependencies
-->  Running transaction check
--->  Package mariadb.x86_64 1:5.5.47-1.el7_2 will be installed
.
.
.
Dependency Installed:
  perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7     perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7     perl-DBD-MySQL.x86_64 0:4.023-5.el7      
  perl-DBI.x86_64 0:1.627-4.el7                    perl-Data-Dumper.x86_64 0:2.145-3.el7           perl-IO-Compress.noarch 0:2.061-2.el7    
  perl-Net-Daemon.noarch 0:0.48-5.el7              perl-PlRPC.noarch 0:0.2020-14.el7              

Dependency Updated:
  mariadb-libs.x86_64 1:5.5.47-1.el7_2                                                                                                      

Complete!

Once the package is installed, restart and enable the service.

[root@linuxhelp ~]# systemctl restart mariadb
[root@linuxhelp ~]# systemctl enable mariadb
ln -s ' /usr/lib/systemd/system/mariadb.service'  ' /etc/systemd/system/multi-user.target.wants/mariadb.service' 

[root@linuxhelp ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we' ll need the current
password for the root user.  If you' ve just installed MariaDB, and
you haven' t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from ' localhost' .  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named ' test'  that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you' ve completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

To create Simple Database and tables

Login to the database.

[root@linuxhelp ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with   or g.
Your MariaDB connection id is 10
Server version: 5.5.47-MariaDB 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)]> 

Create a New database called ' test'

MariaDB [(none)]>  create database test 
Query OK, 1 row affected (0.10 sec)

MariaDB [(none)]>  show databases 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

And create a sample table inside the test database.

MariaDB [(none)]>  use test 
Database changed
MariaDB [test]>  create table sample (a int, b int, c int) 
Query OK, 0 rows affected (0.21 sec)

Insert the values to the newly created tables.

MariaDB [test]>  insert into sample (a,b,c) values (1,2,3) 
Query OK, 1 row affected (0.19 sec)

MariaDB [test]>  select * from sample 
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
+------+------+------+
1 row in set (0.15 sec)

MariaDB [test]>  exit
Bye

To Backup MySQL Database

Use the following syntax to take a backup of MySQL databases.

[root@linuxhelp ~]# mysqldump -u [username] &ndash p[password] [database_name] >  [dump_file.sql]

To Backup a Single MySQL Database

Run the following command to take a backup of single database. The resultant will be dumped into a single dump file with .sql extension.

[root@linuxhelp ~]# mysqldump -u root -p test >  test.sql
Enter password: 
[root@linuxhelp ~]# ls
anaconda-ks.cfg  Desktop  Documents  Downloads  initial-setup-ks.cfg  Music  Pictures  Public  Templates  test.sql  Videos

To Backup Multiple MySQL Databases

Use the following command to take backup of multiple databases.

[root@linuxhelp ~]# mysqldump -u root -p --databases test test1 >  multi.sql
Enter password: 
[root@linuxhelp ~]# ls
all.sql  anaconda-ks.cfg  Desktop  Documents  Downloads  initial-setup-ks.cfg  multi.sql  Music  Pictures  Public  Templates  Videos

To Backup All MySQL Databases

Use the following command to take backup of all available databases.

[root@linuxhelp ~]# mysqldump -u root -p --all-databases >  all-data.sql
Enter password: 
[root@linuxhelp ~]# ls
all-data.sql  anaconda-ks.cfg  Documents  initial-setup-ks.cfg  Music     Public     Videos
all.sql       Desktop          Downloads  multi.sql             Pictures  Templates

To Backup MySQL Database Structure Only

Use the following command, if you want to take the backup of database structure without data and it exports to the new file.

[root@linuxhelp ~]# mysqldump -u root -p --no-data test >  nodata.sql
Enter password: 
[root@linuxhelp ~]# ls
all-data.sql  anaconda-ks.cfg  Documents  initial-setup-ks.cfg  Music       Pictures  Templates
all.sql       Desktop          Downloads  multi.sql             nodata.sql  Public    Videos

To Backup MySQL Database Data Only

Use the following command, to backup only database data without structure.

[root@linuxhelp ~]# mysqldump -u root -p --no-create-db --no-create-info test >  dataonly.sql
Enter password: 
[root@linuxhelp ~]# ls
all-data.sql  anaconda-ks.cfg  Desktop    Downloads             multi.sql  nodata.sql  Public     Videos
all.sql       dataonly.sql     Documents  initial-setup-ks.cfg  Music      Pictures    Templates

To Backup Single Table of Database

Use the following command to take backup of single table or certain tables of your database.

Example: To take backup of sample table from the database test.

[root@linuxhelp ~]# mysqldump -u root -p test sample >  sample.sql
Enter password: 
[root@linuxhelp ~]# ls
all-data.sql  anaconda-ks.cfg  Desktop    Downloads             multi.sql  nodata.sql  Public      Templates
all.sql       dataonly.sql     Documents  initial-setup-ks.cfg  Music      Pictures    sample.sql  Videos

To Backup Multiple Tables of Database

Use the following command to take backup of multiple tables from the database.

Example:

To take backup of sample, example tables from the database test.

[root@linuxhelp ~]# mysqldump -u root -p test sample example >  tables.sql
Enter password: 
[root@linuxhelp ~]# ls
all-data.sql  anaconda-ks.cfg  Desktop    Downloads             multi.sql  nodata.sql  Public      tables.sql  Videos
all.sql       dataonly.sql     Documents  initial-setup-ks.cfg  Music      Pictures    sample.sql  Templates

To Backup Remote MySQL Database

Use the following command to take the backup of remote server [192.168.5.89] database [info] into a local server.

[root@linuxhelp ~]# mysqldump -h 192.168.5.89 -u root -p info >  info.sql
Enter password: 
[root@linuxhelp ~]# ls
all-data.sql  anaconda-ks.cfg  Desktop    Downloads  initial-setup-ks.cfg  Music       Pictures  sample.sql  Templates
all.sql       dataonly.sql     Documents  info.sql   multi.sql             nodata.sql  Public    tables.sql  Videos

To Restore MySQL Database

Use the following command to restore a database and also create an empty database on the target machine.

[root@linuxhelp ~]# mysql -u root -p test <  test.sql
Enter password: