banner Debian GNU/Linux

Linux Database Server

mySQL for absolute beginners


This is part of a write-up of an attempt to use Linux as a "Small Business Server", and describes a simple mySQL database server setup. Setting up mysql in combination with apache and php is also a prerequisite for setting up a wiki.

install

Install mySQL database server. You may want to use apt pinning to get a quite new version. In any case, set a password for the mysql 'root' administrator ! Also read the readme to get some quick info on passwords and the first steps after installation.

	#check that mysql daemon is up and running :	
	mysqladmin -s
	(alt: mysql ->status)

	#set a root password
	/usr/bin/mysqladmin -u root password 'enter-your-good-new-password-here'

	#readme
	less /usr/share/doc/mysql-server/README.Debian
	

change default location of databases

As described, we want to use /srv as default location for data. We've already made a /srv/db directory for databases. We will now create a /srv/db/mysql directory that will hold all of our mysql databases. This is done by editing /etc/mysql/my.cnf, copying some files, and setting user permissions / ownership on the new location.

	
	# Moving the MySQL data directory.
	# Purpose : allow sysadmin to decide on which partition / in which directory the mysql database files will reside, 
	# as opposed to accepting the default /var/lib/mysql

	# Based on http://rajshekhar.net/blog/archives/90-Moving-the-MySQLs-datadir-directory.html
	############################################################################################

	# copy the files from the old datadir to the new location 
	# the -p option preserves file permissions and ownership, as it should be
	cp -rp /var/lib/mysql /srv/db/mysql

	## use vim or find/replace with sed | awk to
	## edit /etc/mysql/my.conf to point to new datadir
	##	#datadir =	/var/lib/mysql
	##	datadir = 	/srv/db/mysql

	# restart mysql
	/etc/init.d/mysql restart

	# if mysql restarted correctly, 
	# 	create a new database and check that it shows up in the correct (new) datadir
	
	# the inno transaction logs may cause trouble. For a new setup, it's save to delete them if need be
	ls -al
	rm -r ib_*
	
	# Continue configuration of mysql (if this procedure is part of your mysql setup)
	# If the mysql server appears to be running properly, delete the obsoleted files in /var/lib/mysql
	

to check that mysql survived this operation, you can execute 'mysql' and send some sql commands to check if everything is working

Basic Operations

If all you need is a database to support other applications you're planning to install, you're done. However, you may have planned to create and maintain databases of your own. In that case, it helps if you are able to perform some basic database operations to get you on your way.

For remote access to the database server, you have to configure it to listen on (a.k.a. bind to) the IP address of the server. By default, this is set to a local loopback address, but this is insufficient for access from a remote machine, e.g. if you'll be running client-server applications from a remote system.

	## my.cnf
	# bind to real address i.s.o. local loopback
	bind-address            = 192.168.0.20
	

You'll need this address (or a DNS solution) to have remote access to the database. Also remember the default port for mysql : 3309 tcp. You can change that in the configuration file.

create a (test) database

	server:~# mysql -root -p
	Enter password: 

	mysql> create database dbApptest;
	mysql> use dbApptest;				-- to operate on newly created database

	mysql> create table tblData01 (
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
			data1 VARCHAR(100),
			data2 INT
			);	
    
    -- http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html
    -- --
    
    -- populate the table
	INSERT INTO tblData01 (data1,data2) VALUES('Douglas Adams', 42 ) ;
	INSERT INTO tblData01 (data1,data2) VALUES('Hot Babe', 69 ) ;
	INSERT INTO tblData01 (data1) VALUES('Spam') ;

	-- check what we have so far
	mysql> select * from tblData01 ;

	+----+---------------+-------+
	| id | data1         | data2 |
	+----+---------------+-------+
	|  1 | Douglas Adams |    42 | 
	|  2 | Hot Babe      |    69 | 
	|  3 | Spam          |  NULL | 
	+----+---------------+-------+
	3 rows in set (0.00 sec)


	-- create a user with "READ" rights,  create a user with db admin rights

	mysql> USE mysql;		--switch to the mysql database for user management
	
	mysql> GRANT SELECT ON dbApptest.* TO 'reader'@'%' IDENTIFIED by 'ReaderPASS';
	mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP  ON dbApptest.* TO 'AppUser'@'%' IDENTIFIED by 'AppPASS';

	-- check
	mysql> select user from user ;
	+------------------+
	| user             |
	+------------------+
	| AppUser          | 
	| reader           | 
	| debian-sys-maint | 
	| root             | 
	| root             | 
	+------------------+
		

The above statements are enough to create a simple database with some data in it, e;g.to test if you can access data through remote client tools.

The database will show up as a bunch of files in a subdirectory of mysql's datadir

To remove a database : mysql> drop database TEST;

client and admin tools

you can use mysql and mysqladmin as tools to create, manage and use mysql databases, but these are command line tools and you 'll need to know some sql. While this is ok for small tasks or to execute sql scripts, you may also want some GUI stuff.

After you've installed apache and php, you can apt-get install phpmyadmin which will allow you to use a web browser (from a remote system) to manage your mysql database system and its databases. If you're going to make extensive use of mysql, you might want to look for other mysql clients and admin tools that you can install on a Windows or Linux workstation.

If all you need is a working mysql database for use with other applications (such as a LAMP server or any application that needs a database to store its data), this is already more than you need to know. If you want to access the database to retrieve the data and create reports, or want to create (simple) applications, you might want to consider using Open Office as a database front-end, or check the links at the bottom of this page.

links


Koen Noens
June 2006