MySQL Performance: Converting MySQL to MariaDB

Create a backup of all databases.

mysqldump --all-databases –add-drop-database > all-databases-backup.sql

This command has no visible output unless errors occur. The above command creates a single file backup of all databases used for easy restoration when using the –add-drop-database flag. This .sql file can re-import into MySQL for deletion and re-creations of all databases. The file created is named all-databases-backup.sql and will be needed later in the Sample Reversion Plan.

Create a backup of MySQL’s configuration file.

cp -p /etc/my.cnf{,.bak}

This command has no visible output unless errors occur. The above command will create an exact copy of /etc/my.cnf named /etc/my.cnf.bak which can be used to restore the configuration if needed.

Step 1: Restore all databases into MySQL

mysql < all-databases-backup.sql

This command has no visible output unless errors occur.   Step 2:Preserve existing config by renaming /etc/my.cnf to /etc/my.cnf.bak

mv -p /etc/my.cnf{,.bad}

This command has no visible output unless errors occur.   Step 3: Copy the /etc/my.cnf.bak file to /etc/my.cnf

cp -p /etc/my.cnf{.bak,}

This command has no visible output unless errors occur.   Step 4: Restart MySQL Service

service mysqld restart

Example Output:

~ $ service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]

~ $ sudo apt-get install mariadb-server -y Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: linux-headers-3.13.0-153 linux-headers-3.13.0-153-generic linux-headers-3.13.0-24 linux-headers-3.13.0-24-generic linux-headers-3.13.0-91 linux-headers-3.13.0-91-generic linux-image-3.13.0-153-generic linux-image-3.13.0-24-generic linux-image-3.13.0-91-generic Use 'apt-get autoremove' to remove them. The following extra packages will be installed: libmariadbclient18 libmysqlclient18 mariadb-client-5.5 mariadb-client-core-5.5 mariadb-common mariadb-server-5.5 mariadb-server-core-5.5 Suggested packages: tinyca mailx mariadb-test The following packages will be REMOVED: mysql-client-5.5 mysql-client-core-5.5 mysql-server mysql-server-5.5 mysql-server-core-5.5 The following NEW packages will be installed: libmariadbclient18 mariadb-client-5.5 mariadb-client-core-5.5 mariadb-common mariadb-server mariadb-server-5.5 mariadb-server-core-5.5 The following packages will be upgraded: libmysqlclient18 1 upgraded, 7 newly installed, 5 to remove and 1 not upgraded. Need to get 10.4 MB of archives. After this operation, 22.1 MB of additional disk space will be used. Get:1 http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu/ trusty/main mariadb-common all 5.5.61+maria-1~trusty [3,180 B] Get:2 http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu/ trusty/main libmysqlclient18 amd64 5.5.61+maria-1~trusty [2,862 B] Get:3 http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu/ trusty/main libmariadbclient18 amd64 5.5.61+maria-1~trusty [521 kB] Get:4 http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu/ trusty/main mariadb-client-core-5.5 amd64 5.5.61+maria-1~trusty [622 kB] Get:5 http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu/ trusty/main mariadb-client-5.5 amd64 5.5.61+maria-1~trusty [961 kB] Get:6 http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu/ trusty/main mariadb-server-core-5.5 amd64 5.5.61+maria-1~trusty [3,831 kB] Get:7 http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu/ trusty/main mariadb-server-5.5 amd64 5.5.61+maria-1~trusty [4,413 kB] Get:8 http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu/ trusty/main mariadb-server all 5.5.61+maria-1~trusty [2,896 B] Fetched 10.4 MB in 1s (7,992 kB/s) Preconfiguring packages ... (Reading database ... 165598 files and directories currently installed.) Removing mysql-server (5.5.61-0ubuntu0.14.04.1) ... Removing mysql-server-5.5 (5.5.61-0ubuntu0.14.04.1) ... mysql stop/waiting Removing mysql-client-5.5 (5.5.61-0ubuntu0.14.04.1) ... Removing mysql-client-core-5.5 (5.5.61-0ubuntu0.14.04.1) ... Removing mysql-server-core-5.5 (5.5.61-0ubuntu0.14.04.1) ... Processing triggers for man-db (2.6.7.1-1ubuntu1) ... Selecting previously unselected package mariadb-common. (Reading database ... 165377 files and directories currently installed.) Preparing to unpack .../mariadb-common_5.5.61+maria-1~trusty_all.deb ... Unpacking mariadb-common (5.5.61+maria-1~trusty) ... Preparing to unpack .../libmysqlclient18_5.5.61+maria-1~trusty_amd64.deb ... Unpacking libmysqlclient18 (5.5.61+maria-1~trusty) over (5.5.61-0ubuntu0.14.04.1) ... Selecting previously unselected package libmariadbclient18. Preparing to unpack .../libmariadbclient18_5.5.61+maria-1~trusty_amd64.deb ... Unpacking libmariadbclient18 (5.5.61+maria-1~trusty) ... Selecting previously unselected package mariadb-client-core-5.5. Preparing to unpack .../mariadb-client-core-5.5_5.5.61+maria-1~trusty_amd64.deb ... Unpacking mariadb-client-core-5.5 (5.5.61+maria-1~trusty) ... Selecting previously unselected package mariadb-client-5.5. Preparing to unpack .../mariadb-client-5.5_5.5.61+maria-1~trusty_amd64.deb ... Unpacking mariadb-client-5.5 (5.5.61+maria-1~trusty) ... Selecting previously unselected package mariadb-server-core-5.5. Preparing to unpack .../mariadb-server-core-5.5_5.5.61+maria-1~trusty_amd64.deb ... Unpacking mariadb-server-core-5.5 (5.5.61+maria-1~trusty) ... Processing triggers for man-db (2.6.7.1-1ubuntu1) ... Setting up mariadb-common (5.5.61+maria-1~trusty) ... Selecting previously unselected package mariadb-server-5.5. (Reading database ... 165514 files and directories currently installed.) Preparing to unpack .../mariadb-server-5.5_5.5.61+maria-1~trusty_amd64.deb ... Unpacking mariadb-server-5.5 (5.5.61+maria-1~trusty) ... Selecting previously unselected package mariadb-server. Preparing to unpack .../mariadb-server_5.5.61+maria-1~trusty_all.deb ... Unpacking mariadb-server (5.5.61+maria-1~trusty) ... Processing triggers for man-db (2.6.7.1-1ubuntu1) ... Processing triggers for ureadahead (0.100.0-16) ... Setting up libmysqlclient18 (5.5.61+maria-1~trusty) ... Setting up libmariadbclient18 (5.5.61+maria-1~trusty) ... Setting up mariadb-client-core-5.5 (5.5.61+maria-1~trusty) ... Setting up mariadb-client-5.5 (5.5.61+maria-1~trusty) ... Setting up mariadb-server-core-5.5 (5.5.61+maria-1~trusty) ... Setting up mariadb-server-5.5 (5.5.61+maria-1~trusty) ... Installing new version of config file /etc/apparmor.d/usr.sbin.mysqld ... Installing new version of config file /etc/init.d/mysql ... Installing new version of config file /etc/logrotate.d/mysql-server ... Installing new version of config file /etc/mysql/debian-start ... mysql start/running, process 7255 Processing triggers for ureadahead (0.100.0-16) ... Setting up mariadb-server (5.5.61+maria-1~trusty) ... Processing triggers for libc-bin (2.19-0ubuntu6.14) ...

~ $ sudo mysql_upgrade MySQL upgrade detected Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.engine_cost OK mysql.event OK mysql.func OK mysql.gtid_executed OK mysql.gtid_slave_pos OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Upgrading from a version before MariaDB-10.1 Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine Phase 3/7: Fixing views from mysql sys.host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.host_summary_by_file_io Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.host_summary_by_file_io_type Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.host_summary_by_stages Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.host_summary_by_statement_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.host_summary_by_statement_type Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.innodb_buffer_stats_by_schema Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.innodb_buffer_stats_by_table Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.innodb_lock_waits Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.io_by_thread_by_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.io_global_by_file_by_bytes Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.io_global_by_file_by_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.io_global_by_wait_by_bytes Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.io_global_by_wait_by_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.latest_file_io Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.memory_by_host_by_current_bytes Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.memory_by_host_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.memory_by_thread_by_current_bytes Error : Table 'performance_schema.memory_summary_by_thread_by_event_name' doesn't exist Error : View 'sys.memory_by_thread_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.memory_by_user_by_current_bytes Error : Table 'performance_schema.memory_summary_by_user_by_event_name' doesn't exist Error : View 'sys.memory_by_user_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.memory_global_by_current_bytes Error : Table 'performance_schema.memory_summary_global_by_event_name' doesn't exist Error : View 'sys.memory_global_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.memory_global_total Error : Table 'performance_schema.memory_summary_global_by_event_name' doesn't exist Error : View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.metrics Error : Table 'performance_schema.global_status' doesn't exist Error : View 'sys.metrics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.processlist Error : Table 'performance_schema.events_transactions_current' doesn't exist Error : View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.ps_check_lost_instrumentation Error : Table 'performance_schema.global_status' doesn't exist Error : View 'sys.ps_check_lost_instrumentation' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.schema_auto_increment_columns OK sys.schema_index_statistics Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.schema_object_overview OK sys.schema_redundant_indexes OK sys.schema_table_lock_waits Error : Table 'performance_schema.metadata_locks' doesn't exist Error : View 'sys.schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.schema_table_statistics Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.schema_table_statistics_with_buffer Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.schema_tables_with_full_table_scans Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.schema_unused_indexes OK sys.session Error : Table 'performance_schema.events_transactions_current' doesn't exist Error : View 'sys.session' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.session_ssl_status Error : Table 'performance_schema.status_by_thread' doesn't exist Error : View 'sys.session_ssl_status' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.statement_analysis Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.statements_with_errors_or_warnings Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.statements_with_full_table_scans Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.statements_with_runtimes_in_95th_percentile Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.statements_with_sorting Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.statements_with_temp_tables Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.user_summary Error : Table 'performance_schema.memory_summary_by_user_by_event_name' doesn't exist Error : View 'sys.user_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.user_summary_by_file_io Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.user_summary_by_file_io_type Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.user_summary_by_stages Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.user_summary_by_statement_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.user_summary_by_statement_type Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.version OK sys.wait_classes_global_by_avg_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.wait_classes_global_by_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.waits_by_host_by_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.waits_by_user_by_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.waits_global_by_latency Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.x$host_summary Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.x$host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$host_summary_by_file_io OK sys.x$host_summary_by_file_io_type OK sys.x$host_summary_by_stages OK sys.x$host_summary_by_statement_latency OK sys.x$host_summary_by_statement_type OK sys.x$innodb_buffer_stats_by_schema OK sys.x$innodb_buffer_stats_by_table OK sys.x$innodb_lock_waits OK sys.x$io_by_thread_by_latency OK sys.x$io_global_by_file_by_bytes OK sys.x$io_global_by_file_by_latency OK sys.x$io_global_by_wait_by_bytes OK sys.x$io_global_by_wait_by_latency OK sys.x$latest_file_io OK sys.x$memory_by_host_by_current_bytes Error : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist Error : View 'sys.x$memory_by_host_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$memory_by_thread_by_current_bytes Error : Table 'performance_schema.memory_summary_by_thread_by_event_name' doesn't exist Error : View 'sys.x$memory_by_thread_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$memory_by_user_by_current_bytes Error : Table 'performance_schema.memory_summary_by_user_by_event_name' doesn't exist Error : View 'sys.x$memory_by_user_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$memory_global_by_current_bytes Error : Table 'performance_schema.memory_summary_global_by_event_name' doesn't exist Error : View 'sys.x$memory_global_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$memory_global_total Error : Table 'performance_schema.memory_summary_global_by_event_name' doesn't exist Error : View 'sys.x$memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$processlist Error : Table 'performance_schema.events_transactions_current' doesn't exist Error : View 'sys.x$processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$ps_digest_95th_percentile_by_avg_us OK sys.x$ps_digest_avg_latency_distribution OK sys.x$ps_schema_table_statistics_io Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.x$schema_flattened_keys OK sys.x$schema_index_statistics OK sys.x$schema_table_lock_waits Error : Table 'performance_schema.metadata_locks' doesn't exist Error : View 'sys.x$schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$schema_table_statistics Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.x$schema_table_statistics_with_buffer Error : Cannot load from mysql.proc. The table is probably corrupted error : Corrupt sys.x$schema_tables_with_full_table_scans OK sys.x$session Error : Table 'performance_schema.events_transactions_current' doesn't exist Error : View 'sys.x$session' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$statement_analysis OK sys.x$statements_with_errors_or_warnings OK sys.x$statements_with_full_table_scans OK sys.x$statements_with_runtimes_in_95th_percentile OK sys.x$statements_with_sorting OK sys.x$statements_with_temp_tables OK sys.x$user_summary Error : Table 'performance_schema.memory_summary_by_user_by_event_name' doesn't exist Error : View 'sys.x$user_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.x$user_summary_by_file_io OK sys.x$user_summary_by_file_io_type OK sys.x$user_summary_by_stages OK sys.x$user_summary_by_statement_latency OK sys.x$user_summary_by_statement_type OK sys.x$wait_classes_global_by_avg_latency OK sys.x$wait_classes_global_by_latency OK sys.x$waits_by_host_by_latency OK sys.x$waits_by_user_by_latency OK sys.x$waits_global_by_latency OK Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases information_schema performance_schema sys sys.sys_config OK Phase 7/7: Running 'FLUSH PRIVILEGES' OK