作者：秦广飞

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

本文来源：原创投稿

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

背景

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

一、准备环境

1. 安装 MariaDB 10.1.9

[root@qin_1 ~]# lsanaconda-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.sockWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 5Server version: 10.1.9-MariaDB-log MariaDB ServerCopyright (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                           = 3306socket                         = /data/mysql/data/mysqld.sock[mysqld]port                           = 3306socket                         = /data/mysql/data/mysqld.sockbasedir                        = /usr/local/mysqldatadir                        = /data/mysql/datatmpdir                         = /data/mysql/data### skip-character-set-client-handshakelog_bin_trust_function_creators = 1innodb_print_all_deadlocks      = 1skip-external-lockingskip-name-resolveautocommit                     = 1innodb_thread_concurrency      = 8innodb_defragment              = 1character_set_server           = utf8init_connect                   = 'SET NAMES utf8'init_connect                   = 'SET collation_connection = utf8_general_ci'# 从库关闭binloglog-bin                        = binlogbinlog_format                  = ROWmax_binlog_size                = 256Mexpire_logs_days               = 5binlog_cache_size              = 64M#server-id                      = 1001innodb_data_home_dir           = /data/mysql/datainnodb_data_file_path          = ibdata1:100M:autoextendinnodb_log_group_home_dir      = /data/mysql/datainnodb_log_file_size           = 128Minnodb_log_buffer_size         = 8M# 重要参数innodb_buffer_pool_size        = 1Ginnodb_flush_method            = O_DIRECTinnodb_file_per_table          = 1innodb_flush_log_at_trx_commit = 1# CACHES AND LIMITS #query-cache-type               = 0query-cache-size               = 0open_files_limit               = 65535innodb_open_files              = 4096table_open_cache               = 2000thread_cache_size              = 200max_connections                = 2000max_connect_errors             = 5000# REPLICATION #read_only                      = 0event_scheduler                = 1slave-net-timeout              = 60slave-skip-errors              = 1062slave_parallel_threads         = 4wait_timeout                   = 3600interactive_timeout            = 3600lock_wait_timeout              = 600innodb_lock_wait_timeout       = 600concurrent_insert              = 2key_buffer_size                = 256Mmax_allowed_packet             = 64Msort_buffer_size               = 2Mread_buffer_size               = 8Mjoin_buffer_size               = 8Mread_rnd_buffer_size           = 8M# LOGGING #log-error                      = /data/mysql/data/mysql-error.logslow_query_log                 = 1long_query_time                = 1log-queries-not-using-indexes = 1slow_query_log_file            = /data/mysql/data/mysql-slow.logtmp_table_size                 = 64Mmax_heap_table_size            = 64Mbulk_insert_buffer_size        = 64Mgroup_concat_max_len           = 102400[mysqldump]quickmax_allowed_packet             = 64M[mysql]no-auto-rehash

2. 安装 mysql 5.7.25

[root@qin_2 ~]# lsanaconda-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.sockWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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.sockmysql: [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 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 partport = 3306server_id = 1163174063basedir = /usr/local/mysqldatadir = /data/mysql/datalog_bin = /data/mysql/data/mysql-bintmpdir = /data/mysql/datarelay_log = /data/mysql/data/mysql-relayinnodb_log_group_home_dir = /data/mysql/datalog_error = /data/mysql/data/mysql-error.log# BINLOGbinlog_error_action = ABORT_SERVERbinlog_format = rowbinlog_rows_query_log_events = 1log_slave_updates = 1master_info_repository = TABLEmax_binlog_size = 250Mrelay_log_info_repository = TABLErelay_log_recovery = 1sync_binlog = 1# ENGINEdefault_storage_engine = InnoDBinnodb_buffer_pool_size = 1Ginnodb_data_file_path = ibdata1:1G:autoextendinnodb_file_per_table = 1innodb_flush_log_at_trx_commit=1innodb_flush_method = O_DIRECTinnodb_io_capacity = 200innodb_log_buffer_size = 64Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 2innodb_max_dirty_pages_pct = 60innodb_print_all_deadlocks=1#innodb_stats_on_metadata = 0innodb_strict_mode = 1#innodb_undo_logs = 128 #Deprecated In 5.7.19innodb_undo_tablespaces=3    #Deprecated In 5.7.21innodb_max_undo_log_size=4Ginnodb_undo_log_truncate=1innodb_read_io_threads = 8innodb_write_io_threads = 8innodb_purge_threads = 4innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_buffer_pool_dump_pct=25innodb_sort_buffer_size = 8M#innodb_page_cleaners = 8innodb_buffer_pool_instances = 8innodb_lock_wait_timeout = 10innodb_io_capacity_max = 2000innodb_flush_neighbors = 1#innodb_large_prefix = 1innodb_thread_concurrency = 4innodb_stats_persistent_sample_pages = 64innodb_autoinc_lock_mode = 2innodb_online_alter_log_max_size = 1Ginnodb_open_files = 4096innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50Ginnodb_rollback_segments = 128innodb_numa_interleave = 1# CACHEkey_buffer_size = 16Mtmp_table_size = 64Mmax_heap_table_size = 64Mtable_open_cache = 2000query_cache_type = 0query_cache_size = 0max_connections = 2000thread_cache_size = 200open_files_limit = 65535binlog_cache_size = 1Mjoin_buffer_size = 8Msort_buffer_size = 2Mread_buffer_size = 8Mread_rnd_buffer_size = 8Mtable_definition_cache = 2000table_open_cache_instances = 8# SLOW LOGslow_query_log = 1slow_query_log_file = /data/mysql/data/mysql-slow.loglog_slow_admin_statements = 1log_slow_slave_statements = 1long_query_time = 1# MISClog_timestamps=SYSTEMlower_case_table_names = 1max_allowed_packet = 64Mread_only = 0skip_external_locking = 1skip_name_resolve = 1skip_slave_start = 1socket = /data/mysql/data/mysqld.sockpid_file = /data/mysql/data/mysqld.piddisabled_storage_engines = ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDBlog-output = TABLE,FILEcharacter_set_server = utf8mb4secure_file_priv = ""performance-schema-instrument ='wait/lock/metadata/sql/mdl=ON'performance-schema-instrument = 'memory/% = COUNTED'expire_logs_days = 7max_connect_errors = 1000000interactive_timeout = 1800wait_timeout = 1800log_bin_trust_function_creators = 1##BaseConfigcollation_server = utf8mb4_binexplicit_defaults_for_timestamp = 1transaction_isolation = READ-COMMITTED

二、迁移测试

1. 对 MariaDB 全备

//在qin_1上给MariaDB制造点数据[root@qin_2 ~]# yum install -y sysbench[root@qin_2 ~]# sysbench --versionsysbench 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  preparesysbench 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.sqlmysqldump: [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 sysbenchDatabase changedmysql> 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 mysqlDatabase changedmysql> 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: utf8collation_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; end1 row in set (0.00 sec)mysql>##看起来似乎是正常的，不过这张表是关于存储过程的，那我们创建存储过程看下mysql> use sysbenchDatabase changedmysql> 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 corruptedmysql> ## 可以看到创建存储过程是报错的，所以这张表还是有问题的。//接下来我们对比下MariaDB 10.1.9与正常MySQL5.7.25的这张表的表结构--MariaDB 10.1.9MariaDB [(none)]> show create table mysql.proc\G*************************** 1. row ***************************      Table: procCreate 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: procCreate 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: 0mysql> 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: 0mysql>//接下来，再次创建存储过程，发现可以成功创建了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  preparesysbench 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.00SQL 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:              55960Latency (ms):        min:                                   15.63        avg:                                   68.63        max:                                  505.18        95th percentile:                      139.85        sum:                              3840406.15Threads 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.00SQL 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:              75606Latency (ms):        min:                                   19.19        avg:                                  101.62        max:                                  413.55        95th percentile:                      179.94        sum:                              7682850.47Threads 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  preparesysbench 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.00SQL 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:              61354Latency (ms):        min:                                   13.42        avg:                                   62.59        max:                                  230.37        95th percentile:                      101.13        sum:                              3840353.89Threads 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.00SQL 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:              76150Latency (ms):        min:                                   12.75        avg:                                  100.88        max:                                  341.79        95th percentile:                      153.02        sum:                              7681914.19Threads 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 读写性能相差不大。

五、总结

经测试，MariaDB 10.1.9 可以正常迁移到 MySQL 5.7.25，并能保证迁移后性能不下降或者略有上升。