Installing MariaDB on Windows. Switching from MySQL to MariaDB on Windows

What is MariaDB

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

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

MariaDB is also binary compatible with MySQL connectors. Those. If you use MySQL connectors, you don't need to change them when moving to MariaDB.

MariaDB is compatible with MySQL database formats, but there are some caveats. If you transfer databases via export/import (for example, using a .SQL file), then the databases transferred in this way will be fully compatible between any versions and do not require any further actions. However, if you installed MariaDB on top of MySQL, that is, MariaDB uses database files from MySQL, then you need to consider compatibility:

• MariaDB 10.2 is compatible with previous versions of MariaDB data files, as well as 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 MySQL 5.6.

More 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 both in the form of source codes and compiled executable files for Windows and all popular Linux distributions.

Why MariaDB is better than MySQL

MariaDB supports more storage engines (Storage Engines).

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

• ColumnStore, a column-oriented storage system, is optimized for data warehousing.
• MyRocks, a highly compressed storage system, added in version 10.2
• Aria, a replacement for MyISAM with improved caching.
• FederatedX (replacement for Federated)
• OQGRAPH.
• SphinxSE.
• TokuDB.
• CONNECT.
• SEQUENCE.
• Spider.
• Cassandra.

MariaDB has many different improvements and optimizations in processing speed.

MariaDB has been updated with new extensions and features.

Download MariaDB for Windows

MariaDB is free and it is highly recommended to download it from the official website. MariaDB download page: https://downloads.mariadb.org/

You will see several episodes - several major versions of the program. If you do not need any specific compatibility with MySQL, then simply select the latest version and click the “Download” button

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

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

.zip- These are portable versions that need to be installed independently, but which give complete freedom in fine tuning. A .msi is an installer for Windows.

In this instruction I will show you an example of working with the version .zip.

On the next page, just click the button: “ No thanks, just take me to the download»:

Installing MariaDB on Windows

For Windows, the MariaDB DBMS is distributed as an installer and a ZIP archive. I prefer installing from a ZIP archive because it gives me complete control over the process.

In all examples I install in the folder C:\Server\bin\, since I have MariaDB as part of a web server installed with . If yours is different, then take this into account and make appropriate adjustments.

mariadb and move to C:\Server\bin\.

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

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

Switching from MySQL to MariaDB on Windows

Switching from MySQL to MariaDB while maintaining databases

You can make the transition in different ways. I will show you the most universal method that guarantees full compatibility and no further problems.

You need to start by creating a backup copy of your databases. We'll do this on the command line using a utility (comes with MySQL and is located in the folder bin).

Open Windows Command Prompt. To do this, click Win+x and select Windows PowerShell (Administrator). In the window that opens, do

Let's go to the folder where this utility is located (you may have a different path):

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

Make a dump (backup) of all databases with the following command:

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

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

Now stop the MySQL service and remove it from startup:

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

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

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

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

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

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

To install and start the service, run the commands:

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

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

Cmd cd C:\Server\bin\mariadb\bin\

And run a command like:

Mysql -uroot< C:\путь\до\файла\резервной_копии.sql

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

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

Wait until the import is completed - if the file is large, the process may take longer.

Switching from MySQL to MariaDB without saving databases

Stop the MySQL service and remove it from startup:

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

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

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

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

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

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

To install and start the service, run the commands:

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

This article, Installing MariaDB 10 on CentOS 7, will show you how to install and configure MariaDB 10 on CentOS 7. As you probably already know, the MariaDB database server is now on CentOS by default due to Oracle (MySQL parent company) and GPL licensing issues. MariaDB is open source and the community is slowly moving away from MySQL and towards MariaDB.

If you don't know the history, here's a quick summary. Before Oracle owned MySQL, it was based on the GPL (GNU General Public License), which says you can use the software for free, but you can't change or sell it unless you give away the source code. This means you can use it in your closed source project.

When Oracle changed the licensing agreement from the GPL model, the largely open source community rebelled and some started a group from MySQL and created MariaDB.

So basically, MariaDB database server is the same as MySQL, but with a GPL licensing model.

Now that you know a little more about why and how MariaDB was founded, let's learn how to install it on CentOS 7.

Installing MariaDB on CentOS 7

Now, MariaDB is the default database server for CentOS, all you need to do is run the following commands to install it. The problem is that centos downloads and installs an older but stable version of MariaDB.

If you want to install the latest version of MariaDB, you need to add its software repository to your computer and install it. To do this, run the following commands to create a file from the repository.

$ sudo vim /etc/yum.repos.d/MariaDB.repo

Then copy and paste the lines below into a file and save it.

Name = MariaDB baseurl = http://yum.mariadb.org/10.0/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1

Finally, run the commands below to install MariaDB 10 on your server:

$ sudo yum install MariaDB-server MariaDB-client

If you are having problems with errors and MariaDB cannot be installed due to a conflict with mariadb-libs-xxxx, run the command below to remove the conflicting libraries, and then re-run the installation with the command above:

$ sudo yum remove mariadb-libs*

You should also remove the Postfix package. Re-run the above command to install MariaDB.

Once the installation is complete, run the command below to start the MariaDB server:

$ sudo /etc/init.d/mysql start

Then run the following command to secure the database server.

$sudo mysql_secure_installation

Then select "Y" (Yes) for the rest of the prompts until you're done.

Enter current password for root (enter for none): press Enter Set root password? Y New password: Type new root password Re-enter new password: Confirm the password Remove anonymous users? Y Disallow root login remotely? Y Remove test database and access to it? Y Reload privilege tables now? Y

Lastly, you need to replace the default cnf.ini file in /etc/ for MariaDB. But first you need to go to:

$ cd /usr/share/mysql

And use one of the predefined cnf.ini configurations that are available (Huge, Medium and Small) in this folder.

Let's make a backup copy of the cnf.ini file:

$ sudo mv /etc/cnf.ini /etc/cnf.ini.bak

Then copy one of the pre-configurations into MariaDB:

$ sudo cp /usr/share/mysql/my-huge.cnf /etc/cnf.ini

Restart MaraiDB:

$ systemctl restart mysql

You can check the status:

$ systemctl status mariadb

You can add MariaBD to OS startup:

$ systemctl enable mariadb

I need to create a user so that he can connect from any computer, for this:

$ mysql -uroot -u >GRANT ALL PRIVILEGES ON *.* TO "captain"@"%" IDENTIFIED BY "my_password" WITH GRANT OPTION; > exit

Let's check which port MariaDB is listening on using the SS command:

# ss -tnlp | grep 3306 LISTEN 0 0 127.0.0.1:3306 *:* users:(("mysqld",1159,14))

Mine looks like this (I allowed remote connection to the DB server):

LISTEN 0 50 *:3306 *:* users:(("mysqld",10884,13))

Installation of MariaDB 10 on CentOS 7 is complete.

| |

1: Install MariaDB

Debian 9 contains the MariaDB 10.1 package in the standard repository. This is its default MySQL option.

To install it, update the package index:

Now install the package:

sudo apt install mariadb-server

The command will install MariaDB, but will not prompt you to choose a password or change other settings. Currently, the MariaDB installation has several vulnerabilities that need to be addressed.

2: Setting up MariaDB

After installation is complete, you need to run a security script that will remove untrusted parameters and protect the database from unauthorized access.

sudo mysql_secure_installation

The script will ask a series of questions. First you need to provide your MariaDB root password. This is a MariaDB administrative account that has elevated privileges. You just installed MariaDB and haven't made any configuration changes yet, you don't have this password yet, so just press Enter.

In the next request, the script will ask you to configure the root password for the database. Type N and press Enter. On Debian, the MariaDB root account is closely tied to automated system maintenance, so you cannot change the default authentication methods for this account. Otherwise, when updating the package, the database may be damaged, and access to the root account may be lost. Later we'll look at how to set up an additional administrator account if socket authentication doesn't work for you.

For other questions, you can press Y and Enter. This will remove anonymous users and test databases, disable remote root logins, and update the current MariaDB settings.

3: Configuring password authentication support

On new Debian installations, the MariaDB root user by default supports authentication using the unix_socket plugin rather than using a password. This can improve security and usability in many cases, but can also make things more difficult if you need to allow access to an external program (such as phpMyAdmin).

Since the server uses the root user for tasks such as log rotation and starting and stopping the server, it is best not to change the root account authentication. Changing the credentials in the /etc/mysql/debian.cnf file may work initially, but further package updates will overwrite these changes. Instead, the developers recommend creating a separate administrator account with password authentication.

So, create an account called admin with the same rights as root, but with support for password authentication. To do this, open a MariaDB command prompt in a terminal:

Now create a new user with root privileges and password authentication support. Specify your username and password in the command.

GRANT ALL ON *.* TO "admin"@"localhost" IDENTIFIED BY "password" WITH GRANT OPTION;

Reset privileges:

FLUSH PRIVILEGES;

Close the MariaDB shell:

4: Testing MariaDB

When installed from the standard repository, MariaDB starts automatically. To verify this, check the service status:

sudo systemctl status mariadb
mariadb.service - MariaDB database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2018-09-04 16:22:47 UTC; 2h 35min ago
Process: 15596 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSIT
Process: 15594 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Process: 15478 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||
Process: 15474 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITI
Process: 15471 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysql
Main PID: 15567 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 27 (limit: 4915)
CGroup: /system.slice/mariadb.service
└─15567 /usr/sbin/mysqld
Sep 04 16:22:45 deb-mysql1 systemd: Starting MariaDB database server...
Sep 04 16:22:46 deb-mysql1 mysqld: 2018-09-04 16:22:46 140183374869056 /usr/sbin/mysqld (mysqld 10.1.26-MariaDB-0+deb9u1) starting as process 15567 ...
Sep 04 16:22:47 deb-mysql1 systemd: Started MariaDB database server.

If the DBMS does not start for some reason, enter:

sudo systemctl start mariadb

To further check, you can try connecting to the database using the mysqladmin tool (this is a client that allows you to run administrative commands). For example, this command will connect to MariaDB as root and output the version using a Unix socket:

sudo mysqladmin version
mysqladmin Ver 9.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Server version 10.1.26-MariaDB-0+deb9u1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 2 hours 44 min 46 sec
Threads: 1 Questions: 36 Slow queries: 0 Opens: 21 Flush tables: 1 Open tables: 15 Queries per second avg: 0.003

If you have created an additional administrator, you can perform this operation using the command:

mysqladmin -u admin -p version

MariaDB is running and working properly.

In this article we will look at how to install and configure Linux, Apache, MariaDB, PHP on Centos7 / RHEL 7. There are many changes in the new version of Centos7 / RHEL 7.

What is LAMP?

LAMP is a software package on an OS consisting of the Linux operating system, Apache web server, MySQL database server and PHP (or Perl / Python) programming language. LAMP is used to run heavy dynamic websites consisting entirely of free and open source software. In this article, I am going to explain how Linux, Apache, MySQL/MariaDB (replacement for MySQL), PHP (LAMP) are installed on CentOS 7 or RHEL 7.

• Install RHEL 7 or CentOS 7 server. Open a terminal to the server via ssh, you must have root superuser rights.
• You will also need knowledge of yum commands
• You will need the IP address of your server, use the following command to determine the IP address for the eth0 interface

ifconfig eth0 or ip a show eth0 or ip addr list eth0 | awk "/inet /(sub(/\/+/,"",$2); print $2)" or ifconfig eth0 | awk "/inet /(print $2)" 10.180.10.10

• We will use the resulting IP address 10.180.10.10 to test the installation

So let's get started

Installing Apache on a CentOS 7 /RHEL 7 server

To install the web server we use the command

Yum install httpd

Enable HTTPd service in startup

systemctl enable httpd.service ln -s "/usr/lib/systemd/system/httpd.service" "/etc/systemd/system/multi-user.target.wants/httpd.service"

To disable automatic downloads

Systemctl disable httpd.service rm "/etc/systemd/system/multi-user.target.wants/httpd.service"

Start HTTPd service on CentOS 7/RHEL 7

systemctl start httpd.service

At this point, you can point your web browser to the IP address of your server, http://10.180.10.10. You will see the apache start page:

Stopping HTTPd service on CentOS 7 / RHEL 7

systemctl stop httpd.service

Restarting HTTPd service on CentOS 7 / RHEL 7

View apache service status on CentOS 7/RHEL 7

Make sure the web server is running

Systemctl status httpd.service

Also, restarting the web server can be done with the following command

Apachectl graceful

Checking apache/httpd for configuration errors on Centos 7/ RHEL 7

Apachectl configtest

Default HTTPD server configuration:

1. Default configuration file: /etc/httpd/conf/httpd.conf
2. Configuration files for loadable modules: /etc/httpd/conf.modules.d/ (for example, PHP)
3. Select MPMs as loadable modules and events: /etc/httpd/conf.modules.d/00-mpm.conf
4. Standard ports: 80 and 443 (SSL)
5. Default log files: /var/log/httpd/(access_log,error_log)

Installing MariaDB on a CentOS 7/RHEL server

MariaDB is an updated replacement for the MySQL server. On RHEL/CentOS 7, the MariaDB database management system is used instead of MySQL. Enter the following yum command to install MariaDB server:

Yum install mariadb-server mariadb

To start MariaDB, use the command:

Systemctl start mariadb.service

To ensure that the MariaDB service starts automatically during boot, enter:

Systemctl enable mariadb.service

Command output

Ln -s "/usr/lib/systemd/system/mariadb.service" "/etc/systemd/system/multi-user.target.wants/mariadb.service"

To stop/restart and disable MariaDB use the following commands:

Sudo systemctl stop mariadb.service #-- Stop mariadb server sudo systemctl restart mariadb.service #-- Restart mariadb server sudo systemctl disable mariadb.service #-- Disable autoload mariadb server sudo systemctl is-active mariadb.service #-- Check running is there a server?

First launch of MariaDB

Enter the following command:

/usr/bin/mysql_secure_installation

By answering the questions you will be able to configure the initial security of the database

Verifying your MariaDB installation

Enter the following command

Mysql -u root -p

Example output:

Installing PHP on CentOS 7 / RHEL 7

To install PHP and modules such as GD/mysql enter the following command

Yum install php php-mysql php-gd php-pear

You need to restart the HTTPD (Apache) server, enter:

Systemctl restart httpd.service

To search for all other PHP modules:

Yum search php-

To get more information about the module:

Yum info php-pgsql

Checking PHP on the server

Create a file called /var/www/html/test.php like this:

Vi /var/www/html/test.php

Add the following code:

LAMP server is installed, if you have problems setting up the server, we perform one-time work on installing and configuring the LAMP web server.

I think everyone knows that MariaDB is a branch of MySQL. MariaDB is developed and supported by MariaDB Corporation Ab and the MariaDB Foundation. The lead developer of MariaDB is the well-known Michael Widenius, the author of the original version of MySQL.

The current version is the one that was released on February 13, 2018. In the new version of MariaDB, the InnoDB store was updated to release 5.7.21, and more than 100 bugs were fixed, including those that could be used to initiate a remote denial of service.

Let's try installing the new version on a clean Debian 9.3 (Stretch) system.

I won’t hide the fact that I really like MariaDB and I long ago abandoned using Oracle MySQL in favor of MariaDB or Percona Server for MySQL, and I don’t regret it one bit. MariaDB is used as a replacement for Oracle MySQL in many Linux distributions. MariaDB is also used as a database on a large number of large sites, in fact, my blog is no exception - I use MariaDB 10.2 as a database.

Initial data: OS Debian 9.3 (Stretch);
Task: Install MariaDB 10.2.13 with a minimum of effort and perform basic database setup;

You can find many articles on the Internet about how to install MariaDB on Debian, but they all offer rather cumbersome solutions.
There is also, but it also does not contain a simple solution, but there is one!

Installing MariaDB 10.2.13 on Debian 9:

1. Installation of necessary additional equipment. packages:

Apt-get update apt-get install dirmngr wget -y

2. And now the simple magic that is not written about anywhere - downloading and running the repository installation script:

Wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup && chmod a+x mariadb_repo_setup ./mariadb_repo_setup --mariadb-server-version=10.2

For information:
This script will analyze your system, install GPG keys and add the repository to the file /etc/apt/sources.list.d/mariadb.list
The script is official and supports distributions: RHEL/CentOS 6 & 7, Ubuntu 16.04 LTS (xenial) & 18.04 (bionic), Debian 8 (jessie) & 9 (stretch) and SLES 12 and 15
The script also supports installing repositories for different versions of MariaDB, MaxScale and MariaDB Tools. You can download it and see all launch options:

./mariadb_repo_setup --help

P.S. For those who like to do everything by hand, where you can more flexibly select the desired repository depending on the geographical location of your server.

3. Update the list of packages:

Apt-get update

4. Install the latest version of MariaDB 10.2.x:

Apt-get install mariadb-server -y

During the installation process, the installer will ask us to come up with a password for the root user; at this stage we will leave it blank and change it later.

After installation, check the status of MariaDB:

# systemctl status mariadb ● mariadb.service - MariaDB 10.2.13 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Drop-In: /etc/systemd/system/mariadb. service.d └─migrated-from-my.cnf-settings.conf Active: active (running) since Thu 2018-02-15 12:14:17 +05; 19s ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Main PID: 7270 (mysqld) Status: "Taking your SQL requests now..." CGroup: /system. slice/mariadb.service └─7270 /usr/sbin/mysqld # netstat -ltupn | grep mysql tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 7270/mysqld # ps -ef | grep [m]ysql mysql 7270 1 0 12:14 ? 00:00:00 /usr/sbin/mysqld

We will also try to connect to the database using the mysql command (since at the installation stage we left the root password empty, we will not use the -u root -p options):

# mysql Welcome to the MariaDB monitor. Commands end with ; or\g. Your MariaDB connection id is 10 Server version: 10.2.13-MariaDB-10.2.13+maria~stretch-log mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type "help;" or "\h" for help. Type "\c" to clear the current input statement. MariaDB [(none)]>

We have successfully connected to the MariaDB server.

5. Now let’s launch the Security Configuration Wizard:

Mysql_secure_installation

To the question:
Enter current password for root (enter for none):
Press Enter, the current root password is empty.

And the last question:
Reload privilege tables now?
enter Y, yes we want to reload the privilege table for them to take effect.

After this, we performed the minimum security measures on our MariaDB instance.

Let's now try to connect to the database:

# mysql ERROR 1045 (28000): Access denied for user "root"@"localhost" (using password: NO)

As we can see, with an empty root password we are no longer allowed in.

Let's connect by specifying additional options and entering a password:

# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or\g. Your MariaDB connection id is 20 Server version: 10.2.13-MariaDB-10.2.13+maria~stretch-log mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type "help;" or "\h" for help. Type "\c" to clear the current input statement. MariaDB [(none)]>

Great, everything worked out.

That's all, see you soon. If you have any questions or want me to help you, you can always.