MariaDB 公司的云数据库 SkySQL 介绍

本文仅仅是作为一个对 SkySQL 的简介。部分图片来自 Mariadb 官网

SkySQL 核心的平台架构于 K8s 之上,并采用 ServiceNow 作为 ITSM,实现数据库资产的管理和自治。技术上采用了 MaxScale 实现读写分离以及多 Replica 。

基础价格起步是 $0.45 每小时,起步注册赠送 $500 。

从油管介绍视频来看,负载均衡或者说高可靠的核心是 RW 端口和 RO 端口的配置,供应给上层数据库连接做配置。

在其他数据库参数配置方面,SkySQL 似乎提供的选项并不多,和 阿里的RDS比较起来少好多。譬如腾讯云可以设置参数模版:


而 AWS RDS 中 MariaDB 的参数配置居然有 30 页

好了, 这里不比较云厂商之间的性能,可靠性等,只是想说各家云厂商都有自己的 MySQL 实例和 MariaDB 实例提供。看来,在 MySQL 被 Oracle 收购后, MariaDB 正在成为关系型数据库的一匹黑马,异军突起。

抓取每张表的行数

show table status 命令比较宽, 不能选择指定的列,而且“rows” 对 InnoDB 时数据也不正确。

用 information_schema 里的数据就比较正确,姿势:

MariaDB [db]> select table_name,table_rows from information_schema.tables where table_schema=database() order by 2;

information_schema.tables 这张表的表结构如下:

找到空表的 auto_increment 大于 1 的表,
select table_name,auto_increment from information_schema.tables where table_schema=database() and auto_increment>1 and table_rows=0;

导出表名后,用 awk 生成 alter table table_name auto_increment=1 ; 的 SQL 执行即可。

MariaDB 集群,多主技术的快速上手

老思想总是认为 MySQL 就是一个 master 多个 Slave, 但是看完这篇文章, 将会颠覆你的“传统”思路,而且其配置之简单,令人惊讶!

在一台 Ubuntu18 的 物理机上用 KVM 安装(克隆)了多个 CentOS 7 的虚拟机。 以两台虚拟机(以下称节点) centos-02 (192.168.7.162/24) 和 centos-03 (192.168.7.163/24) 为例。

在 CentOS 7 上首先准备清华大学的 MariaDB 源,官方文档默认的都是国外的,下载速度呵呵。

然后就是 yum update; yum install mariadb-server galera-4; systemctl start mariadb;

以上需要在每个节点上都运行一遍,毕竟这个是基础,略过不表。

接下来我们在每个节点的 /etc/my.cnf.d/ 目录下创建一个 galera.cnf 的文件,截图是节点 02 的配置,如果是节点 03,只要修改 IP 和节点名即可。

[mysqld]
mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=256M
bind-address=192.168.7.162
# Galera settings
wsrep_provider=”/usr/lib64/galera-4/libgalera_smm.so”
# SSL for Galera
# wsrep_provider_options=”socket.ssl_key=/etc/mysql/ssl/server-key.pem;socket.ssl_cert=/etc/mysql/ssl/server-cert.pem;socket.ssl_ca=/etc/mysql/ssl/ca-cert.pem”
wsrep_cluster_name=”yj_cluster”
wsrep_cluster_address=”gcomm://192.168.7.162,192.168.7.163″
wsrep_sst_method=rsync
wsrep_on=ON
wsrep_node_address=”192.168.7.162″
wsrep_node_name=”centos-02″

配置好以上,在第一个节点 02 上运行 galera_new_cluster ,进入命令行 mysql 界面,运行 show status like ‘wsrep%’ 就可以看到 cluster 的情况了。 wsrep_cluster_size 就是集群里的活动节点数量。

如果所有的节点都 down 了, 第一个启动的节点,必须先运行 galera_new_cluster 才能启动集群。直接用 systemctl start mariadb 会报错,因为它在尝试连接集群。

多主技术就这么简单!数据库在内网复制的话,无需安装证书相关,十分简便。 IPtables 和 SELinux 这些烦人的东西,只要关闭即可,所谓 眼不见为净。

为了防止偶数节点产生集群的脑裂(split brain),我们需要另外找一个节点安装 Galera Arbitrator(仲裁器) 服务。
假设新节点为: centos-01(192.168.7.161) 我们只要往 /etc/sysconfig/garb 文件写入以下两行:
GALERA_NODES=”192.168.7.162 192.168.7.163 192.168.7.164″
GALERA_GROUP=”yj_cluster”
然后运行 systemctl start garb 即可。
然后用 MySQL 客户端,查看 wsrep_cluster_size 可以看到新加了一个数字。
这个能有效防止集群 down 机。

6.14每日一题: MySQL 的 Union 语句的 Order By

MySQL 的 union 语句主要用于联合查询多表时合并结果集。但是合并结果集后的排序是怎么做的,有点小技巧,分享如下:
假定有语句 A order by field_x ,然后有语句 B order by field_x,在 Union 时,用如下的语法是要报错的:
SELECT field_a, field_b, field_x FROM table_a ORDER BY field_x
UNION
SELECT field_a, field_b, field_x FROM table_b ORDER BY field_x

那正确的做法其实就是:
SELECT field_a, field_b, field_x FROM table_a
UNION
SELECT field_a, field_b, field_x FROM table_b ORDER BY field_x

这就实现两个结果集合并后,对字段 field_x 做了排序

如果要实现对结果集 A 先排序, 再 和 排序后的结果集 B , Union,我们应该这么写:

SELECT * FROM (SELECT field_a, field_b, field_x FROM table_a ORDER BY field_x) ta
UNION
SELECT * FROM (SELECT field_a, field_b, field_x FROM table_b ORDER BY field_x) tb

csv 文件 BOM(Byte Order Mark) 的去除以及 MySQL 的导入

Windows 上的 Excel 导出 csv 文件时,默认添加了 BOM,这个 BOM 的全称是 Byte Order Mark,以前的机器有字节顺序的问题,Windows 至今都没有去掉这个 BOM 头。这在 Linux 下导入 CSV 文件时会报字段格式有错。

The UTF-8 BOM is a sequence of Bytes at the start of a text-stream (EF BB BF) that allows the reader to more reliably guess a file as being encoded in UTF-8.

Normally, the BOM is used to signal the endianness of an encoding, but since endianness is irrelevant to UTF-8, the BOM is unnecessary.

According to the Unicode standard, the BOM for UTF-8 files is not recommended:


解决的办法是 (选任意一种方法)
1. tail -c +4 orig.txt > withoutBOM.txt
2. dos2unix orig.txt
3. sed -i '1s/^\xEF\xBB\xBF//' orig.txt

然后,我们用 以下命令就可以把 csv 文件导入到对应的数据库表了。

LOAD DATA INFILE ‘file’
IGNORE INTO TABLE table
CHARACTER SET UTF8
FIELDS TERMINATED BY ‘;’
OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’

查询MySQL 数据库里面所有表的行数

当一个数据库有上千张表的时候,用 show table status 查看返回的行数,那就基本上看不见了。
# mysql -A db_name -e “show table status”|awk ‘{print $1,$5}’|sort -n -k2

另外一个简单的办法就是:
select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = ‘db_name’ order by table_rows;

最简办法消除数据库里重复的行

说这个方法最简是因为不用烧脑写复杂的 SQL 语句。 只要用 Alter Ignore table create unique index index_name(index_key) 即可。

但是前提是要先把引擎切换为 MyISAM。 这样创建的 Unique Index 会自动把重复的行给删除了。
完整的语句:
alter table table_name engine MyISAM;

alter table table_name add unique index index_name (index_key);

alter table table_name engine InnoDB;

就这么简单!

在 CentOS7 的容器上用 PMM 对 MySQL 数据库做可视化监控

Percona 的开源数据库监控 PMM 简直是酷毙了。 老徐花了2个小时的时间,从无到有在云端的CentOS7 服务器上快速搭建了基于docker 容器技术的 PMM Server。在这里把实施步骤和遇到的问题汇总一下,十分值得初学者借鉴。

1. 容器的安装: # yum install docker ,然后启动 systemctl start docker;
2. 由于防火墙的原因,我们需要使用国内docker 镜像,使用 doacloud 加速,去 https://www.daocloud.io/ 注册以后,就可以得到一条生成镜像的脚本,类似:
curl -sSL https://get.daocloud.io/daotools/set_mirror.sh | sh -s http://xxxx.m.daocloud.io
脚本会自动帮助设置 docker 镜像的路径,可惜的是,在 CentOS 7 上生成的文件有误,运行完毕以上命令后,我们需要修改
/etc/docker/daemon.json 为如下内容:
{“registry-mirrors”: [“http://xxxx.m.daocloud.io”],”insecure-registries”: []}

3. 接下来就是把 pmm-server 镜像拉下来: # docker pull percona/pmm-server
4. 创建容器的数据卷,

docker create \
-v /opt/prometheus/data \
-v /opt/consul-data \
-v /var/lib/mysql \
-v /var/lib/grafana \
–name pmm-data \
percona/pmm-server /bin/true

5. 运行 pmm-server 容器,容器内部是 80 端口,外部我们设置为 18888 端口。后面会讲到不开防火墙的情况下怎样从外网访问这个18888端口。

docker run -d \
-p 18888:80 \
–volumes-from pmm-data \
–name pmm-server \
–restart always \
percona/pmm-server

     如果一切正常的话,wget http://localhost:18888/ 应该返回200 状态,并且取到 index.html。然而, 在我的环境里, 发现 docker 里面启动的是 IPV6 的地址,我们需要去 /etc/sysconfig/docker-network 添加 -ip 选项,因为 PMM Server 需要让外部的 PMM Client 连接上来,我们不能让它听在 127.0.0.1 上。

DOCKER_NETWORK_OPTIONS=”-ip=192.168.0.92″

重启 docker 服务。
经过以上步骤以后,一个 PMM Server 就基本搭建完成了。

6. 接下来我们找一台机器把 PMM Client(Agent)安装上去。再简单配置一下。
# wget https://www.percona.com/downloads/pmm/1.8.0/binary/redhat/7/x86_64/pmm-client-1.8.0-1.x86_64.rpm; rpm -ivh pmm-client-1.8.0-1.x86_64.rpm
# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
# yum install pmm-client
# pmm-admin config –server 192.168.0.92
# pmm-admin config –server 192.168.0.92:18888

OK, PMM server is alive.

PMM Server | 192.168.0.92:18888
Client Name | iot-db-01
Client Address | 192.168.0.92
# pmm-admin list
pmm-admin 1.8.0

PMM Server | 192.168.0.92:18888
Client Name | iot-db-01
Client Address | 192.168.0.92
Service Manager | linux-systemd

No services under monitoring.

然后我们添加收集数据的用户:
# pmm-admin add mysql –host localhost –user root –password P@ssw0rd
[linux:metrics] OK, now monitoring this system.
[mysql:metrics] OK, now monitoring MySQL metrics using DSN root:***@unix(/var/lib/mysql/mysql.sock)
[mysql:queries] OK, now monitoring MySQL queries from slowlog using DSN root:***@unix(/var/lib/mysql/mysql.sock)

查看prometheus状态:

http://192.168.0.92:18888/prometheus/targets

================================
但是如果在公网上配置的话,我们不希望在安全组上开那么多“洞”,接下来我们讲下通过 Apache mod_proxy 来反向代理,在浏览器通过  80 端口访问 PMM Server。
以下是 Apache Virtualhost 的配置,我们就可以通过: http://demo.yj777.cn/graph/ 访问了。

<VirtualHost *:80>
ServerName demo.yj777.cn
ServerAlias 192.168.0.92

RewriteEngine On
ProxyRequests Off
ProxyPreserveHost On

<Proxy *>
Require all granted
</Proxy>

ProxyPass “/graph/” “http://192.168.0.92:18888/graph/” connectiontimeout=5 timeout=30 keepalive=on
ProxyPassReverse “/graph/” “http://192.168.0.92:18888/graph/”
RewriteRule ^/graph$ http://%{HTTP_HOST}/graph/ [L,R=301]

ProxyPass “/qan/” “http://192.168.0.92:18888/qan/” connectiontimeout=5 timeout=30 keepalive=on
ProxyPassReverse “/qan/” “http://192.168.0.92:18888/qan/”
RewriteRule ^/qan$ http://%{HTTP_HOST}/qan/ [L,R=301]

ProxyPass “/qan-api/” “http://192.168.0.92:18888/qan-api/” connectiontimeout=5 timeout=30 keepalive=on
ProxyPassReverse “/qan-api/” “http://192.168.0.92:18888/qan-api/”
RewriteRule ^/qan-api$ http://%{HTTP_HOST}/qan-api/ [L,R=301]

ProxyPass “/orchestrator/” “http://192.168.0.92:18888/orchestrator/” connectiontimeout=5 timeout=30 keepalive=on
ProxyPassReverse “/orchestrator/” “http://192.168.0.92:18888/orchestrator/”
RewriteRule ^/orchestrator$ http://%{HTTP_HOST}/orchestrator/ [L,R=301]

ProxyPass “/v1/” “http://192.168.0.92:18888/v1/” connectiontimeout=5 timeout=30 keepalive=on
ProxyPassReverse “/v1/” “http://192.168.0.92:18888/v1/”
RewriteRule ^/v1$ http://%{HTTP_HOST}/v1/ [L,R=301]

ProxyPass “/prometheus/” “http://192.168.0.92:18888/prometheus/” connectiontimeout=5 timeout=30 keepalive=on
ProxyPassReverse “/prometheus/” “http://192.168.0.92:18888/prometheus/”
RewriteRule ^/prometheus$ http://%{HTTP_HOST}/prometheus/ [L,R=301]

ProxyPass “/consul/” “http://192.168.0.92:18888/consul/” connectiontimeout=5 timeout=30 keepalive=on
ProxyPassReverse “/consul/” “http://192.168.0.92:18888/consul/”
RewriteRule ^/consul$ http://%{HTTP_HOST}/consul/ [L,R=301]

ProxyPass “/managed/” “http://192.168.0.92:18888/managed/” connectiontimeout=5 timeout=30 keepalive=on
ProxyPassReverse “/managed/” “http://192.168.0.92:18888/managed/”
RewriteRule ^/managed$ http://%{HTTP_HOST}/managed/ [L,R=301]

</VirtualHost>

一行命令修改所有外键值

由于业务需要,我们需要把主表的一个主键 id  = 1,用做特别的处理。 因此原来所有引用该主键的外键值也需要修改为新的值。

我们在主表里插入了 id = 8 的记录,然后修改所有有外键引用的表里的记录值从 id = 1 修改为 8。

for t in `mysqldump -d dbname|grep ‘company_id’|grep REFERENCES|awk ‘{print $2}’|sed -e ‘s/_ibfk.*$//g’ -e ‘s/\`//g’|sort|uniq`; do echo $t; mysql dbname -e “update $t set company_id=8 where company_id=1”; done

以上单行命令,我们从 mysqldump 导出的表结构里,找到所有外键的名字,并且把外键的表的名字取出来,然后更新每张表的 company_id。

mysqldump 导出后导入 MySQL 字符混乱问题的“解决”

这其实是一个十分简单的数据库客户端连接字符集设置问题。

当我们用 mysqldump 导出数据时,只要数据库的字符集都有正确指定,那么导出的 SQL 文件的内容应该正确的设置了需要的字符集。

当我们把 SQL 文件拷贝需要导入的机器,传输过程中当然不存在 SQL 文件字符集被修改的理由。

因此,问题就出在我们导入 SQL 文件时,连接所采用的字符集。

有解决方案,需要设置 /etc/my.cnf 里,为 [mysqld] 添加
character-set-server=utf8
然后,在同一个 /etc/my.cnf  里,为默认的 mysql 客户端连接,[mysql] 添加同样的一行:
default-character-set=utf8

然后重启 MySQL 服务进程。
经过这样的修改后,我们在 Linux 终端里,运行 mysql 命令, 连接到数据库,敲 “\s” 命令,可以看到数据库连接状态中,有三个字符集为: utf8

Server characterset: utf8
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8

实际上,如果我们导出的 SQL 文件是正确的话, 没有必要修改服务器的字符集。
因此,我们注释掉 [mysqld] 里面的那行  character-set-server=utf8,重新启动 MySQL后,用 “\s” 指令,我们可以看到字符集只有 “Client” 和 “Conn.” 是采用了 utf8,用 select 语句查看表中的内容,所有原来的 ?? 都能正确显示为中文了。

Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8