Category: MySQL

MySQL related posts

Dangers of skip-grant-tables

MySQL

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”

Two storage engines; different plans, Part II

MySQL

In Part I of this article, we have seen how the internal structure of the storage engine’s index can affect an execution plan. We’ve seen that some plans are inherent to the way engines are implemented.

We wish to present a second scenario in which execution plans vary for different storage engines. Again, we will consider MyISAM and InnoDB. Again, we will use the world database for testing. This time, we will see how confident the storage engines are in their index search capabilities.

Many newcomers to databases often believe that an index search is always preferable to full table scan. This is not the case. If I were to look for 10 rows in a 1,000,000 rows table, using an indexed column – I could benefit from an index search. However, if I’m looking for 200,000 rows on that table (that’s 20% of the rows) – an index search can actually be much more expensive than a full table scan. Continue reading » “Two storage engines; different plans, Part II”

Two storage engines; different plans, Part I

MySQL

A popping question is: “Can an execution plan change for different storage engines?”

The answer is “Yes”. I will present two such cases, where the MySQL optimizer will choose different execution plans, based on our choice of storage engine.

We will consider MyISAM and InnoDB, the two most popular engines. The two differ in many respects, and in particular, the way they implement indexes and statistics: two major players in the optimizer’s point of view. Continue reading » “Two storage engines; different plans, Part I”

MySQL parameters & configuration sample file

MySQL

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”

Parameters to use on mysqldump

MySQL

mysqldump is commonly used for making a MySQL database backup or for setting up a replication.

As in all mysql binaries, there are quite a few parameters to mysqldump. Some are just niceties but some flags are a must. Of course, choosing the parameters to use greatly depends on your requirements, database setup, network capacity etc.

Here is my usual setup for mysqldump. The parameters below apply for an InnoDB based schema (no MyISAM, Memory tables). Parameters can be specified on the command line, or under the [mysqld] scope in the MySQL configuration file.

mysqldump -u dump_user -p -h db_host --routines --master-data --single-transaction  --skip-add-locks --skip-lock-tables --default-character-set=utf8 --compress my_db

Let’s review these parameters and see their effect: Continue reading » “Parameters to use on mysqldump”

Welcome!

MySQL

My name is Shlomi Noach. I am a developer, DBA, consultant and instructor.

code.openark.org is a place for thoughts and code sharing.

Some open source projects and code snippets I’m working on will be published in this site. Most of them will have to do with MySQL, or with MySQL accessing from Java & Python.

Please stay tuned to this site. Hope to start publishing in a short while!

Shlomi