MySQL Installation manual (binary & source)

05 December 2018 Hits: 2297

In this article, we will step you through the basics of installing MySQL. Before you install, you need to decide whether to install from source or binary distribution, whether you need transactional table support, and whether you will use the stable or the development version. We will discuss the issues associated with making those decisions. Afterward, we will provide a sequence of steps for different methods of installation, followed by brief troubleshooting guidelines.

Table of contents[Show]

Method of Installation

There are basically two methods for installing MySQL. The first is to download a binary supplied by MySQL AB; the second is to compile your own binary from the provided source. If you do not want to struggle for too long with this decision and want a quick approach, try the binary first and worry about the source only if the binary does not work on your system or is not available.

These two methods of installation have both advantages and disadvantages. MySQL AB currently provides binaries for the following platforms:

Linux (x86, Alpha, SPARC, and IA64)
FreeBSD
Windows
SPARC Solaris - HP-UX
Mac OS
AIX
Irix
Dec OSF

This list of platforms may change somewhat from time to time, but you can always count on having current x86 Linux, SPARC Solaris, Windows, and FreeBSD binaries available.

MySQL AB’s goal is to build binaries with the maximum degree of portability, performance, and stability. Each binary is compiled with a special set of compiler flags, and in the case of x86, Linux is linked against specially patched system libraries to achieve that goal. Although MySQL AB always tries its best when building a binary, it is important to understand that the degree of expertise varies from platform to platform, which in turn will affect the quality of the binary.

Therefore, although on some platforms it might be worthwhile to try to build your own binary that will run better than the one supplied by MySQL AB, on others it is not likely that even an expert user will succeed in doing so. I recommend using MySQL AB’s binaries on x86 Linux, FreeBSD, Solaris, and Windows unless you have a good reason to build your own (such as a need to extend the server).

In some instances, you may be restricted to building from source. The most obvious case is when the binary for your platform is not available, but you may also need to build from source when the system libraries are not compatible with the MySQL AB binary. This happens quite often on commercial Unix systems such as Solaris and HP-UX. And of course, if you want to extend MySQL server, you will need the source code.

Even when suitable binaries are available, some people prefer to install from source, for several reasons. Some like the security of knowing that if something goes wrong with MySQL, they have the source they can fix. Some want to experiment with different compilers and/or compiler options. For others, it’s a matter of principle: I know many system administrators who install everything from source, partially so that they know exactly what is being installed on their system, and partially for the sense of satisfaction from knowing that they have personally compiled every package on their system.

The Need for Transactional Table Support

If you are in a hurry to get MySQL up and running and do not want to spend too much time thinking about whether you will need transactional tables, assume you will, make the decision to install MySQL-Max, and skip the rest of this section.

One unique feature of MySQL is the support for multiple table handlers or types. A table handler can be described in simple terms as the low-level data and index storage/retrieval implementation. In other words, the MySQL query optimizer abstracts itself from the low-level storage and retrieval and makes calls to the table handler routines when such operations are required, leaving it up to them to do the “dirty job.” This design naturally allows for hooks to get the “dirty job” done in several different ways. The hook - in other words, the table handler - takes full control of all operations associated with the low-level storage and retrieval of data, and is free to implement them in a variety of ways.

Currently, the binaries from MySQL AB support the following table handlers: MyISAM, ISAM, HEAP, MERGE, INNODB, and BDB. MySQL AB supplies two kinds of binaries: regular and Max. Support for BDB and INNODB is included only in Max; otherwise, both binaries support every other table handler. Therefore, it may become important to know prior to the installation if the benefits of having support for BDB and INNODB in the binary are worth the overhead.

For most enterprise users, the answer will be yes. Unlike all other table types, BDB and INNODB tables have transactional capabilities. With a nontransactional table, if you perform an update the change is irreversible. With a transactional table, an update might be reversed (or rolled back) if you change your mind prior to committing. There is a good chance that eventually you will need that functionality in your application. The main disadvantage of having that capability supported in the binary when you are not using it is the increase in the memory footprint of the server somewhere in the order of a magnitude of 5MB, depending on the platform plus whatever you decide to allocate for the transaction-specific buffers, just in case you might decide to have a transactional table someday. On most modern systems, this overhead is not worth worrying about; however, there is no need to incur it if your data is updated very infrequently and the majority of your queries only read the data.

Version Issue

At any given time, two version branches of MySQL are available: stable and development. The stable branch contains more mature and tested code. The only modifications made in the stable branch are critical bug fixes. No major new features are added so as not to disrupt the core code. Communication protocols and storage formats do not change as the version updates are released.

The development branch keeps changing quite frequently. Portions of the core code are rewritten. Major new features are constantly added. Communication protocols change. Incompatibility with the old version protocols and formats may be introduced. Although development branch versions are tested with the same degree of rigor internally prior to a release, the lack of field testing of the new code means that there might be some subtle bugs in those areas.

The decision on which branch to use depends on whether you need the new features available only in the development branch bad enough to sacrifice the possible difference in stability. If you are a new user trying to learn a bit about MySQL, you should probably use the stable version. If you are planning an enterprise application and you already know that the stable branch can do everything you need, experimenting with the development branch may not be worth the risk. However, do not be afraid to try out the development branch if you need a certain feature, or if you simply would like to have the latest feature set at your fingertips. MySQL development branches have a history of being much more stable than the “release” version software of many commercial vendors.

As of this writing, the stable branch is 5.7.24. Some of the feature highlights of MySQL:

Query cache: greatly improves performance of the applications that run the same query over and over while the tables involved in the query do not change nearly as frequently as the query being run. See this.
Multi-table UPDATE and DELETE: allows you to delete or update records in several tables in one query that match the criteria of a join. This feature, for example, permits deleting records in one table that have a counterpart in some others in one query. See this and this.
UNION: allows you to combine the results of different select queries, thus avoiding the need for temporary tables in many cases. See this.
HANDLER: allows the application to have complete control of index traversal by issuing direct commands bypassing the optimizer. See this article.
A number of performance enhancements.
Rewrite of the replication slave to use a two-threaded model - one thread reading the updates from the master and storing them in a temporary relay log, while the other is applying them on the slave. This reduces the data loss to the minimum when the slave server is behind the master on updates and the master server goes down and never comes back.

Installation Process

By the time you get to this point, I assume you have already decided if you want to use the source or binary installation, whether you need transactions, and whether you will use the stable or the development branch. In the examples that follow, I assume that you are using the Max distribution, which supports transactions in all cases. If you do not want transactions at all, simply drop the -Max command out of all the examples.

I also assume that you are installing the current stable branch - 5.7. Visit download page and follow the appropriate links from there to get the correct distribution.

I would like to emphasize the importance of understanding the principle of how to install MySQL rather than simply following the installation instructions I provide here in hope that the guru magic will work. Although I have tried to ensure that the majority of users will be able to install painlessly by following these instructions, I realize that systems vary to a degree that makes it impossible to cover every potential issue. If you experience trouble during the installation, I recommend that you spend some time understanding the installation instructions and the concepts they are based on. For example, you may want to study Unix file system permissions, read the manual page on RPM, familiarize yourself with basic Unix commands, investigate the meaning of standard Unix error numbers and messages, or learn about Unix sockets. You may also find it beneficial to examine the source of the shell scripts referenced in the installation instructions to help you understand what is actually going on.

The time will be well spent. When you have a basic understanding of the installation process and your system, or if something out of the ordinary happens that I have not covered in the troubleshooting section, you still will be able to diagnose and solve it yourself. This will give you a fulfilling sense of competence and add a stripe to your sysadmin karate belt.

Binary Installation

Binary installation is essentially the same for all platforms, with the exceptions of Windows and Linux. On Linux, MySQL AB provides RPM packages in addition to the regular binary installation. RPM is the recommended installation method for systems that have RPM (e.g., RedHat, SuSE, Mandrake, and Caldera). If you have a Linux system without RPM (such as Debian), or if you are installing as a non-root user, you will need to use the standard binary distribution as opposed to RPM. On Windows, the installation process simply consists of unzipping the distribution archive and running the setup program. We first discuss the Linux RPM installation, then Windows, and then talk about the generic Unix installation.

Linux RPM

Follow these steps to install MySQL on any version of Linux with RPM:

Check to see if MySQL is already installed on your system. Some distributions, such as RedHat and SuSE, may preinstall MySQL if told to do so during system installation. There are several ways to check; one is ls -l /usr/sbin/mysqld. If this command shows that /usr/sbin/mysqld exists, then MySQL is already installed. If the file does not exist, although it is technically possible to install it in other locations, I do not know of a distribution using RPM that would. So you can quite safely assume that it is not installed.
If MySQL is already installed, you may consider uninstalling it and replacing it with the RPM from MySQL AB. This way, you get an updated version and the binary built for a high-load setup. Additionally, the preinstalled version may not have been built with transactional table support. The following should work on all RPM-based distributions for the preinstalled MySQL package removal:

rpm -qa | grep -i mysql | xargs rpm -e --nodeps

Alternatively, you may use rpm -qa | grep -i mysql and then remove each MySQL package manually one at a time, addressing the issue of dependencies as you go. However, you may decide that the version that is already installed is good enough for your needs and stop here.

Go to download page. Scroll down to RedHat packages, and click on the link that says Server.
Jump through the mirror selection hoops, picking a mirror that is closest to you, and download the rpm file - we assume you have saved it as /tmp/mysql-community-server-5.7.24-1.el7.x86_64.rpm on your Linux server.
Repeat the process and download at least the client and the development (libraries and headers) RPMs. You may also want to get the client shared libraries and the benchmark RPMs.
Run this command as root: rpm -i /tmp/mysql-community-server-5.7.24-1.el7.x86_64.rpm. This will install the MySQL package.
Run rpm -i for each additional package (client, development, shared libraries, and benchmarks) that you would like to install. You may skip benchmarks and shared libraries, but I strongly recommend you install client and development RPMs.
By now you will have the regular server running. Stop it with /etc/rc.d/init.d/mysql stop.
Execute /etc/rc.d/init.d/mysql start to start the Max binary. If you want to understand how this piece of magic operates, study /usr/bin/safe_mysqld.
Now MySQL server is installed and running

Windows

Here are installation instructions for Windows:

Go to download page.
Scroll down to the Windows section and download the zip file.
Create a temporary folder and extract the contents of the zip file into that folder.
Execute exe from the new folder.
MySQL server now should be installed and running.

Standard Unix Binary (Root User)

The following are installation instructions for installing MySQL as a root user on a standard Unix binary:

Go to download page.
Scroll down to your platform section.
Download the binary.
Check if you have GNU tar installed. You can verify this by typing gtar. If you get the “Command not found” message, it is either not installed or is not in your path, in which case you can run find / -name gtar -print and see if it will turn up something. (Be careful with the previous command if you are not the only user on the system - it might be very resource-intensive, and other users may not appreciate the slowdown.) If it is not installed, you either use the native system tar or you can get it from ftp://ftp.gnu.org/pub/gnu/tar/. GNU tar is recommended for all systems and required for Solaris.
Let’s suppose you have saved the distribution archive in /tmp/mysql.tar.gz. Proceed with cd /usr/local; gtar zxvf mysql.tar.gz (or if you do not have gtar, gunzip -c mysql-max.tar.gz I tar xvf -).
Run ln -s mysql-version mysql, replacing version with the MySQL version identifier; for example, ln -s mysql-community-server-5.7.24-1.el7.x86_64 mysql.
Run the following commands:

cd mysql .

groupadd mysql

useradd -g mysql mysql

scripts/mysql_install_db

chown -R root .

chown -R mysql data

chgrp -R mysql .

bin/mysqld_safe --user=mysql

Standard Unix Binary (Non-Root User)

Here are installation instructions for installing MySQL as a non-root user on a standard Unix binary:

Obtain the distribution for your platform as described in the root user installation section.
Check if you have gtar installed. See the Unix binary tar discussion for the tar issues.
Run gtar zxvf mysql.tar.gz or gunzip -c mysql.tar.gz.
Run ln -s mysql-version mysql, replacing version with the actual version string of the distribution.
Run the following commands:

cd mysql

scripts/mysql_install_db

bin/safe_mysqld --datadir=$HOME/mysql/data --

bin/mysqld_safe --datadir=$HOME/mysql/data --socket=$HOME/mysql/data/mysql.sock

Source Installation

In this section, we describe how to compile your own version of MySQL from source files.

Unix

Follow these steps for compiling MySQL source on a Unix platform.

Visit download page.
Scroll down to the very bottom and download the tarball distribution.
Make sure you have a C++ compiler installed. For most platforms, it is recommended that you install GCC version 2.95. There are platforms, though, where it is better to compile with gcc. However, the recommended version of the compiler may change as the newer GCC versions stabilize. Check the operating system notes for your OS at this for the most recent recommendation.
Install GNU make.
Assuming you have saved the downloaded file in mysql.tar.gz, you execute gunzip -c mysql.tar.gz | tar xvf -.
Run cd mysql-*.
Now the most important and most challenging part: to run the configure command that will examine your system and will prepare the source tree for the compilation. This step varies from platform to platform and some tweaking may be necessary. Usually the following works: CFLAGS=”-O3” CXXFLAGS=”-O3 -felide-constructors -fno-expections -fno-rtti” CXX=gcc ./configure --prefix=/usr/local/mysql --with-innodb . If you are using a different compiler than GCC, the flags would need to be different. Check the compiler manual to see how to disable exceptions and RTTI (run-time type information). If you have less than 128MB of RAM, you may need to add --with-low-memory argument to ./configure.
Run gmake (to make sure GNU make, not the native one, is executed) - this will take approximately 5 minutes on a dual Pentium 500 with 512MB of RAM.

Optionally, you can execute gmake test once make completes. Ideally, all tests should pass. However, if you are on a platform that MySQL has never been compiled on by the MySQL team, chances are some tests might fail. It is usually the replication tests that do, because they are more prone to be affected by various quirks in the TCP/IP implementation and in the thread library on a particular platform. The binary might still be usable for some purposes even if that happens, though.
If everything is fine up to this point, su to become root, and type make install.
Run cd /usr/local/mysql; bin/safe_mysqld &.

Windows

Windows installation is a little more straightforward, but for the purposes of completeness and for your convenience, I am providing the instructions found in the MySQL manual at this.

You will need the following:

C++ compiler. The Pre-processor package is necessary for the macro assembler.

The MySQL source distribution for Windows, which can be downloaded from download page.

To build MySQL, follow these steps:

Create a work directory (e.g., workdir).
Unpack the source distribution in the aforementioned directory.
Start the VC++ compiler.
In the File menu, select Open Workspace.
Open the mysql.dsw workspace you find on the work directory.
From the Build menu, select the Set Active Configuration menu.
Click over the screen selecting mysqld - Win32 Debug and click OK.
Press F7 to begin the build of the debug server, libs, and some client applications.
When the compilation finishes, copy the libs and the executables to a separate directory.
Compile the release versions that you want, in the same way.
Create the directory for the MySQL stuff: e.g., c:\mysql.
From the workdir directory copy for the c:\mysql directory the following directories:

Data
Docs
Share

Create the directory c:\mysql\bin and copy all the servers and clients that you compiled previously.
If you want, also create the lib directory and copy the libs that you compiled previously.
Do a clean using Visual Studio.

Basic Post-Installation Checks

Now we are entering the exciting part of the journey. We are going to see for ourselves if the installation process was successful. I personally find it very fulfilling to verify that an installation was successful even in simple cases, and especially when I have had some trouble down the road.

The first check is to see if you can connect with the command-line MySQL client. If you installed from RPM, it will be in /usr/bin/mysql, which is most likely in your path. If you installed as Unix root, execute PATH=/usr/local/ mysql/bin:$PATH; export PATH if you are in the Bourne shell, and setenv PATH /usr/local/mysql/bin:$PATH if you are using the C shell. If you installed as a nonroot Unix user, execute PATH=$HOME/mysql/bin; export PATH for the Bourne shell, and setenv PATH $HOME/mysql/bin:$PATH for the C shell. If you are not sure which shell you are running, or if your shell is neither Bourne nor C, try it both ways - one of them will work, and in the worst case the other will simply produce an error.

Once your path has been set, you can try to connect to the running MySQL server with the command-line client. If you have installed as a root user, either with RPM or with the non-RPM binary distribution, simply type mysql. If you have installed as non-root user, you need to give it a socket argument, so type mysql --socket=$HOME/mysql/data/mysql.sock. If everything works, you’ll see something like this:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2 to server version: 5.7.24-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Unfortunately, sometimes there are problems during MySQL installation, and you may get an error message instead. If you see one, read the troubleshooting section in this article.

To exit the command-line client, type quit or exit.

Post-Installation Setup

When moving into a new home or apartment, it does not quite feel like home until you unload the truck, arrange the furniture, unpack the boxes, and put your stuff where it belongs. In a similar way, after installing MySQL there are things to do to make your installation feel like home.

Just like installing a good lock would be a must to feel secure in your home, the very first thing to do with your newly installed MySQL is setting the root password. This can be done in one shell command:

mysqladmin -uroot password tops3cret

replacing tops3cret with the actual value of the password. If you type your intended root password incorrectly, or simply do not like your initial root password, you can change it in the following way:

mysqladmin -uroot -poldtops3cret password newtops3cret

Be sure to type your old password exactly the same way after -p as you did when you set the password initially.

An alternative way to set the root password is to connect to the server using the command-line client and execute a special command:

mysql -uroot

SET PASSWORD=PASSWORD('tops3cret');

The next move will be similar to securing your garage door. Access to the garage is not the same as access to the house, but you still do not want strangers walking in there. MySQL by default installs with an open garage door - a local test user with full privileges on the test database. While it makes things easier for the test and benchmark scripts, this is perhaps not what you want on your system. A malicious local user can cause a lot of problems even with this rather restricted test account. Let’s close this garage door by removing the test account. Connect to your MySQL server with mysql -uroot -ptopsScret and type

DELETE FROM mysql.user WHERE user='';

FLUSH PRIVILEGES;

Now it is time to create some users and allow them to operate on some databases. Although there are a multitude of ways to set up users and grant them privileges, there are some common user configuration patterns. Let’s talk about the most common ones and provide some examples.

Proxy Database Access

This configuration pattern applies when users are not given direct access to database tables. The only way data can be read from or written to the database is by going through some database client application that performs strict checks on user input before issuing any kind of query. This would be the case, for example, if you have a Web-based shopping cart with the MySQL backend. The users of the shopping cart do not even know that a database is involved at all. All interaction with MySQL occurs through the shopping cart Web application, which plays the role of a proxy or a mediator between the end user and the database.

Although ideally the proxy database application will ensure the integrity of the queries being sent to the database, the reality often differs from the ideal. Even the most thoroughly written, reviewed, and tested applications might have hidden security holes that will allow a malicious user to execute an arbitrary query in the database. Therefore, it is a good idea to give the proxy application the minimum rights it needs to do the job.

Just as there are many ways to arrange furniture in a room for any given furniture collection, and there are a large number of furniture collections to add to the variety, there are many possible ways to set up access for a proxy-style database application, and it would not be possible to cover the issue comprehensively. However, let’s consider a few commonly used examples.

Suppose the proxy-style database application runs on the same machine as the database server, operates only in one database called sales, and needs to be able to perform schema modifications, such as creating, dropping, or altering tables. We can solve the problem with the following command in the MySQL command-line client after connecting to MySQL as root (mysql -uroot -p):

CREATE DATABASE sales;

GRANT ALL ON sales.* TO 'salesuser'@'localhost' IDENTIFIED BY 'trades3cret';

This creates a database called sales; then creates a user salesuser that is allowed to connect from the local host and can perform any table operation as long as it happens in the sales database with trades3cret as the password.

Now let’s consider a more complex situation. Our application can be broken into two parts - one that operates on the data itself - inserting, selecting, deleting or updating rows - and the one that administers the schema, creating, dropping, or altering tables. We solve the problem by creating separate users for each one of the functions:

CREATE DATABASE sales;

GRANT SELECT,INSERT,DELETE,UPDATE ON sales.* TO

'salesuser'@'localhost' IDENTIFIED BY 'trades3cret';

GRANT CREATE,DROP,ALTER,INDEX ON sales.* TO 'salesadmin'@'localhost' IDENTIFIED BY 'admins3cret';

Now let’s consider a slightly more complex scenario. Everything is the same as in the example above, except we have a farm of servers that are running different instances of our application. Schema modification code runs only on server www1.mycompany.com, while the code that operates on data is on 10 servers named www1.mycomany.com through www10.mycomany.com. We proceed with the following:

CREATE DATABASE sales;

GRANT SELECT,INSERT,DELETE,UPDATE ON sales.* TO

'salesuser'@'www%.mycompany.com' IDENTIFIED BY 'trades3cret';

GRANT CREATE,DROP,ALTER,INDEX ON sales.* TO

'salesadmin'@'www1.mycompany.com' IDENTIFIED BY 'admins3cret';

The first GRANT actually accomplishes a bit more than what we wanted. It allows connections for salesuser from any host that resolves into something that starts with www and ends with .mycompany.com. If you do not take proper measures, this could be a security problem, although not a very big one. Without going into a deep discussion of security, placing the database server and your Web servers behind a firewall that blocks the MySQL port (3306 by default) for connections coming from the outside is sufficient to take care of it.

Hosting Provider

If you are running a hosting service and would like to provide access to MySQL for your users, a standard solution is to create a database for each user and grant that user full rights on it:

CREATE DATABASE username;

GRANT ALL ON username.* TO 'username'@'localhost' IDENTIFIED BY 's3cretk3y';

Single User

Some installations may require having only one user. For example, you may have installed MySQL on your desktop so that you can experiment with it, perhaps to do some data mining, or to run some application that needs a database and supports MySQL as one of the options. In this case, the following should take care of your user needs:

GRANT ALL ON *.* TO 'me'@'localhost' IDENTIFIED BY 's3cretpwd';

Direct Multiple-User Database Access

In some cases, you may have multiple users accessing the same data, and you would like to give them enough flexibility to execute arbitrary SQL queries. For example, you could have an employee roll. Suppose you want Larry to be able to have full rights in the employee database, but you want Kerry and Joe only to be able to select from it. Larry’s computer is called larry.mycompany.com; Kerry’s is called kerry.mycompany.com, and Joe’s is called joe.mycompany.com. You do not want them to be able to access MySQL from other computers. Here is how we can solve the problem:

CREATE DATABASE employee;

GRANT ALL ON employee.* TO 'larry'@'larry.mycompany.com';

GRANT SELECT ON employee.* TO 'kerry'@'kerry.mycompany.com';

GRANT SELECT ON employee.* TO 'joe'@'joe.mycompany.com';

I hope the previous examples have given you enough of a jump-start to decide how you want to initially configure the MySQL privilege system on your server and actually do it.

At some point in the future you might want to modify the default configuration parameters of your MySQL server. Therefore, we recommend you set up to be able to do so right away. Although MySQL server will look for a default configuration file in several locations, the most common one is /etc/my.cnf on Unix and C:\my.cnf on Windows. While it is possible to handcraft your own my.cnf file from scratch if you are up for a challenge, it is much easier to copy one of the sample distribution files into /etc/my.cnf or C:\my.cnf if you are on Windows, and either use it as is or edit it to make it more suitable for your needs. The sample my.cnf files are placed in /usr/doc/MySQL-version/ in the RPM distribution, /usr/local/mysql/support-files in the binary or source distribution on Unix, and in C:\mysql on Windows.

On a Unix system, to make your life a bit easier you might want to create a file in your home directory called .my.cnf (do not miss the . symbol at the beginning), and put the following into it:

[client]

user=user_you_normally_connect_as password= the_password_of_the_above_user

You should replace the placeholders with the actual values of user and password. This may seem obvious, but some very capable and intelligent people at

times take instructions too literally. It is very important to execute chmod 600 .my.cnf so that this file will not be readable to others since it contains your password. This will allow you to use the command-line client mysql and other MySQL command-line utilities without having to type your MySQL username and password if you are logged into your Unix shell account.

One thing many experienced users like to do after installing MySQL is to perform some simple set of operations to convince themselves that it actually works and to give themselves a sense of satisfaction of actually having accomplished something. Let’s try a few simple commands:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t1 (name char(20), phone char(15));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values ('Larry','123-4567'), ('Kerry','234-
5678'), ('Joe','345-6789');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+--------+---------+
| name   | phone   |
+--------+---------+
| Larry  | 123-4567|
| Kerry  | 234-5678|
| Joe    | 345-6789|
+--------+---------+
3 rows in set (0.00 sec)

It looks like our installation is actually working. However, suppose you encountered problems that you were not able to resolve. In this case, you need to read the upcoming troubleshooting section.

Troubleshooting

Unfortunately, things are not always as rosy as described in the previous section. Problems can arise. There are so many things that could go wrong with even such a simple process as installing MySQL that it is not possible to discuss all of them even if we were to dedicate the entire book to that topic. In this section, we cover some of the common problems, and then provide some guidance on what to do in case your problem is not listed.

mysqld ended

This message comes from the safe_mysqld script on Unix, which is a shell script wrapper around the server binary mysqld. The message means that there was an attempt to start mysqld but that attempt failed. There can be numerous reasons why. The key to diagnosing the problem is reading the error log of mysqld. The error log is located in the data directory and is called 'hostnameerr ( backticks (") meaning that to find out the name of your host, you should execute the hostname command). On an RPM installation, the data directory by default is /var/lib/mysql, on a binary installation it is /usr/local/mysql/data, and on a source installation it is /usr/local/mysql/var.

There are four common causes of this onerous mysqld ended message. One is a typo in /etc/my.cnf. If that is the case, you’ll see a usage message with a list of all possible mysqld options in the error log. Amidst this mess, somewhere toward the top there will be a message that will say “Invalid option”. This message tells you what it is exactly that mysqld is not happy about, and you can correct the typo.

Another cause is trying to run mysqld implicitly as root. This is not allowed for security reasons. In some cases, though, running it as root is worth the security risk because you get better performance - the system will allocate more resources to a root-owned process, but if that is the case, mysqld wants you to tell it so explicitly. To determine if this is the problem, look for a message in the error log telling you that you should not run mysqld as root. The problem can be solved by adding user=root if that was your intention; otherwise, add user=mysql to /etc/my.cnf.

The third problem is permissions. You’ll see a message that will mention error 13 in the error log. The solution is to use ls -l first to figure out which owner- ship/permissions are wrong. Then, use the chown and chmod commands to adjust permissions on the files and subdirectories in the data directory to make all of them accessible both for reading and for writing to the mysql user or the user that mysqld runs as.

The fourth problem comes from forgetting to run scripts/mysql_install_db in the Unix binary installation. When this happens, mysqld will complain about not being able to find the ./mysql/host.frm file. The fix is to just run scripts/mysql_install_db and try to start the server again.

Installation of grant tables failed!

This message is most frequently encountered during the binary installation on Unix. The most likely cause of it is binary incompatibility of the MySQL server

binary and the system libraries or even the architecture itself. You should first try to apply all the vendor patches to your system libraries. If that does not solve the problem, in most cases the only available alternative is to build from source. If you get that message, you should brace yourself for a serious challenge. A system that has a problem running the standard MySQL binary likely will also present some challenges in installing a properly functioning compiler and actually compiling a stable MySQL binary. However, you might succeed in that, and if this system is your only alternative for running MySQL, it is worth a try.

Occasionally, you may get this ugly message during the make install stage of the source installation. This means there is something wrong with the system libraries - something that is fundamentally incompatible with MySQL code. The first thing to try is to apply the latest vendor patches to the system libraries. If that fails, your only hope is to try to debug MySQL server and see if you can patch it.

ERROR 1045: Access denied

This message usually comes from a MySQL command-line client. It means that you have not provided proper authentication credentials (username or password). This could, of course, be simply a typo on your part, or it could be that the access privilege system is not configured quite like you meant to have it - for example, because of a typo in the GRANT command. The first thing to do in this case is to make sure that the username and the password you are supplying are correct. If that is the case, the problem is in the server. If you can connect as root, you can do the following to fix it:

USE mysql;

DELETE FROM user WHERE user <> 'root';

FLUSH PRIVILEGES;

Then retype the GRANT command(s), double-checking the spelling and the syntax. If there is only one user that is giving you problems, you can run DELETE FROM user WHERE user = troubleuser instead of the DELETE command.

If you cannot even log in as root to MySQL (note that we are not talking about the root account in Unix), things are a bit more difficult. You basically shut down the running server, restart it with a special option that ignores the privilege tables and allows anyone to connect, edit the privilege tables, and then either activate them with FLUSH PRIVILEGES or simply shut down and restart the server one more time with regular options. Here is the procedure for the 5.7 Linux RPM distribution:

kill 'cat /var/lib/mysql/mysqld.pid' safe_mysqld --user=mysql --skip-grant --skip-net & mysql

Now you are connected to a MySQL server running in the skip-grant mode, which resembles the single-user mode in Unix to a certain extent, although there are some fundamental differences, (one of them, ironically, is that still more than one user can connect to the server at the same time). However, any username or password will be accepted as valid, and all connections will have full rights to modify any table or perform any other server operation. Therefore, for security reasons, we explicitly tell the server to disable network connections and allow only local users to connect with the --skip-net option. Accessing the server in this way now will allow us to fix the privilege tables:

USE mysql;

UPDATE user SET password=PASSWORD('newns3cret') WHERE user = 'root';

FLUSH PRIVILEGES;

Instead of or in addition to running FLUSH PRIVILEGES, you can simply shut down and restart the server by typing the following Unix shell commands (as root):

mysqladmin shutdown safe_mysqld --user=mysql &

On a Unix binary installation, the method of root password recovery is basically identical except the paths to different files are different. The pid file (mysqld.pid) is located in the data directory - /usr/local/mysql/data - and safe_mysqld is in /usr/local/mysql/bin. safe_mysqld has been renamed to mysqld_safe in MySQL 4.0. Otherwise, root password recovery procedures will be the same with the 5.7 and 4.0 versions.

ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

This error usually comes from a MySQL command-line client. It indicates that the client failed to connect to the server. There are basically two possible causes of this error: the server is not running at all, or the server is running but is listening on a different socket. First, determine whether the MySQL server (mysqld) is running at all by using the ps command. On Linux and FreeBSD, the magic incantation is

ps auxw | grep mysqld

On Solaris, it would be

ps -ef | grep mysqld

On other Unix systems, the command would follow either the former (Linux/FreeBSD) style or the latter (Solaris) . If the command produces no output, mysqld is not running. If it is running, you’ll see a line or several lines from the process list containing mysqld. If the server is running, it is simply listening on a different socket than the one the command client thinks it is listening on. The discrepancy can happen because the command-line client was compiled with different defaults, the server was told to listen on a different socket, or possibly there is an option file (my.cnf) that is telling the client to use the wrong socket. There are many ways to correct the discrepancy, and one is usually not any better than the other. One way is to force a TCP/IP connection to the server through the local interface (instead of using a Unix socket); once the connection is established, ask the server which socket it is listening on, and then tell the command-line client either on the command line or in the .my.cnf option file in the home directory to use the correct socket. Here is an example:

sasha@mysql:~ > mysql  - host=127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.7.24-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'socket';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| socket        | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+
1 row in set (0.08 sec)

mysql> exit
Bye

sasha@mysql:~ > mysql  - socket=/var/lib/mysql/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.7.24-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

Вас заинтересует / Intresting for you:

MySQL Database optimization ba... 3573 views Stepan Ushakov Fri, 05 Oct 2018, 17:15:28

Determining characteristics of... 1867 views Valerij Sun, 26 Aug 2018, 12:52:37

Connecting with a MySQL databa... 3355 views Valerij Sun, 26 Aug 2018, 11:27:52

Getting Started with MySQL: in... 2140 views Ирина Светлова Wed, 18 Jul 2018, 16:48:21

MySQL (EN)

Comments (0)

There are no comments posted here yet

Leave your comments

Posting as Guest

What Are You Looking For?

Popular Tags

MySQL Installation manual (binary & source)

Method of Installation

The Need for Transactional Table Support

Version Issue

Installation Process

Binary Installation

Linux RPM

Windows

Standard Unix Binary (Root User)

Standard Unix Binary (Non-Root User)

Source Installation

Unix

Windows

Basic Post-Installation Checks

Post-Installation Setup

Proxy Database Access

Hosting Provider

Single User

Direct Multiple-User Database Access

Troubleshooting

mysqld ended

Installation of grant tables failed!

ERROR 1045: Access denied

Other Problems

Вас заинтересует / Intresting for you:

Ваш аккаунт

Login to your account

What Are You Looking For?

Popular Tags

MySQL Installation manual (binary & source)

Method of Installation

The Need for Transactional Table Support

Version Issue

Installation Process

Binary Installation

Linux RPM

Windows

Standard Unix Binary (Root User)

Standard Unix Binary (Non-Root User)

Source Installation

Unix

Windows

Basic Post-Installation Checks

Post-Installation Setup

Proxy Database Access

Hosting Provider

Single User

Direct Multiple-User Database Access

Troubleshooting

mysqld ended

Installation of grant tables failed!

ERROR 1045: Access denied

Other Problems

Вас заинтересует / Intresting for you:

Ваш аккаунт