Setting up MySQL when you are an ordinary user

Here's my notes for getting MySQL setup on a Linux machine when you are an ordinary user (i.e. not root). I typed these up because

So these are my notes.

Caveat

Since these notes are primarily for my own use, I have made not particular attempt to make them beautiful or coherent for anyone but me. If they are useful to you, great. If you have comments or questions, about these instructions, send me email if you like. But, unless you are one of my current students, understand, that I might or might not respond at all. Please don't be upset if you don't hear from me.

Unloading the tarball; setting up links

I created a directory /jaguar/cisc474 ; that will be my base directory.  

I then downloaded mysql-standard-4.1.10-pc-linux-gnu-i686.tar.gz and exploded it inside that directory.

That left me with /jaguar/cisc474/mysql-standard-4.1.10-pc-linux-gnu-i686.  I then made a symbolic link /jaguar/cisc474/mysql to point to that directory.

jaguar[78] > ln -s mysql-standard-4.1.10-pc-linux-gnu-i686 mysql
jaguar[79] >

Running the Configure Script

Note that the configure script indicates that there is no configuration or installation necessary in the sense of copying things to some other target directory; what you see is what you get.  However, running the configure script does set up a few things.  There are more "post installation things" you have to do that are described in the documentation that comes with MySQL, like changing the root password, setting up users, etc. etc. It also starts up the server for the first time.

cd /jaguar/cisc474/mysql
./configure

Here's the output I got:

jaguar[80] > ./configure
NOTE: This is a MySQL binary distribution. It's ready to run, you don't
need to configure it!

To help you a bit, I am now going to create the needed MySQL databases
and start the MySQL server for you. If you run into any trouble, please
consult the MySQL manual, that you can find in the Docs directory.

Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h jaguar.cis.udel.edu password 'new-password'
See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

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 ./bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
Starting the mysqld server. You can test that it is up and running
with the command:
./bin/mysqladmin version
jaguar[81] > Starting mysqld daemon with databases from /jaguar/cisc474/mysql/data

Then do the following to make sure the server is running:

jaguar[81] > ./bin/mysqladmin version
./bin/mysqladmin Ver 8.41 Distrib 4.1.10, for pc-linux-gnu on i686
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 4.1.10-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 25 min 58 sec

Threads: 1 Questions: 1 Slow queries: 0 Opens: 11 Flush tables: 1 Open tables: 0 Queries per second avg: 0.001
jaguar[82] >

So far, so good.

Post installation configuration

You can also find the manual in docs/manual.txt, or at http://dev.mysql.com/doc/mysql/en/index.html

We are going to pick up with section 2.9.2, Unix Post Installation by trying to shut down the server.

cd /jaguar/cisc474l/mysql
./bin/mysqladmin -u root shutdown

Then step 6: verify that we can restart. I'm omitting the --user=mysql that's in the instructions since I'm not running as root.

cd /jaguar/cisc474/mysql
./bin/mysqld_safe --log &

Looks ok so far...

"One time, at MySQL camp..."

One time when I tried this, I had terrible problems. I would always get the same error. The log file excerpt below shows that I was shutting down the server correctly (and I verified that no other mysqld process was running on the system.) Nevertheless, I would get this error:

...

050308 13:09:13 InnoDB: Started; log sequence number 0 0
/usa/pconrad/local/mysql/bin/mysqld: ready for connections.
Version: '4.1.9-standard' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-s
tandard binary
050308 13:11:18 [Note] /usa/pconrad/local/mysql/bin/mysqld: Normal shutdown

050308 13:11:18 InnoDB: Starting shutdown...
050308 13:11:21 InnoDB: Shutdown completed; log sequence number 0 43634
050308 13:11:21 [Note] /usa/pconrad/local/mysql/bin/mysqld: Shutdown complete

050308 13:11:21 mysqld ended

050308 13:12:09 mysqld started
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
...

The following seems to have fixed it.. I startd up mysqld without the innodb stuff.

jaguar[299] > ./bin/mysqld_safe --log --skip-innodb

To be honest, I don't yet know what the implications of that are. As best I can figure, it meanst that I wont get certain advanced features that seem to be most applicable to large scale commerical enterprises... stuff I probably won't need for an academic course. Anyway, the error went away after I move to a disk with more space, so I'm suspecting I was running into disk quota problems and was coruppting the integrity of my installation somehow because of that.

So now my shutdown and startup commands are:

startup:

cd /jaguar/cisc474/mysql
./bin/mysqld_safe --log &

previously, I used:

./bin/mysqld_safe --log --skip-innodb &

shutdown:

cd /jaguar/cisc474/mysql
./bin/mysqladmin -u root shutdown

Changing the port number

I added a file "my.cnf" under /jaguar/cisc474/mysql/data as follows:

jaguar[52] > cat > /jaguar/cisc474/mysql/data/my.cnf
[mysqld]
#set basedir to your installation path
basedir=/jaguar/cisc474/mysql
datadir=/jaguar/cisc474/mysql/data
port=8099

After doing this, I tried shutting down and restarting again.

cd /jaguar/cisc474/mysql
./bin/mysqladmin -u root shutdown
./bin/mysqld_safe --log &

At first I had a syntax error, and the restart didn't take. So I fixed it and then in worked.

Continuing with the post-install steps

Continue with step 2.9.3 "Securing the initial MySQL Accounts".

The first thing to do is add a password for the root account.

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

This allows root logins only from localhost and the specific "host_name" mentioned here.

I then removed the anonymous accounts with:

shell> mysql -u root -p
Password: xxxxxxx
mysql> DELETE FROM mysql.user WHERE User = '';
mysql> FLUSH PRIVILEGES;

Adding a privleged user:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

I did this command to create an administrator account for my TA (e.g. janeTA). I didn't use the % which is the wildcard for hostnames (remember that the "key" for the "user" table in the database "mysql" is both the Host and User fields together). Instead, I created three rows, for the three separate hosts from which she logs in.

Adding regular users:

See:

 

Here are commands to add two regular users to the database. Note that we create a database with that userid first.

CREATE DATABASE IF NOT EXISTS pconrad ;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON pconrad.* TO 'pconrad'@'localhost' IDENTIFIED BY 'xxxxx';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON pconrad.* TO 'pconrad'@'%.cis.udel.edu' IDENTIFIED BY 'xxxxx';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON pconrad.* TO 'pconrad'@'strauss.udel.edu' IDENTIFIED BY 'xxxxx';

CREATE DATABASE IF NOT EXISTS gibson ;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON gibson.* TO 'gibson'@'localhost' IDENTIFIED BY 'xxxxx';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON gibson.* TO 'gibson'@'%.cis.udel.edu' IDENTIFIED BY 'xxxxx';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON gibson.* TO 'gibson'@'strauss.udel.edu' IDENTIFIED BY 'xxxxx';

If these commands are in a file called "makeusers.sql", you can execute it with:

> /jaguar/cisc474/mysql/bin/mysql -u root -p < makeusers.sql
password: pppppppp

Here is a shell script that can do the job in bulk. Note the quoting of the quote and semicolon characters (via \' and \;). This script sends sql commands to standard output.

#!/bin/sh
#addUsersToMySQL.sh P. Conrad Spring 2006

makeUser()
{
user=$1
pass=$2
host1=%.cis.udel.edu
host2=strauss.udel.edu

echo CREATE DATABASE IF NOT EXISTS $user \;
echo GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON $user.* TO \'$user\'@\'localhost\' IDENTIFIED BY \'$pass\'\;
echo GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON $user.* TO \'$user\'@\'$host1\' IDENTIFIED BY \'$pass\'\;
echo GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON $user.* TO \'$user\'@\'$host2\' IDENTIFIED BY \'$pass\'\;

}

# loop through users

makeUser pconrad xxxxx
makeUser gibson yyyyy

Basic Commands to get oriented

show databases;
use database;
show tables;
show columns from table;


Listing users (from administrator account)

use mysql;
select User,Host from user;

Here is a sample interaction with the database:

jaguar[58] > bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.1.10-standard-log

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

mysql> show databases;
+----------+
| Database |
+----------+
| jsample |
| mysql |
| test |
+----------+
3 rows in set (0.12 sec)

mysql> use mysql;
'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> show Host,User from user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Host,User from user' at line 1
mysql> select Host,User from user;
+---------------------+---------+
| Host | User |
+---------------------+---------+
| % | gibson |
| % | jsample |
| jaguar.cis.udel.edu | |
| jaguar.cis.udel.edu | root |
| localhost | |
| localhost | gibson |
| localhost | jsample |
| localhost | root |
+---------------------+---------+
8 rows in set (0.02 sec)

mysql> exit
Bye
jaguar[59] >

That's it for now...


Spring 2006

Using version 5.0.18

Some changes:

my.cnf is now located directly underneath the main ~jaguar/cisc474/mysql directory.

The contents of my.cnf are as follows. This is just what I copied from the appropriate section in the MySQL documentation about option-files , except that I changed the port to 8099, and the socket from /tmp/mysql.sock to /tmp/cisc474.mysql.sock.

[client]
port=8099
socket=/tmp/cisc474.mysql.sock


[mysqld]
port=8099
socket=/tmp/cisc474.mysql.sock
key_buffer_size=16M
max_allowed_packet=8M


[mysqldump]
quick

I also did a chmod -R 755 . on the mysql base directory, and then a chmod -R o-rx on the data directory (where passwords are stored).

However, it was NOT reading my configuration file at first. The reason was that my defiinition of the environment variable MYSQL_HOME wasn't set correctly to /jaguar/cisc474/mysql (the base directory of the MySQL installation).

Startup and Shutdown

startup:

cd /jaguar/cisc474/mysql
./bin/mysqld_safe --log &

shutdown:

cd /jaguar/cisc474/mysql
./bin/mysqladmin -u root -p shutdown
[Type in the MySQL root password].

An excel trick to generate passwords

=CHAR(RAND()*26+CODE("a")) & FLOOR(RAND()*899,1)+100 & CHAR(RAND()*26+CODE("a")) & CHAR(RAND()*26+CODE("a"))