Step by Step to install Mysql 5.7 on Ubuntu 14.04
https://dev.mysql.com/doc/refman/5.7/en/installing.html
- Operation System Ubuntu 64bit
- Distributor ID Ubuntu
- Description Ubuntu 14.04.5 LTS
- Release 14.04
- Codename trusty
- Mysql Version 5.7
- machine M4.large on an EBS Volume
- Distribution format binary
General Installation Guidance
The steps are done by root user.
How to get MySQL
The deb file can be seen on the page http://dev.mysql.com/downloads/
This installation uses the current tarball package, Linux - Generic - glibc 2.12 64-bit => mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
The package downloaded won't be checked by md5sum
Packages pre-requisites
MySQL has a dependency on the libaio
sudo apt-get install libaio1 libaio-dev
sudo apt-get install libnuma-dev # (Support for Non Uniform Memory Access NUMA)
Installation Layout for Generic Unix/Linux Binary Package
Oracle provides a set of binary distributions of MySQL. These include generic binary distributions in the form of compressed tar files This section covers the installation of MySQL from a compressed tar file binary distribution on Unix/Linux platforms.
To install a compressed tar file binary distribution, unpack it at the installation location you choose (typically /usr/local/mysql). This creates the directories shown in the following table.
| Directory | Contents of Directory |
--------------------------------------------------------------|
| bin | mysqld server, client and utility programs |
| docs | MySQL manual in Info format |
| man | Unix manual pages |
| include | Include (header) files |
| lib | Libraries |
| share | Error messages, dictionary, and SQL for database installation
| support-files | Miscellaneous support files |
Generate the my cnf configuration file
It is mandatory that the default my.cnf file exists and it sets the datadir and basedir if they are diffrent from the defautl. When you try to bring up database server, if it defaults to the default areas its because it cannot find a directory or doesn't have permission for directories specified so it will then try the default locations. Use a template file in the support-files directory.
If the /etc/my.cnf hasn't been created previously, it is needed to let the mysqld knows where is the datadir
vi /etc/my.cnf
# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# The following options will be passed to all MySQL clients
[client]
port = 3306
socket = /tmp/mysql.5.7.12.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.5.7.12.sock
pid-file = /mnt/db_main/data/mysqld.pid
basedir = /usr/local/mysql
datadir = /mnt/db_main/data
log_error = /mnt/db_main/logs/mysql57.log
tmpdir = /tmp
skip-external-locking
max_allowed_packet = 32M
character-set-server = utf8
#innodb_data_home_dir=/u01/mysql5.6/innodata
#innodb_data_file_path=ibdata01:50M;ibdata02:50M:autoextend:max:512M
#innodb_log_group_home_dir=/u01/mysql5.6/innologs
#innodb_log_buffer_size=5M
#innodb_buffer_pool_size=2048M
#log-bin=/u01/mysql5.6/binlogs/mysql01-bin
#log-slow-queries
#long_query_time=1
server-id = 1
Setup the MySQL environment
The first set of commands are run as the root OS user (# prompt) to set up the environment. In the /usr/local directory unzip and untar the MySQL software and build a symbolic link. This will set up the MYSQL_HOME directory to be in /usr/local/mysql location.
Because the user is required only for ownership purposes, not login purposes, the useradd command uses the -r and -s /bin/false options to create a user that does not have login permissions to your server host.
Copy tar file to /usr/local directory and setup up MySQL software.
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
cd /usr/local
cp ~/mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz .
tar zxvf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.21-linux-glibc2.12-x86_64 mysql
The datadir of the installation is set to a different path https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html
mkdir /mnt/db_main/data
chown mysql:mysql /mnt/db_main/data
chmod 750 /mnt/db_main/logs
Initializing the Data Directory
In the examples shown here, the server is going to run under the user ID of the mysql login account. This assumes that such an account exists.
cd /usr/local/mysql
Create a directory whose location can be provided to the secure_file_priv system variable, which limits import/export
mkdir mysql-files
chown mysql:mysql mysql-files
chmod 750 mysql-files
It is not necessary to specify the custom location of the datadir because the command reads the default configuration my.cnf file
bin/mysqld --initialize --user=mysql
Also those are valid initialized commands
bin/mysqld --initialize --user=mysql --datadir=/mnt/db_main/data # if basedir is the default
bin/mysqld --initialize --user=mysql --datadir=/mnt/db_main/data --basedir=/usr/local/mysql
If the custom default file position is different from the default file where Mysql looks for it, you can specify its location by
bin/mysqld --defaults-file=/opt/mysql/etc/my.cnf --initialize --user=mysql
Run the MySQL SSL Certificate and RSA Key Generation Utility
If you want the server to be able to deploy with automatic support for secure connections,
bin/mysql_ssl_rsa_setup
- You can specify the datadir if you need (--datadir=/mnt/db_main/data)
Starting the Server
It is important that the MySQL server be run using an unprivileged (non-root) login account. To ensure this, run mysqld_safe as root and include the --user option as shown.
bin/mysqld_safe --user=mysql &
Testing the Server
https://dev.mysql.com/doc/refman/5.7/en/testing-server.html After the data directory is initialized and you have started the server, perform some simple tests to make sure that it works satisfactorily. Alternatively, add the bin directory to your PATH environment variable setting.
mysqladmin -uroot -p version
mysqladmin -uroot -p variables
mysqladmin -uroot -p shutdown
mysqlshow -uroot -p
mysqld_safe --user=mysql &
Connect to the server
If you used --initialize but not --initialize-insecure to initialize the data directory, connect to the server as root using the random password that the server generated during the initialization sequence:
mysql -uroot -p
If you used --initialize-insecure to initialize the data directory,
mysql -u root --skip-password
Securing the Initial MySQL Accounts
mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user;
+---------------+-----------+------------------------------------------------------------------------------------+
| User | Host | HEX(authentication_string) |
+---------------+-----------+------------------------------------------------------------------------------------+
| root | localhost | 2A38314635453231453335343037443838344136434434413733314145424642364146323039453142 |
| mysql.session | localhost | 2A5448495349534E4F544156414C494450415353574F52445448415443414E42455553454448455245 |
| mysql.sys | localhost | 2A5448495349534E4F544156414C494450415353574F52445448415443414E42455553454448455245 |
+---------------+-----------+------------------------------------------------------------------------------------+
If the output on your system shows any accounts with empty passwords, your MySQL installation is unprotected until you do something about it
- Assign a pwd to each Mysql root account that does not have one
- To prevent clients from connecting as anonymous users without pwd, either assign one or remove the account itself.
root accounts Current versions of MySQL 5.7 create only a 'root'@'localhost' account, If these root accounts have empty passwords, anyone can connect to the MySQL server as root without a password and be granted all privileges. On Unix, each root account permits connections from the local host
After connecting, assign a new root password: At the first access, it is mandatory to change the root's password.
alter user 'root'@'localhost' identified by 'root';
SET PASSWORD FOR user = PASSWORD('new_password'); # Before 5.7.6
anonymous accounts If accounts for anonymous users were created, these have an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.
SET PASSWORD FOR ''@'localhost' = PASSWORD('new_password');
Or
DROP USER ''@'localhost';
Securing Test Databases Some installation methods create a test database If that is true on your system, the mysql.db table will contain rows that permit access by any user to the test database and other databases with names that start with test_.
If you want to remove any-user access to test databases, do so as follows:
DELETE FROM mysql.db WHERE Db LIKE 'test%';
FLUSH PRIVILEGES;
DROP DATABASE test;
============> Secure database
Clean up the database server by adding passwords and getting rid of anonymous users. The
MySQL database super user is called root. This step will add a password for this MySQL database user.
Lauch script :
mysql_secure_installation
The version 5.0.51 had a bug. Bug #46842 http://bugs.mysql.com/bug.php?id=46842.
I Problem:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
I fix this problem:
ln -s /tmp/mysql.5.0.51.sock /tmp/mysql.sock
============>
Starting and Stopping MySQL Automatically
https://dev.mysql.com/doc/refman/5.7/en/mysql-server.html MySQL distributions on Unix and Unix-like system include a script named mysql.server Which starts the MySQL server using mysqld_safe
!NOTE To run the server as some specific user, add an appropriate user option to the [mysqld] group of the global /etc/my.cnf
To start and stop MySQL automatically on your server, you must add start and stop commands to the appropriate places in your /etc/rc* files
If you install MySQL from a source distribution or using a binary distribution format that does not install mysql.server automatically, you can install the script manually. It can be found in the support-files directory under the MySQL installation directory
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
After installing the script, the commands needed to activate it to run at system startup depend on your operating system. On Linux, you can use update-rc.d https://askubuntu.com/questions/9382/how-can-i-configure-a-service-to-run-at-startup
update-rc.d mysql defaults
You can add specific configuration for mysql.server application only If specified, they must be placed in an option file, not on the command line It can specify the basedir, datadir, pid-fil, service-startup-timeout [mysql.server] basedir=/usr/local/mysql
Further considerations
Log file not found and how to configure log_error
https://dev.mysql.com/doc/refman/5.7/en/error-log-unix.html 2018-02-14T16:18:29.752230Z mysqld_safe error: log-error set to '/mnt/db_main/logs/.err', however file don't exists. Create writable for user 'mysql'.
This is due to the missing log file, the issue is that at the time of initiate the data, the log file was created in the default location (data dir). The issue occurs when the my.cnf file has the log_error folder specified after the initiate step and it is different from the default location, the mysqld doesn't start because it doesn't find the log file in the right location. The steps I've done to find this issue was
bin/mysqld --initialize --user=mysql
echo "log_error=/mnt/db_main/logs/mysql57.log" >> /etc/my.cnf
bin/mysqld_safe --user=mysql &
2018-02-14T16:18:29.752230Z mysqld_safe error: log-error set to '/mnt/db_main/logs/.err', however file don't exists. Create writable for user 'mysql'.
I resolved it by re-initiating the data dir.
Pid file configuration setting
pid-file=file_name => The path name of the file in which the server should write its process ID.
At the first attempt, the pid-file was created in a memory file system /var/run/mysql/mysql.pid, the issue happened at the restart of the server, where the folder /var/run/mysql was not present and the server couldn't start. The second attempt, was to create the pid file in the folder /var/run itself, but the was permission denied error. A solution to this issue is to create the pid file in the basedir folder.
Errors encountered
bin/mysqld_safe --user=mysql & => 2018-02-14T13:15:44.100940Z mysqld_safe error: log-error set to '/mnt/db_main/logs/.err', however file don't exists. Create writable for user 'mysql'.https://dev.mysql.com/doc/refman/5.7/en/error-log-unix.html
Output commands
bin/mysqld --initialize --user=mysql --datadir=/mnt/db_main/data --basedir=/usr/local/mysql
2018-02-14T10:13:23.380290Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-02-14T10:13:24.442929Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-02-14T10:13:24.718131Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-02-14T10:13:24.786248Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b143f25f-116f-11e8-9d52-022fd32d743e.
2018-02-14T10:13:24.789074Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-02-14T10:13:24.790003Z 1 [Note] A temporary password is generated for root@localhost: V>fs=lc2YjJO
bin/mysql_ssl_rsa_setup --datadir=/mnt/db_main/data
Generating a 2048 bit RSA private key
...........................................................................................................................+++
........................................................................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
................................................................................................................................................+++
...............+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
...................................................+++
.............................................+++
writing new private key to 'client-key.pem'
-----