Difference between revisions of "MySQL"
From Briki
| (7 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| + | == Logging in and switching to a DB == | ||
| + | <pre> | ||
| + | sudo mysql | ||
| + | show databases; | ||
| + | use <db>; | ||
| + | show tables; | ||
| + | </pre> | ||
| + | |||
== Assigning passwords to users == | == Assigning passwords to users == | ||
Login to mysql as the relevant user and run: | Login to mysql as the relevant user and run: | ||
| Line 23: | Line 31: | ||
REVOKE ALL ON database.* FROM myuser@localhost; | REVOKE ALL ON database.* FROM myuser@localhost; | ||
</pre> | </pre> | ||
| + | |||
| + | == Show privileges == | ||
| + | <pre> | ||
| + | SHOW GRANTS FOR 'user'@'host'; | ||
| + | </pre> | ||
| + | |||
| + | == Recover all corrupt tables == | ||
| + | <pre> | ||
| + | sudo find /var/lib/mysql -name *.MYI -exec myisamchk -r {} \; | ||
| + | </pre> | ||
| + | |||
| + | == Copying a database between hosts == | ||
| + | * On the source: | ||
| + | <pre> | ||
| + | mysqldump <db_name> -u root -p > file.sql | ||
| + | </pre> | ||
| + | * On the target: | ||
| + | <pre> | ||
| + | mysqladmin create <db_name> -u root -p | ||
| + | cat file.sql | mysql <db_name> -u root -p | ||
| + | </pre> | ||
| + | |||
| + | == 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?'' | ||
Latest revision as of 10:53, 13 March 2023
Contents
Logging in and switching to a DB
sudo mysql show databases; use <db>; show tables;
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 myuser@localhost IDENTIFIED BY 'password';
Or, to create a user with no password:
GRANT ALL ON database.* TO myuser@localhost;
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 myuser@localhost IDENTIFIED BY 'password'; GRANT ALL ON database.* TO myuser@'%' IDENTIFIED BY 'password';
Obviously, different privileges can be assigned to databases and tables. To revoke privileges, the syntax is:
REVOKE ALL ON database.* FROM myuser@localhost;
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?
