MySQL Database Backup using mysqldump command.

S.Gopala krishnan says:

Hi
I need examples to take database backup from online server.
I worked your examples query but it was not working.
i am using MySql. In MySql i use test database i need how to take backup in local folders
and how to run the restore database.
There was a problem like this error occur : “#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘mysqldump –-user root password = root db_test > db_test.sql’ at line 1 ”

Thank you
S.Gopalakrishnan
Email : [email protected]

Wouter says:

@S.Gopala krishnan
try this:
mysqldump -u root -psecret db_test > db_test.sql

John says:

Please tell me how to restore MySql database file using java class. I have written a code but it cant restore here is the code:

import java.io.File;
import javax.swing.JFileChooser;
import javax.swing.JPanel;

public class restoreDB extends JPanel {
    public restoreDB() {
        myRestore();
    }
	public void myRestore() {
		File file=null;
		JFileChooser filechooser=new JFileChooser();
		int selection=filechooser.showOpenDialog(new MainWindow());
                if(selection==JFileChooser.APPROVE_OPTION) {
                    file=filechooser.getSelectedFile();
                    String r=file.getAbsolutePath();
                    String s=r.replace('\\', '/');
                //    String s=r.replaceAll(" ", "\" \"");
                   // String[] executeCmd = new String[]{"C:/Program Files/MySQL/MySQL Server 6.0/bin/mysql", "userlogin", "--user=root" , "--password=root", "-e", s };
                    System.out.println(s);
                    try {
			//	Process runtimeProcess =Runtime.getRuntime().exec(executeCmd);
                    Process p=Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 6.0/"+
				"bin/mysql -u root -proot  < "+s);
                    }catch(Exception e) {
				System.out.println(e);
			}
                }
        }
}

please give me an example.

Joe says:

to back up all databases use –all-databases, not -all-databases. At least in my browser that’s how the code appears from this page (as an en-dash maybe?).

Joe says:

I think this blog forces two hyphens to become an en-dash.

to back up all databases use – – all-databases (two hyphens), not -all-databases (one hyphen). At least in my browser that’s how the code appears from this page.

NIraj Patel says:

Hi Viral,
Thnks for your article.
I want to share something.

We can also take Dump only rows selected by the given WHERE condition.

shell> mysqldump –user [username] –password=[password] [database name] [table name] where = “[condition]”\ > /tmp/sugarcrm_accounts_contacts.sql

Example:
shell> mysqldump –user root –password=myrootpassword db_test customers where = ” id=’15’ ” \ > db_test_customers.sql

Rara says:

Thank you for Backup all databases in MySQL command

Olof Larsson says:

You should take a look at this instead:
https://sourceforge.net/projects/automysqlbackup/

I just installed it and it works as a charm :D , Just what I was looking for!

This script is even in the ubuntu repositories! Just use:
apt-get install automysqlbackup

gajendran says:

hi..

i took mysql database backup in windows server and i restored that mysql db in linux pc.. its sucessfully restored ..i connected that mysql db in client xp mechine ..i compiled that db procedure but its show tables doesn’t exit..will u pls tel how to solve this problem

Ilan Hazan says:

Restoring a dump table into the MySQL master server can lead to serious replication delay.
To overcome the replication delay, caused by restoring the dump table on the master, there is a need to widespread the massive inserts. This can be done by the MySQL SLEEP command.
See As restoring a dump table into the MySQL master – you better get some sleep

Jennifer says:

What does the -h command do? I have been trying to figure it out and I don’t know what it does in the command line. See, like on this tutorial they do it: http://www.fortasse.com/tutorials/mysqldump-mysql-database-backup/

But on others they don’t… Anyone know?

khan says:

i tried mysqldump to backup it worked ie file .sql is created but when i try to restore it it also shows sucess but tables are not created

raj says:

mysqldump is a comment.. don run it in mysql…..

mysqldump -u root -p password db_test > db_test.sql

Alejandro Arauz says:

The only problem with the mysqldump command is that you need privileges to execute this command on the server. I had this problem before in a shared enviroment and I found this tool: MySQLBackupFTP (http://mysqlbackupftp.com). It has a free version that allows you to connect to MySQL through phpMyAdmin so you don’t need admin access to the server. I hope this help others with the same problem.

Atul Goyal says:

Dear I am using IBatis with Spring 3.0 for database for a web applicantion . Now can anyone tell me how can i take the back up of database ?

pranav says:

thanks a lot this helped me lot

Neo Cambell says:

Very nice explanation.

Kuldeep Singh says:

Your article are really awesome. Actually I was in search for some good articles on MySQL Database Backup file and finally I got one.
The most important thing of this article is simplicity and understandable. You’ve elaborate it very well. Check this links too it also having good explanation on MySQL database backup…..
http://mindstick.com/Blog/239/How%20to%20Back%20Up%20and%20Restore%20a%20MySQL%20Database

http://stackoverflow.com/questions/823841/mysql-backup-and-restore-from-command-line

Thanks Everyone for your valuable post!

vivek says:

mysqldump –opt –user=root –password db > db.sql try this it worked for me…

GIlbert says:

Hey friends where ll the backups be stored?….

Viral says:

Sir I am getting access denied problem while backing up database please give me alternate solution.

dhaval says:

I have query regarding to use mysqldump command. i have one mysql database and while i am creating .sql file using mysqldump commnad then it is creating not full backup. i don’t know how to take full data backup in mysql.

Lucy says:

Great article, directly to the point. Thanks for sharing!!!

Gurbrinder says:

Good work!
Well explained, appreciate your efforts.

gude says:

hi i m able to take the backup and even zip the data
now after i unzip the code i m not able to restore the same file.
can u provide me some suggestions.

Gurdeep Singh says:

i just want to say please check your “syntaxes”. FOR Backup multiple databases the right syntax is . mysqldump –user root –password=myrootpassword –databases db_test db_second db_third > db_test.sql

please don’t mind and i request you to provide right sysntaxes. Please go through once again..

Matt says:

Thanks for the tips. I was stuck on the correct syntax. This helped a lot.

Ahmed Khan says:

I think in second example you need to add “–databases” before the database names. so new command should look like:

mysqldump –-user root –-password=myrootpassword --databases db_test db_second db_third > db_test.sql

luoly says:

I think you missed a space before the password, and the commands “–database” before [database name] can’t be omitted:

mysqldump –uroot –pmyrootpassword db_test > db_test.sql

should be:

mysqldump –uroot –p myrootpassword -database db_test > db_test.sql

mysqldump is too trouble to learn, you can try the software – Automatic Backup Scheduler for MySQL ( http://www.databasethink.com ) to back up and resotre your databases.

Oussama says:

I really liked your post
i’m making a bash script to automatically backup db and send’em via ftp to a storage device :)

Abuzar says:

Nice job done…..!

anm says:

a really quick noob question. The command ‘mysqldump’ makes the backup and deletes the original database or does it leave it untouched? Thank you in advance.

Veerareddy Boddapati says:

export the database into our file using command prompt

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump -u root -p password bookch
eque >bookcheque.sql;

NithinS says:

An example on how to use these commands from JSP code can be found here
http://www.jvmhost.com/articles/mysql-postgresql-dump-restore-java-jsp-code

Vennam Anand says:

Hi Viral
I ran both the below queries
mysqldump –uroot –ppassword drf3 > db_test.sql
mysqldump -u root -p password drf3 > db_test.sql
My database name : drf3
User : root
Password : password
Both the queries are not working..
Kindly help me with this

Sandeep says:

Hi,
I want to take object-wise db backup. Like all tables, all views, all functions.
I tried this with reference to one of the comments posted above:

mysqldump -u [username] -p [password] [database_name] –all-tables

but this didn’t work and mysql ignored “–all-tables”.
Appreciate your help !!!
Thanks

Steven Middlenight says:

Often I meet MySQL database when working on WordPress projects. As for me I use dbForge Studio for MySQL for all tasks of MySQL management and administration. Speaking about backup I want to say that its built in MySQL backup tool ( http://www.devart.com/dbforge/mysql/studio/mysql-backup.html ) is very fast and easy to use! Few steps and you’ll get a dump file of any size!

Ugesh says:

Hi all
i try mysqldump –uroot –pmysql unidb > unidbusa.sql
getting error ( mysqldump command not found in centos os

vz says:

Ran the string, no errors but nothing else either. Just goes to the next line from original prompt MySql> to simply –>. What the heck does that mean? Dont see dump file anywhere on the system either. Just starting to use MySql so maybe it is meaningful but… Anybody some ideas?

Magesh M says:

@ GIlbert,

You can mention the path whihc you want to create the backup, if you not mentioned the backup location by default it was stored at “/root” directory.

See the below comamnd for path.

mysqldump -u root -p [password] [database-name] | gzip -9 > database-name.sql.gz

Neha Yadav says:

If i am trying mysqldump command for backup using command :

mysql> mysqldump –user root –password=admin replication_mysql > replication_my
sql.sql;

then it show an error like :

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘mysql
dump –user root –password=admin replication_mysql > replication_mysql.sql’ at
line 1

can any one suggest me how to do this ………

sobarie says:

I’ve tried and succeeded, but the data stored empty.
can any one suggest me how to do this ………

santhosh says:

I wont get db backup from mysql 5.5.1 version, When i tried like below it is giving the error.
mysqldump -u root -psanthosh s > s.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘mysql
dump -u root -psanthosh s > s.sql’ at line 1

please help me regarding this.
Thanks in advance.

jagadeesh says:

This video explains how to take backup mysql database in xampp shell
http://youtu.be/aeVrpj5J2bM

Wellington Torrejais da Silva says:

Thanks

atul says:

how to take backup of database for individual day in mysql when front end is .NET

Honorato Dalaguit III says:

please help me! i cant restore my database in Java. this is my code…

public boolean restoreDB(String dbUserName, String dbPassword, String source) {
 
        String[] executeCmd = new String[]{"C:\\Program Files (x86)\\MySQL\\MySQL Server 5.1\\bin\\mysql.exe", "--user=" + dbUserName, "--password=" + dbPassword,"-e", "source "+source};
 
        Process runtimeProcess;
        try {
 
            runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();
 
            if (processComplete == 0) {
                System.out.println("Backup restored successfully");
                return true;
            } else {
                System.out.println("Could not restore the backup");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
 
        return false;
    }

 private void jButtonRestoreActionPerformed(java.awt.event.ActionEvent evt) {        
            restoreDB("root", "1234", jTextField2.getText().toString());
    }        

Sudha says:

Hey All,

I have stumbled into a issue. I am trying to restore a backup that i took from one of MySQL server and copied this .sql file to a Mariadb 10.0 server. Trying to restore it like below:

[mysql@test-mdb-pc-001 mysql]$ mysql -uroot -p DBfromMySQLPrd</tmp/dump_2015-10-05_08-00-01.sql

However, when i connecto db DBfromMySQLPrd and run show tables; it doesnt show anything. cat ****.sql file shows contents though.

Can someone please guide me on whats wrong .

Regards
Sudha

Mahendran says:

Hi team,
Could anyone help me how we can reduce the time for mysqldump and restoring data into new database. I am doing this process on AWS RDS instance database

Database Volume: 45 gb, duration is 4 hours
dump size: 2.5 gb (zip)

Restore database: 45 gb, duration is 10 hours.

Is there any better option to do this process as quick as possible?

Thanks

Kuldip says:

I am trying to dump using mysqldump command but I am getting following error.

Info: Using unique option prefix ‘pass’ is error-prone and can break in the future. Please use the full name ‘password’ instead.

I tried all the combination, but still not working. I used -p, -pass, –password.

I am using MariaDB.

Prince says:

Hi,
Thanks for instruction you have given.
but my following commands gives error as
“D:\xampp\mysql\bin>mysqldump -um1 -pm2 test > db_test1.sql
mysqldump: Got error: 1044: Access denied for user ”@’localhost’ to database ‘?
um1’ when selecting the database”

mysqldump –um1 –pm2 test > db_test1.sql
mysqldump –-user m1 –-password=m2 test > db_test2.sql

here m1 = user name
m2 = password
host = localhost.
And yes this works fine once i will do the PDO connection.

sunish kumar says:

how to mysql dump into mdb or ms-access database