技术分享 | MariaDB 10.1.9 迁移到 MySQL 5.7.25

爱可生开源社区

发布于 2020-06-12 11:32:28

1.9K0

发布于 2020-06-12 11:32:28

文章被收录于专栏：爱可生开源社区爱可生开源社区

作者：秦广飞

爱可生 DBA 团队成员，负责项目日常问题处理及公司平台问题排查，对数据库有兴趣，对技术有想法。一入 IT 深似海，从此节操是路人。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

背景

客户环境数据库目前使用的是 MariaDB 10.1.9，计划迁移到 MySQL 5.7.25，需要测试迁移是否能成功，以及迁移到 MySQL 后数据库的性能对比。

一、准备环境

1. 安装 MariaDB 10.1.9

[root@qin_1 ~]# ls
anaconda-ks.cfg  mariadb-10.1.9-linux-x86_64.tar.gz  original-ks.cfg
[root@qin_1 ~]# groupadd mysql
[root@qin_1 ~]# useradd -g mysql mysql
[root@qin_1 ~]# cd /usr/local/
[root@qin_1 local]# tar -zxvpf /root/mariadb-10.1.9-linux-x86_64.tar.gz
[root@qin_1 local]# ln -s /usr/local/mariadb-10.1.9-linux-x86_64/ /usr/local/mysql
[root@qin_1 local]# mkdir -p /data/mysql/data
[root@qin_1 local]# chown -R mysql:mysql /usr/local/mysql/
[root@qin_1 local]# chown -R mysql:mysql /data/mysql/data/
[root@qin_1 local]# cd mysql/
[root@qin_1 mysql]# yum install libaio* jemalloc -y
[root@qin_1 mysql]# ./scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf
[root@qin_1 mysql]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[root@qin_1 mysql]# /usr/local/mysql/bin/mysqladmin -uroot password '666666a'
[root@qin_1 mysql]# /usr/local/mysql/bin/mysql -uroot -p666666a -S /data/mysql/data/mysqld.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.9-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

[client]
port                           = 3306
socket                         = /data/mysql/data/mysqld.sock

[mysqld]
port                           = 3306
socket                         = /data/mysql/data/mysqld.sock
basedir                        = /usr/local/mysql
datadir                        = /data/mysql/data
tmpdir                         = /data/mysql/data

### skip-character-set-client-handshake
log_bin_trust_function_creators = 1
innodb_print_all_deadlocks      = 1
skip-external-locking
skip-name-resolve
autocommit                     = 1
innodb_thread_concurrency      = 8
innodb_defragment              = 1

character_set_server           = utf8
init_connect                   = 'SET NAMES utf8'
init_connect                   = 'SET collation_connection = utf8_general_ci'

# 从库关闭binlog
log-bin                        = binlog
binlog_format                  = ROW
max_binlog_size                = 256M
expire_logs_days               = 5
binlog_cache_size              = 64M

#
server-id                      = 1001
innodb_data_home_dir           = /data/mysql/data
innodb_data_file_path          = ibdata1:100M:autoextend
innodb_log_group_home_dir      = /data/mysql/data
innodb_log_file_size           = 128M
innodb_log_buffer_size         = 8M

# 重要参数
innodb_buffer_pool_size        = 1G
innodb_flush_method            = O_DIRECT
innodb_file_per_table          = 1
innodb_flush_log_at_trx_commit = 1

# CACHES AND LIMITS #
query-cache-type               = 0
query-cache-size               = 0
open_files_limit               = 65535
innodb_open_files              = 4096
table_open_cache               = 2000
thread_cache_size              = 200
max_connections                = 2000
max_connect_errors             = 5000

# REPLICATION #
read_only                      = 0
event_scheduler                = 1
slave-net-timeout              = 60
slave-skip-errors              = 1062
slave_parallel_threads         = 4

wait_timeout                   = 3600
interactive_timeout            = 3600
lock_wait_timeout              = 600
innodb_lock_wait_timeout       = 600
concurrent_insert              = 2
key_buffer_size                = 256M
max_allowed_packet             = 64M

sort_buffer_size               = 2M
read_buffer_size               = 8M
join_buffer_size               = 8M
read_rnd_buffer_size           = 8M

# LOGGING #
log-error                      = /data/mysql/data/mysql-error.log
slow_query_log                 = 1
long_query_time                = 1
log-queries-not-using-indexes = 1
slow_query_log_file            = /data/mysql/data/mysql-slow.log

tmp_table_size                 = 64M
max_heap_table_size            = 64M
bulk_insert_buffer_size        = 64M
group_concat_max_len           = 102400

[mysqldump]
quick
max_allowed_packet             = 64M

[mysql]
no-auto-rehash

2. 安装 mysql 5.7.25

[root@qin_2 ~]# ls
anaconda-ks.cfg  mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz  original-ks.cfg
[root@qin_2 ~]# groupadd mysql
[root@qin_2 ~]# useradd -g mysql mysql
[root@qin_2 ~]# cd /usr/local/
[root@qin_2 local]# tar -xf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -C /usr/local
[root@qin_2 local]# mv mysql-5.7.25-linux-glibc2.12-x86_64/ mysql
[root@qin_2 local]# chown -R mysql:mysql mysql
[root@qin_2 local]# mkdir -p /data/mysql/data
[root@qin_2 local]# chown -R mysql:mysql /data/mysql/data/
[root@qin_2 local]# yum install libaio* -y
[root@qin_2 local]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
[root@qin_2 local]# /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
[root@qin_2 local]# /usr/local/mysql/bin/mysql -uroot -S /data/mysql/data/mysqld.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '666666a';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

[root@qin_2 local]# /usr/local/mysql/bin/mysql -uroot -p666666a -S /data/mysql/data/mysqld.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

[mysql]
no-auto-rehash
#default-character-set = utf8mb4
#tee = /data/mysql_tmp/mysql_operation.log

[mysqld]
super_read_only = 0
# DO NOT MODIFY, Universe will generate this part
port = 3306
server_id = 1163174063
basedir = /usr/local/mysql
datadir = /data/mysql/data
log_bin = /data/mysql/data/mysql-bin
tmpdir = /data/mysql/data
relay_log = /data/mysql/data/mysql-relay
innodb_log_group_home_dir = /data/mysql/data
log_error = /data/mysql/data/mysql-error.log

# BINLOG
binlog_error_action = ABORT_SERVER
binlog_format = row
binlog_rows_query_log_events = 1
log_slave_updates = 1
master_info_repository = TABLE
max_binlog_size = 250M
relay_log_info_repository = TABLE
relay_log_recovery = 1
sync_binlog = 1

# ENGINE
default_storage_engine = InnoDB
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit=1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 200
innodb_log_buffer_size = 64M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 60
innodb_print_all_deadlocks=1
#innodb_stats_on_metadata = 0
innodb_strict_mode = 1
#innodb_undo_logs = 128 #Deprecated In 5.7.19
innodb_undo_tablespaces=3    #Deprecated In 5.7.21
innodb_max_undo_log_size=4G
innodb_undo_log_truncate=1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct=25
innodb_sort_buffer_size = 8M
#innodb_page_cleaners = 8
innodb_buffer_pool_instances = 8
innodb_lock_wait_timeout = 10
innodb_io_capacity_max = 2000
innodb_flush_neighbors = 1
#innodb_large_prefix = 1
innodb_thread_concurrency = 4
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size = 1G
innodb_open_files = 4096
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G
innodb_rollback_segments = 128
innodb_numa_interleave = 1

# CACHE
key_buffer_size = 16M
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 2000
query_cache_type = 0
query_cache_size = 0
max_connections = 2000
thread_cache_size = 200
open_files_limit = 65535
binlog_cache_size = 1M
join_buffer_size = 8M
sort_buffer_size = 2M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
table_definition_cache = 2000
table_open_cache_instances = 8


# SLOW LOG
slow_query_log = 1
slow_query_log_file = /data/mysql/data/mysql-slow.log
log_slow_admin_statements = 1
log_slow_slave_statements = 1
long_query_time = 1


# MISC
log_timestamps=SYSTEM
lower_case_table_names = 1
max_allowed_packet = 64M
read_only = 0
skip_external_locking = 1
skip_name_resolve = 1
skip_slave_start = 1
socket = /data/mysql/data/mysqld.sock
pid_file = /data/mysql/data/mysqld.pid
disabled_storage_engines = ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB
log-output = TABLE,FILE
character_set_server = utf8mb4
secure_file_priv = ""
performance-schema-instrument ='wait/lock/metadata/sql/mdl=ON'
performance-schema-instrument = 'memory/% = COUNTED'
expire_logs_days = 7
max_connect_errors = 1000000
interactive_timeout = 1800
wait_timeout = 1800
log_bin_trust_function_creators = 1

##BaseConfig
collation_server = utf8mb4_bin
explicit_defaults_for_timestamp = 1
transaction_isolation = READ-COMMITTED

二、迁移测试

1. 对 MariaDB 全备

//在qin_1上给MariaDB制造点数据
[root@qin_2 ~]# yum install -y sysbench
[root@qin_2 ~]# sysbench --version
sysbench 1.0.17

[root@qin_1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.186.64.16 --mysql-port=3306 --mysql-user=root --mysql-password='666666a' --mysql-db=sysbench --mysql-socket=/data/mysql/data/mysqld.sock --table-size=20000 --tables=1 --threads=4  prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Initializing worker threads...

Creating table 'sbtest1'...
Inserting 20000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
[root@qin_1 ~]# 

//在qin_2上对MariaDB做全备
[root@qin_2 ~]# /usr/local/mysql/bin/mysqldump -h10.186.64.16 -P3306 -uroot -p666666a --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --hex-blob --triggers --routines --events --all-databases > /tmp/all_db_data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@qin_2 ~]#

2. 导入全备到 MySQL 5.7.25

[root@qin_2 ~]# /usr/local/mysql/bin/mysql -uroot -p666666a -S /data/mysql/data/mysqld.sock </tmp/all_db_data.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1728 (HY000) at line 989: Cannot load from mysql.proc. The table is probably corrupted
[root@qin_2 ~]#

可以看到在导入全备时有个报错，从字面看，是 mysql.proc 这张表损坏了。
接下来我们分析下，这个报错到底是什么

3. 解决报错问题

//首先查看我们导入备份后的库表，可以看到MariaDB上的test库以及sysbench库都已经成功导入
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| sysbench           |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use sysbench
Database changed
mysql> show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1            |
+--------------------+
1 row in set (0.00 sec)

mysql> select k from sbtest1 where id=1;
+------+
| k    |
+------+
| 9974 |
+------+
1 row in set (0.01 sec)

mysql>

//然后根据报错，查看mysql.proc这张损坏的表
mysql> use mysql
Database changed
mysql> select * from proc limit 1\G
*************************** 1. row ***************************
                 db: test
               name: AddGeometryColumn
               type: PROCEDURE
      specific_name: AddGeometryColumn
           language: SQL
    sql_data_access: CONTAINS_SQL
   is_deterministic: NO
      security_type: DEFINER
         param_list: catalog varchar(64), t_schema varchar(64),
  t_name varchar(64), geometry_column varchar(64), t_srid int
            returns: 
               body: begin
 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
            definer: @
            created: 2020-05-24 11:40:41
           modified: 2020-05-24 11:40:41
           sql_mode: 
            comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
       db_collation: utf8_general_ci
          body_utf8: begin
 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
1 row in set (0.00 sec)

mysql>

##看起来似乎是正常的，不过这张表是关于存储过程的，那我们创建存储过程看下
mysql> use sysbench
Database changed
mysql> delimiter //
mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
   ->        BEGIN
   ->          SELECT COUNT(*) INTO cities FROM world.city
   ->          WHERE CountryCode = country;
   ->        END//
ERROR 1728 (HY000): Cannot load from mysql.proc. The table is probably corrupted
mysql> 

## 可以看到创建存储过程是报错的，所以这张表还是有问题的。//接下来我们对比下MariaDB 10.1.9与正常MySQL5.7.25的这张表的表结构
--MariaDB 10.1.9
MariaDB [(none)]> show create table mysql.proc\G
*************************** 1. row ***************************
      Table: proc
Create Table: CREATE TABLE `proc` (
 `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `name` char(64) NOT NULL DEFAULT '',
 `type` enum('FUNCTION','PROCEDURE') NOT NULL,
 `specific_name` char(64) NOT NULL DEFAULT '',
 `language` enum('SQL') NOT NULL DEFAULT 'SQL',
 `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
 `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
 `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
 `param_list` blob NOT NULL,
 `returns` longblob NOT NULL,
 `body` longblob NOT NULL,
 `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
 `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `body_utf8` longblob,
 PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'
1 row in set (0.00 sec)

MariaDB [(none)]> 

--MySQL5.7.25（需要另外找一个正常的数据库）
mysql> show create table mysql.proc\G
*************************** 1. row ***************************
      Table: proc
Create Table: CREATE TABLE `proc` (
 `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `name` char(64) NOT NULL DEFAULT '',
 `type` enum('FUNCTION','PROCEDURE') NOT NULL,
 `specific_name` char(64) NOT NULL DEFAULT '',
 `language` enum('SQL') NOT NULL DEFAULT 'SQL',
 `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
 `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
 `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
 `param_list` blob NOT NULL,
 `returns` longblob NOT NULL,
 `body` longblob NOT NULL,
 `definer` char(93) DEFAULT NULL,
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT NULL,
 `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `body_utf8` longblob,
 PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'
1 row in set (0.01 sec)

mysql>

##通过对比表结构发现，MySQL5.7.25的proc表的'definer'字段长度只有93，而MariaDB 10.1.9的该字段长度是141；此外，'sql_mode'字段的取值范围也不相同

//接下来就是把导入备份后损坏的proc表的表结构修改正确
mysql> alter table proc modify column definer char(93);
ERROR 1067 (42000): Invalid default value for 'modified'
mysql>
##此时又遇到报错，查看报错字段'modified'发现，该字段是个timestamp 类型，而且默认值是'0000-00-00 00:00:00'，我们知道MySQL5.7版本的sql_mode可能会限制日期全为0的值，那么我们可以在会话级别修改sql_mode值，允许插入全为0的日期
mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> set @@session.sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter table proc modify column definer char(93);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table proc modify column sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

//接下来，再次创建存储过程，发现可以成功创建了
mysql> delimiter //
mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
   ->        BEGIN
   ->          SELECT COUNT(*) INTO cities FROM world.city
   ->          WHERE CountryCode = country;
   ->        END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

三、性能测试

使用 sysbench 工具分别压测 MariaDB 10.1.9 和 MySQL 5.7.25 的读写性能。
测试以下场景：500 万行数据，64、128 线程下两者的读写性能。

//对于MariaDB10.1.9，准备500万行数据
[root@qin_1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.186.64.16 --mysql-port=3306 --mysql-user=root --mysql-password='666666a' --mysql-db=sysbench --mysql-socket=/data/mysql/data/mysqld.sock --table-size=1000000 --tables=5 --threads=4  prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Initializing worker threads...
......（略）
[root@qin_1 ~]# 

//64线程下压测一分钟
......（略）
[ 55s ] thds: 64 tps: 1241.41 qps: 22326.41 (r/w/o: 17354.73/4968.67/3.01) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 64 tps: 914.71 qps: 16479.74 (r/w/o: 12831.91/3647.84/0.00) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 64 tps: 1092.23 qps: 19650.14 (r/w/o: 15266.21/4382.92/1.00) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 64 tps: 831.65 qps: 15034.58 (r/w/o: 11701.99/3331.60/1.00) lat (ms,95%): 118.92 err/s: 1.00 reconn/s: 0.00
[ 59s ] thds: 64 tps: 704.42 qps: 12646.47 (r/w/o: 9841.83/2804.65/0.00) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 64 tps: 1179.06 qps: 20846.98 (r/w/o: 16137.76/4708.22/1.00) lat (ms,95%): 81.48 err/s: 0.00 reconn/s: 0.00
SQL statistics:
   queries performed:
       read:                            783636
       write:                           223847
       other:                           35
       total:                           1007518
   transactions:                        55960  (932.04 per sec.)
   queries:                             1007518 (16780.75 per sec.)
   ignored errors:                      14     (0.23 per sec.)
   reconnects:                          0      (0.00 per sec.)

General statistics:
   total time:                          60.0388s
   total number of events:              55960

Latency (ms):
        min:                                   15.63
        avg:                                   68.63
        max:                                  505.18
        95th percentile:                      139.85
        sum:                              3840406.15

Threads fairness:
   events (avg/stddev):           874.3750/13.87
   execution time (avg/stddev):   60.0063/0.01

[root@qin_1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.186.64.16 --mysql-port=3306 --mysql-user=root --mysql-password='666666a' --mysql-db=sysbench --mysql-socket=/data/mysql/data/mysqld.sock --table-size=1000000 --tables=5  --report-interval=1 --skip-trx=on --mysql-ignore-errors=1062,1213 --time=60 --threads=64 run

//128线程下压测一分钟
......（略）
[ 55s ] thds: 128 tps: 1276.32 qps: 23032.76 (r/w/o: 17926.48/5104.28/2.00) lat (ms,95%): 161.51 err/s: 1.00 reconn/s: 0.00
[ 56s ] thds: 128 tps: 1212.06 qps: 21824.00 (r/w/o: 16944.78/4878.22/1.00) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 128 tps: 1426.03 qps: 25716.61 (r/w/o: 20013.48/5698.14/5.00) lat (ms,95%): 139.85 err/s: 2.00 reconn/s: 0.00
[ 58s ] thds: 128 tps: 1167.45 qps: 21016.02 (r/w/o: 16347.24/4667.79/1.00) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 128 tps: 1388.10 qps: 24845.51 (r/w/o: 19346.22/5496.28/3.01) lat (ms,95%): 158.63 err/s: 2.00 reconn/s: 0.00
[ 60s ] thds: 128 tps: 1483.76 qps: 26867.84 (r/w/o: 20868.75/5996.09/3.00) lat (ms,95%): 155.80 err/s: 1.00 reconn/s: 0.00
SQL statistics:
   queries performed:
       read:                            1059114
       write:                           302452
       other:                           107
       total:                           1361673
   transactions:                        75606  (1258.46 per sec.)
   queries:                             1361673 (22664.99 per sec.)
   ignored errors:                      45     (0.75 per sec.)
   reconnects:                          0      (0.00 per sec.)

General statistics:
   total time:                          60.0770s
   total number of events:              75606

Latency (ms):
        min:                                   19.19
        avg:                                  101.62
        max:                                  413.55
        95th percentile:                      179.94
        sum:                              7682850.47

Threads fairness:
   events (avg/stddev):           590.6719/10.98
   execution time (avg/stddev):   60.0223/0.03

[root@qin_1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.186.64.16 --mysql-port=3306 --mysql-user=root --mysql-password='666666a' --mysql-db=sysbench --mysql-socket=/data/mysql/data/mysqld.sock --table-size=1000000 --tables=5  --report-interval=1 --skip-trx=on --mysql-ignore-errors=1062,1213 --time=60 --threads=128 run

//对于MySQL5.7.25，同样准备500万行数据
[root@qin_2 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.186.64.37 --mysql-port=3306 --mysql-user=root --mysql-password='666666a' --mysql-db=sysbench --mysql-socket=/data/mysql/data/mysqld.sock --table-size=1000000 --tables=5 --threads=4  prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Initializing worker threads...
......（略）
[root@qin_2 ~]# 

//64线程下压测一分钟
......（略）
[ 55s ] thds: 64 tps: 1118.02 qps: 20083.38 (r/w/o: 15598.35/4485.03/0.00) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 64 tps: 1012.74 qps: 18293.54 (r/w/o: 14238.56/4053.98/1.00) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 64 tps: 1200.80 qps: 21555.44 (r/w/o: 16772.23/4783.21/0.00) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 64 tps: 1092.11 qps: 19698.96 (r/w/o: 15318.53/4379.44/1.00) lat (ms,95%): 87.56 err/s: 1.00 reconn/s: 0.00
[ 59s ] thds: 64 tps: 1131.04 qps: 20412.79 (r/w/o: 15868.61/4541.18/3.00) lat (ms,95%): 92.42 err/s: 1.00 reconn/s: 0.00
[ 60s ] thds: 64 tps: 1048.92 qps: 18898.49 (r/w/o: 14691.83/4205.66/1.00) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00
SQL statistics:
   queries performed:
       read:                            859194
       write:                           245423
       other:                           44
       total:                           1104661
   transactions:                        61354  (1021.97 per sec.)
   queries:                             1104661 (18400.25 per sec.)
   ignored errors:                      17     (0.28 per sec.)
   reconnects:                          0      (0.00 per sec.)

General statistics:
   total time:                          60.0339s
   total number of events:              61354

Latency (ms):
        min:                                   13.42
        avg:                                   62.59
        max:                                  230.37
        95th percentile:                      101.13
        sum:                              3840353.89

Threads fairness:
   events (avg/stddev):           958.6562/11.51
   execution time (avg/stddev):   60.0055/0.01

[root@qin_2 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.186.64.37 --mysql-port=3306 --mysql-user=root --mysql-password='666666a' --mysql-db=sysbench --mysql-socket=/data/mysql/data/mysqld.sock --table-size=1000000 --tables=5  --report-interval=1 --skip-trx=on --mysql-ignore-errors=1062,1213 --time=60 --threads=64 run

//128线程下压测一分钟
......（略）
[ 55s ] thds: 128 tps: 1428.94 qps: 25702.87 (r/w/o: 19995.12/5704.75/3.00) lat (ms,95%): 134.90 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 128 tps: 1360.18 qps: 24454.18 (r/w/o: 19046.46/5406.72/1.00) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 128 tps: 1159.69 qps: 20917.50 (r/w/o: 16243.70/4670.79/3.00) lat (ms,95%): 167.44 err/s: 2.00 reconn/s: 0.00
[ 58s ] thds: 128 tps: 1215.81 qps: 21934.45 (r/w/o: 17070.23/4864.22/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 128 tps: 1168.20 qps: 21052.67 (r/w/o: 16356.84/4694.83/1.00) lat (ms,95%): 189.93 err/s: 1.00 reconn/s: 0.00
[ 60s ] thds: 128 tps: 1436.66 qps: 24754.09 (r/w/o: 19077.44/5675.64/1.00) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
SQL statistics:
   queries performed:
       read:                            1066576
       write:                           304599
       other:                           103
       total:                           1371278
   transactions:                        76150  (1268.09 per sec.)
   queries:                             1371278 (22835.22 per sec.)
   ignored errors:                      34     (0.57 per sec.)
   reconnects:                          0      (0.00 per sec.)

General statistics:
   total time:                          60.0497s
   total number of events:              76150

Latency (ms):
        min:                                   12.75
        avg:                                  100.88
        max:                                  341.79
        95th percentile:                      153.02
        sum:                              7681914.19

Threads fairness:
   events (avg/stddev):           594.9219/8.56
   execution time (avg/stddev):   60.0150/0.02

[root@qin_2 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.186.64.37 --mysql-port=3306 --mysql-user=root --mysql-password='666666a' --mysql-db=sysbench --mysql-socket=/data/mysql/data/mysqld.sock --table-size=1000000 --tables=5  --report-interval=1 --skip-trx=on --mysql-ignore-errors=1062,1213 --time=60 --threads=128 run

四、性能测试结果

从 sysbench 压测的结果来看，在相同配置的服务器以及保持重要参数一致的情况下（比如双一打开），MariaDB 10.1.9 与 MySQL 5.7.25 读写性能相差不大。