Securing MySQL on Windows.
Don't assume the installation wizard worked
Whether the installation wizard has a problem, or I perhaps don't always use it correctly,
I think it is prudent to verify some of the work it was supposed to do:
#1.) if you told it to install MySQL as a Windows service, and it says installation failed:
Verify if it did. Open 'services.msc' and look for it in the list of installed services.
#2.) If you told it to set a root password, did it do so?
If you can log in to mysql with 'bin\mysql -uroot', then root has no password.
#3.) If you told it to not allow remote access, I would not be too eager to trust that.
Cleaning up after the installation wizard and/or yourself.
By default, up to four accounts may have been created, none of which has a password:
- two root accounts:
--- one for local access ('root'@'localhost'), and
--- one for remote access ('root'@'%'), and
- two anonymous accounts (with no username - 2 single-quotes with no space between):
--- ''@'localhost' for local access and
--- ''@'%' for remote access
You have to assign passwords to some of them, and delete the others.
But first, you must be sure that the server is up and running
First, verify that the server is running
This step saves you from having to figure out why you can't get in. In a command shell, type:
net start | find /i "mysql"
If the MySQL server is running, you should see the word "MySQL" right below, when you hit "Enter".
If you don't see it, then you need to start up MySQL server.
Plan B (other ways to start up the MySQL server, so you can log in.)
If MySQL was not automatically installed as a Windows service,
you can still do so manually at any time, using a command similar to this:
mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
(Creates a Windows service with the name "MySQLXY", as seen in 'services.msc')
You don't have to install MySQL as a service. You can start it manually whenever you want:
mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
One way or another, the MySQL server must be up and running before you can log in to MySQL.
Keep in mind that the server, once it starts up, could be accepting connections
from anonymous users anywhere on the internet.
This is the phase of maximum exposure, so you might want to disconnect from the internet until you're done.
Choose the root password wisely
Using only alphanumeric characters is not as secure as including "special characters".
But there is a problem.
If you plan to put the password inside a connection string stored in "web.config", then any "special characters" can prevent the password from being correctly parsed. It may be best to use only alphanumeric characters in the password, while you are still developing your application, and hold off on using "special characters" in the passsword until you are done testing.
These are some of the error messages you can expect, if your password is "too secure":
"Format of the initialization string does not conform to specification starting at index 49."
"Access denied for user 'root'@'localhost' (using password: YES)"
Top priority steps - set a password for accounts to keep, delete the others.
Set the root password:
UPDATE mysql.user SET Password=PASSWORD('buzzword') WHERE user='root';
To cause the changes to take effect immediately, without having to log out first and/or restart the server, follow up with this command:
FLUSH PRIVILEGES;
Check if the user table contains an account with user and host values of 'root' and '127.0.0.1':
mysql> select host, user from user where user = 'root';
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| localhost | root |
+-----------+------+
2 rows in set (0.00 sec)
If it does, use an additional SET PASSWORD statement to set that account's password:
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('buzzword');
Delete the remote root acount:
DELETE FROM user WHERE user = 'root' AND host = '%';
FLUSH PRIVILEGES;
Delete the two (one remote, and one local) anonymous accounts:
DELETE FROM user WHERE user = '';
FLUSH PRIVILEGES;
Verifying that those top priority steps took effect.
Verify that you do NOT get in with:
mysql> bin\mysql
(if you do, the two anonymous accounts were not deleted)
Verify that you do NOT get in with:
mysql> bin\mysql -uroot
(if you do, the root password was not set)
Verify that you DO get in with:
mysql> bin\mysql -uroot -p
(after typing the correct password at the prompt that follows)
To avoid the prompt for the password, include it, in double-quotes.
Do not leave a blank between "-p" and the password.
If you do, the meaning changes to: prompt me for the password, and use the database with a name of (what you intended to be the) password.
Securing MySQL after setup - additional steps
1.) Edit the "my.ini" file, under the section "[mysqld(-nt)]", add a line:
bind-address=127.0.0.1
(to make sure MySQL only listens to local connections.)
2.) "hosts" file should have a line "127.0.0.1 localhost".
To verify this, open a command shell and paste in the following line:
type %windir%\system32\drivers\etc\hosts
3.) create a domain account with limited rights and edit the mysql service (services.msc) to use it (instead of the local system account, which has more rights than MySQL needs).
If you are securing a MySQL server that came with a wampserver,
the name of the windows service is 'wampmysqld', not 'MySQL'.
Removing windows services.
The best way to remove unwanted services is to simply uninstall the underlying application.
MySQL - other useful tips
If you run sql from batch files and redirect the output to a file,
you can still get the output in interactive format with mysql -t.
To echo the commands themselves to the output: mysql -vvv.
The following example assumes you don't want to log in first,
but a password has been set, so you must include the "-p" parameter.
You want to use a database named 'test' and you want both the output and
any error messages to go to an output file named 'MySQLOutput1.txt'
bin\mysql -tvvv -uroot -p test -e "source MyScriptsFolder/myScript.sql" 1> MySQLOutput1.txt 2>>&1
An example of such an output file:
--------------
SELECT * FROM `cars`
--------------
+----+--------+----------+------+---------------------+
| id | Make | Model | Year | Date & Time Entered |
+----+--------+----------+------+---------------------+
| 1 | Isuzu | Dunno | 1997 | 2010-01-21 21:07:27 |
| 2 | Toyota | Camry | 1998 | 2010-01-21 21:10:55 |
| 3 | Honda | Accord | 1986 | 2010-01-21 21:11:11 |
| 7 | Audi | Whatever | 2001 | 2010-01-21 21:23:57 |
| 8 | Audi | Whatever | 2001 | 2010-01-21 21:24:49 |
| 30 | Mazda | Izusu | 2005 | 2010-01-22 00:15:28 |
| 31 | Isuzu | Brendan | 2020 | 2010-01-23 13:20:42 |
+----+--------+----------+------+---------------------+