My MySQL tipsvalid-rss-rogers

How and why tmp_table_size and max_heap_table_size are bounded. PDF Print E-mail
Written by Marco Tusa   
Monday, 06 September 2010 11:55


In some cases, MySQL creates internal temporary tables while processing queries.
On the base of the dimensions of the resultset MySQL will use the MEMORY engine AND/OR the MyISAM engine.
The difference is that MEMORY engine will handle the table in memory, while MyISAM will write it on disk.
A table created using the MEMORY engine can be automatically converted by the MySQL server if exceed the define threshold.

Then there are others circumstances which could create temporary tables using MEMORY but that can go to MyISAM (so disk) if too large:

  • If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue;
  • DISTINCT combined with ORDER BY may require a temporary table;
  • In the case of the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

There are some conditions which will force the temporary table to use MyISAM :

  • Presence of a BLOB or TEXT column in the table;
  • Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes;
  • Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL;

The tables explicitly created with CREATE TABLE ENGINE MEMORY use ONLY the max_heap_table_size system variable to determines how large the table is permitted to grow and there is no conversion to on-disk format.

Why still doing a post on this issue?

Because i still see on MySQL configuration that DBA seems use it as separate assignments.Let us take this from the start, the manual.
In the manual section ( we can find the tmp_table_size definition.

Command-Line Format --tmp_table_size=#
Config-File Format tmp_table_size

Option Sets Variable Yes,tmp_table_size

Variable Name tmp_table_size
Variable Scope Both
Dynamic Variable Yes
Permitted Values
Type numeric
Default system dependent
Range 1024-4294967295


What we need to take in account, and in mind, are these two element:

Default system dependent
Range 1024-4294967295


Now let us check for the max_heap_table_size

Command-Line Format --max_heap_table_size=#
Config-File Format max_heap_table_size
Option Sets Variable Yes, max_heap_table_size
Variable Name max_heap_table_size
Variable Scope Both
Dynamic Variable Yes
Permitted Values
Type numeric
Default 16777216
Range 16384-4294967295


We can easily identify that both can be dynamically modified (Dynamic Variable Yes), that both the variable could be assign at global scope or per session (Variable Scope Both).


Finally that max_heap_size is by default 16M while tmp_table_size is system dependent.


Let us to try to clarify this digging in the code doing:

<mysql_source_dir>/sql ->
[root@tusacentral07 sql]# grep tmp_table_size *.*


and we found this files. sys_var_thd_ulonglong sys_tmp_table_size(&vars, "tmp_table_size", &SV::tmp_table_size);


From that my understanding is that both starts with a value of 16MB full-stop.

In the statement is:

"static sys_var_thd_ulonglong sys_tmp_table_size(&vars, "tmp_table_size",&SV::tmp_table_size);"

we have no explicit reference to allocation.


The place were we have it is in :

{"tmp_table_size", OPT_TMP_TABLE_SIZE,
"If an internal in-memory temporary table exceeds this size, MySQL will"
" automatically convert it to an on-disk MyISAM table.",
&max_system_variables.tmp_table_size, 0, GET_ULL,
REQUIRED_ARG, 16*1024*1024L, 1024, MAX_MEM_TABLE_SIZE, 0, 1, 0},


{"max_heap_table_size", OPT_MAX_HEP_TABLE_SIZE,
"Don't allow creation of heap tables bigger than this.",
&max_system_variables.max_heap_table_size, 0, GET_ULL,
REQUIRED_ARG, 16*1024*1024L, 16384, MAX_MEM_TABLE_SIZE,


In the MySQL 5.5 and above, we have a clearer place and assignment:




static Sys_var_ulonglong Sys_tmp_table_size(
"If an internal in-memory temporary table exceeds this size, MySQL "
"will automatically convert it to an on-disk MyISAM table",
VALID_RANGE(1024, (ulonglong)~(intptr)0), DEFAULT(16*1024*1024),


static Sys_var_ulonglong Sys_max_heap_table_size(
"Don't allow creation of heap tables bigger than this",
SESSION_VAR(max_heap_table_size), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(16384, (ulonglong)~(intptr)0), DEFAULT(16*1024*1024),


But the result is the same, MySQL start from 16MB, no matter what system you have, or if it does, is very well hide from search.



Now let see what the manual say about how this two variables and their relation:


The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.)

If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.

Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

This variable does not apply to user-created MEMORY tables.


Well this one seems clear enough for everyone but to avoid confusion, check the code:

so again

[root@tusacentral07 sql]# grep tmp_table_size *.*


but this time we open the file

if (thd->variables.tmp_table_size == ~ (ulonglong) 0) // No limit
share->max_rows= ~(ha_rows) 0;
share->max_rows= (ha_rows) (((share->db_type() == heap_hton) ?
thd->variables.max_heap_table_size) :
thd->variables.tmp_table_size) /

Here is were we find the min() functions used from MySQL.



So as the manual state MySQL will take the LOWER value assign to the two variables, as the good value for tmp_table_size (only).


To be more clear, if we have in the my.cnf:

tmp_table_size      = 32M
max_heap_table_size = 16M


MySQL will assign to tmp_table_size the value of 16M and to max_heap_table_size 16M.


if we have:

tmp_table_size      = 16M
max_heap_table_size = 32M


MySQL will assign to tmp_table_size the value of 16M and to max_heap_table_size 32M.


This needs to take in account because I still see at client site a lot of :

tmp_table_size = 32M
max_heap_table_size = 16M


Which makes no sense.



Finally, this variable has huge possible impact on memory consumption.

Just consider that having:

max_connctions = 600
tmp_table_size = 32M
max_heap_table_size = 32M


These settings could potentially generate a memory usage on the server of (600 x 16MB) = 9.3 GB.


Given that the variables are dynamic, and that could be assign at session level, it is good practics to increase the value of tmp_table_size only when is really needed, in the session before performing the operation which will require it.


Seems all to me but If I have miss something here, corrections are welcome.




Last Updated on Sunday, 18 August 2013 18:45
How to insert information on Access denied on the MySQL error log PDF Print E-mail
Written by Marco Tusa   
Wednesday, 19 May 2010 16:54


MySQL is really poor in is support for AUDITING.

There will be some new feature in 5.5 or later, in which we will see the AUDIT interface finally implemented.

But ... who knows what will really happen, and who know if it will work or not.

So in the meantime, if you want to have some information printed out (at least), you can use this simple pathc.

What you need to do is modifying the file in the sql directory.

in:> sql/
Look for the function : int my_message_sql(uint error, const char *str, myf MyFlags)

change the code in the function with the one here, but also check that it is not inserting new bugs ;-).

int my_message_sql(uint error, const char *str, myf MyFlags)
THD *thd;
DBUG_PRINT("error", ("error: %u  message: '%s'", error, str));

Code added for writing access denied
if ((global_system_variables.log_warnings > 1) &&
{ // then this is an access-denied error, log it

Recompile and test it just trying to accessing something with a user that DO NOT have th permission to do it.

You will see the Alert in the log!


So simple so coool

Last Updated on Sunday, 18 August 2013 18:49
How to log all MySQL query without stressing the MySQL server with the general Log PDF Print E-mail
Written by Marco Tusa   
Wednesday, 19 May 2010 14:42

It is possible to trap all the query sent to MySQL, using tcpdump.

This will be done capturing the network traffic. Since this uses the libpcap library, we need to have root privileges.

Because tcpdump will not capture queries sent to socket file, be sure that the request you want to track will not use it.

So having clarified this obvious limitation, let us assume that all the application running locally are accessing MySQL using on standard port 3306.


Capturing the queries to a file named all_query_tcpdump.log executing:

tcpdump -i lo port 3306 -s 2000 -w all_query_tcpdump.log

Please note that we will capture 2000 (-s 2000) headers bytes, which should be fine in the most cases.


Having done the data collection, we have now to analyze it.
For that we will use  thsark (wireshark) :

tshark -r all_query_tcpdump.log -d tcp.port==3306,mysql -T fields -e mysql.query > mysql_query.log

The generated log, will contains a lot of empty lines which could be removed as follow:
cat mysql_query.logt | grep -v "^$" > mysql_clean_query.log

That's all folks!!!



В пьесе содержатся еще более крамольные идеи.

Они полагают, что найти подходящую работу на Земле трудно и что мне следует попытать счастья среди звезд.

Люди охотно согласились, потому что он спас тонущую девочку.

Камень нашел его, и он принадлежал Камню.

Он был сложен из огромных серых каменных блоков, "Скачать книгу светлана иванова"каждый величиной с дом.

В силу стесненных финансовых обстоятельств Фриде мог позволить себе осуществлять наблюдение лишь за той частью звезды, которая была доступна исследовательской платформе на настоящий момент.

Я постарался как можно тактичнее "Программы напоминалки скачать"задать тот же самый вопрос.

Конечно, с волшебными конями хлопот будет несколько больше.

Медный Бабуин медленно полз вперед.

Морепа, наверное, забрал ее по той "Учебники этика скачать"же причине, что Небопа отнял у нас Амбри.

Машины и автобусы "кредиты наличными в спб"аэропорта на Западном проспекте застыли в ожидании смены светофора.

Тогда-то Сибирская республика внезапно заявила об открытии у себя месторождений нефти и газа, япошки тут же провели по просьбе правительства республики "Чистые пруды скачать песню"экспертизу месторождений, оценили их как гигантские и захапали кучу концессий.

Но странно звучал этот мерный торжественный звон в залитом водой городе, у ворот которого стояла смерть.

Из-за этих доспехов и боролись Одиссей "Кодек пак мега скачать"и Аякс.

Поль сделал глоток вина и поставил стакан.

Но обещай, что никому не скажешь, что я здесь.

Долоникус снова взглянул вниз и похлопал себя "Рингтоны скачать приколы"по животу.

Они даже стали бы пускать по нему ракеты или пытаться подавить его фотонную энергию средствами радиоэлектронной борьбы.

Однако глаза миссис Моллой по-прежнему "ирина молчанова книги скачать" горели злостью и недоверием.

Хесус Гомес стал "дневники алисы скачать" медленно продвигаться вдоль потемневшей от времени обшивки корабля, "минусовку скачать бабок ежек" освещая себе путь специальным глубинным фонарем и стараясь не наступать "скачать стас михайлов новая песня" всем весом на обшивку, дабы не провалиться "скачать игру клуб по сериалу" внутрь корпуса.

Но ему очень не хотелось переделывать свою работу, столь близкую к завершению.

Она протянула руку и взялась "притчи скачать видео" пальцами за правое предплечье Дилвиша.

Да "гоночные симуляторы скачать" от него просто несет трухой или чем-то в "бесплатные игры на мобильный скачать" этом роде.

Исидора тоже вышла, почти наступая на шлейф своей соперницы.

Ты, "реп скачать новое"кажется, не питаешь большого уважения к этой "скачать песни винни пуха"профессии.

Локоть задел покрывало; оно зашуршало.

Да потому что "машина времени песня скачать"в глубине души Харви Соммерштейн не верил "игра в карты паук"этому.

Она будет сушиться очень долго, проговорила Джакара.

Вдруг мне почудилось, что "скачать книгу т"я слышу какой-то слабый "Скачать песню чита герлз бесплатно"звук, напоминающий шипение, но, взглянув в оба "игры карты дурак на раздевание"конца коридора, я никого там не обнаружил.

Двое "скачать игру контра страйк соурс"или больше игроков получают каждый по несколько "скачать акунин азазель"карт и делают ставки на то, у кого на "Скачать музыку воровайки"руках комбинация из самых старших карт.

Это "Алексей Кулаковский" не та вещь, которую я могу взять на похороны.

Джек "Бухгалтерия на компьютере для индивид. предпринимателя" начал подчинять валун своей воле.

Рисса собиралась поговорить с "Трудно быть богом Попытка к бегству. Далекая радуга" вами об этом.

Я почти "Как исцелить цветом" нигде не бывала, только гостила в разных частях Англии.

В самом "Английский язык Справочник для учащихся" центре ее были свалены в огромную кучу "Спецкорр" самые различные предметы резная деревянная мебель, картины, косметика, "Двенадцать стульев Золотой теленок Одноэтажная Америка" женские украшения и даже церковные ризы.

Музыка несла "Всеобщее управление качеством" поезд вперед по "Путешествия за чудесами Путеводитель…" дороге, проложенной от конца времени до усеянных обломками и развалинами огромных "Хаврошечка" пространств Непостижимых Полей.

Если "Сценарии" у нас болит горло, или, скажем, зуб, или "Недоросль" просто нападает хандра, никто не утешает нас.

С обогатительных фабрик непрестанно похищают уран, "Броненосец Слава Непобежденный герой Моонзунда" и мы не можем "Георгины" этого остановить.

Голова ассасина откинулась назад, свободно и беззаботно, как "Шпаргалка Судопроизводство" у мужчины, наслаждающегося поцелуями красавицы.

Маргарита, которой надоело наблюдать за возней Фауста, встала "Самый полный посевной календарь на каждый день 2012" с табуретки и "Священная охота" начала ходить взад-вперед по камере от одной стенки к другой, словно пантера, посаженная в "Лев Лангедока" тесную клетку.

Руки висели у меня вдоль "Практика познания души Обрети гармонию" тела свободно, став на мгновение "РННА. Враг в советской форме" бесполезными.

Я полагал, что эта ваза находится в одной частной коллекции во Франции.

Большинство из "Белый пудель" них никогда не выбиралось наружу.

Я вошел в "Русич Перстень Тамерлана" потайную комнату, самую дальнюю "поло автокредит" в покоях Бранда.

Он стоял между присутствующими "НеВозможно" и тем, что лежало на земле.

Когда я повернулся, "Производство молока и говядины в фермерском хозяйстве" чье-то полузнакомое лицо мелькнуло справа от "Linux для чайников" меня.

Я знаю о том, что "Секретные бункеры Кенигсберга" он ненадолго посетил Двор Хаоса "Реалити-шоу Подстава Искатель неприятностей" в конце войны, но не имел чести лично познакомиться "Тренируем руку Многоразовая тетрадь" с ним.

Все эти "Формирование вычислительных навыков на уроках математики. 5-9 классы" экзотические вещества не должны были содержать ни малейшей примеси.

Last Updated on Tuesday, 02 July 2013 19:32
MySQL installation PDF Print E-mail
Written by Marco Tusa   
Thursday, 09 July 2009 00:00

Very often people not expert ask me some simple detailed directions on how to install MySQL from fresh.

The following is a simple one that if you will follow step by step should give you a MySQL working installation.

this articule assume that you have the possibility to attach different storage for:

System and binaries




1) You need to have access to the client area for download the MySQL software from somwere possible points are:

2) Perl DBI and DBD::mysql installed on the machine (See How to Install MySQL Perl module guide)

3) Root privileges



1) Install the Perl modules

2) Create a directory layout as follow:

|-- data
| |-- mysql
| `-- test
`-- lost+found

|-- exports
`-- logs
|-- Q_MySQL
    |-- binlog
    |-- general
    |-- innodb
`-- relay


3) Create a directory for the MySQL binary in:



4) Expand the downloaded file (MySQL server binary installation) into the mysqldistributions directory


5) Create the symbolic link to /usr/local/mysql


6) Copy the file from /usr/local/mysql/support-files/mysql.server to /etc/init.d/ as mysql


7) Register it chkconfig –-add mysql


8) Create in /etc/ the file my.cnf and modify the server-id with a unique value


9) Create the group and user mysql

groupadd mysql
seradd -g mysql –s /bin/bash –p mysql


10) Change the permissions on all the relevant directories:

chown –R mysql:mysql /mysql_logs
chown –R mysql:mysql /mysql_data
chown –R mysql:mysql /usr/local/mysql*


11) Impersonate the user mysql su –l mysql


12) Run the script to initialize the mysql database

./scripts/./mysql_install_db --basedir=/usr/local/mysql --datadir=/mysql_data/data


13) Check that the directories are created and the permissions correctly set:

ll /mysql_data/data

14) Open another terminal window to check the log


15) Become root again


16) Start the MySQL server with

/etc/init.d/mysql start


17) On the opened terminal type:

tail -fn200 /mysql_data/data/.err to check what is going on


18) On the original terminal, go inside the mysql server using the local

mysql console: /usr/local/mysql/bin/mysql –uroot

19)Check for anonymous user to remove

root@localhost:mysql.sock [(none)]> select user,host from mysql.user;


| user | host |
| root | |
| | localhost |
| root | localhost |
| | |
| root | |

5 rows in set (0.00 sec)
and remove them:

root@localhost:mysql.sock [(none)]> drop user ''@localhost;
root@localhost:mysql.sock [(none)]> drop user ''@’machinename’;

    Add the Password for the user root for *ANY* hosts:
set password for root@localhost=Password('mysql');
set password for root@’machinename’=Password('mysql');

set password for root@’’=Password('mysql');


20) Exit and try if all was done correctly:

root@localhost:mysql.sock [(none)]> exit


[root@xx mysql]# /usr/local/mysql/bin/mysql -uroot -pmysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.31sp1-enterprise-gpl-pro-log MySQL Enterprise Server - Pro Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost:mysql.sock [(none)]>


21)Now add al the other user as you like




Last Updated on Sunday, 18 August 2013 18:50
How to set up the MySQL Replication PDF Print E-mail
Written by Marco Tusa   
Thursday, 21 May 2009 00:00




1) Before setting up a replication, it could be important to have a clear idea on the why you are setting up a MySQL replication.

The most common one are:
1) Backup purpose
2) Scalability
3) Geographic Distribution
4) High Availability
(More see

So stop, relax think and decide which one is yours because it will make the difference.

2) One of the most common question clients asks is HOW the Replication works?


There are two main concepts to keep in mind:

1) in MySQL the replication is asynchronous

2) the replication is based on the binlog

The Replication is based on the work of 3 (note please 3 not 2) threads:

1) BinlogDump thread

2) Slave I/O thread

3) Slave SQL thread

What they are used for?

The Binlog dump thread. The master creates a thread to send the binary log contents to the slave. This thread can be identified in the output of SHOW PROCESSLIST on the master as the Binlog Dump thread.

The binlog dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.

Slave I/O thread. When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.

The slave I/O thread reads the updates that the master' Binlog Dump thread sends and copies them to local files — known as relay logs - in the slave's data directory.

The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.

Slave SQL thread. The slave creates this thread to read the relay logs that were written by the slave I/O thread. The slave SQL thread is also used to execute the updates contained in the relay logs.

Please Note that MySQL create a Binlog Dump thread for each connected slave. This could lead to a increment of load on the Master specially if is a very busy master in write.

Because this it is good practics to do not attach too many slaves to the same Master. In order to bypass the limitation it is possible to attach a slave to another slave doing the replication "on cascade".

Something like:

/ \
      S1       S2
/ \
S1.1   S1.2

The other question very often rose, and related to this is... how long the replication takes to propagate the data across all slaves.

A possible formula but quite far to be perfect could be:

PT = (ND + QT) * NL


PT = Propagation Time

ND = Network time delay

QT = Query Execution Time

NL = Number of Level

This formula take in account the need of the replication to wait for each slaves to be fully propagate, but also that it is not serialized but that could be executed in parallel.


3) What needs to be replicated?


Last but not least point is what is really needed to be replicated?

There are two main points to keep in account,

  • the binlog is not only used by the replication
  • filtering could be applied at slave level.

It is possible to do not write statements for a specific Database using binlog-ignore-db=db_name, but this will imply that all the changes will not be save in the binlog, which imply that we will not have a way to recover the time "delta".

It is quite obvious that there *must* be a very good reason to use this approach.

So for me it is much better to go for filtering at slave level, also if this implies a bigger amount of traffic at network level.


4) Master and Slave where and how synch the data?

Well if we are going to implement the Replication on a new Environment then no problem because it will start from scratch, but what if we have to do it on a Master with an existing data set?

The easy answer is to load a valid backup in the Slave(s).

But how to take a valid backup? (read other article on how to take backups on MySQL)


In MySQL a valid backup, that respect the principle of internal consistency and that could be re conducted to a Binlog file and position, needs to take in account the different behavior of the different engines.

So any time we have to take a backup we must think on how to handle the different engines.

See also

And it is always a good practice to perform at regular interval a "restore check" to analyze the backup procedure validity.

Briefly we can say the following:

For MyIsam:


2) use one of the following

a) copy files

b) Export (with SELECT ... INTO OUTFILE 'file_name')

c) mysqldump with --master-data (2)


  1. FLUSH TABLES WITH READ LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits.

  2. The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

For InnoDB(1), PBXT, NDB

  1. using one transaction (or single transaction if you prefer)
  2. use one of the following methods:
    a) Export (with SELECT ... INTO OUTFILE 'file_name')
    b) mysqldump with --master-data and --single-transaction
    c) For cluster ONLY use START BACKUP (

All the above (excluding mysqldump which is storing the info inside) should also store the information for the binary log file and position.


Transforming all this into a step by step procedure:

1) Create a user that will be used by the slaves for connecting to the master as follow:


Please note that it is good practice to use an internal set of IP instead public ones when setting up the replication.

2) Modify the my.cnf(my.ini) on both Master and Slave(s):

a) first thing to do is to modify the server-id such that any server in the Replication will have a unique identifier

b) If you are going to use multilevel Replication add log-slave-updates = 1 on the Slaves or you will not have the data propagated correctly.

c) In the unlikely case you have taken the fool decision of excluding a Database from the binlog. Add on the master binlog-ignore-db=

d) In the case you need to filter at slave level the replication by Database or Table. Add the command (one for each, Database or table) replicate-do/ignore-db=db_name and --replicate-do/ignore-table=db_name.tbl_name

3) Load the master dataset on the slave(s) taken from the valid backup previously taken

4) Register the Master on the Slave with the command:


5) Start the slave(s)

6) Check the Replication Status with SHOW SLAVE STATUS\G

7) Test the replication creating on the master a database, a table and filling it with some data. Then check the latest Slave of each chain to see if the data is replicated correctly.



Setting up Master - Master replication (with one node at time acting as the ACTIVE node)
MySQL 5.1 or above

If you want to filter the replication you have two different ways to do that, at master (by inclusion or exclusion), at slave (by inclusion or exclusion).


Parameter to use for filtering the binary log at MASTER:

binlog-do-db = DB_to_replicate

binlog-ignore-db = DB_to_IGNORE

To specify more than one database to ignore, use this option multiple times, once for each database.

When using this way of filtering, it is advisable to use binlog_format = ROW, this to ensure that the replication will act consistently, and will ignore the USE or dbname.tablename clausole.

Parameter to use for filtering the binary log at SLAVE:








Assuming two MySQL sites site:

Site_A IP (

Site_B IP (



1) First of all be sure both machine will have correct GRANTS/User for the replication

Execute on Site_A


Execute on Site_B



2) check/modify the configuration files:

Site_A (active):


Setting (or checking) the UNIQUE id for the server:

server-id = XX (i.e. 100)

Activate the binary log:

log-bin = /var/log/mysql/mysql-bin.log

binlog_format = MIXED;

Set MySQL to generate different values for the auto increment (Site_A/Site_B)

auto_increment_increment = 2

auto_increment_offset = 1 #for Master

Write the setting for future use but keep it commented, it will be then possible to set it at command line from mysql client, and then modify it also in the configuration file if needed.



Site_B (read only/standby):


Setting (or checking) the UNIQUE id for the server:

server-id = YY (i.e. 200)

Activate the binary log:

log-bin = /var/log/mysql/mysql-bin.log

binlog_format = MIXED;

Set MySQL to generate different values for the auto increment (Site_A/Site_B)

auto_increment_increment = 2

auto_increment_offset = 2 #for FailOverMaster

Set the server as READ ONLY


Custom steps (on both sites):


log-slave-update Add this parameter in the case you have additional SLAVES attached for scale-out purpose on one or both Site.

slave-load-tmpdir = file_name The name of the directory where the slave creates temporary files for replicating LOAD DATA INFILE statements.

Final look could be:


server-id = 100

binlog-do-db = DB_to_replicate

log-bin = /var/log/mysql/mysql-bin.log

binlog_format = MIXED;

auto_increment_increment = 2

auto_increment_offset = 1



slave-load-tmpdir = /tmp


server-id = 200

binlog-do-db = DB_to_replicate

log-bin = /var/log/mysql/mysql-bin.log

binlog_format = MIXED;

auto_increment_increment = 2

auto_increment_offset = 2



slave-load-tmpdir = /tmp

3) Taking a valid Snapshot of the dataset on a new Environment ther is no problem because it will start from scratch, but what if we have to do it on a Master with an existing data set?

The easy answer is to load a valid backup in the Slave(s).

But how to take a valid backup? (read Above) ;-)



4) Setting up the Replication on slaves

On Site_A



Master Log file and Master log position can be taken from the OTHER SLAVE/MASTER with SHOW MASTER STATUS:

So connect to SITE_B and execute SHOW MASTER STATUS


| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |


| binlog.000002 | 106 | | |


1 row in set (0.00 sec)

Check that the settings are taken correctly :


Start the slave:


Check the slave status again and see if it is running correctly:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

On Site_B



Master Log file and Master log position can be taken from:

the dump like HEAD -n50:

-- Position to start replication or point-in-time recovery from


-- Table structure for table 'columns_priv'

or from the master with SHOW MASTER STATUS:


| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |


| binlog.000006 | 106 | | |


1 row in set (0.00 sec)

Check that the settings are taken correctly :


Start the slave:


Check the slave status again and see if it is running correctly:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5) Do final check to be sure about data propagation

On Site_A


from mysql console:


create table tb1(a int, b char(3);

Use rep_test;

show tables;

On Site_B


Show databases;

Use rep_test;

If all went fine both sites will have database and table.

At this point check IF also from Site_B the actions are replicated.

On Site_B



SET GLOBAL read_only=0;

Drop database rep_test;

Show databases;

SET GLOBAL read_only=1;

On Site_A


Show databases;

Database rep_test should not be there anymore.

Summary of the actions to perform:


1) Check/Assign GRANTS

2) Modify configuration files on boths sites

3) Take snapshot if requested

4) Register Master on SLAVE(S)

5) Do final check.


Setting up Master - Master replication (with both nodes acting as the ACTIVE node)
MySQL 5.1 or above

If you want to have MASTER-MASTER
Follow the steps above only keep commented the :
Also on the Site_B and that's it.






It is possible to write a very simple custom script which will check the replication from the SHOW SLAVE STATUS.

Some of them are also on my site.

Or I can write some for you just This e-mail address is being protected from spambots. You need JavaScript enabled to view it me

Or you can use the MAATKIT which has two very useful little tools:

mk-table-checksum - Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers. (

mk-table-sync - Synchronize MySQL tables efficiently. (

BUT what is the meaning of the MySQL SHOW SLAVE STATUS output?


It is not good to re-invent the wheel or to rewrite something already written and clear.

So go here for details:




HELP!!!!!! My replication is broken....


This e-mail address is being protected from spambots. You need JavaScript enabled to view it we will be happy to help. ;-)


Last Updated on Sunday, 18 August 2013 18:52

Page 14 of 14

Who's Online

We have 34 guests online