Powershell坑-MySQL数据备份

前言

由于迁移系统, 对MySQL存储的数据进行迁移, 使用MySQL自带的mysqldump命令进行数据迁移.

mysqldump -hlocalhost -uroot -ppass database_name > d:/backupfile.sql

使用mysqldump备份好数据后, 由于数据量较大, 为保障万无一失, 使用navicat进行了额外的增量备份(navicat备份数据, 生成的文件更小).

注: 备份数据, 注意安全冗余.

问题

恢复数据, 使用source命令

d:

source backupfile.sql

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. 
Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''.

-- 添加该参数, 依然还是会出现错误.

I have tried putting --binary-mode in the ini file but it still gives the same error. What should I do? Please help.

问题出现, 出现了编码的异常, 同时还有报 Error 22错误, can't open file.

查询一下, stackflow有相关的问题.

问题_a

问题_b

这里隐藏着powershell的一个潜在坑, powershell默认使用的编码类型是utf-16

The problem comes from the default encoding of powershell is UTF16. To look deeper into this, we can use "file" utility of GNU, and there exists a windows version here.
The output of my dump file is:

注: notepad++, 打开大型的文本, 注意卡顿

在mysql的文档中, 也提及了这个问题

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:

However, UTF-16 is not permitted as a connection character set (see Impermissible Client Character Sets), so the dump file cannot be loaded correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format:

同时也需要注意这个问题, 在powershell执行输出结果到文本时.

进一步延申应该和Windows上编码问题unicode - Why does Windows use UTF-16LE? - Stack Overflow

Windows was one of the first Operating Systems to adopt Unicode. Back then, there was indeed no UTF-8 yet, and UCS-2 was the most common encoding used for Unicode. So Windows' initial Unicode support was based on UCS-2.

By the time Unicode outgrew UCS-2, and UTF-8 and UTF-16 became more popular, it was too late for Windows to change over to UTF-8 without breaking tons of existing code 1, however UTF-16 is backwards compatible with UCS-2, so Microsoft was able to switch to UTF-16 with minimal effort, and little-to-no changes to existing user code.

1: and now, 20-odd years later, in Windows 10, Microsoft is only just starting to really begin to support UTF-8 at the Win32 API layer, but that functionality is still experimental, has to be enabled manually by the user or on a per-application basis via app manifests, and typically requires changes to user code to take advantage of UTF8-enabled APIs rather than UTF16-based APIs.

windows10上utf-8的支持, 该特性还处于beta状态.

解决

根据上述的文档, 调整mysqldump命令的参数.

注: 这里的utf-8和mysql utf8mb4完全一致?.

mysqldump -hlocalhost -uroot -pMySQL@2022 --default-character-set=UTF8 --databases test_logistics --hex-blob --result-file=C:\Users\Lian\Desktop\backupfile_utf_8.sql

--default-character-set=UTF8, 指定字符编码

--hex-blob

Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected data types are BINARY, VARBINARY, BLOB types, BIT, all spatial data types, and other non-binary data types when used with the binary character set.

--result-file

Direct output to the named file. The result file is created and its previous contents overwritten, even if an error occurs while generating the dump.This option should be used on Windows to prevent newline \n characters from being converted to \r\n carriage return/newline sequences.

针对windows的专用命令

其他

powershell和cmd在cd命令上的差异

powershell字符集相关, URL

mysqldump参数: