Student-administered MySQL Database installation for CS634, Spring '16, and assigned ports for mysqld

We will share all the mysql system binaries and other read-only configuration files. Each student will install and start their own database server and will be a DBA for the installation, using their non-privileged Linux account.

Each student has access to local diskspace on topcat by ownership of a directory under /var/local/cs634. User joe has directory /var/local/cs634/joe for example. We'll refer to this directory as $db634. You need to be logged into topcat.cs.umb.edu to access this directory, unlike, for example, your cs634 homework directory /courses/cs634/s16/eoneil/joe, which is available from any cs.umb.edu Linux host.

This area under /var/local/cs634 has also been specially authorized for access by mysql processes by configuration of the Ubuntu file security system AppArmor, in file /etc/apparmor.d/local/usr.sbin.mysqld. We used this feature for problem 5 of homework 1, when we put employees.txt there for bulk loading of the employees table.

Be sure you understand the ps and kill UNIX commands before starting this. You will be running a the mysqld, the mysql "daemon", in the background as well as several tools as normal foreground programs.

1. Look at the directory system under /var/local/cs634/eoneil and make a directory system (all empty directories for now) under your $db634 directory to match:

    ./etc/mysql: for .cnf files (like /etc/mysql)
  ./log/mysql: for error.log  (like /var/log/mysql, protection rwxr-x---)
  ./lib/mysql: for database files/logs  (like /var/lib/mysql, protection rwx------)
  ./run/mysqld: for mysql.pid and mysql.sock (like /var/run/mysqld)

2.  Copy my.cnf from the eoneil system to your own, and replace 'eoneil' with your own username everywhere in it.  Fill in your own port instead of 3406.

3. Copy *.sh from the base directory of the eoneil system to your own, and replace 'eoneil' with your own username everywhere in them, and change the port too.

4. Make *.sh executable with chmod.

5. Execute makedb.sh to create the database files. After this, check that lib/mysql now has a large file ibdata1, the first database file.

6. Execute runserver.sh& to start your database server mysqld. The & sign makes it a background process, and you will get a new system prompt, at least after you do a carriage return. After this, check that it's running with "mysqladmin.sh status". 

7. Use "mysql.sh -u root -p" to access your new installation as the mysql user root. To start, the password is null, so just do a carriage-return when prompted for the password.

8. Use "create database test1" to prove your DBA powers. Change your root password to protect your system.

9. Exit from the mysql client.

10. Use "mysqladmin.sh shutdown" to bring down your server.  You can see more commands with mysqladmin.sh --help.

Troubleshooting:

• If need be, you can kill your mysqld background process by using ps to find its pid, and "kill <pid>". Then allow a minute or so before retrying to let the port get released.
  topcat$ ps
  PID TTY          TIME CMD
13259 pts/5    00:00:00 bash
19576 pts/5    00:00:00 bash
19577 pts/5    00:00:00 mysqld_safe
19983 pts/5    00:00:00 mysqld
22825 pts/5    00:00:00 ps
topcat$ kill 19983
topcat$ 160227 17:11:52 mysqld_safe mysqld from pid file /var/local/cs634/eoneil/run/mysqld/mysqld.pid ended
[1]+  Done                    runserver.sh
  
• You can read the error.log file, particularly the last 100 lines. Use "tail -100 error.log|more" to read this part.
• netstat -a |grep <port>  tells you whether or not that port is in use.

Assigned Ports