{"id":104,"date":"2013-07-03T02:26:14","date_gmt":"2013-07-03T02:26:14","guid":{"rendered":"https:\/\/code4reference.com\/?p=104"},"modified":"2023-10-01T13:33:30","modified_gmt":"2023-10-01T13:33:30","slug":"code4referencemysql-commands-important-for-db-admin-but-usually-forgotten-code4reference","status":"publish","type":"post","link":"https:\/\/code4reference.com\/?p=104","title":{"rendered":"MySQL commands: Important for db admin, but usually forgotten."},"content":{"rendered":"

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<\/a> if you are interested in debugging your SQL query.<\/p>\n

Mysql Installation<\/h3>\n

This might be the easy-one, but sometimes we end-up googling this commands.<\/p>\n

\n\/> sudo apt-get install mysql-server\n<\/pre>\n
This command also installs mysql-client so you don\u2019t have to issue another command to install MySQL client.<\/p>\n
Password resetting<\/h3>\n
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.<\/p>\n
    \n
  1. mysqladmin<\/code> tool can be used to changed the password and this is quite easy one.\n
    \n\/> mysqladmin -uroot -pold_password password myNewpassword\n<\/pre>\n<\/li>\n
  2. One can also change password by login to mysql and issuing the below commands.\n
    \nmysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') \n -> WHERE User='userName';\nmysql> FLUSH PRIVILEGES;\nmysql> EXIT;\n<\/pre>\n
    Note:<\/strong>
    \nIt is necessary to flush the privileges, otherwise cache will have the older password and you may face some temporary login issue.<\/p>\n<\/li>\n<\/ol>\n
    User creation<\/h3>\n
    Once in a while one needs to create username and password for application or for providing database access to other.<\/p>\n
    \n#In this case user can access DB from localhost machine.\n#This user will not have permission to provide access grant to other\n\nmysql> GRANT ALL ON db_name.* TO 'db_user'@'localhost' IDENTIFIED BY 'db_password';\n\n#Using this command, user can access from any machine and\n#any table, any database. This user can also provide access grant \n#to others.\n\nmysql> GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'%' WITH GRANT OPTION;\n<\/pre>\n
    If this db_name.*<\/code> is used, that means the user can access all the tables present in db_name<\/code> whereas *.*<\/code> means the user can access any database and any table. If \"WITH GRANT OPTION\"<\/code> is present in the command then the new user can create other user. This 'db_user'@'hostname'<\/code> restricts the database access from the specific host, whereas 'db_user'@'%'<\/code> allows access from any machine. These options can be combined to get the specific access privilege.<\/p>\n
    Table metadata<\/h3>\n
    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.<\/p>\n
    \nmsql> SHOW CREATE TABLE tableName\n\n#Example of this command and its output\nmysql> SHOW CREATE TABLE github_user \\G\n*************************** 1. row ***************************\n       Table: github_user\nCreate Table: CREATE TABLE `github_user` (\n  `id` int(11) NOT NULL,\n  `name` varchar(200) DEFAULT NULL,\n  `login` varchar(200) NOT NULL,\n  `email` varchar(200) DEFAULT NULL,\n  `location` varchar(200) DEFAULT NULL,\n  `company` varchar(200) DEFAULT NULL,\n  `repos` int(11) DEFAULT NULL,\n  `created` datetime DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `idx_login` (`login`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\n1 row in set (0.00 sec)\n\n<\/pre>\n
    Table dropping or deleting entire records<\/h3>\n
    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\u2019t need the table.<\/p>\n
    \nmysql> DROP TABLE tablename;\n<\/pre>\n
    The TRUNCATE<\/code> 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(....);\"<\/code><\/p>\n
    \nTRUNCATE TABLE tablename;\n<\/pre>\n
    Performance monitoring<\/h3>\n
    mytop<\/code>, 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<\/code>.<\/p>\n
    \nsudo apt-get install mytop\n<\/pre>\n
    Once install you can run this application by executing the following command<\/p>\n
    \nmytop -uUsername -pPassword\n<\/pre>\n
    mytop-sample-view-e1372402942327-7155659<\/a><\/p>\n
    mytop sample view<\/p>\n
    \nmytop<\/code> can also be configured so that you don\u2019t 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<\/code> in home directory and copy the lines shown below.<\/p>\n
    \nvi ~\/.mytop\n##Put the following line in .mytop configuration file.\nuser=username\npass=userpassword\nhost=localhost\ndelay=2   #Screen refresh rate in seconds.\nheader=1\ncolor=1\nidle=0  #If you want to see the idle process then set it to '1'\n<\/pre>\n
    MySQL service commands<\/h3>\n
    If you want to start, stop or restart the mysql service the one of the following command can be used.<\/p>\n
    \nsudo \/etc\/init.d\/mysql [status|start|stop|restart|reload|force-reload]\nsudo service mysql [status|start|stop|restart|reload|force-reload]\n<\/pre>\n
    Uninstalling\/Removing MySQL<\/h3>\n
    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<\/code> from the system.<\/p>\n
    \nsudo apt-get remove --purge mysql-server mysql-client mysql-common\nsudo apt-get autoremove\nsudo apt-get autoclean\n<\/pre>\n
    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<\/a>.<\/p>\n
    Related topics<\/h4>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-104","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/posts\/104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/code4reference.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=104"}],"version-history":[{"count":1,"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/posts\/104\/revisions"}],"predecessor-version":[{"id":344,"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/posts\/104\/revisions\/344"}],"wp:attachment":[{"href":"https:\/\/code4reference.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code4reference.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code4reference.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}