Category Archives: MySQL

What to do when MySQL or MariaDB shits the bed

Had a server outage mid transaction?

Now you can’t start MYSQL ?

You probably have a transaction stuck and the service won’t come back up until you deal with it…


Here is how you deal with it.

mysqld --tc-heuristic-recover=ROLLBACK

service mysql start

set connection multi-source Mariadb replication

SET @@default_master_connection=”

mariadb db replication reset

How to Reset ( Re-Sync ) MySQL Master-Slave Replication

MySQL ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal

mysql> create user test identified by password '12345';
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

You can resolve this by following the steps listed below.

mysql> select password('12345');
| password('123456') |
| 2ff898e158cd0311        |
1 row in set (0.00 sec)

mysql> create user test identified by password ’2ff898e158cd0311′;
Query OK, 0 rows affected (0.00 sec)

Mysql Remote Access

Need to free up some memory? Disable INNODB

Pop this into your my.cnf file


This will disable this database engine, hence saving you a LOT of memory resources.

mysql memory instances reduced from 30mb to 10mb of memory allocation. This is _VERY_ significant, especially when running on a limited resource VPS.

Mysql Caching

By default there is no caching enabled in MYSQL, to improve performance, add a little memory to the cache to help speed up responses by storing queries.

in mysql type

show variables like 'query_cache_size';

this will show the value of the current cache size, to modify the cache size use ->

SET GLOBAL query_cache_size = 16777216;

That sets the cache to 16mb of each instance of mysql that runs. Don’t over do this value if you are running on a VPS with limited resources.

How do you know if your cache size is right? Check out the Qcaches and look at the results

show status like "qcache%";

| Variable_name           | Value    |
| Qcache_free_blocks      | 42       |
| Qcache_free_memory      | 16431944 |
| Qcache_hits             | 339      |
| Qcache_inserts          | 759      |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 179      |
| Qcache_queries_in_cache | 188      |
| Qcache_total_blocks     | 451      |
The lowmem_prunes is the important value here,  it counts the number of  times the cache had to be free up spaces to cache a new result set.

Loading mysql db from bash term

create database dbname;

use dbname;

source dbname.sql;