MySQL: An Introduction for Oracle DBAs

You are an Oracle Database Administrator. You enjoy looking after Oracle databases and you are really good at it. In the corner of the machine room, on the edge of your peripheral vision, is a server running a database called MySQL.

You have been told that this holds the data for your company’s website and your corporate blog, all running cool, free, open-source software. A trendy, young contractor installed this stuff a while back. He told your boss that, as MySQL is open-source, light and free, it does not require the overhead of database administration, unlike big, complex Oracle. You were both impressed by and sceptical about this claim.

The contractor has long gone. The company website and the corporate blog are a big success. On the edge of your peripheral vision, that server keeps worrying you.

One day your manager calls you into his office, “Can you just have a quick look at that MySQL database server, the one with the website and the blog on it? You know, make sure it’s secure and stuff. Shouldn’t be too hard for an Oracle DBA like yourself.”

You know absolutely nothing about MySQL. Where do you start?

How do I connect to MySQL?

First logon to the server hosting MySQL, typically via ssh (let’s assume someone has at least given you access to the server, otherwise we might as well give up now). The server in this story is running linux; however most of the commands we will cover are operating system independent. Type the following:

mysql -u root

If you do not get an error message and this command seems to work, then you can already tell things are not as they should be. This means the MySQL root user (which is nothing to do with the operating system root user; it is the equivalent of Oracle’s sysdba) is not password-protected. This is the default situation and I’ll show you how to fix it in a minute.

However if there is a password for the MySQL root user and, assuming you have been told it, type the command below, followed by the password when prompted:

mysql -u root -p

You are now logged in to the mysql client (the equivalent of sqlplus) and will see a prompt, typically 'mysql>'.

What’s in this database?

Within the mysql client, invoked above, type:

show databases;

This will list the databases contained within this MySQL server. Beware databases are not equivalent to databases in Oracle; they are equivalent to Oracle schemas. One MySQL server may contain many databases. In the mysql client you can use the word schema interchangeably with the word database.

In the list of databases you will see some or all of the following standard databases: information_schema, mysql, performance_schema and test. Anything else will be a database supporting one of your own applications.

To enter one of these databases:

use database_name;

To see which tables are in this database:

show tables;

and to see more detail:

show table status;

The second column in the output of the above command is “Engine”. An unusual feature of MySQL (for those familiar with Oracle) is that it has many ways of storing data, called storage engines. The two most common storage engines are MyISAM and InnoDB. MyISAM (the default prior to MySQL 5.5) is a very basic storage engine (e.g. it has table-level locking and reads are blocked by updates). InnoDB (the default from MySQL 5.5 onwards) is more similar to Oracle, providing advanced features such as transactions, multi-version concurrency, referential integrity and row-level locking.

You can use SQL against the tables in a database as you would expect, optionally qualifying a table with its database name, i.e.

select * from table_name;

or:

select * from database_name.table_name;

Bear in mind that each command must end with a ‘;’ or ‘\G’; the latter displaying the output vertically, which is handy for tables with many columns. You will also notice, by pressing the up arrow, that the mysql client has a command history, like the bash shell.

To see the indexes on a table:

show index from table_name;

To see the full definition of a table:

show create table table_name;

Where is my data stored?

Exit out of the mysql client, back to the server’s operating system, and have a look around your MySQL base data directory, typically /var/lib/mysql:

ls -l /var/lib/mysql

You will see a directory for each database. A database’s directory contains many files of the format *.frm, which describe the structure of each table within the database. For MyISAM tables, the data (*.MYD) and indexes (*.MYI) are stored within this directory also.

InnoDB tables are stored in InnoDB tablespaces, each of which consists of one or more files, which are very similar to Oracle tablespaces. By default all InnoDB data and indexes for all databases on a MySQL server are held in one tablespace, consisting of one file: /var/lib/mysql/ibdata1. InnoDB has log files, which are the equivalent of Oracle redo logs, allowing automatic crash recovery. By default there are two log files: /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1. Undo data is held within the tablespace file.

Is there a Data Dictionary?

Since MySQL 5.0 the information_schema database has been available, containing data dictionary information. To see all the tables (actually views) contained in the data dictionary, within the mysql client:

use information_schema;
show tables;

And then query any of these tables, for example:

select * from information_schema.tables where table_schema='database_name';
select * from information_schema.views where table_schema='database_name';

Information_schema is defined in the SQL 2003 standard and is implemented by other major databases, e.g. SQL Server, PostgreSQL.

Who is connected to my MySQL server?

To see sessions currently connected to MySQL:

show processlist;

This will tell you:

• which users are connected;
• from which host;
• to which database;
• which commands they are currently executing.

Which users are defined in my MySQL server and what can they do?

This information is held in another standard MySQL database, called mysql. To see the users defined:

select * from mysql.user;

This shows all the users defined in the MySQL server, together with their global privileges.

By default root users do not have a password (as we saw in the first part of this blog post) and there is also an anonymous user with no password. To make your MySQL server more secure (create a password for root, remove the anonymous user and clear up a few other default security holes) it is recommended that you run the script below (on the operating system, not within the mysql client):

mysql_secure_installation

An important difference between MySQL and Oracle is that, in MySQL, it is the combination of user name and host that uniquely identify a user. So the user billy@server1 is a different user to billy@server2; they can have different passwords and different privileges. Also MySQL only allows a user to connect from a host defined for it in the mysql.user table. This means, for example, assuming the two users above are the only users defined with the user name billy, if someone attempts to connect to this MySQL server from server3 as the user billy, then MySQL will deny access.

To create a user:

create user webapp@server1 identified by 'apple';

Typically, as with Oracle databases these days, database users are pooled connections from an application (e.g. a content management or web server) and are given all privileges on the database which holds their data:

grant all on database_name.* to webapp@server1;

Ideally privileges can and should be granted at a lower granularity.

To see the database-level privileges for all users:

select * from mysql.db;

It is possible to create the user and grant privileges in one statement:

grant all on database_name.* to webapp@server1 identified by 'apple';

It is also possible to put a wildcard (%) into the host to allow a given user to connect from anywhere (quotes are needed around the wildcard):

create user billy@'%' identified by 'banana';

How do I start and stop MySQL?

On linux and unix MySQL is typically controlled through init.d scripts, as you might expect:

/etc/init.d/mysql start
/etc/init.d/mysql stop
/etc/init.d/mysql restart

If MySQL fails to start, check the error log, usually /var/lib/mysql/server_name.err

If you examine the running MySQL server processes (ps -ef|grep mysqld) you will see two: mysqld and mysqld_safe. The former is the MySQL server process and the latter is a process that monitors the MySQL server process and will restart it if it has failed.

Where is the MySQL Server Configuration data?

The MySQL configuration file (the equivalent of an Oracle pfile or spfile) is usually /etc/my.cnf (or my.ini on Windows). The file is divided into different sections, each of which provides configuration data for different programs. The section labelled [mysqld] controls the behaviour of the mysqld server. The section labelled [mysql] controls the behaviour of the mysql client.

Often very little is needed in this file as the default values are fine for typical installations.

How do I Backup MySQL?

The easiest way to backup MySQL is to use the program mysqldump. This creates a logical database backup, which is a text file containing the SQL commands necessary to recreate the database objects and populate the data. You need to specify the user name, the database to be backed up and a file in which to store the backup:

mysqldump -u root -p database_name > backup_file_name

By default mysqldump will make all tables read-only, preventing updates during this logical backup. If all your tables are stored in the InnoDB storage engine then it is better to apply the –single-transaction option to mysqldump, which will create a consistent backup while leaving tables open for update.

There are many other more powerful and flexible ways to backup MySQL which are beyond the scope of this introduction (e.g. file system snapshots, MySQL Enterprise Backup, Percona XtraBackup).

How do I restore MySQL?

It is very simple to restore a backup taken with mysqldump. As the file created by mysqldump contains plain SQL, simply invoke the mysql client, providing the mysqldump backup file as input:

mysql -u root -p database_name < backup_file_name

Bear in mind that, as this is a logical backup, restore will involve applying many SQL statements and may therefore take a long time for large databases.

How do I Install MySQL?

The easiest way to install MySQL is from your operating system provider’s standard repositories. To install the server on Oracle Enterprise Linux, Red Hat or Centos:

yum install mysql-server

and then the client:

yum install mysql

However your distribution is unlikely to be providing the latest MySQL version. To obtain the latest version go tohttp://dev.mysql.com/downloads and obtain the software for your operating system (e.g. Linux rpm package files or Windows Installer installation files). For example to install version 5.5.19 of the server and client on 32-bit Oracle Enterprise Linux, Red Hat or Centos :

rpm -ivh MySQL-server-5.5.19-1.rhel5.i386.rpm
rpm -ivh MySQL-client-5.5.19-1.rhel5.i386.rpm

You can also obtain versions for other operating systems here, e.g. Windows Installer installation files.

A good way to get started is to install one of the sample databases provided by MySQL. These can be found, confusingly, on the MySQL documentation page: http://dev.mysql.com/doc/index-other.html. I have found the Sakila database, which models a DVD rental shop, very useful for getting to know MySQL.

Is MySQL free?

The community edition of MySQL can be downloaded (http://dev.mysql.com/downloads) and used for free, even for public-facing live systems.

If you want support and extra tools (e.g. the MySQL Enterprise Backup hot backup tool) then you can purchase this. Details are athttp://www.mysql.com/products.

Additionally if you embed MySQL in a product which you distribute you will need to purchase licences. Details are athttp://www.mysql.com/oem.

Summary

As an Oracle DBA I have been very impressed with MySQL’s features, reliability and ease-of-use. I hope I have wetted your appetite and given you the confidence to jump in there and have a look at those MySQL servers that are sitting in the corner of your server rooms, or to download and install the free version and have a go with it.

And now that MySQL is owned by Oracle you are not even being unfaithful.