Setting up your server's backbone (FreeBSD 12/ MySQL 8)

[Shogun 4480]

Hi everyone,

 

Since I have just set up a new server using the latest versions of both FreeBSD, I took the chance to come back and make a little contribution to my old forum.

 

NOTE: MySQL 5.7 and newer deprecate the PASSWORD() function and thus requires changes on the source and on your website code. Install MySQL 5.6 instead if you are not ready for this - these instructions should work just fine with it, except for the my.cnf path

 

1. Physical server setup

 

I divided this guide into three parts; hardware setup, OS and MySQL.

 

Previous to the configuration of the OS we will have, of course, rented our server and set up some partitions. In my case, it's a server with 64 GB RAM and 2 NVMe disks. NVMe disks are the fastest in the market, performing even better than SSD. As for the OS, my provider, a very popular french company, offers only one BSD option - a premade template using Freebsd 11.3 ZFS, which I chose. Next I opted for ENglish and "set custom partitions". 

 

In the partition setup screen, I decide to assign MySQL its own partition on /var/db/mysql, with roughly three quarters of the space available (400 GB) and leave the rest for everything else. Finally I set a 2GB swap partition. Note that since we are using a RAID setup, the space taken by our partitions show as double.

 

 

[A RAID, to explain it in layman terms, is a number of hard drives, all containing the same data, working simultaneously - so if one of them fails, the server can continue to work until the faulty disk is replaced and no data or uptime is lost. The downside is that the extra hard drives don't give us any extra space, a price that is worth paying for mission critical applications like this]

 

Having MySQL in its own partition is not strictly needed so if you don't know how much space your MySQL partition will need, just make one big partition for everything (Mountpoint "/") and delete the default "/home" partition which is of little use for us. Remember to use the arrow icon when you set up your last partition to make sure you're using all the available space left!

 

Once done here, enter a hostname for your server and let it get installed. Once completed, you will receive an e-mail with the root password, which you can use to connect to your server through putty on port 22. I won't explain here the usage of putty.

 

 

2. Upgrading to latest FreeBSD

 

2.1 Standard upgrade procedure

 

I have highlighted the word upgrade so you notice we are talking about updating the whole kernel here to a new version - thus, an upgrade. Once we logged in, it's time to update our server to the latest FreeBSD release which at the time of writing this post is 12.1, but do check here first: freebsd.org

freebsd-update upgrade -r 12.1-RELEASE

Follow the on screen instructions. We have a clean system, so when asked "Does this look reasonable?" feel free to answer y as we are not going to break anything at this point. However, if you upgrade an existing server you should check each file that is being changed, assuming you know what those changes mean of course.

 

You will be eventually shown, as in every OS update, a list of files to be updated - just type :q and enter to keep going. Finally you will be prompted to type "freebsd-update install" and, after the installation is done, to reboot. Now we are supposed to rebuild our ports, since we did not install anything yet and there is nothing to rebuild we will ignore this step; otherwise, rebuild your ports or upgrade your packages using either "portmaster -GaD" or "pkg update".

 

Finally we will type

freebsd-update install

and we will have our new version of FreeBSD ready to use.

 

 

2.2 Standard update procedure

 

If you are using OVH, it's time now to change your repositories since OVH does not provide updates for FreeBSD 12 in their custom repos yet, so we will be pulling our updates from the standard FreeBSD repositories instead. The downside is that we won't be able to use OVH's Real Time Monitoring system, but in my experience it is not of much use anyway. So let's do this:

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

If the file is empty, paste the following text:

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

Now let's disable OVH repos:

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

And change both instances of "enabled: yes" to "enabled: no". If OVH adds FreeBSD 12 repositories to their repos at some point, you can always revert this change in the future.

 

And now it's time to test updating our system works:

portsnap fetch extract

pkg update

pkg upgrade

If you just installed version 12.1 of FreeBSD like me it's possible that pkg is not working for you either. The solution is the following:

pkg-static install -f pkg

Then you should be able to run pkg normally.

 

 

2.3 Rebuilding databases

 

Some system databases need to be rebuilt after a change, such as the changes resulting in a release upgrade like the one we just made. Also, we want to add our e-mail to receive root e-mail messages which may be important.

 

ee /etc/aliases

 

under:

# root: [email protected]

add:

root:  [your email]

Save and exit, and then let's sync the users database:

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

 

2.4 Set up sendmail

 

By default, FreeBSD generates audits periodically which can alert us to issues in our system (see crontab). So we will have them forwarded to our email.

ee /etc/crontab

We add at the top:

MAILTO=[our email address]

This forwards the cron output to our chosen mail address.

 

Now to basic sendmail setup:

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

NOTE: if you run your own mail server, you should add it as a smart host for sendmail; take a look here: https://lists.freebsd.org/pipermail/freebsd-questions/2004-February/035329.html

 

Check /var/maillog to make sure everything works.

 

(Be aware that your mail server might not like some random unknown server sending e-mail to it, so receiving them may require some work.)

 

 

2.4 Weeding out issues

 

Version 12 being quite new, we will find some things are a bit buggy. These actions are not (should be not) part of the installation process, but are particular issues encountered with version 12.

 

I got some error message with Perl 5.30 while building ports so I installed it from ports and the problem is gone:

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

Another error was caused by SSL missing, so I installed OpenSSL to solve it:

pkg install openssl

And added this lime in /etc/make.conf so the ports stop complaining when building. /etc/make.conf does not exist in the base install, so just create it.

DEFAULT_VERSIONS+=ssl=openssl

 

2.5 System tuning

 

There are hundreds of guides in the internet about tuning this or other sysctl setting on FreeBSD. Take them with a pinch of salt as they may not be appropiate to your FreeBSD version, and the default settings change in every version, potentially leaving former tuning settings obsolete. Here are a few ones that are proven to apply to this specific case:

 

in /boot/loader.conf

# prevent swapping by mysql - set this to a quarter or half of your RAM - ZFS only!
vfs.zfs.arc_max="16G"

In /etc/sysctl.conf

kern.ipc.soacceptqueue=512 # max incoming TCP connections (default is 128, not very good in case of DDoS)
compat.ia32.maxdsiz=2147483648 # compatibility for 32 bit apps

Finally, if you want to have a firewall, you may want to install pf. I think I made a guide at some point, in any case it's beyond the scope of this tutorial.

 

On a production server, you should also set up ssh key authentication and log in through an user specially created for running your Metin server. Remember to add this user to the wheel group so you can su into root from it as necessary; thereafter you can disable password authentication altogether in /etc/ssh/sshd_config. There are plenty of guides as well on how to do this, as it's a very standard procedure in the Unix world.

 

 

3. Installing MySQL 8

 

3.1 The installation itself

 

Allright so far so good. Now it's time for MySQL 8. I have no idea why they decided to jump from 5.7 to 8 straight away, but it seems MySQL 8 does better on benchmarks than the previous versions. So I will attempt to run Metin on it.

pkg install mysql80-server

pkg install mysql80-client

Add in rc.conf

mysql_enable="YES"

Now let's disable binary logging to save space, by commenting this line in /usr/local/etc/mysql/my.cnf

#log-bin                         = mysql-bin

And disable strict SQL - Metin does not like it:

sql-mode                        = ""

At this point you should start MySQL and check everything is fine:

 

service mysql-server start

 

Note you do not need to run mysql_upgrade in this version. 

 

Now you should import whichever data you want to import into MySQL. Do not just copy the files, use mysqldump. You should not import the mysql database if you're upgrading from older MySQL or MariaDB versions - add your users manually there.

 

3.2 Important changes in MySQL 8.0

 

Of course this version is faster and more efficient and so on, but it's not up to me to explain those changes. What I am going to go over is changes in configuration and behavior that you need to know about.

 

Note that MySQL 8.0 does not create passwords with the old algorithm by default. We have to enable it. Also - very important- the MySQL PASSWORD() function has been deprecated - replace it in your code if you use it (the source does use it).

 

Creating an user with the old password method:

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

Or you can set this as default in my.cnf. Note the FreeBSD port of MySQL 8 saves my.cnf in /usr/local/etc/mysql/my.cnf - instead of /usr/local/etc/my.cnf as older versions used to do.

[mysqld]
default-authentication-plugin=mysql_native_password

3.3 Getting my.cnf ready

 

If you are using the default config file supplied, make sure you set max_conections to a reasonable number, let's say 200. Optionally, we can set do some tuning as well.

# tuning for large RAM (>16GB)
innodb-flush-log-at-trx-commit  = 2
# safe on ZFS - may corrupt data on non journaling filesystems
skip-innodb_doublewrite
# tuning for fast disks. Set to 500 for SSD. Remove both lines if your disk is an old SATA drive.
innodb_io_capacity = 1000
innodb_flush_neighbors = 0

Here is how our my.cnf looks like with these changes. I have also commented out bind-address since I do want to connect to my database from the outside, but that may not be your case. I have also set innodb_buffer_pool to 16G and innodb_log_file_size to 4G, since I have converted the game tables to InnoDB. If you are not using innodb, you can just leave the default of 1G.

 

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

[client]
port                            = 3306
socket                          = /tmp/mysql.sock

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
#bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql/
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
#log-bin                         = mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
default_password_lifetime       = 0
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
open_files_limit                = 32768
table_open_cache                = 16384
table_definition_cache          = 8192
net_retry_count                 = 16384
key_buffer_size                 = 256M
max_allowed_packet              = 64M
long_query_time                 = 5
innodb_buffer_pool_size         = 16G
innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 4G
innodb_log_buffer_size          = 128M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
skip-symbolic-links

# Our stuff here
default-authentication-plugin   = mysql_native_password
max_connections = 200
# disable strict mode (doesnt play well with our queries)
sql-mode                        = ""
# tuning for large RAM
innodb-flush-log-at-trx-commit  = 2
# safe on zfs - may corrupt data on non journaling filesystems
skip-innodb_doublewrite
# tuning for ssd and nvme
innodb_io_capacity = 1000
innodb_flush_neighbors = 0

[mysqldump]
max_allowed_packet              = 512M
quote_names
quick

  For further tuning I recommend running mysql-tuner once in a while. It will provide us with valuable insights on our server; note that you should first let it run live so data can be gathered.

cd /root

fetch http://mysqltuner.pl

perl mysqltuner.pl

 

For instructions visit 

This is the hidden content, please

• Sign In
• or
• Sign Up

 

Feel free to comment on any issues you may have with my tutorial, I may try to help.

 

 

Edited August 25, 2022 by Metin2 Dev
Core X - External 2 Internal

[arves100 1496]

Amazing tutorial, thanks for sharing.

I'd say you miswrote  "sql-mode" and "log-bin" file, they should go in my.cnf

Edited March 31, 2020 by arves100

[Shogun 4480]

2 hours ago, arves100 said:

Amazing tutorial, thanks for sharing.

I'd say you miswrote  "sql-mode" and "log-bin" file, they should go in my.cnf

 

Fixed thanks

[Trial 231]

Nice tutorial, thanks for sharing!

Edited April 1, 2020 by Trial

[Defacer35 21]

Amazing content ! Thank you.

[Shogun 4480]

A small update - I decided to revert to using MariaDB 10.4 instead because our Log DB is running on MariaDB and the upgrade to MySQL 8 would involve a full dump and restore which would take hours with the server offline (50 GB of data there)

 

Another reason is neither percona toolkit or xtrabackup, two tools I wanted to try out recently (more on this in a new post) do not have a FreeBSD port that is compatible with MySQL 8.

[scrabbyyy 20]

On 4/19/2020 at 9:03 AM, Shogun said:

A small update - I decided to revert to using MariaDB 10.4 instead because our Log DB is running on MariaDB and the upgrade to MySQL 8 would involve a full dump and restore which would take hours with the server offline (50 GB of data there)

 

Another reason is neither percona toolkit or xtrabackup, two tools I wanted to try out recently (more on this in a new post) do not have a FreeBSD port that is compatible with MySQL 8.

 

 i cant connect to mysql8 or mariadb10.4 with freebsd12.1 please help

[rcdcompany 1]

On 5/18/2020 at 10:28 AM, scrabbyyy said:

 

 i cant connect to mysql8 or mariadb10.4 with freebsd12.1 please help

too

[porexemplo 13]

service mysql-server start
 

Quote

/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql

I'm bugging with this mysql installation. The perl5 version is not the right one for.

Edited February 10, 2022 by porexemplo