MySQL 小技巧

新增user

讓 john@localhost 有所有權限

• For Linux

  > user mysql;
  > INSERT INTO user(host,user,password) VALUES('%','john',password('johnpwd'));
  > GRANT ALL ON *.* TO 'john'@localhost IDENTIFIED BY 'johnpwd' WITH GRANT OPTION;
  > flush privileges;
  

• For MAC

  > mysql -u root
  > CREATE USER 'john'@'localhost';
  > GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost';
  $ brew services restart mysql;
  

更大的限權

# 讓john在任何IP有所有權限
> GRANT ALL ON *.* TO john@'%' IDENTIFIED BY 'johnpwd' WITH GRANT OPTION;

查看權限

> SHOW GRANTS FOR john@localhost;
> SHOW GRANTS FOR john;

忘記密碼

$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables &
$ mysql -u root (不用密碼登入)
> use mysql;
> UPDATE user SET password=PASSWORD("new_password") WHERE user='root';
> flush privileges;
> quit
$ /etc/init.d/mysql stop
$ /etc/init.d/mysql start

參考

MySQL權限設定