🛠️ Accessing MySQL on SME Server

🔍 Overview

SME Server includes MySQL, but access is restricted by default. I installed a SmE server on an older machine and needed MySQL to communicate with the rest of my network. This guide shows how to configure secure access.

🧰 Procedure

1. Login as root

su -

2. Access MySQL shell

mysql -u root

3. Configure user access

a. Full access for a user (less secure)

GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

b. Limit privileges (more secure)

GRANT SELECT, UPDATE, INSERT, DELETE ON databasename.* TO 'username'@'hostname' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

c. Restrict access to localhost

GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

d. Restrict access to a specific IP

GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'10.0.0.25' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Note: No system user needs to match the MySQL user.

4. Confirm privileges

SHOW GRANTS FOR 'username'@'hostname';

5. Exit MySQL

quit;

6. Open Port 3306

MySQL uses port 3306 by default. Ensure this port is open to allow remote connections.

📝 Notes

• Always use strong passwords.
• Limit user privileges where possible.
• Avoid granting full root-level access remotely unless absolutely required.
• If using phpMyAdmin, you can manage users via the “Users” link on the main page.