Installing MariaDB with MySQL on Mac

Question

I am trying to install MariaDB on my Mac using brew. However, I am struggling to get this installed due to it conflicting with MySQL. I was wondering whether anyone can advise how to set it up so I have both MariaDB and MySQL as I will need both on my machine as I work on multiple projects which need to use one or the other.

3x-iMac:~ admin$ mysql.server start
Starting MariaDB
 SUCCESS! 

3x-iMac:~ admin$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

3x-iMac:~ admin$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.3.8-MariaDB, for osx10.13 (x86_64) using readline 5.1

Connection id:      24
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server:         MySQL
Server version:     8.0.11 MySQL Community Server - GPL
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /tmp/mysql.sock
Uptime:         2 hours 47 min 30 sec

Threads: 6  Questions: 1257  Slow queries: 0  Opens: 154  Flush tables: 2  Open tables: 130  Queries per second avg: 0.125
--------------

Answer 1

The problem with having both MySQL and MariaDB installed simultaneously is not so much the conflicting port (both servers bind to port 3306 by default), because that could be changed in the server configuration. The problem rather is that MariaDB is a drop-in replacement for MySQL and thus uses the same paths and names for the binaries (e.g. mysqld for the server, mysql for the client). So they are designed that way that one has either one or the other installed, but not both at the same time.

A better approach would be to set up a Docker container for both database servers and use that. This approach also has the charm that one could also run several different versions of both database servers, if that is desired. You still have to map the each container to a different port, though.

A simple docker-compose.yml to set up MySQL 5 and MariaDB 10 could look like this:

version: '2'
services:
  mysql5:
    image: mysql:5
    ports:
     - "3305:3306/tcp"
    environment:
      - MYSQL_ROOT_PASSWORD=secret_password
      - MYSQL_USER=user
      - MYSQL_PASSWORD=user_password_here
      - MYSQL_DATABASE=my_db
  mariadb10:
    image: mariadb:10
    ports:
     - "3310:3306/tcp"
    environment:
      - MYSQL_ROOT_PASSWORD=secret_password
      - MYSQL_USER=user
      - MYSQL_PASSWORD=user_password_here
      - MYSQL_DATABASE=my_db

You can start both containers by typing docker-compose up -d into the terminal in the directory of the docker-compose.yml file, given that docker-compose is installed on your system.

MySQL 5 would be available on port 3305, MariaDB on port 3310. (That would leave the possibility to have a native MySQL or MariaDB on port 3306.) The environment variables for database users, passwords and names of the databases should be adjusted, of course. Same goes for the versions of MySQL and MariaDB, if you need different versions.

After the containers are up (docker-compose up -d) you can connect to them e.g. via:

mysql --host=::1 --user=user --password my_db --port=3310

(Take care that you give the correct port argument. In this case, port 3310 is for MariaDB 10.)

After typing the password for the user user you can issue your SQL queries:

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.5-10.3.8-MariaDB-1:10.3.8+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_db              |
+--------------------+
2 rows in set (0,00 sec)

mysql> USE my_db;
Database changed
mysql> SHOW TABLES;
Empty set (0,00 sec)

mysql> quit
Bye

Have fun!

Answer 2

If you run brew info, it will even warn you not to install them side by side because they will conflict:

brew info mysql
mysql: stable 8.0.13 (bottled)
Open source relational database management system
https://dev.mysql.com/doc/refman/8.0/en/
Conflicts with:
  mariadb (because mysql, mariadb, and percona install the same binaries.)
  mariadb-connector-c (because both install plugins)
  mysql-cluster (because mysql, mariadb, and percona install the same binaries.)
  mysql-connector-c (because both install MySQL client libraries)
  percona-server (because mysql, mariadb, and percona install the same binaries.)
Not installed

In fact, run brew info mariadb and it will say it is a Drop-in Replacement:

brew info mariadb
mariadb: stable 10.3.12 (bottled)
Drop-in replacement for MySQL
https://mariadb.org/
Conflicts with:
  mariadb-connector-c (because both install plugins)
  mysql (because mariadb, mysql, and percona install the same binaries.)
  mysql-cluster (because mariadb, mysql, and percona install the same binaries.)
  mysql-connector-c (because both install MySQL client libraries)
  mytop (because both install `mytop` binaries)
  percona-server (because mariadb, mysql, and percona install the same binaries.)
/usr/local/Cellar/mariadb/10.3.12 (658 files, 174.4MB) *
  Poured from bottle on 2019-01-25 at 09:50:26
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/mariadb.rb
==> Dependencies

What is a Drop-in Replacement? It refers to the ability to replace one software component with another one without any other code or configuration changes being required and resulting in no negative impacts.

Consequently, both mysql and mariadb run with mysql.server start, both log into mysql using mysql -h localhost -u root -p, both reference the same data directory at /usr/local/var/mysql, both use the same commands such as mysqldump, all of which indicates that the two operate interchangeably. They cannot coincide, unless you install them on different virtual machines like vmware or run them in a docket container (which was suggested in the other answer).

But if you cannot run them on separate virtual machines or in a docket container, then I strongly recommend removing MySQL and using MariaDB, since MariaDB keeps compatibility with MySQL, but also contains other features such as CHECK CONSTRAINTS.

This is how you would remove MySQL and install MariaDB instead. Note in my system I was using [email protected] via HomeBrew so I specify that instead of mysql:

brew remove [email protected]
brew cleanup

And that's it. Some guides suggest to remove individual directories such as this:

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/var/mysql
sudo rm -rf /usr/local/mysql*
sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*

But on my system, I did not have MySQL in my Preference Pane or in Launch or even auto started. So the only other place I had it was the actual database data in /usr/local/var:

/usr/local/var/mysql

But since MariaDB is a Drop-In Replacement, you don't need to delete this data and MariaDB will use it once installed.

So to install MariaDB:

brew install mariadb
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 2 taps (homebrew/core and homebrew/services).
==> New Formulae
...
==> Updated Formulae
...
==> Deleted Formulae
...
==> Downloading https://homebrew.bintray.com/bottles/mariadb-10.3.12.mojave.bottle.tar.gz
######################################################################## 100.0%
==> Pouring mariadb-10.3.12.mojave.bottle.tar.gz
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To connect:
    mysql -uroot

To have launchd start mariadb now and restart at login:
  brew services start mariadb
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
  /usr/local/Cellar/mariadb/10.3.12: 658 files, 174.4MB

So as you can see from the install and also by running brew info mariadb, mariadb has been installed at

/usr/local/Cellar/mariadb/10.3.12

And the mysql executable in your $PATH is pointing to that MariaDB binary:

$ which mysql
/usr/local/bin/mysql
$ ls -l /usr/local/bin/mysql
lrwxr-xr-x  1 viggy  admin  35 Jan 25 09:50 /usr/local/bin/mysql -> ../Cellar/mariadb/10.3.12/bin/mysql

For me, since I already had a data directory with [email protected], MariaDB was able to use it and I still had access to my data (albeit it is still encouraged to back up the database with mysqldump prior to removing mysql):

mysql -h localhost -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.12-MariaDB Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

USE my_old_database;
Database changed
MariaDB [my_old_database]>

As you can see, now it is using MariaDB.