Category Archives: MySQL

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

skip-innodb

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;