O'Reilly logo

Learning PHP and MySQL by Jon A. Phillips, Michele E. Davis

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Managing the Database

Now that you're connected to the database, you can create users, databases, and tables. You may not need to create a database or user account if you're using a MySQL server in a hosted environment, and if they supplied you with a username and a database name.

Creating Users

To create users above and beyond the default privileged root user, issue the grant command. The grant command uses this syntax:

GRANT PRIVILEGES ON DATABASE.OBJECTS TO'USER'@'HOST' IDENTIFIED BY 'PASSWORD';

For example:

GRANT ALL PRIVILEGES ON *.* TO 'michele'@'localhost' IDENTIFIED BY 'secret';

This creates the user michele who can access anything locally. To change to the michele user, at the mysql command prompt, type:

exit

Then start MySQL from the command line with the new username and password. The syntax for specifying the username and password when starting MySQL is:

mysql -h hostname -u username -ppassword

If you don't want users to access tables other than their own, replace * with the name of the user's database, like this:

GRANT ALL PRIVILEGES ON `store`.* TO 'michele'@'localhost' IDENTIFIED BY 'secret';

You'll need to run the above line as root or as someone with permission. In the above code, the word store correlates to the database name to which privileges are assigned, which you'll create in the next section.

Creating a MySQL Database

You're going to create a database called store. The create database command works like this:

CREATE DATABASE `store`;

If this works, you'll get a result ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required