Tag: MySQL

MariaDB Galera: Recover Cluster after full crash

Monowar Leave a comment
  • Selecting the right node: Look at the grastate.dat file on each server to see which machine has the most current data. The node with the biggest seqno is the node with the current data

    /var/lib/mysql/grastate.dat

  • In the same server execute the following command to bootstrap

    mysqld –wsrep-new-cluster

  • Next login to node 2 and start MySQL
  • Once its success, start MySQL on node 3
  • Once the cluster is stable, stop the bootstrap process in node1 and start it using systemctl.

MySQL: Terminate idle connections

Monowar 2 Comments

Manual cleanup:

  • Login to MySQL

    mysql -uroot -p

  • Run the following query

    select concat(‘KILL ‘,id,’;’) from information_schema.processlist where Command=’Sleep’;

  • Copy the query result, paste and remove a pipe ‘ | ‘ sign, copy, and paste all again into the query console
  • Hit ENTER

Automatic cleanup:

  • Configure mysql-server by setting a shorter timeout on wait_timeout and interactive_timeout
  • Check your existing configuration using the following command

    show variables like “%timeout%”;

  • Set with:

    set global wait_timeout=3; set global interactive_timeout=3;

Logstash: MySQL – Slow Log Grok

Monowar Leave a comment 
^# User@Host: %{USER:user}\[%{USER:current_user}\]%{SPACE}@%{SPACE}\[%{IP:ip}\](.|\r|\n)*#%{SPACE}Thread_id:%{SPACE}%{NUMBER:thread_id:int}%{SPACE}Schema:%{SPACE}%{USER:schema}%{SPACE}QC_hit:%{SPACE}%{USER:qc_hit}(.|\r|\n)*# Query_time: %{NUMBER:query_time:float}%{SPACE}Lock_time:%{SPACE}%{NUMBER:lock_time}%{SPACE}Rows_sent:%{SPACE}%{NUMBER:rows_sent:int}%{SPACE}Rows_examined:%{SPACE}%{NUMBER:rows_examined:int}(.|\r|\n)*# Rows_affected:%{SPACE}%{NUMBER:rows_affected:int}(.|\r|\n)*SET%{SPACE}timestamp=%{NUMBER:timestamp};%{GREEDYDATA}

Reset Forgotten MySQL root Password for Windows

Monowar Leave a comment

If you forgot root password for your MySQL database server running in Microsoft Windows, You can reset it following the steps below.

  1. Stop your MySQL server. Start “services.msc” using Run window, and stop MySQL service.
  2. Open your MS-DOS command prompt “cmd” using Run window. Then go to your MySQL bin folder, e.g., “C:MySQLbin” Path could be different for your setup.
  3. Execute the following command in the command prompt:

mysqld.exe -u root –skip-grant-tables

  1. Leave the current MS-DOS command prompt as it is, and open another new MS-DOS command prompt window.
  2. Go to your MySQL bin folder again and enter “mysql” and press Enter.
  3. Select “mysql” Database. Type “use mysql;”.
  4. Execute the following command to update the password:

UPDATE user SET Password = PASSWORD(‘your_new_passowrd’) WHERE User = ‘root’;

After the procedure close the first command prompt. Now in second command prompt type “exit;”. You can now start the MySQL service as normal.