Mysql

Sometimes you need to kill multiple SQL queries at once. This may be time consuming, since the MYSQL kill command can have only one argument. A simple hack is to create a script that will do it for us. First we start by listing the processes we wish to kill. This is already outlined here https://support.qualityunit.com/245419-Show-processlist-without-sleep-command You may want to fine-tune this script, if you want to kill processes that run longer than x seconds. SELECT * FROM information_...

Once your Mysql server gets into troubles and you need to identify SQLs with the worst performance, the first thing every server administrator is doing is to open mysql console and start digging in the SHOW PROCESSLIST or SHOW FULL PROCESSLIST. On servers with many concurent connections to many databases you will see a lot of of rows with commands with text: Sleep. In time when I'm hunting slow sqls I don't want to be disturbed with those sleeping connections and I want to see just real troubl...

In case your Mysql or Mariadb replication crashed with following error, here are steps we do to recover replication: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.006270' at 357275228, the last event read from 'mysql-bin.006270' at 4, the last byte read from 'mysql-bin.006270' at 4.' In our case was the root of problem crash of master server, therefore the slave didn't fini...

Here is SQL command you should use, when you need to calculate size of databases on your Mysql server directly from mysql console: SELECT table_schema "DB Name", sum( data_length + index_length ) / 1024 / 1024 "DB Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema ; Enjoy!

Recently we have been facing higher load on our mysql servers and we decided to migrate our databases to Galera cluster based on MariaDB. One of the restriction with Galera Cluster is, that it doesn't support MyISAM tables, just InnoDB engine is supported for master-master synchronization. So here is a easy way how to migrate Mysql database with MyISAM tables into Galera and InnoDB: - Make sure your db schema doesn't contain FULLTEXT indexes or any other constructions, which are not...

Here are steps you should do to upgrade your older MariaDB 10.1.x to the latest version of MariaDB 10.1.x. 0. Prepare for upgrade - make sure no traffic is coming to database server - make backup of configuration files /etc/my.cnf* - backup all your databases running on the server in case something goes wrong during upgrade 1. Update yum repository config located in /etc/yum.repos.d/MariaDB.repo with new content. You can generate your config file here: https://downloads.mariadb.org/mariad...

To reduce used space by Mysql InnoDb tables (we use file per table setting), you need to optimize table. Otherwise the InnoDB data file just grows. Even if you delete some rows from table, free space for deleted rows is correctly reused later by new rows, but data file on disk will never reduce the size alone. IMPORTANT: - small tables NO NEED to optimize - there is no big win to do that - huge tables SHOULD NOT be optimized on live server - it will lock your table for minutes/hours Re...

Sometimes mysql replication crash with corrupted relay binlogs and it is not possible to restart it with simple "start slave" command. Here is step by step manual how to fix it. To check the current slave status execute command: show slave status\G You should see result similar to this: ************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.194.74 Master_User: replicator Master_Port: 3306 Connect_Retry: 60 Mast...