PlanetMysql.ru — информация о СУБД MySQL

Normally I like to use the OS’s package manager to manage the software on my system. This ensures that things are done consistently. When managing MySQL I’d also like to manage all my instances the same way. That makes life easier for me but also for my fellow DBAs and sysadmin colleagues.

I use CentOS and the rpm packages it provides and these work quite well. However, the MySQL rpms do not allow me to manage single and multiple instances alike and while mysqld runs as the mysql user the instance management needs to be done as root (stopping, starting instances, or default access). If you want to run multiple instances you can also use mysqld_multi, but that’s not the default setup.

So this is not ideal. While this may not be much of an issue if you manage a single server, if you manage tens or hundreds things changes somewhat and you REALLY want to manage all instances consistently.

So I’ve been thinking about finding an alternative which would suit me better. What do I need?

• The same treatment of a single instance or multiples on one box.
• Management as much as possible from a non-root user (mysql?)
• Separation of instances into separate “areas”, along the lines of Oracle’s Optimal Flexible Architecture.
• Automatic startup on server boot, and shutdown of “SELECTED” instances.
• Ability to support different versions of mysql running at the same time.
• Straight forward mechanism to upgrade a server or switch the version of MySQL that is used to manage a specific instance..

I was thinking of writing my own scripts but one solution which looks potentially close enough to my ideal solution would be to use Giuseppe Maxia’s MySQL Sandbox. I think this project was born with a completely different goal (enable you to quickly get a different mysql version up and running for testing), but it actually seems to solve most of the things I want for managing production instances.

If you’ve not used it yet take a look. It works pretty well and is easy to setup.

So what can it do at the moment:

• Allow you to install different versions of mysql on the same box.
• Run everthing as a non-root user
• Stop and start either one or all instances easily

For my needs it seems to be missing:

• The ability to indicate which instances should start / stop and a way to trigger this from init during startup / shutdown.
• The way to access the instances is quite different from a normal mysql installation. That has confused me quite a bit and as I use the Sandbox infrequently I have to go back and workout how to “get in” and also how to start or stop any instance, or check if it’s running.
• I’m not entirely sure if it’s easy to adjust the paths for the different mysql “areas” to fit in with my usage of MySQL or something similar to OFA. These things are probably pretty easy to fix.
• The name MySQL Sandbox sounds rather inappropriate if you really used it to manage mysql instances. That’s easy to change.

Ideal MySQL Layout

So how would I like the layout to look like?

Tarball binaries should probably be located somewhere like /path/to/some_binary_name, which allows us to distinguish between different architectures (i686, x86_64), and versions (5.0.89, 5.1.42, 5.5.0-m2). That’s already solved by the current tarballs which have a prefix such as mysql-5.5.0-m2-osx10.5-x86.

Each instance’s data should probably be located along the lines of:

/path/to/instance_name/data (for MySQL data files and my.cnf)
/path/to/instance_name/tmp (for MySQL tmpdir)
/path/to/instance_name/log (for MySQL logfiles (binlog and relaylog)

This allows the locations under /path/to/ to be mounted on different file systems for performance (if needed). In my case /path/to is simply /mysql, but that does not need to be that rigid. OFA is slightly different but the idea is the same.

Accessing the Instances

How do I access the instances?

Currently I access the instances using the following mechanism. Each instance has a name so that name is used to access the instance as a prefix of the normal mysql command.

So instance “a” is “managed”/”accessed” as follows:

mysql_a access to mysql command line
mysqldump_a access to mysqldump
mysqladmin_a access to mysqladmin
…

Oracle does something slightly different by setting up the environment to determine which instance to be accessed. Then the normal command line utilities talk to the right instance.

Either way is fine but it should be very clear which instance you are attempting to access to avoid what could be costly mistakes.

It would be nice if the sandbox provided a common directory which could be included in $PATH so that all instances could be accessed from one place.

I don’t currently have a managed way to start / stop the instances. A file like /etc/oratab would do and then a command like:

mysql_init start # start all ‘marked’ instances
mysql_init stop # stop all ‘marked’ instances
mysql_init_a start # start instance a

The final thing to want to do is to upgrade an instance. Normally this involves:

• Downloading the new version.
• Stopping the instance to be upgraded.
• Adjusting links/directories.
• Perform various “admin tasks” to upgrade the instance.
• Starting it normally again.

All that MySQL sandbox would need to do would be to perform 3.

So these are some of my ideas. I need to look at the MySQL Sandbox code in more detail to see if it will fit my needs but I expect it should not be too difficult to adjust it if needed.

Perhaps if you use other operating systems or package managers and manage one or more MySQL instances on multiple servers these issues are less of a problem. I have not seen comments by others worrying about some of the limitations of the current MySQL rpms and the problem is not really with rpm itself but with the expected usage of a single instance on a single server.

So is there a better way to do this or might MySQL Sandbox be the right solution to achieve my goal?

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL has an unusual grants system that allows a user to be specified by host, ip or network address. That is you identify a user as ’some_user’@'host.host.name’, ’some_user’@'1.2.3.4′ or ’some_user’@'10.3.%’.

That is quite a nice facility but using it is rather tricky. This potentially provides a lot more security as it allows you to specify that different types of database users can only perform certain actions from different types of hosts. So even if you know the user and password you may have trouble getting into a mysqld server. That’s good.

However, this flexibility comes at a price. There are no tools to help you manage this and I have often seen people resorting to using the simplest type of grant, for some_user@’%', or some_user@’10.%’.

I recently wrote a small template script which would allow me to configure similar grants for the same user but in different networks in a way which made this process more manageable. This is only a partial solution to the problem but does make it possible to configure in a reasonably simple fashion grants for a user with access coming from one of several locations.

An Example

An example might make this clearer.

A simple grant for a web user connection from webserver1 might be.

GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';

If we want to duplicate this for 3 web servers we need 2 more sets of grants:

GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';

GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';

If this is for a larger number of locations the grants become unmanageable (for the human to follow easily).

So my script allows me to do:

-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'

-- Grants for %WEB_USER%

GRANT USAGE TO %WEB_USER% IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO %WEB_USER%;

Adding more “web servers” just requires adding a single extra line.

With a simple case like this where there are only 2 grant lines for each user this is not so complicated, but if we had 20 or 30 grants for each user then this simplifies things a huge amount.

The script can be found here and generates the following output:

$ create_grants_from_template grants.template.sql

-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'

-- Grants for 'web'@'webserver1.example.com'

GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';

-- Grants for 'web'@'webserver2.example.com'

GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';

-- Grants for 'web'@'webserver3.example.com'

GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';

$

This output can be piped directly into the MySQL command line.

Further Improvements

While this might be helpful, it does not really solve the problem of managing grants, especially if you have a large number of database users and are managing quite a few database servers. I’ve been thinking about what I would like to do to solve this problem and while the ideas are not yet fully set think that something like the /etc/sudoers file is a good base to work from.

That is use a file which defines various things:

• database users and their passwords
• database servers and database server groups
• individual grants and groups of grants

and combines this so that a script can create the specific rights for a specific database server, the results being different from one server to another.

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL has an unusual grants system that allows a user to be specified by host, ip or network address. That is you identify a user as ’some_user’@'host.host.name’, ’some_user’@'1.2.3.4′ or ’some_user’@'10.3.%’.

That is quite a nice facility but using it is rather tricky. This potentially provides a lot more security as it allows you to specify that different types of database users can only perform certain actions from different types of hosts. So even if you know the user and password you may have trouble getting into a mysqld server. That’s good.

However, this flexibility comes at a price. There are no tools to help you manage this and I have often seen people resorting to using the simplest type of grant, for some_user@’%', or some_user@’10.%’.

I recently wrote a small template script which would allow me to configure similar grants for the same user but in different networks in a way which made this process more manageable. This is only a partial solution to the problem but does make it possible to configure in a reasonably simple fashion grants for a user with access coming from one of several locations.

An Example

An example might make this clearer.

A simple grant for a web user connection from webserver1 might be.

GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';

If we want to duplicate this for 3 web servers we need 2 more sets of grants:

GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';

GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';

If this is for a larger number of locations the grants become unmanageable (for the human to follow easily).

So my script allows me to do:

-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'

-- Grants for %WEB_USER%

GRANT USAGE TO %WEB_USER% IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO %WEB_USER%;

Adding more “web servers” just requires adding a single extra line.

With a simple case like this where there are only 2 grant lines for each user this is not so complicated, but if we had 20 or 30 grants for each user then this simplifies things a huge amount.

The script can be found here and generates the following output:

$ create_grants_from_template grants.template.sql

-- VARIABLE: %WEB_USER% = 'web'@'webserver1.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver2.example.com'

-- VARIABLE: %WEB_USER% = 'web'@'webserver3.example.com'

-- Grants for 'web'@'webserver1.example.com'

GRANT USAGE TO 'web'@'webserver1.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver1.example.com';

-- Grants for 'web'@'webserver2.example.com'

GRANT USAGE TO 'web'@'webserver2.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver2.example.com';

-- Grants for 'web'@'webserver3.example.com'

GRANT USAGE TO 'web'@'webserver3.example.com' IDENTIFIED BY 'some password';

GRANT SELECT ON web_db.* TO 'web'@'webserver3.example.com';

$

This output can be piped directly into the MySQL command line.

Further Improvements

While this might be helpful, it does not really solve the problem of managing grants, especially if you have a large number of database users and are managing quite a few database servers. I’ve been thinking about what I would like to do to solve this problem and while the ideas are not yet fully set think that something like the /etc/sudoers file is a good base to work from.

That is use a file which defines various things:

• database users and their passwords
• database servers and database server groups
• individual grants and groups of grants

and combines this so that a script can create the specific rights for a specific database server, the results being different from one server to another.

PlanetMySQL Voting: Vote UP / Vote DOWN