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 to http://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 at http://www.mysql.com/products.

Additionally if you embed MySQL in a product which you distribute you will need to purchase licences. Details are at http://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.

Leave a comment

22 Comments

  1. Please add your blog to the Planet MySQL site so we can share your message with more users.

    http://planet.mysql.com/new

    Reply
  2. David Tomlinson

     /  January 23, 2012

    Patrick, great article! One lovely little tip for making your mysql client prompt informative.

    From

    mysql>

    to

    root@scaramanga [wordpress]>

    Tuck this in the mysql or client sections of your my.cnf file. my.cnf can be in lots of places – mine is at /etc in os x, but you can find out where your client and database are looking for it by typing

    mysql –help

    and make or move an existing file there.

    [mysql]
    prompt=\\u@\\h [\\d]>

    where u = who you’re logged in as, h = host and d = database you’re working on. There are lots of configurations to optimise the prompt just the way you want it at http://bit.ly/z0N9Id – point 4.

    Reply
  3. Very informative for startup person

    Reply
  4. sravani

     /  February 7, 2012

    its very informative.
    i enjoyed reading this.

    Reply
  5. Jo

     /  March 1, 2012

    Reading the intro I had a feeling of deja vu!
    It’s exactly what happened to me :-) LOL!!! Great blog!

    Reply
  6. Nice post…..and its very easy learning step by step for the beginners

    Reply
  7. Gavin Towey

     /  March 14, 2012

    I would make a note that /var/lib/mysql isn’t always the datadir; it can be configured to be anyway. Much better to do SELECT @@datadir; and find out for certain.

    Reply
  8. Rhonda

     /  October 3, 2012

    This is a great beginner guide. I was tasked with installing mysql and importing a dump file of another mysql database. I managed to get it installed, but couldn’t connect to it. Thanks to you I got connected, learned my way around and I’m going to import a dump file.

    Reply
  9. awesome, thank for this very informative arcticle

    Reply
  10. its very easy learning step by step for the beginners

    Reply
  11. Thanks, awesome site.

    Reply
  12. Anonymous

     /  January 17, 2013

    thanks a lot for your post

    Reply
  13. I really wonder the reasons why you named this specific blog post, “MySQL:
    An Introduction for Oracle DBAs Patrick Hurley”.
    In either case I personally enjoyed the post!Thanks for
    your effort-Lisette

    Reply
  14. Hello There. I found your blog using msn. This is a very well written article.
    I will make sure to bookmark it and return to read more of your
    useful information. Thanks for the post.
    I will definitely return.

    Reply
  15. Hi! I just wanted to ask if you ever have any problems with hackers?
    My last blog (wordpress) was hacked and I ended up losing months of hard work due to no data backup.
    Do you have any solutions to protect against hackers?

    Reply
  16. Ravindra Bhatt

     /  September 20, 2013

    I am glad, i found this very informative article right at the beginning of my endeavor with MySQL. it has really set a nice platform for me to jump into the wide field of MySQL.

    Many many THANKS Patrick.

    Regards,
    Ravi

    Reply
  17. What’s up all, here every person is sharing these experience, therefore it’s nice to read this weblog, and I used to go to
    see this website every day.

    Reply
  1. An Oracle DBA meets MySQL « Patrick Hurley
  2. MySQL: An Introduction for Oracle DBAs « Patrick Hurley « yurink
  3. Rutweb Technology : My First Collaborate
  4. MySQL for Oracle DBA(s) | Daniel Fisher :- Bullet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: