MYSQL导入导出命令

最新推荐文章于 2024-10-13 21:08:42 发布

rainv

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量2.4k

点赞数

分类专栏： Mysql[工具] 文章标签： mysql user database transactions access download

本文链接：https://blog.csdn.net/rainv/article/details/1541452

版权

Mysql[工具] 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

备份：
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

[hide]

[ edit]

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

[ edit]

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

[ edit]

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.

[ edit]

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.