技术分享|MariaDB10.1.9迁移到MySQL5.7.25

database

作者:秦广飞

爱可生 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 1G

*************************** 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.procG

*************************** 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.procG

*************************** 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 读写性能相差不大。

五、总结

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

以上是 技术分享|MariaDB10.1.9迁移到MySQL5.7.25 的全部内容, 来源链接: utcz.com/z/534031.html

回到顶部