DB/Mysql
< DB
Jump to navigation
Jump to search
This is reference regarding mysql databases. It's worth to mention that MySQL open source project now is called MariaDB. These commands below are mainly tested with Amazon RDS instances.
Connect
mysql -h database-eu.example.com -u admin@database-eu -p mysql -h database-eu.example.com -u admin@database-eu -pPassw0rd123
- References
Check MySQL current connections
watch -n1 "mysql -u root -pPASSWORD -te \"select id, command, host, time, left(info,60) from information_schema.processlist where info is not null or time > 300 order by time asc;\" 2>/dev/null"
Mysql client - prompt
How to show the name of the current database in the MySQL prompt If you need the name of the currently selected database in your MySQL prompt, use the following lines in your MySQL my.cnf configuration file entry like this [mysql] prompt='mysql(\d)> ' Now, when you connect, the MySQL prompt will look like what’s shown below:
mysql((none))> use test; #Database changed mysql(test)> mysql(test)> use mysql; #Database changed mysql(mysql)>
Create a user
Show users
# Show users and their main set of privileges SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv
FROM mysql.user
Create or delete a user
Mysql 5.7
CREATE USER 'new.user'@'localhost' IDENTIFIED BY 'password'; GRANT USAGE ON *.* TO 'new.user'@'%' IDENTIFIED BY 'password'; DROP USER 'new.user'@'localhost';
Mysql 5.6. Notice usage lines, it creates a user without permissions if the user does not exist
GRANT USAGE ON *.* TO 'new.user'@'localhost'; GRANT USAGE ON *.* TO 'new.user'@'%'; DROP USER 'new.user'@'localhost'; DROP USER 'new.user'@'%'; CREATE USER 'new.user'@'%' IDENTIFIED BY 'password';
Grant a user privileges per tables
<syntaxhighlightjs lang="sql"> SHOW GRANTS username;
<syntaxhighlightjs lang="sql">
- Limit to a specific table only
GRANT select ON mysql.general_log TO 'newuser'@'%'; GRANT type_of_permission ON db_name.table_name TO 'newuser'@'localhost';
- Revoke if needed
REVOKE type_of_permission ON db_name.table_name FROM 'newuser'@'localhost';
- Once finalised the permissions for your new users, always reload all the privileges
FLUSH PRIVILEGES; </syntaxhighlightjs>
Show tables grants
mysql> select * from mysql.tables_priv; +-----------+-------+------------+-------------+-------------------+---------------------+------------+-------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+-------+------------+-------------+-------------------+---------------------+------------+-------------+ | % | mysql | bob | general_log | root@10.10.11.10 | 0000-00-00 00:00:00 | Select | | | % | mysql | bob | proc | root@10.10.11.10 | 0000-00-00 00:00:00 | Select | | | % | mysql | bob | slow_log | root@10.10.11.10 | 0000-00-00 00:00:00 | Select | | | % | mysql | newuser1 | general_log | root@10.10.11.10 | 0000-00-00 00:00:00 | Select | | | localhost | mysql | newuser1 | general_log | root@10.10.11.10 | 0000-00-00 00:00:00 | Select | | +-----------+-------+------------+-------------+-------------------+---------------------+------------+-------------+
Queries
Show process list
Note in the info column there will be the current SQL query running.
mysql> show full processlist; +----+----------+--------------------+-------------+---------+------+----------------------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+--------------------+-------------+---------+------+----------------------+-----------------------+ | 34 | rdsadmin | localhost | NULL | Sleep | 0 | delayed send ok done | NULL | | 35 | rdsadmin | localhost | NULL | Sleep | 5 | cleaned up | NULL | | 36 | rdsadmin | localhost | NULL | Sleep | 151 | delayed send ok done | NULL | | 38 | root | 10.10.11.10:35357 | core_db | Sleep | 260 | cleaned up | NULL | | 39 | root | 10.10.11.10:35439 | core_db | Sleep | 1 | cleaned up | NULL | | 40 | root | 10.10.11.10:35675 | NULL | Query | 0 | init | show full processlist | +----+----------+--------------------+-------------+---------+------+----------------------+-----------------------+
Check size of all tables
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;
Check version
mysql> SELECT VERSION(); +------------+ | VERSION() | +------------+ | 5.6.10-log | +------------+ mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | aurora_version | 1.17.7 | | innodb_version | 1.2.10 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.10-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+
AWS - Aurora logs
# Parameters in parameter group general_log 0 #disabled log slow_log 1 #enabled log log_output TABLE #redirect log to tables # Read logs SELECT * FROM mysql.slow_log; SELECT * FROM mysql.general_log;
Align SQL inserts by a comma
cat inserts.sql | sed 's/`,/`@,/g' | sed "s/',/'@,/g" | sed "s/,'/@,'/g" | column -t -s "@" > inserts-aligned.sql # accounts for `, # accounts for ', # accounts for ,'