Category Archives: MySQL

Compound Indexes

drop index INDEX_NAME on TABLE_NAME;

ALTER TABLE TABLE_NAME add index INDEX_NAME(col1, col2);

MariaDB replication with GTID

Improves resiliency of replication in case of primary crash

https://mariadb.com/resources/blog/enabling-gtids-server-replication-mariadb-100

InnoDB Cluster config

There is not a lot of information that is easy to understand to get innodb cluster running,

The following is the terminal dump of what I needed to do to get it working, important notes.

you must bind mysql to 0.0.0.0

prior to adding a server to the cluster execute RESET MASTER; to clear the logs.

apt install python -y
wget http://dev.mysql.com/get/mysql-apt-config_0.8.4-1_all.deb
dpkg -i ./mysql-apt-config_0.8.4-1_all.deb
apt-get update
apt-get install mysql-server mysql-shell -y

mysql_ssl_rsa_setup –uid mysql

mysqlsh

\connect [email protected]:3306

dba.configureLocalInstance();

1) Create remotely usable account for ‘root’ with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel

Please select an option [1]: 2
Please provide an account name (e.g: [email protected]%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: [email protected]%

shell.connect(‘[email protected]:3306’);
var cluster = dba.createCluster(‘cluster name’, { memberSslMode: ‘REQUIRED’, ipWhitelist:’192.168.0.0/16,127.0.0.1/8′});

cluster.addInstance(‘[email protected]:3306’, { memberSslMode: ‘REQUIRED’, ipWhitelist:’192.168.0.0/16,127.0.0.1/8′});

GRANT ALL PRIVILEGES ON *.* TO ‘icroot’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
flush privileges

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

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.