Setting up mysql

How do I set up my own MySQL database?

To set up a persistent MySQL server, you will need access to grieg, our "server server".
If you don't have access, see Running your own server for details.
You can run a database on any of the lab computers for as long as you are logged in.
You can re-run the database on another computer if you keep the database files in your account.
This may be sufficient to test a program or develop an assignment.

Installing your database

If you are running the service on Grieg or Newgrieg then you probably want to use the special /srvr directory to hold your database files:
Once you have access, you can run the following commands:
$ ssh grieg
$ priv srvr
$ cd /srvr/username
$ mkdir database
Otherwise, just create a database directory in your home directory:
$ cd; mkdir database
Then, to set up a new MySQL server.
$ cat > database/my.cnf <<EOF
[client]
port=3306
socket=/srvr/username/database/mysql.sock
[mysqld]
port=3306
socket=/srvr/username/database/mysql.sock
key_buffer_size=16M
max_allowed_packet=1M
bind-address=username.srvr
[mysqldump]
quick
EOF
$ cp database/my.cnf ~/.my.cnf
Then create the initial database in the /srvr directory:
$ mysql_install_db --datadir=/srvr/username/database
Or, just in your home directory:
$ mysql_install_db --datadir=/home/username/databaseThe rest of this document is written for the case where you are using a /srvr directory.
If you are not, just use the /home file path instead and ignore instructions to log into Grieg or run priv srvr.

Securing your MySQL database

Once you've installed the database, you need to set passwords to secure it.
To do this, you can use the following commands.
Please note that you will need to run priv srvr if you have logged out since creating the database.
mysqld_safe --datadir=/srvr/username/database --pid-file=mysqld.pid --log-error=mysqld_err.log --socket=mysql.sock &
And run:
/usr/bin/mysqladmin -u root password MyRootPassword
where MyRootPassword is a suitably non-guessable string. This will set the password for the root mysql user.
(Note that this is NOT the same as the UNIX root user.
You do not need root access to the system in order to do this.)
That being done, you can now create your own MySQL users, each with their own passwords (again, these are purely local, and do not correspond directly to UNIX usernames), in order to actually run queries on the database:
$ mysql -u root -pMyRootPassword
mysql> GRANT ALL PRIVILEGES ON *.* TO MyLocalUserName@localhost IDENTIFIED BY 'MyLocalUserPassword' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO MyLocalUserName@'%' IDENTIFIED BY 'MyLocalUserPassword' WITH GRANT OPTION;
mysql> exit
$ mysqladmin -u root -pMyRootPassword shutdown
Once again replacing MyLocalUserName and MyLocalUserPassword with suitable values.
Note that you should not use your own login password for this.
Database passwords can appear in log files, and you might need to store them in plain-text in programs you write to access your database.
This is an unacceptable security risk where your own login password is concerned.

Starting and stopping your database

If you are trying to run a mysql database created on grieg (under mysql 5.0) on a lab machine or on newgrieg (under mysql 5.1), you may need to run mysql_upgrade on it first
The MySQL server daemon needs to be run from grieg, within the priv srvr environment.
This means that you need to run priv srvr after logging in to grieg.
See Running your own server for details.
To start the server:
$ mysqld_safe --datadir=/srvr/username/database --pid-file=mysqld.pid --log-error=mysqld_err.log --socket=mysql.sock &
The server will continue to run in the background, until it is shut down, even if you kill the shell that you ran it from.
Some users are reporting problems with the above command line; if the error log shows problems binding to the port, try adding --bind-address=username.srvr
To stop the server:
$ mysqladmin -u root -pMyRootPassword shutdown

If you do not require continual use of your database (ie, unless you are running an 'always on' service such as a publicly-accessible web application), then please shut it down after use to conserve system resources for other users.

Connecting to your database

You are now ready to connect to your database.
To connect using the command-line client:
$ ssh grieg
$ mysql -uMyLocalUserName -p
Password:
And type your local user password.
This can be specified on the command line ( -pMyLocalUserPassword ) if required in scripts, etc, but the above method helps keep passwords out of your shell history, which is always a good thing.
To connect from other hosts (assuming the mysql client is installed), add the parameter -h username.srvr to the command line.
To connect from other clients (ie, scripting languages or database-enabled applications), see the documentation for the software in question.
It is important to note that the host to connect to is username.srvr, NOT grieg.

Log Files

It is helpful to keep logfiles, so error logging is enabled in the commandlines above.
To turn on full transaction logging run mysqld_safe with --general_log_file=logfile.
A busy database can log an awful lot of data to its logfiles.
If they log so much data that you fill up your directory and run out of diskquota, your database may stop working.
You will have to truncate the logs and restart the database if that happens, so keep an eye on the size of your logfiles and rotate them regularly.
Some advice on MySQL log files can be found at the MySQL Server Log Maintenance page.

Automatic log rotation with logrotate

If you do not know what logrotate and crontab are, you should read the relevant manual pages first.
You can use logrotate to rotate log files and delete old logs.
You can run it nightly using a crontab setting. Eg:
25 6 * * * /usr/sbin/logrotate /srvr/username/mysql-logrotate.conf
You can find an example logrotate configuration file for MySQL in this directory: /etc/logrotate.disabled
Last edited by jbc 06/09/2016