MySQL support
From OCFwiki
The MySQL server currently installed is version 4.1. It is accessible at:
mysql.ocf.berkeley.edu
See the following sections for information about performing common MySQL tasks. Note that the command line instructions assume that you have logged into an OCF machine via SSH. The MySQL commands on the OCF are configured to work with the OCF's MySQL server by default, which is why many of the command lines are so short. If you are connecting using a MySQL client from a non-OCF machine, you may need to add extra options to the command line that specify, for example, the username and server hostname.
Contents |
Creating a MySQL database
SSH into your OCF account and run
makemysql
This will create a MySQL database and assign it a randomly-generated password. Note that the OCF does not allow changing of MySQL passwords from the randomly-generated default (see below for rationale on why we do this). The database name will be whatever your OCF username is. You can create as many tables as you want in your database, but you cannot create additional databases.
If you don't wish to use SSH, you may use the web interface to do this (select the "makemysql" option); note, however, that most MySQL administration tasks will require using the shell, unless you install a web interface to MySQL such as phpMyAdmin.
Using old-style MySQL passwords
If you are connecting to the OCF's MySQL server from another system with out-of-date MySQL client libraries, it may be necessary to set an old-style MySQL password on your database. While it would obviously be better to upgrade the client libraries, you can direct makemysql to use old-style passwords by invoking it as follows:
makemysql -o
Connecting to a MySQL database
Assuming your database is set up, in order to access it (or allow a web application to access it), you will need 4 pieces of information:
- Database Host: mysql.ocf.berkeley.edu
- Database Name: <your OCF username>
- Database User: <your OCF username>
- Database password: <your database password>
Note: Your database password is not the same as your OCF account password. It is a randomly generated password that was created when your database was created. If you don't know what your database password is but know your database is set up, contact staff. We may be able to help you retrieve it.
To use your OCF MySQL database with a web application, enter the above information during the application's installation process.
To connect to the OCF's MySQL server using the MySQL client on an OCF machine, simply run the command
mysql
This command will prompt you for your MySQL database password.
Backing up a MySQL database
To backup your database (which you should probably do regularly), the basic command to use is
mysqldump [username] > backup
where [username] denotes your OCF username and backup is the name of the file you want to dump the contents of your database into. This command will prompt you for your MySQL password.
To see more options, try running
mysqldump --help
Restoring a MySQL database from backup
If you need to restore your database from a backup (dump file) you made previously, you simply need to connect to the database and run the SQL commands in the dump file. To do this from the command line, use
mysql -D [username] < backup
where backup is the name of the file that contains the dumped data. As usual, this command will prompt you for your MySQL password.
Changing or resetting your database password
If you have lost or forgotten the randomly-generated password that you received when you created your database, you can easily reset your password. Simply SSH into an OCF machine and re-run
makemysql
This will change your MySQL password to a new random password and tell you what that password is.
Alternately, you may visit the web interface and select the "makemysql" option to reset your database password.
Using .my.cnf for passwordless access to MySQL
If you are using MySQL in a script or in another program, you will probably want to set it up so that the mysql call does not prompt for your password. While passing the -p option is the obvious way to achieve this, it is also very insecure because anyone on OCF can see the password while the mysql command is running. A better approach is to create a .my.cnf file with proper permissions that contains your MySQL password. To do this, enter the following commands: make sure you replace dbpasswd with the MySQL password given to you when you ran makemysql.
cat >> ~/.my.cnf << EOF [client] password = dbpasswd EOF chmod 600 ~/.my.cnf
And make sure that your .my.cnf has correct permissions by running
ls -l ~/.my.cnf
The output should look something like
-rw------- 1 staff ocf 64 2005-11-15 16:16 /home/s/st/staff/.my.cnf
where the important part is -rw-------. After this, the mysql command will automatically log you in, so you won't have to memorize or write down your MySQL password.
If you want to find out what other things you can do with .my.cnf, take a look at the .cnf files in /opt/local/packages/mysql/share/mysql/.
A note on support for LOAD DATA LOCAL INFILE
The MySQL developers turned off this feature by default starting in the 4.x series of MySQL releases, due to potential security issues. But since the OCF's current configuration is not affected by these issues, we do currently allow the use of LOAD DATA LOCAL INFILE. However, users should be aware that it is possible future directions in OCF configuration may require us to disable this feature.
