Running MySQL

Here are some basics for configuring and running MySQL. For details try the mysql documentation at www.mysql.com/doc/.

Step one. Basic configuration of MySQL is in the following text file (as root):

/etc/my.cnf

MySQL comes with four sample configuration files from which to choose depending on your system and database size:

/etc/my-huge.cnf

/etc/my-large.cnf

/etc/my-medium.cnf

/etc/my-small.cnf

To do a default configuration of MySQL, select the appropriate file (I chose my-small.cnf since I don't actually have a need for a large database) and copy it to /etc/my.cnf

cp /etc/my-small.cnf /etc/my.cnf

Step two. Create the MySQL privilege database. This includes the host, user, tables_priv, columns_priv, and func tables which manage user access to your databases. To accomplish this, the MySQL installation comes with a script file that creates this database for you. It is important that you run this script file since you cannot start the MySQL daemon without the privilege database.

To run the script file call:

/usr/bin/mysql_install_db

Step three. Setup the "mysql" user, group and read/write permissions for the MySQL database. For security reasons, you should setup a user account that will be running the MySQL daemon. This is not necessary since MySQL can be run as root, but it is highly recommended. The binary installation does this for you by creating a "mysql" user and group. If no such user exists on your system, you can create one using the adduser and addgroup commands. Note, that the user running the MySQL daemon does not have to be 'mysql', but for the duration of the instructions it is assumed that it is.

To setup the read/write permissions for MySQL, you fist need to find the location of your database files. On my computer the database directory is located under /var/lib/mysql. The location of the database directory is set up in the my.cnf file. To change the default directory edit the file appropriately. Read/write permissions for the "mysql" user database can be set by changing the ownership of the database directory.

chown -R mysql /var/lib/mysql

chgrp -R mysql /var/lib/mysql

Step four. Start mysqld. There are two methods to start the MySQL daemon. First, you can start mysqld manually using the safe_mysqld script. Remember, since you are root you need pass the name of the user with which you would like to start mysqld with.

/usr/bin/safe_mysqld --user=mysql &

Alternately, configure mysqld to start during boot time. This can be done by adding the following line to the /etc/rc.local file.

/usr/share/mysql/mysql.server start

At this point you should have a running MySQL daemon.

Step one. Set the root password for MySQL. Without a root password, anyone from your localhost can log on with root privileges to your MySQL database which is probably not good if you intend to keep there anything usable.

mysqladmin --user=root password 'my_root_password'

If you would like to change a password for a specific user (including root), use the following syntax (in this case the password for the user root is changed from "my_root_password" to "my_NEW_root_password"):

mysqladmin --user=root --password='my_root_password' password 'my_NEW_root_password'

Step two. Create a database to work with. This would be the reason why you actually installed MySQL.

mysqladmin --user=root --password='my_root_password'create my_database

Step three. Log on to MySQL.

mysql --user=root --password='my_root_password'

Once you are logged in, you should get a display that looks something like this,

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5 to server version: 3.23.56

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

mysql>

The last "mysql>" represents the mysql prompt.

Step four. Create a user that can read, write and create tables in "my_database". To do this I used the GRANT command while logged on to MySQL with root privileges.

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER

-> ON my_database.* TO peter@localhost

-> IDENTIFIED BY 'peters_password';

Note, MySQL only processes the command when ended with a semicolon. Once entered, you should get an output similar to this,

Query OK, 0 rows affected (0.00 sec)

mysql>

To log out of MySQL just type:

mysql> exit

Now you can log on to MySQL with your new user account and start building your new database.

mysql --user=peter --password='peters_password' my_database

Step five. Create a table in my_database. To add a table to your database use the CREATE TABLE command. The syntax for the command is as follows:

CREATE TABLE [table_name] ([column_name] [column_type] [DEFAULT value],...)

My command looked like this:

mysql> CREATE TABLE my_table (person TEXT DEFAULT "", age INTEGER DEFAULT 0);

To alter the table once you have created it, you can use the ALTER TABLE command. To add another column to the table my_table with the name "height" of the type REAL and with a DEFAULT value of NULL, I would use the following command:

mysql> ALTER TABLE my_table ADD COLUMN (height REAL DEFAULT NULL);

Step six. Populate the table with values. Note, when you enter the values, they must be in the same order as the columns in the table. Use "\N" for a NULL value.

mysql> INSERT INTO my_table VALUES('Bill',28,184);

You can also populate the table from an ASCII text tab delimited file. To do this you must pass the option "--local-infile" to MySQL when logging in and then call the LOAD DATA command.

mysql --local-infile --user=peter --password='peters_password' my_database

mysql> LOAD DATA LOCAL INFILE "my_data_filename" INTO TABLE my_table;

There are many ways to query the MySQL database using the SELECT command. A basic syntax for SELECT would look like this:

SELECT [columns_to_display] FROM [table] WHERE [column] [LIKE] [match_sequence]

For example, using the following command I can search the "age" column and output the information in the "columns_to_display" for the people in their 20's. Note, the "*" selects all the available columns in the table.

mysql> SELECT * FROM my_table WHERE age LIKE "2%";

MySQL databases can also be queried remotely by other applications (for example MS Access). To allow access for other systems to query your database, you can use the GRANT command. To give yourself permission to access the database from multiple domains, alter the DOMAIN_I_WILL_BE_ACCESSING_FROM and run the command again.

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER

-> ON my_database.* TO peter@DOMAIN_I_WILL_BE_ACCESSING_FROM

-> IDENTIFIED BY 'peters_password';