Mysql Administration

return to DevMysql

Basic Mysqldump

From linux command line.
Note: time command will output the time it takes, useful as a benchmark
# backup selected databases
time mysqldump DB_NAME | bzip2 > /tmp/DB_NAME-$(date +%Y%m%d).bz2

# restore
time bunzip2 < /tmp/DB_NAME-$(date +%Y%m%d).bz2 | mysql -uUSER -p

Reference: mysqldump Man

Show Running Queries

$ mysql -uroot -p -e "SHOW PROCESSLIST"

Reference: http://forge.mysql.com/tools/tool.php?id=42

Create Users

Create different types users and assign privileges
# create local user with
mysql> CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'secret_pw';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;

# create user with CRUD powers
mysql> CREATE USER 'crud_user'@'%' IDENTIFIED BY 'crud_pw';
mysql> GRANT INSERT, SELECT, UPDATE, DELETE ON some_db TO 'crud_user'@'%'

# create wildcard user with limited powers
mysql> CREATE USER 'read_only'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT SELECT ON some_db TO 'read_only'@'%';

# never forget
mysql> FLUSH PRIVILEGES;

# check privileges
mysql> SELECT * FROM mysql.user WHERE User='crud_user';

References:

References

Kill Mysql Queries (forge.mysql.com)
Valid XHTML 1.0 Transitional • Valid CSS • WikkaWiki