Creating and configuring a new MySQL database via the command line

When developing almost any application, you will need to have database of some type to store the data created and used by the application and its users. The most popular open-source database is MySQL. Of course, there are other well-known open-source database platforms, such as PostgreSQL and SQLite, as well as so-called NoSQL databases like MongoDB, since MySQL is ubiquitous, we’ll work with it.

Just as there are many different database platforms, there are many ways to interact with MySQL. There are web-based tools, like phpMyAdmin and Adminer, and regular client applications, such as DBeaver and SQuirrel SQL, all of which are free and open-source. I use and can recommend all of the tools mentioned. However, since you may not always have access to these tools, it’s always good to know how to use the default command-line/shell tools for MySQL.

To log into MySQL at the command line, enter this command:

mysql -uusername -ppassword

Note that there is no space between the -u and -p and the username and the password, respectively. (Technically, you can put a space between -u and username, but I find it difficult to remember which one you can and which one you can’t, so I just do them both the same way!) Assuming that you enter a valid username and password, you will be logged in and at the MySQL command prompt: mysql>.

All MySQL commands end with ; (semi-colon). To try things out, let’s look at the databases that you already have:

mysql> SHOW DATABASES;

Running this command should return something like this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.40 sec)

Every MySQL database server will have an information_schema and a mysql database. These databases hold metadata about the configuration of the MySQL instance itself. While you won’t often use these databases, you might want to know a little about them.

Let’s create a new database and grant privileges (permissions) to that database to a specific (new) user. I prefer to create a user with the same name as the database itself, but you can use any username that you like. For this example, we’ll the username will be different from the database name to avoid confusion. Of course, to maintain security, you should usually not use the MySQL root user for access to application databases.

mysql> CREATE DATABASE `laravel` CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> CREATE USER 'laravel_user'@'%' IDENTIFIED BY 's3cr3t';
mysql> GRANT ALL ON `laravel.*` TO 'laravel_user'@'%';

After each command above, you should get a response message of Query OK followed by the number of rows affected (usually 0 or 1 for DDL commands like these). Also, if you get the message ERROR 1046 (3D000): No database selected when running the GRANT command, this means that MySQL doesn’t know what database you are referring to. You can execute USE `laravel`; to select the laravel database as the default.

Let’s look at the details of each of these commands in turn. The CREATE DATABASE command does just that: it creates a new database with the given name. Note that we included the name (laravel) in a pair of back ticks (`). While this isn’t absolutely necessary in most cases, it’s good practice since it allows you to use non-ASCII characters in the name. Likewise, we specified the optional CHARACTER SET and COLLATE parameters. By using UTF8 character set, this ensures that our database can store non-ASCII characters properly. Essentially, we are making our database more flexible.

Next, we turn to the CREATE USER command. Obviously, this creates a new user in our database platform. However, initially, this user (laravel_user) can’t actually do anything (other than log into MySQL). The username should be contained in single quotes (') [not back ticks!). Also, you are probably wondering about the @'%' following the username. This defines the scope of accessiblity for this user and % essentially means that this user can connect to the database from any host (machine). If you want to restrict connections to only the host where MySQL is running, such as in a development environment, you can specify @'localhost' (or @'127.0.0.1') instead of @'%'. Finally, the IDENTIFIED BY clause specifies the password (called authentication_string in MySQL terminology) for the user. In this case, we simply specify the actual password (s3cr3t) enclosed in single quotes. MySQL hashes passwords so that they are not stored in plain text in the database. You can see the hashed value for this user (laravel) by running:

mysql> select authentication_string from mysql.user where user = 'laravel_user';

You should get a result similar to this:

+-------------------------------------------+
| authentication_string                     |
+-------------------------------------------+
| *58C036CDA51D8E8BBBBF2F9EA5ABF111ADA444F0 |
+-------------------------------------------+
1 row in set (0.08 sec)

Obviously, the actual value will (or should!) be different on your system.

Finally, we come to the GRANT command, which explicitly gives priveliges (permissions) to a user for certain databases or objects in a database. In this case the ALL specification means that all privileges (permissions) available are being given (“granted”) to the specified user. If you want to grant only certain permissions, such as to allow “read-only” access, you can specify any list of the other privileges, such as SELECT, CREATE, UPDATE, and DELETE. (See MySQL documentation on GRANT for full list.) The specification after the ON indicates the scope of the privileges that are granted. In this case, by specifying `laravel.*`, we are granting these permissions to all objects (not only tables) in the laravel database. If you want to limit permissions to only a specific table, you could specify that, such as `laravel.user`. And, last but not least, the TO clause indicates which user (or users) these permissions should be given to. Again, the format of the user follows that of the format for CREATE USER. You can specify multiple usernames by separating each with a comma (,).

To log out of the MySQL command prompt, run:

mysql> exit;

Now, we have our database created and ready to use. In Laravel, you need to update the .env file in the root directory of your project with the appropriate parameter values to match your new database. Using the database above (and assuming that MySQL server is running on the same machine as our Laravel project), our settings would be:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=laravel_user
DB_PASSWORD=s3cr3t

Hope you enjoyed this article. Please provide feedback/questions in the comments.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s