FreeBSD ➔ MySQL


Writing Icon

Lizenz: Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0)
Letzte Aktualisierung:
Veröffentlicht:
Verfasser: Markus Kohlmeyer
Beitragender: Jesco Freund
Beitragender: Matthias Weiss


Zu den Voraussetzungen für dieses HowTo siehe bitte: FreeBSD ➔ WebHosting System ➔ Voraussetzungen

Einleitung

Unser WebHosting System wird um folgende Dienste erweitert.

  • MySQL 5.7.22 (InnoDB, MyISAM, GTID)

Installation

MySQL unterstützt mehrere Engines, dieses HowTo beschränkt sich allerdings auf die zwei am Häufigsten verwendeten: InnoDB, MyISAM

Wir installieren databases/mysql57-server und dessen Abhängigkeiten.

cat >> /etc/make.conf << "EOF"
DEFAULT_VERSIONS+=mysql=5.7
"EOF"
mkdir -p /var/db/ports/archivers_libarchive
cat > /var/db/ports/archivers_libarchive/options << "EOF"
_OPTIONS_READ=libarchive-3.3.2
_FILE_COMPLETE_OPTIONS_LIST=LZ4 LZO NETTLE
OPTIONS_FILE_SET+=LZ4
OPTIONS_FILE_SET+=LZO
OPTIONS_FILE_UNSET+=NETTLE
"EOF"

mkdir -p /var/db/ports/devel_bzr
cat > /var/db/ports/devel_bzr/options << "EOF"
_OPTIONS_READ=bzr-2.7.0
_FILE_COMPLETE_OPTIONS_LIST=CA_BUNDLE SFTP
OPTIONS_FILE_SET+=CA_BUNDLE
OPTIONS_FILE_SET+=SFTP
"EOF"

mkdir -p /var/db/ports/devel_cmake
cat > /var/db/ports/devel_cmake/options << "EOF"
_OPTIONS_READ=cmake-3.11.1
_FILE_COMPLETE_OPTIONS_LIST=MANPAGES
OPTIONS_FILE_SET+=MANPAGES
"EOF"

mkdir -p /var/db/ports/devel_py-Jinja2
cat > /var/db/ports/devel_py-Jinja2/options << "EOF"
_OPTIONS_READ=py27-Jinja2-2.10
_FILE_COMPLETE_OPTIONS_LIST=BABEL
OPTIONS_FILE_SET+=BABEL
"EOF"

mkdir -p /var/db/ports/security_py-pynacl
cat > /var/db/ports/security_py-pynacl/options << "EOF"
_OPTIONS_READ=py27-pynacl-1.2.1
_FILE_COMPLETE_OPTIONS_LIST=BUNDLED_NACL
OPTIONS_FILE_UNSET+=BUNDLED_NACL
"EOF"

mkdir -p /var/db/ports/textproc_py-docutils
cat > /var/db/ports/textproc_py-docutils/options << "EOF"
_OPTIONS_READ=py27-docutils-0.14
_FILE_COMPLETE_OPTIONS_LIST=PYGMENTS
OPTIONS_FILE_SET+=PYGMENTS
"EOF"

mkdir -p /var/db/ports/textproc_py-snowballstemmer
cat > /var/db/ports/textproc_py-snowballstemmer/options << "EOF"
_OPTIONS_READ=py27-snowballstemmer-1.2.0
_FILE_COMPLETE_OPTIONS_LIST=PYSTEMMER
OPTIONS_FILE_SET+=PYSTEMMER
"EOF"

mkdir -p /var/db/ports/databases_mysql57-client
cat > /var/db/ports/databases_mysql57-client/options << "EOF"
_OPTIONS_READ=mysql57-client-5.7.22
_FILE_COMPLETE_OPTIONS_LIST=SASLCLIENT
OPTIONS_FILE_UNSET+=SASLCLIENT
"EOF"

mkdir -p /var/db/ports/databases_mysql57-server
cat > /var/db/ports/databases_mysql57-server/options << "EOF"
_OPTIONS_READ=mysql57-server-5.7.22
_FILE_COMPLETE_OPTIONS_LIST=ARCHIVE BLACKHOLE EXAMPLE FEDERATED INNOBASE PARTITION PERFSCHEMA PERFSCHM
OPTIONS_FILE_SET+=ARCHIVE
OPTIONS_FILE_SET+=BLACKHOLE
OPTIONS_FILE_UNSET+=EXAMPLE
OPTIONS_FILE_SET+=FEDERATED
OPTIONS_FILE_SET+=INNOBASE
OPTIONS_FILE_SET+=PARTITION
OPTIONS_FILE_SET+=PERFSCHEMA
OPTIONS_FILE_SET+=PERFSCHM
"EOF"

cd /usr/ports/databases/mysql57-client
make config-recursive all install clean-depends clean

cd /usr/ports/databases/mysql57-server
make config-recursive 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}

echo 'mysql_enable="YES"' >> /etc/rc.conf
echo 'mysql_limits="YES"' >> /etc/rc.conf
echo 'mysql_dbdir="/data/db/mysql"' >> /etc/rc.conf
echo 'mysql_optfile="/usr/local/etc/mysql/my.cnf"' >> /etc/rc.conf

Konfiguration

HINWEIS: Die Konfiguration orientiert sich an diesem RootForum Community Forenbeitrag.

cat > /usr/local/etc/mysql/my.cnf << "EOF"
[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                    = ::
basedir                         = /usr/local
datadir                         = /data/db/mysql
tmpdir                          = /data/db/mysql_tmpdir
slave-load-tmpdir               = /data/db/mysql_tmpdir
secure-file-priv                = /data/db/mysql_secure
log-bin                         = /data/db/mysql/mysql-bin
log-output                      = FILE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
general-log                     = 0
general-log-file                = /data/db/mysql/general.log
slow-query-log                  = 1
slow-query-log-file             = /data/db/mysql/slow-query.log
default_authentication_plugin   = mysql_native_password
default_password_lifetime       = 0
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 256K
binlog_stmt_cache_size          = 256K
enforce-gtid-consistency        = 1
gtid-mode                       = ON
max_connections                 = 501
safe-user-create                = 1
lower_case_table_names          = 1
explicit_defaults_for_timestamp = 1
myisam-recover-options          = FORCE,BACKUP
net_retry_count                 = 16384
open_files_limit                = 32768
table_open_cache                = 8192
table_definition_cache          = 4096
max_allowed_packet              = 64M
key_buffer_size                 = 256M
myisam_sort_buffer_size         = 16M
bulk_insert_buffer_size         = 64M
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
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.05
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 2G
innodb_buffer_pool_dump_pct     = 100
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_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_flush_log_at_timeout     = 2
innodb_flush_log_at_trx_commit  = 2
innodb_disable_sort_file_cache  = 1
innodb_write_io_threads         = 4
innodb_read_io_threads          = 8
innodb_sort_buffer_size         = 2048K
#log-queries-not-using-indexes
skip-symbolic-links
#skip-name-resolve

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick
"EOF"

Sicherheit

MySQL wird nun zum ersten Mal gestartet, was durch das Erzeugen der InnoDB-Files einige Minuten dauern kann.

service mysql-server start

Abschliessend wird das Klartext MySQL root-Passwort aus /root/.mysql_secret mittels mysql_config_editor verschlüsselt in /root/.mylogin.cnf gespeichert und /root/.mysql_secret sicherheitshalber gelöscht.

cat /root/.mysql_secret

# First change MySQL-root password
mysqladmin -h localhost -uroot -p password --default-auth=mysql_native_password

# Setup /root/.mylogin.cnf
mysql_config_editor set --login-path=client --host=localhost --socket=/tmp/mysql.sock --user=root --password

# Remove cleartext password-file
rm /root/.mysql_secret

Wir erlauben dem MySQL-root User das Einloggen von ::1, 127.0.0.1 und localhost mit dem zuvor festgelegtem Passwort.

mysql -h localhost -uroot --default-auth=mysql_native_password

CREATE USER 'root'@'::1' IDENTIFIED WITH mysql_native_password BY '__MYSQL_ROOT_PASSWORD__' PASSWORD EXPIRE NEVER;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION;
CREATE USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '__MYSQL_ROOT_PASSWORD__' PASSWORD EXPIRE NEVER;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '__MYSQL_ROOT_PASSWORD__' PASSWORD EXPIRE NEVER;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
QUIT;

Abschluss

MySQL sollte abschliessend einmal neu gestartet werden.

service mysql-server restart

Über den Autor