MySQL

Three Ways of Backup

• Dump (backup) MySQL databases to XML

mysqldump -u $USER --password=$PASSWORD --all-databases --xml > mysql.sql.xml

• MediaWiki MySQL XML dump

php dumpBackup.php --full > group_wiki.sql.xml

• Dump MySQL databases using crontab

nice -n 19 mysqldump -u $USER -p$PASSWORD $DATABASE --default-character-set=$CHARSET -c | nice -n 19 gzip -9 > mysql-$DATABASE-$(date '+%Y%m%d').sql.gz

set CHARSET=binary or utf8
set DATABASE=--all-databases or group_wiki or group_web

Miscellaneous

List User

select * from mysql.user;
select User from mysql.user;

Delete User

delete from mysql.user WHERE User='name';

Show character set

status: Server characterset

SHOW CREATE TABLE text: DEFAULT CHARSET