User Guide/SQL Database

This document is based off of a document created by OCF, and used under the Creative Commons Attribution 4.0 International ("CC BY 4.0") license. A human-readable summary of the CC BY 4.0 is available at: https://creativecommons.org/licenses/by/4.0/

This information contained may not be entirely accurate

• User Guide
  • Web Hosting
  • VPN
  • Shell
  • SQL Database
  • Python

UGCS provides each user with a MySQL database. Read the following sections for information about performing common MySQL tasks, and our rules and policies regarding MySQL.

The actual server runs MariaDB, which is an active community-driven fork of MySQL. In practice, you shouldn’t notice any important differences between the two, though MariaDB does contain some improvements.

Policies

We limit users to 1 database, and this database has the same name as your username. As a user of shared database server we ask you to respect the community and refrain from any activities that will hurt the quality of service for other users. We understand this policy is vague, so our suggested maximum size for databases is 1GB, and for individual tables 256 MB. Storing large amounts of data is not a problem unless the server has to perform complex queries on this data.

Web-based interface for editing

If you are looking for a familiar phpMyAdmin interface, host it on your sites instance

Download the latest phpmyadmin to your public_html folder. Find the download link on the phpmyadmin page. downloads

~/public_html/$ wget https://files.phpmyadmin.net/phpMyAdmin/4.x.x/phpMyAdmin-4.x.x-all-languages.tar.gz

Then unzip it

tar -zxf phpMyAdmin-4.x.x-all-languages.tar.gz

Since we use php cgi, we have to make the php files executable. This command does that

find . -iname "*.php" | xargs chmod +x

Then just browse over to your own personal phpmyadmin

Creating a MySQL database

We have two options to create a database using the terminal or our web management tool.

Web-based tool

create database

SSH Terminal

nope

Reset Password

To reset a MySQL password simply follow the directions above for creating a MySQL database. This program WILL NOT DELETE an existing database.

Accessing MySQL

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: usersql
• Database Name: {your UGCS username}
• Database User: {your UGCS username}
• Database password: {your database password}

Note: Your database password is not the same as your UGCS account password. It is a randomly generated password that was created when your database was created. To use your UGCS MySQL database with a web application, enter the above information during the application’s installation process.

To connect to the UGCS’s MySQL server using the MySQL client on a UGCS machine, simply run the command: mysql -h usersql.ugcs.caltech.edu -p

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 UGCS 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.

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 UGCS 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.

touch ~/.my.cnf
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
-rw-------  1 user user 64 2005-11-15 16:16 /home/user/.my.cnf

Now the mysql command will automatically log you in, so you won’t have to memorize or write down your MySQL password.