MySQL mysqldump 和使用特殊字符恢复。？？？？？？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3583691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysqldump and restore with special characters. ??????
提问by Mario Michelli
Locally I do this to dump and move a database, upgrading silverstripe 2.3 to 2.4:
在本地，我这样做是为了转储和移动数据库，将 Silverstripe 2.3 升级到 2.4：
mysqldump --opt --default-character-set=latin1 --skip-set-charset --user=$root -p$password $oldDatabase -r db.sql
iconv -f LATIN1 -t UTF8 db.sql > db_utf.sql
CREATE DATABASE $newDatabase CHARACTER SET utf8 COLLATE utf8_swedish_ci; FLUSH PRIVILEGES; GRANT ALL PRIVILEGES ON $newDatabase . * TO '$newUser'@'localhost'; FLUSH PRIVILEGES;
SET NAMES utf8; SOURCE db_utf.sql;
And it works, but on the server Ubuntu 8.04, with mysql Ver 14.12 Distrib 5.0.51a. I get crazy √∏ charterers instead of ??????.
它可以工作，但在服务器 Ubuntu 8.04 上，使用 mysql Ver 14.12 Distrib 5.0.51a。我变得疯狂 √∏ 租船人而不是 ??????。
Anyone know where I've gone wrong?
有谁知道我哪里出错了？
采纳答案by d1jhoni1b
This worked for me:
这对我有用：
- ssh the server and connect.
- create db dump running
- ssh 服务器并连接。
- 创建数据库转储运行
mysqldump -h my_guid.cloud.database.com -u my_user -p my_database_name -r ~/my_db_backup.sql
mysqldump -h my_guid.cloud.database.com -u my_user -p my_database_name -r ~/my_db_backup.sql
The console will prompt and ask for the password and there you can type it, my pwd had special characters so i was able to run this command with ease
控制台将提示并要求输入密码，您可以在那里输入密码，我的密码有特殊字符，因此我可以轻松运行此命令
回答by user614467
Try to run with the following command:
尝试使用以下命令运行：
mysqldump -u root -p database -r output.sql
instead of redirecting the output with arrow '>'
而不是用箭头“>”重定向输出
回答by Bakly
It took me Two days to find out I had the same problem and solved it when trying to export a database in arabic using mysqldump and each time you open the outputfile in notepad++ its encoding is in ansi and you need it to be utf-8 my code for export and import was as follows it turns out i was right but i was checking the database on the terminal but the terminal doesn't support encoding and i just tried checking it with phpmyadmin and its good don't try to open the file in notepad++ or just try your application directly it will work.
我花了两天才发现我遇到了同样的问题并在尝试使用 mysqldump 以阿拉伯语导出数据库时解决了它，并且每次在记事本 ++ 中打开输出文件时，它的编码都是 ansi，你需要它是 utf-8 my导出和导入的代码如下，结果证明我是对的，但我正在检查终端上的数据库，但终端不支持编码，我只是尝试使用 phpmyadmin 进行检查，这很好，不要尝试打开文件在记事本++中或直接尝试您的应用程序它会起作用。
export command
导出命令
mysqldump -uuser -ppassword --default-character-set=utf8 dbname > outputfile //or even if you use -r instead of > no difference
mysqldump -uuser -ppassword --default-character-set=utf8 dbname > outputfile //or even if you use -r instead of > no difference
import command
mysql -uuser -ppassword --default-character-set=utf8 dbname < outputfille // please take in mind this does override existing database
导入命令
mysql -uuser -ppassword --default-character-set=utf8 dbname < outputfille // please take in mind this does override existing database
回答by Mario Michelli
This fixed the issue for me.
这为我解决了这个问题。
Import the double encoded input.sql
Export it again
mysqldump -h "$DB_HOST -u "$DB_USER" -p"$DB_PASSWORD" --opt --quote-names --skip-set-charset --default-character-set=latin1 "$DB_NAME" > output.sqlImport clean output.sql
导入双重编码的 input.sql
再次导出
mysqldump -h "$DB_HOST -u "$DB_USER" -p"$DB_PASSWORD" --opt --quote-names --skip-set-charset --default-character-set=latin1 "$DB_NAME" > output.sql导入干净的 output.sql
回答by Sean
I succeed as follows:
我成功如下：
mysql --default-character-set=utf8 -u ..
May this will help you.
愿这对你有帮助。
回答by thomas-peter
It's very important to make sure the client is set to UTF8. Confusingly, it's not the same as setting your database to UTF8. Open /etc/my.cnf and make sure you have default-character-set = utf8 under [mysql] not just under [mysqld]
确保客户端设置为 UTF8 非常重要。令人困惑的是，这与将数据库设置为 UTF8 不同。打开 /etc/my.cnf 并确保您在 [mysql] 下有 default-character-set = utf8 而不仅仅是在 [mysqld] 下
Now you should be able to pipe UTF8 dumps directly into the mysql client. I also recommend using the option --hex-blob on the mysqldump command as mysqldump is not perfect.
现在您应该能够将 UTF8 转储直接通过管道传输到 mysql 客户端。我还建议在 mysqldump 命令上使用选项 --hex-blob，因为 mysqldump 并不完美。
回答by Mike
Have you tried it without the iconvstep?
你试过没有iconv步骤吗？
Here's what I use when dumping UTF-8 databases:
这是我在转储 UTF-8 数据库时使用的内容：
mysqldump \
-u $DB_USER -p"$DB_PASS" \
--default-character-set=Latin1 \
--result-file=$DATAFILE
And to restore:
并恢复：
mysql -u $DB_USER -p"$DB_PASS" \
--default-character-set=latin1 < $DATAFILE
回答by unutbu
Perhaps just copy the tables to $newDatabase as latin1. Then, for each table, execute:
也许只是将表复制到 $newDatabase 作为 latin1。然后，对于每个表，执行：
ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_swedish_ci
回答by Erik H?glund
Only way that worked for me was to export the utf-8 tables as latin-1 (character set of file: iso-8859-1) in phpmyadmin.
唯一对我有用的方法是在 phpmyadmin 中将 utf-8 表导出为 latin-1（文件的字符集：iso-8859-1）。
Open the exported file in notepad++ convert to UTF8 (with BOM)
在notepad++中打开导出的文件转换为UTF8（带BOM）
Then upload file and import with SOURCE dump.sql.
然后上传文件并使用 SOURCE dump.sql 导入。
