MySQL Configuration
Secure your installation of MySQL, it’s recommended to disable root remote access, turn off anonymous users and delete the test database. Choose either Low or Medium security when installing the database. I choose medium for this tutorial and then modified the settings to the standards I desire, which did not include special characters.
Execute the following command on your server.
| sudo mysql_secure_installation |
Allow external access to the database by changing the mysql bind address to 0.0.0.0 and save the file.
| sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf |
Execute the command to restart your mysql service.
| sudo systemctl restart mysql |
Log into MySQL at the command line by typing the following and hitting enter.
| sudo mysql -u root –p |
At the MySQL prompt, run the following commands. Make sure to change ‘YourPasswordHere’ to a properly secure password.
| CREATE USER ‘authserver’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘YourPasswordHere’; CREATE USER ‘gameserver’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘YourPasswordHere’; CREATE USER ‘websiteuser’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘YourPasswordHere’; CREATE USER ‘websiteuser’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘YourPasswordHere’; CREATE USER ‘gameadmin’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘YourPasswordHere’; CREATE USER ‘gamechecker’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘YourPasswordHere’; FLUSH PRIVILEGES; CREATE DATABASE atavism; CREATE DATABASE admin; CREATE DATABASE master; CREATE DATABASE world_content; GRANT INDEX, DELETE, INSERT, SELECT, UPDATE ON master.* TO ‘authserver’@‘localhost’; GRANT ALL PRIVILEGES ON atavism.* TO ‘gameserver’@‘localhost’; GRANT ALL PRIVILEGES ON admin.* TO ‘gameserver’@‘localhost’; GRANT ALL PRIVILEGES ON master.* TO ‘gameserver’@‘localhost’; GRANT ALL PRIVILEGES ON world_content.* TO ‘gameserver’@‘localhost’; GRANT INDEX, INSERT, SELECT, UPDATE ON atavism.* TO ‘websiteuser’@’%’; GRANT INDEX, INSERT, SELECT, UPDATE ON admin.* TO ‘websiteuser’@’%’; GRANT INDEX, INSERT, SELECT, UPDATE ON master.* TO ‘websiteuser’@’%’; GRANT INDEX, INSERT, SELECT, UPDATE ON world_content.* TO ‘websiteuser’@’%’; GRANT INSERT, SELECT, UPDATE ON admin.server_status TO ‘gamechecker’@‘localhost’; GRANT SELECT, DELETE ON admin.server TO ‘gamechecker’@‘localhost’; GRANT ALL PRIVILEGES ON *.* TO ‘gameadmin’@’localhost’;
|
Note:
Certain password characters are allowed, but present an issue on login. If your user is denied repeatedly despite the password being correct, this is likely the issue. Change the password and attempt to create the user again.