mysql_scripts.sh

# quick dump
mysqldump --compact --skip-opt --skip-lock-tables --single-transaction -q "$@" | pv | gzip > dbdump.gz

# create user
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'newuser'@'%';
FLUSH PRIVILEGES;

# remove / grant privileges
DELETE FROM mysql.db WHERE user='phabricator';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, DROP, SHOW VIEW ON `phabricator\_%`.* TO 'phabricator'@'%';

# remove anonymous users
delete from mysql.user where password='';

# change password
SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');

# raw user edit (in case grant/revoke not working)
select user,host,password from mysql.user;
delete from mysql.user where password = '';

# utf8 issues
CREATE DATABASE $db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
ALTER DATABASE $db CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; # migrates old records

# reset root password
service mysql stop
mysqld_safe --skip-grant-tables &
mysql --user=root mysql
update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit;
pkill mysqld