Creator: Created page with "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..."`


2019-09-08T14:38:55Z
Created page with "Note! By default mysqldump uses <code>--lock-tables</code> parameter when making a backup. That possibly prevents your website from working. * You may want to use <code>--skip..."
New page
Note! By default mysqldump uses <code>--lock-tables</code> parameter when making a backup. That possibly prevents your website from working.

* You may want to use <code>--skip-lock-tables</code> but that may cause inconsistency in the backup.

* You can also change all tables from MyISAM to InnoDB and use hotcopy or option <code>--single-transaction</code>??? (haven't tested)

* If you are doing a backup on the server and your tables all are MyISAM tables, consider using the [http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html 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: <code>mysqldump: Got error: 1130: Host 'xxx.xx.xxx.x' is not allowed to connect to this MySQL server when trying to connect</code>

* 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 ==



<pre>

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

</pre>



== Backup Multiple Databases ==



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

<pre>

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

</pre>



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

Mysqldump - Revision history

Creator: Created page with "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..."

Creator: Created page with "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..."`