Mysql

From UF HPC Wiki

At the HPC Center we use mysql for a number of different things. As time goes on, I am sure that there will be more uses for the database software.

There is also some information on MySQL Administration on the Wiki.

Connection to MySQL

If you want to connect to the CLI of the mysql database, and you already have a user account for the MySQL database server, you can connect via the following means:

mysql -p -h <hostname> -u <username>

• hostname: The hostname of the server that you are connecting to.
• username: The username you are using to connect to the server.

The -p option tells the mysql client to ask for a password for the login.

Database Repair

From time to time, due to the mysql server crashing, the tables become corrupt in some databases hosted by the service. Here is how to fix them.

• Run mysqlcheck on the database that is broken:

hpc:~ mysqlcheck -p pbslogs
Enter password: 
pbslogs.dept_lookup                                OK
pbslogs.jobs_aborted                               OK
pbslogs.jobs_checkpointed                          OK
pbslogs.jobs_confirmed_resources_reservation       OK
pbslogs.jobs_deleted                               OK
pbslogs.jobs_ended
error    : Table './pbslogs/jobs_ended' is marked as crashed and should be repaired
pbslogs.jobs_queued                                OK
pbslogs.jobs_requested_removal_reservation         OK
pbslogs.jobs_rerun                                 OK
pbslogs.jobs_reservation_terminated                OK
pbslogs.jobs_reserved                              OK
pbslogs.jobs_reserved_ended                        OK
pbslogs.jobs_restarted_checkpoint                  OK
pbslogs.jobs_started
error    : Table './pbslogs/jobs_started' is marked as crashed and should be repaired
pbslogs.jobs_unconfirmed_resources_reservation     OK

• Login to mysql and repair the broken tables:

hpc:~ mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1519
Server version: 5.0.45-5-log (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use pbslogs;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> repair table jobs_started;
+----------------------+--------+----------+----------+
| Table                | Op     | Msg_type | Msg_text |
+----------------------+--------+----------+----------+
| pbslogs.jobs_started | repair | status   | OK       | 
+----------------------+--------+----------+----------+
1 row in set (33.54 sec)

mysql> repair table jobs_ended;
+--------------------+--------+----------+----------+
| Table              | Op     | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| pbslogs.jobs_ended | repair | status   | OK       | 
+--------------------+--------+----------+----------+
1 row in set (26.26 sec)

mysql> 

• Recheck the tables to be sure that they are no longer broken:

hpc:~ mysqlcheck -p pbslogs
Enter password: 
pbslogs.dept_lookup                                OK
pbslogs.jobs_aborted                               OK
pbslogs.jobs_checkpointed                          OK
pbslogs.jobs_confirmed_resources_reservation       OK
pbslogs.jobs_deleted                               OK
pbslogs.jobs_ended                                 OK
pbslogs.jobs_queued                                OK
pbslogs.jobs_requested_removal_reservation         OK
pbslogs.jobs_rerun                                 OK
pbslogs.jobs_reservation_terminated                OK
pbslogs.jobs_reserved                              OK
pbslogs.jobs_reserved_ended                        OK
pbslogs.jobs_restarted_checkpoint                  OK
pbslogs.jobs_started                               OK
pbslogs.jobs_unconfirmed_resources_reservation     OK