loading...

MySQL – MariaDB – Installing MySQL and MariaDB

How to Setup a Node.js Application Using PM2

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. The mysqld 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 tool for 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 install homebrew, 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 another
utility 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 and not 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.

Comments are closed.

loading...