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 tojobs@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/dataCreate the base mysql db
$ /usr/sbin/mysqld --initialize-insecure --basedir=/usr/pkg \ --datadir=/local/scratch/$USER/mysql/dataStart 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> quitThat'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 theerror.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 itThis 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 3306should 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