Tag Archives: mysql

Restart slave thread in MySQL when specific error occurred

by

On one of slaves I got error:

write failed: No space left on device (28)

I also found that slave got 1062 error (“Duplicate entry”) and stopped. I cleaned up some free space (old logs). When I tried to restart it with pt-slave-restart then I found that IO_Thread downloads binlogs from master and uses all free space again.

As a workaround – I decide to start just SQL_Thread, let it process all relay logs and then start IO_Thread again.

This is quick bash oneliner I created, which checks replication and if 1062 error exists then does skipping and starting SQL_Thread again.

while true; do if [[ $(mysql -e "show slave status\G" | grep "Last_SQL_Errno: 1062" -c) -gt 0 ]]; then mysql -e "set global sql_slave_skip_counter=1; start slave sql_thread;"; fi; done

This was enough to get issue fixed.

How quickly to check time of mysql query by running it N times

by

After query optimization I just needed to run query several times to check time and compare it with original query. Query code was placed into query.sql file.

TIMEFORMAT=%R && for i in {1..10}; do time mysql < query.sql > /dev/null; done

Continue reading …

How to get list of indexes in MySQL

by

This is query which returns the list of indexes in mysql:

SELECT table_name,
       index_name,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) as 'columns_list'
FROM information_schema.statistics
GROUP BY table_name, index_name;

Install Percona repo on Fedora 17

by

While trying to install Percona repo on Fedora 17 I got an error:

[user@fedora17 ~]$ yum list | grep percona
http://repo.percona.com/centos/17/os/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found : http://repo.percona.com/centos/17/os/x86_64/repodata/repomd.xml
Trying other mirror.
Error: failure: repodata/repomd.xml from percona: [Errno 256] No more mirrors to try.

The fix is easy: update baseurl for Percona repo (/etc/yum.repos.d/Percona.repo)
Continue reading …

Way to check what mysql config used

by

Recently I needed to know if mysql is using any config (.cfg) except default one (/etc/my.cfg).
This command allows to check things quickly:

strace mysql ";" 2>&1 | grep cnf

Quick hint: analyzing mysql general query log

by

I had a long running transaction and general log enabled.
So I wanted to check which queries caused transaction to be running for a long time.

If you have thread id for a long running transaction:
(you can get it from “mysql> show engine innodb status;” or pt-deadlock-logger in case of deadlock)
then you can grep log by thread id and then use pt-query-digest and analyze log

sudo grep -i " <thread id> " <genera-log-filename> > somefile.txt
sudo pt-query-digest --type genlog  somefile.txt

Syncing MySQL tables by pt-table-checksum when there is no unique key

by

Today I needed to checksum and sync tables in simple master-slave replication.
So I used percona-toolkit for this.

Following checksumming (command should be running on master) of tables shows that repl.t9 and repl.t1 tables have differences:

$ pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table --ask-pass h=localhost,u=root
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
05-26T01:50:30      0      1        4       1       0   0.016 repl.t1
05-26T01:50:30      0      1        6       1       0   0.016 repl.t9

Let’s run sync and check result:
Continue reading …

How to move mysql datadir

by

This example will show you how to move MySQL datadir to another dir (in my case to “/mysqldata”) step-by-step (OS: Linux Mint):

1. create new dir (/mysqldata) and set permissions to it

sudo mkdir /mysqldata
sudo chown mysql:mysql /mysqldata/

Continue reading …