In this quick tip tutorial, we’ll learn about the following:
- How to create a MySQL database
- How to create a user that connects to this database
- How to do all of the above from the terminal without PHPMyAdmin
Now if you were installing WordPress or a PHP application whatsoever for your site, you’ll normally be opted to provide credentials for a database and MySQL user for these apps to make database connections. FYI, I am running a light stack of LAMP on Ubuntu 16.04 which is my preferred local development environment.
Before you get this started, open in your terminal and init MySQL terminal typing the following:
mysql -u root -p
The above code will prompt you to enter your mysql password, which you have provided while configuring your mysql server. There is another way to do this without being prompted to enter the password, while you’re on root, using the -e flag followed by the MySQL query, something like:
mysql -u root -e "SHOW DATABASES;"
and hopefully it wouldn’t request for a password.
Step 1 – Create database
You just want to type in the name of your database to the following command:
CREATE DATABASE my_database_name;
And pressing enter will create your new database named my_database_name. Let’s make sure it is there:
You’ll see a tree of databases which you have in your install, among them the one we just created.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bbpm | | mu2 | | my_database_name | | mysql | | performance_schema | | sys | | wp | +--------------------+ 8 rows in set (0.06 sec) mysql>
Great for step 1, the database is there.
Step 2 – Create a MySQL user and grant permissions
Good, so to usually connect to this database we have to create a mysql user and grant them permissions. Let’s begin by creating the user:
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
- username: The MySQL user username (myuser in this example).
- host: To enable this user for remote connections, user ‘%’ instead of ‘localhost’ in the host field
- password: The password which the user will need to enter in order to be granted a connection. Leave as empty ” if you are on a local development server. (the password is set to ‘mypassword’ in this example)
Replace the text in orange with the user name of your choice, and the password as well. Remember, the password will be used to connect to this database using this user.
That will create the user, but yet we want to grant them privileges to perform SQL queries:
GRANT ALL PRIVILEGES ON * . * TO 'myuser'@'localhost';
This should give the user full privileges across the MySQL server (i.e all databases). To give permissions in a single database or two, use:
GRANT ALL PRIVILEGES ON databasename.* TO 'myuser'@'localhost';
The 2nd wildcard (star) may be used as well to reflect a data table, so in case the privileges should be restricted to a database table you can use it.
Lastly, we want to flush the privileges:
Great, so all done for step 2, but before that, let’s test this new user and database out to find out!
Step 3 – Just a test
So as we created the new user, we want to see whether everything’s functional. Remember the username and password you stated above, let’s use them to connect:
First, ctrl+d or type in ‘bye’ to disconnect from the mysql session we used with root, in the beginning of this tutorial. Now, let’s connect with out new user:
mysql -u myuser -p
You’ll be prompted to enter a password, the one you provided in step 2. Connected? Cool! then everything’s normal.
mysql> ^DBye [email protected]:~/htdocs$ mysql -u myuser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2016, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bbpm | | mu2 | | my_database_name | | mysql | | performance_schema | | sys | | wp | +--------------------+ 8 rows in set (0.00 sec) mysql> use my_database_name; Database changed mysql> show tables; Empty set (0.00 sec) mysql>
We now have the database empty with no tables and that is our main point, the apps will opt to ask for a clean database and they append their database tables upon a fresh install, like WordPress, for instance.
You could manually create database tables as well, first just make sure you switched to your target database:
It is always good to follow the manual, here are few resources:
- How to create tables.
- DROP USER Syntax if you wanted to delete the custom user we created
- DROP DATABASE Syntax to delete databases
Cheap Cloud SSD Hosting
Get a VPS now starting at $5/m, fast and perfect for WordPress and PHP applications