MySQL installation and multi-instance (detailed)

MySQL installation and multi-instance, configuration optimization

Yum Install tree nmap sysstat Lrzsz dos2unix-y

Install the related packages
TAR-XF cmake-2.8.8.tar.gz
CD cmake-2.8.8
./configure
Make && make install

Yum install gcc gcc-c++ autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*

1. Add Users
Groupadd MySQL
Useradd mysql-m-s/sbin/nologin-g MySQL

2.
Mkdir-p/database/mysql

Chown Mysql.mysql-r/database/mysql

TAR-ZXVF mysql-5.6.24.tar.gz
CD mysql-5.6.24
Make clean
CMake. -dcmake_install_prefix=/usr/local/mysql/\
-dmysql_datadir=/database/mysql \
-DSYSCONFDIR=/ETC \
-dwith_innobase_storage_engine=1 \
-dwith_partition_storage_engine=1 \
-dwith_federated_storage_engine=1 \
-dwith_blackhole_storage_engine=1 \
-dwith_myisam_storage_engine=1 \
-denabled_local_infile=1 \
-denable_dtrace=0 \
-DDEFAULT_CHARSET=UTF8MB4 \
-DDEFAULT_COLLATION=UTF8MB4_GENERAL_CI \
-dwith_embedded_server=1 \

Make && make install

/bin/cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld
/bin/cp/usr/local/mysql/support-files/my-small.cnf/etc/my.cnf

chmod +x/etc/init.d/mysqld
Chkconfig--add mysqld
Chkconfig mysqld on

Configuring Environment variables
Echo ' Export path=/usr/local/mysql/bin: $PATH ' >>/etc/profile
Source/etc/profile
Echo $PATH

Initializing the database
Cd/usr/local/mysql/scripts
./mysql_install_db--user=mysql--basedir=/usr/local/mysql--datadir=/database/mysql

Chown Mysql.mysql-r/database/mysql
Chmod-r 1777/tmp/

/etc/init.d/mysqld start

Problems:

Log in to MySQL troubleshooting
ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)
Problem solving:
Kill Process Pkill Mysqld
Delete a data file rm-rf/database/mysql/data/*
Reinitialization of Cd/usr/local/mysql/scripts
./mysql_install_db--user=mysql--basedir=/usr/local/mysql--datadir=/database/mysql

Problem solving of system error character set
Change into UTF-8

vim/etc/sysconfig/i18n

Lang= "en_US. UTF-8 "
Sysfont= "Latarcyrheb-sun16"

Basic optimization

Simple cleanup of user tables:
View User table Select User,host from Mysql.user;
Remove password for empty user delete from mysql.user where password= ';
Delete User is empty delete from mysql.db where user= ';
Delete IPV6 Delete from Mysql.user where host= ':: 1 ';

Add Administrator account:
Grant all privileges on * * to [email protected] ' localhost ' identified by ' 123456 ' with GRANT option;
Flush privileges;

Add root password:

mysqladmin-u root password ' linuxboy '

MySQL Multi-instance

is to open multiple different ports on a single machine and run multiple MySQL service processes. These MySQL multi-instance common set of installers use different (and can be the same) configuration files,
Launch programs, data files. In the provision of services, multi-instance MySQL logically appears to be separate, multiple instances of itself is based on the configuration file corresponding to the set value, to obtain the relevant hardware resources of how much.

function and Disadvantage
Efficiently utilize the resources of the server, and when a single server resource is available, you can take advantage of the remaining resources to provide more services.
High concurrency under one instance consumes a lot of resources competing for problems

Single configuration file deployment scenario:
Mysqld_multi--config-file=/database/mysql/my_multi.cnf Start 1,2,3,4,5,6,7 startup
Too high coupling

MySQL Multi-instance installation

Yum install gcc gcc-c++ autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl* libaio-devel

1. Add Users
Groupadd MySQL
Useradd mysql-m-s/sbin/nologin-g MySQL

Create a MySQL multi-instance data file directory

Mkdir-p/data/{3306,3307}/data

Major modifications to the configuration file:
Mainly modify the configuration file path
[Client]
Port = 3306
Socket =/data/3306/mysql.sock

[Mysqld]

Port = 3306
Socket =/data/3306/mysql.sock
Basedir =/usr/local/mysql
DataDir =/data/3306/data

[Mysqld_safe]

Log-error=/data/3306/ilanni.err
Pid-file=/data/3306/ilanni.pid

******************************************************

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

[Mysqld]

Port = 3307
Socket =/data/3307/mysql.sock
Basedir =/usr/local/mysql
DataDir =/data/3307/data

[Mysqld_safe]

Log-error=/data/3307/ilanni.err
Pid-file=/data/3307/ilanni.pid

Multi-instance startup scripts

#!/bin/bash

port=3306
Mysql_user= "Root"
Mysql_passwd= "123456"
Cmdpath= "/usr/local/mysql/bin"
mysql_sock= "/data/${port}/mysql.sock"

Function_start_mysql ()
{
if [!-e "$mysql _sock"];then
printf "Starting mysql...\n"
/bin/sh ${cmdpath}/mysqld_safe--defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
Else
printf "Mysql is running...\n"
Exit
Fi
}
Function_stop_mysql ()
{
if [!-e "$mysql _sock"];then
printf "Mysql is stopped...\n"
Exit
Else
printf "stoping mysql...\n"
${cmdpath}/mysqladmin-u ${mysql_user}-p${mysql_passwd}-s/data/${port}/mysql.sock shutdown
Fi
}

Function_restart_mysql ()
{
printf "Restang 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

Authorizing MySQL Users

Chown-r Mysql.mysql/data

Initializing a MySQL multi-instance database file
/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql–datadir=/data/3306/data–user=mysql

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql–datadir=/data/3307/data–user=mysql
Start MySQL

/data/3306/mysql start

Single Instance add password

Mysqladmin-u root-s/data/3306/mysql.sock password ' linuxboy '

MySQL Application management

Retrieve the lost MySQL root user password

Use--skip-grant-tables to start MySQL, ignoring authorization login verification

Mysqld_safe--skip-grant-tables--user=mysql &

Multi-instance MySQL startup modify lost root password method

Add--skip-grant-tables parameter at startup
Mysqld_safe--defaults-file=/data/3306/my.cnf--skip-grant-table &
Mysql-u root-p-s/data/3306/mysql.sock

MySQL installation and multi-instance (detailed)