MySQL

From Briki
Revision as of 18:04, 19 February 2010 by Andrew (talk | contribs) (Getting rid of /var/log/mysql.*)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Assigning passwords to users

Login to mysql as the relevant user and run:

SET PASSWORD = PASSWORD('biscuit');

Creating new users

Login to mysql as root, and run:

GRANT ALL ON database.* TO [email protected] IDENTIFIED BY 'password';

Or, to create a user with no password:

GRANT ALL ON database.* TO [email protected];

To allow login for a user from a remote host (2 lines are needed because, without the first, the user privileges default to those of the anonymous local user):

GRANT ALL ON database.* TO [email protected] IDENTIFIED BY 'password';
GRANT ALL ON database.* TO [email protected]'%' IDENTIFIED BY 'password';

Obviously, different privileges can be assigned to databases and tables. To revoke privileges, the syntax is:

REVOKE ALL ON database.* FROM [email protected];

Show privileges

SHOW GRANTS FOR 'user'@'host';

Recover all corrupt tables

sudo find /var/lib/mysql -name *.MYI -exec myisamchk -r {} \;

Copying a database between hosts

  • On the source:
mysqldump <db_name> -u root -p > file.sql
  • On the target:
mysqladmin create <db_name> -u root -p
cat file.sql | mysql <db_name> -u root -p

Investigating problems

  • mytop --prompt will show long-running/large queries
  • Turn logging on in /etc/mysql/my.cnf to trace all queries (though this will slow the server down)

Getting rid of /var/log/mysql.*

These files never get written to, but apparmor creates them anyway. Comment out the appropriate lines in /etc/apparmor.d/usr.sbin.mysqld. Does this work, or can we simply delete them and they'll disappear forever?