I just encountered the problem described in the title, on a server I don’t have enough free space for create a backup as long the replica is catching up, because the relay logs eats all the available free space as the IO thread works faster than the SQL thread in the replication.
What to do? First, we need to get rid of the current relay logs, and then limit the server to not create too much in the future. The relay logs are not meant for manual deletion so we stop the replica, purge the relay logs, set back the replica to the previous position and then restart the mysqld with the relay_log_space_limit variable set up.
mysql> STOP SLAVE;
mysql> PAGER egrep "Relay_Master_Log_File|Exec_Master_Log_Pos"
mysql> SHOW SLAVE STATUS\G
mysql> RESET SLAVE ALL;
mysql> CHANGE MASTER TO ...
mysql> START SLAVE;
bash> echo "relay-log-space-limit = 10737418240" >>/etc/mysql/my.cnf #10G
bash> systemctl mysql restart
As Jean-François Gagné pointed out (right) this is only applies on a really simple case when there is no GTID configured, no parallel replication exists, the slave is not delayed, no SSL, etc.etc.
I started to use Percona Monitoring and Management (PMM) recently because it seems promising, and my friends from Percona always recommending it to try out, and frankly, at first sight, I like it.
There are few things which I am not happy about, but mostly I feel OK – but when it comes to the price/value evaluation it becomes better – it is free.
However I found a really disturbing problem, what is bugging me – it needs AWS credentials to discover hosts on RDS. Let me show you.
Continue reading “PMM and IAM Roles”
It was a long time ago so I won’t write about the conference (it was good as always), but at least I share the slides of my talk here.
I just created the Budapest MySQL Meetup group. I hope there will be interest for that, the first event is under organising. Check it if you are near Budapest!
Earlier this week we had a discussion with fellow DBAs about our mysql prompts, and at the end of the day it showed up, that a lot of us hit the same problem.
The problem is, that when you set up your mysql prompt then ‘\h’ will be resolved to ‘localhost’ when you connect locally – instead the name of your host as you expect it. It always bugs me, and once I spent a good afternoon figuring out how to workaround this.
Well, the workaround is not a big deal, because you can insert any text into your mysql prompt, and after you realise that you can do it, then it is easy: just put the hostname into your prompt with your chosen provisioning tool and that will do.
Continue reading “My MySQL command prompt”
Saturday I was in my favorite grocery store, standing in the line, browsing the net on my phone. I read Vadim Tkachenko‘s blog post about Measuring Percona Server Docker CPU/network overhead and his findings were opposite than mine – he didn’t found any measurable difference. Reading his post, he did found huge impact in networking which I didn’t check, so I was re-run my checks again, but now with paying attention to network configuration.
Continue reading “MySQL/docker performance report update”
Back in October I have write about possible ways of running multiple MySQL instances on the same hardware. As the months passing by, the project of splitting our database schemas into standalone instances is closing in, so I started to check the different ways.
EDIT: This post is outdated, here is the follow up.
Continue reading “MySQL in docker or native – performance benchmarks”
With MySQL, it is relatively easy to create “point in time” restores. All you need is recent(ish) backup and a bunch of saved binary logs. You can restore the backup you have, and when it is completed, you can use mysqlbinlog utility to apply your saved binary logs to the desired state of your database.
I have created a simple go application to make your life easier. You can find it on my GitHub page.
Continue reading “Streaming MySQL binary logs for backup”
Currently, we have one database cluster with 15 different schemas – these schemas could be either schema which contains “real” data, or just schemas with metadata.
I guess the next evolutionary step of our database stack would be to split up the database cluster vertically along these schemas. All the data schemas should be moved to standalone mysql instances and put the metadata schemas next to them. This also could be a good project for prepare to move a certain part of the database for example to a cloud provider while other parts are still kept on bare metal.
Continue reading “Running multiple instances on the same hardware”