Downloading instructions
Distribution variations
Installation procedures (source, binary, RPMs)
Setting the MySQL administrator password
Starting and stopping MySQL
Installing MySQL as a system service
MySQL configuration and optimization issues
Reconfiguring PHP to use MySQL
By the chapter’s conclusion, you’ll have learned how to install and configure an operational MySQL server.
Downloading MySQL
Two editions of the MySQL database are available: MySQL Community Server and MySQL Enterprise Server. You should use the former if you don’t require MySQL’s array of support, monitoring, and priority update services. If any or all of the aforementioned services might appeal to you, learn more about MySQL Enterprise at https://www.mysql.com/products/enterprise . This book presumes you’re using the Community Server edition, which is available for free download via the MySQL website.
To download the latest MySQL version, navigate to https://www.mysql.com/downloads . From there, you’ll be able to choose from 10 different supported operating systems, or you can download the source code.
If you’re running Linux or OS X, I strongly recommend installing MySQL using your distribution’s package manager. Otherwise, you can install MySQL using available RPMs or the source code from https://www.MySQL.com . I’ll guide you through the process of installing MySQL from both RPM and source later in this chapter.
MySQL offers a wide range of packages for download, ranging from the server package to cluster versions and bundled tools to use on Windows for development or production environments. If you go to https://dev.mysql.com/downloads you can see a full list of the available packages. Similar you can go to https://mariadb.com/downloads/ to download the current version of MariaDB.
Installing MySQL
Database server installation can often be a painful process. Fortunately, MySQL server installation is fairly trivial. In fact, after a few iterations, you’ll find that future installations or upgrade sessions will take just a few minutes to complete and can even be done by memory.
In this section, you’ll learn how to install MySQL on both the Linux and Windows platforms. In addition to offering comprehensive step-by-step installation instructions, topics that often confuse both newcomers and regular users alike are discussed, including distribution format vagaries, system-specific problems, and more.
Note
Throughout the remainder of this chapter, the constant INSTALL-DIR is used as a placeholder for MySQL’s base installation directory. Consider modifying your system path to include this directory.
Installing MySQL on Linux
Although MySQL has been ported to at least 10 platforms, its Linux distribution remains the most popular. This isn’t surprising because Linux is commonly used in conjunction with running web-based services. This section covers the installation procedures for all three of MySQL’s available Linux distribution formats: RPM, binary, and source. In addition, it’s available through most Linux distributions package managers (yum, apt-get etc). This is usually the easiest and best way to install and manage the MySQL installation. There is no need to deal with compilers or manual installations.
RPM, Binary, or Source?
Software intended for the Linux operating system often offers several distribution formats. MySQL is no different, offering RPM, binary, and source versions of each released version. Because these are all popular options, this section offers instructions for all three. If you’re new to these formats, take care to read each of these sections carefully before settling upon a format, and perform additional research if necessary.
The RPM Installation Process
If you’re running a RPM-driven Linux distribution , the RPM Package Manager (RPM) provides a simple means for installing and maintaining software. RPM offers a common command interface for installing, upgrading, uninstalling, and querying software, largely eliminating the learning curve historically required of general Linux software maintenance.
Tip
Although you’ll learn a few of RPM’s more useful and common commands in this section, it hardly scratches the surface of its capabilities. If you’re unfamiliar with RPM format, you can learn more about it at www.rpm.org .
MySQL offers RPMs for a variety of different processor architectures. To carry out the examples found throughout the remainder of this book, you need to download only the MySQL-server and MySQL-client packages. Download these packages, saving them to your preferred distribution repository directory. It’s typical to store packages in the /usr/src directory , but the location has no bearing on the final outcome of the installation process.
You might consider adding the –v option to view progress information as the RPM installs. Upon execution, the installation process will begin. Assuming all goes well, you will be informed that the initial tables have been installed, and that the mysqld server daemon has been started.
This will install both the client and server elements of MariaDB. It is still possible to install the MySQL version on CentOS but it is no longer the preferred/supported version.
This command will actually install the MariaDB version of the server.
Believe it or not, by executing this single installation command, the initial databases have also been created, and the MySQL server daemon is running.
Tip
Uninstalling MySQL is as easy as installing it, involving only a single command:
%>rpm –e MySQL-VERSION
Although the MySQL RPMs offer a painless and effective means to an end, this convenience comes at the cost of flexibility. For example, the installation directory is not relocatable; that is, you are bound to the predefined installation path as determined by the packager. This is not necessarily a bad thing, but the flexibility is often nice and sometimes necessary. If your personal situation requires that added flexibility, read on to find out about the binary and source installation processes. Otherwise, proceed to the “Setting the MySQL Administrator Password” section .
The Binary Installation Process
A binary distribution is simply precompiled source code, typically created by developers or contributors with the intention of offering users a platform-specific optimized distribution. Although this chapter focuses on the Linux installation process, keep in mind that the procedure is largely identical for all platforms (many of which are available for download on the MySQL website) except for Windows, which is covered in the next section.
To install the MySQL binary on Linux, you need to have tools capable of unzipping and untarring the binary package. Most Linux distributions come with the GNU gunzip and tar tools, which are capable of carrying out these tasks.
You can download the MySQL binary for your platform by navigating to the MySQL website’s Downloads section. Unlike the RPMs, the binaries come with both the server and client packaged together, so you need to download only a single package. Download this package, saving it to your preferred distribution repository directory. It’s common to store packages in the /usr/src directory , but the location has no bearing on the final outcome of the installation process.
- 1.Create the necessary group and owner (you need to have root privileges for this and the following steps):%>groupadd mysql%>useradd –g mysql mysql
- 2.Decompress the software to the intended directory. Using the GNU gunzip and tar programs are recommended.%>cd /usr/local%>tar -xzvf /usr/src/mysql-VERSION-OS.tar.gz
- 3.Link the installation directory to a common denominator:%>ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql
- 4.Install the MySQL database. mysql_install_db is a shell script that logs in to the MySQL database server, creates all of the necessary tables, and populates them with initial values.%>cd mysql%>chown -R mysql .%>chgrp -R mysql .%>scripts/mysql_install_db --user=mysql%>chown -R root .%>chown -R mysql data
That’s it! Proceed to the “Setting the MySQL Administrator Password” section .
The Source Installation Process
The MySQL developers have gone to great lengths to produce optimized RPMs and binaries for a wide array of operating systems, and you should use them whenever possible. However, if you are working with a platform for which no binary exists, require a particularly exotic configuration, or happen to be a rather controlling individual, then you also have the option to install from source. The process takes only slightly longer than the binary installation procedure.
- 1.Create the necessary group and owner:%>groupadd mysql%>useradd –g mysql mysql
- 2.Decompress the software to the intended directory. Using the GNU gunzip and tar programs is recommended.%>cd /usr/src%>gunzip < /usr/src/mysql-VERSION.tar.gz | tar xvf -%>cd mysql-VERSION
- 3.Configure, make, and install MySQL. A C++ compiler and make program are required. Using recent versions of the GNU gcc and make programs is strongly recommended. Keep in mind that OTHER-CONFIGURATION-FLAGS is a placeholder for any configuration settings that determine several important characteristics of the MySQL server, such as installation location. It’s left to you to decide which flags best suit your special needs.%>./configure –prefix=/usr/local/mysql [OTHER-CONFIGURATION-FLAGS]%>make%>make install
- 4.Copy the sample MySQL configuration (my.cnf ) file into its typical location and set its ownership. The role of this configuration file is discussed in depth later, in the “The my.cnf File” section.%>cp support-files/my-medium.cnf /etc/my.cnf%>chown -R mysql .%>chgrp -R mysql .
- 5.Install the MySQL database. mysql_install_db is a shell script that logs in to the MySQL database server, creates all of the necessary tables, and populates them with initial values.%>scripts/mysql_install_db --user=mysql
- 6.Update the installation permissions:%>chown -R root .%>chown -R mysql data
That’s it! Proceed to the “Setting the MySQL Administrator Password” section .
Installing and Configuring MySQL on Windows
Open source products continue to make headway on the Microsoft Windows server platform, with historically predominant Unix-based technologies like the Apache Web server, PHP, and MySQL gaining in popularity. In addition, for many users, the Windows environment offers an ideal development and testing ground for web/database applications that will ultimately be moved to a production Linux environment.
Installing MySQL on Windows
As it is the case with the Linux version, it is possible to install both MySQL and MariaDB on a Windows system. Any version of Windows above 8 works fine. Both databases can be installed via MSI installation files. These will not only install and configure the necessary files but also prompt the user to set a root password and perform other security settings.
Although it is possible to install from source, it’s not recommended to do so. The installation packages take care of security settings, and you do not need to have access to compilers and other build tools not normally installed on a Windows system.
Start by downloading the MSI installation file from MySQL ( https://dev.mysql.com/downloads/mysql/ ) or MariaDB ( https://mariadb.com/downloads/mariadb-tx ). The two installers work slightly different based on the differences in the two products. Although they share the same root, they have evolved to include different options.
Starting and Stopping MySQL
The MySQL server daemon is controlled via a single program, located in the INSTALL-DIR/bin directory . Instructions for controlling this daemon for both the Linux and Windows platforms are offered in this section.
Controlling the Daemon Manually
Although you’ll ultimately want the MySQL daemon to automatically start and stop in conjunction with the operating system, you’ll often need to manually execute this process during the configuration and application testing stages.
Starting MySQL on Linux
Keep in mind that mysqld_safe will not execute unless you first change to the INSTALL-DIR directory . In addition, the trailing ampersand is required because you’ll want the daemon to run in the background.
The mysqld_safe script is actually a wrapper around the mysqld server daemon, offering features that are not available by calling mysqld directly, such as runtime logging and automatic restart in case of error. You’ll learn more about mysqld_safe in the “Configuring MySQL” section.
Starting MySQL on Windows
Presuming you followed the instructions from the earlier section “Configuring MySQL on Windows,” then MySQL has already been started and is running as a service. You can start and stop this service by navigating to your Services console, which can be opened by executing services.msc from a command prompt .
Stopping MySQL on Linux and Windows
Assuming that you supply the proper credentials, you will be returned to the command prompt without notification of the successful shutdown of the MySQL server. In the case of an unsuccessful shutdown attempt, an appropriate error message is offered.
Configuring and Optimizing MySQL
Unless otherwise specified, MySQL assumes a default set of configuration settings upon each start of the MySQL server daemon. Although the default settings are probably suitable for users who require nothing more than a standard deployment, you’ll at least want to be aware of what can be tweaked, because such changes not only will better adapt your deployment to your specific hosting environment, but could also greatly enhance the performance of your application based on its behavioral characteristics. For example, some applications might be update intensive, prompting you to adjust the resources that MySQL requires for handling write/modification queries. Other applications might need to handle a large number of user connections, prompting a change to the number of threads allocated to new connections. Happily, MySQL is highly configurable; as you’ll learn in this and later chapters, administrators have the opportunity to manage just about every aspect of its operation.
This section offers an introduction to many of the configuration parameters that affect the general operation of the MySQL server. Because configuration and optimization are such important aspects of maintaining a healthy server (not to mention a sane administrator), this topic is returned to often throughout the remainder of the book.
The mysqld_safe Wrapper
Although the aforementioned mysqld is indeed MySQL’s service daemon, you actually rarely directly interact with it; rather, you can interface with the daemon through a wrapper called mysqld_safe. The mysqld_safe wrapper adds a few extra safety-related logging features and system-integrity features to the picture when the daemon is started. Given these useful features, mysqld_safe is the preferred way to start the server, although you should keep in mind that it’s only a wrapper and should not be confused with the server itself.
Note
Installing from RPM or Debian packages includes some extra support for systemd and so mysqld_safe is not installed on these platforms. Use the my.cnf configuration file instead, detailed in the next section.
The next section highlights several of the more commonly used parameters .
MySQL’s Configuration and Optimization Parameters
This section introduces several basic configuration parameters that might be useful to tweak when getting started managing the server. But first take a moment to review how you can quickly view MySQL’s present settings.
Viewing MySQL’s Configuration Parameters
Managing Connection Loads
A well-tuned MySQL server is capable of working with many connections simultaneously. Each connection must be received and delegated to a new thread by the main MySQL thread, a task that, although trivial, isn’t instantaneous. The back_log parameter determines the number of connections that are allowed to queue up while this main thread deals with a particularly heavy new connection load. By default, this is set to 80.
Keep in mind that you can’t just set this to a very high value and assume it will make MySQL run more efficiently. Both your operating system and web server may have other maximum settings in place that could render a particularly high value irrelevant.
Setting the Data Directory Location
Keep in mind that you need to copy or move the MySQL permission tables (stored in DATADIR/mysql ) to this new location. Because MySQL’s databases are stored in files, you can do so by using operating system commands that are typical for performing such actions, such as mv and cp. If you’re using a GUI, you can drag and drop these files to the new location .
Setting the Default Storage Engine
Once it is assigned, all subsequent table creation queries will automatically use the MEMORY engine unless otherwise specified.
Automatically Executing SQL Commands
Logging Potentially Nonoptimal Queries
The log-queries-not-using-indexes parameter defines a file to which all queries are logged that aren’t using indexes. Regularly reviewing such information could be useful for discovering possible improvements to your queries and table structures .
Logging Slow Queries
The log_slow_queries parameter defines a file to which all queries are logged that take longer than long_query_time seconds to execute. Each time that query execution time surpasses this limit, the log_slow_queries counter is incremented. Studying such a log file using the mysqldumpslow utility could be useful for determining bottlenecks in your database server.
Setting the Maximum Allowable Simultaneous Connections
The max_connections parameter determines the maximum permitted number of simultaneous database connections. By default, this is set to 151. You can check the maximum number of connections simultaneously opened by your database by reviewing the max_used_connections parameter, available by executing SHOW STATUS. If you see that this number is approaching the century mark, consider bumping the maximum upward. Keep in mind that as the number of connections increases, so will memory consumption, because MySQL allocates additional memory to every connection it opens.
Setting MySQL’s Communication Port
By default, MySQL communicates on port 3306; however, you can reconfigure it to listen on any other port by using the port parameter .
Disabling DNS Resolution
Enabling the skip-name-resolve parameter prevents MySQL from resolving hostnames. This means that all Host column values in the grant tables consist either of an IP address or localhost. If you plan to use solely IP addresses or localhost, enable this parameter. The DNS lookup will convert a host name to an IP address before the connection is attempted. Enabling this option will disable the lookup allowing only IP addresses to work. The hostname localhost is a special case that always resolve to the local ip address (127.0.0.1 for IVv4).
Limiting Connections to the Local Server
Enabling the skip-networking parameter prevents MySQL from listening for TCP/IP connections and to use a UNIX socket instead. This will prevent remote access to the server without the need to configure special firewall rules.
Setting the MySQL Daemon User
The my.cnf File
You’ve already learned that configuration changes can be made on the command line when starting the MySQL daemon via its wrapper, mysqld_safe . However, there exists a much more convenient method for tweaking the startup parameters—as well as the behaviors—of many MySQL clients, including mysqladmin, myisamchk, myisampack, mysql, mysqlcheck, mysqld, mysqldump, mysqld_safe, mysql.server, mysqlhotcopy, mysqlimport, and mysqlshow. You can maintain these tweaks within MySQL’s configuration file, my.cnf.
/etc/my.cnf (C:my.cnf or windows-sys-directorymy.ini on Windows): Global configuration file. All MySQL server daemons located on the server refer first to this file. Note the extension of .ini if you choose to place the configuration file in the Windows system directory.
DATADIR/my.cnf: Server-specific configuration. This file is placed in the directory referenced by the server installation. A somewhat odd, yet crucial characteristic of this configuration file is that it references only the data directory specified at configuration time, even if a new data directory is specified at runtime. Note that MySQL’s Windows distribution does not support this feature.
--defaults-extra-file=name: The file specified by the supplied file name, complete with absolute path.
~/.my.cnf: User-specific configuration. This file is expected to be located in the user’s home directory. Note that MySQL’s Windows distribution does not support this feature.
MySQL Configuration Templates
Name | Description |
|---|---|
my-huge.cnf | Intended for high-end production servers, containing 1 to 2GB RAM, tasked with primarily running MySQL |
my-innodb-heavy-4G.cnf | Intended for InnoDB-only installations for up to 4GB RAM involving large queries and low traffic |
my-large.cnf | Intended for medium-sized production servers, containing around 512MB RAM, tasked with primarily running MySQL |
my-medium.cnf | Intended for low-end production servers containing little memory (less than 128MB) |
my-small.cnf | Intended for minimally equipped servers, possessing nominal RAM (less than 64MB) |
Comments are prefaced with a hash mark (#) .
Variables are assigned exactly like they would be when assigned along with the call to mysqld_safe, except that they are not prefaced with the double hyphen.
The context of these variables is set by prefacing the section with the intended beneficiary, enclosed in square brackets. For example, if you want to tweak the default behavior of mysqldump, you begin with:
This context is assumed until the next square-bracket setting is encountered.
Configuring PHP to Work with MySQL
The PHP and MySQL communities have long enjoyed a close relationship. The respective technologies are like two peas in a pod, bread and butter, wine and cheese … you get the picture. The popularity of MySQL within the PHP community was apparent from the earliest days, prompting the PHP developers to bundle the MySQL client libraries with the distribution and enable the extension by default in PHP version 4.
But you can’t just install PHP and MySQL and necessarily expect them to automatically work together. You need to carry out just a few more steps, described next.
Reconfiguring PHP on Linux
On Linux systems , after you successfully install MySQL, you need to reconfigure PHP, this time including the --with-mysqli[=DIR] configuration option, specifying the path to the MySQL installation directory. Once the build is complete, restart Apache and you’re done.
Reconfiguring PHP on Windows
Restart Apache or IIS and you’re ready to begin using PHP and MySQL together!
Note
Regardless of platform, you can verify that the extensions are loaded by executing the phpinfo() function (see Chapter 2 for more information about this function).
Summary
This chapter set the stage for starting experimentation with the MySQL server. You learned not only how to install and configure MySQL, but also a bit regarding how to optimize the installation to best fit your administrative and application preferences. Configuration and optimization issues are revisited throughout the remainder of this book as necessary.
The next chapter introduces MySQL’s many clients, which offer a convenient means for interacting with many facets of the server .