MySQL HOW-TO
Hua Zhong
Table of Contents
1. What is this HOW-TO?
2. Why do I need to run my own mysqld?
3. How do I start the server?
4. Trouble-shooting in starting server
5. How do I connect to my server?
6. Set your database administrator password!
7. How do I shutdown the server?
8. How do I set up privileges?
9. How do I connect to the server by Perl/PHP3/C?
10. How do I connect to the server using Java?
11. I have more questions
What is this HOW-TO?
This HOW-TO is for the following:
* How to run a mysqld server of your own. You will be the root of your own mysqld with full control of it.
* How to configure it so that you can connect to the server.
* Some important issues you need to know, especially security.
This HOW-TO only aims to get you started, and is specific to the CS department. If you want to know more about SQL programming or MySQL administration, please go to http://www.mysql.com.
Back to Top
Why do I need to run my own mysqld?
Why not set up a global mysqld server so that everyone can connect to it?
There are several reasons:
* Data location: By default mysqld will save all users' databases to the "data directory". We don't want our system directory to be flooded. By running your own mysqld, you can save all of your databases in your own directory by setting 'datadir' (e.g., ~/mysql/var')
* Ease of maintenance: mysql has complex access control mechanisms. It's tedious and error-prone to set privileges for all the users in the department. We want each user to manage his own databases. This is good because you have full control.
* Security: By running your own mysqld, the server is running in your UID. The databases of different servers are completely independent. However, you still need to set the privileges correctly, or others might be able to connect to your server and potentially steal all your files.
Back to Top
How do I start the server?
MySQL has been installed on the web server. You can execute it from any Solaris machine in the department. What you need to do is to set up the initial environment for mysqld to run.
1. Add /import/http/mysql/bin to your PATH.
2. Download mysql.tar.gz to your home directory. It's only 3K. It's just the initial data, not the binaries.
3. cd to your home directory.
4. Do gzip -d mysql.tar.gz and tar xvf mysql.tar. This will initialize your data directory.
5. Verify there is a mysql/var directory in your home directory now.
6. Download the .my.cnf and put it into your home directory, and set its permission to 600.
7. Very important: open .my.cnf to change the 'your_home_directory' to your actual home directory!
8. run user_server start.
9. If you see a line like 'Starting mysqld daemon with databases from /n/opus/u/staff/huaz/mysql/var', you've started it. Check it by execute ps -u your_user_name and see whether mysqld is there. There should be some files like xxx.pid, xxx.err in the directory var. xxx is the hostname where you started the server. And also a socket file mysql.sock (ended by a '=' under ls).
10. You should not run multiple servers over the same data directory. The script prevents it by checking the pid file. For more information read the next question.
If you want to run mysqld in cron, you can add a line in your crontab file:
0 * * * * /import/http/mysql/bin/user_server start>/dev/null
This will run mysqld every hour. If there is already a server running, it simply exits. Thus you can restart mysqld automatically after a crash of the server or host.
Back to Top
Trouble-shooting in starting server
Sometimes you may get an error message like 'You have been running another server on the same datadir. Check the pid files to see on which host it is running.' when you try to start the server.
This means you have a pid file left in the datadir that implies there has already been one server running on a different host over the same datadir. You should not run multiple servers over the same data directory . The script prevents it by checking the pid file.
Each time a server starts, it creates a pid file under the data directory. For example, if you start a server on diamond, then it will create a file diamond.cs.columbia.edu.pid. Pid files should be deleted when the server is shut down. When you start up a server, the script will check the existence of any pid files. If it finds another server is running on the same host, it will try to make sure this one is running properly. If not, it will run a new server. If it finds another server is already running on a different host, it just quits.
If everything is going well, this is no problem. But sometimes the server or the host may crash so that the pid file is not cleaned up and it prevents you from starting a new server. In this case, just have a look at the pid file and logon to the corresponding host to make sure whether there _is_ a mysqld running and execute 'user_server stop' or any other method described below to shutdown the server and clear the pid file.
If you get other error messages, or you get a 'mysqld daemon ended' message immediately, it's probably because you didn't follow the instructions well. Below is a checklist if you get any error messages in the start-up:
* Have you added /import/http/mysql/bin to your PATH?
* Have you specified the correct paths in your ~/.my.cnf?
* Have you already been running a mysqld server?
* Has the port number already been used by another application? Read the next question first.
Back to Top
How do I connect to my server?
There are basically two ways to connect to a server: Unix Domain (socket file) or TCP/IP.
You may connect to the server by using mysql clients or by Perl or other languages like C. Using mysql clients is the simplest way, so I just use them to show you how to connect to the server. For Perl/PHP3/C programming, refer to How do I connect to the server by Perl/PHP3/C.
Unix Domain:
Unix domain is just named pipes. It's only for local connections. The file mysql.sock is for this purpose. Its function is equivalent to the host:port if you use TCP/IP connection. If there is something wrong with this file, you can not connect to the server by Unix Domain (by -h localhost or default). For example, the file is gone, or server and client are using different socket files. To use Unix Domain:
1. Start the server on some host. Make sure the two socket files in the two sections of your ~/.my.cnf file are the same.
2. On the same host, execute 'mysql -h localhost'. If you didn't put a 'host=' option in your ~/.my.cnf file, the default host name is localhost so you can just say 'mysql'.
Unix domain is the default if you don't specify the host name. It's faster than TCP/IP. But remember: it's only for local connection! We recommend you use TCP/IP.
TCP/IP
You need to specify the host and port for a TCP/IP connection. It's more general and allows remote connection as long as you set the privileges correctly. With the configuration that you just installed, any CS Solaris machine can connect to your server once you start it.
The only problem is what the port should be. If you see an error message like 'can't bind address/port...' or the server exits immediately after you start it, it's probably because someone else has been using this port that you are trying to use. The solution is to find another free port number and change it in ~/.my.cnf. Please note the first 1024 port numbers are reserved for the system.
Be sure you connect to your mysqld! By the default setting, anyone can connect to any mysql server that is running once you know the host and port.
To use TCP/IP, do the following:
1. Start the server on some host. Make sure the two port numbers in the two sections of your ~/.my.cnf file are the same.
2. Execute 'mysql -h hostname'. You can't use localhost here - it's only for Unix Domain.
Below is a checklist if you start the server but can't connect to it:
* Is there really a server running on the hostname and port number you specified? Are server and client using the same port and socket file? Are the two port numbers and socket files are identical in the .my.cnf file?
* If you get 'access denied', have you obeyed the privilege rules? Read this first.
* If localhost or TCP/IP doesn't work, try the other method. Are you trying to connect to the server by localhost but you are not on the same host? Remember 'localhost' is the default!
Read more: Connecting to the MySQL server.
Back to Top
Set your database administrator password!
Once you succeed in starting up the server and connecting to it, the first thing you need to do is setting your administrator password, or anyone else could connect to your server by root and read all your files by loading them into the database! This is super-important.
Do the following (locally!):
shell> mysql -u root mysql
mysql> set password for root@localhost = password('yourpass');
mysql> flush privileges;
Then your administrator password will be set to 'yourpass'. This is saved in your mysql database in your data directory. Be sure to use password() function because it encrypts the password you typed.
Advice: only use root to do administrator work, such as creating/drop databases, setting passwords and privileges. Don't use root in your program. If you want to access a database, set up a less-privileged user to do so. Read How do I set up privileges for more instruction.
References:
1. MySQL user names and passwords
2. How to set up passwords
3. Keeping your password secure
4. How to reset a forgotten password
Back to Top
How do I shutdown the server?
There are many ways to shutdown the server. Do any of the below on the same host:
* Run user_server stop
* Run mysqladmin [-h host] -u root -p shutdown
Try the above two first. If they don't work, something is wrong.
* See if there is a xxxx.pid file in the ~/mysql/var directory, do kill `cat xxxx.pid`
* If there is no such a pid file but you can see mysqld is running by ps -u yourusername, directly type kill itsPID.
* If kill doesn't work, try 'kill -9'.
* If all fail, send email to crf.
Make sure the corresponding pid files are deleted if you kill the server in any abnormal way.
Back to Top
How do I set up privileges?
Mysql saves the privileges in the database mysql which is only accessible by mysql root. The default setting you've installed is:
* root can only login by localhost. No password - you need to set it immediately!
* Any other user can connect from any host in the CS department (Solaris only). No password.
* Only root can access the administrative database 'mysql' and create other databases than the test databases.
* No other user can access any databases.
Read The MySQL access privilege system. I don't want to re-post the whole manual. I only give one example.
For example, you may want to write a program to manage a certain database. By default only root can create/access/drop any database. The right thing you need to do is to set up a user with correct privileges: create a database and grant the user the access to it. Don't give it more than enough.
Suppose we want to set up a user 'class' for accessing databases with name 'classdb', we do the following:
shell> mysql -u root -p
mysql> grant all privileges on classdb.* to class identified by 'class_password';
mysql> flush privileges;
If you want multiple databases, you can either set up different users for them or grant their privileges to the same user. You can also give one user access to multiple databases at one time or vice versa. It's a good habit to confine your users to databases with certain prefixes so that they won't get mixed with the system database mysql.
Read more: Adding new user privileges to MySQL.
Back to Top
How do I connect to the server by Perl/PHP3/C?
Read MySQL client tools and APIs and PHP Mysql Functions.
Simple examples can be downloaded here:
* PHP3. Click to test the PHP3 code.
* Perl
* C (Makefile).
Back to Top
How do I connect to the server using Java?
Read MySQL Java connectivity (JDBC)
MM MySQL Drivers - GPL drivers for MySQL
Back to Top
I have more questions
All the detailed information can be found from MySQL Reference Manual. A tutorial is here.
If you have any questions of running mysqld, please send email to crf@cs.columbia.edu.
Back to Top
Hua Zhong
Revised: November 26, 2000 .
Reference: http://www1.cs.columbia.edu/~crf/mysql/index.htm
