Technical Note - Personal MySQL Server

Summary

This shows how to configure a MySQL server on your workstation and create a database.

NOTE: This is mainly intended for students that have a need to run MySql locally on a machine. If you don't have any requirements to be able to play with the server then you are better off sending a request to jobs@ecs.vuw.ac.nz asking for a database to be created on our main MySql server.

Details

In all the commands below the $ signifies the command line prompt.
Single quotes ' are used around text in sql statements so must be used.

WARNING If in the pass you have been using mysql and have created a .my.cnf file in your home directory. You must rename this file so it can't override default behaviour.

Create a directory on your computers local hard drive /local/scratch.
$ mkdir -p /local/scratch/$USER/mysql/data

Create the base mysql db
$ /usr/sbin/mysqld  --initialize-insecure --basedir=/usr/pkg \
--datadir=/local/scratch/$USER/mysql/data

Start the mysql server
$ /usr/pkg/bin/mysqld_safe --log-error=error.log --basedir=/usr/pkg \
--datadir=/local/scratch/$USER/mysql/data &

Connect, set root's password and exit mysql.
$ mysql -u root --skip-password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'your-password';
mysql> quit

That's it for the startup stuff.

If you reboot your computer you will have to restart the mysql server
$ /usr/pkg/bin/mysqld_safe --log-error=error.log --basedir=/usr/pkg \
--datadir=/local/scratch/$USER/mysql/data &

Now you need to create yourself a database by going into mysql
$ mysql -u root -p

Once at the " mysql> " prompt create your database

mysql> create database myDBname;  (Don't put any spaces or punctuation characters in the database name)

Create a user and give yourself access to the database
grant all on myDBname.* to 'myUserName'@'%' identified by 'myPassword' with grant option;

Exit mysql and that's it you now have a database that you can do what you want with.

You can now connect to the database by doing
$ mysql -u myUserName -p myDBname

If you want to import data
$ mysql -u myUserName -h hostName -p myDBname < dumpfile

Shutting your local MySQL server down

In order to do a proper shutdown, you need to do it as the database "root" user, unless you have given your own usename the SHUTDOWN privilege.

$ /usr/pkg/bin/mysqladmin -h localhost -u root -p shutdown
Enter password: 

Caveat: MySQL upgrades

Hopefully, your need for a personal MySQL server will be short-lived enough that you won't access your local database, its data stored locally to your machine, with different versions of an underlying MySQL server, however, you may wish to keep personal data in your personal database over a long period, and we do upgrade the system software from time to time, so it does pay to look at the error.log from time to time, because you might then see that MySQL is trying to tell you something useful, eg

[ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it

This command, issued in the shell, (though you might need to do a ps to get the port number for your MySQL server process, alternatively, it might be visible in the error.log file)

% /usr/pkg/bin/mysql_upgrade  -h localhost -u root -p --protocol=tcp -P 3306

should do what is needed, however, having a backup/dump of your database before running such command is a good thing.

Doing a backup/dump straight after the upgrade is probably also a good thing.

Lastly, as always, it helps to look at the manual which, as expected, is online.
Also " man mysql " gives help on all the mysql command options