备份：
C:\Documents and Settings\Acer>mysqldump -h127.0.0.1 -utest -ptest hello>d:\sq_vitamin.sql
导入：
C:\Documents and Settings\Acer>mysql -utest -ptest hello<d:\sq_vit
amin.sql
注意的是 -u和-p后面紧跟着用户名和密码，中间没有空格分隔。
远程备份的时候出现了以下错误，找到了解决方案。增加一个选项--single-transaction,解决了。
即 mysqldump --single-transaction -h127.0.0.1 -utest -ptest hello>d:\sq_vitamin.sql
mysqldump: Got error: 1044: Access denied for user 'test@'%' to database
'test' when using LOCK TABLES
下边有更详细的说明.
http://blog.chinaunix.net/u/17006/showart.php?id=140205
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 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.
发表于 @ 2007年03月26日 13:51:00|评论(loading...)|编辑