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

Error creating new Laravel project with ‘composer create-project’

After upgrading (or installing) version 1.0.0-beta1 of Composer, I found that I was unable to successfully create a new Laravel project using composer create-project command. It appeared that everything was successful, but the dependency resolution process fails with this error message (or similar) when running the command composer create-project laravel/laravel laravel "5.1.*" --prefer-dist:

 [RuntimeException]                                                                                                                                                                      
Error Output: PHP Warning:  require(/home/vagrant/laravel/bootstrap/../vendor/autoload.php): failed to open stream: No such file or directory in /home/vagrant/laravel/bootstrap/autoload.php on line 17                                                                                                                                                    
PHP Fatal error:  require(): Failed opening required '/home/vagrant/laravel/bootstrap/../vendor/autoload.php' (include_path='.:/usr/share/pear:/usr/share/php') in /home/vagrant/larave/bootstrap/autoload.php on line 17

Upon investigation, you’ll find that this line is trying to load autoload.php from the Laravel (Composer) vendor directory, but that the vendor directory does not yet exist. There is some discussion and debate on GitHub Composer project issue #5066 about whether this is a Composer problem or Laravel problem. However, it does not seem clear about whether or not there will be a fix for the issue.

In the meantime, you can work around the problem by reverting to version 1.0.0-alpha11 (or earlier) version of Composer to return the previous behavior. To do so, follow the usual instructions on the Composer site for command-line installation, except when running php composer-setup.php (or sudo php composer-setup.php on Linux), use the --version option to specify version 1.0.0-alpha11 (or earlier). For example:

sudo php composer-setup.php --install-dir=/usr/local/bin --filename=composer --version=1.0.0-alpha11

Subsequently, you should be able to run composer create-project to create your new project.

After you have created your project, you can upgrade to the later version of Composer, using composer self-update, but, of course, you’ll have to revert to version 1.0.0-alpha11 (or earlier) the next time you create another project. Nevertheless, for doing updates to existing projects, such as adding packages, the later version works fine.

Install latest (or specific) version of Node.js on Ubuntu Linux

The typical way to install Node.js in Ubuntu Linux is to use the apt-get package manager (i.e., sudo apt-get install nodejs). However, the Ubuntu repositories tend to lag a good bit behind the latest version of the platform. In this tutorial, we’ll install Node.js by downloading the source and compiling it ourselves. We will install it our local $HOME directory to avoid any conflicts with the standard package. This tutorial should work fine on your local Ubuntu machine, as well as a Ubuntu Vagrant box virtual machine (VM).

First, we need to update our system and install a few packages, if they aren’t already installed. Specifically, we need to install build-essential, which contains the GCC C++ compiler, and curl for downloading files from the Internet via the command line. At the command prompt, run these commands:

sudo apt-get -y update
sudo apt-get -y install build-essential curl

To install Node.js to our $HOME directory, we need to add $HOME/local/bin to the PATH environment variable and then export it to our environment by running:

echo 'export PATH=$HOME/local/bin:$PATH' >> $HOME/.bashrc
. $HOME/.bashrc

Now, create two new directories, local and node-latest-install, in the $HOME directory. These directories will hold the installed version of Node.js and the Node.js source code, used to compile the new version, respectively.

mkdir $HOME/{local,node-latest-install}

Next, we change to the node-latest-install directory, download the Node.js source code and extract the source code into that directory.

cd $HOME/node-latest-install
curl -LOk http://nodejs.org/dist/node-latest.tar.gz
tar xz node-latest-tar.gz --strip-components=1

This will get the very latest released version of Node.js. If you prefer a specific version, navigate to http://nodejs.org/dist/ in your web browser and find the appropriate directory and file name ending with tar.gz. For example, to download the “latest” version from the 4.x branch (which at the time of writing is 4.2.2), we would download http://nodejs.org/dist/latest-v4.x/node-v4.2.2.tar.gz.

Finally, we compile the Node.js application. Notice specifically that we specify to install to the $HOME/local directory.

./configure --prefix=$HOME/local
make
make install

Depending on the speed/power of your machine, compiling may take several minutes. The main thing to look for is whether or not any errors are returned. If you get errors while compiling, ensure that you have build-essential package installed.

To verify that Node.js installed successfully, run node -v and the command prompt. It should return something like v4.2.2. To confirm that you are running Node.js from the local installation, you can run which node at the command prompt, which should return something like /home/username/local/bin/node where username is your username (or vagrant, if you are installing on a Vagrant box).

The Node.js installation also includes the Node Package Manager (NPM). This is the official package manager for Node.js and allows you to install almost any package for Node.js that you could need. In particular, NPM is required to use Laravel Elixir, Laravel’s development task running, which acts as a wrapper for Gulp.

Fix ‘403 Forbidden’ error on Apache 2.4 HTTP server in Ubuntu

In the latest versions (specifically version 2.4.3 and above) of the Apache 2.4 HTTP server, on a new installation, you may receive the ‘403 Forbidden’ HTTP error when attempting display your site.  To increase security, the Apache developers changed the default configuration to block all requests.  To fix this, you must explicitly grant permissions via your site configuration file.

In Ubuntu Linux, you will need to edit the appropriate site configuration file.  Typically, the name of this file is 000-default.conf and it is located in /etc/apache2/sites-available directory. Open this file in a text editor (remember to open it using sudo, since it is a system file) and add the following line in the <Directory> section:

Require all granted

For example, the updated <Directory> section may now look like:

        <Directory /var/www/>
                Options Indexes FollowSymLinks MultiViews
                AllowOverride All
                Order allow,deny
                allow from all
                Require all granted
        </Directory>

To make this change take effect, you must restart the Apache web server using this command:

sudo service apache2 restart

References

Stack Overflow – Apache VirtualHost 403 Forbidden

Increase session time-out in phpMyAdmin

If you do any development with PHP and MySQL, undoubtedly, you are familiar with phpMyAdmin, which is a web-based tool for managing and editing MySQL databases. While phpMyAdmin is an excellent tool, one of it’s annoying aspects is the default session time-out of 30 minutes. However, you can make a quick configuration change to extend the time-out to most any value that you like.

To do this, just open the config.inc.php file for phpMyAdmin (which is in the /etc/phpmyadmin directory on Ubuntu Linux) in an editor as the administrative user (i.e., use sudo in Linux). Then, add these lines within the 'Servers' configuration section:

/* Set session time out to 8 hours (28800 seconds) */
$cfg['Servers'][$i]['LoginCookieValidity'] = 28800;

Save the changes and restart the Apache web server:

sudo service apache2 restart

The next time that you log into phpMyAdmin, you will stay logged in for 8 hours (or whatever duration you specified). For more information, see phpMyAdmin documentation for LoginCookieValidatity.

Install latest version of Adminer MySQL administration tool on Ubuntu Linux

Adminer is a lightweight, PHP-based MySQL administration tool that is a great alternative to PHPMyAdmin. It comes as a single file and can easily be installed globally on your Ubuntu Linux box, including a Vagrant box.

The prerequisites for installing and using Adminer are PHP, MySQL, and Apache, the so-called LAMP stack. To install them on Ubuntu, in the terminal, run:

sudo apt-get install php5 php5-cli php 5-mysql mysql-server apache2

You’ll be prompted for configuration information during the installation.

Once the LAMP stack is installed, install Adminer by running these commands in the terminal:

sudo mkdir /usr/share/adminer
sudo wget -O /usr/share/adminer/latest.php "http://www.adminer.org/latest.php"
sudo ln -s /usr/share/adminer/latest.php /usr/share/adminer/adminer.php
echo "Alias /adminer.php /usr/share/adminer/adminer.php" | sudo tee /etc/apache2/conf.d/adminer.conf
sudo service apache2 restart

After installing Adminer, open a web browser and navigate to http://localhost/adminer.php. The Adminer login screen should be displayed. You can now log in to any of the MySQL databases configured.

If you want to upgrade to a new version of Adminer later, since you created a symlink to the latest.php, you can simply run this command in the terminal:

sudo wget -O /usr/share/adminer/latest.php "http://www.adminer.org/latest.php"

Use PHP array_map() function to remove white space from array elements

One common scenario in PHP is take an input list, such as some search criteria entered by a user, and tokenize the input into individual items using a separator, such as comma. The PHP explode() function works great for splitting up the individual values into an array(). However, if have white space before or after the separator, each of the elements of the new array will contain that white space. You could use a foreach loop, with the $key => $value syntax to apply the trim() function to each item. But PHP has the perfect function already built in: array_map().

array_map() takes a callback function as its first argument and an array as the second argument. It applies the specified callback function to each element of the array that is passed in. You simply provide the name of the callback function as a string (either in single or double quotes) and the function can be a function within your code or any of the built-in PHP functions. For example, to put all of the pieces together, you could do something like this:

$input = "apricot, banana, cherry, dewberry, eggplant, fig";
$output_trimmed = array_map("trim", explode(',', $input));