How to take complete backup of mysql database using mysqldump command line utility? I tried the following command :
mysqldump -u username -p password db1 > backup.sql
It gave error like You have an error in your SQL Syntax.Please Help..
-
Try this .. mysqldump -u username -ppassword db1 > backup.sqlSiraj Khan– Siraj Khan2013-11-11 10:16:19 +00:00Commented Nov 11, 2013 at 10:16
-
Did any solution work?Mad Dog Tannen– Mad Dog Tannen2013-11-24 00:22:05 +00:00Commented Nov 24, 2013 at 0:22
Add a comment
|
5 Answers
try like this- (remove space between -p and password)
mysqldump -u username -ppassword db1 > backup.sql
For more info: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
1 Comment
Mad Dog Tannen
You dont need spaces after -u and -p. Also, some more options should added if a complete dump is to be made.
backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
Ref: http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/
Comments
You can specify mysqldump to take entire backup as :
mysqldump -u root -p --all-databases > alldb_backup.sql
and if you think dump file size is too large you can export in ZIP format as well:
mysqldump -u root -p --all-databases | gzip -9 > [alldb_backup.sql.gz]
Comments
If it's an entire DB, then:
$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql
If it's all DBs, then:
$ mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql
If it's specific tables within a DB, then:
$ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql
You can even go as far as auto-compressing the output using gzip (if your DB is very big):
$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz
If you want to do this remotely and you have the access to the server in question, then the following would work (presuming the MySQL server is on port 3306):
$ mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql
Comments
If you want to make a COMPLETE backup, you need to add , --all-databases.
Like this
mysqldump -uUserName -ppassword --all-databases --routines > "fulldatabase.sql"
Note that you dont need spaces between -uUserName and -pPassword.
Also, if you are using a PORT in Mysql you must define the syntax like this
mysqldump -uUserName --password=password -P yourportnumber --all-databases
--routines > "fulldatabase.sql"
This ensures all stored procedures are also dumped to the file.