Table of Contents for

MySQL in a Nutshell, 2nd Edition

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. Some versions of MySQL are 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 15.

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 the mysqld daemon is running, you should get a prompt like this:

mysql>

This is the prompt for the mysql client interface. You should set the password for all root users. To get a list of users and their hosts for the server, execute the following command from the mysql client:

SELECT User, Host FROM mysql.user;

+------+-----------------------+
| User | Host                  |
+------+-----------------------+
| root | 127.0.0.1             | 
| root | russell.dyerhouse.com | 
| root | localhost             | 
+------+-----------------------+

The results from my server are shown here. After installing, I have three user and host combinations. Although 127.0.0.1 and localhost translate to the same host, the password should be changed for both along with the one for my domain. To change the root user’s password, enter the following at this prompt:

SET PASSWORD FOR 'root'@'127.0.0.1'=PASSWORD('password');

SET PASSWORD FOR 'root'@'russell.dyerhouse.com'=PASSWORD('password');

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

Replace the word password in quotes with the password that you want to use for root. On some systems, the wildcard % is used to allow root login from any host. After you change all of the root passwords, log out of the mysql client and log back in with the new password.

On some older systems or versions of MySQL, there are anonymous users. (Newer editions don’t have them.) They will appear in the results of the SELECT statement shown earlier with blank fields for usernames. You should delete them 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 statements here 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 her. If you want to set up a user who can view only data, you should enter something like the following from the mysql client:

GRANT SELECT ON *.* TO 'kerry'@'localhost' IDENTIFIED BY 'beck123';

In this line, the user is kerry from the localhost and her password is beck123. If you want to give a user more than viewing privileges, you should add additional privileges to the SELECT privilege, 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 'kerry'@'localhost' IDENTIFIED BY 'beck123';

In this example, the user kerry has all basic privileges, but only for the db1 database and only when logged in from the localhost, not remotely. This statement adds the user kerry 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 kerry 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—but this is not a recommended method. If you do this, 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 16. If your data needs to be converted from a text file, see the explanation of the LOAD DATA INFILE statement in Chapter 6. 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. If you have existing data, always upgrade one release at a time. Don’t skip any or you may have problems with tables, passwords, or any applications you’ve developed.

With the MySQL installation software downloaded and installed and all of the binary files and data in their places and properly set, MySQL is now ready to use. For an introduction to using MySQL, see the next chapter.