MySQL

MySQL is a relational database used in online shops, CMS platforms, SaaS applications and internal software. Tables, relations and transactions help keep data about customers, orders, payments and documents consistent.

MySQL’s popularity makes it easy to find drivers, tools and cloud services. It doesn’t, however, remove the need to design the schema, indexes, backups and permissions. A database can run without problems for years if its model matches what the application does and it is monitored regularly.

We can set up a new environment, optimise an existing database or plan a migration. We start with the data and the risks: which processes are critical, how much downtime is acceptable and what is holding the application back today.

MySQL is a good fit for systems with well-structured relationships and a large number of typical transactional operations. If an application saves an order with its line items, checks the payment and must identify the customer unambiguously, the relational model gives clear rules.

It’s worth considering if your team uses PHP, Java, Node.js or another ecosystem with a mature MySQL driver. The wide availability of hosting and managed services makes it easier to match the infrastructure to your budget.

If the project needs specific PostgreSQL features or its data is naturally document-shaped, another engine may be a better fit. We don’t treat a database migration as a goal in itself. The technology should simplify a specific process or reduce risk.

We start designing the schema from the operations the application performs. We decide which data must stay together, where uniqueness is needed and which relationships should be protected by foreign keys. Constraints in the database stop some errors regardless of which service is writing the data.

A transaction lets several related operations complete together. If saving an order fails, the database can roll back its line items and any reservations made in the same transaction. The boundaries need to be chosen carefully, though, so that locks aren’t held during a long call to an external API.

We make structural changes through versioned migrations. For large tables we assess the lock time, the extra disk space needed and whether the operation can be done online or in stages.

A slow query can be caused by a missing index, the wrong column order, fetching too much data or a lock. We analyse the execution plan, the slow query log, CPU load, memory and disk activity. Only then do we change the configuration or the infrastructure.

An index speeds up searching, but it makes writes more expensive and takes up space. We choose indexes to match the real filters, sorting and joins. We remove unnecessary indexes and check that, after the change, queries really do scan less data.

Sometimes the biggest improvement comes from a cache or from changing an API that runs a series of similar queries. So optimisation covers both the database and the way the application uses it. We put off a bigger server until measurements show it is needed.

Replication passes changes from the source instance to replicas. It can spread read load, support analytics and shorten recovery time after a failure. You do, however, need to monitor replication lag and remember that an asynchronous replica may briefly be missing the latest write.

MySQL offers Group Replication, InnoDB Cluster, ReplicaSet and routing tools, among others. The choice depends on the required level of availability, the number of locations and the acceptable risk of losing the most recent transactions.

Automatic failover needs the whole path to be tested: choosing the new primary, redirecting connections and how the application behaves. Simply having a second server doesn’t guarantee that the system will be back up within the expected time.

We design backups around RPO and RTO. The first defines how much of the most recent data you can afford to lose, the second how long it may take to get the system back. These values determine how often backups run, how binary logs are used and where copies are stored.

Backups can be taken from a replica to reduce the load on the main instance. Replication is not a backup, though: a faulty operation can be copied along with everything else. You need independent retention and protection against a single account being able to change or delete every copy.

We regularly restore the database in a separate environment and check the application’s data. That way the procedure is familiar before a failure, not only at the moment when time matters most.

Each application should use its own account with the minimum permissions it needs. We restrict administrative access at network level, encrypt connections and keep credentials outside the code. We regularly update the server and the drivers the application uses.

MySQL can run on your own server, in a container or as a managed cloud service. In the cloud the provider can take over part of the backups, updates and high availability, but the configuration still needs oversight. Self-hosting gives more freedom at the cost of more operational responsibility.

Monitoring covers connection usage, replication lag, locks, errors and storage capacity. An alert should fire before the disk fills up, not after writes have stopped.

Before changing versions, we check for incompatibilities, character sets, SQL modes, the functions the application uses and driver compatibility. We do a trial run on a copy of the data and test the most important processes.

With a large database, migration may require replication to the new environment and a short switchover of traffic. The plan also includes a way back, consistency checks and monitoring after go-live.

Moving from MariaDB, PostgreSQL or another engine needs extra mapping of data types, queries and transaction behaviour. An automated tool will move part of the structure, but it won’t make decisions about the data model or performance.

MySQL is a mature choice for many transactional applications and has a broad ecosystem. MariaDB remains highly compatible but is developed independently. PostgreSQL often offers more advanced SQL features and data types, while MongoDB takes a document-based approach.

We compare the application’s requirements, the team’s skills, the cloud services available, how the system needs to scale and the cost of migration. If your current database works well, switching technology may bring less benefit than improving the schema, indexes and backup process.

It’s worth testing the decision on representative data and queries. That shows not only the speed but also how complex the implementation and later maintenance will be.

Fortunately, we work with every database technology discussed here, so if your requirements call for a specific database engine, we don’t limit ourselves to MySQL.