MySQL
Quick Reference
# Setting Password
SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
SET PASSWORD = PASSWORD('biscuit'); # for your own account
GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';
FLUSH PRIVILEGES; ## DON'T FORGET THIS!!
DROP USER 'jeffrey'@'%';
DROP DATABASE jeffrey; # if database name == jeffrey
Merging Servers
I'm trying to merge two MySQL servers into one server
Copying User Data
I did this to dump the contents only from the 2nd server. I really don't want it to delete the previous entries.
mysqldump --skip-opt -t -S /tmp/mysql.sock2 -u root -p mysql > mysql2.dump
Then I opened the dump file in one window and mysql attached to the destination server in the other. Then I could query the destination server and compare the results with the dumpfile from the source server. If any entries would collide, I wanted to know about it and delete the source or destination entry I didn't want. Once I was confidant there would be no collisions, I ran the dump file against the destination server.
Low Memory Causes Row Level Permissions to Fail
I had a recent problem where row-level permissions weren't working. Show grants didn't show any row-level permissions and trying to grant them caused the server instance to crash. After much toil, I figured out it was memory. I was playing around on my VM which has only 188Megs of ram. The mysql log showed a dump of stuff from when the server instance would crash, including how much memory it needed. It wasn't until I'd been staring at it for a while that I realised that number was larger than my total RAM. I adjusted the values for caches and whatnot in my.cnf and suddenly row level permissions stared working.