Access denied for user 'root'@'localhost'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# MySQLを停止
sudo systemctl stop mysql

# セーフモードで起動
sudo mysqld_safe --skip-grant-tables &

# パスワードなしでログイン
mysql -u root

# パスワードをリセット（MySQL 5.7+）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

# MySQL 5.6以前
UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;

1
2
3
4

-- rootでログイン後
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

1
2
3
4
5
6
7

-- ユーザーのホスト設定を確認
SELECT User, Host FROM mysql.user;

-- 任意のホストからの接続を許可
CREATE USER 'myuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

1
2
3

-- caching_sha2_password から mysql_native_password に変更
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

1
2
3
4
5
6

-- ユーザーの権限を確認
SHOW GRANTS FOR 'myuser'@'localhost';

-- 権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

1
2
3
4
5
6

// PHP
$pdo = new PDO(
    'mysql:host=localhost;dbname=mydb',
    'myuser',
    'password'
);

1
2
3
4
5
6
7
8
9

# Python
import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='myuser',
    password='password',
    database='mydb'
)

1
2
3
4
5
6
7
8

// Node.js
const mysql = require('mysql2');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'myuser',
  password: 'password',
  database: 'mydb'
});

1
2
3
4
5
6
7
8
9

# docker-compose.yml
services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: mydb
      MYSQL_USER: myuser
      MYSQL_PASSWORD: userpassword

1
2

# コンテナ内でMySQLに接続
docker exec -it mysql-container mysql -u root -p