hosted services

MySQL is a nice little toy database. It's gotten better over the years but its not postgres, no where near. It's still good though, not bad in the sense Oracle is bad, not bad in the sense that Microsoft is bad either. It's not bad, therefore it's somewhere on the good scale.

If you want to convert some MyISAM tables to InnoDB then you can do the following:

show create table thing;

Replace CREATE TABLEthingwith `CREATE TABLE `thing_innodb and ENGINE=MyISAM with ENGINE=innodb.

Once done, do

insert into thing_innodb select * from thing

This could take a while, then once done do

rename table thing to thing_myisam, thing_innodb to thing

It's worth setting innodb file per table in my.cnf since innodb files are quite hard to shrink, which is an annoying feature.

[mysqld]
innodb_file_per_table

load data infile

Suppose you wish to import from csv (its a LOT faster):

load data infile '/var/tmp/data' into table wotsit (ip,likes,bag);
ERROR 1045 (28000): Access denied for user 'walkers'@'localhost' (using password: YES)

You'll need the global FILE priv:

grant file on *.* to 'walkers'@'localhost';

Log out and back in to mysql.

joins

There is a very good article about joins at the codeproject.

copying user access

At times if you have a group of mysql servers and various users have various passwords, you may want to copy the computed password from one to another.

Find the user's password like so:

select User,plugin,authentication_string from mysql.user;

alter user 'user'@'%' identified with mysql_native_password as '*FDSHFSFSJKHKFD...';

Note, when setting a password string, you use identified with mysql_native_password by '...', when using the hash, it is identified with mysql_native_password as '...'. (as, not by).