MySQL Databases

    ARRGGGG!! This came out different in SuSE 9.2. Notes are in Blue. It all worked like it should!!!
And now we will see how it works in SuSE 10.2 ... Well it worked. I am happy to say that a DB project that I started in the 9.x era has carried over into the 10.x era and the PHP4 interface works fine in PHP5.... so far. There is one thing that I will have to coordinate. I have this same DB on my WebSite. Need to make sure that they both have the same data and tables. Another interesting challenge.

   The above exasperation was from, as you can see, my difficulties with SuSE 9.1 and then the reprieve in SuSE 9.2. The following 5 steps, two of which are just a test, should work... after the installation of MySQL via YAST in SuSE 10.2. Note: You must be root to execute these commands. However, notice that the user is specified to be mysql.

  1. mysql_install_db --user=mysql   Creates the DB
  2. mysqld_safe --user=mysql   Starts the DB
  3. mysqladmin version   Test
  4. mysqladmin variables   Test
  5. mysqladmin -u root shutdown   Stops the DB

Memories ... raging thru the echos of my mind ...

    Now then, I had created a DB and even wrote up the experience, which is one of the selections above. However, that was months ago. I saved the DB files with the expectation of just bringing them over and reusing them. Unfortunately, I don't remember where they all go. So, now my notes get a real big test. Can I reconstruct the whole mess from them. I'm sure there will be areas that need additional notes but then that will be benificial to any of you who care to use any of this.

    Ok. The DB files can be found in /var/lib/mysql/. I found the DB that I had gzipped and saved. The full path is: /var/lib/mysql/TRcustomers/ and under it is all the tables. I created an empty TRcustomers DB and then extracted the one from the gzipped file. I entered MySQL and executed the 'show tables' command. It displayed a list of tables that matched my notes. Next I issued 'describe address' and it also displayed data that matched my notes. So far it looks as though my saved DB is intact.

    Alllllrighty!! I have a PHP GUI interface that is described in my DB Creation Pages and it works!! I am so happy and proud. Only problem is it is out of date. But ... that will give me reasons and a chance to get back into all this. Maybe even make some enhancements!!

     The above will establish MySQL as a DB on your system. The following verbiage concerns my previous exploits. Some you may have encountered yourself.


   The reason for the '?' above is; this is just supposed to be about Databases (DBs). However, PHP is used alot with MySQL so there will probably be some of that in here. In fact, the book that I will be referencing alot is titled "PHP, MySQL and Apache". Which brings out another aspect. Networking. That is the Apache part. I will also be referencing the book in my Networking section. Or I plan to anyway.

  Side Note: In a recovery episode, I found that my DB wouldn't work. I had all the Customer files and the HTML/PHP code that I wrote saved and then re-loaded but the thing kept failing. It couldn't find a PHP function: session_start(). Well, after scratching my head and looking around on the Web I came up with the answer. I'm not sure how I loaded all this the first time but there are some packages called "PHP4 Extension Modules" that contain all or most of the functions that I was using. So, if you try to use something that you know "should" be there, check that all your PHP modules got selected and loaded. Also, unless you are real smart and can do otherwise, you will have to restart your system after you have loaded the PHP stuff. This is so it will get initialized and started.

   Ok, so much for the BS. Now then, I theoretically have MySQL loaded on my Linux machine. During my setup in SuSE Linux I checked off that I wanted MySQL installed. I want to get MySQL running on my box before I jump into the one on my WebSite. I know MySQL is not running on my PC via the ps command (ps auxw | grep mysql). So, what to do?? Well, according to the book there should be a mysql user. This can be confirmed (in SuSE Linux) by looking in the passwd file which is located in the /etc directory. Yep, user mysql is there:
     mysql:x:60:2:MySQL database admin:/var/lib/mysql:/bin/false
Now all I have to do is find out what the password is. I do believe that there is a default that I am gona have to look up. Nope, there is not supposed to be one on a new installation. However, a new password can be created using:
     mysqladmin -u root password 'new-password'
Of course you must know the root password when using the above command.

   So, let's see if we can start it.

ctaylor@dusty-tr:~> /usr/sbin/mysqld --user=mysql
Warning: One can only use the --user switch if running as root
040330  9:41:02  Can't start server : Bind on unix socket: Permission denied
040330  9:41:02  Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ?
040330  9:41:02  Aborting

040330  9:41:02  /usr/sbin/mysqld: Shutdown Complete

Nope. Didn't think it would be that simple. So, let's see; you are not supposed to start it as root, but you have to be root in order to use the --user switch. Hmmmmm...

   Here goes a try through root:

dusty-tr:/home/ctaylor # mysqld --user=mysql
040408 22:26:12  Fatal error: Can't open privilege tables: Table '' doesn't exist
040408 22:26:12  Aborting

040408 22:26:12  mysqld: Shutdown Complete

09 Apr 04

   Nope. That no worky either...

30 May 04

   Memorial Day Week-End. Been working on other things and I got a job at Wal-Mart. Time is not as plentiful as it was. And today we have two races. Plus I have to work!! But I want to get into database mode and get this working ... finally.

   Ok. First thing I did was to check on what got loaded. I did a re-load and added some tools that I missed. So now, this is what I have installed on my system. MySQL version 4.0.15-9 i586 for SuSE ver 9.0.

  • Clients
  • mysqlcc - MySQL Control Center, a MySQL GUI Client
    It can be used to control a MySQL database daemon and modify any database.
  • mysql-client - MySQL - Client
    This package contains the standard MySQL clients.
  • qt3-mysql - MySQL plugin for Qt
    Plugin for using the MySQL database with the generic Qt database interface.
  • Servers
  • mysql - A true multi-user, multi-threaded SQL database server
    MySQL is a client/server implementation that consists of a server daemon mysqld and many different client programs and libraries.
    Note: This package only contains the server-side programs.
  • mysql-Max - MySQL - server with Berkeley DB
    An optional MySQL server binary that supports features like transactional tables. To make use of these features, just install this package in addition to the MySQL package.
  • Tools
  • db-utils - Command Line tools for Managing Berkeley DB Databases
    The Berkeley DB Database is a programmatic toolkit that provides database support for applications. This package contains the command line tools for managing Berkeley DB databases.
  • MyODBC-libiodbc - ODBC interface for communication with MySQL compiled with libiodbc
    MyODBC 2.50 is an interface for communication with MySQL. It is one of the most popular ODBC drivers in the Open Source market, used by many users to access the MySQL functionality. It is compiled with libiodbc.
  • MyODBC-unixODBC - ODBC interface for communication with MySQL compiled with unixODBC
    MyODBC 2.50 is an interface for communication with MySQL. It is one of the most popular ODBC drivers in the Open Source market, used by many users to access the MySQL functionality. It is compiled with unixODBC.
  • mysql-bench - MySQL - Benchmarks
    This package contains MySQL benchmark scripts and data. If you like to run these database benchmarks, start the script "run-all-tests" in the directory /usr/share/sql-bench after starting MySQL.
  • Other Tools
  • libiodbc - The iODBC Driver Manager Library
    The iODBC Driver Manager is a free implementation of the SAG CLI and ODBC compliant driver manager which allows developers to write ODBC compliant applications that can connect to various databases using appropriate backend drivers.
  • mdbtools - A Suite of libraries and programs to access Microsoft Access databases
    Mdbtools contains: mdb-dump -- simple hex dump utility for looking at mdb files mdb-schema -- prints DDL for the specified table mdb-export -- export table to CSV format mdb-tables -- a simple dump of table names to be used with shell scripts mdb-header -- generates a C header to be used in exporting mdb data to a C prog mdb-parsecvs -- generates a C program given a CSV file made with mdb-export mdb-sql -- demo SQL engine program mdb-ver -- print version of database
  • mdbtools-gui - A Suite of libraries and programs to access Microsoft Access databases
    GUI version of the above.
  • unixODBC - ODBC driver manager with some drivers included
    UnixODBC aims to provide a complete ODBC solution for the Linux platform. Further drivers can be found at

   Alllllrighty now. I was able to excute mysqlcc (the control center) directly from the command line after the installation. By the way, I didn't have to do a re-start. I am presented with two windows. A large one and a small one. The small one appears to be some kind of a sign-on/set-up window. The first item in the small win is name. Not your name, but an alias name for the DB connection you are about to make. Next is host name of the 'puter. Then we have your user name which is filled in with your system login ID or user name. And then we have a password. The rest appear to be default options and operations for when you connect. We'll see.

   There were three buttons at the bottom and two were Test and Add. I tried both. For Test I got "Testing connection to MySQL Server ..." and "connecting ..." For Add I got "Connection added successfully." The only problem is, the DB is still not running. Now how do I get this thing going?? Not sure how the connection got added successfully. Hmmmmm??

   I had executed mysqlcc from the command line and unfortunately I got the following message:
      "Xlib: extension "GLX" missing on display ":0.0".
Even though it worked... or at least appeared to. Had this before in the past but can't remember what I did, if anything, to correct it.

   Ok, we read the manual. Post install needs to execute mysql_install_db. Either this didn't get done via the SuSE install or I need to set/grant some permissions. Findings?? It didn't get done and when I tried to execute it I got complaints about permissions. So we don't have a database yet. Ok, we'll switch to root and try again.

    dusty-tr:/home/ctaylor # mysql_install_db --user=mysql
    Preparing db table
    Preparing host table
    Preparing user table
    Preparing func table
    Preparing tables_priv table
    Preparing columns_priv table
    Installing all prepared tables
    040602  7:55:44  /usr/sbin/mysqld-max: Shutdown Complete
    This is done with:
    /usr/bin/mysqladmin -u root password 'new-password'
    /usr/bin/mysqladmin -u root -h dusty-tr password 'new-password'
    See the manual for more instructions.
    You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
    cd sql-bench ; perl run-all-tests
    Please report any problems with the /usr/bin/mysqlbug script!
    The latest information about MySQL is available on the web at
    Support MySQL by buying support/licenses at
    dusty-tr:/home/ctaylor # 

   Hurray for the good guys!! We have success. Arrgg! Short lived. Now all the stuff is owned by root!

Well, after running this in SuSE 9.2 I got it all owned by mysql. Both the user and group were mysql. Should have been that way the first time. But then we don't learn anything unless we have some kind of troubles.
Hmmmmm. These permissions get so frustrating sometimes. Dishonest people, untrusting people, non-shaing people and paranoia brings all of this on us. So much confusion and wasted computing power could be saved if it weren't for all these. We should all trust in the Lord.

   Ok now, in the passwd file, mysql shows an ID of 60 and a group of 2, which is daemon. I'm not sure where the daemon group lies in the permissions heirarchy but it's got to be pretty high. my.cnf is in /etc. So, we have a user named mysql but you can't sign on as mysql user cause he doesn't have a home dir. Well... that isn't true. In the passwd file the home-dir for mysql is listed as /var/lib/mysql.
   ldata=/var/lib/mysql- - - - - - -Owner: mysql   Group: daemon
   mdata=/var/lib/mysql/mysql- -Owner: mysql   Group: daemon
       All of the following files were changed to have mysql as owner and daemon as group.
This changed in SuSE 9.2. I didn't have any troubles with mysql!!
   -rw-rw---- 1 root root 8778 2004-06-02 07:55 columns_priv.frm
   -rw-rw---- 1 root root 0 2004-06-02 07:55 columns_priv.MYD
   -rw-rw---- 1 root root 1024 2004-06-02 07:55 columns_priv.MYI
   -rw-rw---- 1 root root 9088 2004-06-02 07:55 db.frm
   -rw-rw---- 1 root root 306 2004-06-02 07:55 db.MYD
   -rw-rw---- 1 root root 3072 2004-06-02 07:55 db.MYI
   -rw-rw---- 1 root root 8641 2004-06-02 07:55 func.frm
   -rw-rw---- 1 root root 0 2004-06-02 07:55 func.MYD
   -rw-rw---- 1 root root 1024 2004-06-02 07:55 func.MYI
   -rw-rw---- 1 root root 9064 2004-06-02 07:55 host.frm
   -rw-rw---- 1 root root 0 2004-06-02 07:55 host.MYD
   -rw-rw---- 1 root root 1024 2004-06-02 07:55 host.MYI
   -rw-rw---- 1 root root 8877 2004-06-02 07:55 tables_priv.frm
   -rw-rw---- 1 root root 0 2004-06-02 07:55 tables_priv.MYD
   -rw-rw---- 1 root root 1024 2004-06-02 07:55 tables_priv.MYI
   -rw-rw---- 1 root root 9806 2004-06-02 07:55 user.frm
   -rw-rw---- 1 root root 168 2004-06-02 07:55 user.MYD
   -rw-rw---- 1 root root 2048 2004-06-02 07:55 user.MYI

   Inside mysql_install_db is the following:
  # Disabled the following message, since it is recommended to use the
  # init script instead, which takes care using the correct user id etc.
  # Start it with "rcmysql start" instead.

   Wellll, I tried it anyway. Both from my home area and from root. In my area it couldn't find the command. In root, it tried and failed. But we are getting closer. The message in the logfile says that it couldn't find ./mysql/host.frm. Now as you can see above, it is there. Hmmmm... let's see now. When in root I was still in my home dir. Let's see what happens if I change to /var/lib/mysql and try it. Same thing... it failed cause it couldn't find host.frm.   Ok now, Why??  

   Well, the blurb above says that the init script takes care of everything... which is at start-up. I thought that rcmysql was essentially running the same code. Evidently not or there is something else that I am missing. The paths are not getting setup. So let's break down and do a restart, like we do in Windows, and see if things come together.


   Didn't even try to start at boot-up and when I tried rcmysql it failed. Again because it couldn't find host.frm. So somewhere the path information is not being set... at least not correctly. Ok now, it looks like we are trying to use the InnoDB version.
   Arrgg!! Arrrggg!   Permissions!!!
Oh what a pain in the ?@?# this has been. I changed owner/group to mysql/daemon for all the files and dirs concerned and the mysql DB started and stayed running!! I started it with:
    mysqld_safe --user=mysql &
  response was:
    Starting mysqld-max daemon with databases from /var/lib/mysql

Now my biggest gripe in all this is that, the error message said it couldn't find the file, when really the problem was with permissions! Sometimes these error messages can be awfully misleading.
So, all the files and /var/lib/mysql dirs listed above with root/root ... change them!

(Leave the /usr ones alone.)

As mentioned above, this all changed in SuSE 9.2. The initial creation came out owned by mysql. Both owner and group. I was able to skip a lot of the above "fixes" cause it came out correctly. After the creation I was able to start mysql without any troubles.

   One of the tests to see if it is running is mysqladmin version and mysqladmin variables. I executed the commands a received a proper response for both. Hoorray!!

   Allllrighty now. How do we properly shut it down?? Well, that is also pretty simple:
  mysqladmin -u root shutdown
was executed and "mysqld ended" appeared.
So, we can now start and stop MySQL on my PC. Now I will be able to practice MySQL on my PC and then translate the tables into a DB up on my WebSite.

   Ooops. I still need to see if it will start at boot-up .. or not. I really don't want it to start at boot up cause I won't always be using it and I also like to run Oracle. Don't need both at the same time. ... Alright. I re-booted and MySQL didn't start up.

   Ok. The DB is setup and apparently working. Now all I have to do is figure out what I am going to use it for. In order to start it I must be signed on as root and execute:

      mysqld_safe --user=mysql &

This in turn executes:

      /usr/sbin/mysqld-max --basedir=/usr --datadir=/var/lib/mysql --user=mysql
        --pid-file=/var/lib/mysql/ --skip-locking
        --port=3306 --socket=/var/lib/mysql/mysql.sock

Notice that this was started in root BUT not as root. The owner is mysql. This is for security purposes. If the daemon is not owned by root, the ability of bad folks to gain access to the server is limited.

A Customer DB

   One of the items a DataBase can be used for is tracking customers. I have a few that I have serviced and I believe a nice little DB would help me remember what they have and what I did.
Customer DB Creation



Valid HTML 4.01! Click here to validate current page. Best viewed with ANY browser! Valid CSS! Click here to validate current CSS.

Copyright © 2004-2004 All rights reserved.