Difference between revisions of "MySQL"

From Wasya Wiki
Jump to: navigation, search
Line 20: Line 20:
 
  SHOW GRANTS;
 
  SHOW GRANTS;
 
  SHOW GRANTS FOR CURRENT_USER;
 
  SHOW GRANTS FOR CURRENT_USER;
 +
 +
SHOW GRANTS FOR 'jeffrey'@'localhost';
  
 
== Create a user ==
 
== Create a user ==

Revision as of 18:52, 2 May 2022


Configure External Access

Don't forget to change bind_ip in /etc/mysql/my.cfg to 0.0.0.0 to allow external connections.

Allow root to login from anywhere. From: http://stackoverflow.com/questions/11223235/mysql-root-access-from-all-hosts

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;


 CREATE USER 'bjjc_prod_user'@'%' IDENTIFIED BY '<some-pass>';
 GRANT ALL PRIVILEGES ON bjjc_wordpress.* TO 'bjjc_prod_user'@'%' WITH GRANT OPTION;
 FLUSH PRIVILEGES;

Show privileges of a user

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR 'jeffrey'@'localhost';

Create a user

# CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password'; ## may or may not work with php ?
CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT <PRIVILEGE>, <PRIVILEGE> ON database.table TO 'username'@'host';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Develop

dump/restore

mysql -u username -p database_name < file.sql