Premium Shogun 4590 Posted March 25, 2020 Premium Share Posted March 25, 2020 (edited) 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 84 1 1 1 1 3 1 1 32 1 79 Link to comment Share on other sites More sharing options...
Former Staff arves100 1543 Posted March 31, 2020 Former Staff Share Posted March 31, 2020 (edited) 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 Everyday you wake up as a Metin2 developer is a bad day... METIN1 src when Link to comment Share on other sites More sharing options...
Premium Shogun 4590 Posted April 1, 2020 Author Premium Share Posted April 1, 2020 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 Link to comment Share on other sites More sharing options...
Trial 234 Posted April 1, 2020 Share Posted April 1, 2020 (edited) Nice tutorial, thanks for sharing! Edited April 1, 2020 by Trial 1 Link to comment Share on other sites More sharing options...
Defacer35 21 Posted April 4, 2020 Share Posted April 4, 2020 Amazing content ! Thank you. Link to comment Share on other sites More sharing options...
Premium Shogun 4590 Posted April 19, 2020 Author Premium Share Posted April 19, 2020 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. 1 Link to comment Share on other sites More sharing options...
scrabbyyy 20 Posted May 18, 2020 Share Posted May 18, 2020 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 Link to comment Share on other sites More sharing options...
rcdcompany 1 Posted August 3, 2021 Share Posted August 3, 2021 On 5/18/2020 at 10:28 AM, scrabbyyy said: i cant connect to mysql8 or mariadb10.4 with freebsd12.1 please help too Link to comment Share on other sites More sharing options...
porexemplo 13 Posted February 10, 2022 Share Posted February 10, 2022 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts