WebHosting Linux: How to migrate a MySQL node from RedHat EL 6 to RedHat EL 7
Original Publishing Date:
2020-11-13
Introduction
Ingram Micro declares the End of Life (EOL) of RedHat Enterprise Linux 6 and CentOS Linux 6 operating systems on service nodes in CloudBlue Commerce (formerly, Odin Automation). However, MySQL services being run in RedHat Enterprise Linux 6 must remain operable in RedHat Enterprise Linux 7 after reaching this EOL.
Steps to Migrate
Use the following instructions to migrate a MySQL database node from a source RedHat Enterprise Linux 6 to a new RedHat Enterprise Linux 7 server.
The plan of migration
- Deploy a new (destination) server and add it to CloudBlue Commerce with one of the following roles:
- MariaDB 10.1 database server
- MySQL 5.6 database server
- MySQL 5.7 database server
- Migrate MySQL databases to a destination server.
- Modify the source node in CloudBlue Commerce using the script attached to this article.
- Detach the destination server from CloudBlue Commerce.
- Assign the IP addresses from the source server to the destination server.
- Re-register the MySQL node in the CloudBlue Commerce Provider Panel.
Server notes
How to migrate
1. Prepare a new host with RedHat EL 7 or CentOS 7 for the destination MySQL node.
1.1. Deploy a RedHat EL 7 or CentOS 7-based VPS/VDS/DS with enough hardware resources for a destination database server.
Assign a temporary IP address to this host to access it during the migration. IP addresses from the source database host (private and public) will be assigned to the destination server after the migration.
1.2 Update the operating system.
# yum update
1.3. Remove all the MySQL and MariaDB packages that are already installed to avoid conflicts.
1.4. Configure the system firewall to allow incoming TCP connections to these ports: 8352 (pa-agent), 22 (ssh), and 3306 (MySQL).
1.5. Attach the new host as a database server:
Go to CloudBlue Commerce Provider Panel > Operations > Infrastructure > Service Nodes > Add New Node.
Then select one of the following roles (be careful, here you are selecting the target database version for migration)
- MariaDB 10.1 database server
- MySQL 5.6 database server
- MySQL 5.7 database server
and pass through the wizard screens. Do not continue until all tasks are successfully completed.
The database server software of the selected version will be installed. The host will be added to the service node list.
Note: This host is only temporarily attached to CloudBlue Commerce. It will be detached after running a script that copies CloudBlue Commerce settings of this node to the source MySQL node.
1.6. Mark the node as "Not Ready To Provide".
1.7. Set administrative MySQL user and password for the source node if you do not keep them in your records.
Go to CloudBlue Commerce Provider Panel > Infrastructure > Services > MySQL5 > Manage application "MySQL5" > General > Edit > set new values > Submit.
1.8. Migrate the database configuration.
Make sure that the destination MySQL configuration contains the necessary compatible settings from the source.
Especially, [mysqld] section in /etc/my.cnf and included files must be copied to the destination configuration. If the "MySQL 5.7 database server" node role is selected, add the following line to /etc/my.cnf in [mysqld] section:
plugin-load-add=validate_password.so
Restart the MySQL / MariaDB service after making the changes.
For MySQL:
# systemctl restart mysql.service
For MariaDB:
# systemctl restart mariadb.service
2. Migrate MySQL databases from the source server to the destination server.
Warnings:
Before migrating databases, back up the source MySQL database directory or the whole source server.
Plan in advance a maintenance window during the migration to avoid the difference between source and migrated databases. Depending on MySQL data volume, the migration can take one hour or several hours.
- Check the databases after the migration. All databases, tables and records from the source server must be present on the destination server.
2.1. Mark the source MySQL node as "not ready to provide". Enable the maintenance mode for the affected hosting services.
2.2. Migrate MySQL databases using the mysqldump utility.
-
Correct the source MySQL permission tables.
On the source host:
# cat >~/pw_file
ENTER_MYSQL_ROOT_PASSWORD
Ctrl+D
# mysql -uroot --password=`cat ~/pw_file` -Dmysql -e "update db set host='%' where host=''"
# mysql -uroot --password=`cat ~/pw_file` -Dmysql -e "FLUSH PRIVILEGES;"
-
Make a MySQL dump of all databases excluding "mysql" and "information_schema"
On the source host:
# mysql -uroot --password=`cat ~/pw_file` -Nse 'show databases' | grep -v '^\(information_schema\|mysql\)$' \
| xargs mysqldump --password=`cat ~/pw_file` --no-autocommit --databases | gzip >~/db_dump.gz
-
Make a MySQL dump of database users and privileges.
1. Download Percona Toolkit at https://www.percona.com/downloads/percona-toolkit/LATEST/ and install it on the source host.
2. Dump DB users and privileges:
# pt-show-grants -uroot --password=`cat ~/pw_file` --flush | gzip > ~/user_dump.gz
-
Copy the dump files to the destination host.
# cd
# rsync -e ssh -av --append-verify --progress db_dump.gz user_dump.gz root@DESTINATION:
-
Restore the databases from the MySQL dump.
On the destination host:
cat >~/pw_file
ENTER_MYSQL_ROOT_PASSWORD
Ctrl+D
# zcat db_dump.gz user_dump.gz | mysql -uroot --password=`cat ~/pw_file`
Do not remove the user_dump.gz file, you will need it again later.
- Copy the ~/pw_file from the source server to the destination server because MySQL root user credentials were also restored from user_dump.gz.
3. Change the service node role of the source node in CloudBlue Commerce to the value of the destination database node role using the script attached to this article.
3.1. Upload the script mysql_migration_rh6_to_rh7.py that is attached to this article to the Management node.
3.2. Run the script on the Management node:
# chmod +x mysql_migration_rh6_to_rh7.py
# ./mysql_migration_rh6_to_rh7.py --src-host-id SRC_ID --dst-host-id DST_ID
where SRC_ID is the source node ID in Service Nodes list, DST_ID is the destination node ID in the Service Nodes list.
4. Detach the "destination" database node.
Go to CloudBlue Commerce Provider Panel > Operations > Infrastructure > Service Nodes > select the newly attached node > Delete > confirm.
Wait until all tasks are completed. The host will be removed from service node list.
5. Move the IP addresses (private and public) from the source server to the destination server and turn off the source server.
NOTE: If the destination server had a hostname different from the source server, CloudBlue Commerce control panel would still display the hostname of the source server once re-registration is completed. To avoid the confusion, change the hostname of the destination server accordingly.
5.1. On the source server, change the IP addresses (private and public) to some other addresses.
5.2. Turn off the source server.
5.3. On the destination server, set the IP addresses (private and public) and the hostname to the initial values of the source server.
5.4. Restart the destination server to access it by new IP addresses.
6. Re-register the MySQL server in CloudBlue Commerce Provider Panel (it will be shown in the node list with the source node ID).
6.1. Go to CloudBlue Commerce Provider Panel > Operations > Infrastructure > Service Nodes > the MySQL node > Reregister, specify the "root" system user with the password, click Reregister, and then click OK.
6.2. Wait until all tasks are completed.
You can check them in CloudBlue Commerce Provider Panel > Operations > Tasks.
6.3. Restore database users again on the re-registered MySQL node (database users can disappear during re-registration):
# zcat user_dump.gz | mysql -uroot --password=`cat ~/pw_file`
Also, correct MySQL permission tables:
# mysql -uroot --password=`cat ~/pw_file` -Dmysql -e "update db set host='' where host='%'"
# mysql -uroot --password=`cat ~/pw_file` -Dmysql -e "FLUSH PRIVILEGES;"
6.5. Remove the line enabling the 'Validate Password' plugin from /etc/my.cnf if it was added earlier (MySQL 5.7 only) and restart the service:
# sed -i '/plugin-load-add=validate_password.so/d' /etc/my.cnf
# systemctl restart mysqld
6.5. Mark the database node as "Ready To Provide". Switch the services being in the maintenance mode to normal operation.