When you have access to the command line and you have the export large database, you will not be able to export from phpmyadmin.
Hence mysql provide you handy method to export the database from the command line. Below are various options, you can use while exporting the database.
——databases – This allows you to specify the databases that you want to backup. You can also specify certain tables that you want to backup. If you want to do a full backup of all of the databases, then leave out this option
——add-drop-database – This will insert a DROP DATABASE statement before each CREATE DATABASE statement. This is useful if you need to import the data to an existing MySQL instance where you want to overwrite the existing data. You can also use this to import your backup onto a new MySQL instance, and it will create the databases and tables for you.
——triggers – this will include the triggers for each dumped table
——routines – this will include the stored routines (procedures and functions) from the dumped databases
——events – this will include any events from the dumped databases
——set-gtid-purged=OFF – since I am using replication on this database (it is the master), I like to include this in case I want to create a new slave using the data that I have dumped. This option enables control over global transaction identifiers (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output.
——user – The MySQL user name you want to use
——password – Again, you can add the actual value of the password (ex. ——password=mypassword), but it is less secure than typing in the password manually. This is useful for when you want to put the backup in a script, in cron or in Windows Task Scheduler.
——single-transaction – Since I am using InnoDB tables, I will want to use this option.
——no-create-db – don’t create database
——no-create-info – don’t create table structure
——extended-insert=FALSE => Each row as seperate insert querry
Syntax : mysqldump ——triggers ——routines ——databases databasename ——user=root ——password > database_export_file_name.sql
Example : mysqldump ——no-create-db ——triggers ——extended-insert=FALSE ——routines ——databases mydatabase ——user=root ——password > mydatabase.sql
Sometime you have to type the absolute path for mysqldump executable path to execute the above command for example.
Example : /var/mysqlFolder/bin/mysqldump ——no-create-db ——triggers ——extended-insert=FALSE ——routines ——databases mydatabase ——user=root ——password > mydatabase.sql
More option you can explore on Maria DB website
Vinod Ram has been in Software Industry since 2006 and has experience of over 16 years in Software Development & Project Management domain specialised majorly in LAMP stack & Open Source Technology, building enterprise level Web based Application, Large Database driven and huge traffic Websites and Project Management.
He loves to write information articles and blog to share his knowledge and experience with the outside world and help people to find solution for their problems.