mysql.md · Duan Yao/development-notes

参考资料

[2.1] Linux( CentOS)安装Mysql https://www.jianshu.com/p/15a1659bc38d

[2.2] A Quick Guide to Using the MySQL Yum Repository https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

[2.3] AppArmor and MySQL https://blogs.oracle.com/jsmyth/apparmor-and-mysql

[2.4] Change user password in MySQL 5.7 with “plugin: auth_socket” https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/

[4.1] Introduction: MySQL vs. MariaDB https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/

[4.2] Table 'performance_schema.session_variables' doesn't exist https://stackoverflow.com/questions/31967527/table-performance-schema-session-variables-doesnt-exist

[5.3] connector-j Character Sets and Unicode https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html

[5.4] 深刻理解gtid_executed和gtid_purged https://www.itgank.com/archives/2740

[6.1] json-bigint https://github.com/sidorares/json-bigint

[7.1] 7.2 Database Backup Methods https://dev.mysql.com/doc/refman/8.0/en/backup-methods.html

[8.1] mysql死锁 DeadLock定位与解决 https://blog.csdn.net/h2604396739/article/details/86632031

[8.2] 幻读、间隙锁、行锁、next-key lock、加锁规则、间隙锁导致的死锁、隔离级别设置、for update的理解 https://blog.csdn.net/h2604396739/article/details/86518943

[9.1] 如何快速定位当前数据库消耗CPU最高的sql语句？ https://mp.weixin.qq.com/s?__biz=MzA4NjgxMjQ5Mg==&mid=2665771355&idx=1&sn=a7e756b53fe1328e6bcb48f7f35586fc&chksm=84d22378b3a5aa6eb8b7126b82c5225a6ad5496c78900375d2e9a29b08070e550b71cc3d60c2&scene=132#wechat_redirect

安装

mariadb + centos 7

原来的包名 mysql-server 其实是指向 mariadb-server 。

yum install mariadb          # 客户端工具
yum install mariadb-server   # 服务器端

mariadb 与 MySQL 的异同见 [4.1]，他们并不是完全兼容的。Percona Server for MySQL 是另一个与 MySQL 兼容的数据库。

mariadb 5.5.60 @ centos 7.4 上，初始 root 口令是空。

修改密码：

mysqladmin -u root -p password

安装 mysql 5.7 + centos 7

去 mysql 网站上下载，选择 yum 方式，下载 https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm

rpm -i mysql80-community-release-el7-3.noarch.rpm
yum repolist all | grep mysql
yum-config-manager --disable mysql80-community  # yum-config-manager 来自包 yum-utils
yum-config-manager --enable mysql57-community

yum install yum-plugin-downloadonly
yum install –downloadonly mysql-community-server
sudo yum install mysql-community-server
sudo systemctl start mysqld.service

可以从 mariadb 迁移到 mysql：直接安装 mysql-community-server 即可替换掉 mariadb。但已有的 mariadb 创建的数据库需要升级，命令是[4.2]：

mysql_upgrade -u root -p --force
systemctl start mysqld.service

mysql 5.7 + debian9/deepin 15.11

sudo apt install mysql-server-5.7 mysql-client-5.7  libmysqlclient-dev
sudo systemctl start mysql

错误日志在： /var/log/mysql/error.log

mysql-server 5.7.21-1 启动会有 apparmor 相关的错误，journalctl 中显示：

4月 03 01:13:17 duanyao-laptop-c audit[26982]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/26982/status" pid=26982 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0
4月 03 01:13:17 duanyao-laptop-c audit[26982]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=26982 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0
4月 03 01:13:17 duanyao-laptop-c audit[26982]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/26982/status" pid=26982 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0
4月 03 01:13:17 duanyao-laptop-c audit[26988]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/26988/status" pid=26988 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=122 ouid=122
4月 03 01:13:17 duanyao-laptop-c audit[26988]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=26988 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=122 ouid=0
4月 03 01:13:17 duanyao-laptop-c audit[26988]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/26988/status" pid=26988 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=122 ouid=122
4月 03 01:13:17 duanyao-laptop-c audit[26991]: AVC apparmor="DENIED" operation="create" profile="/usr/sbin/mysqld" pid=26991 comm="mysqld" family="inet" sock_type="stream" protocol=6 requested_mask="create" denied_mask="create"
4月 03 01:13:18 duanyao-laptop-c mysqld[26988]: Initialization of mysqld failed: 0
4月 03 01:13:18 duanyao-laptop-c systemd[1]: mysql.service: Control process exited, code=exited status=1

这可以通过修改 apparmor-mysqld 配置文件来解决[2.3]：

/etc/apparmor.d/usr.sbin.mysqld
/etc/apparmor.d/local/usr.sbin.mysqld

但最简单的方法是将 apparmor-mysqld 从 enforce mode 转入 complain mode，这样就不会阻止 mysqld 的启动[2.3]:

sudo apt install apparmor-utils
sudo aa-complain /usr/sbin/mysqld
sudo systemctl reload apparmor
sudo systemctl start mysql

检查 apparmor-mysqld 的状态：

sudo aa-status
1 profiles are in complain mode.
   /usr/sbin/mysqld
1 processes are in complain mode.
   /usr/sbin/mysqld (21553)

初始设置（以后也可以重复执行）：

sudo mysql_secure_installation

注意，尽管这里提示设置数据库root密码，但设置了也不生效。

在设置数据库root密码之前，只能用 auth_socket 模式，即只允许 root 用户从本地登录，且无需密码。设置数据库 root 密码：

sudo mysql -u root  # auth_socket 登录模式，无需密码，但必须 sudo

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '94n84-ji7';

BY 后面就是密码。这之后，就可以用

mysql -u root -p

登录了，而原来的 auth_socket 模式则停用。

启用 x protocol

sql 命令：

INSTALL PLUGIN mysqlx SONAME 'mysqlx.so'

yum install mysql-shell

初始设置

使用命令行客户端

mysql -u root -p --host rm-2ze73ql0z63t1iy6r.mysql.rds.aliyuncs.com --port 3306

-u 指定用户名，-p 交互式输入口令，--host 指定连接的主机地址，--port 指定端口。

数据导出

dbeaver 和 mysql workbench 都可以将数据库表或者一个查询结果导出为文件，其格式包括 csv、json、sql、xlsx 等。

大整数丢失精度的问题

导出 json 和 csv 都存在大整数丢失精度的问题。 json 虽然没有明确规定 number 类型的精度，但很多实现中是 double，这样就不能表示 bigint（64位），会损失精度。 dbeaver 和 mysql workbench 导出的 json 本身没有损失精度，所以可以用支持 bigint 的自定义 json 解析器来解析[6.1]。 csv/xls/xlsx 中的数字的整数部分也是不到 64 位的，超过后也会损失精度。这个损失并非发生在文件中，csv 仍然允许存储 bigint，但是 excel、wps 等打开它时会转换并丢失精度。

mysql workbench 导出时可以选择 excel speadsheet 格式，这是 xlsx 的一种 xml 表示，这时它里面的 bigint 会被转换成文本格式，从而不损失精度。所以含有 bigint 的数据最好用这种形式导出。mysql workbench 6.3.8 的 excel speadsheet 导出会默认添加 xml 后缀，应该改为 xlsx 方可被 wps 等打开。

null 的处理

mysql workbench 导出 csv 时，null 变成空字符串 ""。如果列的类型不是字符串，这在导入时可能造成问题，例如 load data local infile 命令会忽略这样的行。

mysql workbench 导出 json 时，如果列的类型是数字，则 null 会导出为 0，这也是个问题。

数据导入

mysql 命令行

命令行：

use aiparents
load data local infile '/home/duanyao/project/ai-parents-app.git/temp/child_dbeaver_1.csv' into table child fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows;

load data local infile '/home/duanyao/project/ai-parents-app.git/temp/child_dbeaver_1.csv' into table child fields terminated by ',' lines terminated by '\n' ignore 1 rows;

mysql-shell

mysqlsh root@host:3308/db_name --import xxx.json table_name

mysqlsh mysqlx://root@127.0.0.1:3308/aiparents --import /home/duanyao/project/ai-parents-app.git/temp/child_dbeaver_2.json child

不过这对服务器的版本似乎有一定的要求，服务器必须支持 mysql x protocol(不是图形界面的那个) ，mariaDB 是不支持的[4.1]。但没有查到从哪个版本起 mysql x protocol 开始支持。 mysql x protocol 似乎并非使用原来的 3306 端口，而是 33060 端口。

一些陷阱

127.0.0.1 和 localhost

连接非默认的本地数据库端口时，mysql 和 mysqldump 等客户端工具的 --host 参数应该指定 127.0.0.1 而不是 localhost ，否则 --port 参数会无效（使用默认端口 3306），导致连接失败。

因此，应该总是使用 127.0.0.1 而不是 localhost ，包括 dbeaver 等客户端工具。

备份

文件型备份

mysql/mariadb 的默认存储位置是：

/var/lib/mysql

其中的数据表文件是 *.MYD, *.MYI *.sdi，备份整个目录也可以。复制文件前，可以先停下服务器，或者加上 read lock 并刷新缓存。

死锁及其处理

mysql 在客户代码没有逻辑和时序错误的情况下，也有可能出现死锁，往往是在用 update 语句同时对一张表的多个行进行操作时[8.1]。这种死锁是暂时的，一般可以通过重试失败的 update 语句来解决。

mysql node.js 中，可以用异常对象的 err.code == ER_LOCK_DEADLOCK 来检测死锁错误， err.sql 可以得到死锁的语句。

性能剖析

[9.1]

Duan Yao / development-notes

参考资料

安装

mariadb + centos 7

安装 mysql 5.7 + centos 7

mysql 5.7 + debian9/deepin 15.11

启用 x protocol

初始设置

使用命令行客户端

数据导出

大整数丢失精度的问题

null 的处理

数据导入

mysql 命令行

mysql-shell

一些陷阱

127.0.0.1 和 localhost

备份

文件型备份

死锁及其处理

性能剖析

简介

发行版

贡献者

近期动态

Duan Yao / development-notes .gitee-modal { width: 500px !important; }

参考资料

安装

mariadb + centos 7

安装 mysql 5.7 + centos 7

mysql 5.7 + debian9/deepin 15.11

启用 x protocol

初始设置

使用命令行客户端

数据导出

大整数丢失精度的问题

null 的处理

数据导入

mysql 命令行

mysql-shell

一些陷阱

127.0.0.1 和 localhost

备份

文件型备份

死锁及其处理

性能剖析

简介

发行版

贡献者

近期动态

搜索帮助

Duan Yao / development-notes