release Switching to MariaDB: A Comprehensive Guide

[Queen]

In this article, we'll explore how to install MariaDB as a replacement for Oracle MySQL and discuss the rationale behind this decision.

Why Choose MariaDB Over MySQL?

MariaDB is a fork of MySQL, designed to be fully compatible with it. Many industry predictions suggest that MariaDB will eventually take over the role of Oracle MySQL Server as the preferred relational database management system (RDBMS).

One of the key advantages of MariaDB is its enhanced performance and data integrity features. Notably, it includes the Aria storage engine, which is an improvement over the older MyISAM storage engine used by some private game servers like Metin2. MyISAM has limitations, including bugs and a lack of ACID compliance, which InnoDB provides. MariaDB also integrates XtraDB, an enhanced version of InnoDB. If you're unable or prefer not to convert your Metin2 tables from MyISAM to either InnoDB or XtraDB (the recommended options), Aria serves as an effective alternative storage engine.

How to Install MariaDB on FreeBSD

Managing MariaDB is quite similar to handling MySQL. The service is referred to as mysql-server, and its home directory is located at /var/db/mysql, just like the traditional Oracle MySQL server.

Steps to Remove the Existing MySQL Installation

1. Create a security backup of your MySQL data directory:

• This is the hidden content, please

  • Sign In
  • or
  • Sign Up

   

• Perform a comprehensive backup of your databases (for example, Metin2 or your website) using dump. You can utilize the CLI tool mysqldump or a GUI like Navicat:

  • Right-click on the database name -> Dump SQL file -> Structure and data.

• Stop the MySQL server:

• This is the hidden content, please

  • Sign In
  • or
  • Sign Up

   

• Remove the MySQL directory:

• This is the hidden content, please

  • Sign In
  • or
  • Sign Up

   

• Check the installed MySQL version:

• This is the hidden content, please

  • Sign In
  • or
  • Sign Up

   

• Uninstall MySQL from your ports:

6. This is the hidden content, please

   • Sign In
   • or
   • Sign Up

   Replace xx with the version identified in the previous step.

Installing MariaDB

This guide is based on the version of MariaDB 10.1.

1. Update your ports tree:

• This is the hidden content, please

  • Sign In
  • or
  • Sign Up

   

• Install MariaDB:

• This is the hidden content, please

  • Sign In
  • or
  • Sign Up

   

• If it’s not already enabled, activate the MySQL service:

• This is the hidden content, please

  • Sign In
  • or
  • Sign Up

   

• Start the MySQL server:

4. This is the hidden content, please

   • Sign In
   • or
   • Sign Up

    

5. Import your application databases (e.g., Metin2, website) from the backup dump. Avoid importing from the data file!

   If you're using Navicat, remember to set up a new connection for MariaDB instead of MySQL.

Converting MyISAM to a New Storage Engine

For improved performance and data security, it’s advisable to convert your tables to a new storage engine. You can select different storage engines for each table.

• Opt for Aria for tables with predominantly read operations and low write frequency (e.g., item_proto, mob_proto, shop, shop_item) or for those utilizing the FULLTEXT indexing feature for efficient text searches.

• Choose XtraDB for critical tables with a high write throughput (e.g., player, item, account).

To convert tables between storage engines, you have two approaches:

1. Using a GUI like Navicat: Right-click on the table -> Design Table -> Options -> Engine. Change it, and then save.

2. Using a query:

2. This is the hidden content, please

   • Sign In
   • or
   • Sign Up

    

Important Considerations

While transitioning to MariaDB offers many benefits, it doesn't negate the need for regular database backups.

• Regularly back up your application databases using the mysqldump utility to safeguard your data.

• For added protection, perform a data file backup of the entire MySQL data directory (/var/db/mysql). This creates a safety net for emergencies if the data import fails.

Use data file restoration only when there are no available alternatives and strictly for the entire MySQL directory.