5. Installation of MySQL on FreeBSD 11.2 AWS - tanalam2411/freebsd GitHub Wiki

Installation of MySQL on FreeBSD 11.2

1. Package update

root@freebsd:~/performance/mysql # pkg update

2. Search for the latest MySQL Server

root@freebsd:~/performance/mysql # pkg search mysql

.
.
.
mysql55-client-5.5.62_1        Multithreaded SQL database (client)
mysql55-server-5.5.62_1        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_1        Multithreaded SQL database (client)
mysql80-server-8.0.12_1        Multithreaded SQL database (server)
.
.
.

3. Install latest MySQL pkg

root@freebsd:~/performance/mysql # pkg install mysql80-server-8.0.12_1

4. Enable MySQL at system startup

root@freebsd:~/performance/mysql # 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@freebsd:~/performance/mysql # service mysql-server start

Starting mysql.

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

root@freebsd:~/performance/mysql # mysql -u root 
> ALTER USER 'root'@'localhost' IDENTIFIED BY '$PASSWORD';

Example

root@freebsd:~/performance/mysql # mysql -u root 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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.02 sec)

root@localhost [(none)]> exit
Bye
root@freebsd:~/performance/mysql # mysql -u root -p
Enter password: 
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)]> exit
Bye

7. Create privileged user

> CREATE USER '$USERNAME'@'localhost' IDENTIFIED BY '$PASSWORD';
> GRANT ALL PRIVILEGES ON *.* TO '$USERNAME'@'localhost' WITH GRANT OPTION;
> CREATE USER '$USERNAME'@'%' IDENTIFIED BY '$PASSWORD';
> GRANT ALL PRIVILEGES ON *.* TO '$USERNAME'@'%' WITH GRANT OPTION;
> FLUSH PRIVILEGES;

Example

root@freebsd:~/performance/mysql # 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)]> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin123';
Query OK, 0 rows affected (0.15 sec)

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

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

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

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

root@localhost [(none)]> exit
Bye

8. Create Database

> create database testdb;

Example:

root@freebsd:~/performance/mysql # mysql -u admin -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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.

admin@localhost [(none)]> create database testdb;
Query OK, 1 row affected (0.14 sec)

admin@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.04 sec)

9. Create my.cnf file for additional configuration

root@freebsd:~/performance/mysql # 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
     
root@freebsd:~/performance/mysql # cp /usr/ports/databases/mysql80-server/files/my.cnf.sample.in /var/db/mysql
root@freebsd:~/performance/mysql # mv /var/db/mysql/my.cnf.sample.in /var/db/mysql/my.cnf

#Do additional changes and restart mysql-server server
root@freebsd:~/performance/mysql # service mysql-server restart