Creating a MySQL Database

In the case above, the -p instructs MySQL to prompt me for the password of the account under which the database will be created. If I am logged on as root, it will prompt for the root password for MySQL, which is not necessarily the same as the password you use to login to the system under that account. The create businesscards instructs MySQL to create a datbase named businesscards.

I then want to create a userid and password with which this particular database will be accessed.

# mysql -p --user=root
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 519 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('localhost','carduser',PASSWORD('Some1Pwd'));
Query OK, 1 row affected (0.11 sec)

In the example above, I started mysql specifing --user root to use the MySQL account root and -p to be prompted for the password for that account. I then create a new user named

carduser

with a password of Some1Pwd. The password() function encrypts the supplied password.

Note: Your commands are stored in ~/.mysql_history, including any PASSWORD('xxx') calls, so that file should have permissions of 600, i.e. only the owner should have access to the file. That will normally be the case, but you may want to verify it, so that no one else on the system can potentially acces the file and see the command listing the password.

The localhost value for host specifies this user account will only be granted access from the system on which the database resides, since in this case I don't want to allow any access from a remote system.

Note: if you don't enter the use mysql command prior to the INSERT INTO command, you will receive the error message ERROR 1046: No Database Selected

After entering the above commands, use the flush privileges command to update the grant tables.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

To grant all privileges to the user named carduser for the database businesscards, the following command could be used:

mysql> GRANT ALL ON businesscards.* to 'carduser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

If you just wanted to grant read-only capability, i.e., the capability to select entries, you could use GRANT SELECT instead of GRANT ALL.