How to Install MySQL on FreeBSD 14.0

MySQL is an open-source relational database management system (RDBMS) that uses the SQL (Structured Query Language) syntax to create, manage, and manipulate databases on a server. MySQL works as a reliable database server for small, medium, and large-scale applications to offer continuous record interaction, reliability, and high performance.

This article explains how to install MySQL on FreeBSD 14.0, secure the database server, and enable authenticated access to the database console.

Prerequisites

Before you begin, you need to:

• Deploy a FreeBSD 14.0 server instance.
• Access the server using SSH as a non-root user with sudo privileges.

Install MySQL

MySQL is available in the default FreeBSD 14.0 repositories with multiple versions and add-on packages. Follow the steps below to install the latest MySQL database server package using the default pkg package manager.

1. Update the server package index.

   CONSOLE

   Copy

   $ sudo pkg update

   Explain Code

   Powered by Vultr AgentBeta

2. Search all MySQL packages available in the default repositories.

   CONSOLE

   Copy

   $ sudo pkg search mysql

   Explain Code

   Powered by Vultr AgentBeta

   Your output should look like the one below:

   ...
   mysql80-client-8.0.35          Multithreaded SQL database (client)
   mysql80-server-8.0.35_1        Multithreaded SQL database (server)
   mysql81-client-8.1.0           Multithreaded SQL database (client)
   mysql81-server-8.1.0           Multithreaded SQL database (server)
   ...

   Based on the above output, MySQL 8.1 is the latest version available in the default repository catalog. Compile and install MySQL using the latest FreeBSD ports collection information to install unavailable versions on your server.

3. Install the latest MySQL package on your server.

   CONSOLE

   Copy

   $ sudo pkg install -y mysql81-server

   Explain Code

   Powered by Vultr AgentBeta

4. View the installed MySQL version on your server.

   CONSOLE

   Copy

   $ sudo mysql --version

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   mysql  Ver 8.1.0 for FreeBSD14.0 on amd64 (Source distribution)

Manage the MySQL System Service

MySQL uses the mysql-server system service to control the database server processes and runtime on your FreeBSD server. Follow the steps below to enable the MySQL service to start at boot time and verify the service status to manage the database processes on your server.

1. Enable MySQL to start automatically at boot time.

   CONSOLE

   Copy

   $ sudo sysrc mysql_enable="YES"

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   mysql_enable:  -> YES

2. Start the MySQL service.

   CONSOLE

   Copy

   $ sudo service mysql-server start

   Explain Code

   Powered by Vultr AgentBeta

3. View the MySQL service status and verify that it's running.

   CONSOLE

   Copy

   $ sudo service mysql-server status

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   MySQL is running as pid 2199.

4. Stop the MySQL service.

   CONSOLE

   Copy

   $ sudo service mysql-server stop

   Explain Code

   Powered by Vultr AgentBeta

5. Restart the MySQL server.

   CONSOLE

   Copy

   $ sudo service mysql-server restart

   Explain Code

   Powered by Vultr AgentBeta

Secure the MySQL Server

MySQL uses the mysql_secure_installation script to remove insecure defaults such as databases and enable authentication for all database users on your server. Follow the steps below to run the script, set up a new root database user password, remove anonymous users, and disable test databases on your server.

1. Run the MySQL secure installation script.

   CONSOLE

   Copy

   $ sudo mysql_secure_installation

   Explain Code

   Powered by Vultr AgentBeta

   Reply to each of the following prompts to secure your MySQL database server.

   • Enter Y and press Enter when prompted to enable the VALIDATE PASSWORD component that tests the user password strength and ensure strict password policies on your server.

   ...
   Would you like to setup VALIDATE PASSWORD component?
   Press y|Y for Yes, any other key for no.
   • Enter your desired password strength validation policy. For example, enter 2 to enforce strong password usage on your server.

   LOW    Length >= 8
   MEDIUM Length >= 8, numeric, mixed case, and special characters
   STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
   Please enter 0 = LOW, 1 = medium, and 2 = strong.
   • Enter a new root database user password.

   New password:
   • Repeat the password to calculate the strength and validate the new user password.

   Re-enter new password:

   Output:

   Estimated strength of the password: 100
   • Enter Y and press Enter when prompted to save your new password.

   Do you wish to continue with the password provided? (Press y|Y for yes, any other key for no.)
   • Enter Y and press Enter when prompted to remove anonymous users on your database server.

   Remove anonymous users? (Press y|Y for Yes, any other key for no.)
   • Enter Y and press Enter when prompted to disable remote access for the root database user.

   Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
   • Enter Y and press Enter when prompted to remove the test database.

   Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
   • Enter Y and press Enter when prompted to reload privilege tables and apply changes.

   Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

   Output:

   Success.
   All done

2. Restart the MySQL database server to apply your configuration changes.

   CONSOLE

   Copy

   $ sudo service mysql-server restart

   Explain Code

   Powered by Vultr AgentBeta

Access MySQL

You can use the mysql CLI utility to access the MySQL database server console or application modules such as php-mysqli to integrate MySQL with other applications such as PHP. Follow the steps below to access the MySQL console and create sample database records on your server.

1. Log in to the MySQL database server as root.

   CONSOLE

   Copy

   $ mysql -u root -p

   Explain Code

   Powered by Vultr AgentBeta

   Enter the root database user password you created earlier when prompted to access the MySQL console. Your output should be similar to the one below when successful.

   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection ID is 12
   Server version: 8.1.0 Source distribution
   Copyright (c) 2000, 2023, Oracle, and/or its affiliates.
   
   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>

2. Create a new sample database. For example, example_vultr.

   SQL

   Copy

   mysql> CREATE DATABASE example_vultr;

   Explain Code

   Powered by Vultr AgentBeta

3. View all databases on your server and verify that the new database is available.

   SQL

   Copy

   mysql> SHOW DATABASES;

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   +--------------------+
   | Database           |
   +--------------------+
   | example_vultr      |
   | information_schema |
   | mysql              |
   | performance_schema |
   | sys                |
   +--------------------+
   5 rows in set (0.01 sec)

4. Create a new sample database user vultr_user with a strong password. Replace STRONG-PASSWORD with your desired password.

   SQL

   Copy

   mysql> CREATE USER 'vultr_user'@'localhost' IDENTIFIED BY 'STRONG-PASSWORD';

   Explain Code

   Powered by Vultr AgentBeta

5. Grant the new user full privileges to the example_vultr database.

   SQL

   Copy

   mysql> GRANT ALL PRIVILEGES ON example_vultr.* TO 'vultr_user'@'localhost';

   Explain Code

   Powered by Vultr AgentBeta

6. Reload the MySQL privileges table to apply changes.

   SQL

   Copy

   mysql> FLUSH PRIVILEGES;

   Explain Code

   Powered by Vultr AgentBeta

7. Switch to the example_vultr database.

   SQL

   Copy

   mysql> USE example_vultr;

   Explain Code

   Powered by Vultr AgentBeta

8. Create a new sample table sample_users with five columns to store different data types.

   SQL

   Copy

   mysql> CREATE TABLE sample_users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(50) NOT NULL,
            email VARCHAR(100) UNIQUE,
            phone VARCHAR(20),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          );

   Explain Code

   Powered by Vultr AgentBeta

   The above SQL query creates a new table with the following columns and data types:

   • id: Automatically increments the value for each new record using the AUTO_INCREMENT function. The PRIMARY KEY attribute ensures that each record is unique in the table.
   • name: Stores user names with up to 50 mixed characters.
   • email: Stores user email addresses with up to 100 mixed characters. The UNIQUE attribute ensures that each email address is unique across all records.
   • phone: Stores user phone numbers with up to 20 mixed characters.
   • created_at: Automatically stores the timestamp when a record is created.

9. Insert new sample data into the sample_users table.

   SQL

   Copy

   mysql> INSERT INTO sample_users (name, email, phone) VALUES
          ('user1', 'user1@example.com', '123-456-7890'),
          ('user2', 'user2@example.com', '234-567-8901'),
          ('user3', 'user3@example.com', '345-678-9012');

   Explain Code

   Powered by Vultr AgentBeta

10. View all records in the sample_users table to verify that the new data is available.

    SQL

    Copy

    mysql> SELECT * FROM sample_users;

    Explain Code

    Powered by Vultr AgentBeta

    Output:

    +----+-------+-------------------+--------------+---------------------+
    | id | name  | email             | phone        | created_at          |
    +----+-------+-------------------+--------------+---------------------+
    |  1 | user1 | user1@example.com | 123-456-7890 | 2024-07-03 20:07:17 |
    |  2 | user2 | user2@example.com | 234-567-8901 | 2024-07-03 20:07:17 |
    |  3 | user3 | user3@example.com | 345-678-9012 | 2024-07-03 20:07:17 |
    +----+-------+-------------------+--------------+---------------------+

11. Exit the MySQL console.

    SQL

    Copy

    mysql> EXIT

    Explain Code

    Powered by Vultr AgentBeta

Conclusion

You have installed MySQL on your FreeBSD 14.0 server and secured the database server to require authentication for all database users. You can use the MySQL database server as a dedicated backend by modifying the main configuration to listen for connections from a specific address such as a VPC network. In addition, you can install add-on modules to use MySQL with other applications such as PHP on your server. For more information about MySQL, visit the official documentation.