Download as PDF, PPTX
![• MariaDB • Password Validation Plugin API • Two plugins simple_password_check MariaDB [performance_schema]> show variables like ’simple_password_check%’; +–––––––––––––––––––––––––––––––––––––––––+–––––––+ | Variable_name | Value | +–––––––––––––––––––––––––––––––––––––––––+–––––––+ | simple_password_check_digits | 1 | | simple_password_check_letters_same_case | 1 | | simple_password_check_minimal_length | 8 | | simple_password_check_other_characters | 1 | +–––––––––––––––––––––––––––––––––––––––––+–––––––+ 4 rows in set (0.001 sec) Password Validation 12](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-28-2048.jpg)
![• MariaDB • Password Validation Plugin API • Two plugins cracklib_password_check MariaDB [performance_schema]> show variables like ’cracklib%’; +––––––––––––––––––––––––––––––––––––+–––––––––––––––––––––––––––––––––––+ | Variable_name | Value | +––––––––––––––––––––––––––––––––––––+–––––––––––––––––––––––––––––––––––+ | cracklib_password_check_dictionary | /var/cache/cracklib/cracklib_dict | +––––––––––––––––––––––––––––––––––––+–––––––––––––––––––––––––––––––––––+ 1 row in set (0.001 sec) Password Validation 12](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-29-2048.jpg)
![• Syntax is same • MariaDB• Stored in mysql user roles_mapping MariaDB [information_schema]> select * from mysql.roles_mappingG *************************** 1. row *************************** Host: localhost User: root Role: my_role Admin_option: Y *************************** 2. row *************************** Host: % User: sveta Role: my_role Admin_option: N 2 rows in set (0.001 sec) Roles 13](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-35-2048.jpg)
![• MariaDB MariaDB [test]> analyze select * from ol -> where thread_id=10432 and site_id != 9939 order by id limit 3G *************************** 1. row *************************** ... type: index possible_keys: thread_id key: PRIMARY key_len: 4 ref: const rows: 100000 r_rows: 100000.00 filtered: 8.96 r_filtered: 0.00 Extra: Using where ANALYZE 20](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-52-2048.jpg)
![• MariaDB MariaDB [(none)]> show processlistG *************************** 1. row *************************** Id: 3 User: root Host: localhost:51126 db: test Command: Query Time: 2 State: User sleep Info: select sleep(30) Progress: 0.000 EXPLAIN for Running Thread 21](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-55-2048.jpg)
![• MariaDB MariaDB [(none)]> show explain for 3G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: No tables used 1 row in set, 1 warning (25.718 sec) EXPLAIN for Running Thread 21](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-56-2048.jpg)
![• MariaDB MariaDB [(none)]> show processlistG *************************** 1. row *************************** Id: 3 User: root Host: localhost:51126 db: test Command: Query Time: 2 State: User sleep Info: select sleep(40) Progress: 0.000 ... MariaDB [(none)]> show explain for 3G ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction EXPLAIN for Running Thread 21](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-57-2048.jpg)
![• MariaDB • In Information Schema MariaDB [information_schema]> select stage, max_stage, progress -> from processlist where info like ’alter table%’; +–––––––+–––––––––––+––––––––––+ | stage | max_stage | progress | +–––––––+–––––––––––+––––––––––+ | 1 | 2 | 46.881 | ... | 1 | 2 | 68.103 | ... | 1 | 2 | 83.452 | +–––––––+–––––––––––+––––––––––+ 1 row in set (0.001 sec) Progress for ALTER TABLE 29](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-71-2048.jpg)
![• MariaDB • In SHOW PROCESSLIST MariaDB [information_schema]> show processlistG *************************** 1. row *************************** Id: 5 User: root Host: localhost:53616 db: employees Command: Query Time: 4 State: copy to tmp table Info: alter table salaries engine=innodb Progress: 31.510 Progress for ALTER TABLE 29](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-72-2048.jpg)
![• MariaDB • In the supporting client MariaDB [employees]> alter table salaries engine=innodb; Stage: 1 of 2 ’copy to tmp table’ 73.4% of stage done Progress for ALTER TABLE 29](https://image.slidesharecdn.com/migration-201017130432/75/How-to-migrate-from-MySQL-to-MariaDB-without-tears-73-2048.jpg)
Uploaded bySveta Smirnova
How to migrate from MySQL to MariaDB without tears
The document provides a comprehensive guide on migrating from MySQL to MariaDB, detailing compatibility checks, initial setup, and key differences in features and functionalities. It covers aspects such as authentication methods, replication mechanisms, and specific SQL differences between the two database systems. Additionally, it addresses diagnostics, including performance schema and user management changes during migration.
How to migrate from MySQL to MariaDB without tears
- 1. How to Migratefrom MySQL to MariaDB without Tears September 14-16, 2020 Sveta Smirnova
- 2. • MySQL SupportEngineer • Author of • MySQL Troubleshooting • JSON UDF functions • FILTER clause for MySQL • Speaker • Percona Live, OOW, Fosdem, DevConf, HighLoad... Sveta Smirnova 2
- 3. •Initial Setup •Authentication •Replication •SQL Differences •FunctionalDifferences •Diagnostics Table of Contents 3
- 4. • Started asfork of MySQL MariaDB 4
- 5. • Started asfork of MySQL • Independent product MariaDB 4
- 6. • Started asfork of MySQL • Independent product • Hundreds of unique features MariaDB 4
- 7. • Started asfork of MySQL • Independent product • Hundreds of unique features • Important improvements are backported • from MySQL MariaDB 4
- 8.
- 9. • Check versioncompatibility MySQL MariaDB 5.1 5.1, 5.2, 5.3 5.5 5.5 5.6 10.0, 10.1 5.7 10.2, 10.3, 10.4, 10.5 8.0 - Drop-in Replacement 6
- 10. • Check versioncompatibility • Stop MySQL Server Drop-in Replacement 6
- 11. • Check versioncompatibility • Stop MySQL Server • Install MariaDB Drop-in Replacement 6
- 12. • Check versioncompatibility • Stop MySQL Server • Install MariaDB • Point MariaDB to MySQL datadir Drop-in Replacement 6
- 13. • Check versioncompatibility • Stop MySQL Server • Install MariaDB • Point MariaDB to MySQL datadir • Start MariaDB Drop-in Replacement 6
- 14. • Check versioncompatibility • Stop MySQL Server • Install MariaDB • Point MariaDB to MySQL datadir • Start MariaDB • Run mariadb-upgrade Drop-in Replacement 6
- 15. • Before upgrade $ls -w100 data/mysql/ columns_priv.frm gtid_executed.ibd proc.MYD slow_log.CSV columns_priv.MYD help_category.frm proc.MYI slow_log.frm columns_priv.MYI help_category.ibd procs_priv.frm tables_priv.frm db.frm help_keyword.frm procs_priv.MYD tables_priv.MYD db.MYD help_keyword.ibd procs_priv.MYI tables_priv.MYI db.MYI help_relation.frm proxies_priv.frm time_zone.frm db.opt help_relation.ibd proxies_priv.MYD time_zone.ibd engine_cost.frm help_topic.frm proxies_priv.MYI time_zone_leap_second.frm engine_cost.ibd help_topic.ibd server_cost.frm time_zone_leap_second.ibd event.frm innodb_index_stats.frm server_cost.ibd time_zone_name.frm event.MYD innodb_index_stats.ibd servers.frm time_zone_name.ibd event.MYI innodb_table_stats.frm servers.ibd time_zone_transition.frm func.frm innodb_table_stats.ibd slave_master_info.frm time_zone_transition.ibd ... System Database Changes 7
- 16. • After upgrade $ls -w100 data/mysql/ columns_priv.frm gtid_executed.ibd proc.frm slow_log.frm columns_priv.MAD gtid_slave_pos.frm proc.MAD tables_priv.frm columns_priv.MAI gtid_slave_pos.ibd proc.MAI tables_priv.MAD column_stats.frm help_category.frm procs_priv.frm tables_priv.MAI column_stats.MAD help_category.MAD procs_priv.MAD table_stats.frm column_stats.MAI help_category.MAI procs_priv.MAI table_stats.MAD db.frm help_keyword.frm proxies_priv.frm table_stats.MAI db.MAD help_keyword.MAD proxies_priv.MAD time_zone.frm db.MAI help_keyword.MAI proxies_priv.MAI time_zone_leap_second.frm db.opt help_relation.frm roles_mapping.frm time_zone_leap_second.MAD engine_cost.frm help_relation.MAD roles_mapping.MAD time_zone_leap_second.MAI engine_cost.ibd help_relation.MAI roles_mapping.MAI time_zone.MAD event.frm help_topic.frm server_cost.frm time_zone.MAI ... System Database Changes 7
- 17. • Specific MariaDBfeatures • • Storage engines • SQL statements • Options • Just use them Differences 8
- 18. • Specific MariaDBfeatures • Specific MySQL features • • SQL statements • Plugins • They are missed Differences 8
- 19. • Specific MariaDBfeatures • Specific MySQL features • Features, present in both products • • Working differently • Our focus today Differences 8
- 20.
- 21. • MySQL • –initialize • mysql_install_dbdeprecated, removed in 8.0 Initial Setup 10
- 22. • MySQL • –initialize • mysql_install_dbdeprecated, removed in 8.0 • MariaDB • Only mysql_install_db Initial Setup 10
- 23. • Supported plugins • MySQL Native CachingSHA-2 - Default in 8.0 SHA-256 Cleartext No-login Socket Peer-Credential Authentication Plugins 11
- 24. • Supported plugins • MariaDB mysql_native_password mysql_old_password SHA-256- Client only! ed25519 GSSAPI PAM Named Pipe Unix Socket Authentication Plugins 11
- 25. • Supported plugins •User management SQL is same Authentication Plugins 11
- 26. • Supported plugins •User management SQL is same • MariaDB supports multiple plugins per account Authentication Plugins 11
- 27. • MySQL • validate_password plugin Deprecated! • validate_passwordcomponent mysql> SHOW VARIABLES LIKE ’validate_password%’; +––––––––––––––––––––––––––––––––––––––+––––––––+ | Variable_name | Value | +––––––––––––––––––––––––––––––––––––––+––––––––+ | validate_password_check_user_name | ON | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +––––––––––––––––––––––––––––––––––––––+––––––––+ Password Validation 12
- 28. • MariaDB • PasswordValidation Plugin API • Two plugins simple_password_check MariaDB [performance_schema]> show variables like ’simple_password_check%’; +–––––––––––––––––––––––––––––––––––––––––+–––––––+ | Variable_name | Value | +–––––––––––––––––––––––––––––––––––––––––+–––––––+ | simple_password_check_digits | 1 | | simple_password_check_letters_same_case | 1 | | simple_password_check_minimal_length | 8 | | simple_password_check_other_characters | 1 | +–––––––––––––––––––––––––––––––––––––––––+–––––––+ 4 rows in set (0.001 sec) Password Validation 12
- 29. • MariaDB • PasswordValidation Plugin API • Two plugins cracklib_password_check MariaDB [performance_schema]> show variables like ’cracklib%’; +––––––––––––––––––––––––––––––––––––+–––––––––––––––––––––––––––––––––––+ | Variable_name | Value | +––––––––––––––––––––––––––––––––––––+–––––––––––––––––––––––––––––––––––+ | cracklib_password_check_dictionary | /var/cache/cracklib/cracklib_dict | +––––––––––––––––––––––––––––––––––––+–––––––––––––––––––––––––––––––––––+ 1 row in set (0.001 sec) Password Validation 12
- 30. • MySQL • MariaDB •Password expiration works same way Password Validation 12
- 31. • Syntax issame Roles 13
- 32. • Syntax issame • MySQL • Details are in Information Schema ADMINISTRABLE_ROLE_AUTHORIZATIONS APPLICABLE_ROLES ENABLED_ROLES ROLE_COLUMN_GRANTS ROLE_ROUTINE_GRANTS ROLE_TABLE_GRANTS Roles 13
- 33. • Syntax issame • MySQL • Stored in mysql user role_edges mysql> select * from mysql.role_edgesG *************************** 1. row *************************** FROM_HOST: % FROM_USER: my_role TO_HOST: % TO_USER: sveta WITH_ADMIN_OPTION: N 1 row in set (0.00 sec) default_roles Roles 13
- 34. • Syntax issame • MariaDB • Details are in Information Schema APPLICABLE_ROLES ENABLED_ROLES Roles 13
- 35. • Syntax issame • MariaDB• Stored in mysql user roles_mapping MariaDB [information_schema]> select * from mysql.roles_mappingG *************************** 1. row *************************** Host: localhost User: root Role: my_role Admin_option: Y *************************** 2. row *************************** Host: % User: sveta Role: my_role Admin_option: N 2 rows in set (0.001 sec) Roles 13
- 36. • Syntax issame • MariaDB • The role automatically available for the grantor Roles 13
- 37.
- 38. • Compatible • Canwork any way around No GTIDs 15
- 39. • Compatible • Canwork any way around • Unless data types are incompatible • Before 8.0! No GTIDs 15
- 40. • Implementation isdifferent • MariaDB understands MySQL GTIDs • MySQL does not accept MariaDB GTIDs • Only replication from MySQL is possible! GTIDs 16
- 41. • Different concurrencycontrol Multi-threaded Replication 17
- 42. • Different concurrencycontrol • MySQL: slave_parallel_workers Multi-threaded Replication 17
- 43. • Different concurrencycontrol • MySQL: slave_parallel_workers • MySQL: slave_parallel_type=DATABASE | LOGICAL_CLOCK • MYSQL: binlog_transaction_dependency_tracking = COMMIT_ORDER | WRITESET | WRITESET_SESSION Multi-threaded Replication 17
- 44. • Different concurrencycontrol • MariaDB: slave_parallel_threads Multi-threaded Replication 17
- 45. • Different concurrencycontrol • MariaDB: slave_parallel_threads • MariaDB: slave_parallel_max_queued Multi-threaded Replication 17
- 46. • Different concurrencycontrol • MariaDB: slave_parallel_threads • MariaDB: slave_parallel_max_queued • MariaDB: slave_domain_parallel_threads Multi-threaded Replication 17
- 47. • Different concurrencycontrol • MariaDB: slave_parallel_threads • MariaDB: slave_parallel_max_queued • MariaDB: slave_domain_parallel_threads • MariaDB: slave_parallel_mode=optimistic | conservative | aggressive | minimal | none Multi-threaded Replication 17
- 48. • Different syntaxfor the source name • CHANGE MASTER • MySQL CHANGE MASTER TO MASTER_HOST=’source1’ FOR CHANNEL ’source_1’; • MariaDB CHANGE MASTER ’source_1’ TO MASTER_HOST=’source1’; Multi-source Replication 18
- 49. • Different syntaxfor the source name • Slave status • MySQL SHOW SLAVE STATUS FOR CHANNEL ’source1’; SELECT * FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME=’source_1’; Default is for all channels • MariaDB SHOW SLAVE ’source_1’ STATUS SHOW ALL SLAVES STATUS Default is default connection Multi-source Replication 18
- 50.
- 51. • MySQL mysql> explainanalyze select * from ol where thread_id=10432 and site_id != 9939 -> order by id limit 3G *************************** 1. row *************************** EXPLAIN: -> Limit: 3 row(s) (actual time=364.792..364.792 rows=0 loops=1) -> Filter: ((ol.thread_id = 10432) and (ol.site_id <> 9939)) (cost=0.06 rows=3) (actual time=364.789..364.789 rows=0 loops=1) -> Index scan on ol using PRIMARY (cost=0.06 rows=33) (actual time=0.417..337.585 rows=100000 loops=1) ANALYZE 20
- 52. • MariaDB MariaDB [test]>analyze select * from ol -> where thread_id=10432 and site_id != 9939 order by id limit 3G *************************** 1. row *************************** ... type: index possible_keys: thread_id key: PRIMARY key_len: 4 ref: const rows: 100000 r_rows: 100000.00 filtered: 8.96 r_filtered: 0.00 Extra: Using where ANALYZE 20
- 53. • MySQL mysql> showprocesslistG *************************** 1. row *************************** Id: 2 User: root Host: localhost:41352 db: test Command: Query Time: 37 State: User sleep Info: select sleep(100) Rows_sent: 0 Rows_examined: 0 ... EXPLAIN for Running Thread 21
- 54. • MySQL mysql> explainfor connection 2G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used 1 row in set (0.00 sec) EXPLAIN for Running Thread 21
- 55. • MariaDB MariaDB [(none)]>show processlistG *************************** 1. row *************************** Id: 3 User: root Host: localhost:51126 db: test Command: Query Time: 2 State: User sleep Info: select sleep(30) Progress: 0.000 EXPLAIN for Running Thread 21
- 56. • MariaDB MariaDB [(none)]>show explain for 3G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: No tables used 1 row in set, 1 warning (25.718 sec) EXPLAIN for Running Thread 21
- 57. • MariaDB MariaDB [(none)]>show processlistG *************************** 1. row *************************** Id: 3 User: root Host: localhost:51126 db: test Command: Query Time: 2 State: User sleep Info: select sleep(40) Progress: 0.000 ... MariaDB [(none)]> show explain for 3G ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction EXPLAIN for Running Thread 21
- 58. • MySQL • Only histograms •Collected on demand Histograms and Table-Independent Statistics 22
- 59. • MySQL • Only histograms •Collected on demand • MariaDB • Histograms collected and used by default • Other Collected on demand Used by default Histograms and Table-Independent Statistics 22
- 60.
- 61. • MySQL • Specialdata type • Functions, not available in MariaDB JSON_OVERLAPS JSON_PRETTY JSON_STORAGE_FREE JSON_STORAGE_SIZE JSON_TABLE JSON 24
- 62. • MySQL • MariaDB •Data type JSON mapped to LONGTEXT CHECK (JSON_VALID(...)) automatically enabled if JSON alias is used • Functions, not available in MySQL JSON_COMPACT JSON_DETAILED JSON_EXISTS JSON_LOOSE JSON_QUERY JSON_VALUE JSON 24
- 63. • MySQL • MariaDB •Replication • Statement-based works fine • Row-based Convert MySQL JSON data type to LONGTEXT JSON 24
- 64. • "SQL withGeometry Types" of OGC • Both MySQL and MariaDB • Not all MySQL GIS functions are in MariaDB GIS 25
- 65. • MySQL • Full Unicodesupport since 8.0.4 ICU library • Extended regex syntax Regular Expressions 26
- 66. • MySQL • Full Unicodesupport since 8.0.4 ICU library • Extended regex syntax • MariaDB • Full Unicode support since 10.5 • PCRE syntax Regular Expressions 26
- 67. • MySQL • Full Unicodesupport since 8.0.4 ICU library • Extended regex syntax • MariaDB • Full Unicode support since 10.5 • PCRE syntax • REGEXP_LIKE only in MySQL Regular Expressions 26
- 68. • Character setgb18030 only in MySQL • MariaDB has more collations • MariaDB supports not all MySQL collations Localization 27
- 69.
- 70. • MySQL • In PerformanceSchema mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED -> FROM performance_schema.events_stages_current where event_name like ’%alter%’; +––––––––––––––––––––––––––––––––––––––––––––––––––––––+––––––––––––––––+––––––––––––––––+ | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | +––––––––––––––––––––––––––––––––––––––––––––––––––––––+––––––––––––––––+––––––––––––––––+ | stage/innodb/alter table (read PK and internal sort) | 488 | 8512 | ... | stage/innodb/alter table (read PK and internal sort) | 9396 | 9396 | +––––––––––––––––––––––––––––––––––––––––––––––––––––––+––––––––––––––––+––––––––––––––––+ 1 row in set (0.00 sec) Progress for ALTER TABLE 29
- 71. • MariaDB • In InformationSchema MariaDB [information_schema]> select stage, max_stage, progress -> from processlist where info like ’alter table%’; +–––––––+–––––––––––+––––––––––+ | stage | max_stage | progress | +–––––––+–––––––––––+––––––––––+ | 1 | 2 | 46.881 | ... | 1 | 2 | 68.103 | ... | 1 | 2 | 83.452 | +–––––––+–––––––––––+––––––––––+ 1 row in set (0.001 sec) Progress for ALTER TABLE 29
- 72. • MariaDB • InSHOW PROCESSLIST MariaDB [information_schema]> show processlistG *************************** 1. row *************************** Id: 5 User: root Host: localhost:53616 db: employees Command: Query Time: 4 State: copy to tmp table Info: alter table salaries engine=innodb Progress: 31.510 Progress for ALTER TABLE 29
- 73. • MariaDB • Inthe supporting client MariaDB [employees]> alter table salaries engine=innodb; Stage: 1 of 2 ’copy to tmp table’ 73.4% of stage done Progress for ALTER TABLE 29
- 74. • Tables, whichMariaDB misses • Locks data_lock_waits data_locks MariaDB: in Information Schema SE_* Performance Schema 30
- 75. • Tables, whichMariaDB misses • Variables global_variables - MariaDB: in I_S session_variables - MariaDB: in I_S variables_by_thread - MariaDB: do not exist variables_info - MariaDB: I_S.SYSTEM_VARIABLES Performance Schema 30
- 76. • Tables, whichMariaDB misses • Replication replication_applier_filters replication_applier_global_filters replication_applier_status_by_worker replication_connection_status MariaDB: SHOW SLAVE STATUS Performance Schema 30
- 77. • Tables, whichMariaDB misses • Errors events_errors_summary_by_account_by_error events_errors_summary_by_host_by_error events_errors_summary_by_thread_by_error events_errors_summary_by_user_by_error events_errors_summary_global_by_error Performance Schema 30
- 78. • Tables, whichMariaDB misses • Statements histograms events_statements_histogram_by_digest events_statements_histogram_global Performance Schema 30
- 79. • Tables, whichMariaDB misses • User-defined functions user_defined_functions MariaDB: mysql.func Performance Schema 30
- 80. • Tables, whichMariaDB misses • Binary log status and LSN log_status MariaDB: SHOW MASTER STATUS and SHOW ENGINE INNODB STATUS Performance Schema 30
- 81. • Initial upgradeis straightforward • Feature differences are easily solvable • MariaDB has options which simplify migration • Migrate to explore all MariaDB advantages! Conclusions 31
- 82. • Migrating to MariaDBfrom MySQL MariaDB vs MySQL: Features Function Difference Replication Compatibility Matrix Optimizer Features Comparison Matrix Spatial Support Matrix More Details 32
- 83.
- 84.