Search:  
Gentoo Wiki

MySQL/Backup

This article is part of the HOWTO series.
Installation Kernel & Hardware Networks Portage Software System X Server Gaming Non-x86 Emulators Misc

This article is still a Stub. You can help Gentoo-Wiki by expanding it.

Contents

Data Backup

how to dump the database entirely into a file, called dumpfile.mysql:

let the database be "pluto", the user "pippo", and his password "ciccio".

mysqldump -ac --add-drop-table --user="pippo" --password="ciccio" pluto > dumpfile.mysql

If you want a complete backup of all databases with all functionality and want to restore them into MySQL then the --opt optional tells mysqldump to include it:

 mysqldump --opt --user="pippo" --password="ciccio" --all-databases > dumpfile.mysql

According to the mysqldump man page, "As of MySQL 4.1, --opt is enabled by default. To disable the options that it enables, use --skip-opt." So in other words, if you are using MySQL 4.1 or greater, you do not need to worry about the --opt option.

Data Recovery

to get back your data

mysql pluto -u pippo -p

--->enter in mysql shell

mysql> source dumpfile.mysql;

this should work.

Try this first with non important data, as i don't assume any responsability for errors or mistakes -- dave_AT_ccni_DOT_it

For more information, see www.newsforge.com

Automatic Backup

I like to use the AutoMySQLBackup script to backup my MySQL databases.

Just download the script, enter your mysql root password, select your databases and other options, and then stick it in your /etc/cron.daily/ directory.

If you have the ability to create users and do not want your root mysql password in a shell script somewhere you can create a new 'backup' user specifically for this. The following command logs in as the root user (prompts for password) and then creates a mysql user named backup with password backupPW. The user has only SELECT priveleges and will only connect from localhost, thus if the password is read by another they will not be able to edit the database.

 mysql -u root -p -e "GRANT SELECT, LOCK TABLES ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'backupPW';"

Be sure to edit the downloaded script to have the correct login and password for the backup user and to save the backups to the correct location.

Backup Troubleshooting

Error 1044

If you are getting the error mysqldump: Got error: 1044: Access denied for user 'user'@'server' to database 'db' when using LOCK TABLES then the user does not have lock priviledges. Use a different user with adequate privileges. If you only have this user account, you can use the --single-transaction option to avoid the LOCK TABLES function of mysqldump.

If an error code is returned when issuing a backup request, the most likely cause is insufficient memory or insufficient disk space. You should check that there is enough memory allocated for the backup. Also check that there is enough space on the hard drive partition of the backup target.

Currently (as of MySQL 4.1.10a) NDB does not support repeatable reads, which can cause problems with the restore process. While the backup process is "hot", restoring a MySQL Cluster from backup is not a 100% "hot" process. This is due to the fact that, for the duration of the restore process, running transactions get non-repeatable reads from the restored data. This means that the state of the data is inconsistent while the restore is in progress.

See Also

Retrieved from "http://www.gentoo-wiki.info/MySQL/Backup"

Last modified: Mon, 04 Aug 2008 09:32:00 +0000 Hits: 35,642