Getting Started with MySQL

This chapter presents the first steps of getting started with MySQL. I’ll show you how to install MySQL on both Linux and Windows systems, so be sure to follow along on the platform of your choice. Then you’ll begin to get acquainted with MySQL’s command-line client as we use it to connect to the database server and create our first database.

Often the first step of installing an application is to determine which version is appropriate, so it’s worth noting that MySQL is available in several “flavors.” From Oracle there is the freely available Community Edition and the paid commercial Standard, Enterprise, and Cluster Carrier Grade editions. The differences between Community Edition and the paid versions boil down to licensing and support contracts, some additional server plugins, and backup and monitoring utilities.

MySQL is open-source software released under the GNU General Public License so it should come as no surprise there are also alternative forks available. Two popular forks are MariaDB, a community-maintained “enhanced, drop-in replacement” for MySQL, and Percona Server, a drop-in maintained by the consulting firm Percona LLC. The differences between MySQL, MariaDB, and Percona are mostly imperceptible to the casual user.

You’re free to use whichever flavor of MySQL you like, but to maintain focus and consistency I’ll use Oracle’s Community Edition version 5.6.23 (the current stable release at the time I’m writing this book). I’ll also limit these instructions to Debian/Ubuntu, RedHat/CentOS, and Windows Server 2012. This list of operating systems covers the major platforms that MySQL is likely to run on in a production environment.

 Installing MySQL on Linux

Linux isn’t a homogeneous platform and each distro has a preferred way to install software. In this section, I’ll cover how to install MySQL on Debian/Ubuntu and Red Hat/CentOS systems using a package manager and how to compile and install MySQL from source. This will equip you with the necessary skills to handle most any Linux-based installation scenarios you may encounter.

 Installing via a Package Manager

Most modern Linux systems use a package manager to make software installation a trivial task. And because it’s so popular, chances are MySQL or one of its forks is available in your distro’s package repositories. Debian/Ubuntu offers Oracle’s MySQL Community Edition in their repos, and users can get up and running by simply typing sudo apt-get install mysql-server. Red Hat/CentOS repositories recently replaced MySQL with MariaDB; users can install MariaDB with su -c 'yum install mariadb-server'.

Installing software from a distro-maintained repository is fine for most users, but relying on these repos may not give you the most current release. Luckily, we don’t have to give up the convenience that working with packages affords us. Oracle provides up-to-date RPM and DEB packages which can be installed using rpm and dpkg. They also maintain APT and Yum repositories and provide special packages to automatically add these repos to your system’s list of known repositories.

The following steps register one of Oracle’s repositories and install MySQL Community Edition from it. If your server isn’t running a graphical interface and you can’t use a text-based browser like Lynx, you’ll need to complete the first four steps on another system and copy the file to your server.

1. Open a browser and navigate to the MySQL Repositories page at http://dev.mysql.com/downloads/repo.

2. Click the Download link for the MySQL Yum Repository or MySQL APT Repository depending on your platform’s package manager. You’ll be redirected to a page that lists various configuration packages.

3. Click the Download button next to the package appropriate for your system. For example, a Red Hat/CentOS 7 user should download the package Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package. An Ubuntu user using Trusty Tahr should download the package Ubuntu Linux 14.04 (Architecture Independent), DEB.

4. Oracle will try to trick you into signing up for an account. This isn’t mandatory, so scroll down to the bottom of the page and click the link No thanks, just start my download to start the download.

5. Using a terminal window, navigate to the directory you downloaded (or copied) the package to and execute the appropriate command to install it:

   • Red Hat/CentOS users should run rpm -i mysql-community-release-el7-5.noarch.rpm.

   • Debian/Ubuntu users should run dpkg -i mysql-apt-config_0.2.1-1ubuntu14.04_all.deb.

6. The repository is now registered and you can install MySQL Community Edition with your package manager:

   • Red Hat/CentOS users should run su -c 'yum install mysql-community-server'.

   • Debian/Ubuntu users should run sudo apt-get install mysql-server-5.6.

Ubuntu users will be prompted during the installation process for a password for MySQL’s root user (Debian and Red Hat/CentOS users will provide this password with a post-install command in the next step). MySQL maintains its own list of accounts separate from the user accounts on our system—that is, while the username may be the same, the MySQL root user isn’t the same as the Linux root user.

Red Hat/CentOS users should run these post-install commands to set the password for MySQL’s root user, register MySQL as a system service, and start a running instance (Debian/Ubuntu automatically registers and starts MySQL):

1. Set the root user’s password for MySQL: mysqladmin -u root password.

2. Register MySQL to start when the system boots: su -c 'chkconfig --level 2345 mysqld on'.

3. Start the MySQL server: su -c 'systemctl start mysql'.

MySQL Community Edition is now installed on your system. For future reference, the following commands are used to start, stop, and check the running status of MySQL:

• Start MySQL

  • Ubuntu — sudo service mysql start

  • Debian — sudo systemctl start mysqld

  • Red Hat/CentOS — su -c 'systemctl start mysql'

• Stop MySQL

  • Ubuntu — sudo service mysql stop

  • Debian — sudo systemctl stop mysqld

  • Red Hat/CentOS — su -c 'systemctl stop mysql'

• Query MySQL’s running state

  • Ubuntu — service mysql status

  • Debian — sudo systemctl status mysqld

  • Red Hat/CentOS — su -c 'systemctl status mysql'

 Installing from Source

It’s becoming less and less common for system administrators to compile software from source code, but doing so often gives complete control over an application’s features, optimizations, and configuration settings. As you might expect, it’s also the most involved installation method.

The following steps show how to download the MySQL Community Edition source code, compile it, and install it. Again, if you don’t have access to a graphical interface or text-based browser on the server then you’ll need to complete the first few steps on another system and copy over the download.

1. Open a browser and navigate to the MySQL Community Downloads page at http://dev.mysql.com/downloads.

2. Click the MySQL Community Server link to be taken to the Download MySQL Community Server page. The various platform options are filtered by the drop-down labeled Select Platform.

3. Set the drop-down to Source Code, scroll down to the Generic Linux (Architecture Independent), Compressed TAR Archive entry, and click its Download button.

4. An Oracle account isn’t mandatory for continuing with the download. Scroll to the bottom of the page and click the link No thanks, just start my download to begin the download.

5. Using a terminal window, create a new user account dedicated solely to running the MySQL server:

   sudo groupadd mysqlsudo useradd -r -g mysql mysql

6. Navigate to the directory you downloaded the source archive to. Extract the archive and change into the code’s directory:

   cd /tmpgzip -cd mysql-5.6.23.tar.gz | tar xvf -cd mysql-5.6.23

7. Generate the build scripts by running cmake. I don’t specify any options below, but a full list of configuration options can be found in the online documentation.

   cmake .

8. Run make to compile MySQL, and then with elevated privileges run make install to copy the resulting binaries, utilities, libraries, and documentation files to their new home on your system:

   makesudo make install

9. Make sure the installed files are assigned the correct ownership and access permissions:

   sudo chown -R mysql /usr/local/mysqlsudo chgrp -R mysql /usr/local/mysql

10. MySQL’s data directory and system tables need to be initialized by the mysql_install_db script found in the installation’s scripts directory. The script uses paths relative to the installation directory, so invoke it from the installation directory rather than the scripts directory or somewhere else:

    cd /usr/local/mysqlsudo scripts/mysql_install_db --user=mysql

11. Start MySQL and set its root user’s password:

    sudo mysqld_safe &mysqladmin -u root password

The installation of MySQL itself is complete, but there’s still some additional system configuration tasks you should consider. I recommend adding the installation’s bin directory to the PATH environment variable so you can run MySQL’s utilities without providing a full path each time. Assuming you use Bash, add the following lines to /etc/profile:

It’s also likely you’ll want MySQL to start automatically when the system boots. These steps assume your system uses a SysV-style init process.

1. Place a copy of the mysql.server script found in the source code’s support-files directory in your system’s init.d directory and make the script executable:

   sudo cp /tmp/mysql-5.6.23/support-files/mysql.server \ /etc/init.d/mysqlsudo chmod 755 /etc/init.d/mysql

2. Create symbolic links that point to the script from the desired runlevels:

   ln -s /etc/init.d/mysql /etc/rc3.d/S99mysqlln -s /etc/init.d/mysql /etc/rc0.d/K01mysql

You can now run the command sudo /etc/init.d/mysql start to start MySQL and run sudo /etc/init.d/mysql stop to stop it.

Installing MySQL on Windows

Windows is a relatively homogeneous platform compared to Linux even though several versions of the OS are actively maintained at any given time by Microsoft. The instructions here target Server 2012, but may be more or less applicable to a desktop OS like Windows 8.

1. Open a browser and navigate to the MySQL Community Downloads page at http://dev.mysql.com/downloads.

2. Click the link for MySQL Community Server to be taken to the Download MySQL Community Server page. The various platform options here are filtered by the drop-down labeled Select Platform.

3. Set the drop-down to Microsoft Windows and click the Download button next to the appropriate Windows MSI Installer for your architecture, most likely 64-bit.

4. Scroll to the bottom of the page and click the link No thanks, just start my download to begin the download.

5. Navigate to the folder you downloaded the MSI file to and double-click the file to launch the installation wizard.

6. Advance through the wizard’s welcome screen by pressing the Next button.

7. At the License Agreement screen, click the checkbox to accept the terms of the agreement, and press Next.

8. At the Choose Setup Type screen, choose Typical, then press the Install button to begin the installation. You may be prompted by User Account Control to proceed depending on the security policies in effect.

9. Press the Finish button once the wizard is finished.

Now follow these post-install configuration steps to add the installation’s bin directory to the system PATH variable and register MySQL as a service.

1. Open the System Properties window.

   1. Press the key combination WIN-C to bring up the Edge UI.

   2. Click the Search charm, search for Control Panel, and click on the Control Panel icon when it appears in the results.

   3. If Control Panel is in Category view, click the System and Security entry and then System to launch the System panel item. If Control Panel is in Icon view, click the System icon.

2. Click the Advanced systems settings link to open the System Properties window.

3. Select the Advanced tab if it’s not already selected and then press the Environment Variables button to open the Environment Variables window.

4. Select the Path entry in the System variables section and press the Edit button.

5. Add the bin directory’s path (C:\Program Files\MySQL\MySQL Server 5.6\bin) to the end of the existing value, separating the entry from the previous entries with a semicolon.

6. Open Command Prompt with administrator privileges. Depending on the security policies in effect, you may be prompted by User Account Control to continue.

   1. Press the key combination WIN-C to bring up the Edge UI.

   2. Click the Search charm and search for Command Prompt.

   3. Right-click the Command Prompt icon when it appears in the results and select Run as administrator.

7. Run mysqld.exe --install at the prompt. The command should report back the service was successfully installed.

You’re now able to invoke the utilities when using Command Prompt without providing their full path because MySQL’s bin directory appears in the list that Windows searches for executables. And since MySQL is registered as a service, it will start automatically when the system boots and can be controlled from Windows Service Manager. Alternatively, the following commands may be executed in Command Prompt with administrator privileges to start and stop the MySQL server as well.

• Start MySQL — net start mysql

• Stop MySQL — net stop mysql

 Communicating with the Server

A MySQL server sits idle, waiting to receive queries. When it receives one, the server performs the requested action on our behalf and responds back with the result. There are several ways we can communicate with MySQL, for example programmatically from an application we wrote or interactively using a dedicated client program. We’ll use the command-line client that’s included in the MySQL installation to connect and communicate with the running server throughout most of this book, and in Chapter 5 we’ll discuss sending SQL statements programmatically.

Open a terminal window or Command Prompt and run mysql -u root -p. The -u option specifies the username of the MySQL account used for the connection and -p will prompt for the account’s password. When prompted, enter the root account’s password you set earlier.

The client displays the mysql> prompt once you’ve successfully connected to MySQL. It’s at this prompt we’ll submit our SQL statements. The client displays the server’s response, timing information for how long it took to execute the request, and whether any errors or warnings were encountered.

The MySQL server is capable of managing more than one database at a time. To ask what databases it’s managing, enter SHOW DATABASES; at the prompt. The response will show a list of all the databases MySQL is managing. If you’re connected to a newly installed instance then you’ll only see the three databases that are used by MySQL itself: information_schema, mysql, and performance_schema. You may also see a test database which is created by mysql_install_db for use as a sandbox.

The CREATE DATABASE statement creates a new database. To create a database named “jumpstart”, send the statement CREATE DATABASE jumpstart; at the prompt. Then send SHOW DATABASES; again, and you’ll see the new database added to the list.

To let the client know we want to work with a specific database, we use the USE command. Enter USE jumpstart; at the prompt, and all subsequent statements we send will be executed against the jumpstart database. It’s possible to specify a target database when connecting with the command-line client, for example mysql -u root -p jumpstart.

The SHOW TABLES statement instructs MySQL to return a list of tables in the currently active database. Of course, we haven’t added any tables to the jumpstart database yet so sending SHOW TABLES; will be met with the response “Empty set.” There’s a fair bit of planning involved to create a table properly, and we’ve covered a lot already, so I’ll save that for the next chapter.

To quit the client, either type exit or use the key combination CTRL-D.

 MySQL Accounts and Security

The final thing I feel the need to cover in this chapter is MySQL user accounts. Even though MySQL’s root user isn’t the same as the system’s root account, it’s still not intended to be used on a regular basis. The MySQL root user should only be used for administrative tasks such as creating new user accounts, setting permissions, and flushing access caches. Less privileged accounts should be used on a day-to-day basis.

To create a new user account, connect to the MySQL server with the command-line client using the root account and send the following CREATE USER statement:

The statement creates a new account with the username “jump” and password “secret” that will permit the user to authenticate from the same system MySQL is running on. Different hostnames and IP addresses can be used in place of localhost to allow connections from different systems and networks. However, bear in mind that MySQL considers each username/hostname pair to be a separate account. That is, jump@localhost and jump@192.168.1.100 are treated as separate accounts, each with their own set of privileges.

Whether MySQL permits a user to perform an activity depends on what privileges are associated with the account. New accounts are created without any privileges so we must explicitly grant any that the account will need. The "jump" user will require several privileges as you use it to follow along throughout the rest of this book. For now, let’s grant a basic set of privileges to start with (you can grant additional privileges as they become necessary). Enter the following statement:

The syntax of MySQL’s GRANT statement is flexible enough that we can narrow the scope of a privilege down to specific columns of a table, or to certain tables in a database. Here, we’ve simply instructed MySQL to allow these permissions for all tables (denoted by the *) in our jumpstart database. The privileges granted are:

• CREATE — allows the user to create databases and tables

• DROP — allows the user to delete entire tables and databases

• ALTER — allows the user to change the definition of an existing table

• INSERT — allows the user to add records to a table

• UPDATE — allows the user to update existing records in a table

• SELECT — allows the user to retrieve existing records from a table

• DELETE — allows the user to delete existing records from a table

• INDEX — allows the user to create or delete indexes

A full list of privileges and what they allow an account to do can be found in the documentation. In the future, if it’s determined an account needs extra privileges then they can be granted by issuing another GRANT statement. Privileges that are no longer needed can be revoked with a REVOKE statement, the syntax of which is identical to that of GRANT:

Whenever a user-related or privilege-related change is made, we need to send a FLUSH PRIVILEGES statement to instruct MySQL to reload the cache of account information it maintains so the updates can take effect. Otherwise, the changes will go unnoticed until MySQL is restarted:

Exit the command-line client after you send the FLUSH PRIVILEGES statement and reconnect using the new "jump" account. If you’ve entered the statements correctly, and provided the correct password when prompted, you’ll be greeted with the mysql> prompt.

 Conclusion

We’ve definitely covered a lot of ground in this chapter. You’ve learned how to install MySQL on various platforms, how to connect to a MySQL server using the command-line client, how to create a new database, and even a bit about basic MySQL user management.

Although you may be anxious to dive into the next chapter, I suggest you skim through the online MySQL manual first—specifically to see what it has to say on the topics we’ve covered so far. Review the details of the CREATE USER and GRANT statements. Learn how to change an account’s password and how to delete an account that’s no longer needed. Think about what privileges you’d assign to an account that needs to store and retrieve data as part of some back-end process for a website.

In Chapter 2, we’ll get into the specifics of storing data in a database. I’ll show you how to create a table and insert new rows into it. We’ll also discuss what types of data can be stored in a table, what a storage engines is, and how our choice of engine affects the way MySQL manages our data.