MySQL Admin Notes/Sheat cheet

My MySQL Admin notes.

I have a docker image with MariaDB (the new version of MySQL) here

Show users

Logon as root: mysql -uroot -p

use mysql
select host, user from user;

Create new schema/user

create database SCHEMA_NAME;
create user 'SCHEMA_NAME'@'localhost';
grant all privileges on SCHEMA_NAME.* to 'SCHEMA_NAME'@'localhost' with grant option;
set password for 'SCHEMA_NAME'@'localhost' = password('PASSWORD');

Test to login with the new schema: mysql -uSCHEMA_NAME -pPASSWORD

Allow to login from any host: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

Grant file privileges: grant file on *.* to 'jsiotpe'@'localhost';. This is needed for load_file.

Drop user

drop user 'SCHEMA_NAME'@'localhost';
drop database SCHEMA_NAME;

Make sure the users can’t login anymore: mysql -uSCHEMA_NAME -pPASSWORD

Allow root to logon from other/any host

Show hostname using the Show users command above.

update user set host=’%’ where user=’root’ and host=’HOSTNAME’;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;
flush privileges;

bind-address in my.cnf also needs to be updated to * or the IP of the host you want to connect from.

Enable query log

Assuming version 5.1 or higher.

SET GLOBAL general_log = 'ON';

show variables like 'general%';

Show grants

SHOW GRANTS FOR 'USER'@'localhost';

Resources