How do I set up my own MySQL database?
To set up your own MySQL server, you will need access to grieg, our "server server". If you don't have access, see Running your own server for details.Installing your database:
Once you have access, you can run the following commands to set up a new MySQL server. Of course, you'll need to replace "YourUserName" with your actual user name.$ ssh grieg
$ priv srvr
$ cd /srvr/YourUserName
$ mkdir database
$ cat > database/my.cnf <<EOF
[client]
port=3306
socket=/srvr/YourUserName/database/mysql.sock
[mysqld]
port=3306
socket=/srvr/YourUserName/database/mysql.sock
set-variable = key_buffer_size=16M
set-variable = max_allowed_packet=1M
[mysqldump]
quick
EOF
$ cp database/my.cnf ~/.my.cnf
$ mysql_install_db --datadir=/srvr/YourUserName/database
Securing your MySQL database
Once you've installed the database, you need to set passwords to secure it. To do this start the server (still in the priv srvr environment):$ mysqld_safe --datadir=/srvr/YourUserName/database --log=mysqld.log --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
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 in plaintext 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
The MySQL server daemon needs to be run from grieg, within the priv srvr environment. See Running your own server for details.To start the server:
$ mysqld_safe --datadir=/srvr/YourUserName/database --log=mysqld.log --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.
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:
To connect from other hosts (assuming the mysql client is installed), add the parameter -h YourUserName.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 YourUserName.srvr, NOT grieg.
A note on logfiles Although it's a good idea to keep logfiles, which is why we've included them in the commandlines above, a busy database can log an awful lot of data to these 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.