俺的 MySQL 8 メモ
1. MySQL 8 から SET PERSIST が使える
SET GLOBAL のグローバル変数の変更に加えて、再起動しても反映されるようになる。
mysql> SET PERSIST innodb_buffer_pool_size = 2684354560;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 2684354560 |
+-------------------------+------------+
1 row in set (0.00 sec)
設定ファイルは datadir の mysqld-auto.cnf にあるよ。
# cat mysqld-auto.cnf | jq '.mysql_server .innodb_buffer_pool_size'
{
"Value": "2684354560",
"Metadata": {
"Timestamp": 1565695718407381,
"User": "root",
"Host": "localhost"
}
}
ちなみに、各種変数がどこから読み込まれてるかもちゃんと確認できるようになった。
mysql> select * from performance_schema.variables_info where variable_source!='COMPILED';
+----------------------------------------+-----------------+--------------------------------+-----------+---------------------+----------------------------+---------------+-----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+----------------------------------------+-----------------+--------------------------------+-----------+---------------------+----------------------------+---------------+-----------+
| auto_increment_increment | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 1 | 65535 | 2019-07-23 00:53:28.873553 | root | NULL |
| auto_increment_offset | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 1 | 65535 | 2019-07-23 00:53:28.873736 | root | NULL |
| binlog_checksum | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-07-23 00:53:09.826023 | root | NULL |
| datadir | GLOBAL | /etc/my.cnf | 0 | 0 | NULL | NULL | NULL |
| default_authentication_plugin | GLOBAL | /etc/my.cnf | 0 | 0 | NULL | NULL | NULL |
| enforce_gtid_consistency | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-07-23 00:53:09.943170 | root | NULL |
| foreign_key_checks | DYNAMIC | | 0 | 0 | 2019-08-07 18:09:57.844377 | NULL | NULL |
| group_replication_autorejoin_tries | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 2016 | 2019-08-07 18:06:16.649402 | root | NULL |
| group_replication_consistency | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.649168 | root | NULL |
| group_replication_exit_state_action | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.648924 | root | NULL |
| group_replication_group_name | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.647856 | root | NULL |
| group_replication_group_seeds | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.648604 | root | NULL |
| group_replication_ip_whitelist | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.648762 | root | NULL |
| group_replication_local_address | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.648494 | root | NULL |
| group_replication_member_expel_timeout | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 3600 | 2019-08-07 18:06:16.649280 | root | NULL |
| group_replication_member_weight | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 100 | 2019-08-07 18:06:16.649043 | root | NULL |
| group_replication_recovery_use_ssl | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.648244 | root | NULL |
| group_replication_single_primary_mode | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.648096 | root | NULL |
| group_replication_ssl_mode | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.648361 | root | NULL |
| group_replication_start_on_boot | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-08-07 18:06:16.649528 | root | NULL |
| gtid_mode | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 0 | 2019-07-23 00:53:09.944481 | root | NULL |
| innodb_buffer_pool_size | DYNAMIC | | 5242880 | 9223372036854775807 | 2019-08-13 20:40:02.415363 | root | localhost |
| lock_wait_timeout | DYNAMIC | | 1 | 31536000 | 2019-08-13 20:59:58.011337 | exporter | localhost |
| log_error | GLOBAL | /etc/my.cnf | 0 | 0 | NULL | NULL | NULL |
| pid_file | GLOBAL | /etc/my.cnf | 0 | 0 | NULL | NULL | NULL |
| server_id | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 0 | 4294967295 | 2019-07-23 00:53:09.944685 | root | NULL |
| socket | GLOBAL | /etc/my.cnf | 0 | 0 | NULL | NULL | NULL |
| super_read_only | DYNAMIC | | 0 | 0 | 2019-08-07 18:09:58.112753 | mysql.session | localhost |
+----------------------------------------+-----------------+--------------------------------+-----------+---------------------+----------------------------+---------------+-----------+
28 rows in set (0.06 sec)