How To Install MariaDB Databases on a FreeBSD v10/11 Unix Server

I

‘m a new FreeBSD unix user. How can I install MariaDB database server on a FreeBSD unix based system?

 

MySQL is a very fast, multi-threaded, multi-user and robust SQL database server. The latest version is located at /usr/ports/databases/mysql57-server/. MariaDB is a database server that offers drop-in replacement functionality for MySQL1. MariaDB is built by some of the original authors of MySQL, with assistance from the broader community of Free and open source software developers around the world. In addition to the core functionality of MySQL, MariaDB offers a rich set of feature enhancements including alternate storage engines, server optimizations, and patches. The latest version is located at /usr/ports/databases/mariadb55-server/. In this tutorial, you will learn how to install MariaDB server and create a databases, users, and grant sql rights using sql commands on a FreeBSD 10 or 11 server.

Update all your ports

Make sure your ports are installed and up to date. I like to do:

# portsnap fetch update && portupgrade -a

FreeBSD MariaDB installation

To install MariaDB Server, MariaDB Client and MariaDB Scripts type the following commands.

A note about MySQL and MariaDB server together

You cannot run MariaDB and MySQL installed together. You will have deinstall one or the other and the clients if you have them. If you want to run them both, you

will need them to be jailed apart. If you are new it’s best to not think about this and run them one at a time focusing on learning one or the other. So for example if you have MySQL and want to install MariaDB you need to deinstall before installing. Check what version you are using by typing the command as root:

# pkg version | grep mysql

 

or as user or root you can type this command:

# mysql –version

 

or use pkg command:

# pkg info | grep mysql

 

Make sure you backup database before you uninstall mysql-server. To deinstall that:

# cd /usr/ports/databases/mysql57-server/ && make deinstall clean

# cd /usr/ports/databases/mysql57-client/ && make deinstall clean

# rm -rf /var/db/mysql/

 

or use pkg command to delete them:

# pkg remove mysql56-server mysql56-client

# rm -rf /var/db/mysql/

Install MariaDB server

To install the port, type:

# cd /usr/ports/databases/mariadb100-server && make install clean

 

When you install make sure you check what you want off in the configuration:

Fig.01: FreeBSD 10 Mariadb Server Install Command

 

Or, to add the binary package using pkg command, run:

# pkg install databases/mariadb100-server

 

Sample outputs:

Fig.02: FreeBSD 10 Install Mariadb using the pkg command

 

Here is output from my FreeBSD 11 box:

Updating FreeBSD repository catalogue…

FreeBSD repository is up-to-date.

All repositories are up-to-date.

The following 5 package(s) will be affected (of 0 checked):

 

New packages to be INSTALLED:

mariadb100-server: 10.0.29

openssl: 1.0.2j_1,1

mariadb100-client: 10.0.29

readline: 6.3.8

indexinfo: 0.2.6

 

Number of packages to be installed: 5

 

The process will require 244 MiB more space.

34 MiB to be downloaded.

 

Proceed with this action? [y/N]: y

Install MariaDB client only

MariaDB Client will be installed automatically. You should now check following options:

[X] THREADSAFE  Build thread-safe client

[X] SSL         Activate SSL support (yassl)

However, if you need MariaDB client on another FreeBSD server or jail, run:

# cd /usr/ports/databases/mariadb100-client

# make install clean

 

OR

# pkg install databases/mariadb100-client

 

Sample outputs:

Fig.03: FreeBSD 11 install mariadb client

How do I start MariaDB on boot?

Type the following command:

echo  mysql_enable= YES   >> /etc/rc.conf

How do I install MariaDB server configuration file?

MariaDB respects FreeBSD layout of file systems (and doesn’t check /etc and /etc/mysql for my.cnf. You will find the following default config files:

# ls -l /usr/local/share/mysql/my*.cnf

 

Sample outputs:

-rw-r–r–  1 root  wheel   4898 Nov 26 12:56 /usr/local/share/mysql/my-huge.cnf

-rw-r–r–  1 root  wheel  20418 Nov 26 12:56 /usr/local/share/mysql/my-innodb-heavy-4G.cnf

-rw-r–r–  1 root  wheel   4885 Nov 26 12:56 /usr/local/share/mysql/my-large.cnf

-rw-r–r–  1 root  wheel   4898 Nov 26 12:56 /usr/local/share/mysql/my-medium.cnf

-rw-r–r–  1 root  wheel   2824 Nov 26 12:56 /usr/local/share/mysql/my-small.cnf

I’m setting up a medium sized server, so I’m going to copy /usr/local/share/mysql/my-medium.cnf to /usr/local/etc/ directory using cp command:

# cp /usr/local/share/mysql/my-medium.cnf /usr/local/etc/my.cnf

 

To edit /usr/local/etc/my.cnf, enter:

# vi !!:2

 

OR

# vi /usr/local/etc/my.cnf

 

To enable remote access to MariaDB database server, enter:

# change ip

bind-address = 127.0.0.1

Save and close the file.

How do I start/stop/restart MariaDB on a FreeBSD 10?

To start the server you are going to type:

# service mysql-server start

 

You will see the following information when you start the server for the first time:

Fig.03 Starting the start for the first time

 

To stop the server you are going to type:

# service mysql-server stop

 

To restart the server you are going to type:

# service mysql-server restart

 

To see the server status you are going to type:

# service mysql-server status

 

You can also use the following commands for the same purpose:

call rc.d script to control MariaDB server ##

/usr/local/etc/rc.d/mysql-server start

/usr/local/etc/rc.d/mysql-server stop

/usr/local/etc/rc.d/mysql-server restart

/usr/local/etc/rc.d/mysql-server status

Sample outputs:

Fig.04: Starting/Stopping MariaDB server on a FreeBSD 10

How do I set root user password for MariaDB?

You should create password for root user after MariaDB installation, enter:

# mysqladmin -u root password YOURSECUREPASSWORD

 

Alternatively, I suggest that you can run the following command to set root password. This command will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers:

# /usr/local/bin/mysql_secure_installation

 

Sample outputs:

 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB

SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

 

In order to log into MariaDB to secure it, we ll need the current

password for the root user.  If you ve just installed MariaDB, and

you haven t set the root password yet, the password will be blank,

so you should just press enter here.

 

Enter current password for root (enter for none):

OK, successfully used password, moving on…

 

Setting the root password ensures that nobody can log into the MariaDB

root user without the proper authorisation.

 

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

… Success!

 

 

By default, a MariaDB installation has an anonymous user, allowing anyone

to log into MariaDB without having to have a user account created for

them.  This is intended only for testing, and to make the installation

go a bit smoother.  You should remove them before moving into a

production environment.

 

Remove anonymous users? [Y/n] y

… Success!

 

Normally, root should only be allowed to connect from  localhost .  This

ensures that someone cannot guess at the root password from the network.

 

Disallow root login remotely? [Y/n] y

… Success!

 

By default, MariaDB comes with a database named  test  that anyone can

access.  This is also intended only for testing, and should be removed

before moving into a production environment.

 

Remove test database and access to it? [Y/n] y

– Dropping test database…

… Success!

– Removing privileges on test database…

… Success!

 

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

 

Reload privilege tables now? [Y/n] y

… Success!

 

Cleaning up…

 

All done!  If you ve completed all of the above steps, your MariaDB

installation should now be secure.

 

Thanks for using MariaDB!

Notes: If you get message with unknown MySQL version when installing ports, you should edit /etc/make.conf and add line:

MYSQL52_LIBVER=16

You can edit with ee or your favorite editor. With ee type:

ee  /etc/make.conf

MYSQL52_LIBVER=16

pres Esc

a,a

or b if you do not want to save the changes.

How do I connect to MariaDB server?

The syntax is:

mysql

mysql -u user -p

mysql -h db-hostname-here -u user-name-here -p

How do I create MariaDB database and users?

First, login as root user:

mysql -u root -p mysql

Sample outputs:

Fig.05: Connecting to the server using mysql client

 

Create a new mysql database called foo. Type the following command at mysql> prompt:

MariaDB [mysql]>  CREATE DATABASE foo;

Create a new user called user1 for database called foo with a password called ‘hiddensecret’:

MariaDB [mysql]>  GRANT ALL ON foo.* TO user1@localhost IDENTIFIED BY  hiddensecret ;

How do I connect to MariaDB database foo using user1 account?

User user1 can connect to the foo database using the following shell command:

$ mysql -u user1 -p foo

 

OR

$ mysql -u user1 -h your-mysql-server-host-name-here -p foo

 

Sample session:

Fig.06: Creating users and database on the MariaDB server

 

See “Mysql User Creation: Setting Up a New MySQL User Account” tutorial for more information.

How do I enable remote access to the MariaDB server?

Edit the my.cnf file, run:

# vi /usr/local/etc/my.cnf

 

Make sure line skip-networking is commented (or remove line) and add the following line in the [mysqld] section:

bind-address=YOUR-SERVER-IP

For example, if your MariaDB server IP is 192.168.1.5 then entire block should be look like as follows:

# The MariaDB server

[mysqld]

port            = 3306

bind-address    = 192.168.1.5

socket          = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

 

# Point the following paths to different dedicated disks

#tmpdir         = /tmp/

# Don t listen on a TCP/IP port at all. This can be a security enhancement,

# if all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (via the  enable-named-pipe  option) will render mysqld useless!

#

#skip-networking

log-bin=mysql-bin

binlog_format=mixed

server-id       = 1

Save and close the file. Restart the server:

# service mysql-server restart

 

Update your pf.conf file:

allows mysql client from 192.168.1.200 ##

pass in on $ext_if proto tcp from 192.168.1.200to any port 3306  flags S/SA synproxy state

Restart pf and test connectivity from 192.168.1.200 by typing any one of the following command:

# use nc for port testing ##

nc -z -w1 192.168.1.5 3306

# or old good telnet ##

echo X | telnet -e X 192.168.1.5 3306

telnet -e X 192.168.1.5 3306<<< X

or use mysql client ##

mysql -h 192.168.1.5 -u USER -p DB

How do I grant access to an existing database over the LAN based session?

Let us assume that you are always making connection from remote IP called 192.168.1.200 for database called foo for user bar, To grant access to this IP address type the following command at MariaDB [mysql]> prompt for existing database, enter:

MariaDB [mysql]> update db set Host= 192.168.1.200  where Db= foo ;

MariaDB [mysql]> update user set Host= 192.168.1.200  where user= bar ;

See “How Do I Enable Remote Access To MySQL Database Server?” tutorial for more information.

How to open ports in a FreeBSD pf firewall

Add the following rule in your pf.conf file:

pass in on $ext_if proto tcp from any to any port 3306

OR only allow access from 192.168.1.10:

pass in on $ext_if proto tcp from 192.168.1.10 to any port 3306

Leave a Reply

Your email address will not be published. Required fields are marked *