MySQL is a high-speed, multi-threaded, multi-user and robust SQL database server. MariaDB is a database server that offers drop-in replacement functionality
for Oracle MySQL server. MariaDB is built by some of the original authors of MySQL, with assistance from the community. This page shows how to install MariaDB Databases on a FreeBSD 10/11 Unix Server.
How To Install MariaDB Databases on a FreeBSD
The procedure to install MariaDB server is as follows:
- Find MariaDB server package names.
- Install MariaDB server either using the ports or pkg command.
- Start MariaDB server
- Set root password for MariaDB server
- Create a database, user, and grant rights
- Open MariaDB ports using the firewall
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/
Find MariaDB server versions
Run pkg command:
# pkg search mariadb
FreeBSD install MariaDB server
To install the port, type and make sure you check what you want off in the configuration:
# cd /usr/ports/databases/mariadb103-server/
# make install clean
Or, to add the binary package using pkg command, run:
# pkg install databases/mariadb103-server
A not about installing 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 or desktop system, run:
# cd /usr/ports/databases/mariadb103-client
# make install clean
# pkg install databases/mariadb103-client
How do I start MariaDB on boot?
Execute the sysrc command to enable MaraiDB server service on boot along with other options:
# sysrc mysql_enable=YES
# sysrc mysql_pidfile=/var/db/mysql/mysql.pid
# sysrc mysql_optfile=/usr/local/etc/my.cnf
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
-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
You can use any one of the above file. I always start with a clean config file:
# vi /usr/local/etc/my.cnf
Here is my minimum my.cnf file:
## SERVER ## [mysqld] datadir = /var/db/mysql socket = /var/db/mysql/mysql.sock pid-file = /run/mysql/mysql.pid log-error = /var/db/mysql/mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = 1 slow-query-log-file = /var/db/mysql/mysql-slow.log
How do I start/stop/restart MariaDB on a FreeBSD 10/11?
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:
# 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
How do I set root user password for MariaDB?
You should create password for root user after MariaDB installation, enter:
# mysqladmin -S /var/db/mysql/mysql.sock -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:
# ln -s /var/db/mysql/mysql.sock /tmp/
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!
Remove file from /tmp/:
# rm /tmp/mysql.sock
How do I connect to MariaDB server?
mysql is a simple SQL shell that one can use to control mysql/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
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
$ mysql -u user1 -h your-mysql-server-host-name-here -p foo
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:
For example, if your MariaDB FreeBSD server IP is 192.168.1.5:
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.200 to any port 3306 flags S/SA synproxy state
Restart pf service 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
|Category||List of Unix and Linux commands|
|Firewall||CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04|
|Network Utilities||dig • host • ip • nmap|
|OpenVPN||CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04|
|Package Manager||apk • apt|
|Processes Management||bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time|
|Searching||grep • whereis • which|
|User Information||groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w|
|WireGuard VPN||CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04|