search

CarbazochromeT / drug_app

drug_app
0 stars 0 forks source link

SQLite3からMySQLに移行できない #9

Closed CarbazochromeT closed 1 year ago

CarbazochromeT commented 1 year ago

お世話になっております。 現在、SQLite3からMySQLへの移行を試みていますが、エラーが発生してしまいます。 解決策に繋がりそうなアドバイスをいただけましたら幸いです。

sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /etc/my.cnf
sudo rm -rf /opt/homebrew/bin/mysql

brew uninstall mysql

MySQL8.1をインストールするにあたり、他のMySQLのファイルを削除してから、インストールしています。

$ brew install mysql                 
==> Downloading https://formulae.brew.
############################### 100.0%
==> Downloading https://formulae.brew.
############################### 100.0%
==> Fetching mysql
==> Downloading https://ghcr.io/v2/hom
Already downloaded: /Users/flare_home/Library/Caches/Homebrew/downloads/92c3b013bf98bd060e2779550b33750f6fb219f18d529b393b551ff8245504c4--mysql-8.1.0.bottle_manifest.json
==> Downloading https://ghcr.io/v2/hom
Already downloaded: /Users/flare_home/Library/Caches/Homebrew/downloads/f852745c89c56ecde7a55c7b499ebb81910ab9eec82d53e0227d6a85952c6c4e--mysql--8.1.0.arm64_ventura.bottle.tar.gz
==> Pouring mysql--8.1.0.arm64_ventura
==> /opt/homebrew/Cellar/mysql/8.1.0/b
Last 15 lines from /Users/flare_home/Library/Logs/Homebrew/mysql/post_install.01.mysqld:

/opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld
--initialize-insecure
--user=anago
--basedir=/opt/homebrew/Cellar/mysql/8.1.0
--datadir=/opt/homebrew/var/mysql
--tmpdir=/tmp

2023-09-02T13:19:40.141354Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2023-09-02T13:19:40.157405Z 0 [System] [MY-013169] [Server] /opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld (mysqld 8.1.0) initializing of server in progress as process 57169
2023-09-02T13:19:40.162745Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2023-09-02T13:19:40.162759Z 0 [ERROR] [MY-013236] [Server] The designated data directory /opt/homebrew/var/mysql/ is unusable. You can remove all files that the server added to it.
2023-09-02T13:19:40.162884Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-09-02T13:19:40.165092Z 0 [System] [MY-010910] [Server] /opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld: Shutdown complete (mysqld 8.1.0)  Homebrew.
2023-09-02T13:19:40.165668Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.
Warning: The post-install step did not complete successfully
You can try again using:
  brew postinstall mysql
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -u root

To restart mysql after an upgrade:
  brew services restart mysql
Or, if you don't want/need a background service you can just run:
  /opt/homebrew/opt/mysql/bin/mysqld_safe --datadir\=/opt/homebrew/var/mysql
==> Summary
🍺  /opt/homebrew/Cellar/mysql/8.1.0: 325 files, 308.4MB
==> Running `brew cleanup mysql`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).

$ which mysql                        
/opt/homebrew/bin/mysql

インストールは問題なくできるのですが、mysqlに入ろうとすると、接続ができなくなってしまいます。

$  mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (38)

そこで、brew postinstall mysqlというコマンドを打つよう言われますが、またbrew postinstall mysqlを打つよう言われてしまいます。

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:administrate x [22:20:30] 
$ brew postinstall mysql
==> Postinstalling mysql
==> /opt/homebrew/Cellar/mysql/8.1.0/b
Last 15 lines from /Users/flare_home/Library/Logs/Homebrew/mysql/post_install.01.mysqld:

/opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld
--initialize-insecure
--user=anago
--basedir=/opt/homebrew/Cellar/mysql/8.1.0
--datadir=/opt/homebrew/var/mysql
--tmpdir=/tmp

2023-09-02T13:21:23.237214Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2023-09-02T13:21:23.242387Z 0 [System] [MY-013169] [Server] /opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld (mysqld 8.1.0) initializing of server in progress as process 58858
2023-09-02T13:21:23.245531Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2023-09-02T13:21:23.245543Z 0 [ERROR] [MY-013236] [Server] The designated data directory /opt/homebrew/var/mysql/ is unusable. You can remove all files that the server added to it.
2023-09-02T13:21:23.245700Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-09-02T13:21:23.246174Z 0 [System] [MY-010910] [Server] /opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld: Shutdown complete (mysqld 8.1.0)  Homebrew.
2023-09-02T13:21:23.246646Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.
Warning: The post-install step did not complete successfully
You can try again using:
  brew postinstall mysql
$ brew doctor           
Your system is ready to brew.

$ brew info mysql
==> mysql: stable 8.1.0 (bottled)
Open source relational database management system
https://dev.mysql.com/doc/refman/8.0/en/
Conflicts with:
  mariadb (because mysql, mariadb, and percona install the same binaries)
  percona-server (because mysql, mariadb, and percona install the same binaries)
/opt/homebrew/Cellar/mysql/8.1.0 (325 files, 308.4MB) *
  Poured from bottle using the formulae.brew.sh API on 2023-09-02 at 22:25:32
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/m/mysql.rb
License: GPL-2.0-only with Universal-FOSS-exception-1.0
==> Dependencies
Build: bison ✘, cmake ✘, pkg-config ✔
Required: icu4c ✔, libevent ✔, libfido2 ✔, lz4 ✔, openssl@3 ✔, protobuf@21 ✔, zlib ✔, zstd ✔
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -u root

To restart mysql after an upgrade:
  brew services restart mysql
Or, if you don't want/need a background service you can just run:
  /opt/homebrew/opt/mysql/bin/mysqld_safe --datadir\=/opt/homebrew/var/mysql
==> Analytics
install: 49,619 (30 days), 148,630 (90 days), 249,039 (365 days)
install-on-request: 49,438 (30 days), 148,025 (90 days), 248,048 (365 days)
build-error: 646 (30 days)

$ mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (38)

この後にMacを再起動し、MySQLの再起動も試みましたが、できませんでした

$ brew services restart mysql
Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (labe
==> Successfully started `mysql` (labe

$ mysql.server restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL
./opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld_safe: line 653: /opt/homebrew/var/mysql/flarenoMacbook-Air.local.err: Permission denied
Logging to '/opt/homebrew/var/mysql/flarenoMacbook-Air.local.err'.
/opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld_safe: line 144: /opt/homebrew/var/mysql/flarenoMacbook-Air.local.err: Permission denied
/opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld_safe: line 199: /opt/homebrew/var/mysql/flarenoMacbook-Air.local.err: Permission denied
/opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld_safe: line 916: /opt/homebrew/var/mysql/flarenoMacbook-Air.local.err: Permission denied
/opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld_safe: line 144: /opt/homebrew/var/mysql/flarenoMacbook-Air.local.err: Permission denied
 ERROR! The server quit without updating PID file (/opt/homebrew/var/mysql/flarenoMacbook-Air.local.pid).
# anago @ flarenoMacbook-Air in ~ [8:00:03] C:1
$ sw_vers                    
ProductName:            macOS
ProductVersion:         13.4.1
BuildVersion:           22F82

# anago @ flarenoMacbook-Air in ~ [8:03:13] 
$ mysql --version
mysql  Ver 8.1.0 for macos13.3 on arm64 (Homebrew)

# anago @ flarenoMacbook-Air in ~ [8:03:20] 
$ uname -n
flarenoMacbook-Air.local

/tmp/mysql.sock との接続が悪いため、再び作ってから接続しようとすると拒否されてしまいます。

# anago @ flarenoMacbook-Air in ~ [8:14:44] C:1
$ sudo touch /tmp/mysql.sock                                     

# anago @ flarenoMacbook-Air in ~ [8:17:14] 
$ mysql -u root -p                                         
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (38)

PIDファイルを作ってみても、そのあとのMySQLとの接続でエラーが発生してしまう。

# anago @ flarenoMacbook-Air in ~ [8:14:10] C:1
$ sudo touch /opt/homebrew/var/mysql/flarenoMacbook-Air.local.pid        

# anago @ flarenoMacbook-Air in ~ [8:14:39] 
$ sudo mysql.server start                                  
Starting MySQL
. ERROR! The server quit without updating PID file (/opt/homebrew/var/mysql/flarenoMacbook-Air.local.pid).

MySQLの既に動いているプロセスを切ろうとしましたが、これもうまくいきません。

# anago @ flarenoMacbook-Air in ~ [8:04:28] C:1
$ ps ax | grep mysql
56423 s015  R+     0:00.00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn --exclude-dir=.idea --exclude-dir=.tox mysql

# anago @ flarenoMacbook-Air in ~ [8:07:37] 
$ mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (38)

# anago @ flarenoMacbook-Air in ~ [8:08:09] C:1
$ kill -TERM 56423
kill: kill 56423 failed: no such process

# anago @ flarenoMacbook-Air in ~ [8:08:49] C:1
$ kill -TERM s015 
kill: illegal pid: s015

Homebrewを用いたMySQLとアンインストールとインストールを繰り返しておりますが、この調子です。 解決方法がわからず詰まってしまっておりますので、確認お願いいたします。

参考文献

https://dezanari.com/mysql-terminal-login-denied/ https://www.naka-style-blog.com/mysql-log-in-erorr/ https://qiita.com/sugeta10/items/5573a12467a0dddf9f8e

kenchasonakai commented 1 year ago

MySQLを削除する時はbrew uninstallでは消えないフォルダが存在するので M1であれば下記記事を参考に消してみたりするといいかもです

$ rm -rf /opt/homebrew/var/mysql
$ rm -rf /opt/homebrew/etc/my.conf
$ rm -rf /opt/homebrew/etc/my.conf.default

https://www.fuwamaki.com/article/398

CarbazochromeT commented 1 year ago

上記のコマンドを打った後、アンインストール、インストールを行ったら接続できました！ありがとうございました！