How To Export Databases in MySQL or MariaDB

by: myfreeonlinetoolsPosted on:

Export Databases in MySQL or MariaDB

Export databases is a basic task for all web developers when using MySQL or MariaDB.

Use MySQL dump for database backup its require for migration or server exchange. 

Use my mysqldump for export single, multiple or all databses. 

Command for MySql or MariaDB Database export.

Nginx server database export without login to SQL.

Export MariaDB or MySql Database

Export single database

mysqldump -u USERNAME -p PASSWORD DBNAME > DBBACKUP.sql

for store database in root 

mysqldump -u USERNAME -p PASSWORD DBNAME > ~/DBBACKUP.sql

Export multiple database

mysqldump -u USERNAME -p PASSWORD --databases DB1 DB2 DB3.. >DBBACKUP.sql

for store database in root

mysqldump -u USERNAME -p PASSWORD --databases DB1 DB2 DB3.. > ~/DBBACKUP.sql

for store database in root

mysqldump -u USERNAME -p PASSWORD --all-databases > ~/ALLDBBACKUP.sql

Exports only tables from selected database.

mysqldump -u username -p database_name tablename_1 tablename_2 > name_sql_file.sql

for store database in root

mysqldump -u username -p database_name tablename_1 tablename_2 > ~/name_sql_file.sql

mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]

How to download MySql or MariaDB database in compress format

mysqldump -u root -ppassword wpdb | gzip > wpdb_backup.sql.gz

 

Expert database with login to your SQL Shell 

mysql -u root -p;
Import your dump sql file in your SQL
source ~/wordpress.sql

 

How to Use mysqldump Without Password?

You have to create a file in your home directory with SQL credentials. 

Use the given command to create a file.

nano ~/mysql.txt

Now add your MySQL credentials as given below.

[mysqldump]
user=root
password=password

Save and close the file and every time you can provide a file in place of credentials like in the code given below.

mysqldump --defaults-file=~/mysql.txt wpdb > wpdb_backup.sql

Parameters Signifies

-u : MySQL username.

-p : MySQL password.

DBNAME : The name of the database that you want to backup.

DBBACKUP.sql

The name of the backup file you want to generate.

-h : Hostname

–databases : Determine the database.

-all-databases : Backup all databases.

–default-auth=plugin

It is used to specify the client-side authentication plugin to use.

–compress : Enable compression in server/client protocol.

-P : Port number to use for MySQL connection.

Configuration

Set the network buffer length (for large size files)

set global net_buffer_length=1000000;
Set the maximum allowed packet size
set global max_allowed_packet=1000000000;
To avoid delays or errors, disable the foreign key.
SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

After done all don’t forget to enable the foreign key

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;