Getting Started With MySQL

Installation

I've sucessfully installed MySQL on Linux, FreeBSD, NetBSD, OpenBSD and Solaris.

RPM-based Linux

To install MySQL on an RPM-based Linux distribution run:


yum install mysql-server mysql-devel

Or on an old RPM-based distribution that doesn't support yum, install them from the CD/DVD's using rpm -i.

Debian and Ubuntu Linux

To install MySQL on Debian and Ubuntu Linux, run:


apt-get install mysql-server libmysqlclient-dev

MySQL packages don't appear to be available for older Debian releases, so for those releases, I've always compiled it from source. Alternatively, you can download a binary distribution in tar.gz format from the MySQL site.

FreeBSD

To install MySQL on FreeBSD, run:

pkg install mysql56-server mysql56-client

(replacing 56 with the major/minor version of mysql that you want to install)

On older versions of FreeBSD, run:

pkg_add -r mysql56-server mysql56-client

You can also install MySQL from the Ports CD(s) that came with your distribution using /stand/sysinstall.

OpenBSD

To install MySQL on OpenBSD, verify that /etc/pkg.conf has a valid path in it, like:


installpath = ftp://ftp.usa.openbsd.org/pub/OpenBSD/5.6/packages/amd64/

And run:

pkg_add mysql-server mysql-client

Packages are also available from ftp.openbsd.org and on the CD's that came with your distribution and can be installed manually using pkg_add.

NetBSD

To install MySQL on NetBSD, run:

pkgin install mysql-server mysql-client

On older versions of NetBSD, verify that the PKG_PATH environment variable is configured to a valid path like:

ftp://ftp.netbsd.org/pub/pkgsrc/packages/NetBSD/i386/4.0.1_2012Q3/All

And run:

pkg_add mysql-server myql-client

Packages are also available from ftp.netbsd.org and on the CD's that came with your distribution and can be installed manually using pkg_add.

Solaris

MySQL packages are available on the Solaris Companion CD and are downloadable from many web sites including the MySQL site. You can use pkgtool to install the package. Note, that with some versions of Solaris and some versions of MySQL, you have to create a "wheel" group before installing the MySQL package. You can do this (as root) using the command: /usr/sbin/groupadd wheel

Compiling From Source

If you want to compile MySQL from source, it should compile cleanly on all of the platforms mentioned above. The source code is available from the MySQL site. With older versions of MySQL, I've run out of memory while compiling on machines with less than 64MB of ram. I don't believe that this is still a problem though. I usually give the configure script the --prefix=/usr/local/mysql parameter so that MySQL will be installed entirely under /usr/local/mysql. I then add /usr/local/mysql/bin to my PATH environment variable and /usr/local/mysql/lib/mysql to my LD_LIBRARY_PATH environment variable.

Starting the Database at Boot Time

The package distributions of MySQL either install a script which starts the database at boot time or use systemd.

If you compiled from source on a platform that supports init-style system initialization, you'll need to install a script like one of the following to start the database at boot time.

For MySQL 4 and higher:


#!/bin/sh

case "$1" in
        start)
                if ( test ! -d "/usr/local/mysql/var" ); then
                        mkdir -p /usr/local/mysql/var/mysql
                fi
                if ( test ! -d "/usr/local/mysql/var/log" ); then
                        mkdir -p /usr/local/mysql/var/log
                fi
                if ( test ! -d "/usr/local/mysql/var/mysql" ); then
                        /usr/local/mysql/bin/mysql_install_db \
                                --datadir=/usr/local/mysql/var
                fi
                chown -R mysql.mysql /usr/local/mysql/var
                chmod 0755 /usr/local/mysql/var
                chmod 0755 /usr/local/mysql/var/log
                /usr/local/mysql/bin/mysqld_safe \
                        --datadir=/usr/local/mysql/var \
                        --err-log=/usr/local/mysql/var/log/mysqld.log \
                        --log=/usr/local/mysql/var/log/mysqld.log \
                        --socket=/tmp/mysql.sock
                ;;
        stop)
                kill `ps -efa | grep mysql | grep -v grep | awk '{print $2}'`
                ;;
        *)
                echo $"Usage: $0 {start|stop}"
                exit 1
esac

exit 0

For MySQL 3:


#!/bin/sh

case "$1" in
        start)
                if ( test ! -d "/usr/local/mysql/var/mysql" ); then
                        /usr/local/mysql/bin/mysql_install_db
                fi
                chown -R mysql.mysql /usr/local/mysql/var
                chmod 0755 /usr/local/mysql/var
                /usr/local/mysql/bin/safe_mysqld
                ;;
        stop)
                kill `ps -efa | grep mysql | grep -v grep | awk '{print $2}'`
                ;;
        *)
                echo $"Usage: $0 {start|stop}"
                exit 1
esac

exit 0

Install this script and run it with the "start" option to start up the database. Running it with the "stop" option shuts the database down. To access a database, it must be running.

Creating a Database

After installation, MySQL is ready to use but to do any useful work, you'll have to create a database.

The installation process creates a database named mysql containing privileges and other housekeeping information and a root user which has no password.

The following commands give the root user the password Passw0rd.


mysql -uroot -e "update user set password=password('Passw0rd') where user='root'" mysql
mysqladmin -uroot reload

Though you can create tables in the mysql database, it's not a good idea. You should create a new database. The following command creates a database called testdb.


mysqladmin -uroot -pPassw0rd create testdb

To create a user, log into the mysql database using the following command.


mysql -uroot -pPassw0rd mysql

On modern versions of mysql, the following queries create a user called testuser with password testpassword and allows it to log in from the local machine or any remote host.


create user 'testuser'@'localhost' identified by 'testpassword'
create user 'testuser'@'%' identified by 'testpassword'

On sufficiently old versions of mysql, you have to insert rows directly into the user table:


insert into user (host,user,password) values ('localhost','testuser',password('testpassword'));
insert into user (host,user,password) values ('%','testuser',password('testpassword'));

Once the user is created, it must be given database-specific priveleges.

On modern versions of mysql, the following queries grant testuser all privileges on the testdb database.


grant all privileges on testdb.* to 'testuser'@'%'

On sufficiently old versions of mysql, you have to insert rows directly into the db table:


insert into db values ('%','testdb','testuser','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

Different versions of MySQL have different numbers of columns in the db table, if the query above fails, run:.


desc db

Then count the number of columns with Type enum('N','Y') and re-run the query above using that number of 'Y' values.

Exit the mysql client and run the following command to activate these changes.


mysqladmin -uroot -pPassw0rd reload

To delete a user, log into the mysql database using the following command.


mysql -uroot -pPassw0rd mysql

On modern versions of mysql, the following queries drop the user testuser.


drop user 'testuser'@'localhost';
drop user 'testuser'@'%';

On sufficiently old versions of mysql, you have to delete rows directly from the user and db tables.


delete from user where user='testuser';
delete from db where user='testuser';

Exit the mysql client and run the following command to activate these changes.


mysqladmin -uroot -pPassw0rd reload

If you want to drop the database, you can do so with the following command.


mysqladmin -uroot -pPassw0rd drop testdb

This should be enough to get you started. To set up more complex configurations, consult the MySQL online documentation.

Accessing a Database

Accessing a MySQL database using the mysql client tool is simple. For example, to access a database called testdb on the local machine as the testuser user with password testpassword, use the following command.


mysql -utestuser -ptestpassword testdb

If you want to access a database on a remote machine, say on testhost, use the -h option as follows.


mysql -htesthost -utestuser -ptestpassword testdb

Once you're connected to the database, the mysql client prompts you to enter a query. Queries may be split across multiple lines. To run a query, end it with a semicolon or type \g on the next line. To exit, type \q.

A sample mysql session follows.


[user@localhost user]$ mysql -utestuser -ptestpassword testdb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.41

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

mysql> create table testtable (
    -> col1 char(40),
    -> col2 integer 
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtable        |
+------------------+
1 row in set (0.00 sec)

mysql> describe testtable;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col1  | char(40) | YES  |     | NULL    |       |
| col2  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into testtable values ('hello',50);
Query OK, 1 row affected (0.00 sec)

mysql> insert into testtable values ('hi',60);
Query OK, 1 row affected (0.00 sec)

mysql> insert into testtable values ('bye',70);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtable;
+-------+------+
| col1  | col2 |
+-------+------+
| hello |   50 |
| hi    |   60 |
| bye   |   70 |
+-------+------+
3 rows in set (0.00 sec)

mysql> update testtable set col2=0 where col1='hi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from testtable;
+-------+------+
| col1  | col2 |
+-------+------+
| hello |   50 |
| hi    |    0 |
| bye   |   70 |
+-------+------+
3 rows in set (0.00 sec)

mysql> delete from testtable where col2=50;
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtable;
+------+------+
| col1 | col2 |
+------+------+
| hi   |    0 |
| bye  |   70 |
+------+------+
2 rows in set (0.00 sec)

mysql> drop table testtable;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

MySQL Quirks

Ancient versions of MySQL don't support transactions.

Newer, but still very old versions support foreign key constraint syntax but don't enforce the constraint.

In modern MySQL, the default isolation level is REPEATABLE READ rather than READ COMMITTED like most databases. This means that you must commit your transaction to be able to see other users' committed changes.

Subscribe to: Posts (Atom)