分类：MySQL&MariaDB

MySQL加解密函数

MySQL自带的加解密函数主要有以下3对：

  • ENCODE()、DECODE()

已在5.7.2版本弃用，目前仍可用，但将在后续版本中删除。

  • DES_ENCRYPT()、DES_DECRYPT()

已在5.7.6版本弃用，目前仍可用，但将在后续版本中删除。

  • AES_ENCRYPT()、AES_DECRYPT()

推荐使用这对加解密函数。aes_encrypt()和aes_decrypt()使用官方的aes（高级加密标准）算法（以前称为“rijndael”）实现数据的加密和解密。

加密后的二进制串长度可以通过下面公式计算：

16 * (trunc(string_length / 16) + 1)

MySQL版本小于5.7.4：

函数参数：

AES_ENCRYPT(str,key_str)，其中str为待加密字符串，key_str为秘钥

AES_DECRYPT(crypt_str,key_str)，其中crypt_str为已加密的二进制串，key_str为秘钥

例：

创建表，插入加密字段，秘钥为‘keykey’查询出的结果为密文：

使用错误的key解密，结果为NULL：

使用正确的key解密：

MySQL版本大于等于5.7.4：

函数参数：

AES_ENCRYPT(str,key_str[,init_vector])，其中str为待加密字符串，key_str为秘钥，其中init_vector根据选择不同的块加密模式为可选项

AES_DECRYPT(crypt_str,key_str[,init_vector])，其中crypt_str为已加密的二进制串，key_str为秘钥，其中init_vector根据选择不同的块加密模式为可选项

st和key_str参数可以是任何长度，init_vector参数不得小于16个字符。

可以通过block_encryption_mode参数，控制块加密模式，默认值为：aes-128-ecb。可配置的形式为：aes-keylen–mode。

其中：

keylen可配置为128, 192, 256

mode可配置为ECB, CBC, CFB1, CFB8, CFB128, OFB

下表展示了不同mode是否需要init_vector参数。

Block Encryption ModeSSL Libraries that Support ModeInitialization Vector Required
ECBOpenSSL, yaSSLNo
CBCOpenSSL, yaSSLYes
CFB1OpenSSLYes
CFB8OpenSSLYes
CFB128OpenSSLYes
OFBOpenSSLYes

默认的ECB模式不需要init_vector参数，用法与5.7.4以前相同。

例：

修改块加密模式：

该模式下再使用两参数加密报错：

三参数加密：

解密成功：

MySQL到Greenplum迁移分析

1 数据类型对比

  MySQL PostgreSQL comments
数值类型 TINYINT SMALLINT gp中无zerofill属性及unsigned类型，所以为了数据不越界需使用大一精度的数据类型匹配
SMALLINT SMALLINT
MEDIUMINT INTEGER
INT|INTEGER INTEGER
BIGINT BIGINT
TINYINT UNSIGNED SMALLINT
SMALLINT UNSIGNED INTEGER
MEDIUMINT UNSIGNED INTEGER
INT UNSIGNED BIGINT
BIGINT UNSIGNED NUMERIC(20)
BIT BIT
FLOAT REAL
FLOAT UNSIGNED DOUBLE PRECISION
DOUBLE|REAL|DOUBLE PRECISION DOUBLE PRECISION
DECIMAL|DEC|NUMERIC|FIXED NUMERIC
字符类型 CHAR CHARACTER|CHAR  
VARCHAR CHARACTER VARYING|VARCHAR  
TINYTEXT TEXT  
TEXT TEXT  
MEDIUMTEXT TEXT  
LONGTEXT TEXT  
BINARY|CHAR BYTE BYTEA  
VARBINARY BYTEA  
TINYBLOB BYTEA  
BLOB BYTEA  
MEDIUMBLOB BYTEA  
LONGBLOB BYTEA  
时间类型 DATE DATE  
TIME TIME  
YEAR 无  
DATETIME TIMESTAMP  
TIMESTAMP TIMESTAMP  
其他类型 BOOL|BOOLEAN BOOLEAN  
ENUM CREATE TYPE … AS ENUM  
SET 无  

2 语法对比

2.1 limit

MySQL：

or

 

Greenplum：

2.2 replace

MySQL：

Greenplum：

不支持该语法，需要使用函数实现，例：

2.3 insert into … on duplicate key update

MySQL：

Greenplum：

不支持该语法，需要使用函数实现，例：

2.4 select … into outfile

MySQL：

Greenplum：

2.5 自增列

MySQL：

列加auto_increment属性，例:create table a(id int auto_increment primary key)

获取当前值:select last_insert_id()

 

Greenplum：

字段类型使用serial,例:create table a(id serial primary key)

获取当前值:select currval(‘a_id_seq’)

2.6 注释

MySQL：

使用#或–

Greenplum：

使用–

2.7 执行存储过程

MySQL：

Greenplum：

Greenplum并无存储过程，使用函数代替，所以执行：

3 常用函数对比

3.1 时间函数

3.1.1 时间转字符串

MySQL：date_format（）

例：select date_format(now(),’%Y%m%d%H%i%s’)

Greenplum：to_char（）

例：select to_char(now(), ‘YYYYMMDDHH24MISS’)

3.1.2 字符串转时间

MySQL：str_to_date()

例：select str_to_date(‘20171120′,’%Y%m%d%H%i%s’)

Greenplum：to_date(),to_timestamp()

例：select to_date(‘20171120’, ‘YYYYMMDD’)

select to_date(‘20171120’, ‘YYYYMMDDHH24MISS’)

3.1.3 时间计算

MySQL：date_add()

例：select date_add(now(), interval 2 day)

Greenplum：直接计算

例：select now() + interval ‘2 day’

3.2 字符函数

3.2.1 空字符串处理

MySQL：ifnull

例：select ifnull(null,‘default’)

Greenplum：coalesce

例：select coalesce(null,‘default’)

3.2.2 字符串拼接

MySQL：concat()

例：select concat(‘abc’,‘def’)

Greenplum：||

例：select ‘abc’||’def’

4 数据迁移

Greenplum数据导入3种方式：

4.1 COPY命令

COPY需要经过master，仅建议在小数据量时使用。无法并行导入，在大量数据导入时效率很低，不过多介绍。

例：COPY tablea FROM ‘/data/tablea_data’;

4.2 使用外部表

外部表以及4.3中的gpload都需要使用gpfdist服务。

gpfdist是Greenplum自带的一个并行文件服务，原理如下图：

gpfdist为每个segment提供并行读写数据文件的服务。

 

1、先启动gpfdist服务，例：

-d 指定数据目录 -p指定服务端口 -l 指定日志文件

将数据文件放入该目录下

2、创建外部表，例：

  • 从外部表导入数据，例：

或者先创建，后导入：

4.3 gpload

通过配置yaml控制文件来进行数据导入，同样依赖gpfdist服务。

例：

1、编辑a.yml文件

2、进行导入：

 

MySQL Test Suite使用

MySQL自动测试套件（The MySQL Test Suite）用于对MySQL程序进行测试，包括各种功能与存储引擎。包含于MySQL与MariaDB版本代码中，位于mysql-test目录下。总体测试脚本为mysql-test-run.pl。该perl脚本通过调用各种已有的测试脚本，将测试结果与预置的result文件做对比来判断测试是否通过。（任何一点差异都会导致测试失败，包括预期之外的warning）

一、主要目录介绍

    • include
包含.inc文件，用于测试开始时判断是否满足测试条件，在测试用通过source命令引入
如hava_innodb.inc:

如不满足，则会跳过需要该验证的测试。

    • suite
包含所有测试suite，每一个suite为一个测试用例集，具体用例集在2节介绍
    • t
1、测试文件
例：1st.test

上面内容即为1st这个测试用例的测试脚本

 2、配置文件
例：innodb_bug53674-master.opt
–loose-innodb-locks-unsafe-for-binlog –binlog-format=mixed
为该测试需要的特殊配置项
3、脚本文件，在测试开始之前执行的脚本
例：rpl_misc_functions-slave.sh
rm -f $MYSQLTEST_VARDIR/master-data/test/rpl_misc_functions.outfile
    • r
结果文件，判断是否通过的依据
使用上面的例子：1st.result

执行完1st用例会严格比对该文件。完全一致则测试通过。

    • std_date
有些测试需要用到一些标准数据。
    • var
测试默认使用该文件夹下的my.cnf文件

二、测试用例集

./mysql-test-run.pl不带任何参数表示执行所有测试用例集。使用—suite=suitename来指定单独执行suitename目录下的所有测试用例。
测试用例集包括：

 

三、主要参数

force：默认情况下，只要遇到一个用例出错，测试程序就会退出，在指定force的情况下，测试程序会继续执行下面的测试（但是最多发现10个错误还是会退出）
suite：指定使用的测试suite
do-test：会以输入的字符串进行匹配用例执行
skip-test：会以输入的字符串进行匹配跳过用例执行
big-test：执行标记为big的测试用例。因为用例较大、耗时较长，标记为big的用例默认不会执行。输入两遍big-test则只执行标记为big的测试用例
recored：重新生成结果文件
mysqld：传递启动参数给mysqld，每个参数需要分别指定一个mysqld
其他还有很多选项，可以使用./mtr –help查看
如果需要在服务启动前执行一些脚本，可以写在 t/testname.sh文件中，由mtr自动执行。

四、测试举例

例1：
可以看出共完成12个测试用例，全部通过。1个用例必须在debug模式下测试，所以跳过。测试过程中服务重启3次。测试默认使用16000-16019端口。
例2：

每个测试结束前，mtr会检索error日志，如果发现warning或error，则测试失败。

再来看两个失败用例：
（截取部分日志）

分析日志可以看出是在测试过程中在错误日志中发现了warnings/errors。原因是使用了废弃的参数innodb_locks_unsafe_for_binlog导致warning。

找到并修改配置文件：innodb_bug53674-master.opt
将–loose-innodb-locks-unsafe-for-binlog –binlog-format=mixed
修改为：–transaction-isolation=READ-COMMITTED –binlog-format=mixed
重新测试：

不再有warning，测试通过。