Techniques — Database basics
Back to techniques
http://pobox.com/~rudolf/techniques/linuxnet.html

Based on Ubuntu 10.04 (a variety of Debian Linux).

MySQL

MySQL installation

sudo apt-get install mysql-server mysql-client mysql-navigator mysql-admin

It'll ask for a root password during installation.

Configuration is in /etc/mysql/my.cnf (and /etc/mysql/conf.d/). Logs are in /var/log/mysql/. Data is by default in /var/lib/mysql/ (so a database called "xxx" becomes a directory of that name there, probably with data elsewhere in /var/lib/mysql/ too).

Connecting to MySQL

Logging in and saying hello:

# log in, prompting for a password (you don't need to specify the --host or --port options for a local login with default settings)
# Also: -u and -p are shorthand for --user and --password:

mysql --host=127.0.0.1 --port=3306 --user=root --password
# Now, let's enter some SQL commands:

SHOW DATABASES;
USE database;
SHOW TABLES;
SHOW CREATE TABLE table;
SELECT * FROM table;

# etc...

help
help contents
status
quit

Creating a database and changing the root password:

CREATE DATABASE newdb;
GRANT ALL ON newdb.* TO root@'myhostname' IDENTIFIED BY 'newpassword';

MySQL backup/restore

To dump all databases:

mysqldump -u username -ppassword --all-databases > backupfile.sql

To dump one database:

mysqldump -u username -ppassword databasename > backupfile.sql

As above, can use just -p for a live prompt for the password.

To import from a multi-database dump (which will contain CREATE DATABASE commands):

mysql -u username -p[password] < backupfile.sql

To import from a single-database dump (which will not):

mysql -u username -p[password] databasename < backupfile.sql

MySQL database copy

mysqldump -u username -ppassword db1name > db.sql
echo "CREATE DATABASE db2name;" | mysql -u username -ppassword
mysql -u username -ppassword db2name < db.sql

SQL

See SQLZoo.net, an excellent interactive SQL tutorial.

In MySQL, you can add user-defined functions, including user-defined aggregate functions (aggregate UDFs), such as MEDIAN(). See http://mysql-udf.sourceforge.net/; http://sourceforge.net/projects/mysql-udf/forums/forum/390115/topic/3698697; http://www.mooreds.com/wordpress/archives/376.

Specifically, that MEDIAN() one... download udf_median.cc. Then do this:

gcc -I /usr/include/mysql -I /usr/include -o udf_median.o -c udf_median.cc
# this will need the MySQL header files... on Ubuntu, probably from package "libmysqlclient-dev"
ld -shared -o udf_median.so udf_median.o
sudo cp udf_median.so /usr/lib/mysql/plugin/
mysql [options]

then im MySQL:

CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';

If you need to drop it:

DROP FUNCTION median;

Now you can use it:

SELECT MEDIAN(field) FROM TABLE... ;

UnixODBC

The most supported ODBC standard on Linux, it seems.

UnixODBC installation

# install unixodbc
sudo apt-get install unixodbc

# print config information
odbcinst -j

# query the drivers installed
odbcinst -q -d

# query the data sources available
odbcinst -q -s

Getting UnixODBC to talk to MySQL

# http://www.unixodbc.org/unixODBCsetup.html

sudo apt-get install libmyodbc
sudo ODBCConfig &
	Drivers > Add
		Name = myodbc
		Description = MySQL ODBC (myodbc) driver
		Driver = /usr/lib/odbc/libmyodbc.so
		Driver64 =
		Setup = /usr/lib/odbc/libodbcmyS.so
		Setup64 =
		UsageCount = [... leave... is 1]
		CPTimeout =
		CPReuse =

... which writes this stuff to /etc/odbcinst.ini

Now a specific data source:

ODBCConfig &
	User DSN > Add > pick the "myodbc" driver
	specify server (e.g. 127.0.0.1), port (e.g. 3306), database - plus a short name and description

ODBCConfig writes to ~/.odbc.ini — or sudo it for system-wide DSNs, in /etc/odbc.ini.

Specifying a source manually in these files:

[tim1]
Description 	= Tim Composite Exp1
Driver		= myodbc
Server		= 127.0.0.1
Port		= 3306
Database	= junk1
User		= XXX_MYSQLUSER
Password	= XXX_MYSQLPASSWORD

Getting UnixODBC to talk to Microsoft Access (.MDB) files

This is not a terribly advanced connection. Don't use it for anything serious; convert the .MDB to another database instead (see below).

First, add the driver:

# http://mdbtools.sourceforge.net/

sudo apt-get install mdbtools libmdbtools libmdbodbc
sudo ODBCConfig &
	Drivers > Add
		Name = mdb
		Description = MDB Tools (Microsoft Access format) ODBC driver
		Driver = /usr/lib/libmdbodbc.so.0
		Driver64 =
		Setup =
		Setup64 =
		UsageCount = [... leave... is 1]
		CPTimeout =
		CPReuse =

Now a specific data source. ODBCConfig doesn't like it (as there's no Setup library; it says "cannot construct a property list"). So edit ~/.odbc.ini (or /etc/odbc.ini for a system-wide data source), e.g.

[tim1]
Description = Tim Composite Exp1
Driver = mdb
Database = /home/rudolf/tmp/Tim_Composite_Exp1.mdb

Testing the database connections

Converting Microsoft Access (.MDB) files to SQL/MySQL

Valid HTML 4.01 Transitional
Valid CSS