8.5 Running MySQL
Here are some basics for configuring and running MySQL. For details try the mysql documentation at www.mysql.com/doc/.
8.5.1 Configuring the MySQL daemon
8.5.2 Setting up a simple database using MySQL
8.5.3 Querying the MySQL database
8.5.1 Configuring the MySQL daemon
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.
8.5.2 Setting up a simple database using MySQL
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:
* License
