How to centralize MySQL databases

Par: Eric Gipon 16-11-2021

Catégories:Blog, Open Source, Technology,

When you're just starting out with your IT environment, everything is still very manageable. But once you're a few years down the road, you might find yourself with a tangle of databases. Centralizing can be the solution, Eric Gipon tells us how in this blog.

From organized stack to chaos

You have a great idea and start a business. You take care of the paperwork and other things needed to get started. Of course, this also includes a modern IT environment, so you hire a brainiac to build it for you. Before you know it, a few years have passed. Your company is doing well and it is time to take a closer look at some internal aspects. That automatically brings you back to your IT environment. When you take a good look at it, you realize that it has become one big plate of spaghetti and you have no idea where it starts and where it ends. And while you once started with two well-organized databases, you now have 24, spread across seven servers. What goes where? You really have no idea. Your environment has become a so-called microservice database environment, and it's time for reorganization. Centralization might just be the solution.

MySQL centralizing databasesThe benefits of centralizing your databases

Centralizing is not the key to success for every company. Having several database servers and several database environments with different database types can be a deliberate choice. But often centralizing gives you a lot of benefits.

1. Server management

If you have seven database servers, you also have to patch all seven. I wouldn't dream of it, what a lot of work! With one server you make the life of your database administrator a lot easier.

2. Database administration

How nice, a new employee! Oh yes, detail: she has to be able to access all databases. Well, then you just add her to all 24 databases, on all seven servers, right? Life will be a lot easier if it only has to be done in one environment. The chance of skipping something somewhere becomes a lot smaller.

3. Cost savings

The operating system already requires the necessary CPU and RAM. If you have one central server, you only have to deal with this once instead of seven times. Centralization makes it a lot easier to provide the desired overhead in CPU, RAM and storage. Moreover, with seven servers you have to deal with seven times the license costs. You often pay these per year or per month, just like in the cloud. I don't have to explain to anyone that paying license fees once is a lot cheaper than paying them seven times.

Points of attention before centralizing

1. DBMS

First of all, determine which database management system (DBMS) you need. Not only do you have to decide whether you want to use MySQL or MariaDB, but also which version. MySQL 5.5 is really different than MySQL 8.0. Also take into account whether you are compatible. If your environment runs well on MySQL 5.7, that does not mean it will run smoothly on MySQL 8.0. Test your application for compatibility with the chosen DBMS. Do not forget to check the expiration date of the support for your DBMS.

2. Server setup

Nothing beats a freshly installed server. It does not matter whether you are talking about a Linux or a Windows environment. If you have the possibility, set up a new server before centralizing. This will get you more than if you start working with your old server. First, determine how much CPU, RAM and storage space you will need. Note that how much of these resources are used can vary from operating system to operating system. To be able to easily upgrade your operating system and DBMS in the future, it is useful to make the data directory independent of the other file systems.

3. Operating System

Choose an appropriate operating system. Much open source software is developed on a specific operating system and then made compatible with other operating systems, for example, written on RedHat and made suitable for Fedora or Windows. With both the operating system and the DBMS, it is important to consider end-of-life support. Linux has many advantages and is often more economical than Windows. But perhaps you are dependent on Windows?

4. Encryption 

Do you use encryption within your database environment? Then it is certainly important to determine whether this is necessary. If so, check whether your applications can handle it if your databases use encryption.

5. Character set

Think about whether you opt for UFT8 or Latin1 and case sensitive or case insensitive and check whether your applications can deal with your chosen option.

6. Date and time indication

In the Netherlands, the 'datetime' is often YYYY-MM-DD HH:MM:SS, but in the United States YYYY-DD-MM is usually used for dates. If you start centralizing, you might find that your years are correct, but that your records occur very often in December - the twelfth month of the year. Oops! When you try to put American dates into a Dutch format, the days are put in the field before the month. All days above the 12th of the month will then be set back to 12. Check which datetime is being used and if necessary, do a conversion before transferring the data to the new server.

Let's go!

All right, you've made your choices. You are going for a Percona Server for MySQL8.0. The server is set up with CentOS7 and equipped with enough resources. Let's go! Make a backup of your databases and restore them to the new server. If it is possible to create a separate schema dump like MySQL, do so. 

Downtime 

Ho, but wait a minute! This means you'll have to deal with downtime! That costs way too much money. Your business is running 24/7, isn't there an alternative? Yes, there is, namely data-forwarding like with a database cluster. When setting up a cluster, with the current environment as primary and the new server as secondary, you can transfer your data that way. Once the new server is synchronized, route the application to the new server and perform a fail-over at the database level so that the new server is primary and can be written to.

Budget left

MySQL clusterCentralization has been done now. What else could you do to make your environment more stable? You saved on costs by going from seven servers to one, so there is some budget left. More CPU? More RAM? Although CPU and RAM are certainly essential, there is something else that may be more important. Have you thought about a database cluster? With this you set up two identical servers, with one being primary and the other secondary. If your primary server goes down, the secondary server can become the new primary server and your environment will remain active and your customers will not experience an outage. An added benefit with a cluster is that you can route all read actions to the secondary server.

In a nutshell

Centralizing, therefore, helps you manage both your server environment and your database instances. You save on resources and licensing costs. Whatever choice you make for your new environment, make sure your application is compatible. Centralize through backup and recovery or through a data forwards to the new server, as with setting up a database cluster.

Are you ready?

Now only one question remains: are you ready to start centralizing! Feel free to contact us, we'd love to help you.

Other blogs about MySQL

Help! MySQL is spinning out of control

Full throttle with MySQL - part 2

Profit from performance tuning

Back to blogoverview