Configuring MySQL
| only for RuBoard - do not distribute or recompile |
This section describes how to create a new database and set up a MySQL user account that can access it. You could use an existing database and user, of course, but I recommend that you set up new ones specifically for trying out the applications developed in this book. You ll find it easier to keep things straight if they re not mixed up with a database and account you re already using for other purposes. Also, if while you re learning you accidentally expose the username and password used for the example database, that won t put your other databases at risk.
For example purposes, I m going to assume that you re working on the host www.snake.net and that your MySQL server and Apache server are both running locally on that same host. This means that when you use MySQL, you ll connect to the server by specifying a host name of localhost. I ll also assume that we re going to connect as the user webdev with a password of webdevpass, and that the database is named webdb. If you want to use a different username, password, or database name, you should make the appropriate substitutions in the examples.
Database access must be set up by an existing MySQL user, such as root, who has the ability to grant privileges. To do this for the webdev user, connect to the MySQL server using the mysql program and issue the appropriate GRANT statement. When asked to enter your password, use your MySQL root password.
% mysql -h localhost -p -u root Enter password: ****** mysql> GRANT ALL ON webdb.* TO webdev@localhost IDENTIFIED BY "webdevpass"; mysql> QUIT
On the mysql command, the -h option specifies the host where the MySQL server is running, -p tells mysql to prompt for a password, and -u specifies the MySQL user to connect as. If the webdev user account doesn t already exist, the GRANT statement creates it, grants it all privileges on the webdb database, and sets its password. If the webdev account does exist, the GRANT statement simply grants the privileges and sets the password. In the latter case, you may want to grant privileges without changing the webdev user s current password; you can do that by leaving out the IDENTIFIED BY clause:
mysql> GRANT ALL ON webdb.* TO webdev@localhost;
The GRANT statement sets up privileges for using the webdb database, but doesn t create it. You should do that now. Use mysql to connect to the server as webdev (the user to whom you just granted privileges), and then issue a CREATE DATABASE statement. When asked to enter your password, use your webdev password.
% mysql -h localhost -p -u webdev Enter password: webdevpass mysql> CREATE DATABASE webdb; Query OK, 1 row affected (0.01 sec) mysql> QUIT
If you want to run a few sample queries with your new database, try the ones presented here. You should see output similar to what s shown. The mysql command shows how you can connect and specify the database name on the command line to make it the default database for your session.When asked to enter your password, use your webdev password.
% mysql -h localhost -p -u webdev webdb Enter password: webdevpass mysql> CREATE TABLE test (i INT); Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; +-----------------+ | Tables_in_webdb | +-----------------+ | test | +-----------------+ 1 row in set (0.00 sec) mysql> DROP TABLE test; Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES; Empty set (0.00 sec) mysql> QUIT
If you want to use a database server that s located on a different host, the preceding instructions must be modified, both for creating the webdev account and for connecting as that user. Let s assume that the MySQL server is running on the host db.snake.net. The privileges will need to be set up on that host, but because you ll be connecting to db.snake.net from www.snake.net, the privileges need to be given to webdev@www.snake.net rather than to webdev@localhost. (In other words, you want to associate the privileges with the host from which you ll be connecting to the MySQL server, not the host where the server runs.) To do this, the MySQL root user should run mysql on db.snake.net and issue a GRANT statement like this. When asked to enter your password, use your MySQL root password.
% mysql -h localhost -p -u root Enter password: ****** mysql> GRANT ALL ON webdb.* TO webdev@www.snake.net -> IDENTIFIED BY "webdevpass"; mysql> QUIT
Note that the mysql command specifies a host of localhost, even though it s being run on db.snake.net, because the server is running locally relative to the host on which mysql is invoked. After the account for webdev@www.snake.net has been created, you should be able to use the webdb database by connecting as webdev from www.snake.net to the server on db.snake.net. To do so, use a mysql command like this:
% mysql -h db.snake.net -p -u webdev webdb Enter password: webdevpass
If you have problems setting up a user account or creating a database, see the MySQL Reference Manual or your MySQL administrator.
| only for RuBoard - do not distribute or recompile |
