CentOS 6.5 under source installation MySQL 5.6

MySQL is divided into development version and stable version (GA), the development version has the latest features, but not stable, nor completely tested, there may be serious bugs, and stable version is a long time testing, eliminate the known bugs, its stability and security are guaranteed.

For a MySQL version number such as: MYSQL-5.6.1-M1, what does this version number mean?
1. Explanation for 5.6.1: The first number 5 represents the file format, the second number 6 represents the release level, and the third number 1 represents the version number. When the update amplitude is small, the last number will increase, the second number will increase when there is a significant feature update, and the first number will increase when the file format changes.
2. For M1 explanation: This is used to indicate the stability level of this MySQL version, if there is no suffix, then this version is a stable version (GA), if the suffix is mn (for example, M1,M2) format, indicating that this version has been added to some thoroughly tested new features, It can be thought that this is a trial production of the mold; if the suffix is RC, indicating that this is a candidate version, has modified the known important bug, but did not pass long enough time to confirm that all bugs have been fixed.

Once the version number is selected, you should choose which distribution to use, you can use the binary distribution such as RPM package or zip compression package, etc., but if you want to implement the following functions, it is necessary to choose the source code installation (This article is the way to choose the source code installation):
1.L Installing the MYSQ to the specified location
2.l some features of MySQL (standard binary versions do not have these features) such as: TCP packet support, debug MySQL
3. All character sets are supported by default in the binary version, but you can specify the character set when compiling and installing the source code, which makes the installation of MySQL smaller

Download MySQL

Download the mysql:http://dev.mysql.com/downloads/mysql/here

This is what I chose:

After downloading, you need to check the MD5 of the file to confirm whether the original copy is downloaded from the official website (in case someone has tampered with the software).

After I upload to Linux from the Windows download, use the md5sum command to check:

[Email protected] src]# md5sum mysql-5.6.25.tar.gz
37664399c91021abe070faa700ecd0ed mysql-5.6.25.tar.gz

As can be seen, and in the MD5 is consistent, if not consistent, it is necessary to replace a mirror address to download MySQL.

Install MySQL

Preparing the installation Environment

First check if MySQL has been installed:

[Email protected] src]# Rpm-qa | grep MySQL

If you do, uninstall the previously installed MySQL:

[[email protected] src]# rpm-e--nodeps xxx (xxx is search result)

and delete all the relevant files:

/etc/my.cnf

Compiling and installing

Install the packages needed to compile the code

[Email protected] src]# yum-y install make gcc-c++ cmake bison-devel ncurses-devel Libaio
[email protected] src]# Yum install Libaio libaio-devel-y
[email protected] src]# Yum install perl-data-dumper-y
[email protected] src]# Yum install net-tools-y

Unzip the installation package and compile the installation

[Email protected] src]# tar xvf mysql-5.6.25.tar.gz
[Email protected] src]# CD mysql-5.6.25
[Email protected] mysql-5.6.25]#

Cmake-dcmake_install_prefix=/usr/local/mysql-dmysql_datadir=/usr/local/mysql/data-dsysconfdir=/etc-dwith_ Myisam_storage_engine=1-dwith_innobase_storage_engine=1-dwith_memory_storage_engine=1-dwith_readline=1-dmysql_ Unix_addr=/usr/local/mysql/mysql.sock-dmysql_tcp_port=3306-denabled_local_infile=1-dwith_partition_storage_ Engine=1-dextra_charsets=all-ddefault_charset=utf8-ddefault_collation=utf8_general_ci

Compilation succeeded

[[email protected] src]# make && make install

At this point, MySQL installation is complete

Configure MySQL

Check if the system already has a MySQL user and if not, create

[Email protected] mysql-5.6.25]# CAT/ETC/PASSWD | grep MySQL
[Email protected] mysql-5.6.25]# Cat/etc/group | grep MySQL

Creating a MySQL user (but not using the MySQL account login system)

[Email protected] mysql-5.6.25]# Groupadd mysql-s/sbin/nologin
[[email protected] mysql-5.6.25]# useradd-g MySQL MySQL

Modify Permissions

[Email protected] mysql-5.6.25]# chown-r mysql:mysql/usr/local/mysql

At this point, MySQL installation is complete

There are two configurations, which are recommended by multi-instance configuration

Single-instance configuration

Single-instance configuration method

Go to installation path

[Email protected] mysql-5.6.25]# Cd/usr/local/mysql

Enter the installation path, execute the initialization configuration script, create the system's own database and table

[Email protected] mysql]# scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data-- User=mysql

Note: When starting the MySQL service, will be in a certain order to search my.cnf, first in the/etc directory, find will search "$basedir/my.cnf", in this case is/usr/local/mysql/ MY.CNF, this is the default location for the new MySQL configuration file!

Note: After the minimum installation of the CentOS 7 operating system is complete, there will be a my.cnf in the/etc directory, you need to rename this file to another name, such as:/etc/my.cnf.bak, otherwise, the file will interfere with the correct configuration of the source installed MySQL, causing the failure to start.

After updating the system with Yum Update, you need to check if there is a my.cnf in the/etc directory, and if so, rename it to something else. Otherwise, MySQL will start with this configuration file, which may cause problems such as an inability to start properly.

Add firewall

[Email protected] mysql]# firewall-cmd--zone=public--add-port=3306/tcp--permanent
[Email protected] mysql]# Firewall-cmd--reload

Start MySQL

Add services, Copy service scripts to the INIT.D directory, and set boot boot

[email protected] mysql]# CP Support-files/mysql.server/etc/init.d/mysql
[[email protected] mysql]# chkconfig MySQL on
[[Email protected] mysql]# service MySQL start--start MySQL

See if MySQL started successfully

[Email protected] mysql]# NETSTAT-LNTP | grep 3306

If MySQL does not start successfully, view the error log under directory/usr/local/mysql/data

[[email protected] data]# tail Localhost.localdomain.err (Localhost.localdomain is host name)

If the log directory is not generated, the MySQL installation does not succeed (recompile the installation once)

Restart MySQL

First kill the MySQL process

[Email protected] 3306]# Pkill mysqld

Then check if the MySQL process has been killed

[Email protected] 3306]# NETSTAT-LNTP | grep 3306

The shell has no output at this point, indicating that the MySQL process has been killed

Then restart MySQL and check again to see if it started successfully.

[[Email protected] 3306]# service MySQL start
[Email protected] 3306]# NETSTAT-LNTP | grep 3306

Multi-instance Configuration

What is a multi-instance

To put it simply, multiple service ports are opened on a single machine, running multiple MySQL service processes that provide their own services through different sockets that listen to different service ports.

These MySQL multi-instance common set of MySQL installation program, using different MY.CNF configuration files, startup programs, data files, in the provision of services, multiple instances logically appear to be separate, multiple instances of the configuration file based on the set value, to obtain the hardware resources of the relevant server

Multi-Instance Configuration method

In this article, multiple instances are configured by opening two ports on MySQL (3306 and 3307来, as the following master-slave synchronization is used to simulate the two ports)

Create a directory (the log directory is where the MySQL logs are stored)

[Email protected] mysql]# mkdir-p/data/{3306,3307}/data
[Email protected] mysql]# mkdir-p/data/{3306,3307}/log

Create a new my.cnf in/data/3306

[Email protected] mysql]# cd/data/3306
[Email protected] mysql]# VI my.cnf

Copy the following contents into the file

[Client]
Port = 3306
Socket =/data/3306/mysql.sock

[Mysqld]
port=3306
Socket =/data/3306/mysql.sock
Pid-file =/data/3306/data/mysql.pid
Basedir =/usr/local/mysql
DataDir =/data/3306/data
Server-id=1
#log-bin=mysql-bin
#log-bin-index= Mysql-bin.index

# LOGGING
Log_error=/data/3306/log/mysql-error.log
Slow_query_log_file=/data/3306/log/mysql-slow.log
Slow_query_log=1

Similarly, create a new my.cnf in/data/3307

[Email protected] mysql]# cd/data/3307
[Email protected] mysql]# VI my.cnf

Copy the following to the file (change the above 3306 to 3307 and the value of Server-id)

[Client]
Port = 3307
Socket =/data/3307/mysql.sock

[Mysqld]
port=3307
Socket =/data/3307/mysql.sock
Pid-file =/data/3307/data/mysql.pid
Basedir =/usr/local/mysql
DataDir =/data/3307/data
Server-id=3
#log-bin=mysql-bin
#log-bin-index= Mysql-bin.index

# LOGGING
Log_error=/data/3307/log/mysql-error.log
Slow_query_log_file=/data/3307/log/mysql-slow.log
Slow_query_log=1

Check the directory structure to see if you misplaced the file.

[Email protected] 3307]# Tree/data
/data
├──3306
│├──data
│├──log
│└──my.cnf

└──3307
│├──data
│├──log
│└──my.cnf

Create a startup file

Create a new MySQL boot file in/data/3306

[Email protected] mysql]# cd/data/3306
[[Email protected] mysql]# VI MySQL

Copy the following contents into the file

#!/bin/sh
port=3306
Mysql_user= "Root"
Mysql_pwd= ""
Cmdpath= "/usr/local/mysql/bin"

#startup function

Function_start_mysql ()
{
printf "Starting mysql...\n"
/bin/sh ${cmdpath}/mysqld_safe--defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
}

#stop function

Function_stop_mysql ()
{
printf "stoping mysql...\n"
${cmdpath}/mysqladmin-u ${mysql_user}-p${mysql_pwd}-s/data/${port}/mysql.sock shutdown
}

#restart function

Function_restart_mysql ()
{
printf "Restarting mysql...\n"
Function_stop_mysql
Sleep 2
Function_start_mysql
}

Case $ in
Start
Function_start_mysql
;;

Stop
Function_stop_mysql
;;

Restart
Function_restart_mysql
;;
*)

printf "Usage:/data/${port}/mysql {start|stop|restart}\n"
Esac

In the same vein, create a new MySQL boot file in/data/3307

[Email protected] mysql]# cd/data/3307
[[Email protected] mysql]# VI MySQL

Copy the following contents into the file

#!/bin/bash

port=3307
Mysql_user= "Root"
Mysql_pwd= ""
Cmdpath= "/usr/local/mysql/bin"

#startup function

Function_start_mysql ()
{
printf "Starting mysql...\n"
/bin/sh ${cmdpath}/mysqld_safe--defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
}

#stop function

Function_stop_mysql ()
{
printf "stoping mysql...\n"

${cmdpath}/mysqladmin-u ${mysql_user}-p${mysql_pwd}-s/data/${port}/mysql.sock shutdown
}

#restart function

Function_restart_mysql ()
{
printf "Restarting mysql...\n"

Function_stop_mysql
Sleep 2
Function_start_mysql
}

Case $ in
Start
Function_start_mysql
;;
Stop
Function_stop_mysql
;;
Restart
Function_restart_mysql
;;
*)
printf "Usage:/data/${port}/mysql {start|stop|restart}\n"
Esac

Modify file Owners and permissions

[Email protected] 3307]# chown-r mysql:mysql/data

[[email protected] 3307]# find/data-name mysql-exec chmod 700 {} \;

Add MySQL boot path

[Email protected] 3307]# echo ' Export path= $PATH:/usr/local/mysql/bin ' >>/etc/profile

[Email protected] 3307]# Source/etc/profile

[Email protected] 3307]# echo $PATH

/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin

You can see that the MySQL path has been added to the boot path

Initializing the database

[Email protected] scripts]# cd/usr/local/mysql/scripts

[Email protected] scripts]#/mysql_install_db--defaults-file=/data/3306/my.cnf--user=mysql--basedir=/usr/local/ MySQL--datadir=/data/3306/data

[Email protected] scripts]#/mysql_install_db--defaults-file=/data/3307/my.cnf--user=mysql--basedir=/usr/local/ MySQL--datadir=/data/3307/data

Configuring firewalls

[Email protected] scripts]# firewall-cmd--zone=public--add-port=3306/tcp--permanent
Success

[Email protected] scripts]# firewall-cmd--zone=public--add-port=3307/tcp--permanent
Success

[Email protected] scripts]# Firewall-cmd--reload
Success

Start MySQL

Start two ports, respectively

[[email protected] scripts]#/data/3306/mysql start
Starting MySQL ...

[[email protected] scripts]#/data/3307/mysql start
Starting MySQL ...

[Email protected] scripts]# NETSTAT-LNTP | grep 330
TCP6 0 0::: 3306:::* LISTEN 33556/mysqld
TCP6 0 0::: 3307:::* LISTEN 34204/mysqld

You can see that two ports have been successfully started.

Log in to MySQL

There's no login password for MySQL just installed.

[Email protected] scripts]#/usr/local/mysql/bin/mysql-s/data/3306/mysql.sock

If unsuccessful, check the Mysql-error.log log in the/data/3306/log directory and troubleshoot the errors individually

If the login is successful, change the login password below (it is not recommended to change the password in the shell environment, otherwise people can see the password only if they view the command history (if you have no command history))

mysql> Update Mysql.user Set Password=password ("123456") where user= ' root ';
mysql> flush Privileges;

Similarly, use the above method to modify the 3307 login password

To write the above changed password back to the MySQL startup file (otherwise, each time you start, shut down, restart MySQL to enter the password)

[Email protected] 3306]# sed-i ' s/mysql_pwd=\ "\"/mysql_pwd=\ "123456\"/g "/data/3306/mysql
[Email protected] 3306]# sed-i ' s/mysql_pwd=\ "\"/mysql_pwd=\ "123456\"/g "/data/3307/mysql

Note Change the above 123456 to the set password

Restart MySQL

[Email protected] 3306]#/data/3306/mysql restart
[Email protected] 3306]# NETSTAT-LNTP | grep 330
TCP6 0 0::: 3306:::* LISTEN 35430/mysqld

You can see that the 3306 Port reboot is successful, so you can restart Port 3307

Update Mysql.user Set Password=password ("Hujihong") where user= ' root ';
GRANT all privileges on * * to [email protected] identified by ' Hujihong ' with GRANT OPTION;
GRANT all privileges on * * to [email protected] '% ' identified by ' Hujihong ' with GRANT OPTION;

Flush privileges;

CentOS 6.5 under source installation MySQL 5.6