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

作者：秦广飞

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

本文来源：原创投稿

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

---

背景

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

一、准备环境

1. 安装 MariaDB 10.1.9

1. [root@qin_1 ~]# ls

2. anaconda-ks.cfg mariadb-10.1.9-linux-x86_64.tar.gz original-ks.cfg

3. [root@qin_1 ~]# groupadd mysql

4. [root@qin_1 ~]# useradd -g mysql mysql

5. [root@qin_1 ~]# cd /usr/local/

6. [root@qin_1 local]# tar -zxvpf /root/mariadb-10.1.9-linux-x86_64.tar.gz

7. [root@qin_1 local]# ln -s /usr/local/mariadb-10.1.9-linux-x86_64/ /usr/local/mysql

8. [root@qin_1 local]# mkdir -p /data/mysql/data

9. [root@qin_1 local]# chown -R mysql:mysql /usr/local/mysql/

10. [root@qin_1 local]# chown -R mysql:mysql /data/mysql/data/

11. [root@qin_1 local]# cd mysql/

12. [root@qin_1 mysql]# yum install libaio* jemalloc -y

13. [root@qin_1 mysql]# ./scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf

14. [root@qin_1 mysql]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

15. [root@qin_1 mysql]# /usr/local/mysql/bin/mysqladmin -uroot password '666666a'

16. [root@qin_1 mysql]# /usr/local/mysql/bin/mysql -uroot -p666666a -S /data/mysql/data/mysqld.sock

17. Welcome to the MariaDB monitor. Commands end with ; or \g.

18. Your MariaDB connection id is 5

19. Server version: 10.1.9-MariaDB-log MariaDB Server

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

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

22. MariaDB [(none)]> show databases;

23. +--------------------+

24. | Database |

25. +--------------------+

26. | information_schema |

27. | mysql |

28. | performance_schema |

29. | test |

30. +--------------------+

31. 4 rows in set (0.00 sec)

32. MariaDB [(none)]>

1. [client]

2. port = 3306

3. socket = /data/mysql/data/mysqld.sock

4. [mysqld]

5. port = 3306

6. socket = /data/mysql/data/mysqld.sock

7. basedir = /usr/local/mysql

8. datadir = /data/mysql/data

9. tmpdir = /data/mysql/data

10. ### skip-character-set-client-handshake

11. log_bin_trust_function_creators = 1

12. innodb_print_all_deadlocks = 1

13. skip-external-locking

14. skip-name-resolve

15. autocommit = 1

16. innodb_thread_concurrency = 8

17. innodb_defragment = 1

18. character_set_server = utf8

19. init_connect = 'SET NAMES utf8'

20. init_connect = 'SET collation_connection = utf8_general_ci'

21. # 从库关闭binlog

22. log-bin = binlog

23. binlog_format = ROW

24. max_binlog_size = 256M

25. expire_logs_days = 5

26. binlog_cache_size = 64M

27. #

28. server-id = 1001

29. innodb_data_home_dir = /data/mysql/data

30. innodb_data_file_path = ibdata1:100M:autoextend

31. innodb_log_group_home_dir = /data/mysql/data

32. innodb_log_file_size = 128M

33. innodb_log_buffer_size = 8M

34. # 重要参数

35. innodb_buffer_pool_size = 1G

36. innodb_flush_method = O_DIRECT

37. innodb_file_per_table = 1

38. innodb_flush_log_at_trx_commit = 1

39. # CACHES AND LIMITS #

40. query-cache-type = 0

41. query-cache-size = 0

42. open_files_limit = 65535

43. innodb_open_files = 4096

44. table_open_cache = 2000

45. thread_cache_size = 200

46. max_connections = 2000

47. max_connect_errors = 5000

48. # REPLICATION #

49. read_only = 0

50. event_scheduler = 1

51. slave-net-timeout = 60

52. slave-skip-errors = 1062

53. slave_parallel_threads = 4

54. wait_timeout = 3600

55. interactive_timeout = 3600

56. lock_wait_timeout = 600

57. innodb_lock_wait_timeout = 600

58. concurrent_insert = 2

59. key_buffer_size = 256M

60. max_allowed_packet = 64M

61. sort_buffer_size = 2M

62. read_buffer_size = 8M

63. join_buffer_size = 8M

64. read_rnd_buffer_size = 8M

65. # LOGGING #

66. log-error = /data/mysql/data/mysql-error.log

67. slow_query_log = 1

68. long_query_time = 1

69. log-queries-not-using-indexes = 1

70. slow_query_log_file = /data/mysql/data/mysql-slow.log

71. tmp_table_size = 64M

72. max_heap_table_size = 64M

73. bulk_insert_buffer_size = 64M

74. group_concat_max_len = 102400

75. [mysqldump]

76. quick

77. max_allowed_packet = 64M

78. [mysql]

79. no-auto-rehash

2. 安装 mysql 5.7.25

1. [root@qin_2 ~]# ls

2. anaconda-ks.cfg mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz original-ks.cfg

3. [root@qin_2 ~]# groupadd mysql

4. [root@qin_2 ~]# useradd -g mysql mysql

5. [root@qin_2 ~]# cd /usr/local/

6. [root@qin_2 local]# tar -xf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -C /usr/local

7. [root@qin_2 local]# mv mysql-5.7.25-linux-glibc2.12-x86_64/ mysql

8. [root@qin_2 local]# chown -R mysql:mysql mysql

9. [root@qin_2 local]# mkdir -p /data/mysql/data

10. [root@qin_2 local]# chown -R mysql:mysql /data/mysql/data/

11. [root@qin_2 local]# yum install libaio* -y

12. [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

13. [root@qin_2 local]# /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

14. [root@qin_2 local]# /usr/local/mysql/bin/mysql -uroot -S /data/mysql/data/mysqld.sock

15. Welcome to the MySQL monitor. Commands end with ; or \g.

16. Your MySQL connection id is 2

17. Server version: 5.7.25-log MySQL Community Server (GPL)

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

19. Oracle is a registered trademark of Oracle Corporation and/or its

20. affiliates. Other names may be trademarks of their respective

21. owners.

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

23. mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '666666a';

24. Query OK, 0 rows affected (0.00 sec)

25. mysql> flush privileges;

26. Query OK, 0 rows affected (0.01 sec)

27. [root@qin_2 local]# /usr/local/mysql/bin/mysql -uroot -p666666a -S /data/mysql/data/mysqld.sock

28. mysql: [Warning] Using a password on the command line interface can be insecure.

29. Welcome to the MySQL monitor. Commands end with ; or \g.

30. Your MySQL connection id is 2

31. Server version: 5.7.25-log MySQL Community Server (GPL)

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

33. Oracle is a registered trademark of Oracle Corporation and/or its

34. affiliates. Other names may be trademarks of their respective

35. owners.

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

37. mysql> show databases;

38. +--------------------+

39. | Database |

40. +--------------------+

41. | information_schema |

42. | mysql |

43. | performance_schema |

44. | sys |

45. +--------------------+

46. 4 rows in set (0.00 sec)

47. mysql>

1. [mysql]

2. no-auto-rehash

3. #default-character-set = utf8mb4

4. #tee = /data/mysql_tmp/mysql_operation.log

5. [mysqld]

6. super_read_only = 0

7. # DO NOT MODIFY, Universe will generate this part

8. port = 3306

9. server_id = 1163174063

10. basedir = /usr/local/mysql

11. datadir = /data/mysql/data

12. log_bin = /data/mysql/data/mysql-bin

13. tmpdir = /data/mysql/data

14. relay_log = /data/mysql/data/mysql-relay

15. innodb_log_group_home_dir = /data/mysql/data

16. log_error = /data/mysql/data/mysql-error.log

17. # BINLOG

18. binlog_error_action = ABORT_SERVER

19. binlog_format = row

20. binlog_rows_query_log_events = 1

21. log_slave_updates = 1

22. master_info_repository = TABLE

23. max_binlog_size = 250M

24. relay_log_info_repository = TABLE

25. relay_log_recovery = 1

26. sync_binlog = 1

27. # ENGINE

28. default_storage_engine = InnoDB

29. innodb_buffer_pool_size = 1G

30. innodb_data_file_path = ibdata1:1G:autoextend

31. innodb_file_per_table = 1

32. innodb_flush_log_at_trx_commit=1

33. innodb_flush_method = O_DIRECT

34. innodb_io_capacity = 200

35. innodb_log_buffer_size = 64M

36. innodb_log_file_size = 2G

37. innodb_log_files_in_group = 2

38. innodb_max_dirty_pages_pct = 60

39. innodb_print_all_deadlocks=1

40. #innodb_stats_on_metadata = 0

41. innodb_strict_mode = 1

42. #innodb_undo_logs = 128 #Deprecated In 5.7.19

43. innodb_undo_tablespaces=3 #Deprecated In 5.7.21

44. innodb_max_undo_log_size=4G

45. innodb_undo_log_truncate=1

46. innodb_read_io_threads = 8

47. innodb_write_io_threads = 8

48. innodb_purge_threads = 4

49. innodb_buffer_pool_load_at_startup = 1

50. innodb_buffer_pool_dump_at_shutdown = 1

51. innodb_buffer_pool_dump_pct=25

52. innodb_sort_buffer_size = 8M

53. #innodb_page_cleaners = 8

54. innodb_buffer_pool_instances = 8

55. innodb_lock_wait_timeout = 10

56. innodb_io_capacity_max = 2000

57. innodb_flush_neighbors = 1

58. #innodb_large_prefix = 1

59. innodb_thread_concurrency = 4

60. innodb_stats_persistent_sample_pages = 64

61. innodb_autoinc_lock_mode = 2

62. innodb_online_alter_log_max_size = 1G

63. innodb_open_files = 4096

64. innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G

65. innodb_rollback_segments = 128

66. innodb_numa_interleave = 1

67. # CACHE

68. key_buffer_size = 16M

69. tmp_table_size = 64M

70. max_heap_table_size = 64M

71. table_open_cache = 2000

72. query_cache_type = 0

73. query_cache_size = 0

74. max_connections = 2000

75. thread_cache_size = 200

76. open_files_limit = 65535

77. binlog_cache_size = 1M

78. join_buffer_size = 8M

79. sort_buffer_size = 2M

80. read_buffer_size = 8M

81. read_rnd_buffer_size = 8M

82. table_definition_cache = 2000

83. table_open_cache_instances = 8

84. # SLOW LOG

85. slow_query_log = 1

86. slow_query_log_file = /data/mysql/data/mysql-slow.log

87. log_slow_admin_statements = 1

88. log_slow_slave_statements = 1

89. long_query_time = 1

90. # MISC

91. log_timestamps=SYSTEM

92. lower_case_table_names = 1

93. max_allowed_packet = 64M

94. read_only = 0

95. skip_external_locking = 1

96. skip_name_resolve = 1

97. skip_slave_start = 1

98. socket = /data/mysql/data/mysqld.sock

99. pid_file = /data/mysql/data/mysqld.pid

100. disabled_storage_engines = ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB

101. log-output = TABLE,FILE

102. character_set_server = utf8mb4

103. secure_file_priv = ""

104. performance-schema-instrument ='wait/lock/metadata/sql/mdl=ON'

105. performance-schema-instrument = 'memory/% = COUNTED'

106. expire_logs_days = 7

107. max_connect_errors = 1000000

108. interactive_timeout = 1800

109. wait_timeout = 1800

110. log_bin_trust_function_creators = 1

111. ##BaseConfig

112. collation_server = utf8mb4_bin

113. explicit_defaults_for_timestamp = 1

114. transaction_isolation = READ-COMMITTED

二、迁移测试

1. 对 MariaDB 全备

1. //在qin_1上给MariaDB制造点数据

2. [root@qin_2 ~]# yum install -y sysbench

3. [root@qin_2 ~]# sysbench --version

4. sysbench 1.0.17

5. [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

6. sysbench 1.0.17 (using system LuaJIT 2.0.4)

7. Initializing worker threads...

8. Creating table 'sbtest1'...

9. Inserting 20000 records into 'sbtest1'

10. Creating a secondary index on 'sbtest1'...

11. [root@qin_1 ~]#

12. //在qin_2上对MariaDB做全备

13. [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

14. mysqldump: [Warning] Using a password on the command line interface can be insecure.

15. [root@qin_2 ~]#

2. 导入全备到 MySQL 5.7.25

1. [root@qin_2 ~]# /usr/local/mysql/bin/mysql -uroot -p666666a -S /data/mysql/data/mysqld.sock </tmp/all_db_data.sql

2. mysql: [Warning] Using a password on the command line interface can be insecure.

3. ERROR 1728 (HY000) at line 989: Cannot load from mysql.proc. The table is probably corrupted

4. [root@qin_2 ~]#

• 可以看到在导入全备时有个报错，从字面看，是 mysql.proc 这张表损坏了。

• 接下来我们分析下，这个报错到底是什么

3. 解决报错问题

1. //首先查看我们导入备份后的库表，可以看到MariaDB上的test库以及sysbench库都已经成功导入

2. mysql> show databases;

3. +--------------------+

4. | Database |

5. +--------------------+

6. | information_schema |

7. | mysql |

8. | performance_schema |

9. | sys |

10. | sysbench |

11. | test |

12. +--------------------+

13. 6 rows in set (0.00 sec)

14. mysql> use sysbench

15. Database changed

16. mysql> show tables;

17. +--------------------+

18. | Tables_in_sysbench |

19. +--------------------+

20. | sbtest1 |

21. +--------------------+

22. 1 row in set (0.00 sec)

23. mysql> select k from sbtest1 where id=1;

24. +------+

25. | k |

26. +------+

27. | 9974 |

28. +------+

29. 1 row in set (0.01 sec)

30. mysql>

31. //然后根据报错，查看mysql.proc这张损坏的表

32. mysql> use mysql

33. Database changed

34. mysql> select * from proc limit 1\G

35. *************************** 1. row ***************************

36. db: test

37. name: AddGeometryColumn

38. type: PROCEDURE

39. specific_name: AddGeometryColumn

40. language: SQL

41. sql_data_access: CONTAINS_SQL

42. is_deterministic: NO

43. security_type: DEFINER

44. param_list: catalog varchar(64), t_schema varchar(64),

45. t_name varchar(64), geometry_column varchar(64), t_srid int

46. returns:

47. body: begin

48. 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

49. definer: @

50. created: 2020-05-24 11:40:41

51. modified: 2020-05-24 11:40:41

52. sql_mode:

53. comment:

54. character_set_client: utf8

55. collation_connection: utf8_general_ci

56. db_collation: utf8_general_ci

57. body_utf8: begin

58. 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

59. 1 row in set (0.00 sec)

60. mysql>

61. ##看起来似乎是正常的，不过这张表是关于存储过程的，那我们创建存储过程看下

62. mysql> use sysbench

63. Database changed

64. mysql> delimiter //

65. mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)

66. -> BEGIN

67. -> SELECT COUNT(*) INTO cities FROM world.city

68. -> WHERE CountryCode = country;

69. -> END//

70. ERROR 1728 (HY000): Cannot load from mysql.proc. The table is probably corrupted

71. mysql>

72. ## 可以看到创建存储过程是报错的，所以这张表还是有问题的。//接下来我们对比下MariaDB 10.1.9与正常MySQL5.7.25的这张表的表结构

73. --MariaDB 10.1.9

74. MariaDB [(none)]> show create table mysql.proc\G

75. *************************** 1. row ***************************

76. Table: proc

77. Create Table: CREATE TABLE `proc` (

78. `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

79. `name` char(64) NOT NULL DEFAULT '',

80. `type` enum('FUNCTION','PROCEDURE') NOT NULL,

81. `specific_name` char(64) NOT NULL DEFAULT '',

82. `language` enum('SQL') NOT NULL DEFAULT 'SQL',

83. `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',

84. `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',

85. `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',

86. `param_list` blob NOT NULL,

87. `returns` longblob NOT NULL,

88. `body` longblob NOT NULL,

89. `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

90. `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

91. `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

92. `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 '',

93. `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

94. `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

95. `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

96. `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

97. `body_utf8` longblob,

98. PRIMARY KEY (`db`,`name`,`type`)

99. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'

100. 1 row in set (0.00 sec)

101. MariaDB [(none)]>

102. --MySQL5.7.25（需要另外找一个正常的数据库）

103. mysql> show create table mysql.proc\G

104. *************************** 1. row ***************************

105. Table: proc

106. Create Table: CREATE TABLE `proc` (

107. `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

108. `name` char(64) NOT NULL DEFAULT '',

109. `type` enum('FUNCTION','PROCEDURE') NOT NULL,

110. `specific_name` char(64) NOT NULL DEFAULT '',

111. `language` enum('SQL') NOT NULL DEFAULT 'SQL',

112. `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',

113. `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',

114. `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',

115. `param_list` blob NOT NULL,

116. `returns` longblob NOT NULL,

117. `body` longblob NOT NULL,

118. `definer` char(93) DEFAULT NULL,

119. `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

120. `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

121. `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,

122. `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

123. `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

124. `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

125. `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

126. `body_utf8` longblob,

127. PRIMARY KEY (`db`,`name`,`type`)

128. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'

129. 1 row in set (0.01 sec)

130. mysql>

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

132. //接下来就是把导入备份后损坏的proc表的表结构修改正确

133. mysql> alter table proc modify column definer char(93);

134. ERROR 1067 (42000): Invalid default value for 'modified'

135. mysql>

136. ##此时又遇到报错，查看报错字段'modified'发现，该字段是个timestamp 类型，而且默认值是'0000-00-00 00:00:00'，我们知道MySQL5.7版本的sql_mode可能会限制日期全为0的值，那么我们可以在会话级别修改sql_mode值，允许插入全为0的日期

137. mysql> show variables like '%sql_mode%';

138. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

139. | Variable_name | Value |

140. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

141. | 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 |

142. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

143. 1 row in set (0.01 sec)

144. mysql> set @@session.sql_mode='';

145. Query OK, 0 rows affected, 1 warning (0.00 sec)

146. mysql> alter table proc modify column definer char(93);

147. Query OK, 2 rows affected (0.01 sec)

148. Records: 2 Duplicates: 0 Warnings: 0

149. 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');

150. Query OK, 2 rows affected (0.00 sec)

151. Records: 2 Duplicates: 0 Warnings: 0

152. mysql>

153. //接下来，再次创建存储过程，发现可以成功创建了

154. mysql> delimiter //

155. mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)

156. -> BEGIN

157. -> SELECT COUNT(*) INTO cities FROM world.city

158. -> WHERE CountryCode = country;

159. -> END//

160. Query OK, 0 rows affected (0.00 sec)

161. mysql> delimiter ;

三、性能测试

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

1. //对于MariaDB10.1.9，准备500万行数据

2. [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

3. sysbench 1.0.17 (using system LuaJIT 2.0.4)

4. Initializing worker threads...

5. ......（略）

6. [root@qin_1 ~]#

7. //64线程下压测一分钟

8. ......（略）

9. [ 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

10. [ 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

11. [ 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

12. [ 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

13. [ 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

14. [ 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

15. SQL statistics:

16. queries performed:

17. read: 783636

18. write: 223847

19. other: 35

20. total: 1007518

21. transactions: 55960 (932.04 per sec.)

22. queries: 1007518 (16780.75 per sec.)

23. ignored errors: 14 (0.23 per sec.)

24. reconnects: 0 (0.00 per sec.)

25. General statistics:

26. total time: 60.0388s

27. total number of events: 55960

28. Latency (ms):

29. min: 15.63

30. avg: 68.63

31. max: 505.18

32. 95th percentile: 139.85

33. sum: 3840406.15

34. Threads fairness:

35. events (avg/stddev): 874.3750/13.87

36. execution time (avg/stddev): 60.0063/0.01

37. [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

38. //128线程下压测一分钟

39. ......（略）

40. [ 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

41. [ 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

42. [ 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

43. [ 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

44. [ 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

45. [ 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

46. SQL statistics:

47. queries performed:

48. read: 1059114

49. write: 302452

50. other: 107

51. total: 1361673

52. transactions: 75606 (1258.46 per sec.)

53. queries: 1361673 (22664.99 per sec.)

54. ignored errors: 45 (0.75 per sec.)

55. reconnects: 0 (0.00 per sec.)

56. General statistics:

57. total time: 60.0770s

58. total number of events: 75606

59. Latency (ms):

60. min: 19.19

61. avg: 101.62

62. max: 413.55

63. 95th percentile: 179.94

64. sum: 7682850.47

65. Threads fairness:

66. events (avg/stddev): 590.6719/10.98

67. execution time (avg/stddev): 60.0223/0.03

68. [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

69. //对于MySQL5.7.25，同样准备500万行数据

70. [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

71. sysbench 1.0.17 (using system LuaJIT 2.0.4)

72. Initializing worker threads...

73. ......（略）

74. [root@qin_2 ~]#

75. //64线程下压测一分钟

76. ......（略）

77. [ 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

78. [ 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

79. [ 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

80. [ 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

81. [ 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

82. [ 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

83. SQL statistics:

84. queries performed:

85. read: 859194

86. write: 245423

87. other: 44

88. total: 1104661

89. transactions: 61354 (1021.97 per sec.)

90. queries: 1104661 (18400.25 per sec.)

91. ignored errors: 17 (0.28 per sec.)

92. reconnects: 0 (0.00 per sec.)

93. General statistics:

94. total time: 60.0339s

95. total number of events: 61354

96. Latency (ms):

97. min: 13.42

98. avg: 62.59

99. max: 230.37

100. 95th percentile: 101.13

101. sum: 3840353.89

102. Threads fairness:

103. events (avg/stddev): 958.6562/11.51

104. execution time (avg/stddev): 60.0055/0.01

105. [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

106. //128线程下压测一分钟

107. ......（略）

108. [ 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

109. [ 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

110. [ 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

111. [ 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

112. [ 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

113. [ 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

114. SQL statistics:

115. queries performed:

116. read: 1066576

117. write: 304599

118. other: 103

119. total: 1371278

120. transactions: 76150 (1268.09 per sec.)

121. queries: 1371278 (22835.22 per sec.)

122. ignored errors: 34 (0.57 per sec.)

123. reconnects: 0 (0.00 per sec.)

124. General statistics:

125. total time: 60.0497s

126. total number of events: 76150

127. Latency (ms):

128. min: 12.75

129. avg: 100.88

130. max: 341.79

131. 95th percentile: 153.02

132. sum: 7681914.19

133. Threads fairness:

134. events (avg/stddev): 594.9219/8.56

135. execution time (avg/stddev): 60.0150/0.02

136. [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，并能保证迁移后性能不下降或者略有上升。

社区近期动态

 点一下“阅读原文”了解更多资讯