As a DevOps person you get to deal with lot of different databases flavours, MySQL is one of the most commonly used database. One of pain point is since DevOps are not DBA’s so if we don’t get to deal with SQL servers we will forget commands. Mostly we keep a list of commands in our notepad/notes/local editor etc to fetch it quickly whenever we need, So here I am going to share commonly used MySQL commands from my experience. This post is meant for only commonly used MySQL commands by Ops or System Admin.

Personally I use mycli  in order to connect to mysql server, mycli is a command line interface for MySQL and MariaDB with auto-completion and syntax highlighting. Okay! lets gets thing keep rolling.

NOTE: All the below commands are for Unix based system (Mac, Linux) and works if you install mycli.

Login to mysql server

mycli -h <hostname> -u <username> -p<password> <database name>

If you are not using mycli then the native command will be:

mysql -h <hostname> -u <username> -p<password> <database name>

Update root User password

Login to mysql server as root using using above command format.
use mysql;
update user set password=PASSWORD('your_new_password') where User='<username>';
flush priviledges;
quit;

Create user and GRANT privileges:
CREATE USER '<username>' IDENTIFIED BY '<password_here>';

GRANT ALL PRIVILEGES ON `<database>`.* TO '<username>';

In order to give privileges to specific database name that starts with prefix use command like:
GRANT ALL PRIVILEGES ON `prefix\_%`.* TO '<username>';

e.g: If I have user “newuser” and  databases like manan_website, manan_blog, singh_blog on localhost and I want to give privileges to only those databases that starts with “manan_” then command will be:

GRANT ALL PRIVILEGES ON `manan\_%`.* TO 'newuser'@'localhost';

Display only one result line by line rather than column:

“LIMIT 1” will only display one entry and Use “/G” at the end of SQL query rather than “;” to display result line by line eg:

SELECT * FROM <table name> LIMIT 1 \G

How to find the size of all databases on the server:

SELECT table_schema,
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

How to find the size of specific databases on the server:

SELECT table_schema,
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
WHERE table_schema = "<your_database_name>"
GROUP BY table_schema ;

How to find the size of all the tables  in a database:

SELECT table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Size in GB`
FROM information_schema.TABLES

WHERE table_schema = "<your_database_name>"
ORDER BY (data_length + index_length) DESC;

How to find the version of mysql:
SHOW VARIABLES LIKE "%version%";

Show all the Users and Hostname and Password (encrypted):
SELECT User, Host, Password FROM mysql.user;

Show only unique Users:
SELECT DISTINCT User FROM mysql.user;

Show max number of connections allowed:
SELECT @@max_connections;

Show the number of active connections:
SHOW STATUS WHERE `variable_name` = 'Threads_connected';

How to make database backup:

mysqldump -h <hostname> -u <username> -p<password> -v --routines  <database_name> > file_name.sql

How to restore database:

mysql -h <hostname> -u <username> -p<password> <database_name> < restore_location/filename.sql

Please feel free to leave a comment if you would like to add any other command on the list or if you find it useful.

 

Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *