Learning MySQL and MariaDB (2015)
Part I. The Software
Chapter 2. Installing MySQL and MariaDB
The MySQL and MariaDB database server and client software works on several different operating systems, notably several distributions of Linux, Mac OS X, FreeBSD, Sun Solaris, and Windows.
This chapter briefly explains briefly the process of installing MySQL or MariaDB on Linux, Mac OS X, and Windows operating systems. For some operating systems, this chapter has additional sections for different distribution formats. For any one platform, you can install MySQL by reading just three sections of this chapter: the next section on choosing a distribution; the section that applies to the distribution that you choose; and Post-Installation at the end of the chapter. There’s no need to read how to install every version of MySQL.
The Installation Packages
The MySQL and MariaDB packages come with several programs. Foremost is the server, represented by the mysqld daemon.[2] It has the same name in both MySQL and MariaDB. This daemon is the software that actually stores and maintains control over all of the data in the databases. Themysqld daemon listens for requests on a particular port (3306, by default) by which clients submit queries. The standard MySQL client program is called simply mysql. With this text-based interface, a user can log in and execute SQL queries. This client can also accept queries from text files containing queries, and thereby execute them on behalf of the user or other software. However, most MySQL interaction is done by programs using a variety of languages. The interfaces for Perl, PHP, and others are discussed in Chapter 16.
A few wrapper scripts for mysqld come with the server installation. The mysqld_safe script is the most common way to start mysqld, because this script can restart the daemon if it crashes. This helps ensure minimal downtime for database services. You don’t need to know the details of how all of this works if you’re just starting to learn MySQL and MariaDB, but it gives you a sense of how powerful and versatile this database system can be.
MySQL, and thereby MariaDB, also comes with a variety of utilities for managing the server. The mysqlaccess tool creates user accounts and sets their privileges. The mysqladmin utility can be used to manage the database server itself from the command line. This kind of interaction with the server includes checking a server’s status and usage, and shutting down a server. The mysqlshow tool may be used to examine a server’s status, as well as information about databases and tables. Some of these utilities require Perl, or ActivePerl for Windows, to be installed on the server. See the Perl site to download and install a copy of Perl on non-Windows systems, and the ActivePerl site to download and install a copy of ActivePerl on Windows systems.
MySQL and MariaDB also come with a few utilities for importing and exporting data from and to databases. The mysqldump utility is the most popular one for exporting data and table structures to a plain-text file, known as a dump file. This can be used for backing up data or for copying databases between servers. The mysql client can be used to import the data back to MySQL from a dump file. These topics and utilities are explained in detail in Part I.
You can opt not to install the helper utilities. However, there’s no cost for them and they’re not large files. So you may as well install and use them.
Licensing
Although MySQL can be used for free and is open source, the company that develops MySQL — currently Oracle — holds the copyright to the source code. The company offers a dual-licensing program for its software: one allows cost-free use through the GPL under certain common circumstances, and the other is a commercial license requiring the payment of a fee. They’re both the same software, but each has a different license and different privileges. The website for the Free Software Foundation, which created the GPL, has details on the license.
Oracle allows you to use the software under the GPL if you use it without redistributing it, or if you redistribute it only with software that is licensed under the GPL. You can even use the GPL if you redistribute MySQL with software that you developed, as long as you distribute your software under the GPL as well. This is how MariaDB was created and why it is a legal fork of MySQL.
However, if you have developed an application that requires MySQL for its functionality and you want to sell your software with MySQL under a non-free license, you must purchase a commercial license from Oracle. There are other scenarios in which a commercial license may be required. For details on when you must purchase a license, see the MySQL legal site.
Besides holding the software copyright, Oracle also holds the MySQL trademark. As a result, you cannot distribute software that includes MySQL in its name. None of this is important to learning how to use MySQL, but it’s good for you to be aware of these things for when you become an advanced MySQL developer.
Finding the Software
You can obtain a copy of MySQL from MySQL’s site, which requires an Oracle login but is still free, or from one of its mirror sites. You can instead download MariaDB, which contains the latest release of MySQL and some additional features. You can get a copy of MariaDB from the MariaDB Foundation site, which is also free and requires registration.
When downloading the software on both sites, you’ll have to provide some information about yourself, your organization, and how you intend to use the software. They’re collecting information to understand how the software is used and to give to their sales department. But if you indicate that you don’t want to be contacted, you can just download the software and not have to interact further with them.
If your server or local computer has MySQL or MariaDB installed on it, you can skip this chapter. If you’re not sure whether MySQL or MariaDB is running on the computer you’re using, you could enter something like this from the command line of a Linux or Mac machine:
ps aux | grep mysql
If MySQL is running, the preceding command should produce results like the following:
2763 ? 00:00:00 mysqld_safe
2900 ? 5-23:48:51 mysqld
On a Windows computer, you can use the tasklist tool to see whether MySQL is running. Enter something like the following from the command line:
tasklist /fi "IMAGENAME eq mysqld"
If it’s running, you will get results like this:
Image Name PID Session Name Session# Mem Usage
============== ======= ============== ========= ==========
mysqld.exe 1356 Services 0 212 K
If it’s not running, you may get results like this from tasklist:
INFO: No tasks are running which match the specified criteria.
This isn’t conclusive proof that you don’t have MySQL installed. It just shows that the daemon isn’t running. You might try searching your computer for mysqld, using a file manager or some other such program. You might also try running mysqladmin, assuming it’s installed on your server, and use the first line shown here to test MySQL (an example of the results you should see follow):
mysqladmin -p version status
mysqladmin Ver 9.0 Distrib 5.5.33a-MariaDB, for Linux on i686
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Server version 5.5.33a-MariaDB
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 30 days 23 hours 37 min 12 sec
Threads: 4 Questions: 24085079 Slow queries: 0 Opens: 10832 Flush tables: 3
Open tables: 400 Queries per second avg: 8.996 Uptime: 2677032 Threads: 4
Questions: 24085079 Slow queries: 0 Opens: 10832 Flush tables: 3
Open tables: 400 Queries per second avg: 8.996
If one of these tests shows that MySQL is running on your computer, you may move onto Chapter 3. If MySQL is not running, it may be just that you need to start it. That’s covered in this chapter, at the end of each section for each version of MySQL. Look for the section related to your distribution of MySQL or MariaDB (e.g., Mac OS X) and skip to the end of that section to see how to start the daemon. Try then to start it. If it starts, skip to the end of this chapter and read Post-Installation. There are a few important points made in that section, in particular some security steps you should follow. If you’re unable to start the daemon, though, read the whole section for the distribution you choose.
Choosing a Distribution
Before beginning to download an installation package, you must decide which version of MySQL or MariaDB to install. For MySQL, the best choice is usually the latest stable version recommended by Oracle on its site, the version called the generally available (GA) release. This is the best way to go if you’re new to MySQL. There’s no need as a beginner to use a beta version, or a development release. Unless you have a support contract with Oracle, which would provide you access to the Enterprise version of MySQL, you will have to use the MySQL Community Server version. For a beginner, it’s essentially the same as the Enterprise version.
For MariaDB, the latest GA release will be the current stable version. You can download it from the MariaDB Foundation’s download page.
When installing one of these database systems, you also have the option of using either a source distribution or a binary distribution. The binary distribution is easier to install and is recommended. Use a source distribution only if you have special configuration requirements that must be set during the installation or at compile time. You may also have to use a source distribution if a binary distribution isn’t available for your operating system. Otherwise, install the binary; there’s no need to make installation difficult when your goal at this point should be to learn the basics of MySQL.
The _AMP Alternatives
The following sections describe different methods for downloading and installing MySQL or MariaDB for different operating systems, in different formats. An easy method, though, is to use one of the _AMP packages. These letters stand for Apache, MySQL/MariaDB, and PHP/Perl/Python. Apache is the most popular web server. PHP is the most popular programming language used with MySQL. An AMP package or stack is based on an operating system: the Linux stack is called LAMP, the Macintosh stack is called MAMP, and the Windows stack is called WAMP. If you download and install one of these stacks, it will install Apache, MySQL, PHP, and any software upon which they depend on your local computer or server. It’s a simple, turnkey method. If you install MySQL using a stack installation, you still need to make some post-installation adjustments. They’re explained in the last section of this chapter. So after installing, skip ahead to it.
Sites for these packages include:
§ The Apache XAMPP site for the latest Linux version (the extra P in LAMPP stands for Perl). Even though the site calls the package XAMPP instead of LAMPP, it’s the same thing.
§ The SourceForge MAMP site for the latest Mac version.
§ The EasyPHP WAMP site for the latest Windows vision.
All of these packages have easy-to-follow installation programs. The default installation options are usually fine.
Linux Binary Distributions
If your server is running on a version of Linux that installs software through the RPM package format (where RPM originally stood for RedHat Package Manager) or the DEB package format (where DEB stands for Debian Linux), it is recommended that you use a binary package instead of a source distribution. Linux binaries are provided based on a few different Linux distributions: various versions of Red Hat, Debian, SuSE Linux. For all other distributions of Linux, there are generic Linux packages for installing MySQL. There are also different versions of a distribution related to the type of processor used by the server (e.g., 32-bit or 64-bit).
Before proceeding, though, if you have the original installation disks for Linux, you may be able to use its installation program to easily install MySQL from the disks. In this case, you can skip the remainder of this section and proceed to Post-Installation. If your installation disks are old, though, they may not have the latest version of MySQL. So you may want to install MySQL using the method described in the following paragraphs.
For each version of MySQL, there are a few binary installation packages that you can download: the MySQL Server, the Shared Components, the Compatibility Libraries, Client Utilities, Embedded, and the Test Suite. The most important ones are the Server, the Client Utilities, and the Shared Components. In addition to these main packages, you may also want to install the one named Shared Libraries. It provides the files necessary for interacting with MySQL from programming languages such as PHP, Perl, and C. The other packages are for advanced or special needs that won’t be discussed in this book and that you may not need to learn until you’re a more advanced MySQL developer.
The naming scheme for these packages is generally MySQL-server-version.rpm, MySQL-client-version.rpm and MySQL-shared-version.rpm, where version is the actual version number. The corresponding package names for Debian-based distributions end in .deb instead of .rpm.
To install .rpm files after downloading them to your server, you can use the rpm utility or something more comprehensive like yum. yum is better about making sure you’re not installing software that conflicts with other things on your server. It also upgrades and installs anything that might be missing on your server. In addition, it can be used to upgrade MySQL for newer editions as they become available. On Debian-based systems, apt-get is similar to yum. For MySQL, Oracle provides a yum repository and an apt repository. For MariaDB, there is a repository configuration toolfor each operating system.
To install the binary installation files for MySQL using yum, you would enter something like the following from the command line on the server:
yum install MySQL-server-version.rpm \
MySQL-client-version.rpm MySQL-shared-version.rpm
You would, of course, modify the names of the RPM or DEB files to the precise name of the packages you want to install. The yum utility will take you through the installation steps, asking you to confirm the installation, any removals of conflicting software, and any upgrades needed. Unless the server is a critical one for use in business, you can probably agree to let it do what it wants.
To install the binary installation files for MariaDB using yum, you would enter something like the following from the command line on the server:
yum install MariaDB-server MariaDB-client
To install MySQL or MariaDB using the rpm utility, enter something like the following from the command line in the directory where the RPM files are located:
rpm -ivh MySQL-server-version.rpm \
MySQL-client-version.rpm MySQL-shared-version.rpm
If an earlier version of MySQL is already installed on the server, you will receive an error message stating this problem, and the installation will be canceled. If you want to upgrade an existing installation, you can replace the -i option in the example with an upper case -U like so:
rpm -Uvh MySQL-server-version.rpm
MySQL-client-version.rpm MySQL-shared-version.rpm
When the RPM files are installed, the mysqld daemon will be started or restarted automatically. Once MySQL is installed and running, you need to make some post-installation adjustments, as explained in Post-Installation. So skip ahead to it.
Mac OS X Distributions
Recent versions of Mac OS X no longer come with MySQL installed, but previous ones did — they stopped shipping it after Oracle took over MySQL. If your computer started with an older version, it may already be installed, but not running. To see if you have MySQL installed on your system, open the Terminal application (located in Applications/Utilities). Once you have a command prompt, enter the first line shown here (the results you should see are on lines 2–4):
whereis mysql mysqld mysqld_safe
/usr/bin/mysql
/usr/bin/mysqld
/usr/bin/mysqld_safe
If you get the results just shown, MySQL is installed on your computer. Check now whether the MySQL daemon (mysqld) is running. Enter the following from the command line:
ps aux | grep mysql
If it shows that mysqld is running, you don’t need to install it, but skip instead to Post-Installation.
If the daemon is present on your system but not running, enter the following from the command line as root to start it:
/usr/bin/mysqld_safe &
If MySQL is not installed on your Mac system or you want to upgrade your copy of MySQL by installing the latest release, directions are included in the remainder of this section. If MySQL isn’t already installed on your system, you may need to create a system user named mysql before installing MySQL. Oracle’s MySQL package automatically creates a user called _mysql.
Binary file packages (DMG files) are available for installing MySQL. For Mac servers that do not have a GUI or a desktop manager, or for when you want to install it remotely, there are TAR files for installing MySQL.[3] Whether you will be downloading a DMG file or a TAR file, be sure to download the package related to the type of processor on your server (e.g., 32-bit or 64-bit), and for the minimum version of the server’s operating system (e.g., Mac OS X, version 10.6 or higher).
If an older version of MySQL is already installed on your server, you will need to shut down the MySQL service before installing and running the newer version or replacing it with MariaDB. You can do this with the MySQL Manager Application, which is a GUI application that was probably installed when the operating system was first installed along with MySQL. It’s typically installed on recent versions of Mac OS X by default. If your server doesn’t have the MySQL Manager Application, you can enter the following from the command line to shut down the MySQL service:
/usr/sbin/mysqladmin -u root -p shutdown
If you’ve never used MySQL and didn’t set the password, it’s probably blank. When you’re prompted for it after entering the preceding command, just press the Enter key.
To install the MySQL package file, from the Finder desktop manager, double-click on the disk image file (the DMG file) that you downloaded. This will reveal the disk image file’s contents. Look for the PKG files; there will be two. Double-click on the one named mysql-version.pkg (e.g.,mysql-5.5.29-osx10.6-x86.pkg). This will begin the installation program. The installer will take you through the installation steps from there. The default settings are recommended for most users and developers.
To have MySQL started at boot time, add a startup item. Within the disk image file that you downloaded, you should see an icon labeled MySQLStartupItem.pkg. Just double-click it, and it will create a startup item for MySQL. You should also install the MySQL preferences pane so that you can start and stop MySQL easily from Systems Preferences in the Mac system, as well as set it to start automatically at start up time. To do this, click on the icon labeled MySQL.prefPane. If you have problems using the installer, read the ReadMe.txt file included in the DMG image file.
There is not yet an official installer for MariaDB on a Mac machine. However, you can use homebrew to download and install the needed packages, including required libraries. The homebrew utility works much like yum does on Linux systems, but is made for Mac OS X. After you installhomebrew, you can run the following from the command line to install MariaDB:
brew install mariadb
To install MySQL with the TAR package instead of the DMG package, download the TAR file from Oracle’s site and move it to the /usr/local directory, then change to that directory. Next, untar and unzip the installation program like so:
cd /usr/local
tar xvfz mysql-version.tar.gz
Change the name of the installation package in the example to the actual name. From here, create a symbolic link for the installation directory, and then run the configuration program. Here is an example of how you might do this:
ln -s /usr/local/mysql-version /usr/local/mysql
cd /usr/local/mysql
./configure --prefix=/usr/local/mysql \
--with-unix-socket-path=/usr/local/mysql/mysql_socket \
--with-mysqld-user=mysql
The first line creates the symbolic link to give MySQL a universal location regardless of future versions; change version to the actual version number. By making a symbolic link to a generic directory of /usr/local/mysql, you’ll always know where to find MySQL when you need it. You could also just rename the directory with the version name to just mysql. But then you can’t test new versions and keep old versions when upgrading.
With the second line, you enter the directory where the installation files are now located. The third line runs the configuration program to install MySQL. I’ve included a few options that I think will be useful for solving some problems in advance. Depending on your needs, you might provide more options than these few. However, for most beginners, these should be enough.
Next, you should set who owns the files and directories created, and which group has rights to them. Set both the user and group to mysql, which should have been created by the installation program. For some systems, you may have to enable permissions for the hard drive or volume first. To do that, use the vsdbutil utility. If you want to check whether permissions are enabled on the volume first, use the -c option; to just enable it, use -a option for vsdbutil. You should also make a symbolic link from the /usr/bin directory to the mysql and mysqladmin clients:
vsdbutil -a /Volumes/Macintosh\ HD/
sudo chown -R _mysql /usr/local/mysql/.
alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin
The first line of this example enables the main drive of the Mac machine. The name of the drive on which you locate MySQL may be different on your server. The second line changes the owner to the user mysql. The last two lines create aliases for the two key MySQL clients mentioned earlier so that you can run them from anywhere on your system.
At this point, you should be able to start the daemon and log into MySQL or MariaDB. If you installed the preference pane for MySQL with the installer, you can go to the Systems Preference of the operating system and start it there instead:
sudo /usr/bin/mysqld_safe &
mysql -u root -p
Depending on the release of MySQL, the file path for a dmg installation may be different from what is shown in the first line here. An ampersand (&) sends the process to the background. The second line will start the mysql client and let you log in as root, the MySQL user who is in control of the whole server — MySQL users are different from operating system users, so the root user is also different even though the name is the same. The command will prompt you for a password, which will probably be blank. So you can just press Enter for the password and you’ll be in.
Success here simply shows that you can connect to the MySQL or MariaDB server and that you have correctly added the symbolic links for the mysql client. There’s more to do before you start trying MySQL. So type exit and press Enter to exit the mysql client.
Now that MySQL or MariaDB is installed and running, you need to make some post-installation adjustments, as explained in Post-Installation. Skip ahead to that section.
Windows Distributions
Installing MySQL or MariaDB on a server using Microsoft Windows is fairly easy. MySQL’s website now provides one installation package for everything, offering different methods and versions to meet your needs and preference. The MariaDB Foundation’s website provides installation packages for installing MariaDB on servers using Windows. The easiest and best choice for installing MySQL is to download and use the MySQL Installer for Windows. It’s a single file that does everything for you. There are also older versions still available that may be downloaded in a TAR file, but the new installer is easier and will give you the latest version. For both the installer packages and the TAR packages, there are 32-bit and 64-bit versions, which you would choose based on which kind of processor is in your server.
Both the installer and TAR packages contain the essential files for running MySQL or MariaDB, including all of the command-line utilities covered in this book (e.g., mysql, mysqladmin, mysqlbackup), some useful scripts for handling special needs, and the libraries for APIs. They also contain the /usr/local/mysql/docs directory for the version that you download.
If you decide to use the TAR package for Windows, because it does not include an installer to handle everything for you, you will have to do a few things manually at the beginning. First, you will need to unzip the TAR file to get at the installation files. To do this, you need WinZip or anotherutility that you might have installed on your server to uncompress the files. These files need to be copied into the c:\mysql directory. You’ll have to create that directory if it does not already exist on your server. Then, using a plain-text editor (e.g., Notepad) you must create a configuration file that is generally called my.ini in the c:\windows directory. Several examples of this configuration file are provided with the distribution package. Once you have the files in the appropriate place, you can run the setup program. It does provide some assistance, but not as much as the installer.
Before running the installer or the setup program, if MySQL is already installed and running on your server, and you want to install a newer version, you will first need to shut down the one that’s currently running on your server. For server versions of Windows, it’s generally installed as a service. You can enter something like the following within a command window to shut down the service and remove it:
mysqld -remove
If MySQL is running on your server, but not as a service, you can enter the following within a command window to shut it down:
msyqladmin -u root -p shutdown
If that returns an error message, you may have to figure out the absolute path for mysqladmin. Try entering something like the following, adjusting the file path to wherever mysqladmin is located:
"C:\Program Data\MySQL\MySQL Server 5.1\bin\mysqladmin" -u root -p shutdown
After you download the MySQL Installer for Windows from the Windows desktop, double-click on the file’s icon and the Windows Installer program will start. If you’re installing from a ZIP package, look for the file named setup.exe wherever you put the MySQL installation files. Double-click on it to start the installation. From this point, the installation process is pretty much the same for both types of packages.
After you’ve started the installation, once you get past the licensing question and so forth, you will be given a few choices of which type of installation. The Developer choice is the recommended one. However, it will not install the files need for an API, or some other utilities. It will install the MySQL server, libraries, and several MySQL clients on your computer. This is probably the best choice. However, if you’re installing the software on a server and you will be connecting to it from a different computer such as your deskop, you could select “Server only” to install the MySQL server on your server. If you do so, run the installer on your desktop machine and select “Client only” to install only the MySQL clients locally. The MySQL files aren’t very large, though. You could also install the “Server only” on your server and the Developer package on your desktop. This would allow you to use your desktop as a development environment to learn and test a database before uploading it to your server and making it active. Choose the packages and combinations that work best for you. Just be sure to have both the MySQL server and the MySQL clients installed somewhere that you can access them.
On the same screen where you choose the setup type, there will be two boxes for file paths: one where you install the utilities and the other where MySQL stores your data. You can accept the default paths for these or change them, if you want to use a different hard drive or location. The default settings are usually fine. Just make a copy of the paths somewhere, because you may want to know this information later. You can find it later in the configuration file for MySQL, but while it’s handy now, copy it down: it might save you some time later.
Next, the installer will check whether your computer has the required additional files, besides the MySQL package. Allow it to install whatever files it says you need. For the TAR package, you will have to decide which directory to use and put the files where you want them. A typical choice is C:\Program Data\MySQL\ for the installation path, and C:\Program Data\MySQL\MySQL Server version\data\ for the data path, where the word version is replaced with the version number.
The last section before the installer finishes is the Configuration screen, where you can set some configuration options. If you want to set options, you can check the box labeled Advanced Configuration, but because you’re still learning about MySQL, you should leave this unchecked and accept the basic default settings for now. You can change the server settings later.
If you’re installing the MySQL server on this machine and not just the clients, you will see a “Start the MySQL Server at System Startup” checkbox. It is a good idea to check that box. In the Configuration section, you can also enter the password for the MySQL root user. Enter a secure password and don’t forget it. You can also add another user. We’ll cover that in Post-Installation. But if you want to make that process easier, you can add a user here for yourself — but I recommend waiting and using MySQL to add users, so you learn that important skill. As for the rest of the choices that the installer gives you, you can probably accept the default settings.
In this book, you will be working and learning from the command line, so you will need to have easy access to the MySQL clients that work from the command line. To invoke the command-line utilities without having to enter the file path to the directory containing them, enter the following from the command line, from any directory:
PATH=%PATH%;C:\Program Data\MySQL\MySQL Server version\bin
export PATH
Replace the word version with the version number and make sure to enter the actual path where MySQL is installed. If you changed the location when you installed MySQL, you need to use the path that you named. The line just shown will let you start the client by entering simply mysql andnot something like, C:\Program Data\MySQL\MySQL Server version\bin\mysql each time. For some Windows systems, you may need to change the start of the path to C:\Program Files\. You’ll have to search your system to see where the binary files for MySQL were installed — look for the bin\ subdirectory. Any command windows you may already have open won’t get the new path. So be sure to close them and open a new command window.
Once you’ve finished installing MySQL and you’ve set up the configuration file, the installer will start the MySQL server automatically. If you’ve installed MySQL manually without an installer, enter something like the following from a command window:
mysqld --install
net start mysql
Now that MySQL is installed and running, you need to make some post-installation adjustments, as explained in Post-Installation. So jump ahead to the last couple of pages of this chapter.
FreeBSD and Sun Solaris Distributions
Installing MySQL or MariaDB with a binary distribution is easier than using a source distribution. If a binary distribution is available for your platform, it’s the recommended choice. For Sun Solaris distributions, there are PKG files for MySQL on Oracle’s site and PKG files for MariaDB on the MariaDB Foundation’s site. For MySQL, you will have to decide between 32-bit, 64-bit, and SPARC versions, depending on the type of processor used on your server. For MariaDB, there is only a 64-bit version.
There are also TAR files, combining the MySQL files. The FreeBSD files are available only in TAR packages and only for MySQL. For MariaDB, you will have to compile the source files. If you download the TAR files, you will need a copy of GNU’s tar and GNU’s gunzip to unpack the installation files. These tools are usually included on Sun Solaris and FreeBSD systems. If your system doesn’t have them, though, you can download them from the GNU Foundation site.
Once you’ve chosen and downloaded an installation package, enter something like the following from the command line as root to begin the installation process:
groupadd mysql
useradd -g mysql mysql
cd /usr/local
tar xvfz /tmp/mysql-version.tar.gz
These commands are the same for both MySQL and MariaDB. The first command creates the user group, mysql. The second creates the user, mysql, and adds it to the mysql group at the same time. The next command changes to the directory where the MySQL files are about to be extracted. The last line uses the tar utility (along with gunzip via the z option) to unzip and extract the distribution files. The word version in the name of the installation file should be replaced with the version number — that is to say, use the actual file path and name of the installation file that you downloaded as the second argument of the tar command. For Sun Solaris systems, you should use gtar instead of tar.
After running the previous commands, you need to create a symbolic link to the directory created by tar in /usr/local:
ln -s /usr/local/mysql-version /usr/local/mysql
This creates /usr/local/mysql as a link to /usr/local/mysql-version, where mysql-version is the actual name of the subdirectory that tar created in /usr/local. The link is necessary, because MySQL is expecting the software to be located in /usr/local/mysql and the data to be in/usr/local/mysql/data by default.
At this point, MySQL or MariaDB is basically installed. Now you must generate the initial user privileges or grant tables, and change the file ownership of the related programs and data files. To do these tasks, enter the following from the command line:
cd /usr/local/mysql
./scripts/mysql_install_db
chown -R mysql /usr/local/mysql
chgrp -R mysql /usr/local/mysql
The first command changes to the directory containing MySQL’s files. The second line uses a script provided with the distribution to generate the initial privileges or grant tables, which consist of the mysql database with MySQL’s superuser, root. This is the same for MariaDB. The third line changes the ownership of the MySQL directories and programs to the filesystem user, mysql. The last line changes the group owner of the same directory and files to the user, mysql.
With the programs installed and their ownerships set properly, you can start MySQL. This can be done in several ways. To make sure that the daemon is restarted in the event that it crashes, enter the following from the command line:
/usr/local/mysql/bin/mysqld_safe &
The mysqld_safe daemon, started by this command, will in turn start the MySQL server daemon, mysqld. If the mysqld daemon crashes, mysqld_safe will restart it. The ampersand at the end of the line instructs the shell to run the command in the background. This way you can exit the server and it will continue to run without you staying connected.
To have MySQL or MariaDB start at boot time, copy the mysql.server file located in the support-files subdirectory of /usr/local/mysql to the /etc/init.d directory. To do this, enter the following from the command line:
cp support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql
The first line follows a convention of placing the start up file for the server in the server’s initial daemons directory with the name, mysql. The second line makes the file executable. The third sets the run level of the service for startup and shutdown.
Now that MySQL or MariaDB is installed and running, you need to make some post-installation adjustments, as explained in Post-Installation.
Source Distributions
Although a binary distribution of MySQL and MariaDB is recommended, sometimes you may want to use a source distribution, either because binaries are not available for your server’s operating system, or because you have some special requirements that require customizing the installation. The steps for installing the source files of MySQL or MariaDB on all Unix types of operating systems are basically the same. This includes Linux, FreeBSD, and Sun Solaris. These steps are explained in this section.
To install a source distribution, you will need copies of GNU gunzip, GNU tar, GNU gcc (at least Version 2.95.2), and GNU make. These tools are usually included in Linux systems and most Unix systems. If your system doesn’t have them, you can download them from the GNU Foundation site.
Once you’ve chosen and downloaded the source distribution files for MySQL or MariaDB, enter the following commands as root from the directory where you want the source files stored:
groupadd mysql
useradd -g mysql mysql
tar xvfz /tmp/mysql-version.tar.gz
cd mysql-version
These commands are the same for installing MariaDB, except that the name of the installation package file will be something like mariadb-5.5.35.tar.gz and the name of the directory created when expanding the TAR file will be different. The first line creates the filesystem user group, mysql. The second creates the system user, mysql, and adds it to the mysql group at the same time. The next command uses the tar utility (along with gunzip via the z option) to unzip and extract the source distribution file you downloaded. Replace the word version with the version number. Use the actual file path and name of the installation file that you downloaded for the second argument of the tar command. The last command changes the directory to the one created by tar in the previous line. That directory contains the files needed to configure MySQL.
This brings you to the next step, which is to configure the source files to prepare them for building the binary programs. This is where you can add any special build requirements you may have. For instance, if you want to change the default directory from where MySQL or MariaDB is installed, use the --prefix option with a value set to equal the desired directory. To set the Unix socket file’s path, use --with-unix-socket-path. If you would like to use a different character set from the default of latin1, use --with-charset and name the character set you want as the default. Here is an example of how you might configure MySQL with these particular options before building the binary files:
./configure --prefix=/usr/local/mysql \
--with-unix-socket-path=/tmp \
--with-charset=latin2
You can enter this command on one line without the backslashes. Several other configuration options are available. To get a complete and current listing of options permitted with the installation package you downloaded, enter the following from the command line:
./configure --help
You may also want to look at the latest online documentation for compiling MySQL.
Once you’ve decided on any options that you want, run the configure script with those options. It will take quite a while to run, and it will display a great amount of information, which you can ignore usually if it ends successfully. After the configure script finishes, the binaries will need to be built and MySQL needs to be initialized. To do this, enter the following:
make
make install
cd /usr/local/mysql
./scripts/mysql_install_db
The first line here builds the binary programs. There may be plenty of text displayed after that line and the next one, but I omitted that output to save space. If the command is successful, you need to enter the second line to install the binary programs and related files in the appropriate directories. The third line changes to the directory where MySQL was installed. If you configured MySQL to be installed in a different directory, you’ll have to use that directory path instead. The last command uses a script provided with the distribution to generate the initial user privileges or grant tables.
All that remains is to change the ownership of the MySQL programs and directories. You can do this by entering the following:
chown -R mysql /usr/local/mysql
chgrp -R mysql /usr/local/mysql
The first line here changes ownership of the MySQL directories and programs to the filesystem user, mysql. The second line changes the group owner of the same directories and files to the group mysql. These file paths may be different depending on the version of MySQL you installed and whether you configured MySQL for different paths.
With the programs installed and their file ownerships set properly, you can start the daemon. You can do this in several ways. To make sure that the daemon is restarted in the event that it crashes, enter the following from the command line:
/usr/local/mysql/bin/mysqld_safe &
This method is the same for both MySQL and MariaDB, and it starts the mysqld_safe daemon, which will in turn start the server daemon, mysqld. If the mysqld daemon crashes, mysqld_safe will restart it. The ampersand at the end of the line instructs the shell to run the daemon in the background. This way you can exit the server and it will continue to run without you staying connected.
To have MySQL or MariaDB started at boot time, copy the mysql.server file, located in the support-files subdirectory of /usr/local/mysql, to the /etc/init.d directory. To do this, enter the following from the command line:
cp support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql
The first line follows a convention of placing the startup file for the server in the server’s initial daemons directory with the name, mysql. The second command makes the file executable. The third sets the run level of the service for startup and shutdown. All of this is the same for MariaDB.
At this point, MySQL or MariaDB is installed and running. All that remains now are some post-installation adjustments, as explained in the next section.
Post-Installation
After you’ve finished installing MySQL or MariaDB on your server, you should perform a few tasks before allowing others to begin using the service. You may want to change the server’s default behavior by making changes to the configuration file. At a minimum, you should change the password for the database administrator, root, and add some nonadministrative users. Some versions of MySQL have some anonymous users initially, and you should delete them. This section will explain these tasks.
Although the creators of MySQL and MariaDB have set the server daemon to the recommended configuration, you may want to change one or more settings. For instance, you may want to turn on error logging.
Special Configuration
To enable error logging and other such settings, you will need to edit the main configuration file for MySQL. On Unix-like systems, this file is /etc/my.cnf. On Windows systems, the main configuration file is usually either c:\windows\my.ini or c:\my.cnf. The configuration file is a text file that you can edit with a plain-text editor — don’t use a word processor, as it will introduce hidden binary characters that will cause problems.
The configuration file is organized into sections or groups under a heading name contained within square brackets. For instance, settings for the server daemon, mysqld, are listed under the group heading, [mysqld]. Under this heading you could add something like log = /var/log/mysql to enable logging and to set the directory for the log files. You can list many options in the file for a particular group. Here is an example of how a server configuration file might look:
[mysqld]
datadir=/data/mysql
user=mysql
default-character-set=utf8
log-bin=/data/mysql/logs/binary_log
max_allowed_packet=512M
[mysqld_safe]
ulimit -d 256000
ledir=/usr/sbin
mysqld=mysqld
log-error=/var/log/mysqld.log
pid-file=/data/mysql/mysqld.pid
[mysql.client]
default-character-set=utf8
As a beginner, you probably won’t need to make any changes to the server’s configuration file. For now, just know that the configuration file exists, where it’s located on your server, and how to change settings. What is necessary is to set the password for the MySQL user, root. It’s initially blank.
Setting Initial Password for root
You can change the password for the root user in MySQL in a few ways. One way is to use the administration utility, mysqladmin. Enter the following from the command line:
mysqladmin -u root -p flush-privileges password "new_pwd"
Replace the word new_pwd in quotes with a strong password that you want to use for root. If you get a message saying something like, mysqladmin command is not found, it may be because you didn’t make a symbolic link to the MySQL directory where mysqladmin is located or you haven’t added it to your command path. See the instructions for the distribution you installed on how to do one or the other. For now, you can just add the file path to the preceding line and re-enter it. On Linux and other Unix like systems, try running the command as/usr/local/mysql/bin/mysqladmin. On a Windows system, try c:\mysql\bin\mysqladmin.
If you’re working on a networked server, though, it’s better not to enter a password in this way. Someone might be looking over your shoulder or may find it in the server logs later. As of version 5.5.3 of MySQL, you can and should enter it like this:
mysqladmin -u root -p flush-privileges password
After entering this line, you will be prompted for the old password, which will be initially blank, so press the Enter key. Then you will be prompted to enter the new password twice. By this method, the password you enter won’t be displayed on the screen as you type it. If everything was installed properly and if the mysqld daemon is running, you should not get any message in response.
The MySQL user root is completely different from the operating system’s root user, even though it has the same name. It is meaningful only within MySQL or MariaDB. Throughout this book, I will be referring to this MySQL user by default when I use the term root. On the rare occasion where I have to refer to the operating system root user, I will explain that.
More on Passwords and Removing Anonymous Users
Privileges in MySQL are set based on a combination of the user’s name and the user’s host. For instance, the user root is allowed to do everything from the localhost, but very little or nothing from a remote location. This is for security. Therefore, there may be more than one username/host combination for root. Using mysqladmin, you changed the password for root on the localhost, as you would have executed it while logged into the server where MySQL is located locally. Now you should set the password for all of the username/host combinations for root. To get a list of username and host combinations on the server, execute the following from the command line:
mysql -u root -p -e "SELECT User,Host FROM mysql.user;"
+------+-----------------------+
| User | Host |
+------+-----------------------+
| root | 127.0.0.1 |
| root | localhost |
| root | % |
| | localhost |
+------+-----------------------+
If this didn’t work for you, it may be that you don’t have the mysql client in your command path. You may have to preface mysql with /bin/ or /usr/bin/, or the path for wherever the binary files for MySQL are installed. The command will be the same for MariaDB. The results here are contrived. It’s unlikely you will see exactly these results. But there are versions of MySQL whose host for root is %, which is a wildcard meaning any host. This is not good for security, because it allows anybody to claim to be root and to gain access from any location. And there have been versions of MySQL in which the username is left blank, meaning that any username from the localhost is accepted. This is an anonymous user. All of the users you will see in the results, though, will initially have no password. You should delete any unnecessary users and set passwords for those that you want to keep. Although 127.0.0.1 and localhost translate to the same host, the password should be changed for both. To change the root user’s password for the first two entries shown in the previous example and to delete the second two user/host combinations shown, you would enter the following at the command prompt:
mysql -u root -p -e "SET PASSWORD FOR 'root'@'127.0.0.1' PASSWORD('new_pwd');"
mysql -u root -p -e "SET PASSWORD FOR 'root'@'localhost' PASSWORD('new_pwd');"
mysql -u root -p -e "DROP USER 'root'@'%';"
mysql -u root -p -e "DROP USER ''@'localhost';"
When you’ve finished making changes to the initial batch of users, you should flush the user privileges so that the new passwords will take effect. Enter the following from the command line:
mysqladmin -u root -p flush-privileges
From this point on, you’ll have to use the new password for the user, root.
Creating a User
The next step regarding users is to create at least one user for general use. It’s best not to use the root user for general database management. To create another user, enter commands like:
mysql -u root -p -e "GRANT USAGE ON *.*
TO 'russell'@'localhost'
IDENTIFIED BY 'Rover#My_1st_Dog&Not_Yours!';"
These lines create the user russell and allow him to access MySQL from the localhost. The *.* means all databases and all tables. We’ll cover this in more depth later in the book. The statement also sets his password as Rover#My_1st_Dog&Not_Yours!.
This user has no privileges, actually: he can’t even view the databases, much less enter data. When you set up a new user, you should consider which privileges to allow the user. If you want her to be able only to view data, enter something like the following from the command line:
mysql -u root -p -e "GRANT SELECT ON *.* TO 'russell'@'localhost';"
In this line, the user russell may use only the SELECT statement, a command for viewing data. If you would like to see the privileges granted to a user, you could enter something like this from the command line:
mysql -u root -p -e "SHOW GRANTS FOR 'russell@'localhost' \G"
*************************** 1. row ***************************
Grants for russell@localhost:
GRANT SELECT ON *.* TO 'russell'@'localhost'
IDENTIFIED BY PASSWORD '*B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1'
These results show that the user is granted only privileges to use the SELECT statement for viewing data. We’ll cover this in more depth later in the book. Notice that the password is returned encrypted. There’s no way to retrieve someone’s password unencrypted from MySQL.
The user in the previous example, russell on localhost, cannot add, change, or delete data. If you want to give a user more than viewing privileges, you should add additional privileges to the SELECT command, separated by commas. That is covered in Chapter 13. For now, to give a user all privileges, replace SELECT with ALL. Here’s another example using the ALL setting:
mysql -u root -p -e "GRANT ALL ON *.* TO 'russell'@'localhost';"
The user in this example, russell on localhost, has all basic privileges. So that you can experiment while reading this book, you should create a user with full privileges, but use a name other than mine, something that better suits you.
With the MySQL or MariaDB installation software downloaded and installed, all of the binary files and minimal data in place and properly set, and a full privileged user created, the database system is now ready to use and you can begin learning how to use it.
[2] A daemon is a background process that runs continuously; a Unix term for what most people call a “server.”
[3] tar is an archive tool developed on Unix, but its format is understood by many archiving tools on many operating systems.