Migrate from MySQL 8.0 to MariaDB 10.6 on Ubuntu 20.04 LTS (Focal Fossa)

DigitalOcean Referral Badge
Start your VPS now with FREE $100 credit.

If you have currently planning to migrate or move your database server from MySQL 8.0 to the long-term-support version of MariaDB 10.6 database server, kindly read the following documentation. It’s quite easy to applied on your production server, either it running under VPS, Cloud or Phisycal Servers.

Or maybe you have plan to migrate your current production server from AWS (Amazon Web Service) to Google Cloud Platforms (GCP). Well, web content (its folders and files), database (all db files and its db users) are critical parts which should be touched with care and treated differently. This should be done to minimize data loss if you have plan to move your running website from the old server to new server.

Apart from that, this guide can be applied as well to migrate your running MySQL 8.0 into MariaDB 10.6 on the current server. So, you haven’t plan to move your current bare metal or cloud to the new server. It mean you can detroy (remove/uninstall) the current MySQL installation and then deploy new MariaDB environments on the same server or machine.

PREREQUISITES:

* VM, Cloud or Physical Servers
* OS Flavours: Ubuntu 20.04 LTS (Focal Fossa)
* Superuser Privileges (Root Access)
* Gnome Terminal for Linux Desktop
* PuTTy SSH Client for Windows or Mac
* Powershell for Windows 10/11
* Familiar with Basic APT Commands on Debian/Ubuntu Distros

It can be applied on all Debian/Ubuntu based distros. However, before doing it, make sure you have create backup files for all of your databases. Simply run the following command to backup your database file.

1.) Export or Backup Your Database

Just sit back and relax, since the method below it’s super easy to applied via SSH terminal or via command line only. Login to SSH terminal on your old server and then run the following commands to create backup for all of your databases.

~# mysqldump -u [username] -p [database name] > [database name].sql

For example you can use above command to backup your database with root privileges, as follow.

~# mysqldump -u root -p fcgid > fcgid.sql

PLEASE NOTE:

* mysqldump is command to backup/export database into .sql file
* root is superuser priveleges for MySQL/MariaDB Access
* fcgid is database name
* For security reason you must backup your database one by one
* Make sure to not backup all

2.) Create Backup for Website Contents (Directory, Folder and its Files)

READ:  How To Install MySQL 8.0 on Debian 10 "Buster"

Still logged in on your old server, you must run the following command to create full-backup of your website content, including all active directory, folder and its files.

Go to root directory or public_html folder of your website. This must be done one by one per site. Iy you have running under Nginx web server or LEMP Stack, it usually located at the following directory.

~# cd /usr/share/nginx/html

Or if you have previously deploy Nginx web server on Ubuntu 18.04 – 20.04 LTS machine using PPA (Personal Package Archive) from Launchpad repository, it usually has been compiled with the following document root (default website docs root or public_html folder for Nginx).

~# cd /var/www/
~# ls
html yourdomain.com yourdomain1.com yourdomain2.com
yourdomain3.com yourdomain4.com yourdomain5.com

Then run the following command to make a full-backup of your website content in single archive file, including all of your website folder and files.

~# tar -czvf migrate-data.tar.gz yourdomain.com
~# ls
migrate-data.tar.gz html yourdomain.com yourdomain1.com yourdomain2.com
yourdomain3.com yourdomain4.com yourdomain5.com

* PLEASE NOTE: “tar -czvf” is command to compress your website docs root into single archive file with .tar.gz archive format.
Then you can download this backup file with “wget command” via SSH terminal on the new server

3.) Uninstall MySQL 8.0 on the Current Server

On the current server (vps, cloud server or bare metal) run the following commands via command-line to uninstall, remove, or destroy the current running MySQL 8.0 database server. You should do it if you want to migrate, move or upgrade your current MySQL 8.0 to the MariaDB 10.6 database server.

~# apt-get remove –purge mysql-server mysql-client mysql-common -y
~# apt-get autoremove -y
~# apt-get autoclean
~# rm -rf /etc/mysql

4.) Add MariaDB 10.6 APT Repository to Your Ubuntu Machine

READ:  How To Install MySQL 8.0.26 on AlmaLinux 8.5

After you have uninstall MySQL 8.0 on your current system, you can continue to the next step to install and configure MariaDB 10.6 database server. Well, simply add the following MariaDB 10.6 APT Repository into your Ubuntu 20.04 LTS machine. Just do it in few simple commands below.

-------------------------------------------------------------------
~# sudo apt-get install software-properties-common dirmngr apt-transport-https
~# sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
~# sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el,s390x] https://mirror.rackspace.com/mariadb/repo/10.6/ubuntu focal main'
-------------------------------------------------------------------
MariaDB Software Repository Package
MariaDB Software Repository Package

You can also manually add the following MariaDB software repository packages under “/etc/apt/sources.list.d” directory. You just need to create a file named “MariaDB.list” under “/etc/apt/sources.list.d” directory. After that, put the below snippet code in the “MariaDB.list” file. Or simply add these snippet code at the bottom line of the file of “/etc/apt/sources.list”.

-------------------------------------------------------------------
# MariaDB 10.6 repository list - created UTC
# https://mariadb.org/download/
deb [arch=amd64,arm64,ppc64el,s390x] https://mirror.rackspace.com/mariadb/repo/10.6/ubuntu focal main
deb-src https://mirror.rackspace.com/mariadb/repo/10.6/ubuntu focal main
-------------------------------------------------------------------

5.) Next, Let’s Install MariaDB 10.6 Database Server

Both software or package dependencies and MariaDB signing key have been imported into your system. Next, update your software repository packages and then let’s install MariaDB 10.6 database server with the following commands.

~# sudo apt update
~# sudo apt install mariadb-server

6.) Enable, Start and Securing or Harden Your MariaDB 10.6 Server

~# systemctl enable mysql.service
~# systemctl start mysql.service
~# mysql_secure_installation

7.) Upgrade or Force Upgrade Your Database Server from MySQL 8.0 to MariaDB 10.6

During installation progress MariaDB 10.6 will force to upgrade all of your mysql database files, including database tables, database names, database users, database schema, and much more. You can add –force at the end of the following command if you want to force MariaDB to run “mysql_upgrade”.

mysql_upgrade command
mysql_upgrade command
~# mysql_upgrade
Enter password:

OR

~# mysql_upgrade --force
Enter password:

OR

~# mysql_upgrade -u root -p
Enter password:

OR

~# mysql_upgrade -u root -p --force
Enter password:
This installation of MariaDB is already upgraded to 10.6.7-MariaDB.
There is no need to run mysql_upgrade again for 10.6.7-MariaDB.
You can use --force if you still want to run mysql_upgrade
root@fcgid-MS-7C13:/etc/mysql# mysql_upgrade -u root -p --force
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.global_priv OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic 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.servers 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.transaction_registry OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user OK
sys.host_summary OK
sys.host_summary_by_file_io OK
sys.host_summary_by_file_io_type OK
sys.host_summary_by_stages OK
sys.host_summary_by_statement_latency OK
sys.host_summary_by_statement_type OK
sys.innodb_buffer_stats_by_schema OK
sys.innodb_buffer_stats_by_table OK
sys.innodb_lock_waits OK
sys.io_by_thread_by_latency OK
sys.io_global_by_file_by_bytes OK
sys.io_global_by_file_by_latency OK
sys.io_global_by_wait_by_bytes OK
sys.io_global_by_wait_by_latency OK
sys.latest_file_io OK
sys.memory_by_host_by_current_bytes OK
sys.memory_by_thread_by_current_bytes OK
sys.memory_by_user_by_current_bytes OK
sys.memory_global_by_current_bytes OK
sys.memory_global_total OK
sys.metrics OK
sys.processlist OK
sys.ps_check_lost_instrumentation OK
sys.schema_auto_increment_columns OK
sys.schema_index_statistics OK
sys.schema_object_overview OK
sys.schema_redundant_indexes OK
sys.schema_table_lock_waits OK
sys.schema_table_statistics OK
sys.schema_table_statistics_with_buffer OK
sys.schema_tables_with_full_table_scans OK
sys.schema_unused_indexes OK
sys.session OK
sys.session_ssl_status OK
sys.statement_analysis OK
sys.statements_with_errors_or_warnings OK
sys.statements_with_full_table_scans OK
sys.statements_with_runtimes_in_95th_percentile OK
sys.statements_with_sorting OK
sys.statements_with_temp_tables OK
sys.user_summary OK
sys.user_summary_by_file_io OK
sys.user_summary_by_file_io_type OK
sys.user_summary_by_stages OK
sys.user_summary_by_statement_latency OK
sys.user_summary_by_statement_type OK
sys.version OK
sys.wait_classes_global_by_avg_latency OK
sys.wait_classes_global_by_latency OK
sys.waits_by_host_by_latency OK
sys.waits_by_user_by_latency OK
sys.waits_global_by_latency OK
sys.x$host_summary OK
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 OK
sys.x$memory_by_thread_by_current_bytes OK
sys.x$memory_by_user_by_current_bytes OK
sys.x$memory_global_by_current_bytes OK
sys.x$memory_global_total OK
sys.x$processlist OK
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 OK
sys.x$schema_flattened_keys OK
sys.x$schema_index_statistics OK
sys.x$schema_table_lock_waits OK
sys.x$schema_table_statistics OK
sys.x$schema_table_statistics_with_buffer OK
sys.x$schema_tables_with_full_table_scans OK
sys.x$session OK
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 OK
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

Well, from this step you have successfully on migrate your database server from MySQL 8.0 to the MariaDB 10.6. Kindly try to use this tutorial or guide on your production servers, since this is have been tested many time on my production and development server. Or if you are still unsure on this guide you can buy a cheap $5-$10 vps or cloud server either it on Digital Ocean, Vultr, Linode, Amazon Web Service (AWS) or Google Cloud Platform (GCP), then try to use this tutorial. See you next time… 🙂

Leave a Comment