mysqldump command is used to backup up MySQL databases. mysqldump has the following format:
mysqldump -u [login] -p [pass] [dbname] > [backup.sql]
where [backup.sql] is the file to which the backup should be written.
Backing up specific tables By default, mysqldump would backup the entire database. If you need to backup only specific tables, list the tables as follows:
mysqldump -u [user] -p [pass] [dbname] [tables] > [backup.sql]
where [tables] is the list of tables you wish to back up.
Backing up all databases
mysqldump --all-databases -u [user] -p [pass] > [backup.sql]
Backing up specific tables By default, mysqldump would backup the entire database. If you need to backup only specific tables, list the tables as follows:
mysqldump -u [user] -p [pass] --databases [dbnames] > [backup.sql]
where [dbnames] is the list of databases you wish to back up.
Adding drop table commands
mysqldump --add-drop-table -u [login] -p [pass] [dbname] > [backup.sql]
Drop the table if it already exists.
Backup only the structure
mysqldump --no-data -u [login] -p [pass] [dbname] > [backup.sql]
Writing a backup shell script
#!/bin/sh
dt=`date -I`
mysqldump --all-databases | gzip > backup-$dt.sql.gz