分类：MySQL

Windows 使用VSCode远程连接到Linux开发调试MySQL

Posted on by YongJing(敬勇)

Windows 使用VSCode远程连接到Linux开发调试MySQL

1.Linux系统上安装相关开发工具

[root@gbase yum.repos.d]# yum install gdb-gdbserver

[root@gbase yum.repos.d]# yum install gdb

[root@gbase yum.repos.d]# yum install gcc

[root@gbase yum.repos.d]# yum install cmake

2.配置VSCode可以免密登录Linux

2.1在Windows上生成密钥

C:\Users\Administrator>ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (C:\Users\Administrator\.ssh\id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in id_rsa.

Your public key has been saved in id_ras.pub.

The key fingerprint is:

SHA256:2ya72QX0JQBxKTLM+3S1H3FFYnHHrve1t0cSbqXJiqI mysql@gbase

The key’s randomart image is:

+—[RSA 2048]—-+

| o ooo. ++*|

| = …….o+|

| + ……+ |

| . …..oo o|

| oS.. .+ B |

| .o . X +|

| o o..o ++|

| .*… .+|

| E.+o. .+|

+—-[SHA256]—–+

2.2 在Linux服务器上生成密钥

[root@gbase ~]# su – mysql

Last login: Sun Sep 26 09:24:11 CST 2021 on pts/5

[mysql@gbase ~]$ pwd

/home/mysql

[mysql@gbase ~]$ ls -lrt

total 0

drwxrwxr-x. 2 mysql mysql 6 Sep 24 16:19 perl5

[mysql@gbase ~]$ cd ~/.ssh

-bash: cd: /home/mysql/.ssh: No such file or directory

[mysql@gbase ~]$ mkdir ~/.ssh

[mysql@gbase ~]$ cd ~/.ssh

[mysql@gbase .ssh]$ pwd

/home/mysql/.ssh

[mysql@gbase .ssh]$ ls -lrt

total 0

增加root权限：visudo 进入文本后找到root ALL=(ALL) ALL，另起一行，加入admin ALL=(ALL) NOPASSWD:ALL su admin 切换到新用户 再使

用sudo su -切换回root，说明权限正常，进行下一步

[root@gbase ~]# visudo

## Allow root to run any commands anywhere

root ALL=(ALL) ALL

mysql ALL=(ALL) NOPASSWD:ALL

[mysql@gbase ~]$ sudo su –

Last login: Sun Sep 26 11:41:38 CST 2021 on pts/1

[root@gbase ~]# su – mysql

Last login: Sun Sep 26 14:20:50 CST 2021 from 192.168.1.12 on pts/3

[mysql@gbase ~]$

2.3将windows中生成的公钥上传到/home/mysql/.ssh/目录

C:\Users\Administrator>scp C:\Users\Administrator\.ssh\id_rsa.pud mysql@192.168.1.249:/home/mysql/.ssh/authorized_keys

[mysql@gbase ~]$ chmod 700 /home/mysql/.ssh

[mysql@gbase ~]$ chmod 600 /home/mysql/.ssh/authorized_keys

2.4切换到root，关闭root登录 sudo su – vim /etc/ssh/sshd_config 找到#PermitRootLogin yes去掉#把yes改为no systemctl restart sshd 重启服务并生效

[root@gbase .ssh]# vim /etc/ssh/sshd_config

# $OpenBSD: sshd_config,v 1.100 2016/08/15 12:32:04 naddy Exp $

# This is the sshd server system-wide configuration file. See

# sshd_config(5) for more information.

# This sshd was compiled with PATH=/usr/local/bin:/usr/bin

# The strategy used for options in the default sshd_config shipped with

# OpenSSH is to specify options with their default value where

# possible, but leave them commented. Uncommented options override the

# default value.

# If you want to change the port on a SELinux system, you have to tell

# SELinux about this change.

# semanage port -a -t ssh_port_t -p tcp #PORTNUMBER

#

Port 22

#AddressFamily any

#ListenAddress 0.0.0.0

#ListenAddress ::

HostKey /etc/ssh/ssh_host_rsa_key

#HostKey /etc/ssh/ssh_host_dsa_key

HostKey /etc/ssh/ssh_host_ecdsa_key

HostKey /etc/ssh/ssh_host_ed25519_key

# Ciphers and keying

#RekeyLimit default none

# Logging

#SyslogFacility AUTH

SyslogFacility AUTHPRIV

#LogLevel INFO

# Authentication:

#LoginGraceTime 2m

PermitRootLogin no #改成no

#StrictModes yes

#MaxAuthTries 6

#MaxSessions 10

RSAAuthentication yes #要开启

PubkeyAuthentication yes #要开启

# The default is to check both .ssh/authorized_keys and .ssh/authorized_keys2

# but this is overridden so installations will only check .ssh/authorized_keys

AuthorizedKeysFile .ssh/authorized_keys

[root@gbase ~]# service sshd restart

Redirecting to /bin/systemctl restart sshd.service

在windows中测试ssh登录

C:\Users\Administrator>ssh mysql@192.168.1.249

Last login: Sun Sep 26 14:22:54 2021

]ysql@gbase:~[mysql@gbase ~]$ pwd

/home/mysql

2.5 配置vscode通过remote ssh连接Linux的配置文件C:\Users\Administrator\.ssh\config

Host 192.168.1.249

HostName 192.168.1.249

User root

IdentityFile “D:\ssh\vscode”

2.6 通过vscode远程连接Linux服务器出现以下错误

> Waiting for client to transfer server archive…

> Waiting for /home/mysql/.vscode-server/bin/7f6ab5485bbc008386c4386d08766667e1552

> 44e/vscode-scp-done.flag and vscode-server.tar.gz to exist

>

[12:31:20.267] Got request to download on client for {“platform”:”linux”,”arch”:”x64″,”destFolder”:”/home/mysql/.vscode-server/bin/7f6ab5485bbc008386c4386d08766667e155244e”}

[12:31:20.268] Downloading VS Code server locally…

[12:31:20.420] Resolver error: Error: Running the contributed command: ‘_workbench.downloadResource’ failed.

[mysql@gbase /]$ cd /home/mysql

[mysql@gbase ~]$ ls -lrt .vscode-server

total 0

drwxrwxr-x. 3 mysql mysql 54 Sep 26 09:46 bin

[mysql@gbase ~]$ cd .vscode-server

[mysql@gbase .vscode-server]$ ls

bin

[mysql@gbase .vscode-server]$ cd bin

[mysql@gbase bin]$ ls

7f6ab5485bbc008386c4386d08766667e155244e

[mysql@gbase bin]$ cd 7f6ab5485bbc008386c4386d08766667e155244e/

[mysql@gbase 7f6ab5485bbc008386c4386d08766667e155244e]$ ls

vscode-remote-lock.mysql.7f6ab5485bbc008386c4386d08766667e155244e vscode-server.tar.gz

[mysql@gbase 7f6ab5485bbc008386c4386d08766667e155244e]$ ls -lrt

total 0

-rw-rw-r–. 1 mysql mysql 0 Sep 26 11:37 vscode-remote-lock.mysql.7f6ab5485bbc008386c4386d08766667e155244e

-rw-rw-r–. 1 mysql mysql 0 Sep 26 11:37 vscode-server.tar.gz

上面的7f开头的文件夹称为Commit Id，现在利用Commit ID下载远程连接需要的文件。对于Stabe Version（在VS Code报错的的窗口中Ctrl+F

搜索stable，有则为Stable Version）的Remote SSH插件，如下图，在“输出”窗口中Ctrl+F搜索“stable”。

[12:31:14.904] Using commit id “7f6ab5485bbc008386c4386d08766667e155244e” and quality “stable” for server

然后使用这个链接：https://update.code.visualstudio.com/commit:$COMMIT_ID/server-linux-x64/stable下载所需文件，注意链接中的

$COMMIT_ID这几个字符（注意$不要忘记）需要换成自己的，比如我的是 7f6ab5485bbc008386c4386d08766667e155244e，那么此时我通

过https://update.code.visualstudio.com/commit:7f6ab5485bbc008386c4386d08766667e155244e/server-linux-x64/stable就可以下载到

我需要的文件。

对于Insider版本，

通过https://update.code.visualstudio.com/commit:7f6ab5485bbc008386c4386d08766667e155244e/server-linux-x64/insider这个链接下载相

应文件。

下载完毕后，将下载的文件upload到Remote Server的~/.vscode-server/bin/xxx文件夹下（也可以在Remote Server上直接

执行wget https://vscode.cdn.azure.cn/stable/7f6ab5485bbc008386c4386d08766667e155244e/vscode-server-linux-x64.tar.gz即可下载，

这样就不用进行上传操作了），同时删除其他文件，最后再使用tar -xvf vscode-server-linux-x64.tar.gz –strip-components 1解压文件，

sftp> put D:\ssh\vscode-server-linux-x64.tar.gz

Uploading vscode-server-linux-x64.tar.gz to /home/mysql/vscode-server-linux-x64.tar.gz

100% 45296KB 45296KB/s 00:00:00

D:/ssh/vscode-server-linux-x64.tar.gz: 46383314 bytes transferred in 0 seconds (45296 KB/s)

[mysql@gbase 7f6ab5485bbc008386c4386d08766667e155244e]$ tar -xvf vscode-server-linux-x64.tar.gz –strip-components 1

然后使用chmod +x node server.sh为node和server.sh添加可执行权限。

[mysql@gbase 7f6ab5485bbc008386c4386d08766667e155244e]$ chmod +x node server.sh

[mysql@gbase 7f6ab5485bbc008386c4386d08766667e155244e]$ ls -lrt

total 117512

-rwxr-xr-x. 1 mysql mysql 222 Sep 22 20:00 server.sh

-rw-r–r–. 1 mysql mysql 34147 Sep 22 20:00 product.json

-rw-r–r–. 1 mysql mysql 1255 Sep 22 20:00 package.json

-rw-r–r–. 1 mysql mysql 13380 Sep 22 20:00 LICENSE

-rwxr-xr-x. 1 mysql mysql 73873984 Sep 22 20:02 node

drwxr-xr-x. 3 mysql mysql 135 Sep 22 20:02 out

drwxr-xr-x. 3 mysql mysql 33 Sep 22 20:02 bin

drwxr-xr-x. 32 mysql mysql 4096 Sep 22 20:02 extensions

drwxr-xr-x. 78 mysql mysql 4096 Sep 22 20:02 node_modules

-rw-rw-r–. 1 mysql mysql 0 Sep 26 11:37 vscode-remote-lock.mysql.7f6ab5485bbc008386c4386d08766667e155244e

-rw-rw-r–. 1 mysql mysql 0 Sep 26 11:37 vscode-server.tar.gz

-rw-rw-r–. 1 mysql mysql 46383314 Sep 26 11:57 vscode-server-linux-x64.tar.gz

再次使用VS Code尝试链接，这一步会链接成功。

3.vscode在远程远程服务器上依次安装 C/C++、CMake、CMake Tools插件

在使用vscode 远程调试程序时出现以下信息:

Updating C/C++ dependencies…

Downloading package ‘C/C++ language components (Linux / x86_64)’ Failed. Retrying… Failed. Retrying… Failed. Retrying…Waiting 8 seconds… Failed. Retrying…Waiting 15 seconds… Failed. Retrying…Waiting 15 seconds… Failed. Retrying…Waiting 15 seconds… Failed. Retrying…Waiting 15 seconds… Failed. Retrying…Waiting 15 seconds… Failed. Retrying…Waiting 15 seconds… Failed to download https://go.microsoft.com/fwlink/?linkid=2170969

Failed at stage: downloadPackages

Error: getaddrinfo ENOTFOUND go.microsoft.com

at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:67:26)

If you work in an offline environment or repeatedly see this error, try downloading a version of the extension with all the dependencies pre-included from https://github.com/microsoft/vscode-cpptools/releases, then use the “Install from VSIX” command in VS Code to install it.


安装 C/C++ language components插件失败

我们只能手动安装

打开网址https://go.microsoft.com/fwlink/?linkid=2170969下载bin_linupwd

x.zip文件

将bin_linux.zip文件解压

cpptools

cpptools-srv

LICENSE.txt

将这三个文件拷贝到远程Linux服务器的/home/mysql/.vscode-server/extensions/ms-vscode.cpptools-1.6.0/bin目录中

如果原先存在这个文件的话先将其备份。

sftp> put F:\bin_linux\bin\*

Uploading cpptools to /home/mysql/.vscode-server/extensions/ms-vscode.cpptools-1.6.0/bin/cpptools

100% 19544KB 19544KB/s 00:00:00

F:/bin_linux/bin/cpptools: 20014008 bytes transferred in 0 seconds (19544 KB/s)

Uploading cpptools-srv to /home/mysql/.vscode-server/extensions/ms-vscode.cpptools-1.6.0/bin/cpptools-srv

100% 11508KB 11508KB/s 00:00:00

F:/bin_linux/bin/cpptools-srv: 11784664 bytes transferred in 0 seconds (11508 KB/s)

Uploading LICENSE.txt to /home/mysql/.vscode-server/extensions/ms-vscode.cpptools-1.6.0/bin/LICENSE.txt

100% 11KB 11KB/s 00:00:00

F:/bin_linux/bin/LICENSE.txt: 11972 bytes transferred in 0 seconds (11 KB/s)

但是没有用，仍然报错

如是通过vsix来进行安装

github上release好多个版本，我远程看代码的主机是redhat Linux系统，所有下载cpptools-linux.vsix。

先删除我们上传到/home/mysql/.vscode-server/extensions/ms-vscode.cpptools-1.6.0/bin的三个文件

[mysql@gbase bin]$ ls -lrt

total 31188

-rw-rw-r–. 1 mysql mysql 11784664 Aug 23 22:29 cpptools-srv

-rw-rw-r–. 1 mysql mysql 20014008 Aug 23 22:29 cpptools

-rw-rw-r–. 1 mysql mysql 11972 Aug 23 22:34 LICENSE.txt

-rw-rw-r–. 1 mysql mysql 582 Sep 26 12:05 common.json

-rw-rw-r–. 1 mysql mysql 301 Sep 26 12:05 linux.clang.arm.json

-rw-rw-r–. 1 mysql mysql 310 Sep 26 12:05 linux.clang.arm64.json

-rw-rw-r–. 1 mysql mysql 300 Sep 26 12:05 linux.clang.x64.json

-rw-rw-r–. 1 mysql mysql 291 Sep 26 12:05 linux.clang.x86.json

-rw-rw-r–. 1 mysql mysql 301 Sep 26 12:05 linux.gcc.arm.json

-rw-rw-r–. 1 mysql mysql 310 Sep 26 12:05 linux.gcc.arm64.json

-rw-rw-r–. 1 mysql mysql 302 Sep 26 12:05 linux.gcc.x64.json

-rw-rw-r–. 1 mysql mysql 291 Sep 26 12:05 linux.gcc.x86.json

-rw-rw-r–. 1 mysql mysql 284 Sep 26 12:05 macos.clang.arm.json

-rw-rw-r–. 1 mysql mysql 293 Sep 26 12:05 macos.clang.arm64.json

-rw-rw-r–. 1 mysql mysql 283 Sep 26 12:05 macos.clang.x64.json

-rw-rw-r–. 1 mysql mysql 274 Sep 26 12:05 macos.clang.x86.json

-rw-rw-r–. 1 mysql mysql 284 Sep 26 12:05 macos.gcc.arm.json

-rw-rw-r–. 1 mysql mysql 293 Sep 26 12:05 macos.gcc.arm64.json

-rw-rw-r–. 1 mysql mysql 283 Sep 26 12:05 macos.gcc.x64.json

-rw-rw-r–. 1 mysql mysql 274 Sep 26 12:05 macos.gcc.x86.json

drwxrwxr-x. 15 mysql mysql 145 Sep 26 12:05 messages

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.clang.arm.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.clang.arm64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.clang.x64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.clang.x86.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.gcc.arm.json

-rw-rw-r–. 1 mysql mysql 89 Sep 26 12:05 windows.gcc.arm64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.gcc.x64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.gcc.x86.json

-rw-rw-r–. 1 mysql mysql 299 Sep 26 12:05 windows.msvc.arm.json

-rw-rw-r–. 1 mysql mysql 324 Sep 26 12:05 windows.msvc.arm64.json

-rw-rw-r–. 1 mysql mysql 383 Sep 26 12:05 windows.msvc.x64.json

-rw-rw-r–. 1 mysql mysql 359 Sep 26 12:05 windows.msvc.x86.json

[mysql@gbase bin]$ rm -rf cpptools*

[mysql@gbase bin]$ rm -rf LICENSE.txt

[mysql@gbase bin]$ ls -lrt

total 116

-rw-rw-r–. 1 mysql mysql 582 Sep 26 12:05 common.json

-rw-rw-r–. 1 mysql mysql 301 Sep 26 12:05 linux.clang.arm.json

-rw-rw-r–. 1 mysql mysql 310 Sep 26 12:05 linux.clang.arm64.json

-rw-rw-r–. 1 mysql mysql 300 Sep 26 12:05 linux.clang.x64.json

-rw-rw-r–. 1 mysql mysql 291 Sep 26 12:05 linux.clang.x86.json

-rw-rw-r–. 1 mysql mysql 301 Sep 26 12:05 linux.gcc.arm.json

-rw-rw-r–. 1 mysql mysql 310 Sep 26 12:05 linux.gcc.arm64.json

-rw-rw-r–. 1 mysql mysql 302 Sep 26 12:05 linux.gcc.x64.json

-rw-rw-r–. 1 mysql mysql 291 Sep 26 12:05 linux.gcc.x86.json

-rw-rw-r–. 1 mysql mysql 284 Sep 26 12:05 macos.clang.arm.json

-rw-rw-r–. 1 mysql mysql 293 Sep 26 12:05 macos.clang.arm64.json

-rw-rw-r–. 1 mysql mysql 283 Sep 26 12:05 macos.clang.x64.json

-rw-rw-r–. 1 mysql mysql 274 Sep 26 12:05 macos.clang.x86.json

-rw-rw-r–. 1 mysql mysql 284 Sep 26 12:05 macos.gcc.arm.json

-rw-rw-r–. 1 mysql mysql 293 Sep 26 12:05 macos.gcc.arm64.json

-rw-rw-r–. 1 mysql mysql 283 Sep 26 12:05 macos.gcc.x64.json

-rw-rw-r–. 1 mysql mysql 274 Sep 26 12:05 macos.gcc.x86.json

drwxrwxr-x. 15 mysql mysql 145 Sep 26 12:05 messages

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.clang.arm.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.clang.arm64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.clang.x64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.clang.x86.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.gcc.arm.json

-rw-rw-r–. 1 mysql mysql 89 Sep 26 12:05 windows.gcc.arm64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.gcc.x64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 12:05 windows.gcc.x86.json

-rw-rw-r–. 1 mysql mysql 299 Sep 26 12:05 windows.msvc.arm.json

-rw-rw-r–. 1 mysql mysql 324 Sep 26 12:05 windows.msvc.arm64.json

-rw-rw-r–. 1 mysql mysql 383 Sep 26 12:05 windows.msvc.x64.json

-rw-rw-r–. 1 mysql mysql 359 Sep 26 12:05 windows.msvc.x86.json

通过vscode 从vsix来进行安装，选择本地目录D:\ssh\cpptools-linux.vsix文件

[mysql@gbase bin]$ ls -lrt

total 31188

-rw-rw-r–. 1 mysql mysql 582 Sep 26 15:01 common.json

-rw-rw-r–. 1 mysql mysql 20014008 Sep 26 15:01 cpptools

-rw-rw-r–. 1 mysql mysql 11784664 Sep 26 15:01 cpptools-srv

-rw-rw-r–. 1 mysql mysql 11972 Sep 26 15:01 LICENSE.txt

-rw-rw-r–. 1 mysql mysql 301 Sep 26 15:01 linux.clang.arm.json

-rw-rw-r–. 1 mysql mysql 310 Sep 26 15:01 linux.clang.arm64.json

-rw-rw-r–. 1 mysql mysql 300 Sep 26 15:01 linux.clang.x64.json

-rw-rw-r–. 1 mysql mysql 291 Sep 26 15:01 linux.clang.x86.json

-rw-rw-r–. 1 mysql mysql 301 Sep 26 15:01 linux.gcc.arm.json

-rw-rw-r–. 1 mysql mysql 310 Sep 26 15:01 linux.gcc.arm64.json

-rw-rw-r–. 1 mysql mysql 302 Sep 26 15:01 linux.gcc.x64.json

-rw-rw-r–. 1 mysql mysql 291 Sep 26 15:01 linux.gcc.x86.json

-rw-rw-r–. 1 mysql mysql 284 Sep 26 15:01 macos.clang.arm.json

-rw-rw-r–. 1 mysql mysql 293 Sep 26 15:01 macos.clang.arm64.json

-rw-rw-r–. 1 mysql mysql 283 Sep 26 15:01 macos.clang.x64.json

-rw-rw-r–. 1 mysql mysql 274 Sep 26 15:01 macos.clang.x86.json

-rw-rw-r–. 1 mysql mysql 284 Sep 26 15:01 macos.gcc.arm.json

-rw-rw-r–. 1 mysql mysql 293 Sep 26 15:01 macos.gcc.arm64.json

-rw-rw-r–. 1 mysql mysql 283 Sep 26 15:01 macos.gcc.x64.json

-rw-rw-r–. 1 mysql mysql 274 Sep 26 15:01 macos.gcc.x86.json

drwxrwxr-x. 15 mysql mysql 145 Sep 26 15:01 messages

-rw-rw-r–. 1 mysql mysql 87 Sep 26 15:01 windows.clang.arm.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 15:01 windows.clang.arm64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 15:01 windows.clang.x64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 15:01 windows.clang.x86.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 15:01 windows.gcc.arm.json

-rw-rw-r–. 1 mysql mysql 89 Sep 26 15:01 windows.gcc.arm64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 15:01 windows.gcc.x64.json

-rw-rw-r–. 1 mysql mysql 87 Sep 26 15:01 windows.gcc.x86.json

-rw-rw-r–. 1 mysql mysql 299 Sep 26 15:01 windows.msvc.arm.json

-rw-rw-r–. 1 mysql mysql 324 Sep 26 15:01 windows.msvc.arm64.json

-rw-rw-r–. 1 mysql mysql 383 Sep 26 15:01 windows.msvc.x64.json

-rw-rw-r–. 1 mysql mysql 359 Sep 26 15:01 windows.msvc.x86.json

重启vscdoe

4.使用vscode远程编译Linux服务器的MySQL源码，mysql源码在/soft/mysql-5.7.26/目录

这里需要配置Cmake 生成setting.json配置文件 点击CMake Tool的设置按钮，再点击Extention Settings

这里选择Remote [SSH:192.168.1.249]，我这对于Build Directory选择使用默认配置，因为${workspaceFolder}就是/soft/mysql-5.7.26目录，所以$${workspaceFolder}/build也就是/soft/mysql-5.7.26/build目录

所以Source Directory目录选择使用默认的$workspaceFolder}目录

给Configure Args配置以下三个参数

-DWITH_BOOST=/soft/mysql-5.7.26/boost/boost_1_59_0

-DDOWNLOAD_BOOST=1

-DWITH_DEBUG=1

选择编译器

给CMake选择Debug模式

然后进行编译

编译完成后，在Linux服务器上初始化mysql数据库

创建data,etc目录用来分别存放数据文件与配置文件my.cnf

[mysql@gbase build]$ mkdir {data,etc}

[mysql@gbase build]$ cd etc

[mysql@gbase etc]$ vi my.cnf

[mysqld]

basedir=/soft/mysql-5.7.26/build

datadir=/soft/mysql-5.7.26/build/data

bind-address=0.0.0.0

user=mysql

port=3306

log-error=/soft/mysql-5.7.26/build/data/mysql.err

pid-file=/soft/mysql-5.7.26/build/data/mysqld.pid

socket = /soft/mysql-5.7.26/build/data/mysql.sock

character-set-server=utf8mb4

default-storage-engine=INNODB

explicit_defaults_for_timestamp = true

[mysql@gbase build]$ sql/mysqld –defaults-file=etc/my.cnf –initialize-insecure

[mysql@gbase build]$ cd data

[mysql@gbase data]$ ls

auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql mysql.err performance_schema sys

[mysql@gbase data]$ ls -lrt

total 110632

-rw-r—–. 1 mysql mysql 50331648 Sep 26 15:43 ib_logfile1

-rw-r—–. 1 mysql mysql 56 Sep 26 15:43 auto.cnf

-rw-r—–. 1 mysql mysql 1046 Sep 26 15:43 mysql.err

drwxr-x—. 2 mysql mysql 8192 Sep 26 15:43 performance_schema

drwxr-x—. 2 mysql mysql 4096 Sep 26 15:43 mysql

drwxr-x—. 2 mysql mysql 8192 Sep 26 15:43 sys

-rw-r—–. 1 mysql mysql 419 Sep 26 15:43 ib_buffer_pool

-rw-r—–. 1 mysql mysql 50331648 Sep 26 15:43 ib_logfile0

-rw-r—–. 1 mysql mysql 12582912 Sep 26 15:43 ibdata1

[mysql@gbase data]$ more mysql.err

2021-09-26T07:43:43.288066Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)

2021-09-26T07:43:43.288312Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

2021-09-26T07:43:43.289227Z 0 [ERROR] Can’t find error-message file ‘/soft/mysql-5.7.26/build/share/errmsg.sys’. Check error-message file location and ‘lc-messages-dir’ configuration directive.

2021-09-26T07:43:46.058883Z 0 [Warning] InnoDB: New log files created, LSN=45790

2021-09-26T07:43:46.360628Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2021-09-26T07:43:46.558161Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 7b190ba6-1e9d-11ec-93d5-005056a31fca.

2021-09-26T07:43:46.567453Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.

2021-09-26T07:43:46.569018Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the –initialize-insecure option.

[mysql@gbase mysql-5.7.26]$ gcc -v -E -x c++ –

Using built-in specs.

COLLECT_GCC=gcc

Target: x86_64-redhat-linux

Configured with: ../configure –prefix=/usr –mandir=/usr/share/man –infodir=/usr/share/info –with-bugurl=http://bugzilla.redhat.com/bugzilla –enable-bootstrap –enable-shared –enable-threads=posix –enable-checking=release –with-system-zlib –enable-__cxa_atexit –disable-libunwind-exceptions –enable-gnu-unique-object –enable-linker-build-id –with-linker-hash-style=gnu –enable-languages=c,c++,objc,obj-c++,java,fortran,ada,go,lto –enable-plugin –enable-initfini-array –disable-libgcj –with-isl=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/isl-install –with-cloog=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/cloog-install –enable-gnu-indirect-function –with-tune=generic –with-arch_32=x86-64 –build=x86_64-redhat-linux

Thread model: posix

gcc version 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC)

COLLECT_GCC_OPTIONS=’-v’ ‘-E’ ‘-mtune=generic’ ‘-march=x86-64’

/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/cc1plus -E -quiet -v -D_GNU_SOURCE – -mtune=generic -march=x86-64

ignoring nonexistent directory “/usr/lib/gcc/x86_64-redhat-linux/4.8.5/include-fixed”

ignoring nonexistent directory “/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../x86_64-redhat-linux/include”

#include “…” search starts here:

#include < ...> search starts here:

/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../include/c++/4.8.5

/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../include/c++/4.8.5/x86_64-redhat-linux

/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../include/c++/4.8.5/backward

/usr/lib/gcc/x86_64-redhat-linux/4.8.5/include

/usr/local/include

/usr/include

将下面的内容加入到c_cpp_properties.json中的includePath中

/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../include/c++/4.8.5

/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../include/c++/4.8.5/x86_64-redhat-linux

/usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../include/c++/4.8.5/backward

/usr/lib/gcc/x86_64-redhat-linux/4.8.5/include

/usr/local/include

/usr/include

加入后错误信息消失

windows系统vscode远程调试MySQL

Posted on by YongJing(敬勇)

这里介绍如何使用windows上的 vscode远程访问Linux的mysql源码来执行远程调试MySQL

1.在本地windows安装openssh

下载安装包OpenSSH-Win64.zip并解压

进行解压后的目录F:\OpenSSH-Win64\OpenSSH

在cmd命令窗口中执行下面命令进行安装

powershell.exe -ExecutionPolicy Bypass -File install-sshd.ps1

开放22号端口（如果你在windows关闭了防火墙并配置了入站规则可以不执行如下命令，多执行不影响）

netsh advfirewall firewall add rule name=sshd dir=in action=allow protocol=TCP localport=22

配置开机自启sshd服务

sc config sshd start= auto

将F:\OpenSSH-Win64\OpenSSH添加到环境变量path中，免得每次都要切到F:\OpenSSH-Win64\OpenSSH才能使用ssh，启动ssh服务

net start sshd

2.1在Windows上生成密钥

C:\Users\Administrator>ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (C:\Users\Administrator\.ssh\id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in id_rsa.

Your public key has been saved in id_ras.pub.

The key fingerprint is:

SHA256:2ya72QX0JQBxKTLM+3S1H3FFYnHHrve1t0cSbqXJiqI mysql@gbase

The key’s randomart image is:

+—[RSA 2048]—-+

| o ooo. ++*|

| = …….o+|

| + ……+ |

| . …..oo o|

| oS.. .+ B |

| .o . X +|

| o o..o ++|

| .*… .+|

| E.+o. .+|

+—-[SHA256]—–+

2.2 在Linux服务器上生成密钥

[root@gbase ~]# su – mysql

Last login: Sun Sep 26 09:24:11 CST 2021 on pts/5

[mysql@gbase ~]$ pwd

/home/mysql

[mysql@gbase ~]$ ls -lrt

total 0

drwxrwxr-x. 2 mysql mysql 6 Sep 24 16:19 perl5

[mysql@gbase ~]$ cd ~/.ssh

-bash: cd: /home/mysql/.ssh: No such file or directory

[mysql@gbase ~]$ mkdir ~/.ssh

[mysql@gbase ~]$ cd ~/.ssh

[mysql@gbase .ssh]$ pwd

/home/mysql/.ssh

[mysql@gbase .ssh]$ ls -lrt

total 0

增加root权限：visudo 进入文本后找到root ALL=(ALL) ALL，另起一行，加入admin ALL=(ALL) NOPASSWD:ALL su admin 切换到新用户 再使

用sudo su -切换回root，说明权限正常，进行下一步

[root@gbase ~]# visudo

## Allow root to run any commands anywhere

root ALL=(ALL) ALL

mysql ALL=(ALL) NOPASSWD:ALL

[mysql@gbase ~]$ sudo su –

Last login: Sun Sep 26 11:41:38 CST 2021 on pts/1

[root@gbase ~]# su – mysql

Last login: Sun Sep 26 14:20:50 CST 2021 from 192.168.1.12 on pts/3

[mysql@gbase ~]$

2.3将windows中生成的公钥上传到/home/mysql/.ssh/目录

C:\Users\Administrator>scp C:\Users\Administrator\.ssh\id_rsa.pud mysql@192.168.1.249:/home/mysql/.ssh/authorized_keys

[mysql@gbase ~]$ chmod 700 /home/mysql/.ssh

[mysql@gbase ~]$ chmod 600 /home/mysql/.ssh/authorized_keys

2.4切换到root，关闭root登录 sudo su – vim /etc/ssh/sshd_config 找到#PermitRootLogin yes去掉#把yes改为no systemctl restart sshd 重启服务并生效

[root@gbase .ssh]# vim /etc/ssh/sshd_config

# $OpenBSD: sshd_config,v 1.100 2016/08/15 12:32:04 naddy Exp $

# This is the sshd server system-wide configuration file. See

# sshd_config(5) for more information.

# This sshd was compiled with PATH=/usr/local/bin:/usr/bin

# The strategy used for options in the default sshd_config shipped with

# OpenSSH is to specify options with their default value where

# possible, but leave them commented. Uncommented options override the

# default value.

# If you want to change the port on a SELinux system, you have to tell

# SELinux about this change.

# semanage port -a -t ssh_port_t -p tcp #PORTNUMBER

#

Port 22

#AddressFamily any

#ListenAddress 0.0.0.0

#ListenAddress ::

HostKey /etc/ssh/ssh_host_rsa_key

#HostKey /etc/ssh/ssh_host_dsa_key

HostKey /etc/ssh/ssh_host_ecdsa_key

HostKey /etc/ssh/ssh_host_ed25519_key

# Ciphers and keying

#RekeyLimit default none

# Logging

#SyslogFacility AUTH

SyslogFacility AUTHPRIV

#LogLevel INFO

# Authentication:

#LoginGraceTime 2m

PermitRootLogin no #改成no

#StrictModes yes

#MaxAuthTries 6

#MaxSessions 10

RSAAuthentication yes #要开启

PubkeyAuthentication yes #要开启

# The default is to check both .ssh/authorized_keys and .ssh/authorized_keys2

# but this is overridden so installations will only check .ssh/authorized_keys

AuthorizedKeysFile .ssh/authorized_keys

[root@gbase ~]# service sshd restart

Redirecting to /bin/systemctl restart sshd.service

在windows中测试ssh登录

C:\Users\Administrator>ssh mysql@192.168.1.249

Last login: Sun Sep 26 14:22:54 2021

]ysql@gbase:~[mysql@gbase ~]$ pwd

/home/mysql

给vscode安装remote-development插件

在Linux服务器上编译安装mysql(注意编译时需要开启-DWITH_DEBUG=1)并且启动实例

1.Linux系统上安装相关开发工具

[root@gbase yum.repos.d]# yum install gdb-gdbserver

[root@gbase yum.repos.d]# yum install gdb

[root@localhost ~]# yum -y install gcc* gcc-c++ ncurses* ncurses-devel* cmake* bison* libgcrypt* perl* make*

mysql源码存放在/soft/mysql-5.7.26目录

创建相关目录,build用于编译，build/data用来存储数据库文件，build/etc用来存储my.cnf配置文件

[mysql@gbase mysql-5.7.26]$ mkdir -p build/{data,etc}

[mysql@gbase mysql-5.7.26]$ cd build

[mysql@gbase build]$ ls -lrt

total 0

drwxrwxr-x. 2 mysql mysql 6 Sep 27 07:40 data

drwxrwxr-x. 2 mysql mysql 6 Sep 27 07:40 etc

预编译MySQL

[mysql@gbase mysql-5.7.26]$ cmake . -DCMAKE_INSTALL_PREFIX=/soft/mysql-5.7.26/build -DMYSQL_DATADIR=/soft/mysql-5.7.26/build/data -DMYSQL_UNIX_ADDR=/soft/mysql-5.7.26/build/mysql.sock -DWITH_BOOST=/soft/mysql-5.7.26/boost/boost_1_59_0 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DENABLE_DTRACE=0 -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=1

编译源码，这时使用-j 8选项来使用8个进程同时进行编译

[mysql@gbase mysql-5.7.26]$ make -j 8

Building CXX object sql/CMakeFiles/sql.dir/auth/sha2_password_common.cc.o

[100%] Building CXX object sql/CMakeFiles/sql.dir/mysqld_daemon.cc.o

[100%] Generating ../archive_output_directory/mysqlserver_depends.c

Scanning dependencies of target mysqlserver

[100%] Building C object libmysqld/CMakeFiles/mysqlserver.dir/__/archive_output_directory/mysqlserver_depends.c.o

Linking C static library ../archive_output_directory/libmysqld.a

Merging library mysqlserver

Linking CXX static library ../archive_output_directory/libsql.a

[100%] Built target sql

Scanning dependencies of target pfs_connect_attr-t

Scanning dependencies of target mysqld

[100%] Building CXX object sql/CMakeFiles/mysqld.dir/main.cc.o

Linking CXX executable mysqld

[100%] [100%] [100%] Building CXX object storage/perfschema/unittest/CMakeFiles/pfs_connect_attr-t.dir/__/__/__/sql/sql_builtin.cc.o

Building CXX object storage/perfschema/unittest/CMakeFiles/pfs_connect_attr-t.dir/pfs_connect_attr-t.cc.o

Building C object storage/perfschema/unittest/CMakeFiles/pfs_connect_attr-t.dir/__/__/__/mysys/string.c.o

Linking CXX executable pfs_connect_attr-t

[100%] Built target mysqlserver

Scanning dependencies of target mysqltest_embedded

Scanning dependencies of target mysql_embedded

Scanning dependencies of target mysql_client_test_embedded

[100%] [100%] [100%] [100%] Building CXX object libmysqld/examples/CMakeFiles/mysqltest_embedded.dir/__/__/client/mysqltest.cc.o

Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/completion_hash.cc.o

Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/mysql.cc.o

[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/readline.cc.o

Building C object libmysqld/examples/CMakeFiles/mysql_client_test_embedded.dir/__/__/testclients/mysql_client_test.c.o

Linking CXX executable mysql_embedded

Linking CXX executable mysqltest_embedded

Linking CXX executable mysql_client_test_embedded

[100%] Built target mysqltest_embedded

[100%] Built target mysql_embedded

[100%] Built target mysqld

[100%] Built target pfs_connect_attr-t

[100%] Built target mysql_client_test_embedded

安装

[mysql@gbase mysql-5.7.26]$ make install

— Installing: /soft/mysql-5.7.26/build/mysql-test/./t/xa_prepared_binlog_off.test

— Installing: /soft/mysql-5.7.26/build/mysql-test/./t/xml.test

— Installing: /soft/mysql-5.7.26/build/mysql-test/./valgrind.supp

— Installing: /soft/mysql-5.7.26/build/mysql-test/./mtr

— Installing: /soft/mysql-5.7.26/build/mysql-test/./mysql-test-run

— Installing: /soft/mysql-5.7.26/build/mysql-test/./Makefile

— Installing: /soft/mysql-5.7.26/build/mysql-test/./cmake_install.cmake

— Installing: /soft/mysql-5.7.26/build/mysql-test/./CTestTestfile.cmake

— Installing: /soft/mysql-5.7.26/build/./COPYING-test

— Installing: /soft/mysql-5.7.26/build/./README-test

— Up-to-date: /soft/mysql-5.7.26/build/mysql-test/mtr

— Up-to-date: /soft/mysql-5.7.26/build/mysql-test/mysql-test-run

— Installing: /soft/mysql-5.7.26/build/mysql-test/lib/My/SafeProcess/my_safe_process

— Up-to-date: /soft/mysql-5.7.26/build/mysql-test/lib/My/SafeProcess/my_safe_process

— Installing: /soft/mysql-5.7.26/build/mysql-test/lib/My/SafeProcess/Base.pm

— Installing: /soft/mysql-5.7.26/build/support-files/mysqld_multi.server

— Installing: /soft/mysql-5.7.26/build/support-files/mysql-log-rotate

— Installing: /soft/mysql-5.7.26/build/support-files/magic

— Installing: /soft/mysql-5.7.26/build/share/aclocal/mysql.m4

— Installing: /soft/mysql-5.7.26/build/support-files/mysql.server

配置mysql参数,只是设置几个简单的mysql运行参数

[mysql@gbase mysql-5.7.26]$ cd build/etc

[mysql@gbase etc]$ pwd

/soft/mysql-5.7.26/build/etc

[mysql@gbase etc]$ vi my.cnf

[mysqld]

basedir=/soft/mysql-5.7.26/build/

datadir=/soft/mysql-5.7.26/build/data

bind-address=0.0.0.0

user=mysql

port=3306

log-error=/soft/mysql-5.7.26/build/data/mysql.err

pid-file=/soft/mysql-5.7.26/build/data/mysqld.pid

socket = /soft/mysql-5.7.26/build/data/mysql.sock

character-set-server=utf8mb4

default-storage-engine=INNODB

explicit_defaults_for_timestamp = true

[mysql@gbase build]$ bin/mysqld –defaults-file=etc/my.cnf –initialize-insecure

[mysql@gbase build]$ more data/mysql.err

2021-09-27T00:22:49.060540Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)

2021-09-27T00:22:49.060786Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

2021-09-27T00:22:59.734768Z 0 [Warning] InnoDB: New log files created, LSN=45790

2021-09-27T00:23:01.122752Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2021-09-27T00:23:01.272820Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 12e47d2b-1f29-11ec-a401-005056a31fca.

2021-09-27T00:23:01.290654Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.

2021-09-27T00:23:01.293896Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the –initialize-insecure option.

启动mysql

[mysql@gbase build]$ bin/mysqld_safe –user=mysql &

[1] 23685

[mysql@gbase build]$ 2021-09-27T00:30:29.012767Z mysqld_safe Logging to ‘/var/log/mariadb/mariadb.log’.

2021-09-27T00:30:29.097420Z mysqld_safe Starting mysqld daemon with databases from /soft/mysql-5.7.26/build/data

2021-09-27T00:30:30.910622Z mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended

配置自动启动MySQL

[root@gbase log]# cp /soft/mysql-5.7.26/build/support-files/mysql.server /etc/init.d/mysqld

[root@gbase log]# systemctl start mysqld

[root@gbase log]# systemctl status mysqld

鈼[0m mysqld.service – LSB: start and stop MySQL

Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)

Active: active (running) since Mon 2021-09-27 08:33:57 CST; 6s ago

Docs: man:systemd-sysv-generator(8)

Process: 24236 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)

Tasks: 28

CGroup: /system.slice/mysqld.service

鈹溾攢24252 /bin/sh /soft/mysql-5.7.26/build//bin/mysqld_safe –datadir=/soft/mysql-5.7.26/build/data –pid-file=/soft/mysql-5.7.26/build/data/mysqld.pid

鈹斺攢24542 /soft/mysql-5.7.26/build/bin/mysqld –basedir=/soft/mysql-5.7.26/build/ –datadir=/soft/mysql-5.7.26/build/data –plugin-dir=/soft/mysql-5.7.26/build//lib/plugin –user=mysql –log-error=/var/log/mariadb/mariadb.l…

Sep 27 08:33:55 gbase systemd[1]: Starting LSB: start and stop MySQL…

Sep 27 08:33:57 gbase mysqld[24236]: Starting MySQL.. SUCCESS!

Sep 27 08:33:57 gbase systemd[1]: Started LSB: start and stop MySQL.

使用 VSCode 远程访问代码

首先改设置, 左下角 管理（?） -> 设置, 搜 “remote.SSH”, 勾选 remote.SSH.showLoginTerminal

在 VSCode 主界面 ctrl+shift+p 选 Remote.SSH: Connect to host, 输入 root@









然后就可以从侧边栏打开项目路径了, 点击左边的资源管理器，点击打开文件夹，在下图蓝框处输入mysql源码路径（注意不是编译后的运行程序路径是源码解压路径，我的环境源码路径为/soft/mysql-5.7.26，这里我还向下选择sql目录，因为mysql的大多数核心代码在该目录下，如main函数）

安装一些需要的扩展, 如:
? C/C++ IntelliSense, debugging, and code browsing

使用 VSCode 和 gdbserver 远程调试 mysql 代码

在远程 Linux 机器上运行如下代码：

[mysql@gbase ~]$ gdbserver localhost:2333 /soft/mysql-5.7.26/build/bin/mysqld –defaults-file=/soft/mysql-5.7.26/build/etc/my.cnf

Process /soft/mysql-5.7.26/build/bin/mysqld created; pid = 28184

Listening on port 2333

远程 Linux 机器 IP 是 192.168.1.249 gdbserver 的端口设为 2333, 在 VSCode 机器上, 进入菜单 调试 -> 添加配置, 会生成一个配置文件 SSH之后的项目根目录/.vscode/launch.json, 将其修改如下:


{

// Use IntelliSense to learn about possible attributes.

// Hover to view descriptions of existing attributes.

// For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387

“version”: “0.2.0”,

“configurations”: [

{

“name”: “gdb Remote Launch”,

“type”: “cppdbg”,

“request”: “launch”,

“program”: “/soft/mysql-5.7.26/build/bin/mysqld”,

“args”: [],

“stopAtEntry”: true,

“cwd”: “${workspaceFolder}”,

“environment”: [],

“externalConsole”: false,

“MIMode”: “gdb”,

“miDebuggerPath”: “/usr/bin/gdb”,

“miDebuggerArgs”: “gdb”,

“linux”: {

“MIMode”: “gdb”,

“miDebuggerPath”: “/usr/bin/gdb”,

“miDebuggerServerAddress”: “192.168.1.249:2333”,

},

“logging”: {

“moduleLoad”: false,

“engineLogging”: true,

“trace”: false

},

“setupCommands”: [

{

“description”: “Enable pretty-printing for gdb”,

“text”: “-enable-pretty-printing”,

“ignoreFailures”: true

}

]

}

]

}

几个注意的地方:

? 应该是 “request”: “launch”, 不是 “attach”, 此后也并不需要记录进程ID

? 需要填对 “miDebuggerServerAddress”: “192.168.1.248:2333”, 有这个设置才会开启 gdb 远程调试

? “engineLogging”: true 可以看到 gdb 自身的详细消息

? 必须是 “externalConsole”: false 否则报错

? /soft/mysql-5.7.26/build/bin/mysqld 在 gdbserver 和 launch.json 里都要填一次,即mysql编译后的启动程序

之后就可以使用调试功能了, 添加断点, 监视等等, 如图:

MySQL load xml加载数据

Posted on by YongJing(敬勇)

load xml加载数据
load xml语法如下:

LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE [db_name.]tbl_name
[CHARACTER SET charset_name]
[ROWS IDENTIFIED BY '<tagname>']
[IGNORE number {LINES | ROWS}]
[(field_name_or_user_var
[, field_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT},
[, col_name={expr | DEFAULT}] ...]

load xml语句从xml文件中读取数据加载到数据表。file_name必须是文本字符形式且使用单引号将其括起来。在rows identified by选项的tagname子句必须也是文本字符形式并且必须使用<>括号括起来最后还要用单引号将其括起来。

load xml实际上是以xml输出模式来运行mysql客户端的一种补充。为了将表数据写入xml文件，可以调用带有–xml和-e选项的mysql客户端，例如:

[mysql@localhost ~]$ mysql -uroot -pabcd$123 cs --xml -e 'select * from cs.t' >t.xml
mysql: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ ls -lrt
-rw-r--r--. 1 mysql mysql 216 5月 27 16:13 t.xml
[mysql@localhost ~]$ cat t.xml
<?xml version="1.0"?>

<resultset statement="select * from cs.t
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="id">3</field>
<field name="val" xsi:nil="true" />
</row>
</resultset>

为了将这个xml文件中的数据读回到表中，可以使用load xml infile。默认情况下<row>元属就相当于表行，但这可以使用rows identified by子句进行修改。

这个语句支持三种不同的xml格式:
.列名与列值作为属性值:

<row column1="value1" column2="value2" .../>

.列名作为标记且列值作为这些标记的内容:

<row>
<column1>value1</column1>
<column2>value2</column2>
</row>

.列名是<field>标记的name属性并且列值是这些标记的内容:

<row>
<field name='column1'>value1</field>
<field name='column2'>value2</field>
</row>

这种格式也是其实MySQL工具使用的格式，比如mysqldump。

所有三种格式可以同时用于相同的xml文件，导入过程会自动检测每一行的格式并正确解析它。标记是根据标记或属性名称和列名进行匹配的

下面创建一个测试表person，创建语句如下:

mysql> CREATE TABLE person (
-> person_id INT NOT NULL PRIMARY KEY,
-> fname VARCHAR(40) NULL,
-> lname VARCHAR(40) NULL,
-> created TIMESTAMP
-> );
Query OK, 0 rows affected (0.14 sec)

下面假设我们有一个简单的xml文件person.xml，其内容如下(这里使用了三种格式来描述行数据):

<list>
<person person_id="1" fname="Kapek" lname="Sainnouine"/>
<person><person_id>2</person_id><fname>Sajon</fname><lname>Rondela</lname></person>
<person><field name="person_id">3</field><field name="fname">Likame</field><field name="lname">Orrtmons</field></person>
</list>

为了将person.xml中的数据导入person表，可以执行以下语句:

mysql> load xml local infile 'person.xml' into table person rows identified by '<person>';
Query OK, 3 rows affected (0.02 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

rows identified by '<person>'子句意味着xml文件中的每个<person>元属就相当于表中的一行数据。

上面加载语句返回信息显示有3行数据被导入person表，执行简单的select语句来进行验证:

mysql> select * from person;
+-----------+--------+------------+---------+
| person_id | fname | lname | created |
+-----------+--------+------------+---------+
| 1 | Kapek | Sainnouine | NULL |
| 2 | Sajon | Rondela | NULL |
| 3 | Likame | Orrtmons | NULL |
+-----------+--------+------------+---------+
3 rows in set (0.01 sec)

这表明，如前面所述，3种允许的XML格式中的任何一种或所有三种都可以出现在单个文件中，并使用LOAD XML读取。

与刚才所示的导入操作相反–即将MySQL表数据转储到XML文件中–可以使用系统shell中的MySQL客户机来完成，如下所示:

[mysql@localhost ~]$ mysql -uroot -pabcd$123 --xml -e 'select * from cs.person' >person_dump.xml
mysql: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ ls -lrt
-rw-r--r--. 1 mysql mysql 641 5月 27 16:00 person.xml
-rw-r--r--. 1 mysql mysql 641 5月 27 16:52 person_dump.xml
[mysql@localhost ~]$ cat person_dump.xml
<?xml version="1.0"?>

<resultset statement="select * from cs.person
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="person_id">1</field>
<field name="fname">Kapek</field>
<field name="lname">Sainnouine</field>
<field name="created" xsi:nil="true" />
</row>

<row>
<field name="person_id">2</field>
<field name="fname">Sajon</field>
<field name="lname">Rondela</field>
<field name="created" xsi:nil="true" />
</row>

<row>
<field name="person_id">3</field>
<field name="fname">Likame</field>
<field name="lname">Orrtmons</field>
<field name="created" xsi:nil="true" />
</row>

可以通过创建一个person表的副本并导入这个dump文件到新表中来验证这个dump的有效性，例如:

mysql> create table person1 like person;
Query OK, 0 rows affected (0.22 sec)

mysql> select * from person1;
Empty set (0.00 sec)

mysql> load xml local infile 'person_dump.xml' into table person1;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from person1;
+-----------+--------+------------+---------+
| person_id | fname | lname | created |
+-----------+--------+------------+---------+
| 1 | Kapek | Sainnouine | NULL |
| 2 | Sajon | Rondela | NULL |
| 3 | Likame | Orrtmons | NULL |
+-----------+--------+------------+---------+
3 rows in set (0.00 sec)

不要求XML文件中的每个字段都与对应表中的列相匹配。没有相应列的字段将被跳过。可以首先清空person1表并删除created列，然后使用我们刚才使用的LOAD XML语句，如下所示:

mysql> truncate table person1;
Query OK, 0 rows affected (0.08 sec)

mysql> alter table person1 drop column created;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table person1\G
*************************** 1. row ***************************
Table: person1
Create Table: CREATE TABLE `person1` (
`person_id` int(11) NOT NULL,
`fname` varchar(40) DEFAULT NULL,
`lname` varchar(40) DEFAULT NULL,
PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> load xml local infile 'person_dump.xml' into table person1;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from person1;
+-----------+--------+------------+
| person_id | fname | lname |
+-----------+--------+------------+
| 1 | Kapek | Sainnouine |
| 2 | Sajon | Rondela |
| 3 | Likame | Orrtmons |
+-----------+--------+------------+
3 rows in set (0.00 sec)

在XML文件的每一行中给出字段的顺序不影响LOAD XML的操作;字段顺序可以随行变化，不需要与表中相应列的顺序相同。

如前所述，您可以使用一个或多个XML字段的列表(field_name_or_user_var，…)或用户变量(存储相应的字段值以供以后使用)。当您希望将XML文件中的数据插入到名称与XML字段名称不匹配的表列中时，用户变量可能特别有用。为了了解其工作原理，我们首先创建一个名为individual的表，其结构与person表相匹配，但其列的名称不同。

mysql> create table individual (
-> individual_id int not null primary key,
-> name1 varchar(40) null,
-> name2 varchar(40) null,
-> made timestamp
-> );
Query OK, 0 rows affected (0.13 sec)

在这种情况下，不能简单地将XML文件直接加载到表中，因为字段名和列名不匹配:

mysql> load xml local infile 'person_dump.xml' into table cs.individual;
Query OK, 1 row affected, 15 warnings (0.04 sec)
Records: 8 Deleted: 0 Skipped: 7 Warnings: 15

mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 1
*************************** 2. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 3. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 2
*************************** 4. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 5. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 3
*************************** 6. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 7. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 4
*************************** 8. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 9. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 5
*************************** 10. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 11. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 6
*************************** 12. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 13. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 7
*************************** 14. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 15. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 8
*************************** 16. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
16 rows in set (0.00 sec)

mysql> select * from individual;
+---------------+-------+-------+------+
| individual_id | name1 | name2 | made |
+---------------+-------+-------+------+
| 0 | NULL | NULL | NULL |
+---------------+-------+-------+------+
1 row in set (0.00 sec)

这是因为MySQL服务器查找与目标表的列名匹配的字段名。您可以通过将字段值选择到用户变量中来解决这个问题，然后使用SET将目标表的列设置为这些变量的值。您可以在一个语句中执行这两个操作，如下所示:

mysql> load xml local infile 'person_dump.xml' into table cs.individual (@person_id,@fname,@lname,@created)
-> set individual_id=@person_id,name1=@fname,name2=@lname,made=@created;
Query OK, 8 rows affected (0.03 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from individual;
+---------------+--------+------------+------+
| individual_id | name1 | name2 | made |
+---------------+--------+------------+------+
| 1 | Kapek | Sainnouine | NULL |
| 2 | Sajon | Rondela | NULL |
| 3 | Likame | ?rrtmons | NULL |
| 4 | Slar | Manlanth | NULL |
| 5 | Stoma | Milu | NULL |
| 6 | Nirtam | Skl?d | NULL |
| 7 | Sungam | Dulb?d | NULL |
| 8 | Sraref | Encmelt | NULL |
+---------------+--------+------------+------+
8 rows in set (0.00 sec)

用户变量的名称必须与XML文件中相应字段的名称匹配，并添加必要的@前缀，表示它们是变量。用户变量不需要按照与相应字段相同的顺序列出或分配。

使用rows identified by ‘<tagname>’子句，它可以从相同的XML文件中将数据导入有不同定义的表。例如，假设有一个名叫address.xml的文件:

<?xml version="1.0"?>
<list>
<person person_id="1">
<fname>Robert</fname>
<lname>Jones</lname>
<address address_id="1" street="Mill Creek Road" zip="45365" city="Sidney"/>
<address address_id="2" street="Main Street" zip="28681" city="Taylorsville"/>
</person>
<person person_id="2">
<fname>Mary</fname>
<lname>Smith</lname>
<address address_id="3" street="River Road" zip="80239" city="Denver"/>
<!-- <address address_id="4" street="North Street" zip="37920" city="Knoxville"/> -->
</person>
</list>

可以再次使用之前使用的person表，在删除表中记录后显示表结构信息:

mysql> truncate table person;
Query OK, 0 rows affected (0.18 sec)

mysql> show create table person\G
*************************** 1. row ***************************
Table: person
Create Table: CREATE TABLE `person` (
`person_id` int(11) NOT NULL,
`fname` varchar(40) DEFAULT NULL,
`lname` varchar(40) DEFAULT NULL,
`created` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

现在使用下面的create table语句来创建一个名叫address的表:

mysql> CREATE TABLE address (
-> address_id INT NOT NULL PRIMARY KEY,
-> person_id INT NULL,
-> street VARCHAR(40) NULL,
-> zip INT NULL,
-> city VARCHAR(40) NULL,
-> created TIMESTAMP
-> );
Query OK, 0 rows affected (0.13 sec)

为了将数据从XML文件中导入到person表中，执行下面的load xml语句，它是通过<person>元素来指定一行数据:

mysql> load xml local infile 'address.xml' into table person rows identified by '<person>';
Query OK, 2 rows affected (0.13 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

现在来验证被导入的数据:

mysql> select * from person;
+-----------+--------+-------+---------+
| person_id | fname | lname | created |
+-----------+--------+-------+---------+
| 1 | Robert | Jones | NULL |
| 2 | Mary | Smith | NULL |
+-----------+--------+-------+---------+
2 rows in set (0.00 sec)

因为address.xml文件中的<address>元素在表person中没有相关联的列，因此会被跳过。

为了将address.xml文件中的<address>元素导入到address表中，执行下面的语句:

mysql> load xml local infile 'address.xml' into table address rows identified by '<address>';
Query OK, 3 rows affected (0.06 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

现在可以看到<address>元素表示的数据被导入address表了

mysql> select * from address;
+------------+-----------+-----------------+-------+--------------+---------+
| address_id | person_id | street | zip | city | created |
+------------+-----------+-----------------+-------+--------------+---------+
| 1 | 1 | Mill Creek Road | 45365 | Sidney | NULL |
| 2 | 1 | Main Street | 28681 | Taylorsville | NULL |
| 3 | 2 | River Road | 80239 | Denver | NULL |
+------------+-----------+-----------------+-------+--------------+---------+
3 rows in set (0.00 sec)

XML文件中被注释掉的<address>元素所表示的数据没有被导入。然而，因为在address表中有person_id列，因此每个<address>元素的父元素<person>的person_id属性值被导入了address表

安全考虑
与LOAD DATA语句一样，将XML文件从客户机主机传输到服务器主机是由MySQL服务器发起的。理论上，可以构建一个打过补丁的服务器，它将告诉客户机程序传输服务器选择的文件，而不是客户机在LOAD XML语句中命名的文件。这样的服务器可以访问客户机主机上客户机用户具有读访问权的任何文件。

在Web环境中，客户机通常从Web服务器连接到MySQL。可以对MySQL服务器运行任何命令的用户可以使用LOAD XML LOCAL读取Web服务器进程具有读访问权的任何文件。在这个环境中，与MySQL服务器相关的客户机实际上是Web服务器，而不是由连接到Web服务器的用户运行的远程程序

通过使用–local-infile=0或–local-infile=OFF启动服务器，可以禁止从客户机加载XML文件。当启动mysql客户端时，也可以使用这个选项来在客户端会话期间禁用LOAD XML

为了防止客户端从服务器上加载XML文件，不要将FILE特权授予相应的MySQL用户帐户，如果客户端用户帐户已经拥有该特权，则取消该特权。

MySQL通过通用列索引来提供一个JSON列索引

Posted on by YongJing(敬勇)

通过通用列索引来提供一个JSON列索引,不能直接对JSON列进行索引。要创建间接引用此类列的索引，可以定义一个生成的列，提取应该索引的信息，然后在生成的列上创建索引，如本例所示

mysql> CREATE TABLE jemp (
    -> c JSON,
    -> g INT GENERATED ALWAYS AS (c->"$.id"),
    -> INDEX i (g)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO jemp (c) VALUES
    -> ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
    -> ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name  FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name  FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`mysql`.`jemp`.`c`,'$.name')) AS `name` from `mysql`.`jemp` where (`mysql`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

我们已经包装了本例中最后一条语句的输出，以适应查看区域。

在MySQL 5.7.9及以后的版本中支持->操作符。从MySQL 5.7.13开始支持->>操作符

当对一个select或其它包含一个或多个使用->或->>操作符表达式的SQL语句时使用explain时，这些表达式将被翻译成等价的json_extract()和(如果需要)json_unquote()代替，例如下面的explain语句使用show warnings立即输出结果:

mysql> EXPLAIN SELECT c->>"$.name"  FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`mysql`.`jemp`.`c`,'$.name')) AS `c->>"$.name"` from `mysql`.`jemp` where (`mysql`.`jemp`.`g` > 2) order by json_extract(`mysql`.`jemp`.`c`,'$.name')
1 row in set (0.00 sec)

可以看到->和->>操作符使用json_extract()和json_unquote()函数来描述。这种技术可以被用来为那些不能被直接索引的间接引用列提供索引 ，比如geometry列。

MySQL InnoDB搜索索引的Stopwords

Posted on by YongJing(敬勇)

InnoDB搜索索引的Stopwords
InnoDB的默认禁止词列表相对较短，因为来自技术、文学和其他来源的文档经常使用短词作为关键字或重要短语。例如，你可能搜索“是”或“不是”，并期望得到一个合理的结果，而不是让所有这些词都被忽略
InnoDB默认的stopword列表可以通过查询INFORMATION_SCHEMA查看。INNODB_FT_DEFAULT_STOPWORD表。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+
36 rows in set (0.01 sec)

为了对所有InnoDB表定义了一个自定义的stopword列表，那么使用与innodb_ft_default_stopword表相同的结构来定义你自定义的stopword表，然后向表中插入stopwords，并且在创建全文索引之前以db_name/table_name的形式设置innodb_ft_server_stopword_table选项的值。自定义的stopword表必须有一个varchar类型的value列。下面的例子演示了如何为innodb创建一个新的全局stopword表。

mysql> CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;
Query OK, 0 rows affected (0.21 sec)


mysql> INSERT INTO my_stopwords(value) VALUES ('Ishmael');
Query OK, 1 row affected (0.12 sec)


mysql> CREATE TABLE opening_lines (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> opening_line TEXT(500),
    -> author VARCHAR(200),
    -> title VARCHAR(200)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)


mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
    -> ('Call me Ishmael.','Herman Melville','Moby-Dick'),
    -> ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
    -> ('I am an invisible man.','Ralph Ellison','Invisible Man'),
    -> ('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
    -> ('It was love at first sight.','Joseph Heller','Catch-22'),
    -> ('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
    -> ('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
    -> ('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.04 sec)
Records: 8  Duplicates: 0  Warnings: 0



mysql> SET GLOBAL innodb_ft_server_stopword_table = 'mysql/my_stopwords';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 1

默认情况下，长度小于3个字符或大于84个字符的单词不会出现在InnoDB全文搜索索引中。最大和最小字长值可以通过innodb_ft_max_token_size和innodb_ft_min_token_size变量进行配置。这种默认行为不适用于ngram解析器插件。ngram令牌大小由
ngram_token_size选项定义。

通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE中的单词，验证指定的stopword (‘Ishmael’)没有出现。

mysql> SET GLOBAL innodb_ft_aux_table='mysql/opening_lines';
Query OK, 0 rows affected (0.01 sec)


mysql> SELECT word FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+-----------+
| word      |
+-----------+
| across    |
| all       |
| burn      |
| buy       |
| call      |
| comes     |
| dalloway  |
| first     |
| flowers   |
| happened  |
| herself   |
| invisible |
| less      |
| love      |
| man       |
| more      |
| mrs       |
| now       |
| now       |
| now       |
| pleasure  |
| said      |
| screaming |
| she       |
| sight     |
| sky       |
| the       |
| the       |
| this      |
| was       |
| was       |
| when      |
| where     |
| who       |
| would     |
+-----------+
35 rows in set (0.00 sec)

要在一个表一个表的基础上创建stopword列表，需要创建其他stopword表，并在创建全文索引之前使用innodb_ft_user_stopword_table选项指定想要使用的stopword表。

MySQL 全文搜索与查询扩展

Posted on by YongJing(敬勇)

全文搜索与查询扩展
全文搜索支持查询扩展(特别是它的变体盲查询扩展)。当搜索短语太短时，这通常很有用，因为这通常意味着用户依赖于全文搜索引擎所缺乏的隐含知识。例如，用户搜索数据库可能意味着MySQL、Oracle、DB2和RDBMS都是应与数据库匹配并应返回的短语。这是隐含知识。

盲查询扩展(也称为自动关联反馈)是通过添加查询扩展或以自然语言模式在搜索短语之后添加查询扩展来实现的。它的工作原理是执行两次搜索，其中第二次搜索的搜索短语是原始搜索短语与第一次搜索中几个相关度最高的文档相连接。因此，如果其中一个文档包含单词”database”和单词”MySQL”，那么第二次搜索将找到包含单词”MySQL”的文档，即使它们不包含单词”database”。下面的示例显示了这种差异:

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+------------------------------+-------------------------------+
| id | title                        | body                          |
+----+------------------------------+-------------------------------+
|  6 | Database, Database, Database | database database database    |
|  3 | Optimizing Your Database     | In this database tutorial ... |
|  1 | MySQL Tutorial               | This database tutorial ...    |
+----+------------------------------+-------------------------------+
3 rows in set (0.01 sec)


mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY EXPANSION);
+----+------------------------------+-------------------------------------+
| id | title                        | body                                |
+----+------------------------------+-------------------------------------+
|  3 | Optimizing Your Database     | In this database tutorial ...       |
|  6 | Database, Database, Database | database database database          |
|  1 | MySQL Tutorial               | This database tutorial ...          |
|  5 | MySQL Security               | When configured properly, MySQL ... |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     |
|  2 | How To Use MySQL             | After you went through a ...        |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |
+----+------------------------------+-------------------------------------+
8 rows in set (0.01 sec)

另一个例子是搜索Georges Simenon写的关于Maigret的书，当用户不知道如何拼写Maigret时。对麦格里和不情愿证人的搜索发现，只有麦格里和不情愿证人没有查询扩展。带有查询扩展的搜索在第二次搜索时发现所有带有单词Maigret的书籍。因为盲查询扩展会通过返回不相关的文档而显著增加噪声，所以只在搜索短语很短的时候使用盲查询扩展。

MySQL单词搜索相关度排名

Posted on by YongJing(敬勇)

一个单词搜索的相关度排名,这个例子演示了一个单词搜索的相关度排名计算。

mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.56 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','This database tutorial ...'),
    -> ("How To Use MySQL",'After you went through a ...'),
    -> ('Optimizing Your Database','In this database tutorial ...'),
    -> ('MySQL vs. YourSQL','When comparing databases ...'),
    -> ('MySQL Security','When configured properly, MySQL ...'),
    -> ('Database, Database, Database','database database database'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..');
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0



mysql> SELECT id, title, body, MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) AS score FROM articles ORDER BY score DESC;
+----+------------------------------+-------------------------------------+---------------------+
| id | title                        | body                                | score               |
+----+------------------------------+-------------------------------------+---------------------+
|  6 | Database, Database, Database | database database database          |  1.0886961221694946 |
|  3 | Optimizing Your Database     | In this database tutorial ...       | 0.36289870738983154 |
|  1 | MySQL Tutorial               | This database tutorial ...          | 0.18144935369491577 |
|  2 | How To Use MySQL             | After you went through a ...        |                   0 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        |                   0 |
|  5 | MySQL Security               | When configured properly, MySQL ... |                   0 |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |                   0 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     |                   0 |
+----+------------------------------+-------------------------------------+---------------------+
8 rows in set (0.00 sec)

总共有8条记录，其中3条与数据库搜索词匹配。第一条记录(id 6)包含搜索词6次，相关度排名为1.0886961221694946。这个排名值计算使用TF的价值6(数据库搜索词出现6次记录id 6)和IDF值为0.42596873216370745,计算如下(8是记录的总数和3是包含搜索词的记录数量)

${IDF} = log10( 8 / 3 ) = 0.42596873216370745

mysql> select log10( 8 / 3 ) ;
+---------------------+
| log10( 8 / 3 )      |
+---------------------+
| 0.42596873216370745 |
+---------------------+
1 row in set (0.00 sec)

然后将TF和IDF值输入到排名公式中

${rank} = ${TF} * ${IDF} * ${IDF}

在MySQL命令行客户端执行计算将返回一个排名值1.088696164686938。

mysql> SELECT 6*log10(8/3)*log10(8/3);
+-------------------------+
| 6*log10(8/3)*log10(8/3) |
+-------------------------+
|       1.088696164686938 |
+-------------------------+
1 row in set (0.00 sec)

您可能会注意到SELECT … MATCH … AGAINST语句和MySQL命令行客户端所计算的排名值有差别(1.0886961221694946对1.088696164686938)。区别在于InnoDB内部是如何执行整数和浮点数/双精度类型转换的(以及相关的精度和四舍五的决定)，以及它们在其他地方是如何执行的，比如在MySQL命令行客户端或其他类型的计算器中。

多词搜索的相关度排名
这个示例演示了基于前面示例中使用的articles表和数据计算多单词全文搜索的相关度排名。

如果你搜索的是一个以上的单词，那么相关度排名值就是每个单词相关度排名值的总和，如下公式所示:

${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}

执行两个搜索词(‘mysql tutorial’)搜索将返回以下结果:

mysql> SELECT id, title, body, MATCH (title,body) AGAINST ('mysql tutorial' IN BOOLEAN MODE) AS score FROM articles ORDER BY score DESC;
+----+------------------------------+-------------------------------------+----------------------+
| id | title                        | body                                | score                |
+----+------------------------------+-------------------------------------+----------------------+
|  1 | MySQL Tutorial               | This database tutorial ...          |   0.7405621409416199 |
|  3 | Optimizing Your Database     | In this database tutorial ...       |   0.3624762296676636 |
|  5 | MySQL Security               | When configured properly, MySQL ... | 0.031219376251101494 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     | 0.031219376251101494 |
|  2 | How To Use MySQL             | After you went through a ...        | 0.015609688125550747 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        | 0.015609688125550747 |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... | 0.015609688125550747 |
|  6 | Database, Database, Database | database database database          |                    0 |
+----+------------------------------+-------------------------------------+----------------------+
8 rows in set (0.00 sec)

在第一条记录(id 1)中，“mysql”出现一次，“tutorial”出现两次。“mysql”有六条匹配记录，“tutorial”有两条匹配记录。当将这些值插入到用于多个单词搜索的排名公式中时，MySQL命令行客户端返回预期的排名值

mysql> SELECT (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2));
+-------------------------------------------------------+
| (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2)) |
+-------------------------------------------------------+
|                                    0.7405621541938003 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

与单个单词搜索一样，使用select … match … against语句和MySQL命令行工具执行的结果有差别。

MySQL 生成列索引

Posted on by YongJing(敬勇)

MySQL支持在生成列上创建索引，例如:

mysql> create table t1(f1 int,gc int as (f1+1) stored,index(gc));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1(f1) values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| f1   | gc   |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
|    4 |    5 |
|    5 |    6 |
|    6 |    7 |
|    7 |    8 |
|    8 |    9 |
|    9 |   10 |
|   10 |   11 |
+------+------+
10 rows in set (0.00 sec)

生成列gc，它的定义为表达式f1+1。这个列gc也创建了索引因此在生成执行计划时优化器可以使用这个索引。下面的查询where条件引用了列gc并且优化器会考虑使用这个索引是否可以生成更有效的执行计划:

mysql> explain select * from t1 where gc>9\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

优化器可以在生成的列上使用索引来生成执行计划，即使在查询中没有按名称直接引用这些列的情况下也是如此。如果where，order by 或group by子句引用的表达式与某些被索引的生成列相匹配就会出现这种情况。下面的查询没有直接引用生成列gc，但使用的表达式与生成列gc的定义匹配:

mysql> explain select * from t1 where f1+1>9\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

优化器识别出了表达式f1+1与生成列gc相匹配并且gc列创建了索引，所以优化器在生成执行计划时考虑使用了该索引。

实际上，优化器已经用与表达式匹配的生成列的名称gc替换了表达式f1 + 1。这在执行show warnings命令所显示的扩展解释信息中可以很明显地看到重写查询语句确实用生成列替换了表达式。

mysql> show warnings\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `mysql`.`t1`.`f1` AS `f1`,`mysql`.`t1`.`gc` AS `gc` from `mysql`.`t1` where (`mysql`.`t1`.`gc` > 9)
1 row in set (0.00 sec)

优化器使用生成列索引有以下限制和条件:
.对于一个查询语句的表达式是否成生成列定义相匹配，那么表达式必须完全相同并且有相同的结果类型。例如，如果生成列表达式是f1+1，如果查询使用1+f1或者如查f1+1(一个整数表达式)与字符进行比较而不是数字那么优化器将不会认为是匹配的。

.对于这些操作:=,< ,<=,>,>=,between和in优化器处理是，对于不是between和in之外的其它操作符而言，任何一个操作数都可以被匹配的生成列所替换。对于between和in操作符，只有第一个参数可以被匹配的生成列替换，并且其它的参数必须要有相同的结果类型。between和in操作符目前还不支持对JSON的调用。

.生成列必须定义为至少包含一个函数调用或前一项中提到的一个运算符的表达式。表达式不能只是简单的引用其它列。例如，gc int as (f1) stored，这个生成列的定义只是简单的引用了一个列，因此在生成列gc上的索引不会被优化器考虑。

.为了比较字符串与调用JSON函数返回带引号的字符串的被索引的生成列，JSON_UNQUOTE()函数需要在生成列定义中用来删除函数返回值的引号(对于直接比较字符串与函数值，JSON比较器句柄会删了引号，但在索引查找时不会发生)。

.如果优化器未能选择所需的索引，则可以使用索引提示强制优化器做出不同的选择。

MySQL InnoDB的索引扩展

Posted on by YongJing(敬勇)

索引扩展,InnoDB通过将主键列附加到每个辅助索引中来自动扩展该索引。创建如下表结构:

mysql> CREATE TABLE t1 (
    -> i1 INT NOT NULL DEFAULT 0,
    -> i2 INT NOT NULL DEFAULT 0,
    -> d DATE DEFAULT NULL,
    -> PRIMARY KEY (i1, i2),
    -> INDEX k_d (d)
    -> ) ENGINE = InnoDB;

Query OK, 0 rows affected (0.14 sec)

表t1在列(i1,i2)上定义了主键。同时也在列(d)上定义了一个辅助索引，但InnoDB扩展了这个索引并且将它视为(d,i1,i2)来处理。

在决定如何使用以及是否使用该索引时，优化器会考虑扩展辅助索引的主键列。这可以产生更高效的查询执行计划和更好的性能。

优化器可以使用扩展的二级索引来进行ref、range和index_merge索引访问，进行松散索引扫描，进行连接和排序优化，以及进行MIN()/MAX()优化。

下面的示例将显示优化器是否使用扩展辅助索引来影响执行计划 向表t1插入以下数据:


mysql> INSERT INTO t1 VALUES (1, 1, '1998-01-01'), (1, 2, '1999-01-01'), (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
    ->(1, 5, '2002-01-01'), (2, 1, '1998-01-01'), (2, 2, '1999-01-01'), (2, 3, '2000-01-01'),  (2, 4, '2001-01-01'),
    ->(2, 5, '2002-01-01'), (3, 1, '1998-01-01'), (3, 2, '1999-01-01'), (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
    ->(3, 5, '2002-01-01'), (4, 1, '1998-01-01'), (4, 2, '1999-01-01'), (4, 3, '2000-01-01'), (4, 4, '2001-01-01'),
    ->(4, 5, '2002-01-01'), (5, 1, '1998-01-01'), (5, 2, '1999-01-01'),  (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
    ->(5, 5, '2002-01-01');
Query OK, 25 rows affected (0.05 sec)
Records: 25  Duplicates: 0  Warnings: 0

假设执行下面的查询:

SET optimizer_switch = 'use_index_extensions=off';
explain select count(*) from t1 where i1=3 and d= '2000-01-01' ;

在这种情况下，优化器不能使用主键，因为主键包含列(i1、i2)，并且查询没有引用i2。相反，优化器可以使用列(d)上的辅助索引k_d，执行计划取决于是否使用扩展索引。

当优化器不考虑索引扩展时，它将索引k_d仅视为(d)

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,k_d
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

当优化器考虑到索引扩展时，它将k_d视为(d, i1, i2)。在这种情况下，它可以使用最左边的索引前缀(d, i1)来生成更好的执行计划

mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

在这两种情况下，key表示优化器将使用辅助索引k_d，但是EXPLAIN输出显示了使用扩展索引所带来的这些改进:
.key_len从4字节变成了8字节，指示键查找使用了列d和i1，不仅仅是d。
.ref的值从const变成了const,const,因为键查找使用两个键的列而不是一个。
.rows:从5减到1，指示InnoDB将会检查更少的行来生成查询结果。
.Extra值从Using where;Using index变成了Using index。这意味着查询记录只需要使用索引而不用查询数据行记录。

可以使用show status来查看优化器在使用与不使用扩展索引时的差异:

mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.03 sec)

上面的flush table和flush status语句用来清除表的缓存和清除状数据统计数据。

不使用索引扩展时show status产生的结果如下:

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

使用索引扩展时，show status产生的结果如下，其中handler_read_next的值从5减到1，指示使用这个索引更有效率:

mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> flush status
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

系统变量optimizer_switch的use_index_extensions标志允许优化器在决定如何使用InnoDB表的辅助索引时使不使用主键列。默认情况下，use_index_extensions是启用的。为了检查禁用索引扩展是否可以提高性能可以执行以下语句:

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)

使用mysqldump以文本分隔格式来dump数据

Posted on by YongJing(敬勇)

这里将介绍如何使用mysqldump来创建文本分隔格式的dump文件。在调用mysqldump时使用–tab=dir_name选项时，使用dir_name作为输出目录并且在输出目录是为每个表生成两个文件。表名是这些文件的基础名称。对表t，文件名为t.sql和t.txt。其中.sql文件包含的是表的 create table语句。.txt文件包含的是表数据，每一条记录一行。下面的命令将数据库mysql的内容dump到/mysqldata/tmp目录中

在用mysqldump备份时候遇到1290的错误

从提示看到是因为mysql服务启用了--secure-file-priv，所以才无法执行。这个选项或系统变量用被来限制通过load data和select ... into outfile语句和load_file()函数所执行导入和导出操作的数据量。


secure_file_priv有以下设置:

.如果为空，变量不生效。没有安全设置。

.如果设置为一个目录名，服务器对导入和导出操作只对这个目录中的文件生效，但目录必须存在，服务器不会创建目录。

.如果设置NULL，服务器禁止导入和导出操作。


查看数据库当前设置:


mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)



看到secure_file_priv当前设置为NULL,说明限制导入和导出操作。


查看配置文件my.cnf发现没有设置secure_file_priv参数，那么这个参数默认应该是为NULL。


[mysql@localhost mysql]$ cat my.cnf


[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=*
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqlsoft/mysql/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
innodb_flush_method=O_DIRECT
binlog_format = mixed
log-bin=/mysqldata/mysql/binlog
max_binlog_size = 100m
binlog_cache_size = 4m
server-id=1




修改配置文件my.cnf参加secure_file_priv=


[mysql@localhost mysql]$ vi my.cnf
.....
secure-file-priv=



重启mysql数据库


[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!




再次检查secure_file_priv参数设置


mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.00 sec)



再执行导出操作成功


[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.



数据库服务器会生成包含数据内容的.txt文件，因此它是属于运行数据库服务的系统账号。服务器使用select … into outfile来写这个文件，因此你必须有file权限才能执行这个操作，如果一个特定的.txt文件已经存在的话会出错。


数据库服务器为被dump的表发送create定义语句来mysqldump，它将被写入到.sql文件中。因此这些文件属于执行mysqldump命令的用户。


–tab选项最好只在本地服务器上执行dump。如果将它用于远程服务器，–tab选项所指定的目录必须在本地服务器和远程服务器上都要存在，燕且.txt文件会被服务器写入远程目录(数据库服务器所在主机)，而.sql文件将被mysqldump写入本地目录(客户端所在主机)。


对于mysqldump –tab，服务器默认情况下将表数据写入.txt文件，每行表记录一行，列值之间使用制表符，列值周围不使用引号，换行符作为行结束符。


为了能在写入数据文件时使用不同的格式，mysqldump支持以下选项:

.–fields-terminated-by=str

列值分隔符(默认为:tab制表符)


.–fields-enclosed-by=char

将列值括在其中的字符(默认为:没有)


.–fields-optionally-enclosed-by=char

用来括起非数值列值的字符(默认为:没有)


.–fields-escaped-by=char

用于转义特殊字符的字符(默认为:没有转义)

.–lines-terminated-by=str

行终止符(默认为:新行)


根据你所为这些选项指定的值，为了让命令解释器能正确处理可能需要在命令行中为这些值使用引号或转义。另外可以以十六进制格式来指定。假设你想用双引号引用列值。为了达到这个目的应该为–fields-enclosed-by选项指定双引号。但双引号通常对于命令解释器有特定含义并且必须特定对待。例如，在Unix中，可以使用引号来指定双引号:

–fields-enclosed-by='”‘


在任何平台中，可以指定十六进制值:

–fields-enclosed-by=0x22


同时使用几个数据格式化选项是很常见的。例如，为了使用通过回车/换行符对(\r\n)作为行终止符的逗号分隔值格式来dump表，可以执行以下命令:


[mysql@localhost tmp]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp --fields-terminated-by=, --fields-enclosed-by='"'  --lines-terminated-by=0x0d0a mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[mysql@localhost tmp]$ ls -lrt
总用量 3128
-rw-r--r--. 1 mysql mysql    1871 7月   9 17:54 rewrite_rules.sql
-rw-r--r--. 1 mysql mysql    1876 7月  10 15:37 columns_priv.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 columns_priv.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 count.txt
-rw-r--r--. 1 mysql mysql    1298 7月  10 15:37 count.sql
-rw-rw-rw-. 1 mysql mysql       7 7月  10 15:37 cs.txt
-rw-r--r--. 1 mysql mysql    1359 7月  10 15:37 cs.sql
-rw-r--r--. 1 mysql mysql    2917 7月  10 15:37 db.sql
-rw-rw-rw-. 1 mysql mysql     198 7月  10 15:37 db.txt
-rw-rw-rw-. 1 mysql mysql     114 7月  10 15:37 engine_cost.txt
-rw-r--r--. 1 mysql mysql    1630 7月  10 15:37 engine_cost.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 event.txt
-rw-r--r--. 1 mysql mysql    3350 7月  10 15:37 event.sql
-rw-r--r--. 1 mysql mysql    1557 7月  10 15:37 func.sql
-rw-rw-rw-. 1 mysql mysql     398 7月  10 15:37 func.txt
-rw-r--r--. 1 mysql mysql    1578 7月  10 15:37 gtid_executed.sql
-rw-rw-rw-. 1 mysql mysql     964 7月  10 15:37 help_category.txt
-rw-r--r--. 1 mysql mysql    1563 7月  10 15:37 help_category.sql
-rw-r--r--. 1 mysql mysql    1471 7月  10 15:37 help_keyword.sql
-rw-rw-rw-. 1 mysql mysql    9748 7月  10 15:37 help_keyword.txt
-rw-r--r--. 1 mysql mysql    1486 7月  10 15:37 help_relation.sql
-rw-rw-rw-. 1 mysql mysql   10771 7月  10 15:37 help_relation.txt
-rw-r--r--. 1 mysql mysql    1592 7月  10 15:37 help_topic.sql
-rw-rw-rw-. 1 mysql mysql  712538 7月  10 15:37 help_topic.txt
-rw-rw-rw-. 1 mysql mysql      32 7月  10 15:37 imptest.txt
-rw-r--r--. 1 mysql mysql    1337 7月  10 15:37 imptest.sql
-rw-r--r--. 1 mysql mysql    1879 7月  10 15:37 innodb_index_stats.sql
-rw-rw-rw-. 1 mysql mysql    2647 7月  10 15:37 innodb_index_stats.txt
-rw-rw-rw-. 1 mysql mysql     395 7月  10 15:37 innodb_table_stats.txt
-rw-r--r--. 1 mysql mysql    1745 7月  10 15:37 innodb_table_stats.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 ndb_binlog_index.txt
-rw-r--r--. 1 mysql mysql    1842 7月  10 15:37 ndb_binlog_index.sql
-rw-rw-rw-. 1 mysql mysql      76 7月  10 15:37 person.txt
-rw-r--r--. 1 mysql mysql    1571 7月  10 15:37 person.sql
-rw-r--r--. 1 mysql mysql    1420 7月  10 15:37 plugin.sql
-rw-rw-rw-. 1 mysql mysql      53 7月  10 15:37 plugin.txt
-rw-r--r--. 1 mysql mysql    3067 7月  10 15:37 proc.sql
-rw-rw-rw-. 1 mysql mysql     806 7月  10 15:37 proc.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 procs_priv.txt
-rw-r--r--. 1 mysql mysql    1970 7月  10 15:37 procs_priv.sql
-rw-rw-rw-. 1 mysql mysql      60 7月  10 15:37 proxies_priv.txt
-rw-r--r--. 1 mysql mysql    1862 7月  10 15:37 proxies_priv.sql
-rw-rw-rw-. 1 mysql mysql     297 7月  10 15:37 server_cost.txt
-rw-r--r--. 1 mysql mysql    1530 7月  10 15:37 server_cost.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 servers.txt
-rw-r--r--. 1 mysql mysql    1730 7月  10 15:37 servers.sql
-rw-r--r--. 1 mysql mysql    4150 7月  10 15:37 slave_master_info.sql
-rw-r--r--. 1 mysql mysql    2406 7月  10 15:37 slave_relay_log_info.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 slave_worker_info.txt
-rw-r--r--. 1 mysql mysql    2221 7月  10 15:37 slave_worker_info.sql
-rw-r--r--. 1 mysql mysql    1342 7月  10 15:37 t.sql
-rw-rw-rw-. 1 mysql mysql      52 7月  10 15:37 t.txt
-rw-rw-rw-. 1 mysql mysql       3 7月  10 15:37 t1.txt
-rw-r--r--. 1 mysql mysql    1291 7月  10 15:37 t1.sql
-rw-r--r--. 1 mysql mysql    1306 7月  10 15:37 t2.sql
-rw-rw-rw-. 1 mysql mysql       4 7月  10 15:37 t2.txt
-rw-rw-rw-. 1 mysql mysql     162 7月  10 15:37 tables_priv.txt
-rw-r--r--. 1 mysql mysql    2068 7月  10 15:37 tables_priv.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 test.txt
-rw-r--r--. 1 mysql mysql    1304 7月  10 15:37 test.sql
-rw-r--r--. 1 mysql mysql    1487 7月  10 15:37 time_zone.sql
-rw-rw-rw-. 1 mysql mysql   11122 7月  10 15:37 time_zone.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 time_zone_leap_second.txt
-rw-r--r--. 1 mysql mysql    1493 7月  10 15:37 time_zone_leap_second.sql
-rw-r--r--. 1 mysql mysql    1435 7月  10 15:37 time_zone_name.sql
-rw-rw-rw-. 1 mysql mysql   41245 7月  10 15:37 time_zone_name.txt
-rw-r--r--. 1 mysql mysql    1548 7月  10 15:37 time_zone_transition.sql
-rw-rw-rw-. 1 mysql mysql 1998482 7月  10 15:37 time_zone_transition.txt
-rw-r--r--. 1 mysql mysql    1670 7月  10 15:37 time_zone_transition_type.sql
-rw-rw-rw-. 1 mysql mysql  161600 7月  10 15:37 time_zone_transition_type.txt
-rw-rw-rw-. 1 mysql mysql     878 7月  10 15:37 user.txt
-rw-r--r--. 1 mysql mysql    4390 7月  10 15:37 user.sql
-rw-r--r--. 1 mysql mysql    1576 7月  10 15:37 general_log.sql
-rw-r--r--. 1 mysql mysql    1757 7月  10 15:37 slow_log.sql


mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)


[mysql@localhost tmp]$ cat t.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t`
--

DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-07-10 15:37:05

[mysql@localhost tmp]$ cat t.txt
"1","jingyong",\N
"2","yeyali",\N
"3","huangyan",\N
"4","wenyao",\N



如果使用任何数据格式化选项来转储表数据，则在稍后重新加载数据文件时需要指定相同的格式，以确保正确地解释文件内容。


重新加载文本分隔格式的备份文件

对于使用mysqldump –tab命令所生成的备份，每个表在输出目录中都有一个包含create table语句的.sql文件和一个包含表数据的.txt文件。为了重新加载表，首先进入到输出目录。然后使用msyql命令来处理.sql文件创建一个空表，再执行mysqlimport来处理.txt文件加载数据:


[mysql@localhost tmp]$ mysql -uroot -pxxzx7817600  mysql < t.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

[mysql@localhost tmp]$ mysqlimport -uroot -pxxzx7817600  --fields-terminated-by=, --fields-enclosed-by='"'  --lines-terminated-by=0x0d0a mysql /mysqldata/tmp/t.txt
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
mysql.t: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)



另一种替mysqlimport来加载数据文件的方式是使用load data infile语句:


mysql> load data infile '/mysqldata/tmp/t.txt' into table t fields terminated by ',' enclosed by '"' lines terminated by 0x0d0a;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)
Proudly powered by WordPress | Indrajeet by Sus Hill.