地方エンジニアの学習日記

興味ある技術の雑なメモだったりを書いてくブログ。たまに日記とガジェット紹介。

【MySQL】8.4を入れて遊んでみる

https://docs.oracle.com/cd/E17952_01/mysql-8.4-relnotes-en/mysql-8.4-relnotes-en.pdf

リリースされていたので手元のマシンに入れてみるところまでをやってみる。(ソースビルドだと数時間かかりそうなのでとりあえずバイナリでやめておく...)

dev.mysql.com

ダウンロード&解凍

$ wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.0-linux-glibc2.28-x86_64.tar
$ tar -xvf mysql-8.4.0-linux-glibc2.28-x86_64.tar
$ tar -xvf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz

$ ls -1
mysql-8.4.0-linux-glibc2.28-x86_64
mysql-8.4.0-linux-glibc2.28-x86_64.tar
mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz
mysql-router-8.4.0-linux-glibc2.28-x86_64.tar.xz
mysql-test-8.4.0-linux-glibc2.28-x86_64.tar.xz

datadirを作って動かしてみる

bin/mysqld

できた

mysql> status
--------------
bin/mysql  Ver 8.4.0 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:      10
Current database:
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     8.4.0
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /tmp/mysql.sock
Binary data as:     Hexadecimal
Uptime:         5 min 22 sec

Threads: 2  Questions: 9  Slow queries: 0  Opens: 146  Flush tables: 3  Open tables: 65  Queries per second avg: 0.027
--------------

mysql_native_passwordの話

mysql_native_passwordのユーザーを使いたい場合はこれだけだと接続エラーになるので```my.cnfに以下を追記する(mysql_native_passwordが非推奨になったやつ。8.4.0)

[mysqld]
mysql_native_password=ON

パスワードリセットを促されるのでリセット

mysql> status
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

plugin一覧をみると一応プラグイン自体は入っている模様

mysql> show plugins;
+----------------------------------+----------+--------------------+---------+---------+
| Name                             | Status   | Type               | Library | License |
+----------------------------------+----------+--------------------+---------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| caching_sha2_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha2_cache_cleaner               | ACTIVE   | AUDIT              | NULL    | GPL     |
| daemon_keyring_proxy_plugin      | ACTIVE   | DAEMON             | NULL    | GPL     |
| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLES                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESTATS                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_INDEXES                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESPACES               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_COLUMNS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_VIRTUAL                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CACHED_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA               | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| TempTable                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                        | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndbcluster                       | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndbinfo                          | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
| ngram                            | ACTIVE   | FTPARSER           | NULL    | GPL     |
| mysqlx_cache_cleaner             | ACTIVE   | AUDIT              | NULL    | GPL     |
| mysqlx                           | ACTIVE   | DAEMON             | NULL    | GPL     |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
+----------------------------------+----------+--------------------+---------+---------+
48 rows in set (0.01 sec)

どうなるんだろうと思っていたので使えるようでひとまず良かった。とはいえ非推奨ではあるので変えていきましょうはそうなんですよねぇ。

SQL_CALC_FOUND_ROWS

使えた。一安心w

mysql> use db1
Database changed
mysql> CREATE TABLE employees (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(255),
    ->     position VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO employees (name, position) VALUES
    -> ('John Doe', 'Manager'),
    -> ('Jane Smith', 'Developer'),
    -> ('Alice Johnson', 'Designer'),
    -> ('Bob Brown', 'Developer');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 2;
+----+------------+-----------+
| id | name       | position  |
+----+------------+-----------+
|  1 | John Doe   | Manager   |
|  2 | Jane Smith | Developer |
+----+------------+-----------+
2 rows in set, 1 warning (0.00 sec)

SLAVEやMASTERと入っているコマンド

この辺は言われていた通り廃止になっている。mysqld_exporterは対応待ちとなりそう(if文足すだけなのですぐに終わるはず)。ローカルにある手順とかを書き換えるみたいなことすれば終わるのでまあ問題はないでしょう。

ただvariablesにはあるっぽい?

mysql> show variables like '%slave%';
+-----------------------------+---------------+
| Variable_name               | Value         |
+-----------------------------+---------------+
| init_slave                  |               |
| log_slave_updates           | ON            |
| log_slow_slave_statements   | OFF           |
| pseudo_slave_mode           | OFF           |
| rpl_stop_slave_timeout      | 31536000      |
| skip_slave_start            | OFF           |
| slave_allow_batching        | ON            |
| slave_checkpoint_group      | 512           |
| slave_checkpoint_period     | 300           |
| slave_compressed_protocol   | OFF           |
| slave_exec_mode             | STRICT        |
| slave_load_tmpdir           | /tmp          |
| slave_max_allowed_packet    | 1073741824    |
| slave_net_timeout           | 60            |
| slave_parallel_type         | LOGICAL_CLOCK |
| slave_parallel_workers      | 4             |
| slave_pending_jobs_size_max | 134217728     |
| slave_preserve_commit_order | ON            |
| slave_skip_errors           | OFF           |
| slave_sql_verify_checksum   | ON            |
| slave_transaction_retries   | 10            |
| slave_type_conversions      |               |
| sql_slave_skip_counter      | 0             |
+-----------------------------+---------------+
23 rows in set (0.01 sec)

mysqlpumpがなくなった

一度も使わなかった...さよなら...

MySQL Shellを使ってねとのこと