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 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
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
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.
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;
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.