ちゃんるいすのブログ

オタクエンジニアの雑記

俺的 MySQL 8 まとめ


俺的 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)