How to install MariaDB on Windows. How to upgrade from MySQL to MariaDB on Windows

Table of contents

1. What is MariaDB

2. Why MariaDB is better than MySQL

3. Download MariaDB for Windows

4. Installing MariaDB on Windows

5. Upgrading from MySQL to MariaDB on Windows

5.1 Upgrading from MySQL to MariaDB with saving databases

5.2 Upgrading from MySQL to MariaDB with reset of databases

What is MariaDB

MariaDB is a database management system (DBMS), which is based on MySQL and is largely compatible with it.

MariaDB and MySQL are fully compatible in the syntax of SQL queries. That is, if your program uses MySQL databases (for example, a website in PHP), then when you upgrade to MariaDB, you do not need to change anything in the program.

MariaDB is also binary compatible with MySQL connectors. Therefore, you do not need to change them after the upgrading.

MariaDB is compatible with MySQL database formats, but there are some nuances. If you transfer databases through export/import (for example, using a .SQL file), then the transferred databases will be fully compatible between any versions and do not require any further action. However, if you installed MariaDB on top of MySQL, that is, MariaDB uses database files of MySQL, then compatibility should be considered:

• MariaDB 10.2 is compatible with previous versions of MariaDB data files, as well as with MySQL 5.6 and MySQL 5.7, but is not compatible with MySQL 8.0.
• MariaDB 10.1 is compatible with previous versions of MariaDB data files, as well as with MySQL 5.6.

Additional information: https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/

MariaDB runs on Windows and Linux. This program is completely open source. It is distributed in the form of source codes, as well as compiled executable files for Windows and all popular Linux distributions.

Why MariaDB is better than MySQL

MariaDB supports more Storage Engines.

In addition to the standard MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, and MERGE storage engines, MariaDB also has the following:

• ColumnStore, a column oriented storage engine optimized for Data warehousing.
• MyRocks, a storage engine with great compression, added from version 10.2
• Aria, MyISAM replacement with better caching..
• FederatedX (drop-in replacement for Federated)
• OQGRAPH.
• SphinxSE.
• TokuDB.
• CONNECT.
• SEQUENCE.
• Spider.
• Cassandra.

MariaDB has many different improvements and optimizations in the speed of data processing.

MariaDB is supplemented with new extensions and functions.

Details: https://mariadb.com/kb/en/library/mariadb-vs-mysql-features/

Download MariaDB for Windows

MariaDB is free, it is strongly recommended to download it from the official website. Download MariaDB: https://downloads.mariadb.org/

You will see several series - several major versions of the program. If you do not need any specific compatibility with MySQL, then just select the latest version and click the ‘Download’ button.

Since this program runs on different operating systems, in the next window you will see a large selection of files for download.

Windows x86_64 file are 64-bit versions, and Windows x86 are 32-bit versions.

.zip are portable versions that you need to install by yourself, but which give you complete freedom in fine tuning..msi is an installer for Windows.

In this manual, I will use the .zip version.

On the next page, you just need to click the button: ‘No thanks, just take me to the download’:

Installing MariaDB on Windows

For Windows, the MariaDB is distributed as an installer and ZIP archive. I prefer installation from a ZIP archive, since it gives full control over the process.

Installation in all the examples I perform in the C:\Server\bin\ folder, because MariaDB is part of the web server installed according to this guide. If your conditions are different, then consider this and make appropriate amendments.

Unzip the downloaded archive from MariaDB, rename the folder to mariadb and move it to C:\Server\bin\.

Move the C:\Server\bin\mariadb\data\ folder to the C:\Server\data\DB\ folder.

In the C:\Server\bin\mariadb\ folder create a my.cnf file and copy into it:

[mysqld]

datadir="c:/Server/data/DB/data/"

To install and lunch the service, run the following commands:

C:\Server\bin\mariadb\bin\mysqld --install
net start mysql

Upgrading from MySQL to MariaDB on Windows

Upgrading from MySQL to MariaDB with saving databases

You can perform the upgrading in different ways. I will show the most universal method, which guarantees full compatibility and the absence of further problems.

You need to start by creating a backup of the databases. We will do this in the Windows command line using the mysqldump utility (supplied with MySQL and located in the bin folder).

Open a Windows command prompt. To do this, click Win+x and select Windows PowerShell (Administrator). In the opened window, type

cmd

Let us move to the folder where this utility is located (you may have another path):

cd C:\Server\bin\mysql-8.0\bin\

Dump (backup) all databases with the following command:

mysqldump.exe -u root -p --all-databases > all-databases.sql

Now in the C:\Server\bin\mysql-8.0\bin\ folder the all-databases.sql file will appear - be sure to copy it to a safe place!

Now stop the MySQL service and remove it from startup:

net stop mysql
c:\Server\bin\mysql-8.0\bin\mysqld --remove

In addition, copy the C:\Server\data\DB\data\ folder to the safe place - this is an additional backup of the MySQL database files - in case something goes wrong with MariaDB and you want to roll back to MySQL.

Now delete the C:\Server\bin\mysql-8.0\ (binary files) and C:\Server\data\DB\data\ folders.

Unzip the downloaded archive from MariaDB, rename the folder to mariadb and move it to C:\Server\bin\.

Move the C:\Server\bin\mariadb\data\ folder to the C:\Server\data\DB\ folder.

In the C:\Server\bin\mariadb\ folder create a my.cnf file and copy into it:

[mysqld]

datadir="c:/Server/data/DB/data/"

To install and launch the service, run the following commands:

C:\Server\bin\mariadb\bin\mysqld --install
net start mysql

To deploy databases from a backup, go to the C:\Server\bin\mariadb\bin\ folder:

cmd
cd C:\Server\bin\mariadb\bin\

And run the command like this:

mysql -uroot < C:\path\to\backup.sql

For example, I have the all-databases.sql file with a backup of the databases located in the h:\Dropbox\!Backup\ folder, then my command is this:

mysql -uroot < h:\Dropbox\!Backup\all-databases.sql

Wait until the import is complete - if the file is large, the process can take for a while.

Upgrading from MySQL to MariaDB with reset of databases

Stop the MySQL service and remove it from startup:

net stop mysql
c:\Server\bin\mysql-8.0\bin\mysqld --remove

Delete the C:\Server\bin\mysql-8.0\ (binary files) and C:\Server\data\DB\data\ folders.

Unzip the downloaded archive from MariaDB, rename the folder to mariadb and move it to C:\Server\bin\.

Move the C:\Server\bin\mariadb\data\ folder to the C:\Server\data\DB\ folder.

In the C:\Server\bin\mariadb\ folder create a my.cnf file and copy into it:

[mysqld]

datadir="c:/Server/data/DB/data/"

To install and launch the service, run the following commands:

C:\Server\bin\mariadb\bin\mysqld --install
net start mysql