2.8. Postinstallation

After you've finished installing MySQL on your server, you should perform a few tasks before allowing others to begin using the service. You may want to configure the server differently by making changes to the configuration file. At a minimum, you should change the password for the root user and add some nonadministrative users. MySQL is initially set up with anonymous users. You should delete them. This section will briefly explain these tasks.

Although the MySQL developers have set the server daemon to the recommended configuration, you may want to set the daemon differently. For instance, you may want to turn on error logging. To do this, you will need to edit the main configuration file for MySQL. On Unix systems, this file is /etc/my.cnf. On Windows systems, the main configuration file is usually either c:\windows\my.ini or c:\my.cnf. The configuration file is a simple text file that you can edit with a plain-text editor, not a word processor. The configuration file is organized into sections or groups under a heading name contained in square brackets. For instance, settings for the server daemon mysqld are listed under the group heading [mysqld]. Under this heading you could add something like log = /var/log/mysql to enable logging and to set the directory for the log files to the one given. You can list many options in the file for a particular group. For a complete listing and explanation of these options, see Chapter 10.

You can change the password for the root user in MySQL in a few ways. One simple way is to log in to MySQL through the mysql client by entering the following from the command line:

mysql -u root -p

On a Windows system, you may have to add the path c:\mysql\bin\ to the beginning of this line, if you haven't added it to your command path. After successfully entering the command, you will be prompted for the root user's password. This is not the operating system's root user, but the root user for MySQL. Initially there is no password, so press Enter to leave it blank. If everything was installed properly and if the mysqld daemon is running, you should get a prompt like this:

mysql>

This is the prompt for the mysql client interface or MySQL monitor. To change the root user's password, enter the following at this prompt:

SET PASSWORD FOR root@localhost=PASSWORD('password');  SET PASSWORD FOR root@host=PASSWORD('password');

Replace the word password in quotes with the password that you want to use for root. There are two lines here, because typically there are two entries for the root user: one with the localhost and another with the system's hostname. On Windows the wildcard % is used instead of this second account, to allow root login from any host. After you change these passwords, you need to log out of the mysql client and log back in with the new password.

The next security measure to take regarding MySQL users is to delete the anonymous users. You can do this by entering the following from the mysql client:

DELETE FROM mysql.user WHERE User=''; DELETE FROM mysql.db WHERE User=''; FLUSH PRIVILEGES;

The first two commands delete any anonymous users from the user and db tables in the database called mysql that's where the privileges or grant tables are stored. The last line resets the server privileges to reflect these changes.

The next step regarding users is to set up at least one user for general use. It's best not to use the root user for general database management. When you set up a new user, you should consider which privileges to allow the user. If you want to set up a user that can only view data, you should enter something like the following from the mysql client:

GRANT SELECT ON *.* TO tina IDENTIFIED BY 'muller';

In this line, the user is tina and her password is muller. If you want to give a user more than viewing privileges, you should add additional privileges to the SELECT command, separated by commas. To give a user all privileges, replace SELECT with ALL. Here's another example using the ALL flag:

GRANT ALL ON db1.* TO tina IDENTIFIED BY 'muller';

In this example, the user tina has all basic privileges, but only for the db1 database. This statement adds the user tina to the table user in the mysql database, if there is already a row for her in it, but with no privileges. It will also add a row to the db table in the mysql database indicating that tina has all privileges for the db1 database. See the explanation of GRANT in Chapter 4 for more options.

If you have any existing MySQL datafiles from another system, you can copy the actual files to the directory where MySQL data is stored on your server. Just be sure to change the ownership of the files to the mysql user and mysql group with the chown system command after you copy them to the appropriate directory. If your existing datafiles are dump files created by the mysqldump utility, see the explanation regarding that utility in Chapter 11. If your data needs to be converted from a text file, see the explanation of the LOAD DATA INFILE statement in Chapter 4. You probably should also check the online documentation (http://dev.mysql.com/doc/mysql/en/Upgrade.html) on upgrading from a previous version to a current one, especially if you are migrating across major versions.

With the MySQL installation software downloaded and installed and all of the binary files and data in their place and properly set, MySQL is now ready to use.