{"id":1109,"date":"2021-03-17T14:34:35","date_gmt":"2021-03-17T12:34:35","guid":{"rendered":"https:\/\/www.scalahosting.com\/kb\/?p=1109"},"modified":"2025-11-27T11:49:53","modified_gmt":"2025-11-27T09:49:53","slug":"how-to-manage-mysql-users-databases-and-tables-from-the-command-line","status":"publish","type":"post","link":"https:\/\/www.scalahosting.com\/kb\/how-to-manage-mysql-users-databases-and-tables-from-the-command-line\/","title":{"rendered":"Manage MySQL via Command Line Guide"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">MySQL is probably <\/span><b>the most popular relational database management system<\/b><span style=\"font-weight: 400;\"> (RDBMS). A relational database management system organizes data<\/span><span style=\"font-weight: 400;\"> as a relationship between columns and rows in a bunch of tables.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The columns represent the different attributes of the data, while each row records its values.<\/span><span style=\"font-weight: 400;\"> This article can guide you on <\/span><b><i>how to manage MySQL users and databases remotely<\/i><\/b><span style=\"font-weight: 400;\"> using the command line.\u00a0<\/span><\/p>\n<p><b>It details how to:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Create a MySQL users<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Delete a MySQL user<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Create a MySQL database<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Delete a MySQL database<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Delete a MySQL table<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Export a MySQL Database<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Import an SQL file into a MySQL database<\/span><\/li>\n<\/ul>\n<p><b>Follow the steps below to set them up.<\/b><\/p>\n<h2><b>Creating a MySQL User\u00a0<\/b><\/h2>\n<p><b>Securely log in to your server<\/b><span style=\"font-weight: 400;\"> through SSH access to open the command line.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Type this command <\/span><b>to log in to MySQL as the root user:<\/b><\/p>\n<p><i><span style=\"font-weight: 400;\">mysql -u root -p\u00a0<\/span><\/i><\/p>\n<p><b>Enter the MySQL root password<\/b><span style=\"font-weight: 400;\"> and press <\/span><b>Enter<\/b><span style=\"font-weight: 400;\">. Now, this will enable you to run MySQL commands as the user.<\/span><\/p>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-1110 aligncenter\" src=\"https:\/\/www.scalahosting.com\/kb\/wp-content\/uploads\/2021\/03\/2021-03-17_14h32_07.png\" alt=\"Manage MySQL via Command Line Guide, Creating a MySQL User\u00a0\" width=\"440\" height=\"224\" srcset=\"https:\/\/www.scalahosting.com\/kb\/wp-content\/uploads\/2021\/03\/2021-03-17_14h32_07.png 440w, https:\/\/www.scalahosting.com\/kb\/wp-content\/uploads\/2021\/03\/2021-03-17_14h32_07-300x153.png 300w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Now, <\/span><b>to create a new user and assign privileges<\/b><span style=\"font-weight: 400;\">, then run this command from the<\/span><b> mysql<\/b><span style=\"font-weight: 400;\">&gt; prompt:<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">GRANT ALL PRIVILEGES ON *.* TO &#8216;username&#8217;@&#8217;localhost&#8217; IDENTIFIED BY &#8216;password&#8217;;<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Replace <\/span><i><span style=\"font-weight: 400;\">username<\/span><\/i><span style=\"font-weight: 400;\"> with the name of the user you are creating and <\/span><i><span style=\"font-weight: 400;\">password<\/span><\/i><span style=\"font-weight: 400;\"> with the user\u2019s password.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The above command <\/span><b>will assign all privileges to the user<\/b><span style=\"font-weight: 400;\">. But you can restrict the privileges with the <\/span><i><span style=\"font-weight: 400;\">grant<\/span><\/i><span style=\"font-weight: 400;\"> command. And here\u2019s how:<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">GRANT PRIVILEGE ON *.* TO &#8216;username&#8217;@&#8217;localhost&#8217;;<\/span><\/i><\/p>\n<p><b>Replace <\/b><b><i>PRIVILEGE<\/i><\/b><span style=\"font-weight: 400;\"> with the permission you want to grant the user. MySQL has over 50 privileges you can assign a user. You can view the <\/span><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/grant.html#grant-privileges\"><span style=\"font-weight: 400;\">full list here.<\/span><\/a><\/p>\n<p><span style=\"font-weight: 400;\">Now, to log in to MySQL as the new user, type \/q to exit the program, then run this command:<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">mysql -u username -p<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Of course, <\/span><b>replace <\/b><b><i>username<\/i><\/b><span style=\"font-weight: 400;\"> with the name of the user you just created. <\/span><b>Type the user\u2019s password<\/b><span style=\"font-weight: 400;\"> and press <\/span><b>Enter<\/b><span style=\"font-weight: 400;\"> to log in.<\/span><\/p>\n<h2><b>Deleting a MySQL User<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">You can delete a MySQL user <\/span><b>by running this command:<\/b><\/p>\n<p><i><span style=\"font-weight: 400;\">DELETE FROM mysql.user WHERE user = &#8216;username&#8217;;<\/span><\/i><\/p>\n<p><b>Replace <\/b><b><i>username<\/i><\/b> <span style=\"font-weight: 400;\">with the name of the user you intend to remove and press <\/span><b>Enter<\/b><span style=\"font-weight: 400;\">.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">You can view all the MySQL users with this command:<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">SELECT user FROM mysql.user GROUP BY user;<\/span><\/i><\/p>\n<h2><b>Creating MySQL Database<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">A user owns a MySQL database. So to create one, <\/span><b>you\u2019d need to log in first<\/b><span style=\"font-weight: 400;\"> with the user account that holds the database.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Now <\/span><b>run this command<\/b><span style=\"font-weight: 400;\"> to create the database:<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">CREATE DATABASE databname;<\/span><\/i><\/p>\n<p><b>Replace <\/b><b><i>databname<\/i><\/b> <span style=\"font-weight: 400;\">with the name of the database you want to create.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">You\u2019d need to run the <\/span><i><span style=\"font-weight: 400;\">USE <\/span><\/i><span style=\"font-weight: 400;\">command <\/span><b>to access or work with the database<\/b><span style=\"font-weight: 400;\"> you just created or any other databases. To do this, type the below command and press Enter.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">USE databname;<\/span><\/i><\/p>\n<p><b>Replace <\/b><b><i>databname<\/i><\/b> <span style=\"font-weight: 400;\">with the name of the database you want to open or work with.<\/span><\/p>\n<h2><b>Deleting a MySQL Database<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Deleting a MySQL database from the command line is irreversible. So, always ensure to delete only the correct ones.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">It\u2019s advisable to list all the databases on your server and then copy the name of the database you intend to delete. Run the <\/span><i><span style=\"font-weight: 400;\">SHOW DATABASES <\/span><\/i><span style=\"font-weight: 400;\">command to list all your databases.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">SHOW DATABASES;<\/span><\/i><\/p>\n<p><b>Use the <\/b><b><i>DROP DATABASES<\/i><\/b><b> command<\/b><span style=\"font-weight: 400;\"> to delete a MySQL database. Here\u2019s how it should look like after the mysql&gt; prompt:<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">DROP DATABASE databname;<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Replace <\/span><i><span style=\"font-weight: 400;\">databname<\/span><\/i><span style=\"font-weight: 400;\"> in the code with the name of the database you want to delete. <\/span><b>Use the <\/b><b><i>SHOW DATABASE<\/i><\/b><b> command<\/b><span style=\"font-weight: 400;\"> to confirm if the program has removed the database from the server.<\/span><\/p>\n<h2><b>Deleting MySQL Tables<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">You can delete a MySQL table with <\/span><b>the <\/b><b><i>DROP TABLE<\/i><\/b><b> command<\/b><span style=\"font-weight: 400;\">, but first, you\u2019d need to select the database where the table resides. Of course, you can do this with the <\/span><i><span style=\"font-weight: 400;\">USE<\/span><\/i><span style=\"font-weight: 400;\"> command.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">USE databname;<\/span><\/i><\/p>\n<p><b>Replace databname<\/b><span style=\"font-weight: 400;\"> with the database\u2019s name you intend to select. Now run this command to delete a table:<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">DROP TABLE tablename;<\/span><\/i><\/p>\n<p><b>Replace <\/b><b><i>tablename <\/i><\/b><b>in the code<\/b><span style=\"font-weight: 400;\"> with the name of the table you want to delete.<\/span><\/p>\n<h2><b>Exporting MySQL Database<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Exporting a MySQL database is essential <\/span><b>if you intend to <a href=\"https:\/\/www.scalahosting.com\/kb\/how-to-back-up-a-mysql-database\/\" target=\"_blank\" rel=\"noopener\">backup your database<\/a> or transfer it<\/b><span style=\"font-weight: 400;\"> to another server. You can perform this task using the <\/span><b><i>mysqldump<\/i><\/b><span style=\"font-weight: 400;\"> command.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">To export a MySQL database into an SQL file, <\/span><b>type this command<\/b><\/p>\n<p><i><span style=\"font-weight: 400;\">mysqldump -u username -p databname &gt; ExportDatabase.sql<\/span><\/i><\/p>\n<p><b>Replace <\/b><b><i>username<\/i><\/b><span style=\"font-weight: 400;\"> with the name of the database owner and <\/span><i><span style=\"font-weight: 400;\">databname<\/span><\/i><span style=\"font-weight: 400;\"> with the name of the database you intend to export.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Also, <\/span><b>replace <\/b><b><i>ExportDatabase.sql<\/i><\/b><span style=\"font-weight: 400;\"> with the name you\u2019d want to export the MySQL file to.<\/span><\/p>\n<p><b>Enter the user\u2019s password<\/b><span style=\"font-weight: 400;\">, and MySQL will create the SQL file of your database.<\/span><\/p>\n<h2><b>Importing MySQL Database<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">You can restore a MySQL database <\/span><b>by importing the backup file<\/b><span style=\"font-weight: 400;\">. To import an SQL file, for example, <\/span><i><span style=\"font-weight: 400;\">ExportDatabase.sql<\/span><\/i><span style=\"font-weight: 400;\"> to your database, <\/span><b>then run this command:<\/b><\/p>\n<p><i><span style=\"font-weight: 400;\">mysql -u username -p databname &lt; ExportDatabase.sql<\/span><\/i><\/p>\n<p><b>Replace <\/b><b><i>username<\/i><\/b><span style=\"font-weight: 400;\"> with the name of the database owner and <\/span><b><i>databname<\/i><\/b><span style=\"font-weight: 400;\"> with the name of the database you intend to import the file to.\u00a0<\/span><\/p>\n<p><b>Enter the user\u2019s password<\/b><span style=\"font-weight: 400;\"> to complete the import.<\/span><\/p>\n<h2><b>Wrapping It Up<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">You can manage your MySQL databases remotely with a handful of commands. This guide has described how to go about them. <\/span><b>Follow the steps to get started<\/b><span style=\"font-weight: 400;\">.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL is probably the most popular relational database management system (RDBMS). A relational database management system organizes data as a relationship between columns and rows in a bunch of tables.\u00a0 The columns represent the different attributes of the data, while each row records its values. This article can guide you on how to manage MySQL [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_seopress_titles_title":"Manage MySQL via Command Line Guide | ScalaHosting KB","_seopress_titles_desc":"","_seopress_robots_index":"","_seopress_robots_follow":"","_seopress_robots_imageindex":"","_seopress_robots_snippet":"","_seopress_robots_primary_cat":"none","_seopress_robots_breadcrumbs":"","_seopress_robots_freeze_modified_date":"","_seopress_robots_custom_modified_date":"","_seopress_robots_canonical":"","_seopress_social_fb_title":"","_seopress_social_fb_desc":"","_seopress_social_fb_img":"","_seopress_social_fb_img_attachment_id":0,"_seopress_social_fb_img_width":0,"_seopress_social_fb_img_height":0,"_seopress_social_twitter_title":"","_seopress_social_twitter_desc":"","_seopress_social_twitter_img":"","_seopress_social_twitter_img_attachment_id":0,"_seopress_social_twitter_img_width":0,"_seopress_social_twitter_img_height":0,"_seopress_redirections_value":"","_seopress_redirections_enabled":"","_seopress_redirections_enabled_regex":"","_seopress_redirections_logged_status":"both","_seopress_redirections_param":"","_seopress_redirections_type":301,"_seopress_analysis_target_kw":"","_seopress_news_disabled":"","_seopress_video_disabled":"","_seopress_video":[],"_seopress_pro_schemas_manual":[],"_seopress_pro_rich_snippets_disable_all":"","_seopress_pro_rich_snippets_disable":[],"_seopress_pro_schemas":[],"footnotes":""},"categories":[27],"tags":[],"class_list":["post-1109","post","type-post","status-publish","format-standard","hentry","category-vps-hosting"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/comments?post=1109"}],"version-history":[{"count":4,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1109\/revisions"}],"predecessor-version":[{"id":5849,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1109\/revisions\/5849"}],"wp:attachment":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/media?parent=1109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/categories?post=1109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/tags?post=1109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}