Skip to content

# Tag: Configuration

## The importance of report_host & report_port

MySQL Configuration Replication May 13, 2009May 13, 2009

The two server variables, report_host and report_port, are not commonly used. One of the reasons, I suspect, is that they do not appear in any of the standard .cnf files provided with a MySQL installation.

For a replication environment, I find these two variables to be very useful.

#### Where are these variables used?

Here’s a slightly modified exerpt from a MySQL master node I have, and which has two slaves: Continue reading » “The importance of report_host & report_port”

## More on variables ambiguities

MySQL Configuration April 30, 2009May 13, 2009

Continuing Variables ambiguities in names and values, there are two more issues I wish to present.

First, I’ve shown that variable values may be ambiguous. Thus, 1 and ON are interchangeable. But also 1 and YES.

The important thing to note is that it’s not always like that. You can’t just swap 1 for ON or YES as you will: it depends on the variable. Thus, a specific variable (e.g. query_cache_type) may accept ON for 1 – but will not accept YES. Another may accept YES for 1 – bot not ON.

Confused? Here’s the second issue. This one is really a bug, as I see it. Take a look at the following:

>     mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
>     +-----------------------+-------+
>     | Variable_name         | Value |
>     +-----------------------+-------+
>     | innodb_file_per_table | ON    |
>     +-----------------------+-------+

The ON value is actually illegal: if you try to set this value in your my.cnf file – you’ll get a non-file-per-table behavior. The real value you should be using in my.cnf is 1 (or no value at all).

And so in this latter example, 1 equals ON, but only one of them is valid in the my.cnf file, and yet only the other one is being reported.

The behavior is documented here. But, if, like myself, you find it undesired, please comment on bug #44509.

## Variables ambiguities in names and values

MySQL Configuration April 28, 2009April 28, 2009

Writing up some scripts, I see more and more ambiguities with regard to global variables.

For one thing, the names ambiguity between the hyphen (‘-‘) and the underscore (‘_’). So wait_timeout and wait-timeout are the same variable.

But just check out the many levels of inconsistency:

- Command line arguments (e.g. run mysqld with option variables) use the hyphen convention
- mysql –verbose –help shows the hyphen convention
- SHOW GLOBAL VARIABLES uses the underscore convention
- The MySQL supplied sample configuration files use both conventions interchangeably

Enough? Not quite: there are ambiguities in values, as well. For example, you may set query_cache_type to 1 or ON. These are equivalent. That’s very friendly. However: Continue reading » “Variables ambiguities in names and values”

## MySQL User Group Meetings in Israel

MySQL Configuration InnoDB MyISAM User Group March 11, 2009

This is a short note that the MySQL User Group Meetings in Israel are established (well, re-established after a very long period).

Thanks to Eddy Resnick from Sun Microsystems Israel who has set up the meetings. So far, we’ve had 2 successful meetings, and we intend to have more! First one was in Sun’s offices in Herzlia; second one, held last week, was at Interbit (a MySQL training center) in Ramat Gan. We hope to hold these meetings on a monthly basis, and the next ones are expected to be held at Interbit.

A new (blessed) law in Israel forbids us from sending invitations for these meetings via email without prior consent of the recepient (this law has passed as means of stopping spam). We do realize there are many users out there who would be interested in these meeting. For those users: please stay tuned to Interbit’s website, where future meetings will be published – or just give them a call!

It was my honor to present a short session, one of three in this last meeting. Other presenters were Erad Deutch, who presented “MySQL Success Stories”, and Moshe Kaplan, who presented “Sharding Solutions”. I have presented “MyISAM & InnoDB Tuning Fundamentals”, where I have layed down the basics behind parameter tuning for these storage engines.

As per audience request, here’s the presentation in PDF format:

I intend to give sessions in future meetings, and have already started working on my next one. So please come, it’s a fun way to pass a nice afternoon. See you there!

## Manually installing multiple MySQL instances on Linux: HOWTO

MySQL Configuration Installation Linux February 19, 2009September 17, 2010

Installing a single MySQL instance on a linux machine is a very simple operation. It may be as simple as:

>     apt-get install mysql-server

But you cannot use this method to install another MySQL instance. Moreover, if you try to manually install another instance, you may find that some collisions occur.

For example, when trying to install two 5.0 servers, apt-get or yum will just tell me “package is already installed”.

When trying to install a new 5.1 server along with 5.0, an implicit upgrade is performed.

But even if we try forcing the installation, there are collisions:

- A dpkg or rpm will install my.cnf under /etc. Two installations will override one another. With RPM you may get a .rpmsave backup file, but that doesn’t help too much.
- The daemon file: /etc/init.d/mysql(d) is overwritten.
- The default data directory is used for both installations: /var/lib/mysql
- The binaries are overwritten
- Both installations will use port 3306.
- In both installations, the same socket file (e.g. /var/run/mysql/mysql.sock) is used.

Continue reading » “Manually installing multiple MySQL instances on Linux: HOWTO”

## MySQL security: data integrity issues

MySQL Configuration Security sql_mode January 21, 2009

MySQL’s security model is not as elaborate as other popular databases. It’s missing quite a lot.

I wish to point out what I think are some very disturbing security holes, which may affect the database integrity.

This post is not about Roles, Kerberos, IPs and such. It’s about simple MySQL features, which allow common, unprivileged users, to break data integrity by using unprotected session variables.

I will consider three such issues.

Continue reading » “MySQL security: data integrity issues”

## Do we need sql_mode?

MySQL Configuration Installation sql_mode January 8, 2009January 9, 2009

sql_mode must be one of the most elusive issues in setting up a MySQL database. It is also responsible for difficulties in migration, upgrading and securing of databases.

MySQL usually has little issues with migrating database from one machine to another, one version to another, one OS to another, one architecture to another. If we’re not sure, we can always migrate using mysqldump’s logical dump, right?

Not entirely right. The elusive sql_mode (empty by default) may affect out backup+restore, may affect our application behavior, may affect our data integrity. Continue reading » “Do we need sql_mode?”

## Dangers of skip-grant-tables

MySQL Configuration Security November 13, 2008December 13, 2008

When MySQL’s root password is lost and must be reset, there are two popular ways to create a new password. One of the options is far too popular, in my opinion.

The preferred way of setting a root’s password is by using an init-file. The process for doing this is well explained in MySQL’s manual. Using this method requires creating a simple text file, in which the required

`GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFY BY '****' WIth GRANT OPTION;`

(or, alternatively, `SET PASSWORD ...`) statement is written.

An entry must be written to my.cnf, or supplied via command line parameters:

`init-file=/tmp/my-init-file.sql`

MySQL must then be restarted. Upon restart, and before opening any outside connections, the init-file is executed. Once MySQL is up and running, the init-file entry should be dropped. Continue reading » “Dangers of skip-grant-tables”

## MySQL parameters & configuration sample file

MySQL Configuration Installation October 17, 2008November 23, 2009

The following is a sample my.cnf configuration file. It includes some common and uncommon parameters, along with occasional comments.

You can use this file, and tune as appropriate for your machine, operating system, and application needs.

In particular, note the high values set for the InnoDB related parameters, below, and change them to match your system and application’s requirements. You have been warned. Continue reading » “MySQL parameters & configuration sample file”