{"id":30100,"date":"2021-01-18T03:14:19","date_gmt":"2021-01-17T19:14:19","guid":{"rendered":"https:\/\/web.mwwsb.com.my\/pjci\/?post_type=kb&p=30100"},"modified":"2022-09-08T19:13:39","modified_gmt":"2022-09-08T11:13:39","slug":"how-to-manage-mysql","status":"publish","type":"kb","link":"https:\/\/www.casbay.com\/guide\/kb\/how-to-manage-mysql","title":{"rendered":"HOW TO: Manage MySQL"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Manage MySQL Database<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

This guide describes how to do common MySQL database administration tasks using the command line with the mysql<\/em> program. These tasks include how to create and delete databases, users, and tables.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t

Add users and databases<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
  1. Firstly, log in to MySQL as the root user at the command line:
    mysql -u root -p<\/pre><\/li>
  2. Then, type the MySQL root password, and press Enter<\/strong>.

    <\/li>
  3. To create a database user<\/strong>, type the following command. 
    Replace\u00a0username <\/em>and password<\/em> with the user you want to create and\u00a0the user’s password:<\/p>
    GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';<\/pre>
    This command grants the user all permissions. However, you can specify permissions to maintain precise control over database access. For instance, to explicitly grant the SELECT<\/strong> permission, you can use the following command:<\/p>
    GRANT SELECT ON *.* TO 'username'@'localhost';<\/pre>
    For more information about setting MySQL database permissions, kindly visit https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/grant.html<\/a>.<\/p><\/div><\/li>
  4. After that, type \\q<\/span>\u00a0to exit the\u00a0mysql<\/em>\u00a0program.

    <\/li>
  5. To log in to MySQL as the user you just created, type the following command. Replace\u00a0username<\/em>\u00a0with the name of the user you created in step 3:<\/p>
    mysql -u username -p<\/pre><\/li>
  6. Next, type the user’s password, and then press Enter<\/strong>.

    <\/li>
  7. To create a database<\/strong>, type the following command.
    Replace\u00a0dbname<\/em>\u00a0with the name of the database that you want to create:<\/p>
    CREATE DATABASE dbname;<\/pre><\/li>
  8. To work with the new database<\/strong>, use the following command. 
    Replace dbname<\/em>\u00a0with the name of the database you created in step 7:<\/p>
    USE dbname;<\/pre><\/li>
  9. You can now work with the database. For example, the following commands show how to create a basic table named table<\/em>, and how to insert some data into it:<\/p>
    CREATE TABLE table( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_table primary key (id) );\nINSERT INTO table( id, name ) VALUES ( null, 'Sample data' );<\/pre><\/li><\/ol>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t
    Remove Tables and Databases<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t
    To delete a table, use the following command from the mysql><\/strong>\u00a0prompt. Replace\u00a0tablename<\/em>\u00a0with the name of the table that you want to delete:<\/p>
    DROP TABLE tablename;<\/pre>
    This command assumes that you have already selected a database by using the\u00a0USE<\/strong>\u00a0statement.

    <\/div>
    Similarly, to delete an entire database, use the following command from the mysql><\/strong>\u00a0prompt. Replace\u00a0dbname<\/em>\u00a0with the name of the database that you want to delete:<\/p>
    DROP DATABASE dbname;<\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t
    Removing Database Users<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t
    To view a list of all users, type the following command from the\u00a0mysql><\/strong>\u00a0prompt:<\/p>
    SELECT user FROM mysql.user GROUP BY user;<\/pre>
    To delete a specific user, type the following command from the\u00a0mysql><\/strong>\u00a0prompt. Replace\u00a0username<\/em>\u00a0with the name of the user that you want to delete:<\/p>
    DELETE FROM mysql.user WHERE user = 'username';<\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t
    We hope this article helped you to learn how to manage MySQL database. For more articles, please go to Knowledge Base<\/a>.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"
    Manage MySQL Database This guide describes how to do common MySQL database administration tasks using the command line with the mysql program. These tasks include how to create and delete databases, users, and tables. Add users and databases Firstly, log in to MySQL as the root user at the command line: mysql -u root -p […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"site-sidebar-layout":"no-sidebar","site-content-layout":"page-builder","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"disabled","ast-breadcrumbs-content":"","ast-featured-img":"disabled","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"default","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}}},"kbtopic":[60],"kbtag":[106],"mkb_version":[],"_links":{"self":[{"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kb\/30100"}],"collection":[{"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kb"}],"about":[{"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/types\/kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/comments?post=30100"}],"version-history":[{"count":1,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kb\/30100\/revisions"}],"predecessor-version":[{"id":31739,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kb\/30100\/revisions\/31739"}],"wp:attachment":[{"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/media?parent=30100"}],"wp:term":[{"taxonomy":"kbtopic","embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kbtopic?post=30100"},{"taxonomy":"kbtag","embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kbtag?post=30100"},{"taxonomy":"mkb_version","embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/mkb_version?post=30100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}