apt update
apt -y install software-properties-common
apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
add-apt-repository 'deb [arch=amd64] http://mariadb.mirror.globo.tech/repo/10.5/ubuntu focal main
apt update
apt install mariadb-server mariadb-client
cat << EOF > /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB-10.4
baseurl=http://yum.mariadb.org/10.4/centos8-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
rpm –import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
dnf update
dnf –disablerepo=AppStream install MariaDB-server MariaDB-client
If you have some new MariaDB or MYSQL version and slow GROUP BY issue, you can try explain your slow query and find many 'LATERAL DERIVED' select type, so you can try change optimizer:
MariaDB [db]> set optimizer_switch='split_materialized=off';
Query OK, 0 rows affected (0.000 sec)
To set permanently vi /etc/my.cnf.d/server.cnf
[mysqld]
optimizer_switch=split_materialized=off
wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm yum install percona-release-latest.noarch.rpm yum install percona-toolkit Create same MySQL user on different databases and run: pt-config-diff h=192.168.0.99 h=192.168.0.100 -p Password -u User # A software update is available: 74 config differences Variable m1.srv.net old100.srv.net ========================= ========================= ========================= back_log 80 50 binlog_format MIXED STATEMENT general_log_file m1.log /var/lib/mysql/old100.log group_concat_max_len 1048576 1024 have_openssl YES DISABLED have_symlink YES DISABLED hostname m1.srv.net old100.srv.net innodb_autoextend_incr... 64 8 innodb_buffer_pool_ins... 8 1 innodb_buffer_pool_size 5368709120 1073741824 innodb_concurrency_tic... 5000 500 innodb_data_file_path ibdata1:12M:autoextend ibdata1:10M:autoextend innodb_file_format Antelope innodb_flush_method fsync innodb_large_prefix OFF innodb_log_buffer_size 16777216 8388608 innodb_log_file_size 50331648 5242880 innodb_max_dirty_pages... 75.000000 75 innodb_old_blocks_time 1000 0 innodb_open_files 2000 300 innodb_purge_batch_size 300 20 innodb_purge_threads 4 0 innodb_spin_wait_delay 4 6 innodb_stats_on_metadata OFF ON innodb_strict_mode ON OFF innodb_version 10.4.13 5.5.54 join_buffer_size 262144 131072 key_buffer_size 134217728 8388608 lc_messages_dir /usr/share/mysql/ lock_wait_timeout 86400 31536000 log_error /var/log/mysqld.log log_warnings 2 1 max_allowed_packet 16777216 1048576 max_connect_errors 100 10 max_long_data_size 16777216 1048576 max_relay_log_size 1073741824 0 max_seeks_for_key 4294967295 18446744073709551615 max_write_lock_count 4294967295 18446744073709551615 myisam_recover_options BACKUP,QUICK OFF myisam_sort_buffer_size 134216704 8388608 old_alter_table DEFAULT OFF open_files_limit 16364 5000 optimizer_switch index_merge=on,index_m... index_merge=on,index_m... performance_schema_eve... -1 10000 performance_schema_eve... -1 10 performance_schema_max... 90 80 performance_schema_max... -1 1000 performance_schema_max... -1 10000 performance_schema_max... -1 1000000 performance_schema_max... 40 30 performance_schema_max... -1 1000000 performance_schema_max... -1 100000 performance_schema_max... -1 50000 performance_schema_max... -1 1000 pid_file /var/lib/mysql/m1.pid /var/run/mysqld/mysqld... query_alloc_block_size 16384 8192 query_cache_size 1048576 0 query_cache_type OFF ON query_prealloc_size 24576 8192 secure_auth ON OFF secure_file_priv /var/lib/mysql-files/ server_id 1 0 slave_net_timeout 60 3600 slow_query_log_file m1-slow.log /var/lib/mysql/old100-slo... sql_mode STRICT_TRANS_TABLES,ER... sync_master_info 10000 0 sync_relay_log 10000 0 sync_relay_log_info 10000 0 system_time_zone UTC EEST table_open_cache 2000 400 thread_cache_size 151 0 thread_stack 299008 262144 version 10.4.13-MariaDB 5.5.54 version_comment MariaDB Server MySQL Community Server...
vi /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.1 CentOS repository list - created 2016-08-18 06:31 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
chown -R mysql.mysql /var/lib/mysql/
cp -Rf /var/lib/mysql /var/lib/mysql-old
mv /etc/my.cnf /etc/my.cnf-old
/scripts/update_local_rpm_versions –edit target_settings.MySQL50 uninstalled
/scripts/update_local_rpm_versions –edit target_settings.MySQL51 uninstalled
/scripts/update_local_rpm_versions –edit target_settings.MySQL55 uninstalled
/scripts/check_cpanel_rpms –fix –targets=MySQL50,MySQL51,MySQL55
vi /etc/yum.repos.d/mdb.repo
# MariaDB 10.0 CentOS repository list – created 2015-01-16 13:06 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
yum install MariaDB-Galera-server MariaDB-client galera
/etc/init.d/mysql start
mysql_upgrade
/etc/init.d/mysql restart
/scripts/easyapache –build