The mysqldump command dumps databases/tables to standart output. You can use pipe redirect to save it to file:
mysqldump [options] >dump.sql
When you dump a database there are some options of mysqladmin you need to understand:
–skip-comments – Use this option if you want make a smaller file
–compatible – if you export database from higher version to lower you will need this option. Possible values are:
–compatible=mysql323
–compatible=mysql40
–compatible=mysql323
–disable-keys – this will speed up import process
–add-drop-table – if you make backups you can assume that when you restore it, tables will be created and they need to be dropped. This option does it.
–quote-names – if you use reserved words as comumn or table names this will prevent errors during import.
–skip-comments – you don’t want comments to make your dump file bigger
–routines – dumps stored functions and procedures
You can specify credentials with these options:
-h, –host=name
-p, –password[=…]
-u, –user=name
… or enter them in /etc/my.cnf
[mysqldump]
password=
#user can be root if you own the server or you can create username that has select permission to all your databases.
user=
Here are examples how to call mysqldump (combine with options above):
How to dump a table(s):
mysqldump [i]<db_name>[/i] [i]<table1>[/i] [i]<table2>[/i] ...
How to dump a database:
mysqldump [i]<db_name>[/i]
How to dump a more than one database:
mysqldump [u]--databases[/u] [i]<db_name1>[/i] [i]<db_name2>[/i] ...
How to dump all databases:
mysqldump --all-databases
<h2>Dumping databases my way.</h2>
If you are lazy like me (or if you like to be practical) you would not want to write a simple wrapper script. My script is called dumpdbs:
#!/bin/bash
#
#MYSQL_DUMP=/usr/bin/mysqldump
MYSQL_DUMP=`which mysqldump`
#
#if you use extended insert make sure you specify max_allowed_packet in both [mysqldump] and [mysqld] sections of your my.cnf. Example: max_allowed_packet = 16M. If the packet size is bigger on dump location then you won't be able to import it.
# if you don't have control over my.cnf on both servers - the "dump" one and the import one (if it's not the same server), then you probably want to use --skip-extended-insert option. And yes, one of these is default, but it doesn't hurt to include it when you use wrapper script.
# --extended-insert \
#
$MYSQL_DUMP $* \
--skip-extended-insert \
--skip-comments \
--disable-keys \
--add-drop-table \
--routines \
--quote-names