タイトルがすべて。
MySQL 5.7.39をWindowsでinitializeして起動。
PS > bin\mysqld --initialize-insecure
mysqld: Could not create or access the registry key needed for the MySQL application
to log to the Windows EventLog. Run the application with sufficient
privileges once to create the key, add the key manually, or turn off
logging for that application.
PS > bin\mysqld --console
mysqld: Could not create or access the registry key needed for the MySQL application
to log to the Windows EventLog. Run the application with sufficient
privileges once to create the key, add the key manually, or turn off
logging for that application.
2022-10-10T05:54:48.343808Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-10-10T05:54:48.343866Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-10-10T05:54:48.344166Z 0 [ERROR] Cannot open Windows EventLog; check privileges, or start server with --log_syslog=0
2022-10-10T05:54:48.344184Z 0 [Note] C:\Users\yoku0\Downloads\mysql-5.7.39-winx64\mysql-5.7.39-winx64\bin\mysqld.exe (mysqld 5.7.39) starting as process 10508 ...
<snip>
2022-10-10T05:54:48.538979Z 0 [Note] C:\Users\yoku0\Downloads\mysql-5.7.39-winx64\mysql-5.7.39-winx64\bin\mysqld.exe: ready for connections.
Version: '5.7.39' socket: '' port: 3306 MySQL Community Server (GPL)
MySQL on Windows状態でテーブルにINSERT。
PS > bin\mysql -uroot
mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE d1.t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO d1.t1 VALUES (1, 'Initialized on Windows');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM d1.t1;
+-----+------------------------+
| num | val |
+-----+------------------------+
| 1 | Initialized on Windows |
+-----+------------------------+
1 row in set (0.00 sec)
mysql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)
mysqld.exeをシャットダウンして、datadirをLinuxホストにコピー。
Linux(今回はCentOS 7.9だった)側でそのdatadirを指定して起動。
$ scp -r data conoha:/tmp/5739
$ /usr/mysql/5.7.39/bin/mysqld --no-defaults --datadir=/tmp/5739
2022-10-10T05:59:35.702961Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2022-10-10T05:59:35.703117Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2022-10-10T05:59:35.877316Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-10-10T05:59:35.877370Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-10-10T05:59:35.877425Z 0 [Note] /usr/mysql/5.7.39/bin/mysqld (mysqld 5.7.39) starting as process 17330 ...
<snip>
2022-10-10T05:59:36.077227Z 0 [Note] Event Scheduler: Loaded 0 events
2022-10-10T05:59:36.077608Z 0 [Note] /usr/mysql/5.7.39/bin/mysqld: ready for connections.
Version: '5.7.39' socket: '/tmp/mysql.sock' port: 3306 Source distribution
$ /usr/mysql/5.7.39/bin/mysql -uroot
mysql> SELECT * FROM d1.t1;
+-----+------------------------+
| num | val |
+-----+------------------------+
| 1 | Initialized on Windows |
+-----+------------------------+
1 row in set (0.01 sec)
フツーに浮くし、フツーにSELECTできた。
(ただし、8.0で禁止された動作のように途中で lower_case_table_names が変わってるのでテーブル名の大文字小文字を迂闊に変えるとハマる可能性はある)
同じく8.0。
PS > bin\mysqld --initialize-insecure
PS > bin\mysqld --console
2022-10-10T06:01:51.655936Z 0 [System] [MY-010116] [Server] C:\Users\yoku0\Downloads\mysql-8.0.30-winx64\mysql-8.0.30-winx64\bin\mysqld.exe (mysqld 8.0.30) starting as process 7024
2022-10-10T06:01:51.668243Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-10T06:01:51.944691Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-10T06:01:52.288537Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-10-10T06:01:52.288941Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-10-10T06:01:52.318492Z 0 [System] [MY-010931] [Server] C:\Users\yoku0\Downloads\mysql-8.0.30-winx64\mysql-8.0.30-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.30' socket: '' port: 3306 MySQL Community Server - GPL.
2022-10-10T06:01:52.318498Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
PS > bin\mysql -uroot
mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE TABLE d1.t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO d1.t1 VALUES (1, '8.0.30 Init on Windows');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM d1.t1;
+-----+------------------------+
| num | val |
+-----+------------------------+
| 1 | 8.0.30 Init on Windows |
+-----+------------------------+
1 row in set (0.00 sec)
mysql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)
8.0.30はさっきと同じように—no-defaultsでdatadirを食わせるだけだと上がらない。
$ scp -r data conoha:/tmp/8030
$ /usr/mysql/8.0.30/bin/mysqld --no-defaults --datadir=/tmp/8030
2022-10-10T06:06:10.864755Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2022-10-10T06:06:10.864766Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2022-10-10T06:06:11.113624Z 0 [System] [MY-010116] [Server] /usr/mysql/8.0.30/bin/mysqld (mysqld 8.0.30) starting as process 17699
2022-10-10T06:06:11.139472Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-10T06:06:11.437327Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-10T06:06:11.468902Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('0') and data dictionary ('1').
2022-10-10T06:06:11.469136Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-10-10T06:06:11.469160Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-10-10T06:06:11.999160Z 0 [System] [MY-010910] [Server] /usr/mysql/8.0.30/bin/mysqld: Shutdown complete (mysqld 8.0.30) Source distribution.
8.0からは lower_case_table_names が
It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized.
で、MySQL on Windowsのデフォルトは lower_case_table_names=1 , MySQL on Linuxのデフォルトは lower_case_table_names=0 なのでエラる。
initializeした時と同じ値に合わせてやれば良いので（ただしもちろんLinuxなのにテーブル名の大文字小文字を区別しなくなるという弊害(?)は起こる。俺はどんな環境でも lower_case_table_names=1 推奨派なので全く困らないけれども）
$ /usr/mysql/8.0.30/bin/mysqld --no-defaults --datadir=/tmp/8030 --lower_case_table_names=1
2022-10-10T06:10:29.738697Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2022-10-10T06:10:29.738719Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2022-10-10T06:10:29.986666Z 0 [System] [MY-010116] [Server] /usr/mysql/8.0.30/bin/mysqld (mysqld 8.0.30) starting as process 17785
2022-10-10T06:10:29.997767Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-10T06:10:30.222849Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysqld: File '.\binlog.000001' not found (OS errno 2 - No such file or directory)
2022-10-10T06:10:30.520954Z 0 [ERROR] [MY-010958] [Server] Could not open log file.
2022-10-10T06:10:30.520977Z 0 [ERROR] [MY-010041] [Server] Can't init tc log
2022-10-10T06:10:30.520987Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-10-10T06:10:32.001836Z 0 [System] [MY-010910] [Server] /usr/mysql/8.0.30/bin/mysqld: Shutdown complete (mysqld 8.0.30) Source distribution.
今度はバイナリログが見つからないらしい。
これの正体は binlog.index ファイルにWindowsファイルパス形式でバイナリログの在り処を記録しているからで
$ cat /tmp/8030/binlog.index
.\binlog.000001
binlog.index ごとバイナリログを全部消しちゃうか、 binlog.index を書き換えて ./ でLinuxファイルパス形式に書き換えてやれば良い。
$ vim /tmp/8030/binlog.index
$ cat /tmp/8030/binlog.index
./binlog.000001
三度目の正直で起動した。
$ /usr/mysql/8.0.30/bin/mysqld --no-defaults --datadir=/tmp/8030 --lower_case_table_names=1
2022-10-10T06:30:23.396869Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2022-10-10T06:30:23.396885Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2022-10-10T06:30:23.649027Z 0 [System] [MY-010116] [Server] /usr/mysql/8.0.30/bin/mysqld (mysqld 8.0.30) starting as process 18175
2022-10-10T06:30:23.660111Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-10T06:30:23.910902Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-10T06:30:24.223477Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-10-10T06:30:24.223575Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-10-10T06:30:24.225695Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp/8030' in the path is accessible to all OS users. Consider choosing a different directory.
2022-10-10T06:30:24.254458Z 0 [System] [MY-010931] [Server] /usr/mysql/8.0.30/bin/mysqld: ready for connections. Version: '8.0.30' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
2022-10-10T06:30:24.254470Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
$ /usr/mysql/8.0.30/bin/mysql -uroot
mysql> SELECT * FROM d1.t1;
+-----+------------------------+
| num | val |
+-----+------------------------+
| 1 | 8.0.30 Init on Windows |
+-----+------------------------+
1 row in set (0.01 sec)
逆も似たようなもんだと思うけれど、俺にはWindowsとLinuxを跨いでdatadirの移動はやったこともないしやる予定もないと思う。
