Comparing mydumper vs pure mysqldump

Today I tested mydumper.

It showed very cool results, see below.

Data size + index size is 9054MB. Here is timing of dumping/restoring the same database using mydumper vs mysqldump.

     |  mysqldump/restore dump |mydumper/loader
dump   | real    1m36.642s     |real    0m49.342s
restore| real    32m32.732s     |real    20m32.215s

Also during compilation I faced with issue:

mydumper.c:2814: error: ‘MYSQL_TYPE_JSON’ undeclared (first use in this function)
mydumper.c:2814: error: (Each undeclared identifier is reported only once
mydumper.c:2814: error: for each function it appears in.)
make[2]: *** [CMakeFiles/mydumper/mydumper.c.o] Error 1
make[1]: *** [CMakeFiles/mydumper/all] Error 2
make: *** [all] Error 2

Probably because I am using Percoan 5.6 dev package and JSON data type was introduced only in 5.7. I have commented 2 lines with the 

./mydumper.c: /* if (fields[i].type == MYSQL_TYPE_JSON) g_string_append(statement_row, "CONVERT("); */

./mydumper.c: /* if (fields[i].type == MYSQL_TYPE_JSON) g_string_append(statement_row, " USING UTF8MB4)"); */

I hope it will not affect me unless I am using 5.7+.

Докеризация NOXBIT для простомтра torrent-tv.ru

Я использую готовый образ, но обновляю в нем бинари NOXBIT.


Создатим docker файл:
cat << EOF > Dockerfile
FROM sybdata/noxbit
EXPOSE 6689 9903 6881 6882 6883 6884 6885 6886 6887 6888 6889 6890 6891 6892 6893 6894 6895 6896 6897 6898 6899 6900
RUN wget http://download.noxbit.com/noxbit-pa-x86-64.tar.gz
RUN tar xvf noxbit-pa-x86-64.tar.gz -C /opt/noxbit/
RUN sed -i 's/-uid=*./-uid=YOUR_ID/' /opt/noxbit/noxbit.cfg
RUN sed -i 's/-pass=*./-pass=PASSWORDS_FROM_NOXBIT/' /opt/noxbit/noxbit.cfg


ENTRYPOINT /opt/noxbit/STM-Hypervisor -config=/opt/noxbit/noxbit.cfg

EOF

######
Сбилдим образ:
sudo docker build -t my:noxbit .

######

Запускаем контейнер:

sudo /usr/bin/docker run --dns 8.8.8.8 -p 6689:6689 -p 6881:6881  -p 6882:6882  -p 6883:6883  -p 6884:6884  -p 6885:6885  -p 6886:6886  -p 6887:6887  -p 6888:6888  -p 6889:6889 my:noxbit


Все!!!

Доходчиво про индексы MySQL

Ссылка для прочтения.

Troubleshooting row-based replication issues

Hi,

I would like to describe my problem: one of slave server got a permanent issue with replication delay, there was a setup with following topology:
master--STATEMANET BINLOG-->slave1--ROW format-->slave2-ROW format.

Slave2 had replication delay that was continuously increasing.


One of the slave thread in processlist:

*************************** 3. row ***************************
         User: system user
         Host:
           db: NULL
      Command: Connect
         Time: 8935374
        State: Reading event from the relay log
         Info: NULL
    Rows_sent: 0
Rows_examined: 0

           Id: 5

Part of "show engine innodb status":

---TRANSACTION 4675333, ACTIVE 17629 sec fetching rows
mysql tables in use 1, locked 1
28346 lock struct(s), heap size 2618920, 1921370 row lock(s), undo log entries 3220
MySQL thread id 5, OS thread handle 0x7f8c8060e700, query id 21 Reading event from the relay log

Hm, what is going on? No queries in processlist. Then I checked what was in positions in relay logs form show slave status command:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: X.X.X.X
                  Master_User: XXXXX
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db-xxx-bin.001368
          Read_Master_Log_Pos: 9430623
               Relay_Log_File: db-xxx-relay.001192
                Relay_Log_Pos: 409141308        Relay_Master_Log_File: db-xxx-bin.001290
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

sudo mysqlbinlog -v  --base64-output=DECODE-ROWS --start-position=409141308  db-xxx-relay.001192:

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 409141389
#160618 20:06:24 server id 701020101  end_log_pos 409141521 CRC32 0x04f7b62f    Table_map: `db`.`TABLE` mapped to number 1002
# at 409141685
#160618 20:06:24 server id 701020101  end_log_pos 409149640 CRC32 0xaad0c198    Delete_rows: table id 1002
# at 409149804
#160618 20:06:24 server id 701020101  end_log_pos 409157762 CRC32 0x7f27178b    Delete_rows: table id 1002
# at 409157926
#160618 20:06:24 server id 701020101  end_log_pos 409165890 CRC32 0x118c26ad    Delete_rows: table id 1002
# at 409166054
#160618 20:06:24 server id 701020101  end_log_pos 409173977 CRC32 0x137acdbd    Delete_rows: table id 1002
# at 409174141

.....

So here we can see that  table "TABLE" is being cleaned up by some clause. I would like to add that this table contains a lot of records, let's find out SQL code(we use -v  --base64-output=DECODE-ROWS options):

...

#160618 20:06:24 server id 701020101  end_log_pos 928334665 CRC32 0x790815e0    Delete_ro
ws: table id 1002 flags: STMT_END_F
### DELETE FROM `db`.`TABLE`
### WHERE
###   @1='data1'
###   @2='data2'
###   @3='data3'
###   @4=86561281
###   @5='data3'
###   @6=2016-09-13 14:00:00
###   @7=1111
###   @8='Friday'
###   @9='14:30'
###   @10='14:00'
###   @11=14
###   @12='May'
###   @13='14:10'
###   @14='14:13:57'
###   @15=NULL
###   @16=2016-06-13 14:43:57
###   @17=2016
..... 

So  there is DELETE statement for each row with clause that includes every column. I mentioned that the table is pretty big, that is why we got replication delay. I checked table TABLE definition and found that it had been created without primary key definition. And "DELETE from TABLE where column <> 'data1'"(as example) created a lot of events in binary log of slave1. Slave2 have to perform full scan for each record. I'll try to fix it by creating appropriate primary key on master server.

Understand right file_per_table=ON

Let's imagine situation:

You have 100 DBs on your MySQL server and in some point of time you realised that your InnoDB space had been consumed on 80% and you chosen to migrate to file_per_table, luckily it is a dynamic parameter. Well, it's a good idea if you have enough disk space on the host. It easer than adding additional InnoDB file right? You ACKed the alert in your monitoring system and forgot this problem.

This is wrong approach, I will explain why:
It will use separate tablespace only for newly created tables, and if you have some growing tables in system tablespace, it will keep on consuming system INNODB. Don't forget to move tables to separate file by altering table.

How to find tables in SYSTEM InnoDB space:
Select NAME from information_schema.INNODB_SYS_TABLES where SPACE =0 and name not like "SYS_%";

According to MySQL docs SPACE =0 means that it is using SYSTEM InnoDB space.

mysql pam_auth problem on MacOs

I tried to use pam auth using mysql client on MacBook. I got the error below using percona/orcale mysql binaries:

mysql -hxxx.yyy.com -p -uUSER
Enter password:
ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: dlopen(/opt/local/lib/percona/plugin/dialog.so, 2): image not found


How to fix:
You can install mariadb bins from ports collections and use mariadb's mysql bin. But I just copied "mariadb/plugin/dialog.so" lib to my percona's plugin directory. It works fine.

Respawning process on CentOS/RHEL 6

In  Centos/RHEL 5 was another approach with /etc/inittab file, but in Centos/RHEL6 System V was changed by Upstart system.

Here is config example:

[tsolodovnikov@SomeServer ~]$cat /etc/init/dbmap.conf
#
## This service maintains dbmap monitor process.
#
start on startup
start on runlevel [2345] and stopped rc RUNLEVEL=[2345]
stop on runlevel [S016]


respawn
respawn limit 10 120
exec ruby -C /was/chnaged/just/in/case/ /usr/bin/rake RAILS_ENV=production database:monitor
--------------
[tsolodovnikov@SomeServer ~]$ sudo start dbmap
dbmap start/running, process 9027

Now if you gonna kill PID 9027, new process will be created.