nixos/mysql: add settings and configFile option #81940

aanderse · 2020-03-07T00:05:15Z

Motivation for this change

Things done

cc @thorstenweber83

nixos/modules/services/databases/mysql.nix

infinisil

Nice! Perfect use case for the RFC.

nixos/modules/services/databases/mysql.nix

Izorkin · 2020-03-07T08:32:33Z

Not working this configuration:

    ...
    mysqldump = {
      quick;
      max_allowed_packet = "16M";
    };
    ...

error:

syntax error, unexpected ';', at /home/user/works/nixops/deploy/hetzner-all/pve2-test/mysql.nix:57:45

if use package mariadb.server builded withoutClient = true; error:

2mcwly6c6yz8rf70ikz7zjjf278g2gfi-mysql-setup[96574]: /nix/store/2mcwly6c6yz8rf70ikz7zjjf278g2gfi-mysql-setup: line 30: /nix/store/9zjrpl0blyi7rzlzsd2nx4pjjacgpybx-mariadb-server-10.3.22/bin/mysql: No such file or directory

After this update poorly view the current configuration /etc/my.cnf.
Example after:

[client]
default_character_set=utf8mb4

[myisamchk]
key_buffer_size=256M
myisam_sort_buffer_size=256M
read_buffer_size=2M

[mysql]
default_character_set=utf8mb4

[mysqld]
aria_log_dir_path=/var/data/db/mysql/dblogs
aria_sort_buffer_size=2M
back_log=2048
bind_address=0.0.0.0
binlog_cache_size=2M
binlog_format=ROW
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
datadir=/var/data/db/mysql/db
default_storage_engine=InnoDB
expire_logs_days=7
innodb_autoinc_lock_mode=1
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON
innodb_buffer_pool_size=256M
innodb_compression_algorithm=lz4
innodb_compression_default=ON
innodb_data_file_path=ibdata1:12M:autoextend
innodb_data_home_dir=/var/data/db/mysql/db
innodb_defragment=ON
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=16M
innodb_log_checksums=ON
innodb_log_compressed_pages=ON
innodb_log_file_size=128M
innodb_log_group_home_dir=/var/data/db/mysql/dblogs
innodb_max_dirty_pages_pct=5
innodb_sort_buffer_size=2M
innodb_temp_data_file_path=ibtmp1:12M:autoextend
innodb_thread_concurrency=16
interactive_timeout=3600
join_buffer_size=256K
key_buffer_size=16M
key_cache_division_limit=70
log_bin=/var/data/db/mysql/dblogs/mysql_bin.log
log_bin_index=/var/data/db/mysql/dblogs/mysql_bin.index
log_error=/var/data/db/logs/mysql_error.log
log_warnings=2
long_query_time=0.500000
max_allowed_packet=32M
max_binlog_cache_size=4G
max_binlog_size=256M
max_connect_errors=10240
max_connections=1024
max_heap_table_size=1024M
max_user_connections=128
myisam_sort_buffer_size=2M
performance_schema=OFF
plugin-load-add=ha_rocksdb.so;ha_tokudb.so
port=3306
query_cache_limit=2M
query_cache_size=32M
query_cache_type=ON
read_buffer_size=2M
read_rnd_buffer_size=2M
relay_log=/var/data/db/mysql/dblogs/mysql_relay_bin.log
rocksdb_datadir=/var/data/db/mysql/db_rocksdb
secure_auth=ON
skip_name_resolve=ON
slow_query_log=ON
slow_query_log_file=/var/data/db/logs/mysql_slow.log
sort_buffer_size=2M
symbolic_links=OFF
sync_binlog=0
table_definition_cache=4096
table_open_cache=2048
thread_cache_size=64
thread_handling=pool-of-threads
thread_pool_size=8
tmp_table_size=1024M
tokudb_data_dir=/var/data/db/mysql/db_tokudb
tokudb_dir_per_db=ON
tokudb_log_dir=/var/data/db/mysql/dblogs
transaction_isolation=READ-COMMITTED
wait_timeout=3600

[mysqld_safe]
collation_server=utf8mb4_unicode_ci
default_character_set=utf8mb4
log_error=/var/data/db/logs/mysql_error.log
malloc-lib=/nix/store/k78fbnb3167dwiryiv12fanaqp9j575g-jemalloc-4.5.0/lib/libjemalloc.so

Before:

[mysqld]
port = 3306
datadir = /var/data/db/mysql/db

### Paths
datadir = /var/data/db/mysql/db
innodb_data_home_dir = /var/data/db/mysql/db
innodb_log_group_home_dir = /var/data/db/mysql/dblogs
aria_log_dir_path = /var/data/db/mysql/dblogs
innodb_data_file_path = ibdata1:12M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend

### General options
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 1
innodb_defragment = ON
innodb_compression_algorithm = lz4
innodb_compression_default = ON
innodb_file_per_table = ON
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 5
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON
performance_schema = OFF
symbolic_links = OFF
transaction_isolation = READ-COMMITTED

### Connections
bind_address = 0.0.0.0
back_log = 2048
max_allowed_packet = 32M
max_connect_errors = 10240
max_connections = 1024
max_user_connections = 128
secure_auth = ON
skip_name_resolve = ON

### Threads
innodb_thread_concurrency = 16
thread_cache_size = 64
thread_handling = pool-of-threads
thread_pool_size = 8

### Timeout
wait_timeout = 3600
interactive_timeout = 3600

### Logs
innodb_log_file_size = 128M
innodb_log_checksums = ON
innodb_log_compressed_pages = ON
innodb_log_buffer_size = 16M
log_error = /var/data/db/logs/mysql_error.log
log_warnings = 2
long_query_time = 0.500000
slow_query_log_file = /var/data/db/logs/mysql_slow.log
slow_query_log = ON

### Binlogs
binlog_format = ROW
expire_logs_days = 7
log_bin = /var/data/db/mysql/dblogs/mysql_bin.log
log_bin_index = /var/data/db/mysql/dblogs/mysql_bin.index
relay_log = /var/data/db/mysql/dblogs/mysql_relay_bin.log
binlog_cache_size = 2M
max_binlog_size = 256M
max_binlog_cache_size = 4G
sync_binlog = 0

### Buffers
aria_sort_buffer_size = 2M
innodb_sort_buffer_size = 2M
myisam_sort_buffer_size = 2M
sort_buffer_size = 2M
join_buffer_size = 256K
key_buffer_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
innodb_buffer_pool_size = 256M

### Caches
query_cache_type = ON
query_cache_limit = 2M
query_cache_size = 32M
key_cache_division_limit = 70
table_definition_cache = 4096
table_open_cache = 2048
max_heap_table_size = 1024M
tmp_table_size = 1024M

### zfs
innodb_doublewrite = OFF
innodb_compression_algorithm = none
innodb_compression_default = OFF
innodb_use_atomic_writes = OFF
innodb_use_native_aio = OFF

### zfs, checksums
#aria_page_checksum = OFF
#innodb_checksums = OFF
#innodb_checksum_algorithm = NONE
#innodb_log_checksums = OFF
#binlog_checksum = NONE

### RocksDB
plugin-load-add = ha_rocksdb.so
rocksdb_datadir = /var/data/db/mysql/db_rocksdb

### TokuDB
plugin-load-add = ha_tokudb.so
tokudb_dir_per_db = ON
tokudb_data_dir = /var/data/db/mysql/db_tokudb
tokudb_log_dir = /var/data/db/mysql/dblogs

[client]
default_character_set = utf8mb4

[mysql]
default_character_set = utf8mb4

[mysqld_safe]
default_character_set = utf8mb4
collation_server = utf8mb4_unicode_ci
log_error = /var/data/db/logs/mysql_error.log

### TokuDB
malloc-lib = /nix/store/k78fbnb3167dwiryiv12fanaqp9j575g-jemalloc-4.5.0/lib/libjemalloc.so

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 256M
myisam_sort_buffer_size = 256M
read_buffer_size = 2M

aanderse · 2020-03-07T12:33:01Z

@Izorkin thank you for your feedback and testing! You are always so reliable 😄

Not working this configuration:

    ...
    mysqldump = {
      quick;
      max_allowed_packet = "16M";
    };
    ...

I'm under the impression this is a shorthand syntax for what should be written as:

mysqldump = {
  quick = true;
  max_allowed_packet = "16M";
};

if use package mariadb.server builded withoutClient = true; error:

2mcwly6c6yz8rf70ikz7zjjf278g2gfi-mysql-setup[96574]: /nix/store/2mcwly6c6yz8rf70ikz7zjjf278g2gfi-mysql-setup: line 30: /nix/store/9zjrpl0blyi7rzlzsd2nx4pjjacgpybx-mariadb-server-10.3.22/bin/mysql: No such file or directory

Can you please provide /nix/store/2mcwly6c6yz8rf70ikz7zjjf278g2gfi-mysql-setup?

@Izorkin it is very true that RFC42 doesn't let you hand craft beautiful configuration files which is disappointing to you. I don't know how to address this. I think you should mention this in NixOS/rfcs#42 for a discussion point. Any comment @infinisil?

infinisil · 2020-03-07T12:40:48Z

Yeah I guess commenting in config files themselves isn't something supported by this approach, but I think that's rather minor compared to the benefits it gives. It's still possible to have comments in the Nix code at least.

Izorkin · 2020-03-07T12:47:19Z

I'm under the impression this is a shorthand syntax for what should be written as:

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_quick

Can you please provide /nix/store/2mcwly6c6yz8rf70ikz7zjjf278g2gfi-mysql-setup?

#!/nix/store/8gy2bmpz3qawxr3g8hqhfgkf32wb0wfl-bash-4.4-p23/bin/bash -e

# Wait until the MySQL server is available for use
count=0
while [ ! -e /run/mysqld/mysqld.sock ]
do
    if [ $count -eq 30 ]
    then
        echo "Tried 30 times, giving up..."
        exit 1
    fi

    echo "MySQL daemon not yet started. Waiting for 1 second..."
    count=$((count++))
    sleep 1
done

if [ -f /tmp/mysql_init ]
then

# Execute initial script
# using toString to avoid copying the file to nix store if given as path instead of string,
# as it might contain credentials
cat /nix/store/36wz42cljwgx94kba5pgfavrkjw57g61-mysql_init.sql | /nix/store/9zjrpl0blyi7rzlzsd2nx4pjjacgpybx-mariadb-server-10.3.22/bin/mysql -u root -N
    rm /tmp/mysql_init
fi

l /nix/store/9zjrpl0blyi7rzlzsd2nx4pjjacgpybx-mariadb-server-10.3.22/bin/mysql
ls: невозможно получить доступ к '/nix/store/9zjrpl0blyi7rzlzsd2nx4pjjacgpybx-mariadb-server-10.3.22/bin/mysql': No such file or directory

@Izorkin it is very true that RFC42 doesn't let you hand craft beautiful configuration files which is disappointing to you. I don't know how to address this. I think you should mention this in NixOS/rfcs#42 for a discussion point. Any comment @infinisil?

You can process the block while maintaining the layout of the lines from the configuration?

aanderse · 2020-03-07T12:51:00Z

@infinisil I just realized I missed something here... any thoughts on how to deal with this? https://dev.mysql.com/doc/refman/8.0/en/option-files.html#option-file-inclusions

aanderse · 2020-03-07T12:59:47Z

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_quick

services.mysql.settings.mysqldump.quick = true; is compatible with this. If you test you should see this works 👍 I will add this to examples for clarification.

l /nix/store/9zjrpl0blyi7rzlzsd2nx4pjjacgpybx-mariadb-server-10.3.22/bin/mysql
ls: невозможно получить доступ к '/nix/store/9zjrpl0blyi7rzlzsd2nx4pjjacgpybx-mariadb-server-10.3.22/bin/mysql': No such file or directory

This appears to be a bug in master, unrelated to this PR. Can you possibly test and confirm?

You can process the block while maintaining the layout of the lines from the configuration?

I don't think this is possible 😞

Izorkin · 2020-03-07T13:12:13Z

This appears to be a bug in master, unrelated to this PR. Can you possibly test and confirm?

Yes, this bug in master

diff --git a/nixos/modules/services/databases/mysql.nix b/nixos/modules/services/databases/mysql.nix
index 8d520b82fb5..d1f06e2b328 100644
--- a/nixos/modules/services/databases/mysql.nix
+++ b/nixos/modules/services/databases/mysql.nix
@@ -398,7 +398,7 @@ in
                         # Execute initial script
                         # using toString to avoid copying the file to nix store if given as path instead of string,
                         # as it might contain credentials
-                        cat ${toString cfg.initialScript} | ${mysql}/bin/mysql -u root -N
+                        cat ${toString cfg.initialScript} | ${mariadb.client}/bin/mysql -u root -N
                       ''}

                     rm /tmp/mysql_init

Only this option will not work with MySQL 5.7 and 8.0.

aanderse · 2020-03-07T13:26:41Z

Only this option will not work with MySQL 5.7 and 8.0.

Thanks for confirming the bug exists in master. If you can file an issue that would be appreciated.

florianjacob

Works for my configuration, and I'm always happy to throw out types.lines literals. 👍

nixos/modules/services/databases/mysql.nix

aanderse · 2020-03-10T15:47:59Z

Thanks to @infinisil I believe this is ready for final testing and merging.

nixos/modules/services/databases/mysql.nix

aanderse · 2020-03-10T19:24:39Z

@infinisil I just realized I missed something here... any thoughts on how to deal with this? https://dev.mysql.com/doc/refman/8.0/en/option-files.html#option-file-inclusions

I realized that include directives really work against the new declarative/merging nature of a settings option, so I resolved this outstanding issue by including a configFile escape hatch.

Does anyone have any other requests before merging this?

infinisil

With a fully declarative NixOS config these !include statements can even be replaced with a separate NixOS module that gets importsed, because the new settings option now handles composition :). So it's not entirely unjustified to not support it (though this doesn't work with includes from runtime paths). How it's done now looks good

infinisil · 2020-03-10T20:22:15Z

@GrahamcOfBorg test mysql

aanderse requested review from flokli, infinisil and Izorkin March 7, 2020 00:05

ofborg bot added 6.topic: nixos 8.has: module (update) 10.rebuild-darwin: 0 10.rebuild-linux: 1-10 labels Mar 7, 2020

infinisil reviewed Mar 7, 2020

View reviewed changes

nixos/modules/services/databases/mysql.nix Outdated Show resolved Hide resolved

infinisil reviewed Mar 7, 2020

View reviewed changes

nixos/modules/services/databases/mysql.nix Show resolved Hide resolved

nixos/modules/services/databases/mysql.nix Outdated Show resolved Hide resolved

nixos/modules/services/databases/mysql.nix Outdated Show resolved Hide resolved

aanderse force-pushed the mysql-config branch from 0c205ef to 6ae6712 Compare March 7, 2020 03:01

aanderse marked this pull request as ready for review March 7, 2020 03:20

aanderse requested a review from florianjacob March 7, 2020 03:22

infinisil reviewed Mar 7, 2020

View reviewed changes

nixos/modules/services/databases/mysql.nix Outdated Show resolved Hide resolved

aanderse force-pushed the mysql-config branch from 6ae6712 to 983f5dd Compare March 7, 2020 03:50

aanderse force-pushed the mysql-config branch 2 times, most recently from 1fb9af4 to 0b020a9 Compare March 7, 2020 13:11

aanderse changed the title ~~nixos/mysql: add config and plugin options~~ nixos/mysql: add settings and plugin options Mar 7, 2020

aanderse changed the title ~~nixos/mysql: add settings and plugin options~~ nixos/mysql: add settings and plugins options Mar 7, 2020

florianjacob approved these changes Mar 8, 2020

View reviewed changes

aanderse changed the title ~~nixos/mysql: add settings and plugins options~~ nixos/mysql: add settings option Mar 8, 2020

aanderse force-pushed the mysql-config branch from 0b020a9 to a0bcbd1 Compare March 8, 2020 17:53

aanderse commented Mar 8, 2020

View reviewed changes

nixos/modules/services/databases/mysql.nix Outdated Show resolved Hide resolved

aanderse force-pushed the mysql-config branch from a0bcbd1 to cce4ce0 Compare March 10, 2020 00:44

aanderse changed the title ~~nixos/mysql: add settings option~~ nixos/mysql: add settings and configFile option Mar 10, 2020

aanderse force-pushed the mysql-config branch from cce4ce0 to cac1b6d Compare March 10, 2020 01:05

This was referenced Mar 10, 2020

Configuration file formats for JSON, INI, YAML and TOML #75584

Merged

lib/generators: Add toINI option for duplicate keys #82208

Merged

aanderse force-pushed the mysql-config branch from cac1b6d to 08028b0 Compare March 10, 2020 15:46

infinisil requested changes Mar 10, 2020

View reviewed changes

nixos/modules/services/databases/mysql.nix Outdated Show resolved Hide resolved

nixos/modules/services/databases/mysql.nix Outdated Show resolved Hide resolved

nixos/mysql: add settings and configFile options

641b94b

aanderse force-pushed the mysql-config branch from 08028b0 to 641b94b Compare March 10, 2020 19:15

infinisil approved these changes Mar 10, 2020

View reviewed changes

infinisil merged commit a12bea5 into NixOS:master Mar 10, 2020

aanderse deleted the mysql-config branch March 11, 2020 00:43

infinisil mentioned this pull request Aug 25, 2020

The option services.mysql.configFile' defined in /etc/nixos/configuration.nix' does not exist #96176

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

nixos/mysql: add settings and configFile option #81940

nixos/mysql: add settings and configFile option #81940

aanderse commented Mar 7, 2020 •

edited

infinisil left a comment

Izorkin commented Mar 7, 2020

aanderse commented Mar 7, 2020

infinisil commented Mar 7, 2020

Izorkin commented Mar 7, 2020

aanderse commented Mar 7, 2020

aanderse commented Mar 7, 2020

Izorkin commented Mar 7, 2020

aanderse commented Mar 7, 2020

florianjacob left a comment

aanderse commented Mar 10, 2020

aanderse commented Mar 10, 2020

infinisil left a comment

infinisil commented Mar 10, 2020

nixos/mysql: add settings and configFile option #81940

nixos/mysql: add settings and configFile option #81940

Conversation

aanderse commented Mar 7, 2020 • edited

Motivation for this change

Things done

infinisil left a comment

Choose a reason for hiding this comment

Izorkin commented Mar 7, 2020

aanderse commented Mar 7, 2020

infinisil commented Mar 7, 2020

Izorkin commented Mar 7, 2020

aanderse commented Mar 7, 2020

aanderse commented Mar 7, 2020

Izorkin commented Mar 7, 2020

aanderse commented Mar 7, 2020

florianjacob left a comment

Choose a reason for hiding this comment

aanderse commented Mar 10, 2020

aanderse commented Mar 10, 2020

infinisil left a comment

Choose a reason for hiding this comment

infinisil commented Mar 10, 2020

aanderse commented Mar 7, 2020 •

edited