Mysqldump

Note! By default mysqldump uses --lock-tables parameter when making a backup. That possibly prevents your website from working.

• You may want to use --skip-lock-tables but that may cause inconsistency in the backup.
• You can also change all tables from MyISAM to InnoDB and use hotcopy or option --single-transaction??? (haven't tested)
• If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores.

Backup Whole Database

Connect to the database server e.g. using SSH connection and execute following row:

mysqldump -u root -p drupal6 > drupal6_db_bu_20100625.sql

You can also ask mysqldump to add a drop table command before every create command by using the option --add-drop-table (this is probably the default option already). This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

mysqldump --add-drop-table -u root -p drupal6 > drupal6_db_bu_20100625.sql
or
mysqldump --add-drop-table -p drupal6 --user=root > /root/Desktop/drupal6_20100912.sql

Backup And Compress Database

mysqldump -u root -p drupal6 | gzip > drupal6_db_bu_20100625.sql.gz

Backup Only Specified Tables

Backup only "users" and "system" tables from the "drupal6" database:

mysqldump -u root -p drupal6 users system > drupal6_db_2_tables_bu_20100625.sql

Useful command to check what tables exists in specified database:

mysql -u root -p -e 'USE Drupal7; SHOW TABLES;'

Backup and choose tables from text file. Tables have to be on the same line and separated by spaces:

mysqldump -u root -p drupal6 `cat phpbb_tables_onsamerow.txt` > phpbb-tables-bu-20111019.sql

Case: Backup All PhpBB Forum Tables

• Get phpbbforum tables:

mysql -u admin -p -h 123.23.1.1 -e 'USE drupal6; SHOW TABLES;' | grep 'phpbb_' > phpbb-tables.txt

• Convert line breaks into spaces in phpbb-tables.txt file

echo $(cat phpbb-tables.txt) > phpbb-tables-samerow.txt

• Copy/backup table structure and data from source db:

mysqldump -u admin -p -h 123.23.1.1 drupal6 `cat phpbb-tables-samerow.txt` > phpbb-tables-bu-20111020.sql

• Restore data into a target database:

mysql -u admin -p -h 123.23.1.1 drupal7 < phpbb-tables-bu-20111020.sql

• Follow the progress:

SHOW TABLE STATUS LIKE 'phpbb_%'

Backup a Remote Database

mysqldump -h xxx.xxx.xxx.xxx -u username -p database_name > backup_file_name_20100628.sql

Use gzip to compress the remote database backup

mysqldump -h xxx.xxx.xxx.xxx -u username -p database_name | gzip > backup_file_name_20100628.sql.gz

Note! If you get for example following error message: mysqldump: Got error: 1130: Host 'xxx.xx.xxx.x' is not allowed to connect to this MySQL server when trying to connect

• Specified hostname or ipaddress could be wrong
• Check databases mysql.users table. Check the host column and user rights for the user you tried to login

Backup ALL Databases

mysqldump -u root -p -A > mysqlserver-all-db-20111016.sql

Backup Multiple Databases

To backup specific databases, e.g. all drupal dbs:

mysqldump -u root -p --databases drupal5 drupal6 drupal7 > all-drupal-db-20111016.sql

Useful command to check what databases exists:

mysql -u root -p -e 'SHOW DATABASES;'

Check what tables mysqldump file includes:

grep 'Database:' all-drupal-db-20111016.sql

MySQLDump Tips

If MySQLDump Tool Is Not Found

If you get a message: "mysqldump: command not found" try

locate mysqldump

If you find the mysql dump add /usr/local/mysql-5.0.51a-linux-i686/bin/ to your users path or copy the file to your /usr/bin folder, e.g.

cp -a /usr/local/mysql-5.0.51a-linux-i686/bin/mysqldump /usr/bin

If you don't have it in your computer install it by typing:

sudo apt-get install mysql-client  (the most suitable version will be installed)
sudo apt-get install mysql-client-5.0
sudo apt-get install mysql-client-core-5.5