Mysql authentication backwards compability

Had an issue today where an old mysql-client on debian sarge failed to connect to a newer mysql-server on debian etch.

I was happy to find out that it’s easily fixed :

update mysql.user set password=OLD_PASSWORD('supahsecretpassword') where user='theuser' and host='some-ip-address';

then just :

flush privileges;

and you’re gooooood to goooooooooo :)

Tags: , ,

MySQL DB sizes

Wondering how big your database is, and whats taking up the space?

Im running a Bacula server for backing up about 50 servers, and the MySQL dump of the db is 10gb.
Im wondering, what the hell is all this shit, which table is the large one etc.

mysql> SELECT
concat(table_schema,'.',table_name),concat(round(table_rows/1000000,2),'M')
rows,concat(round(data_length/(1024*1024*1024),2),'G')
DATA,concat(round(index_length/(1024*1024*1024),2),'G')
idx,concat(round((data_length+index_length)/(1024*1024*1024),2),'G')
total_size,round(index_length/data_length,2)
idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT 10;

This will save your life, then run:
mysql> OPTIMIZE TABLE bacula.File;

And let the cleanup begin :)

Tags: , , , ,

Setting up mysql replication

This is actually pretty easy. It is well documented here, but I’ve made a quicklist :

Edit my.cnf on the master. It should contain :
server-id = 1
log-bin=mysql-bin

Edit the my.cnf on the slave. It should contain :
server-id = 2

First grant access to a replication user on the master server:
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slavehost.example.com' IDENTIFIED BY 'password';

Lock the master:
FLUSH TABLES WITH READ LOCK;

Get the master status for later use :
SHOW MASTER STATUS;

Dump the master db with mysqldump:
$ mysqldump mybase > mybase_out.sql

Import the master base on the slave :
$ mysql mybase < mybase_out.sql

Set the master info on the slave :
CHANGE MASTER TO MASTER_HOST='masterhost.example.com', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=13963;

Start up the slave :
START SLAVE;

And finally release the master lock by exiting your mysql session.

Tags: ,