Web	me09.cublog.cn

google

MySQL/Backup

From Gentoo Linux Wiki

< (Redirected from )

Jump to: ,

This article is part of the series.

• • • • • • • • • •

This article is a . You can help Gentoo-Wiki by .

Contents

[]

[]

Data Backup

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

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

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

If you want a complete backup of all databases, all functionality and will be restoring into MySQL then the --opt optional tells mysqldump to include it:

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

[]

Data Recovery

to get back your data

mysql pluto -u pippo -p

--->enter in mysql shell

mysql> source dumpfile.mysql;

this could 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

[]

Automatic Backup

I like to use the 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 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

• MySQL/Backup

Retrieved from ""

: |

---

>
> >