Как изменить версию mysql server

Your database more than likely runs on MySQL or its fully open-source fork MariaDB. These popular database management software power 90% of websites, so your server host has probably installed either of them for you.

But not all hosts will continue to keep it up to date, so it’s often up to you to check your MySQL version and keep it upgraded. Outdated software on your server is never good and can also be harmful.

Let’s learn how to check if your MySQL version is up to date, and upgrade it if it isn’t.

Watch Our Video Guide to Checking and Upgrading Your MySQL Version

Why Keep MySQL Up to Date?

Manual server maintenance is not a fun task, especially if you’re not very familiar with your webserver’s inner workings. As long as your server and database are working fine, it may be tempting to ignore an outdated piece of software. But there are quite a few reasons why this is a bad idea.

It’s never good to have outdated software on your server. Even the tiniest hole in your security could be a vector for attackers to slip through and take over.

They may even be able to mess with your database. All sorts of important info are stored there, such as your WordPress posts and all other kinds of sensitive bits. You don’t want anyone unwanted to be able to modify that.

Besides that, a new version means new features and general improvements for what’s already there. Even if these extra features don’t affect you, more recent MySQL versions are more secure, better optimized, and faster.

And with every update of MySQL comes a whole slew of bug fixes, patching up errors that could be an annoyance at best or cause you serious trouble at worst.

When it comes to any software on your website, it’s almost always best to keep it at the latest version.

MySQL runs in the background of 90% of websites 😲… but not all hosts will continue to keep it updated. Learn how to check for yourself right here ⬇️Click to Tweet

Which Version of MySQL is the Best?

Many servers these days are still running on MySQL 5.7 (the latest version before the jump to 8.0), even though a newer and better version exists. Why is this? And which is best, MySQL 5.7 or 8.0?

Most people don’t upgrade because either they don’t know about the latest MySQL version, or they don’t want to take the time to update it. In addition, 5.7 has not been deprecated and will not be until 2023.

But while MySQL 5.7 is still supported, making the switch to the latest version is definitely worth your time. It’s faster and more secure — changes can be observed instantly upon activating it. And for developers who can make use of the new functions, the benefits are more numerous than can be quickly listed.

There are some upgrade considerations and backward incompatibilities you’ll need to know about, but for a majority of sites, they won’t cause issues.

It’s almost always best to keep up with the latest stable version of MySQL. Even minor updates are worth the trouble, though the new built-in auto-updater will likely handle those for you. As for major updates, it’s worth it unless crucial parts of your server are incompatible.

While WordPress supports MySQL versions all the way back to 5.6, 8.0 works perfectly with it. And as 5.6 is no longer supported and is susceptible to bugs and security risks, you should at least update to 5.7.

How to Check MySQL Version

It’s crucial to keep MySQL up to date, but you need to know its version before upgrading. Chances are, you’re already using the latest version and don’t need to do anything at all. There are several ways to check; these are just a handful of the easiest ones.

Check MySQL Version with MyKinsta

Accessing your database and checking your MySQL version with MyKinsta is very easy. There are several ways to do so, as detailed above, but the easiest two are using phpMyAdmin or connecting with the command line.

You can find phpMyAdmin after logging into MyKinsta. Just go to Sites > Info, and under the Database access section, click Open phpMyAdmin. The credentials you need to log in are right there.

You can also connect with the command line with SSH. With Kinsta, we set up everything for you to allow SSH access. Just find your credentials in Sites > Info in the SFTP/SSH section. Then follow the steps below to log in and put in the proper commands.

Use the Command Line

The easiest way to obtain your MySQL version is with a simple code submitted through the command line. It takes mere seconds and answers your question instantly. Getting at your server’s command line shouldn’t be too difficult unless you’re using minimal hosting.

There are a variety of ways to access your server’s command line. For example, your web host may provide a way to submit commands through their back end, such as with cPanel. Or you may be able to use the built-in Terminal or Command Prompt to connect to your server. Tools like PuTTY also exist to help you log in and are usually required for Windows users to connect with SSH.

Either way, you’ll need some SSH login credentials. You can usually find them in your web hosting dashboard, or you can email them and ask for help.

Once you know how you’re going to connect to your server’s command line, follow these steps.

Step 1: Launch the Terminal (Linux, macOS) or Command Prompt/PuTTY (Windows) on your computer. In cPanel, you can find it under Advanced > Terminal.

Step 2: Provide your SSH credentials to connect and log in to the server.

Step 3: Input the following command:

mysql -V

If you have the MySQL Shell installed, you can also use the simple command “mysql” to show your version information.

Either way, the version number should be output on the screen.

Check MySQL Version with phpMyAdmin

There’s another straightforward way to check your MySQL version, and that’s with phpMyAdmin. This software is also ubiquitous and present on most servers, so you have a good shot at finding your MySQL version.

Find your phpMyAdmin credentials by checking your web hosting dashboard. There may even be a direct link to log in there. Email your host if you can’t find them.

Log in to phpMyAdmin once you find your credentials. As soon as you’re in, you’ll see a Database server box on the right side of the screen. There, under the Software version section, is your MySQL version. Easy as that!

Through WordPress Dashboard

A final easy way to find your MySQL version is through your site’s WordPress admin dashboard.

To do that, visit your WordPress dashboard, and go to Tools > Site Health. Under here, first, go to the Info tab, and then to the Database section below.

You can find your server’s current MySQL version listed here beside the Server version label.

How to Upgrade MySQL Version

If you’ve determined your MySQL is out of date, you should rectify that as soon as possible. There are multiple ways to update MySQL to the latest versions; it all depends on what tools and operating systems you’re using.

It’s worth noting that later versions of MySQL will attempt to auto-update if the script notices that your installation is out of date. You may not need to do anything at all. If you’ve determined that MySQL is out of date and needs to be manually updated, follow these steps.

Like checking your MySQL version with the command line, you’ll need to obtain your SSH login credentials from your web host to access the terminal on your server. And, of course, you’ll need the IP address of your server.

This backup contains both your database (an SQL file) and your entire site, so you’ll be extra safe if something goes wrong.

Upgrade MySQL Using cPanel

If your host offers cPanel, it’s probably the easiest way to do an upgrade since you can use the interface provided. But since cPanel defaults to MySQL 5.7 by default, you may want to do an upgrade.

As cPanel doesn’t allow you to downgrade, you should back up your database before proceeding.

Most cPanel installations come with Web Host Manager, WHM, which gives you deeper root access.

Step 1: Log in to WHM by visiting either “example-site.com:2087” or “example-site.com/whm.” You can also ask your web host how to access WHM.

Step 2: Navigate to Software > MySQL Upgrade or type “MySQL” into the search bar. You may also find it under SQL Services > MySQL/MariaDB Upgrade.

Step 3: Select the version of MySQL you want to upgrade to and click Next. Now follow the upgrade steps, and it’ll take care of everything for you.

This method is by far the simplest way to do a MySQL upgrade, but if your host doesn’t offer cPanel or WHM, you may need to do it manually.

Upgrade MySQL in Linux/Ubuntu

There are a variety of Linux distributions, but Ubuntu is by far the most common. These instructions should work for any operating system that uses the apt/apt-get function to install the software.

Linux can easily SSH connect without any additional software requirements. So, you can get right into your server.

Step 1: Launch the Terminal. Obtain your SSH credentials and use the ssh command to log in.

Step 2: You should have the MySQL apt Repository installed since MySQL is already on your server. Update your package list with the following command:

sudo apt-get update

Step 3: Now upgrade MySQL.

sudo apt-get upgrade mysql-server

You can also try this command if the above doesn’t work:

sudo apt-get install mysql-server

It’ll prompt you to choose a version; select 5.7 or 8.0. If you’re upgrading from 5.6, you should update to 5.7 first, rather than going straight to 8.0.

Upgrade MySQL on macOS

While macOS comes with its own version of MySQL, you can’t upgrade it through the usual methods. And most likely, your server is using a separate instance.

Many of these steps are very similar to updating MySQL on Linux. macOS similarly has SSH built into the operating system, so all you need to do is run the commands.

We’ll assume you’re using Homebrew as a package manager as it’s the simplest way to install and update MySQL on Mac. You shouldn’t attempt to upgrade MySQL with Homebrew if you didn’t use it to install it initially.

Step 1: Launch the Terminal program and log in with the ssh command.

Step 2: Run these commands using Homebrew.

brew update
brew install mysql

This command should automatically install 8.0 and upgrade any existing versions.

Are you running into trouble? Try uninstalling the current version of MySQL first. Use these commands:

brew remove mysql
brew cleanup

Then you can again attempt to run the update and install commands again.

Step 3: Make sure you start the MySQL server again manually:

mysql.server start

Upgrade MySQL in Windows

Unlike Mac and Linux, Windows can’t use SSH by default. Instead, you’ll need to install a separate client, and then you can connect to your server.

Step 1: Download PuTTY and install it on your system. Launch it and input your SSH credentials to connect to the server. 3306 is the default port number. Ignore any security alerts; these are normal.

Step 2: Assuming you’re connecting to a Linux terminal, you can use the Linux commands in the Linux/Ubuntu section above to perform the upgrade.

You can also use the MySQL installer to download MySQL onto the system directly. This process skips the SSH connection entirely.

Keeping your MySQL version up to date is crucial for site security 🔒 See how to upgrade your version here ⬇️Click to Tweet

Summary

With any software, it’s crucial to keep it up to date. And with MySQL being so integral to your website and its database, it’s even more imperative. Unfortunately, too many web admins choose to let their MySQL version fall behind, to the detriment of their website’s speed and security.

Luckily, checking your MySQL version and upgrading it is almost always a smooth process. While you should definitely back up your website and database for emergencies, you aren’t likely to experience any issues.

With just a tiny command in your web server’s terminal, your database will be well optimized and faster than ever.

Your database more than likely runs on MySQL or its fully open-source fork MariaDB. These popular database management software power 90% of websites, so your server host has probably installed either of them for you.

But not all hosts will continue to keep it up to date, so it’s often up to you to check your MySQL version and keep it upgraded. Outdated software on your server is never good and can also be harmful.

Let’s learn how to check if your MySQL version is up to date, and upgrade it if it isn’t.

Watch Our Video Guide to Checking and Upgrading Your MySQL Version

Why Keep MySQL Up to Date?

Manual server maintenance is not a fun task, especially if you’re not very familiar with your webserver’s inner workings. As long as your server and database are working fine, it may be tempting to ignore an outdated piece of software. But there are quite a few reasons why this is a bad idea.

It’s never good to have outdated software on your server. Even the tiniest hole in your security could be a vector for attackers to slip through and take over.

They may even be able to mess with your database. All sorts of important info are stored there, such as your WordPress posts and all other kinds of sensitive bits. You don’t want anyone unwanted to be able to modify that.

Besides that, a new version means new features and general improvements for what’s already there. Even if these extra features don’t affect you, more recent MySQL versions are more secure, better optimized, and faster.

And with every update of MySQL comes a whole slew of bug fixes, patching up errors that could be an annoyance at best or cause you serious trouble at worst.

When it comes to any software on your website, it’s almost always best to keep it at the latest version.

MySQL runs in the background of 90% of websites 😲… but not all hosts will continue to keep it updated. Learn how to check for yourself right here ⬇️Click to Tweet

Which Version of MySQL is the Best?

Many servers these days are still running on MySQL 5.7 (the latest version before the jump to 8.0), even though a newer and better version exists. Why is this? And which is best, MySQL 5.7 or 8.0?

Most people don’t upgrade because either they don’t know about the latest MySQL version, or they don’t want to take the time to update it. In addition, 5.7 has not been deprecated and will not be until 2023.

But while MySQL 5.7 is still supported, making the switch to the latest version is definitely worth your time. It’s faster and more secure — changes can be observed instantly upon activating it. And for developers who can make use of the new functions, the benefits are more numerous than can be quickly listed.

There are some upgrade considerations and backward incompatibilities you’ll need to know about, but for a majority of sites, they won’t cause issues.

It’s almost always best to keep up with the latest stable version of MySQL. Even minor updates are worth the trouble, though the new built-in auto-updater will likely handle those for you. As for major updates, it’s worth it unless crucial parts of your server are incompatible.

While WordPress supports MySQL versions all the way back to 5.6, 8.0 works perfectly with it. And as 5.6 is no longer supported and is susceptible to bugs and security risks, you should at least update to 5.7.

How to Check MySQL Version

It’s crucial to keep MySQL up to date, but you need to know its version before upgrading. Chances are, you’re already using the latest version and don’t need to do anything at all. There are several ways to check; these are just a handful of the easiest ones.

Check MySQL Version with MyKinsta

Accessing your database and checking your MySQL version with MyKinsta is very easy. There are several ways to do so, as detailed above, but the easiest two are using phpMyAdmin or connecting with the command line.

You can find phpMyAdmin after logging into MyKinsta. Just go to Sites > Info, and under the Database access section, click Open phpMyAdmin. The credentials you need to log in are right there.

You can also connect with the command line with SSH. With Kinsta, we set up everything for you to allow SSH access. Just find your credentials in Sites > Info in the SFTP/SSH section. Then follow the steps below to log in and put in the proper commands.

Use the Command Line

The easiest way to obtain your MySQL version is with a simple code submitted through the command line. It takes mere seconds and answers your question instantly. Getting at your server’s command line shouldn’t be too difficult unless you’re using minimal hosting.

There are a variety of ways to access your server’s command line. For example, your web host may provide a way to submit commands through their back end, such as with cPanel. Or you may be able to use the built-in Terminal or Command Prompt to connect to your server. Tools like PuTTY also exist to help you log in and are usually required for Windows users to connect with SSH.

Either way, you’ll need some SSH login credentials. You can usually find them in your web hosting dashboard, or you can email them and ask for help.

Once you know how you’re going to connect to your server’s command line, follow these steps.

Step 1: Launch the Terminal (Linux, macOS) or Command Prompt/PuTTY (Windows) on your computer. In cPanel, you can find it under Advanced > Terminal.

Step 2: Provide your SSH credentials to connect and log in to the server.

Step 3: Input the following command:

mysql -V

If you have the MySQL Shell installed, you can also use the simple command “mysql” to show your version information.

Either way, the version number should be output on the screen.

Check MySQL Version with phpMyAdmin

There’s another straightforward way to check your MySQL version, and that’s with phpMyAdmin. This software is also ubiquitous and present on most servers, so you have a good shot at finding your MySQL version.

Find your phpMyAdmin credentials by checking your web hosting dashboard. There may even be a direct link to log in there. Email your host if you can’t find them.

Log in to phpMyAdmin once you find your credentials. As soon as you’re in, you’ll see a Database server box on the right side of the screen. There, under the Software version section, is your MySQL version. Easy as that!

Through WordPress Dashboard

A final easy way to find your MySQL version is through your site’s WordPress admin dashboard.

To do that, visit your WordPress dashboard, and go to Tools > Site Health. Under here, first, go to the Info tab, and then to the Database section below.

You can find your server’s current MySQL version listed here beside the Server version label.

How to Upgrade MySQL Version

If you’ve determined your MySQL is out of date, you should rectify that as soon as possible. There are multiple ways to update MySQL to the latest versions; it all depends on what tools and operating systems you’re using.

It’s worth noting that later versions of MySQL will attempt to auto-update if the script notices that your installation is out of date. You may not need to do anything at all. If you’ve determined that MySQL is out of date and needs to be manually updated, follow these steps.

Like checking your MySQL version with the command line, you’ll need to obtain your SSH login credentials from your web host to access the terminal on your server. And, of course, you’ll need the IP address of your server.

This backup contains both your database (an SQL file) and your entire site, so you’ll be extra safe if something goes wrong.

Upgrade MySQL Using cPanel

If your host offers cPanel, it’s probably the easiest way to do an upgrade since you can use the interface provided. But since cPanel defaults to MySQL 5.7 by default, you may want to do an upgrade.

As cPanel doesn’t allow you to downgrade, you should back up your database before proceeding.

Most cPanel installations come with Web Host Manager, WHM, which gives you deeper root access.

Step 1: Log in to WHM by visiting either “example-site.com:2087” or “example-site.com/whm.” You can also ask your web host how to access WHM.

Step 2: Navigate to Software > MySQL Upgrade or type “MySQL” into the search bar. You may also find it under SQL Services > MySQL/MariaDB Upgrade.

Step 3: Select the version of MySQL you want to upgrade to and click Next. Now follow the upgrade steps, and it’ll take care of everything for you.

This method is by far the simplest way to do a MySQL upgrade, but if your host doesn’t offer cPanel or WHM, you may need to do it manually.

Upgrade MySQL in Linux/Ubuntu

There are a variety of Linux distributions, but Ubuntu is by far the most common. These instructions should work for any operating system that uses the apt/apt-get function to install the software.

Linux can easily SSH connect without any additional software requirements. So, you can get right into your server.

Step 1: Launch the Terminal. Obtain your SSH credentials and use the ssh command to log in.

Step 2: You should have the MySQL apt Repository installed since MySQL is already on your server. Update your package list with the following command:

sudo apt-get update

Step 3: Now upgrade MySQL.

sudo apt-get upgrade mysql-server

You can also try this command if the above doesn’t work:

sudo apt-get install mysql-server

It’ll prompt you to choose a version; select 5.7 or 8.0. If you’re upgrading from 5.6, you should update to 5.7 first, rather than going straight to 8.0.

Upgrade MySQL on macOS

While macOS comes with its own version of MySQL, you can’t upgrade it through the usual methods. And most likely, your server is using a separate instance.

Many of these steps are very similar to updating MySQL on Linux. macOS similarly has SSH built into the operating system, so all you need to do is run the commands.

We’ll assume you’re using Homebrew as a package manager as it’s the simplest way to install and update MySQL on Mac. You shouldn’t attempt to upgrade MySQL with Homebrew if you didn’t use it to install it initially.

Step 1: Launch the Terminal program and log in with the ssh command.

Step 2: Run these commands using Homebrew.

brew update
brew install mysql

This command should automatically install 8.0 and upgrade any existing versions.

Are you running into trouble? Try uninstalling the current version of MySQL first. Use these commands:

brew remove mysql
brew cleanup

Then you can again attempt to run the update and install commands again.

Step 3: Make sure you start the MySQL server again manually:

mysql.server start

Upgrade MySQL in Windows

Unlike Mac and Linux, Windows can’t use SSH by default. Instead, you’ll need to install a separate client, and then you can connect to your server.

Step 1: Download PuTTY and install it on your system. Launch it and input your SSH credentials to connect to the server. 3306 is the default port number. Ignore any security alerts; these are normal.

Step 2: Assuming you’re connecting to a Linux terminal, you can use the Linux commands in the Linux/Ubuntu section above to perform the upgrade.

You can also use the MySQL installer to download MySQL onto the system directly. This process skips the SSH connection entirely.

Keeping your MySQL version up to date is crucial for site security 🔒 See how to upgrade your version here ⬇️Click to Tweet

Summary

With any software, it’s crucial to keep it up to date. And with MySQL being so integral to your website and its database, it’s even more imperative. Unfortunately, too many web admins choose to let their MySQL version fall behind, to the detriment of their website’s speed and security.

Luckily, checking your MySQL version and upgrading it is almost always a smooth process. While you should definitely back up your website and database for emergencies, you aren’t likely to experience any issues.

With just a tiny command in your web server’s terminal, your database will be well optimized and faster than ever.

Прогресс не стоит на месте, поэтому причины обновиться на актуальные версии MySQL становятся всё более весомыми. Не так давно в одном из наших проектов настало время обновлять уютные кластеры Percona Server 5.7 до 8-й версии. Всё это происходило на платформе Ubuntu Linux 16.04. Как выполнить подобную операцию с минимальным простоем и с какими проблемами мы столкнулись при обновлении — читайте в этой статье.

Подготовка

Любое обновление сервера баз данных скорее всего связано с перенастройкой базы: изменений требований к лимитам на системные ресурсы и исправлением конфигов базы, которые надо очистить от устаревших директив.

Перед обновлением мы обязательно обратимся к официальной документации:

• MySQL 8 release notes;
• руководство по обновлению от MySQL;
• руководство по обновлению от Percona;
• руководство MySQL по обновлению реплик и мастеров.

И составим план действий:

1. Исправить конфигурационные файлы, удалив устаревшие директивы.
2. Проверить совместимость утилитами.
3. Обновить slave-базы, поставив пакет percona-server-server.
4. Обновить мастер, поставив тот же пакет.

Разберём каждый пункт плана и посмотрим, что же может пойти не так.

ВАЖНО! Процедура обновления MySQL-кластера на базе Galera имеет свои тонкости, которые в статье не описаны. Не стоит использовать эту инструкцию в таком случае.

Часть 1: Проверка конфигов

В 8-й версии MySQL убрали query_cache. Вообще-то он был признан устаревшим еще в версии 5.7, но теперь и удалён вовсе. Соответственно, необходимо убрать связанные директивы. А для кэширования запросов теперь можно использовать внешние инструменты — например, ProxySQL.

Так же в конфиге нашлись устаревшие директивы про innodb_file_format. Если в MySQL 5.7 имелась возможность выбора формата InnoDB, то 8-я версия уже работает только с форматом Barracuda.

Наш итог — удаление следующих директив:

• query_cache_type, query_cache_limit и query_cache_size;
• innodb_file_format и innodb_file_format_max.

Для проверки воспользуемся Docker-образом Percona Server. Конфиг сервера поместим в директорию mysql_config_test, а рядом создадим директории для данных и логов. Пример теста конфигурации percona-server:

mkdir -p {mysql_config_test,mysql_data,mysql_logs}
cp -r /etc/mysql/conf.d/* mysql_config_test/
docker run  --name some-percona -v $(pwd)/mysql_config_test:/etc/my.cnf.d/  -v $(pwd)/mysql_data/:/var/lib/mysql/ -v $(pwd)/mysql_logs/:/var/log/mysql/ -e MYSQL_ROOT_PASSWORD=${MYSQL_PASSWORD} -d percona:8-centos

Итог: либо в логах Docker, либо в директории с логами — в зависимости от ваших конфигов — появится файл, в котором будут описаны проблемные директивы.

Вот что было у нас:

2020-04-03T12:44:19.670831Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2020-04-03T12:44:19.671678Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2020-04-03T12:44:19.671682Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.

Таким образом, нам потребовалось еще разобраться с кодировками и заменить устаревшую директиву expire-logs-days.

Часть 2: Проверка работающих установок

В документации по обновлению есть 2 утилиты для проверки базы на совместимость. Их использование помогает администратору проверить совместимость имеющейся структуры данных.

Начнём с классической утилиты mysqlcheck. Достаточно просто запустить:

mysqlcheck -u root -p --all-databases --check-upgrade

Если проблемы не обнаружены, утилита завершится с кодом 0:

Кроме того, в современных версиях MySQL доступна утилита mysql-shell (в случае Percona это пакет percona-mysql-shell). Она является заменой классическому клиенту mysql и совмещает в себе функции клиента, редактора SQL-кода и инструменты администрирования MySQL. Для проверки сервера перед обновлением можно через неё выполнить следующую команду:

mysqlsh -- util check-for-server-upgrade { --user=root --host=1.1.1.1 --port=3306 } --config-path=/etc/mysql/my.cnf

И вот какие замечания мы получили:

В общем, ничего критичного — только предупреждения о кодировках (см. ниже). Общий результат выполнения:

Мы решили, что обновление должно пойти без проблем.

Замечание о предупреждениях выше, свидетельствующих проблемы с кодировками. Дело в том, что UTF-8 в MySQL до недавнего времени не являлась «настоящей» UTF-8, так как хранила всего 3 байта вместо 4. В MySQL 8 это наконец-то решили исправить: алиас utf8 вскоре будет вести на кодировку utf8mb4, а старые столбцы в таблицах станут utf8mb3. В дальнейшем кодировка utf8mb3 будет удалена, но не в данном релизе. Поэтому мы решили исправить кодировки уже на работающей инсталляции СУБД, после её обновления.

Часть 3: Обновление серверов

Что же может пойти не так, когда есть столь шикарный план?.. Прекрасно понимая, что нюансы всегда случаются, первый эксперимент мы провели на dev-кластере MySQL.

Как уже упоминалось, официальная документация освещает вопрос обновления MySQL-серверов с репликами. Суть сводится к тому, что сначала стоит обновлять все реплики (slave), так как MySQL 8 умеет реплицироваться с мастера версии 5.7. Некоторая сложность заключается в том, что у нас используется режим master <-> master, когда удалённый мастер находится в режиме read-only. То есть фактически боевой трафик поступает в один ЦОД, а 2-й является резервным.

Топология выглядит следующим образом:

Обновление должно начаться с реплик mysql replica dc 2, mysql master dc 2 и mysql replica dc 1, а закончиться — сервером mysql master dc 1. Для пущей надёжности мы остановили виртуальные машины, сделали их снапшоты, а непосредственно перед обновлением остановили репликацию командой STOP SLAVE. В остальном же обновление выглядит так:

1. Каждую реплику перезапускаем, добавив в конфиги 3 опции: skip-networking, skip-slave-start, skip-log-bin. Дело в том, что обновление базы генерирует бинарные логи с обновлением системных таблиц. Данные директивы гарантируют, что в базе не будет изменения данных приложения, а в бинарные логи не попадет информация об обновлении системных таблиц. Это позволит избежать проблем при возобновлении репликации.
2. Устанавливаем пакет percona-server-server. Важно отметить, что в версии MySQL 8 не требуется запускать команду mysqlupgrade после обновления сервера.
3. После успешного старта еще раз перезапускаем сервер — уже без параметров, которые добавлялись в первом пункте.
4. Убеждаемся, что репликация успешно работает: проверяем SHOW SLAVE STATUS и смотрим, что обновляются таблицы со счетчиками в базе приложения.

Всё это выглядит достаточно просто: обновление dev прошло успешно. Ок, можно спокойно планировать ночное обновление для production.

Не было печали — prod мы обновляли

Однако перенос успешного опыта dev на production не обошёлся без сюрпризов.

К счастью, сам процесс обновления начинается с реплик, поэтому, встретив сложности, мы остановили работы и восстановили реплику из снапшота. Исследование проблем перенесли на следующее утро. В логах оказались следующее записи:

2020-01-14T21:43:21.500563Z 2 [ERROR] [MY-012069] [InnoDB] table: t1 has 19 columns but InnoDB dictionary has 20 columns
2020-01-14T21:43:21.500722Z 2 [ERROR] [MY-010767] [Server] Error in fixing SE data for db1.t1
2020-01-14T21:43:24.208365Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2020-01-14T21:43:24.208658Z 0 [ERROR] [MY-010119] [Server] Aborting

Исследование архивов различных почтовых рассылок в Google привело к пониманию, что такая проблема возникает из-за бага MySQL. Хотя скорее это даже баг утилит mysqlcheck и mysqlsh.

Оказывается, в MySQL сменили способ представления данных для десятичных полей (int, tinyint и т.п.), поэтому внутри mysql-server используется другой способ их хранения. Если ваша база данных изначально была в версии 5.5 или 5.1, а затем вы обновлялись до 5.7, то, возможно, требуется произвести OPTIMIZE для некоторых таблиц. Тогда MySQL обновит файлы с данными, переведя их на актуальный формат хранения.

Также это можно проверить утилитой mysqlfrm:

mysqlfrm --diagnostic -vv /var/lib/mysql/db/table.frm
...
 'field_length': 8,
  'field_type': 246, # формат поля
  'field_type_name': 'decimal',
  'flags': 3,
  'flags_extra': 67,
  'interval_nr': 0,
 'name': 'you_decimal_column',
...

Если field_type у вас равен 0, то в таблице используется старый тип — надо проводить OPTIMIZE. Однако, если стоит значение 246 — у вас уже новый тип. Подробнее с типами можно ознакомиться в коде.

Более того, в данном баге рассматривается вторая возможная причина, которая обошла нас стороной, — это отсутствие InnoDB-таблиц в системной таблице INNODB_SYS_TABLESPACES, если они, таблицы, создавались в версии 5.1. Чтобы избежать проблем при обновлении, можно воспользоваться приложенным SQL-скриптом.

Почему же у нас не возникло таких проблем на dev? База туда периодически копируется с production — таким образом, таблицы пересоздаются.

К сожалению, на реально работающей большой БД не получится просто взять и выполнить повсеместный OPTIMIZE. Здесь поможет percona-toolkit: для операции online OPTIMIZE отлично подходит утилита pt-online-schema-change.

Обновленный план стал получился таким:

1. Провести оптимизацию всех таблиц.
2. Провести обновление баз данных.

Чтобы проверить его и заодно выяснить время обновления, мы отключили одну из реплик, а для всех таблиц запустили следующую команду:

pt-online-schema-change --critical-load Threads_running=150 --alter "ENGINE=InnoDB" --execute --chunk-size 100 --quiet --alter-foreign-keys-method auto h=127.0.0.1,u=root,p=${MYSQL_PASSWORD},D=db1,t=t1

Обновление таблиц производится без продолжительных блокировок благодаря тому, что утилита создает новую временную таблицу, в которую копирует данные из основной таблицы. В момент, когда обе таблицы идентичны, исходная таблица блокируется и подменяется новой. В нашем случае тестовый запуск показал, что для обновления всех таблиц потребуется около суток, но при этом копирование данных вызывало слишком большую нагрузку на диски.

Чтобы этого избежать, на production мы добавили к команде аргумент --sleep со значением 10 — этот параметр регулирует длину ожидания после переноса пачки данных в новую таблицу. Так можно снизить нагрузку, если реально запущенное приложение требовательно к времени ответа.

После выполнения оптимизации обновление прошло успешно.

… но не до конца!

Уже через полчаса после обновления клиент пришел с проблемой. База работала очень странно: периодически начинались сбросы подключений. Вот как это выглядело в мониторинге:

На скриншоте виден пилообразный график, связанный с тем, что часть потоков MySQL-сервера периодически падали с ошибкой. В приложении появились ошибки:

[PDOException] SQLSTATE[HY000] [2002] Connection refused

Беглый осмотр логов выявил, что демон mysqld не мог получить требуемые ресурсы у операционной системы. Разбираясь с ошибками, мы обнаружили в системе «бесхозные» файлы политик apparmor:

# dpkg -S /etc/apparmor.d/cache/usr.sbin.mysqld
dpkg-query: no path found matching pattern /etc/apparmor.d/cache/usr.sbin.mysqld
# dpkg -S /etc/apparmor.d/local/usr.sbin.mysqld
dpkg-query: no path found matching pattern /etc/apparmor.d/local/usr.sbin.mysqld
# dpkg -S /etc/apparmor.d/usr.sbin.mysqld
mysql-server-5.7: /etc/apparmor.d/usr.sbin.mysqld
# dpkg -l mysql-server-5.7
rc  mysql-server-5.7 5.7.23-0ubuntu0.16.04.1      amd64

Эти файлы образовались при обновлении на MySQL 5.7 пару лет назад и принадлежат удалённому пакету. Удаление файлов и перезапуск службы apparmor решил проблему:

systemctl stop apparmor
rm /etc/apparmor.d/cache/usr.sbin.mysqld
rm /etc/apparmor.d/local/usr.sbin.mysqld
rm /etc/apparmor.d/usr.sbin.mysqld
systemctl start apparmor

В заключение

Любая, даже самая простая операция, может привести к неожиданным проблемам. И даже наличие продуманного плана не всегда гарантирует ожидаемый результат. Теперь в любые планы обновления у нашей команды входит еще и обязательная чистка лишних файлов, которые могли появиться в результате последих действий.

А этим не очень профессиональным графическим творчеством я бы хотел сказать огромное спасибо компании Percona за их отличные продукты!

P.S.

Читайте также в нашем блоге:

• «Базы данных и Kubernetes (обзор и видео доклада)»;
• «Kubernetes tips & tricks: ускоряем bootstrap больших баз данных»;
• «6 практических историй из наших SRE-будней»;
• «Одна история с оператором Redis в K8s и мини-обзор утилит для анализа данных этой БД»;
• «Беспростойная миграция MongoDB в Kubernetes».