mysql
MySQL tips.
More information you can found here
Connect
mysql -u root --host myserver.com -p
CRUD
Create
Retrieve
Update
UPDATE glpi_users SET password=MD5('new_pass') WHERE name='admin';
Delete
- Delete row
DELETE FROM table_name [WHERE Clause]
- Drop table
DROP TABLE table_name ;
Permissions
- Show grants for current user
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
- Show all user privileges from information_schema
use information_schema;
select * from USER_PRIVILEGES;
- Grant privileges
use information_schema;
GRANT ALL PRIVILEGES ON `costs`.* TO 'aws_costs'@'1.1.1.1';
- Revoke privileges
# TODO
Admin
- Show table sizes
Metrics
- Show main metrics
select name,status,count,avg_count,max_count,subsystem from INNODB_METRICS;
- show System metrics
select name,status,count,avg_count,max_count,subsystem from INNODB_METRICS where subsystem="os" or subsystem='file_system';
- Open files
select * from global_status where VARIABLE_NAME='OPENED_FILES'
- Table sizes
SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;
- proccess running (queries)
show processlist;
show full processlist;
- w/o sleeping queries
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';
- Kill queries running:
kill <ID>;
- kill all queries
TODO
GUI
phpMySQLADmin
- Docker: https://hub.docker.com/r/phpmyadmin/phpmyadmin/
References:
- https://dev.mysql.com/doc/mysql-getting-started/en/