So you just got MySQL version three-point-something installed, but still need to do the initial setup to get it running and locked down. Here's the drill.
First of all, do you need to run MySQL over a network? If the only things accessing the database are on the same box as the MySQL daemon, then you probably don't need the networking functionality at all, and can increase both security and performance by using a local UNIX domain socket instead of TCP/IP. In your my.cnf file:
[mysqld] ... socket=/path/to/mysql.sock skip-networking
If you have something that runs locally but for some silly reason requires a TCP connection to MySQL anyway, you can at least keep it bound to the loopback address, rather than exposing it to the Ethernet interface:
[mysqld] ... bind-address=127.0.0.1
With either of the above configurations, use "localhost" as the hostname when accessing the database, and set up MySQL users with access only from "@localhost". If you do have to leave MySQL open to the network, at least put in some firewall rules to restrict access as much as possible.
If it hasn't been done already, you need to initialize the main mysql database. This couldn't be easier, just run "mysql_install_db". At this point, MySQL can now be started up.
MySQL starts with a blank root password. To set it:
linmiri% mysqladmin -u root password <rootpasswd>
...or:
linmiri% mysql -u root -h localhost -p
Enter password: [ENTER]
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD('rootpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;
There are also default anonymous accounts, and possibly a root account for "localhost.localdomain" in addition to the standard root@localhost. Get rid of these:
linmiri% mysql -u root -h localhost -p Enter password: <password> mysql> USE mysql; mysql> DELETE FROM user WHERE User=''; mysql> DELETE FROM db WHERE User=''; mysql> DELETE FROM user WHERE User='root' AND Host<>'localhost'; mysql> FLUSH PRIVILEGES;
Note for Red Hat/Fedora Core users:
The startup script /etc/rc.d/init.d/mysqld actually uses the anonymous account when starting the MySQL server to do a "ping" and verify that it has actually started. Removing the anonymous account will cause this check to fail, even though the DB may be up and running fine. To remedy this, edit the script, and make the following edit:
# If you've removed anonymous users, this line must be changed to # use a user that is allowed to ping mysqld. #ping="/usr/bin/mysqladmin -uUNKNOWN_MYSQL_USER ping" ping="pidof mysqld"
That will simply check for a running mysqld process. It will be up to you to verify that it is functioning correctly. You could also add another unprivileged user for this purpose, and simply change the user in the mysqladmin line above.
At this point, you should be able to do a "SELECT * FROM user;" and see only one result: root at localhost, with an encrypted password string. Now you can start adding databases and users.
To create a database named "mydb", and a database user "dbuser" with access to "mydb":
linmiri% mysql -u root -h localhost -p
Enter password: <password>
mysql> use mysql;
mysql> create database mydb;
mysql> GRANT ALL PRIVILEGES ON mydb.* to dbuser@localhost
identified by '<passwd>';
"ALL" can be replaced by a number of levels of access, such as USAGE, SELECT, INSERT, UPDATE, and DELETE. RTFM for more on all that.