Some basic MySQL commands

Floris

I'm just me :) Hi.
Staff member
Joined
Jan 1, 2001
Messages
60,096
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.
 
Top