Code for Reference

MySQL commands: Important for db admin, but usually forgotten.

As a developer, we hardly use DB admin commands and mostly we forget command after use. I am trying to summarize some of the commands. If you find there are few more commands which fall under this category then please put them in the comments section. Check MySQL debugging post if you are interested in debugging your SQL query.

Mysql Installation

This might be the easy-one, but sometimes we end-up googling this commands.

/> sudo apt-get install mysql-server

This command also installs mysql-client so you don’t have to issue another command to install MySQL client.

Password resetting

Once in a while we encounter a situation where we need to change the user password and we wonder, How it was changed last time. Well there are two different ways one can change the password.

  1. mysqladmin tool can be used to changed the password and this is quite easy one. 
    /> mysqladmin -uroot -pold_password password myNewpassword
  2. One can also change password by login to mysql and issuing the below commands. 
    mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') 
 -> WHERE User='userName';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

    Note:
    It is necessary to flush the privileges, otherwise cache will have the older password and you may face some temporary login issue.

User creation

Once in a while one needs to create username and password for application or for providing database access to other.

#In this case user can access DB from localhost machine.
#This user will not have permission to provide access grant to other

mysql> GRANT ALL ON db_name.* TO 'db_user'@'localhost' IDENTIFIED BY 'db_password';

#Using this command, user can access from any machine and
#any table, any database. This user can also provide access grant 
#to others.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'%' WITH GRANT OPTION;

If this db_name.* is used, that means the user can access all the tables present in db_name whereas *.* means the user can access any database and any table. If "WITH GRANT OPTION" is present in the command then the new user can create other user. This 'db_user'@'hostname' restricts the database access from the specific host, whereas 'db_user'@'%' allows access from any machine. These options can be combined to get the specific access privilege.

Table metadata

The following command displays the meta information about the table. This command distinctly shows create command which was used to create this table. This command also shows the index present on this table.

msql> SHOW CREATE TABLE tableName

#Example of this command and its output
mysql> SHOW CREATE TABLE github_user \G
*************************** 1. row ***************************
       Table: github_user
Create Table: CREATE TABLE `github_user` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `login` varchar(200) NOT NULL,
  `email` varchar(200) DEFAULT NULL,
  `location` varchar(200) DEFAULT NULL,
  `company` varchar(200) DEFAULT NULL,
  `repos` int(11) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_login` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Table dropping or deleting entire records

The following command was use to drop the entire table. This not only deletes the records but also remove the tables. This command should be used when you don’t need the table.

mysql> DROP TABLE tablename;

The TRUNCATE command deletes all records from table and reset the auto-incrementing keys to 1. This is as good as creating a brand new table. This should be used when you want to get rid of records but want to keep the table. This command is faster than delete because in delete command scans the entire table to find out the total number of affected records. This command is equivalent to "DROP TABLE tableName; CREATE TABLE tableName(....);"

TRUNCATE TABLE tablename;

Performance monitoring

mytop, it is a command line utility similar to htop, top, etc. It helps in analyzing mysql performance and monitor slow SQL query. Use the following command to install mytop.

sudo apt-get install mytop

Once install you can run this application by executing the following command

mytop -uUsername -pPassword

mytop-sample-view-e1372402942327-7155659

mytop sample view

mytop can also be configured so that you don’t have to provide user-name and password every-time. There are other parameters which can be configured too. For this you need to create configuration file .mytop in home directory and copy the lines shown below.

vi ~/.mytop
##Put the following line in .mytop configuration file.
user=username
pass=userpassword
host=localhost
delay=2   #Screen refresh rate in seconds.
header=1
color=1
idle=0  #If you want to see the idle process then set it to '1'

MySQL service commands

If you want to start, stop or restart the mysql service the one of the following command can be used.

sudo /etc/init.d/mysql [status|start|stop|restart|reload|force-reload]
sudo service mysql [status|start|stop|restart|reload|force-reload]

Uninstalling/Removing MySQL

This occasion comes rarely, but often confuse us about the various mysql package and which one should be removed. The following commands pretty much remove MySQL from the system.

sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean

Hope this blog helped you in some way. If you like this blog then please share it. You can also leave your comment below. You can find Facebook page here.

Related topics

admin