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;