Revision as of 22:52, 17 September 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';
# better:
GRANT ALL PRIVILEGES ON microsites3_test.* TO 'tester'@'localhost';
#
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