Friday Jan 17, 2014

The "test" Database and Security

Many installations of MySQL server come with a built-in database called test. It's initially empty, and you might wonder what it's for, or even if you can delete it without any problems.

What is it for? 

The test database is installed by the MySQL Server RPM as part of the mysql_install_db process, and some other package managers run that script too. If you run that script as part of a manual install of MySQL, you'll get the same effect. It creates the database by creating an empty directory called "test" in the data directory, and creates wide-open access to the database test and any database with a name beginning with test_ by inserting a couple of rows into the mysql.db table that give everyone full access to create or use those databases.

The configuration is designed to make it easy for new users to create a playground or sandbox database to work with, one that doesn't require asking the DBA to open up permissions every time a user wants to create a database for testing purposes.

The test security settings 

These are the lines the mysql_install_db script executes (slightly modified):
    INSERT INTO mysql.db VALUES ('%','test','','Y','Y','Y','Y',
    INSERT INTO mysql.db VALUES ('%','test\_%','','Y','Y','Y','Y',

This is quite significant, because if you create a database called test_db or test_banana or test_anything_else_really, all those databases are wide open to any MySQL user on that server, even otherwise low-privilege users. This is particularly important if you run a MySQL server shared across many projects or customers.

Securing the Default "test" Database Configuration 

It's actually a best-practice to remove the test database along with the rows inserted by the lines above—they make the test database (and others beginning with test_) completely accessible to anyone with a MySQL account. When it's created during installation (as it is with the Oracle-built RPM distributions), anyone has full access to it as a sort of sandbox environment, and could in theory use it as a launching point for an attack.

Run the mysql_secure_installation script to perform a number of security optimizations, including removing the lines added by the mysql_install_db process. Another way is simply to delete the two rows:

DELETE FROM mysql.db WHERE Db IN('test', 'test\_%');

Thursday May 30, 2013

How big is a database?

I got a question a while ago that I thought was quite simple, but turned into an interesting discussion: How much hard disk space does a database take up?

As it happens, there's a simple answer and a much, much more involved (yet ill-defined) answer, and which one you choose depends on what you think goes into a database and how very, very pedantic you are.

The Simple Answer

You can query the size of table and index data using Information Schema or SHOW TABLE STATUS. The columns DATA_LENGTH and INDEX_LENGTH contain the number of bytes for the table and index data respectively. You can construct simple queries based on that data, or do a quick search on the web for more interesting queries (For example, Peter Zaitsev of Percona wrote a post on Researching your MySQL Table Sizes). Adding up the totals for all tables in a database gives you a figure that shows how big the data rows and index contents for the database are.

This really is where you should stop.

Let's Get Slightly Pedantic: Taking the File System's Perspective

In MySQL, databases are little more than file folders, so they don't consume space themselves. The space is consumed by the data within, specifically tables and indexes: Views, stored routines, triggers are little more than command text and so take a trivial amount of space, so for now we can ignore them. 

In MySQL 5.6, table and index data are stored in individual files in the appropriate directory. So, in my /var/lib/mysql/world_innodb folder, I've got City.ibd, Country.ibd, and CountryLanguage.ibd. To check the size of the database, I can simply read the size of that folder, which gives me the approximate size of the table and index data, along with any extra space allocated. The extra space comes from the fact that the file size is typically bigger than the amount of data stored within it, because InnoDB increases the file size by 64MB (8MB in 5.5) every time it fills up. You can change this value by setting the innodb_autoextend_increment option.

In MySQL 5.1 and 5.5, the default InnoDB configuration stores all InnoDB information in a single tablespace with a filename of (typically) /var/lib/mysql/ibdata1. By default, this file contains all table data and index information for all InnoDB tables in all databases, which means you cannot simply calculate the space taken on disk by the data of any single database. Although all InnoDB data defaults to a single tablespace file, you can change this by setting innodb_file_per_table, which is enabled by default in 5.6. By doing so, you store table and index data for each database in a file named for the table, in the appropriate database directory.

Let's Get Really Pedantic: Going Beyond the File System 

The simple answer at the top really is quite simple: It only considers data, so if there is no data, there are no rows taking up space. This means that regardless of how many functions, procedures, triggers, views, tables, permissions, or anything else you've configured in your (otherwise empty) database, MySQL considers that it is empty, and therefore takes up 0 space. Of course, if you're quite pedantic about it, you'll know better.

What Else is in a Database?

MySQL does not allocate space per database, but per table and any indexes associated with that table. If every table in a database contain no data, then MySQL considers that the database contains no data. Queries on DATA_LENGTH and INDEX_LENGTH show information about data (as opposed to metadata), so for the purpose of that query, a database with no data takes up 0 space. This might be a quite simplistic way of looking at it, so let's consider the difference between this viewpoint—a database with no data takes up no space—and another—a database with objects, even empty ones, has to exist, and that must take up some space.

As well as table data and indexes, the other information MySQL maintains about a database is its metadata. Some of this metadata is contained within:

  • The file system: as a database directory as db.opt, .frm, and .trg files
  • The mysql database: stored routines, events, privileges, and other database metadata; these are stored in MyISAM tables, each of which contains information for all databases on the server
As the metadata is distributed in multiple locations, MySQL does not have a single location that you could point to and say "This is the database," so for the purposes of explanation, let's drill into my local copy of the sakila database.

Metadata on the File System

As an administrator, I can see that the structure of my sakila database takes up space in the form of the following file system artefacts:

  •     The database directory inode takes up 4KB on my machine.
  •     The db.opt file takes up 65 bytes (for my sakila database)
  •     Triggers take just over 1KB each (TRG and TRN files)
  •     Table metadata (.frm files) are about 8KB each
  •     View metadata (.frm files) depend on the complexity of the view, but let's say 1KB each

On my system, these add up to 336KB for my slightly-extended copy of the sakila database.

Metadata in the mysql Database

There is also row data in mysql relevant to the sakila database:

  •     The mysql.proc table contains 6 rows for sakila; with an average row length of 1116 bytes—from SHOW TABLE STATUS LIKE 'sakila';—that gives approximately 6KB.
  •     The mysql.db table on my system has two rows on my system (I've set up a couple of users), at ~440 bytes each
  •     The mysql.tables_priv table has one row at 851 bytes (unrepresentative, but included for detail)
  •     I have no mysql.event, mysql.columns_priv, or mysql.procs_priv rows relevant to sakila at this time (again, unrepresentative, but included for detail).

Have We Considered Everything Yet?

This isn't even the whole story; I haven't included mysql.user records for users who only use this database, or log file entries relevant to the sakila database. You can take this as a sign that what constitutes a database is actually quite a fuzzy thing.

So, taking all of that together, you might consider that the metadata for my sakila database takes up ~344KB, but it should be apparent that it is quite a difficult task to define what is and isn't sakila metadata, and there is certainly no built-in way to arrive at this figure in a comprehensive and consistent way that would satisfy the most pedantic administrator.

Best to just consider the data, right?


Jeremy Smyth writes MySQL training courses, and likes exploring interesting questions that come up from novices and experts alike.



« July 2016