Linux: MySQL Database

An introduction to installing and configuring a MySQL database on a Linux system

What is MySQL?


MySQL is a free open source database that is probably the most widely used in the world. MySQL is a RDBMS database (Relational Database Management System). MySQL was named after its creators daughter "My". The "SQL" stands for Structured Query Language. MySQL is widely used as part of what is known as a "LAMP" installation. "LAMP" stands for "Linux", "Apache", "MySQL", and "PHP". Although MySQL is free, there are a few paid for options from its owner Oracle. Many of the web sites on the internet use MySQL in some shape or form. Most distributions of Linux will come with MySQL in their repositories and can be very easily installed.



Installing MySQL


The following examples have been carried out using MySQL installed on a openSUSE installation.

First lets check to see if MySQL is installed:



linux-j2w3:~ # zypper se MySQL
Loading repository data...
Reading installed packages...

S | Name                                 | Summary                                          | Type      
--+--------------------------------------+--------------------------------------------------+-----------
  | calligra-kexi-mysql-driver           | Database Application - MySQL driver              | package   
  | collectd-plugin-mysql                | MySQL Monitoring Plugin for collectd             | package   
  | courier-authlib-mysql                | MySQL support for the Courier authentication l-> | package   
  | dovecot21-backend-mysql              | MySQL support for Dovecot                        | package   
  | go-GoMySQL                           | MySQL client library written completely in Go    | package   
  | go-GoMySQL-doc                       | API documenation                                 | package   
  | icinga-idoutils-mysql                | IDOUtils drivers for MySQL                       | package   
  | libapr-util1-dbd-mysql               | DBD driver for MySQL                             | package   
  | libcppdb_mysql0                      | MySQL database connector for the cppdb library   | package   
  | libdbi-drivers-dbd-mysql             | MySQL driver for libdbi                          | package   
  | libgda-3_0-mysql                     | MySQL Provider for GNU Data Access (GDA)         | package   
  | libgda-5_0-mysql                     | MySQL Provider for GNU Data Access (GDA)         | package   
  | libmysql55client18                   | Shared Libraries for MySQL Community Server      | package   
  | libmysql55client_r18                 | Shared Libraries for MySQL Community Server      | package   
  | libmysqlclient-devel                 | MariaDB development header files and libraries   | package   
i | libmysqlclient18                     | Shared Libraries for MariaDB                     | package   
  | libmysqlclient_r18                   | Shared Libraries for MariaDB                     | package   
  | libmysqlcppconn-devel                | Development files for MySQL Connector/C++        | package   
  | libmysqlcppconn5                     | MySQL Connector/C++: Standardized database dri-> | package   
  | libmysqlcppconn6                     | MySQL database connector for C++ (mimics JDBC -> | package   
  | libmysqld-devel                      | MariaDB embedded server development files        | package   
i | libmysqld18                          | MariaDB embedded server development files        | package   
i | libqt4-sql-mysql                     | Qt 4 MySQL support                               | package   
i | libreoffice-base-drivers-mysql       | MySQL Database Driver for LibreOffice            | package   
  | lighttpd-mod_mysql_vhost             | MySQL based virtual hosts (vhosts) module for -> | package   
  | mysql-community-server               | Server part of MySQL Community Server            | package   
  | mysql-community-server-bench         | Benchmarks for MySQL Community Server            | package   
  | mysql-community-server-client        | Client for MySQL Community Server                | package   
  | mysql-community-server-debug-version | MySQL Community Server with debug options turn-> | package   
  | mysql-community-server-errormessages | MySQL Community Server development header file-> | package   
  | mysql-community-server-test          | Testsuite for MySQL Community Server             | package   
  | mysql-community-server-tools         | MySQL Community Server tools                     | package   
  | mysql-connector-c++-devel            | Files for Developing with mysql-connector-c++    | package   
  | mysql-connector-java                 | Official JDBC Driver for MySQL                   | package   
  | mysql-workbench                      | A MySQL visual modeling tool                     | package   
  | mysql-workbench                      | A MySQL visual modeling tool                     | srcpackage
  | nagios-plugins-mysql                 | Test a MySQL DBMS                                | package   
  | pdns-backend-mysql                   | MySQL backend for pdns                           | package   
  | perl-DBD-mysql                       | MySQL driver for the Perl5 Database Interface -> | package   
  | perl-DateTime-Format-MySQL           | Parse and format MySQL dates and times           | package   
  | php5-mysql                           | PHP5 Extension Module                            | package   
  | php5-pear-MDB2_Driver_mysql          | MySQL MDB2 driver                                | package   
  | php5-pear-MDB2_Driver_mysqli         | MySQLi MDB2 driver                               | package   
  | postfix-mysql                        | Postfix plugin to support MySQL maps             | package   
  | proftpd-mysql                        | MySQL Module for ProFTPD                         | package   
  | python-mysql                         | An Interface to the Popular MySQL Database Ser-> | package   
  | python-storm-mysql                   | MySQL backend for the Storm ORM                  | package   
  | qt3-mysql                            | MySQL Plug-In for Qt                             | package   
  | rsyslog-module-mysql                 | MySQL support module for rsyslog                 | package   
  | rubygem-mysql                        | This is the MySQL API module for Ruby            | package   
  | rubygem-mysql2                       | A simple, fast Mysql library for Ruby, binding-> | package   
  | rubygem-mysql2-doc                   | RDoc documentation for mysql2                    | package   
  | rubygem-mysql2-testsuite             | Test suite for mysql2                            | package   
  | strongswan-mysql                     | OpenSource IPsec-based VPN Solution              | package   
  | ulogd-mysql                          | MySQL output target for ulogd                    | package  

From the above we can see that we have MySQL available. For the following example we will use the "mysql-community-server"


Installing mysql-community-server


To install our MySQL server, we can use the command: zypper in mysql-server-community



linux-j2w3:~ # zypper in mysql-community-server
Loading repository data...
Reading installed packages...
Resolving package dependencies...

Problem: mysql-community-server-5.5.30-1.1.1.i586 conflicts with namespace:otherproviders(mysql) provided by mariadb-5.5.29-1.1.1.i586
 Solution 1: Following actions will be done:
  deinstallation of mariadb-5.5.29-1.1.1.i586
  deinstallation of mariadb-client-5.5.29-1.1.1.i586
 Solution 2: do not install mysql-community-server-5.5.30-1.1.1.i586

Choose from above solutions by number or cancel [1/2/c] (c): 1
Resolving dependencies...
Resolving package dependencies...

The following NEW packages are going to be installed:
  mysql-community-server mysql-community-server-client mysql-community-server-errormessages 

The following packages are going to be REMOVED:
  mariadb mariadb-client 

3 new packages to install, 2 to remove.
Overall download size: 7.7 MiB. After the operation, 17.8 MiB will be freed.
Continue? [y/n/?] (y): y
Retrieving package mysql-community-server-errormessages-5.5.30-1.1.1.i586
                                                                   (1/3), 187.1 KiB (  1.5 MiB unpacked)
Retrieving: mysql-community-server-errormessages-5.5.30-1.1.1.i586.rpm ...........................[done]
Retrieving package mysql-community-server-client-5.5.30-1.1.1.i586 (2/3), 759.3 KiB ( 17.0 MiB unpacked)
Retrieving: mysql-community-server-client-5.5.30-1.1.1.i586.rpm ..................................[done]
Retrieving package mysql-community-server-5.5.30-1.1.1.i586        (3/3),   6.8 MiB ( 40.4 MiB unpacked)
Retrieving: mysql-community-server-5.5.30-1.1.1.i586.rpm .............................[done (2.4 MiB/s)]
(1/5) Removing mariadb-5.5.29-1.1.1 ..............................................................[done]
Additional rpm output:
redirecting to systemctl  stop mysql


(2/5) Removing mariadb-client-5.5.29-1.1.1 .......................................................[done]
(3/5) Installing: mysql-community-server-errormessages-5.5.30-1.1.1 ..............................[done]
(4/5) Installing: mysql-community-server-client-5.5.30-1.1.1 .....................................[done]
(5/5) Installing: mysql-community-server-5.5.30-1.1.1 ............................................[done]
Additional rpm output:
usermod: no changes



Starting MySQL


To start our installation, we need to issue the following "systemctl" commands: systemctl start mysql



linux-j2w3:~ # systemctl start mysql
linux-j2w3:~ # systemctl status mysql
mysql.service - LSB: Start the MySQL database server
	  Loaded: loaded (/etc/init.d/mysql)
	  Active: active (running) since Mon, 2013-06-03 21:59:26 BST; 6s ago
	 Process: 29822 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
	 Process: 30786 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
	  CGroup: name=systemd:/system/mysql.service
		  ├ 30875 /bin/sh /usr/bin/mysqld_safe --mysqld=mysqld --user=mysql --pid-file=/var/...
		  └ 31290 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr...

Jun 03 21:59:26 linux-j2w3.site mysql[30786]: Starting service MySQL ..done
Jun 03 21:59:26 linux-j2w3.site systemd[1]: Started LSB: Start the MySQL database server.

Securing MySQL server - setting passwords


By default our MySQL installation has no passwords set. To rectify this, we can run a special script called:

mysql_secure_installation

By running this script, the following will be accomplished:



  • set a MySQL root password
  • remove anonymous users
  • disallow MySQL root login remotely
  • remove test databases
  • reload privileges table

Make sure that the MySQL server is running (check with systemctl status mysql as described above before running this script.



linux-j2w3:~ # mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

If all has gone well, you should now be able to login with the "root" database account along with the password you created for this. The example below will guide you through a simple creation of a MySQL database:


MySQL Database Example



linux-j2w3:~ # mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.30-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> help

Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

Login to MySQL with user "root"


If you haven't already logged in, then issue the following commands:



linux-j2w3:~ # mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.30-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Create Database


To create our database, we use the command "create database" followed by the name for our new database. Note commands are terminated with a ";



mysql> create database tuxdb01;
Query OK, 1 row affected (0.00 sec)

Specify database to work with using the command "use" followed by the database name



mysql> use tuxdb01;
Database changed

Create a Table



mysql> CREATE TABLE distro (
    -> id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    -> distro_name VARCHAR(100),
    -> pkg_manager VARCHAR(100),
    -> PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

Adding Data into our Table "distro"



mysql> INSERT into distro
    -> (distro_name, pkg_manager)
    -> VALUES
    -> ("Ubuntu", "dbpg");
Query OK, 1 row affected (0.00 sec)

Displaying Table Data


Our data can be displayed easily by issuing a "SELECT" statement followed by the table we wish to display



mysql> SELECT id, distro_name, pkg_manager from distro;
+----+-------------+-------------+
| id | distro_name | pkg_manager |
+----+-------------+-------------+
|  1 | Ubuntu      | dbpg        |
+----+-------------+-------------+
1 row in set (0.00 sec)

Altering an Entry


Don't worry, If you need to change any of the data entered, you can change this with the following:



mysql> UPDATE distro set
    -> pkg_manager = "dpkg"
    -> WHERE id = "1";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now we can display our table information again:



mysql> SELECT id, distro_name, pkg_manager from distro;
+----+-------------+-------------+
| id | distro_name | pkg_manager |
+----+-------------+-------------+
|  1 | Ubuntu      | dpkg        |
+----+-------------+-------------+
1 row in set (0.00 sec)

Displaying an Overview of our table


We can use the "describe" command for a quick overview of our table:



mysql> describe distro;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| distro_name | varchar(100)     | YES  |     | NULL    |                |
| pkg_manager | varchar(100)     | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Display Tables within a Database


To display Tables currently defined within your database, specify the "show tables" command:



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

Adding more data



mysql> insert into distro (distro_name,pkg_manager) VALUES ("RHEL", "rpm");     
Query OK, 1 row affected (0.00 sec)

mysql> insert into distro (distro_name,pkg_manager) VALUES ("SLES", "rpm");
Query OK, 1 row affected (0.00 sec)

mysql> insert into distro (distro_name,pkg_manager) VALUES ("Debian", "dpkg");
Query OK, 1 row affected (0.00 sec)

mysql> insert into distro (distro_name,pkg_manager) VALUES ("CentOS", "rpm");   
Query OK, 1 row affected (0.00 sec)

mysql> insert into distro (distro_name,pkg_manager) VALUES ("Fedora", "rpm");   
Query OK, 1 row affected (0.00 sec)

mysql> insert into distro (distro_name,pkg_manager) VALUES ("MINT", "dpkg");
Query OK, 1 row affected (0.00 sec)

Displaying our entries so far



mysql> SELECT id, distro_name, pkg_manager from distro;
+----+-------------+-------------+
| id | distro_name | pkg_manager |
+----+-------------+-------------+
|  1 | Ubuntu      | dpkg        |
|  2 | RHEL        | rpm         |
|  3 | SLES        | rpm         |
|  4 | Debian      | dpkg        |
|  5 | CentOS      | rpm         |
|  6 | Fedora      | rpm         |
|  7 | MINT        | dpkg        |
+----+-------------+-------------+
7 rows in set (0.00 sec)

Summary of our Entries


To get a summary of our entries, we can use the "select count" command.



mysql> select count(pkg_manager),pkg_manager from distro GROUP BY pkg_manager;
+--------------------+-------------+
| count(pkg_manager) | pkg_manager |
+--------------------+-------------+
|                  3 | dpkg        |
|                  4 | rpm         |
+--------------------+-------------+
2 rows in set (0.00 sec)

Sorting Alphabetically


To sort our entries into Alphabetical order, we can use the "ORDER BY" statement:



mysql> select id,distro_name,pkg_manager from distro ORDER BY distro_name asc;
+----+-------------+-------------+
| id | distro_name | pkg_manager |
+----+-------------+-------------+
|  5 | CentOS      | rpm         |
|  4 | Debian      | dpkg        |
|  6 | Fedora      | rpm         |
|  7 | MINT        | dpkg        |
|  2 | RHEL        | rpm         |
|  3 | SLES        | rpm         |
|  1 | Ubuntu      | dpkg        |
+----+-------------+-------------+
7 rows in set (0.00 sec)

Display Databases


As we saw earlier, we can issue the show tables command to display table information. To view our database, we use the command "show databases;"



mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| john               |
| mysql              |
| test               |
| tuxdb              |
| tuxdb01            |
+--------------------+
6 rows in set (0.00 sec)

phpMyAdmin


phpMyAdmin is a popular graphical interface that is used for managing MySQL databases. For instructions on how to install this tool, click the link above or click: phpMyAdmin


Further help with MySQL


For developer information, including the MySQL Reference Manual, visit: MySQL Reference

Or visit our Linux Book store and search for "MySQL": Linux Books