Tag Archives: mysql

mysql 5.6 GTID global transaction identifier

Posted by on July 21, 2015 No comments

Wow! It`s a really nice feature. Now you can do very easy replication.
i.e. In pre 5.6 you should create replica like this:

1. Turn on binary logs at master

 vi /etc/mysql/my.cnf
 server-id              = 11
 log_bin                 = /var/log/mysql/mysql-bin.log
 # WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
 expire_logs_days        = 10
 max_binlog_size         = 100M
 binlog_do_db            = mydatabase
 #binlog_ignore_db       = include_database_name
 binlog-format=ROW     #I MIXED and STATEMENT sometimes not good
binlog-checksum=crc32  # 5.6 feature speed up binlog
gtid-mode=on           #Use force, Luke

2. Create replication User

 grant replication slave on *.* to 'repl_user'@'%' identified by 'SecurePassword';

3. Dump all databases

mysqldump --master-data=2 --single-transaction --events --routines --triggers --all-databases  > database.sql

4. On slave after dump restore

 CHANGE MASTER TO MASTER_HOST='masterHost', MASTER_USER='repl_user',MASTER_LOG_FILE=, MASTER_LOG_POS=,
 MASTER_PASSWORD='SecurePassword';
 START SLAVE;
 show slave status;

But at 5.6 On slave

change master to MASTER_HOST='masterHost", MASTER_AUTO_POSITION=1, MASTER_USER=’repl_user’, MASTER_PASSWORD=’SecurePassword';
START SLAVE;
show slave status;

P.S. If you need to skip one request on slave:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Mysql can do partitioning on the fly :)

Posted by on July 23, 2013 No comments

Mysql tuning in action…
Yesterday me, and our development team made some tuning of one old project we have couple of tables with 1-10 million records.
It`s NOT BIGDATA, but application makes huge writes to this table.
Only current date records, but you understand partitioning affects index size.
Table can be partitioned by date field. So how to do it:

  1. make sure that dt not null.
  2. recreate primary key. Field for partition should be part of primary key.
  3. create partition for old years and partition for every month.

Read more »

Mysql slave lag monitoring

Posted by on May 16, 2013 2 comments

Everybody know

SHOW SLAVE STATUS;

Also everybody know that ‘Seconds_Behind_Master’ shows difference in seconds between the slave SQL thread and the slave I/O thread.

Sometime it shows nonsense, and if you build monitoring It is not good practice to use ‘Seconds_Behind_Master’
Example from real life:
If replication becomes stalled due to connectivity problems, Seconds_Behind_Master shows 0 while replica is far away from master, changing timeout values not help. 🙁 i mean:

slave_net_timeout=300

So we implement following monitoring – every 3 second write current timestamp at master.
and check replica delay.
Or You can use percona heartbeat, It do almost the same.
Read more »

Mysql check auto_increment values

Posted by on January 10, 2013 No comments

We get old project, with braindead architecture.
Database structure provides surprises regularly. Yesterday we had a problem with maximum integer value reached at one column.

‘id’ at one table was auto_increment and “INT” not UNSIGNED INT or BIG INT, so after reaching id value 2147483647 application stop working, I change type to UNSIGNED BIGINT.

ALTER TABLE ad_campaigns_rates MODIFY id BIGINT UNSIGNED  not null auto_increment, AUTO_INCREMENT = 2147483648;

Read more »

Mysql How to delete all triggers

Posted by on October 29, 2012 5 comments

I`v got an issue from our development team to dump all triggers from prod db and add them to development db.
It`s trivial, except all triggers deletion. Everyone use magic, procedures, etc.

How to dump only triggers:

/usr/bin/mysqldump --single-transaction  --triggers --routines --no-data  --no-create-db --no-create-info
-u backup -pMEGASECUREPASSWORD
-h dbslave1-1.randomthemes.com database_name > /home/backup/mysql/database_name_triggers.sql

How to delete all triggers:

Fastest way (a little bit hindy 🙂 🙂 but working o.k.): Upload trigger names to file, apply regexp, delete

$mysql -e "SELECT trigger_schema, trigger_name from information_schema.triggers" > ./drop_all_triggers.sql

regexps (use vi, Luke):

:%s/\n/;\r/g
:%s/database_name/DROP TRIGGER IF EXISTS database_name/g
:%s/\t/./g
$mysql mydatabase < ./drop_all_triggers.sql

phpmyadmin #1045 Cannot log in to the MySQL server

Posted by on July 17, 2012 No comments

Our developers really crazy about phpmyadmin 🙂 and ask me to setup one.
I really prefer console…
Setup in ubuntu is easy just use aptitude, Luke…
Only change I did is to add Basic Auth to phpmyadmin url, to prevent ugly internet bots from trying to bruteforce my databases.

htpasswd /etc/phpmyadmin/htpasswd.setup developers

But when I try to login to my DB interface I`v got this damned error.
#1045 Cannot log in to the MySQL server
Very very strange! I can login via console with this username…
I create user with following credentials:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,CREATE ROUTINE,
       CREATE TEMPORARY TABLES,CREATE VIEW,EXECUTE,INDEX,SHOW VIEW, ALTER ROUTINE
       on *.* to 'developer'@'%' IDENTIFIED by 'MEGASECUREPASSWORD';

phpmyadmin refuse this user. I turn on mysql debugging.
Read more »

My mysq server databases backup script with Percona

Posted by on July 10, 2012 No comments

Simple, but working o.k.

#!/bin/bash
BDIR="/home/backup/mysql"
#DATAPATH=`date +%m%d%Y%H%M%S`

# Run backup
/usr/bin/innobackupex --user=root --password=MEGASECUREPASSWORD --slave-info $BDIR

LASTBACKUP=`ls -1 -t  $BDIR | head -n 1`

/usr/bin/innobackupex --user=root --password=MEGASECUREPASSWORD  --apply-log $BDIR/$LASTBACKUP

Some mysql howto

Posted by on June 19, 2012 No comments

This topic was done to skip googling in some cases.

How to get mysql database size:

mysql -uroot -pPASSWORD -D DATABASE_NAME -e "show table status\G"| egrep "(Index|Data)_length" | awk 'BEGIN { rsum = 0 } { rsum += $2 } END { print rsum }'

Source: http://eddnet.org/?p=1765

How to set mysql to read only mode:
Attention! root can do r/w regardless of read_only!

mysql> set GLOBAL read_only = true;

edit config

:/etc/mysql/conf.d# cat slave.cnf
[mysqld]

server-id = 18
innodb_buffer_pool_size = 200MB
read_only = true

How to drop user

DROP USER 'USERNAME'@'%';

How to find duplicate rows.

SELECT *,count(id) AS Num FROM clicks_log GROUP BY id HAVING (COUNT(id) > 1 );

How to delete duplicated Rows
I am not SQL guru, maybe there is another perfect way how to do it, but it`s works:

mysql> create table id_tmp AS (SELECT id FROM block_news_views_log GROUP BY id HAVING (COUNT(id) > 1) ) ;
mysql> DELETE  FROM block_news_views_log WHERE ( id IN (SELECT * from id_tmp ) );
mysql> DROP table id_tmp;
mysql> SELECT id FROM block_news_views_log GROUP BY id HAVING (COUNT(id) > 1) ;