Quick MySQL Commands

To Login into the MySQL

#mysql -u username -ppaswword

and if its RDS :

#mysql -h rds.indexpoint/dnsname/ip -u username -ppassword

To List Databases

mysql>show databases;

To View tables

mysql>use databasename;
mysql>show tables;

To View Contents Inside the Table

mysql>select * from tablename;

To Delete Database

mysql>drop database dbname;

To create Database user (we also need to grant permission to a db to gain access for the new user.)

mysql>create user 'UNNI'@'ipaddress/%/localhost' identified by 'passwd3@1';

To Grant Permission to a Database for a User and Create the User at the same Time

mysql>grant all on databasename.* to 'UNNI'@'ipaddress/%/localhost' identified by 'password';

[NOTE– To provide access from all IP Address use ‘%’ instead of ipaddress. By default ‘localhost’ is the value that accepts on a normal mysql installation on a standalone EC2 machine]

To List out all Database Users

mysql>SELECT user,host FROM mysql.user;

To Create a Database

mysql>create database UNNI;

To restore a specific Database in Mysql (database UNNI has to be created already)

#mysql -u username -ppassword UNNI < UNNI.sql

[NOTE: Database UNNI had to be created before restore]

To dump/backup a specific Database in Mysql

#mysqldump -u username -ppassword UNNI > UNNI.sql

[NOTE: Database UNNI is backed up into UNNI.sql]

To know the permission of Mysql User

#show grants for 'unni'@'ipaddress/%/localhost';

Get MySQL Server Info

mysql> \s

To list all mysql admin tables

mysql> show tables from mysql;

To list mysql variables related to Errors

mysql> use dbname;
mysql> show variables like '%err%';

Deleting Users

To delete users from the MySQL database use the DROP command.

 mysql>DROP USER user@host;

The command in turn removes the user record from the mysql.user table.

As the CREATE USER command, even the DROP USER command has been added since MySQL 5.0.2. In previous versions of MySQL you must revoke the user’s privileges first, delete the records from user manually and then issue the FLUSH PRIVILEGES command.

mysql>DELETE FROM user WHERE User= 'technofriends' AND Host= 'localhost';
FLUSH PRIVILEGES;

General Notes

  • There is no concept in MySQL of “Owner” of database or its objects, as there is in MS Access and MS SQL Server. I surmise this from the lack of “owner” field anywhere in mysql system tables.

Create a Read-Only User in MySQL

create user 'new_user'@'%' identified by 'my_tough_passwd';
GRANT SELECT, SHOW VIEW ON database_name.* TO new_user@'%' IDENTIFIED BY 'my_tough_passwd';
FLUSH PRIVILEGES;

To show current user

mysql>select current_user();

Find if a query is doing a full table scan

mysql> show full processlist;
| 7 | root | ip-10-142-159-56:50960 | mydb | Sleep | 0 | | NULL |
| 8 | root | ip-10-142-191-57:60270 | mydb | Query | 0 | Sending data | SELECT `mycustomtable`.* FROM `mycustomtable` WHERE `mycustomtable`.`column_id` = 8373 LIMIT 1 |
| 9 | root | ip-10-138-58-103:35042 | mydb | Sleep | 0 | | NULL
mysql>  explain SELECT  `mycustomtable`.* FROM `mycustomtable`  WHERE `mycustomtable`.`some_columnvalue` = 8373 LIMIT 1
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | mycustomtable | ALL | NULL | NULL | NULL | NULL | 11906 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

The ALL means – This query is doing a full table scan.

 

Restore Dump and You get error : max_allowed_size error (got a packet bigger than ‘max_allowed_packet’ bytes)

 

mysql>show global variables where variable_name='max_allowed_packet';
mysql>SET GLOBAL max_allowed_packet=1073741824;

Quick Fixes

 

1.Kill MySQL sessions which are in sleep state

a. Login to the DB server

b. Execute the below command

echo "select ID FROM INFORMATION_SCHEMA.PROCESSLIST where Command='Sleep' ;" | mysql -u username -p'password' | awk '{ print "kill "$1";"}' > kill.sql

c. Execute the kill.sql in the DB

#mysql -u username -p'passowrd'
mysql> source kill.sql

2.Error While Taking dump from mysql 5.6

mysqldump: Couldn’t execute ‘SET OPTION SQL_QUOTE_SHOW_CREATE=1’: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘OPTION SQL_QUOTE_SHOW_CREATE=1’ at line 1 (1064)

The reason for this is that MySQL 5.6 has removed support for “SET OPTION” and your mysql client tools are probably on older version. Most likely 5.5 or 5.1. There is more info about this issue on MySQL bugs website. The quickest solution is to update your mysql client tools to 5.6 and your problem will be solved. Unfortunately, there is now official binary of MySQL 5.6 tools for Ubuntu at the moment. However, I did find a solution on the good old GitHub where you can add this custom MySQL 5.6 client tools to your ubuntu repository. It works like a charm. To install mysql client tools 5.6 on ubuntu run the following commands:

sudo add-apt-repository ppa:ondrej/mysql-experimental
sudo apt-get update
sudo apt-get remove mysql-client-5.5
sudo apt-get install mysql-client-5.6

Now you should be able to run mysqldump backups with MySQL 5.6.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s