Blog by Shlomi Noach
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.
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”
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.
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:
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”
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!
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:
Continue reading » “Manually installing multiple MySQL instances on Linux: HOWTO”
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.
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?”
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”
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”