Run MySQL 8, RancherOS, gtid

zabbix.cnf

cat << 'EOF' > zabbix.cnf
[mysqld]
innodb_flush_method = O_DIRECT

open_files_limit = 65535
max_connections = 2000

key_buffer_size = 32M
join_buffer_size = 1M
sort_buffer_size = 2M
read_buffer_size = 256K
read_rnd_buffer_size = 256K

optimizer_switch=index_condition_pushdown=off
# https://support.zabbix.com/browse/ZBX-10652
EOF

To really respect open_files_limit in the engine, must adjust service global settings:

mkdir -p /etc/systemd/system/mysqld.service.d
cd /etc/systemd/system/mysqld.service.d
echo -e "[Service]\nLimitNOFILE=65535"> limits.conf
systemctl daemon-reload

binlog settings:

cat << 'EOF' > binlog.cnf
binlog_cache_size = 64M
max_binlog_size = 1G
binlog_expire_logs_seconds = 604800
EOF

memory.cnf:

cat << 'EOF' > memory.cnf
[mysqld]
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 1

# innodb_log_file_size = innodb_buffer_pool_size / innodb_buffer_pool_instances / 4
innodb_log_file_size = 64M
EOF

Live variables:

SELECT
@@innodb_flush_method,
@@open_files_limit,
@@max_connections,
@@key_buffer_size,
@@join_buffer_size,
@@sort_buffer_size,
@@optimizer_switch,
@@innodb_buffer_pool_size,
@@innodb_buffer_pool_instances,
@@innodb_log_file_size
\G

Check MySQL 8 live settings:

select
@@hostname,
@@version,
@@datadir,
@@innodb_file_per_table,
@@skip_name_resolve,
@@key_buffer_size,
@@max_allowed_packet,
@@max_connections,
@@open_files_limit,
@@join_buffer_size,
@@sort_buffer_size,
@@read_buffer_size,
@@thread_cache_size,
@@wait_timeout,
@@innodb_buffer_pool_size,
@@innodb_log_file_size,
@@innodb_log_buffer_size,
@@innodb_flush_method,
@@innodb_buffer_pool_instances,
@@innodb_flush_log_at_trx_commit,
@@optimizer_switch\G

Create data directories

mkdir -p ~/mysql/var/lib/mysql ~/mysql/etc/mysql/conf.d

Move to config dir:

cd ~/mysql/etc/mysql/conf.d

docker:

cat << 'EOF' > docker.cnf
[mysqld]
skip-host-cache
skip-name-resolve
EOF

Important if DB runs in cloud under RDS:

cat << 'EOF' > wait_timeout.cnf
[mysqld]
wait_timeout = 86400
EOF

Disable performance schema:

cat << 'EOF' > performance_schema.cnf
[mysqld]
performance_schema = 0
EOF

Allow mysql_native_password:

cat << 'EOF' > mysql_native_password.cnf
[mysqld]
default-authentication-plugin=mysql_native_password
EOF

Set direcotry for export:

cat << 'EOF' > secure_file_priv.cnf
[mysqld]
secure-file-priv=/tmp
EOF

Enable replication settings:

cat << 'EOF' > gtid_mode.cnf
[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_expire_logs_seconds = 604800
max_binlog_size=100M
EOF

Configure node as unique:

cat << 'EOF' > unique_node.cnf
[mysqld]
log_bin=/tmp/master1
server_id=1
EOF

Configure node as unique:

cat << 'EOF' > unique_node.cnf
[mysqld]
log_bin=/tmp/master2
server_id=2
EOF

Check:

SELECT @@gtid_mode,@@enforce_gtid_consistency,@@binlog_expire_logs_seconds,@@max_binlog_size,@@log_bin,@@server_id\G

Othervise if no replication needed:

cat << 'EOF' > disable_binary_log.cnf
[mysqld]
disable_log_bin
EOF

Zabbix specific settings:

cat << 'EOF' > zabbix.cnf
[mysqld]
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 1

innodb_flush_log_at_trx_commit = 0
# https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
# The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
# With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
# With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

innodb_flush_method = O_DIRECT
# https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method
# fsync or 0: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting.
# O_DSYNC or 1: InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.
# littlesync or 2: This option is used for internal performance testing and is currently unsupported. Use at your own risk.
# nosync or 3: This option is used for internal performance testing and is currently unsupported. Use at your own risk.
# O_DIRECT or 4: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.
# O_DIRECT_NO_FSYNC: InnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call after each write operation.

optimizer_switch=index_condition_pushdown=off
# https://support.zabbix.com/browse/ZBX-10652

max_connections = 2000

# innodb_buffer_pool_size / innodb_buffer_pool_instances / 4
innodb_log_file_size = 64M

join_buffer_size=256K

innodb_read_io_threads = 8
innodb_write_io_threads = 8
EOF

Check variables:

SHOW GLOBAL VARIABLES LIKE '%bin%';
SELECT @@gtid_mode, @@server_id, @@server_uuid, @@expire_logs_days, @@max_binlog_size\G

Launch container:

docker run --name mysql8021 -v $HOME/mysql/var/lib/mysql:/var/lib/mysql -v $HOME/mysql/etc/mysql/conf.d:/etc/mysql/conf.d  -e MYSQL_ROOT_PASSWORD="zabbix" -e TZ="Europe/Riga" -p 3306:3306 -d mysql:8.0.21

Remove previous container:

docker stop mysql8 && docker rm mysql8

docker run --name mysql8 -e MYSQL_ROOT_PASSWORD="zabbix" -e TZ="Europe/Riga" -p 3306:3306 -d mysql:8

Enter client:

docker exec -it mysql8 mysql -uroot -pzabbix

Create user:

CREATE USER "zabbix"@"10.133.253.43" IDENTIFIED BY "zabbix";
ALTER USER "zabbix"@"10.133.253.43" IDENTIFIED WITH mysql_native_password BY "zabbix";
ALTER USER "root"@"%" IDENTIFIED WITH mysql_native_password BY "zabbix";
CREATE DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_bin;
GRANT SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, ALTER, INDEX, REFERENCES ON zabbix.* TO "zabbix"@"10.133.253.43";
FLUSH PRIVILEGES;
exit

Or for test environment:

CREATE USER "zabbix"@"%" IDENTIFIED BY "zabbix";
ALTER USER "zabbix"@"%" IDENTIFIED WITH mysql_native_password BY "zabbix";
ALTER USER "root"@"%" IDENTIFIED WITH mysql_native_password BY "zabbix";
CREATE DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON zabbix.* TO "zabbix"@"%";
FLUSH PRIVILEGES;
exit

Restart:

docker restart mysql8

See live variables:

docker exec -it mysql8 mysql -uroot -pzabbix -e 'select @@hostname, @@version, @@datadir, @@innodb_file_per_table, @@skip_name_resolve, @@key_buffer_size, @@max_allowed_packet, @@max_connections, @@join_buffer_size, @@sort_buffer_size, @@read_buffer_size, @@thread_cache_size, @@wait_timeout, @@innodb_buffer_pool_size, @@innodb_log_file_size, @@innodb_log_buffer_size, @@innodb_flush_method, @@innodb_buffer_pool_instances, @@innodb_flush_log_at_trx_commit, @@optimizer_switch\G'

Import from compresed backup:

echo "SET SESSION SQL_LOG_BIN=0;" | gzip | zcat - db.conf.zabbix.sql.gz | mysql -hmysql8 -uroot -pzabbix zabbix

Import from compresed backup:

echo "SET SESSION SQL_LOG_BIN=0;" | gzip | zcat - db.full.zabbix.sql.gz | mysql -hmysql8 -uroot -pzabbix zabbix

Import from uncompressed backup:

(echo "SET SESSION SQL_LOG_BIN=0;"; cat db.full.zabbix.sql ) | mysql -u root zabbix

2 weeks for raw data, 1h partitions, 1 year for trends, 2 partitions for premade:

Install stored procedures:

cat mysql.partitioning.2w.1y.sql | mysql zabbix

Cronjob:

echo '4 4 * * * root /etc/zabbix/scripts/zabbix_partitioning.sh'> /etc/cron.d/0404zabbixPartitioning

Additionally: https://www.bestremotedba.com/2020/05/05/how-to-configure-multi-master-replication-on-mysql-8/

Run MySQL 8, RancherOS, gtid

No comments:

Post a Comment

solve, automate, speed up

RancherOS

Run MySQL 8, RancherOS, gtid

No comments:

Post a Comment