release Configuring Your Server Foundation (FreeBSD 12 / MySQL 8)

[Queen]

Hello everyone,

I have recently set up a new server utilizing the latest FreeBSD and MySQL versions, which inspired me to share my experience with the community.

Important Note:

If you're using MySQL version 5.7 or later, be aware that the PASSWORD() function has been deprecated. Adjustments to your source code and website may be necessary. For those who prefer to avoid these changes, installing MySQL 5.6 is a viable option, as these instructions will work without modifications—except for the my.cnf file path.

1. Setting Up the Physical Server

This guide is divided into three main sections: hardware setup, operating system configuration, and MySQL installation.

Before configuring the operating system, ensure that you've rented a server and created necessary partitions. In my setup, I opted for a server with 64 GB of RAM and two NVMe disks. Notably, NVMe disks outperform traditional SSDs in speed. My hosting provider, a well-known French company, offered a single BSD option—a preconfigured template with FreeBSD 11.3 ZFS, which I selected. I chose English and customized my partitions.

During partition setup, I allocated a separate partition for MySQL at /var/db/mysql, using about three-quarters of the available space (400 GB), reserving the remainder for other uses. Additionally, I created a 2GB swap partition. Keep in mind that due to the RAID configuration, the displayed partition sizes will appear doubled.

In layman's terms, a RAID setup consists of multiple hard drives that contain identical data and operate together. This redundancy ensures that if one drive fails, the server continues to function, mitigating data loss and downtime. However, it's worth noting that additional drives do not provide extra storage capacity, a trade-off that is justifiable for critical applications.

If you're uncertain about the space required for MySQL, a single large partition (Mountpoint "/") can suffice, and you can eliminate the default "/home" partition, which typically holds minimal value. Utilize the arrow icon during the partition finalization to ensure all available space is utilized!

After partitioning, enter a hostname for the server and proceed with installation. Once completed, a confirmation email will be sent with the root password, which you can use to log into your server via PuTTY on port 22 (this guide won't cover PuTTY usage).

2. Upgrading to the Latest FreeBSD

2.1 Standard Upgrade Procedure

Let’s focus on upgrading the FreeBSD kernel—this is where we update to a new version. After logging in, we will update our server to the latest release of FreeBSD, which is 12.1 at the time of writing. Remember to verify the current version at freebsd.org.

This is the hidden content, please

• Sign In
• or
• Sign Up

Follow the on-screen prompts. Since we have a clean system, feel free to accept the defaults when prompted with “Does this look reasonable?” However, if you're upgrading an existing server, carefully review each file that is being modified.

You’ll receive a list of files to update, exit the list with :q, and then proceed with:

This is the hidden content, please

• Sign In
• or
• Sign Up

After installation is complete, reboot the system. Since no packages are installed yet, we can skip rebuilding ports. However, you can either rebuild or upgrade your packages with:

portmaster -GaD
    

or

pkg update

Finally, run:

freebsd-update install

and your new FreeBSD version will be ready for use.

2.2 Updating Repositories

If using OVH, you’ll need to alter your repositories as OVH currently does not provide updates for FreeBSD 12. Instead, we will source updates from the main FreeBSD repositories, despite not being able to utilize OVH's Real Time Monitoring system, which, in my view, is somewhat ineffective.

Edit the repository configuration:

ee /usr/local/etc/pkg/repos/FreeBSD.conf

If the file is empty, insert the following configuration:

FreeBSD: {
  url: "pkg+http://pkg.FreeBSD.org/${ABI}/latest",
  mirror_type: "srv",
  signature_type: "fingerprints",
  fingerprints: "/usr/share/keys/pkg",
  enabled: yes
}

Now, disable OVH repositories:

 

ee /usr/local/etc/pkg/repos/OVH.conf

 

Change both occurrences of “enabled: yes” to “enabled: no”. If OVH adds repositories for FreeBSD 12 in the future, you can revert this change.

Test your system updates:

portsnap fetch extract
pkg update
pkg upgrade

If you've just installed FreeBSD 12.1 and encounter issues with pkg, resolve this by running:

pkg-static install -f pkg

This should restore normal functionality to the pkg command.

2.3 Rebuilding Databases

Following significant changes like an upgrade, it's necessary to rebuild certain system databases. Additionally, let’s configure email notifications for root messages, which can be crucial.

Edit the aliases file:

ee /etc/aliases
    

Add this line beneath:

# root: [email protected]
root:  [your email]

Save and exit, then synchronize the users' database:

newaliases
/usr/sbin/pwd_mkdb -p /etc/master.passwd
    

2.4 Setting Up Sendmail

FreeBSD generates regular audits to notify system issues (refer to crontab). Let's direct these alerts to our email.

Modify crontab:

ee /etc/crontab
    

Insert the following at the top:

MAILTO=[your email address]

Now, configure sendmail basics:

cd /etc/mail/certs
openssl dhparam -out dh.param 4096

Important Note: If you run a personal mail server, consider adding it as a smart host for sendmail. For additional guidance, refer to

This is the hidden content, please

• Sign In
• or
• Sign Up

.

Check /var/maillog to confirm functionality. Keep in mind that your mail server may reject emails from unidentified sources, necessitating some configuration adjustments.

2.5 Troubleshooting Issues

As FreeBSD 12 is relatively new, you may encounter minor bugs. The following issues are not part of the standard installation but were encountered by users of this version.

I faced an error with Perl 5.30 during the ports build, which I resolved by installing Perl from ports:

cd /usr/ports/lang/perl530 && make install clean

Another issue arose due to missing SSL, which was addressed by installing OpenSSL:

pkg install openssl
    

To avoid further SSL-related complaints during builds, add the following line to /etc/make.conf, creating the file if it doesn’t exist:

DEFAULT_VERSIONS+=ssl=openssl
    

2.6 System Optimization

Numerous online resources address tuning various sysctl settings in FreeBSD. Approach these suggestions carefully, as they may not apply to your specific version, and defaults can vary significantly. Here are proven tuning settings applicable in this particular case:

In /boot/loader.conf:

# Prevent MySQL from swapping - set this to roughly a quarter or half of your RAM (ZFS only)
vfs.zfs.arc_max="16G"

In /etc/sysctl.conf:

kern.ipc.soacceptqueue=512 # Maximum incoming TCP connections (default is 128, which can be inadequate during DDoS attacks).
compat.ia32.maxdsiz=2147483648 # Compatibility for 32-bit applications.

For firewall needs, consider installing pf. I may have documented this process previously, but it's beyond the scope of this tutorial.

On production servers, establishing SSH key authentication and using a dedicated user for your Metin server is advisable. Don’t forget to add this user to the wheel group, allowing you to switch to root as needed. Disabling password authentication can increase security—this can be managed in /etc/ssh/sshd_config. Many guides are available for this common procedure.

3. Installing MySQL 8

3.1 Installation Process

Everything is set up! Now, let's proceed with installing MySQL 8. The jump from 5.7 to 8 is curious, but early benchmarks show that MySQL 8 outperforms its predecessors. Let's set it up to run with Metin.

pkg install mysql80-server
pkg install mysql80-client
    

Add to rc.conf:

mysql_enable="YES"

To save disk space, disable binary logging by commenting out the following line in /usr/local/etc/mysql/my.cnf:

#log-bin                         = mysql-bin

Additionally, disable strict SQL mode, as Metin encounters issues with it:

sql-mode = ""

At this stage, start MySQL and verify that everything is operational:

service mysql-server start

You don’t need to run mysql_upgrade for this version.

Now, import the necessary data into MySQL. Be sure to use mysqldump rather than simply copying files. If you're upgrading from an earlier version of MySQL or MariaDB, avoid importing the MySQL database outright and manually add users instead.

3.2 Key Changes in MySQL 8.0

While this version boasts improved speed and efficiency, developers should pay attention to significant configuration and behavior changes:

• MySQL 8.0 no longer defaults to creating passwords using the older algorithm. You need to enable it.
• Most importantly, the MySQL PASSWORD() function has been deprecated. Make sure to update your code if you depend on it.

To create a user with the old password method:

CREATE USER 'nativeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

You can also set this as the default in my.cnf (note that the FreeBSD port saves my.cnf in /usr/local/etc/mysql/my.cnf, differing from the previous /usr/local/etc/my.cnf).

CREATE USER 'nativeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

3.3 Preparing my.cnf

If you're utilizing the default configuration file, make sure to set max_connections to a reasonable figure, like 200. Optionally, some tuning may be applied as well:

# Tuning for large RAM (>16GB)
innodb-flush-log-at-trx-commit = 2
# Safe for ZFS; may corrupt data on non-journaling filesystems
skip-innodb_doublewrite
# Optimized for fast disks; adjust to 500 for SSDs. Remove these lines if using older SATA drives.
innodb_io_capacity = 1000
innodb_flush_neighbors = 0
    

Here's how your adjusted my.cnf might look, with additional adjustments noted such as commenting out bind-address for external database connections. I also increased innodb_buffer_pool to 16G and set innodb_log_file_size to 4G, as I'm using InnoDB for the game tables. If you don't utilize InnoDB, the default of 1G will suffice.

# $FreeBSD: branches/2020Q1/databases/mysql80-server/files/my.cnf.sample.in 469734 2018-05-12 15:35:25Z mmokhi $

This is the hidden content, please

• Sign In
• or
• Sign Up

For ongoing optimization, I recommend periodically running mysql-tuner. It will provide critical insights about your server, but first, you need to let it run live for data collection.

cd /root
fetch http://mysqltuner.pl
perl mysqltuner.pl

For further instructions, visit

This is the hidden content, please

• Sign In
• or
• Sign Up

.

If you encounter any challenges while following this tutorial, feel free to comment, as I'm here to help!