Upgrade MySQL-5.7 to MariaDB-10.2 Galera

Scenario

Existing server
-DB: MySQL-5.7 Master-slave, DB size: 140GB

New Server
– DB = MariaDB-10.2 (Galera Cluster)

Following are the steps that must be done to migrate and upgrade MySQL-5.7 to MariaDB-10.2

• Stop the mysql service on the MySQL-5.7 server

# service mysql stop

• Copy the full directory / var / lib / mysql or your custom datadir (/ data / mysql) to the new server, adjust it to my.cnf

# rsync -avz /data/mysql/* dbadmin@192.168.177.121:/data/mysql/

• After the rsync process has finished changing the owner of the file and directory belongs to mysql

# chown -R mysql:mysql /data/mysql/

• Start service mariadb (in stand alone condition)

# service mariadb start

• If there are no obstacles, then run mysql_upgrade immediately. The goal is for the MySQL-5.7 system table and convert to the MariaDB system table

# mysql_upgrade -u root -p --socket /data/mysql/mysql.sock

(the –socket option is used if the mysql.sock path is not standard in /var/run/mysql.sock)

• If there are obstacles when doing mysql_upgrade such as table corupt or index corupt, the solution is to drop and recreate the table or the corrupted index, but don’t forget to backup the data first without the tabular structure
• Then restart the mariadb service after you have finished running mysql_upgrade

• The next step is to do a boostrap to build the mariadb galera cluster, but before rebooting and syncing the node changes the timeout in migrated-from-my.cnf-settings.conf to 1800 sec, because the data size in sync is quite large

# root @ mdb01: ~ # vim /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf

# converted using /usr/bin/mariadb-service-convert
#
 [Service]
 TimeoutStartSec=1800
 User=mysql
 StandardOutput=syslog
 StandardError=syslog
 SyslogFacility=daemon
 SyslogLevel=err
 SyslogIdentifier=mysqld

• Bootstrap node1, run the following command to bootstrap

# galera_new_cluster

• Then sync nodes 2 and 3 alternately. if you have logged into mariadb and check the wsrep cluster status

MariaDB [mysql]> show status like 'wsrep_lo%';

---------------------------- + -------------------- ------------------ +
 | Variable_name | Value |
---------------------------- + -------------------- ------------------ +
 | wsrep_local_bf_aborts | 0 |
 | wsrep_local_cached_downto | 1 |
 | wsrep_local_cert_failures | 0 |
 | wsrep_local_commits | 0 |
 | wsrep_local_index | 0 |
 | wsrep_local_recv_queue | 0 |
 | wsrep_local_recv_queue_avg | 0.066667 |
 | wsrep_local_recv_queue_max | 2 |
 | wsrep_local_recv_queue_min | 0 |
 | wsrep_local_replays | 0 |
 | wsrep_local_send_queue | 0 |
 | wsrep_local_send_queue_avg | 0.000000 |
 | wsrep_local_send_queue_max | 1 |
 | wsrep_local_send_queue_min | 0 |
 | wsrep_local_state | 4 |
 | wsrep_local_state_comment | Synced |
 | wsrep_local_state_uuid | 496cf1c0-4609-11e9-bf77-02596c486e7d |
---------------------------- + -------------------- ------------------ +
 17 rows in set (0.01 sec)

MariaDB [mysql]> show status like 'wsrep_clu%';

-------------------------- + ---------------------- ---------------- +
 | Variable_name | Value |
-------------------------- + ---------------------- ---------------- +
 | wsrep_cluster_conf_id | 5 |
 | wsrep_cluster_size | 3 |
 | wsrep_cluster_state_uuid | 496cf1c0-4609-11e9-bf77-02596c486e7d |
 | wsrep_cluster_status | Primary |
 | wsrep_cluster_weight | 3 |
-------------------------- + ---------------------- ---------------- +
 5 rows in set (0.00 sec)

MariaDB [mysql]> show status like 'wsrep_p%';
------------------------ + ------------------------ ----------- +
 | Variable_name | Value |
------------------------ + ------------------------ ----------- +
 | wsrep_protocol_version | 9 |
 | wsrep_provider_name | Galera |
 | wsrep_provider_vendor | Codership Oy info@codership.com |
 | wsrep_provider_version | 25.3.25 (r3836) |
------------------------ + ------------------------ ----------- +
 4 rows in set (0.01 sec)

MariaDB [mysql]> show status like 'wsrep_in%';

-------------------------- + ---------------------- ------------------------------------------ +
 | Variable_name | Value |
-------------------------- + ---------------------- ------------------------------------------ +
 | wsrep_incoming_addresses | 192.168.10.121:3306,192.168.10.122:3306,192.168.10.123:3306 |
-------------------------- + ---------------------- ------------------------------------------ +
 1 row in set (0.00 sec)

• If all the results are in accordance, then the migration process has been successfully carried out