MySQL¶
Einleitung¶
Unser Hosting System wird um folgende Dienste erweitert.
- MySQL 8.0.32 (InnoDB, GTID)
Voraussetzungen¶
Zu den Voraussetzungen für dieses HowTo siehe bitte: Hosting System
Installation¶
MySQL unterstützt mehrere Engines, dieses HowTo beschränkt sich allerdings auf die am Häufigsten verwendete: InnoDB
Wir installieren databases/mysql80-client und dessen Abhängigkeiten.
Bash
cat << "EOF" >> /etc/make.conf
#DEFAULT_VERSIONS+=mysql=8.0
"EOF"
mkdir -p /var/db/ports/comms_hidapi
cat << "EOF" > /var/db/ports/comms_hidapi/options
_OPTIONS_READ=hidapi-0.14.0
_FILE_COMPLETE_OPTIONS_LIST=DOCS
OPTIONS_FILE_SET+=DOCS
"EOF"
mkdir -p /var/db/ports/textproc_groff
cat << "EOF" > /var/db/ports/textproc_groff/options
_OPTIONS_READ=groff-1.22.4
_FILE_COMPLETE_OPTIONS_LIST=UCHARDET
OPTIONS_FILE_SET+=UCHARDET
"EOF"
mkdir -p /var/db/ports/print_gsfonts
cat << "EOF" > /var/db/ports/print_gsfonts/options
_OPTIONS_READ=gsfonts-8.11
_FILE_COMPLETE_OPTIONS_LIST=DOCS
OPTIONS_FILE_SET+=DOCS
"EOF"
mkdir -p /var/db/ports/databases_mysql80-client
cat << "EOF" > /var/db/ports/databases_mysql80-client/options
_OPTIONS_READ=mysql80-client-8.0.32
_FILE_COMPLETE_OPTIONS_LIST= SASLCLIENT
OPTIONS_FILE_UNSET+=SASLCLIENT
"EOF"
cd /usr/ports/databases/mysql80-client
make all install clean-depends clean
Wir installieren databases/mysql80-server und dessen Abhängigkeiten.
Bash
mkdir -p /var/db/ports/databases_mysql80-server
cat << "EOF" > /var/db/ports/databases_mysql80-server/options
_OPTIONS_READ=mysql80-server-8.0.32
_FILE_COMPLETE_OPTIONS_LIST= ARCHIVE BLACKHOLE EXAMPLE FEDERATED INNOBASE PARTITION PERFSCHEMA PERFSCHM
OPTIONS_FILE_UNSET+=ARCHIVE
OPTIONS_FILE_UNSET+=BLACKHOLE
OPTIONS_FILE_UNSET+=EXAMPLE
OPTIONS_FILE_UNSET+=FEDERATED
OPTIONS_FILE_UNSET+=INNOBASE
OPTIONS_FILE_UNSET+=PARTITION
OPTIONS_FILE_UNSET+=PERFSCHEMA
OPTIONS_FILE_SET+=PERFSCHM
"EOF"
cd /usr/ports/databases/mysql80-server
make all install clean-depends clean
mkdir -p /data/db/mysql{,_secure,_tmpdir}
chmod 0750 /data/db/mysql{_secure,_tmpdir}
chown mysql:mysql /data/db/mysql{,_secure,_tmpdir}
sysrc mysql_enable=YES
sysrc mysql_dbdir="/data/db/mysql"
sysrc mysql_confdir="/usr/local/etc/mysql"
sysrc mysql_optfile="/usr/local/etc/mysql/my.cnf"
Konfiguration¶
Note
Die Konfiguration orientiert sich an diesem RootForum Community Forenbeitrag.
Bash
cat << "EOF" > /usr/local/etc/mysql/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
bind_address = 127.0.0.1,::1
basedir = /usr/local
datadir = /data/db/mysql
tmpdir = /data/db/mysql_tmpdir
secure_file_priv = /data/db/mysql_secure
log_bin = mysql-bin
log_output = TABLE,FILE
relay_log_recovery = ON
slow_query_log = OFF
slow_query_log_file = slow-query.log
server_id = 1
sync_binlog = 1
sync_relay_log = 1
binlog_cache_size = 256K
binlog_stmt_cache_size = 256K
enforce_gtid_consistency = ON
gtid_mode = ON
max_connections = 101
safe_user_create = ON
lower_case_table_names = 1
myisam_recover_options = FORCE,BACKUP
net_retry_count = 16384
open_files_limit = 32768
table_open_cache = 8192
table_definition_cache = 4096
join_buffer_size = 512K
sort_buffer_size = 2048K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
max_heap_table_size = 256M
tmp_table_size = 256M
long_query_time = 0.05
innodb_thread_concurrency = 8
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_data_home_dir = /data/db/mysql
innodb_log_group_home_dir = /data/db/mysql
innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_sort_buffer_size = 2048K
#log_queries_not_using_indexes = ON
skip_name_resolve = ON
mysqlx = OFF
mysqlx_port = 33060
mysqlx_socket = /tmp/mysqlx.sock
mysqlx_bind_address = 127.0.0.1,::1
"EOF"
Sicherheit¶
MySQL wird nun zum ersten Mal gestartet, was durch das Erzeugen der InnoDB-Files einige Minuten dauern kann.
Bash
service mysql-server start
Abschliessend wird das MySQL root-Passwort neu gesetzt und mittels mysql_config_editor verschlüsselt in /root/.mylogin.cnf gespeichert.
Bash
# First set new MySQL-root password
mysqladmin -h localhost -uroot password --default-auth=caching_sha2_password
# Setup /root/.mylogin.cnf
mysql_config_editor set --login-path=client --host=::1 --host=127.0.0.1 --host=localhost --socket=/tmp/mysql.sock --user=root --password
Wir erlauben dem MySQL-root User das Einloggen von ::1, 127.0.0.1 und localhost mit dem zuvor festgelegtem Passwort.
Bash
mysql -h localhost -uroot --default-auth=caching_sha2_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '__MYSQL_ROOT_PASSWORD__' PASSWORD EXPIRE NEVER;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
CREATE USER 'root'@'127.0.0.1' IDENTIFIED WITH caching_sha2_password BY '__MYSQL_ROOT_PASSWORD__' PASSWORD EXPIRE NEVER;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
CREATE USER 'root'@'::1' IDENTIFIED WITH caching_sha2_password BY '__MYSQL_ROOT_PASSWORD__' PASSWORD EXPIRE NEVER;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION;
FLUSH PRIVILEGES;
QUIT;
Abschluss¶
MySQL sollte abschliessend einmal neu gestartet werden.
Bash
service mysql-server restart