How To Export a Single Database from MySQL or MariaDB

<<Back to MySQL & MariaDB How To Page

Export a Single Database from MySQL or  MariaDB

Exporting the database in MySQL or MariaDB is rally easy and there is no difference. As you can see below mysqldump utility is available in both MySQL as well as MariaDB.
Although there are various tools to do this I will be using mysqldump. Los gehts

Step1> Login to your MySQL or MariaDB instance type show databases to list the databases running
I want to perform the export of TEST database.

[root@host~]#  mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58921
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database              |
+--------------------+
| information_schema  |
| test1                           |
| mysql                         |
| test                           |
+--------------------+
5 rows in set (0.02 sec).

Step2> Let us quickly find out the size of my TEST database.

mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
    -> FROM information_schema.TABLES GROUP BY table_schema ;
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| test1               |       25234.26562500 |
| information_schema |           0.00781250 |
| mysql              |           0.66800880 |
| test              |        1227.98437500 |
+--------------------+----------------------+
4 rows in set (4.27 sec)

The size of this test database is ~1.3 GB. I need to ensure that there is sufficient space available in file system directory where I am going to dump this database.

Step3> Export the database using mysqldump utility

[root@host~]#mysqldump --single-transaction --databases test > /var/backup/tmp_test/test.sql

I am using --single-transaction option to create consistent snapshot.

--single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them. Option automatically turns off --lock-tables.