Welcome to OMG boards community!
Community of friends where we talk about daily life, offtopic stuff, and what keeps us busy.

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Some basic MySQL commands

Discussion in 'Manage your Site' started by Floris, Mar 14, 2018.

  1. Floris

    Floris I'm just me :) Hi. Staff Member

    Threads:
    10,283
    Messages:
    36,071
    Likes Received:
    466
    Trophy Points:
    855
    Gender:
    Male
    This thread covers how to connect to your MySQL server, how to add a new database, and how to add a user to your newly created database.

    After the first post, we can go a bit more in-depth with specifics and exceptions to the rules, please contribute if you want :)

    Code:
    userx@linux:~/meow$ mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 30480
    Server version: 5.5.59-0ubuntu0.14.04.1 (Ubuntu)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> CREATE DATABASE dbname;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'dbpass';
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> exit
    Bye
    userx@linux:~/meow$
    How to connect to the MySQL server from command line?
    Code:
    mysql -u root -p
    For security reasons, don't include the password in the command line, it will prompt you for it.
    Once connected, you can type exit to get back to your shell prompt.

    How to create a new database in MySQL?
    Once connected to your mysql server with the mysql root user, you can type the following command:
    Code:
    CREATE DATABASE dbname;
    Replace the "dbname" with the name of the database you wish to use. Keep it short and simple.
    Example: CREATE DATABASE omgforums;
    When successfully added, it will tell you like this: Query OK, 1 row affected (0.00 sec)

    How to create a new user in MySQL?
    Once connected and after creating the mysql database, you can type the following command to add a user to the mysql database, so we can use that user for that database next:
    Code:
    CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'dbpass';
    Replace the "dbuser" part with the desired username you want to add, for example on omgboards.com we have a database and we could call it omgbuser. Keep it short and simple.
    Replace the "dbpass" part with the desire password for that user. For example, on omgboards.com we could use a strong, long and unique password like "409id-%eroij_feh3$%" (obviously this is just an example and not actually our db details)
    For example: CREATE USER 'omguser'@'localhost' IDENTIFIED BY '409id-%eroij_feh3$%';

    How to give a user access to a database?
    By default, even after adding a user with a password, you cannot login with it. You have to grant privileges to either everything *.* or on a specific database db.*, this is how we do this:

    Tip: The localhost means that user on that system can do it, no remote connections.

    Code:
    GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost';
    Replace the "dbname" with the database you've just created.
    And replace the "dbuser" with the user you've just created.
    For example: GRANT ALL PRIVILEGES ON omgforums.* TO 'omguser'@'localhost';

    Okay, we're basically done. You've connected to the database server, created a new database, and added a user and gave it permission to use that database. What's next is to let it use our changes.

    Code:
    FLUSH PRIVILEGES;
    You can type: exit
    And you can start creating tables, rows, insert data, etc. Or just import an existing .sql dump and point your webfiles' configuration to use this newly created database.
     
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice