MySQL Geek - follow the dolphin

MySQL Cluster is the best solution for a fault tolerant and high performance database in a clustering architecture.

Being a MySQL product, it is distributed in different flavours: compiled or not, GPL or commercial, and so on.
Following forums, chats and more, one of the classic question is "What is the minimum number of machines required to setup a MySQL Cluster?" 2 machines? 3? 4? In reality, more machines are present in the Cluster, less single points of failure are present.

This series of article about MySQL Cluster is intended to all developers, SysEngs or DBAs that don't have enough machines to try a true redundant cluster. Scope of this series of article is to install and test MySQL Cluster on a single machine.

The following setup can not be used in a production environment and neither in a pre-production testing environment. Use this cluster only for a pre-test self-evaluation environment.

If you get the following warning starting MySQL:
[Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!!

The problem is in your my.cnf.
Find the option log_bin and specify a basename.
Ex.
log_bin = /var/log/mysql/mysql-bin

One of the benefits of MySQL Cluster is that index or table table can be performed in parallel through all the Data Node.

Sometime, to improve performance, it is convenient to use the system variables engine_condition_pushdown, that allow to delegate part of the processing to the Storage Engine, NDB in our case.
One important thing to remember is that when engine_condition_pushdown is enabled, only the WHERE conditions are sent to the storage engine, and anyway not evaluated for any type of condition. No other functions (like aggregation functions) are "pushed down".

The reason is quite simple.
MySQL use a 2 level tier architecture:
- SQL parser and optimizer
- Storage Engine

Often people try to name a column group, a database update or a table interval, and so on.
You must be aware that in MySQL there are a list of reserved words, as shown here.

Best practice is to NOT use reserved words as identifiers (database/table/column/alias names), but you can use reserved words as identifiers if you quote them properly with a backtick (“`”).

max_connections is the mysqld global system variable that specifies the number of simultaneous client connections that are allowed to a running mysqld.
Its default is 151 from version 5.1.15 (default to 100 on previous versions).

Some time mysqld reaches its max_connections limit and no more clients are allowed to login and perform queries. This is a quite common scenario in web based application where quite easily hundreds of web server instances try to connect to a mysql server.

Let's take a look of how it is possible to retrieve some simple information about connections and how to change mysql behaviour.

Never tried to backup INFORMATION_SCHEMA with mysqldump?
Surprise, the dump you got is empty!
As explained here , INFORMATION_SCHEMA is a collection of read-only views and not tables, so it is impossible to execute an SQL dump of them.

What if I need to backup its content anyway, just for reference?

When refering to a column name in MySQL case sensitive isn't an issue because they are case insensitive on any platform, but things became a bit more complicated when referring to databases and tables names.
MySQL use the underlaying filesystem to access databases and tables: for each database a directory with the same name is used, and for each table one file (or more, depending from the storage engine used) with the same name is used.

It isn't the best backup practice (in reality it has some limitations), but if you want to dump all your databases into separated .sql files:

USER="privileged_username"
PASS="password"
MYSQLOPTS="--batch --skip-column-names -u $USER -p$PASS"
DUMPOPTS=" --master-data -u $USER -p$PASS --databases"
for dbname in `echo "show databases" | mysql $MYSQLOPTS`
do
mysqldump $DUMPOPTS "$dbname" > "$dbname".sql
done