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.
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.
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.
mysqladmintool can be used to changed the password and this is quite easy one.
/> mysqladmin -uroot -pold_password password myNewpassword
- 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;
It is necessary to flush the privileges, otherwise cache will have the older password and you may face some temporary login issue.
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;
db_name.* is used, that means the user can access all the tables present in
*.* 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.
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;
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;
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
sudo apt-get install mytop
Once install you can run this application by executing the following command
mytop -uUsername -pPassword
mytopcan 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
.mytopin 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]
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.