4. Installation of MySQL on FreeBSD 11.2

Installation of MySQL on FreeBSD 11.2

1. Package update

root@:/usr/home/tan # pkg update

2. Search for the latest MySQL

root@:/usr/home/tan # pkg search mysql
.
.
.
mysql55-client-5.5.61          Multithreaded SQL database (client)
mysql55-server-5.5.61          Multithreaded SQL database (server)
mysql56-client-5.6.42          Multithreaded SQL database (client)
mysql56-q4m-0.9.14_3           Message queue that works as a pluggable storage engine of MySQL
mysql56-server-5.6.42          Multithreaded SQL database (server)
mysql57-client-5.7.24          Multithreaded SQL database (client)
mysql57-server-5.7.24          Multithreaded SQL database (server)
mysql80-client-8.0.12          Multithreaded SQL database (client)
mysql80-server-8.0.12          Multithreaded SQL database (server)
.
.
.

3. Install MySQL pkg

root@:/usr/home/tan # pkg install mysql80-server-8.0.12
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 6 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	mysql80-server: 8.0.12
	libedit: 3.1.20170329_2,1
	re2: 20180901
	protobuf: 3.5.2_1,1
	libevent: 2.1.8_2
	mysql80-client: 8.0.12

Message from mysql80-client-8.0.12:

* * * * * * * * * * * * * * * * * * * * * * * *

This is the mysql CLIENT without the server.
for complete server and client, please install databases/mysql80-server

* * * * * * * * * * * * * * * * * * * * * * * *

* * * * * * * * * * * * * * * * * * * * * * * *

WARNING: THIS IS STILL-IN-DEV PROJECT! USE WITH CAUTION ON PRODUCTION ENVS!

* * * * * * * * * * * * * * * * * * * * * * * *
Message from mysql80-server-8.0.12:

*****************************************************************************

WARNING: THIS IS STILL-IN-DEV PROJECT! USE WITH CAUTION ON PRODUCTION ENVS!

*****************************************************************************
*****************************************************************************

Remember to run mysql_upgrade the first time you start the MySQL server
after an upgrade from an earlier version.

There is no initial password for first time use of MySQL.
Keep in mind to reset it to a secure password.

MySQL80 has a default %%ETCDIR%%/my.cnf,
remember to replace it with your own
or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf.

*****************************************************************************

4. Enable MySQL at system startup

root@:/usr/home/tan # sysrc mysql_enable=yes
mysql_enable:  -> yes

# check file /etc/rc.conf should have entry -> mysql_enable="yes"

5. Start the mysql-server service

root@:/usr/home/tan # service mysql-server start
Starting mysql.

6. By default MySQL's root password is not set. Reset root user's password

root@:/usr/home/tan # mysql -u root 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.12 Source 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.

root@localhost [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';
Query OK, 0 rows affected (0.04 sec)

root@localhost [(none)]> exit
Bye
root@:/usr/home/tan # mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@:/usr/home/tan # mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.12 Source 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.

root@localhost [(none)]>

7. Create privileged user

https://stackoverflow.com/a/18821224/3270800

root@localhost [(none)]> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin123';
Query OK, 0 rows affected (0.09 sec)

root@localhost [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.04 sec)

root@localhost [(none)]> CREATE USER 'admin'@'%' IDENTIFIED BY 'admin123';
Query OK, 0 rows affected (0.04 sec)

root@localhost [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)

root@localhost [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

8. Setting my.cnf

root@:/usr/home/tan # locate my.cnf
/usr/ports/databases/mysql56-server/files/my.cnf.sample.in
/usr/ports/databases/mysql57-server/files/my.cnf.sample.in
/usr/ports/databases/mysql80-server/files/my.cnf.sample.in
/usr/ports/databases/mysqlwsrep56-server/files/my.cnf.sample.in
/usr/ports/databases/mysqlwsrep57-server/files/my.cnf.sample.in

As we have installed mysql80-server we will use /usr/ports/databases/mysql80-server/files/my.cnf.sample.in sample conf file

root@:/usr/home/tan #  cp /usr/ports/databases/mysql80-server/files/my.cnf.sample.in /var/db/mysql          
root@:/usr/home/tan #  mv /var/db/mysql/my.cnf.sample.in /var/db/mysql/my.cnf

9. Check port status for remote connection

root@:/var/db/mysql # netstat -an -f inet | grep \.3306
tcp4       0      0 127.0.0.1.3306         *.*                    LISTEN

Change binding address from localhost to 0.0.0.0 or

root@:/usr/home/tan # vi /etc/rc.conf
--------------------------
#Add this line
mysql_args="--bind-address=0.0.0.0 --skip-networking"
--------------------------

root@:/usr/home/tan # service mysql-server restart

root@:/usr/home/tan # netstat -an -f inet | grep \.3306
tcp4       0      0 *.3306                 *.*                    LISTEN

Connect from Remote machine

mysql -u admin -h  $mysql-server-ip -p -P 3306

10. Create database

mysql> create database testdb;
Query OK, 1 row affected (0.08 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.02 sec)