Systems Status

Announce RSS Feed RSS

Blog RSS Feed RSS

Help Topics

Deschutes Facilities

Campus Facilities

User Account Info

Roundcube Mail

Contact Systems

ipv6 ready

Introduction

The standard MySQL install is run as a self-contained DBMS that needs an administrator to add users and grant access. For instructional purposes we've written some scripts that allow you to run your own MySQL server (under your own user id), listening on its own socket/port, with its own database area, and with the database tables, permissions, and personal configuration all maintained by you. This is done with the command mysqlctl.

mysqlctl install

The command mysqlctl install creates a mysql data directory (~/mysql-data) and creates the initial tables. When prompted enter a database password of your choosing. Remember this password as you will need it to run all the mysql and mysqladmin commands.

jdash@ix: ~ 16$ mysqlctl install
This script is designed to setup an individual mysql server
Database password (DO NOT use your unix password):
Verify password:
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
Preparing procs_priv table
Installing all prepared tables
jdash@ix: ~ 17$

mysqlctl start

The command mysqlctl start creates a .my.conf file and starts the server.

jdash@ix: ~ 17$ mysqlctl start
Port 3602 busy, trying 3524
Started mysqld on port 3524
jdash@ix: ~ 18$
((sourcend:)


!! [[#stop]] mysqlctl stop
The command @@mysqlctl stop@@ will stop your mysql server.

(:source lang=text -getcode:)
jdash@ix: ~ 21$ mysqlctl stop
Stopping mysqld process 1767

Check the running server

You can use mysqlctl status for a quick check of your running server.

jdash@ix: ~ 18$ mysqlctl status
mysqld (pid 1767) listening on ix:3524

If the server is not running the results will look like this:

jdash@ix: ~ 22$ mysqlctl status
no .pid file for ix:3524

You can also use mysqladmin to check the status of your server.

jdash@ix: ~ 19$ mysqladmin -p version
Enter password:
mysqladmin  Ver 8.41 Distrib 5.0.24a, for sun-solaris2.9 on sparc
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.24a-log
Protocol version        10
Connection              ix via TCP/IP
TCP port                3524
Uptime:                 2 min 35 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 6  Flush tables: 1  
               Open tables: 5  Queries per second avg: 0.006

This should show you that your data directory, port, socket, and any other settings you've made are being correctly read.

Note: when you start the mysql shell or many of the mysql administrative programs, you will need to use the "-p" flag so that you will be prompted for your database password.

Make a new database for your application

The organizational concept each mysql server works under is that it has a number of databases, and each database will in turn have a number of possibly related tables. Access rights can be different for different databases or even different tables. As set up, there is a database called "mysql" which has the tables for permission and access granting. You can see the tables with the command:

jdash@ix: ~ 20$ mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.24a-log

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

mysql> SHOW TABLES FROM mysql;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.01 sec)

mysql>

So to start with, you'll want to create a new database:

mysql> CREATE DATABASE my_db;
Query OK, 1 row affected (0.00 sec)

Tell mysql to use your new database:

mysql> use my_db;
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

then give it one or more tables:

mysql> CREATE TABLE names ( first_name text, last_name text );
Query OK, 0 rows affected (0.02 sec)

Now, to add some names to the names table:

mysql> insert into names values ("Jo Don","Dash");
Query OK, 1 row affected (0.00 sec)

mysql> select * from names;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Jo Don     | Dash      |
+------------+-----------+
1 row in set (0.00 sec)

That's the basic database creation commands.

Debugging

You can enable query logging by uncommenting the line general-log and error logging the line log-error in your .my.cnf file.

# This will be passed to all mysql clients
[client]
port    =       1234
socket  =       /home/users/jdash/mysql-data/mysqld_sock
host    =       ix-trusty
#password       =       my_password

# The MySQL server
[mysqld]
port    =       3753
socket  =       /home/users/jdash/mysql-data/mysqld_sock
datadir =       /home/users/jdash/mysql-data
pid-file=       /home/users/jdash/mysql-data/.pid
# default is to only bind localhost
# bind-address  =       0.0.0.0
# for all mysql versions
#skip-innodb
# for mysql-5.5 and above
#default-storage-engine=myisam
# for mysql-5.6 and above
default-tmp-storage-engine=myisam

# uncomment the following line for logging
#general-log
#log-error

Notes about our local installation

Other stuff to do...

The details of maintaining a mysql server are beyond the scope of this document. However, the official mysql docs are actually fairly understandable, accurate, and up to date.

The mysqlctl program is a shell script located in /local/bin/mysqlctl. It creates a file in your home directory named .my.cnf.

See Also

Manual Pages

 mysql (1)- the MySQL command-line tool
 mysqladmin (1)- client for administering a MySQL server
 mysqldump (1)- a database backup program
 mysqld (8)- the MySQL server
 DBD::mysql (3pm)- MySQL driver for the Perl5 Database Interface (DBI)

External Links

Edited: November 08, 2014, at 07:57 AM
Copyright © 2017, University of Oregon, All rights reserved
Privacy Policy