GianiVersace scrie: ↑06 Sep 2023, 12:41 Sigur nu ai instalat mysql cum trb.

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


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

GianiVersace scrie: ↑06 Sep 2023, 13:26 Roaga-i pe cei de la host sa iti instaleze ei mysql8 si tu doar importi tabelele de pe local.

lonelylup scrie: ↑06 Sep 2023, 14:15 memoria ram cat ai acordat pe langa asta?

GianiVersace scrie: ↑06 Sep 2023, 14:58 Daca hostul tau are 2gb RAM, ia-ti 4 cel putin. Cu 2 gb nu mai faci nimic. Uneori eroarea aia o ai si daca ai prea putina memorie. Dar daca oricum tu ai dat doar pkg install la mysql, o sa ai probleme fiindca trb configurat si alte cele

yonr1 scrie: ↑06 Sep 2023, 15:21

GianiVersace scrie: ↑06 Sep 2023, 14:58 Daca hostul tau are 2gb RAM, ia-ti 4 cel putin. Cu 2 gb nu mai faci nimic. Uneori eroarea aia o ai si daca ai prea putina memorie. Dar daca oricum tu ai dat doar pkg install la mysql, o sa ai probleme fiindca trb configurat si alte cele

cred ca nu e configurat nimic.... daca mysql nu era instalat de loc.