MySQL multi-instance Configuration

MySQL multi-instance Configuration

Lab environment: RHEL6.4 for minimal installation, mysql installation package for general binary installation package, version for mysql-5.6.26

1. Create a mysql user

# Useradd-M-s/sbin/nologin mysql

# Yum-y install ncurses-devel libaio-devel

# Install the mysql dependency package. Otherwise, initialization fails.

2. decompress the Software Package

# Tar xf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz-C/usr/local

# Mv/usr/local/mysql-5.6.26-linux-glibc2.5-x86_64 // usr/local/mysql

3. Create a directory

# Mkdir/data/{3306, 3307}/data-pv

# Mkdir/data/{3306, 3307}/log-pv

# Tree/data/# view the directory tree

4. Create my. cnf in/data/3306

# Cd/data/3306/

# Vim my. cnf

[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

Data =/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

5. Create my. cnf in/data/3307

# Cd ../3307/

# Vim my. cnf

[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

Data =/data/3307/data

Server-id = 2

# 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

6. Create a mysql Startup File in/data/3306

# Cd/data/3306/

# Vim mysql

#! /Bin/sh

[-F/etc/init. d/functions] &./etc/init. d/functions

Port = 3306

Mysql_user = "root"

Mysql_pwd = ""

Mysql_sock = "/data/$ {port}/mysql. sock"

Export Path = "/usr/local/mysql/bin"

# Startup function

Usage (){

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

}

Function_start_mysql ()

{

If [! -E "$ mysql_sock"]; then

/Bin/sh $ {export path}/mysqld_safe -- defaults-file =/data/$ {port}/my. cnf 2> & 1>/dev/null &

Action "Starting MySQL..."/bin/true

Else

Printf "MySQL is running... \ n"

Exit

Fi

}

# Stop function

Function_stop_mysql ()

{

If [! -E "$ mysql_sock"]; then

Printf "MySQL is stopped... \ n"

Exit

Else

Action "Stoping MySQL..."/bin/true

$ {Export path}/mysqladmin-u $ {mysql_user}-p $ {mysql_pwd}-S/data/$ {port}/mysql. sock shutdown

Fi

}

# Restart function

Function_restart_mysql ()

{

Function_stop_mysql &>/dev/null

Sleep 2

Function_start_mysql &>/dev/null

Action "Restarting MySQL..."/bin/true

}

If [$ #-ne 1]; then

Usage

Fi

Case $1 in

Start)

Function_start_mysql

;;

Stop)

Function_stop_mysql

;;

Restart)

Function_restart_mysql

;;

*)

Usage

Esac

# Chmod + x mysql

7. Create a mysql Startup File in/data/3307

# Cd/data/3307/

# Vim mysql

#! /Bin/bash

[-F/etc/init. d/functions] &./etc/init. d/functions

Port = 3307

Mysql_user = "root"

Mysql_pwd = ""

Mysql_sock = "/data/$ {port}/mysql. sock"

Export Path = "/usr/local/mysql/bin"

Usage (){

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

}

# Startup function

Function_start_mysql ()

{

If [! -E "$ mysql_sock"]; then

/Bin/sh $ {export path}/mysqld_safe -- defaults-file =/data/$ {port}/my. cnf 2> & 1>/dev/null &

Action "Starting MySQL..."/bin/true

Else

Printf "MySQL is running... \ n"

Exit

Fi

}

# Stop function

Function_stop_mysql ()

{

If [! -E "$ mysql_sock"]; then

Printf "MySQL is stopped... \ n"

Exit

Else

Action "Stoping MySQL..."/bin/true

$ {Export path}/mysqladmin-u $ {mysql_user}-p $ {mysql_pwd}-S/data/$ {port}/mysql. sock shutdown

Fi

}

# Restart function

Function_restart_mysql ()

{

Function_stop_mysql &>/dev/null

Sleep 2

Function_start_mysql &>/dev/null

Action "Restarting MySQL..."/bin/true

}

Case $1 in

Start)

Function_start_mysql

;;

Stop)

Function_stop_mysql

;;

Restart)

Function_restart_mysql

;;

*)

Usage

Esac

# Chmod + x mysql

8. Modify the file owner and permissions

# Chown-R mysql: mysql/data

9. Add the mysql startup path

# Echo 'export PATH = $ PATH:/usr/local/mysql/bin'>/etc/profile
# Source/etc/profile

10. initialize the database

# Cd/usr/local/mysql/scripts/

#./Mysql_install_db -- defaults-file =/data/3306/my. cnf -- user = mysql -- basedir =/usr/local/mysql -- datadir =/data/3306/data

#./Mysql_install_db -- defaults-file =/data/3307/my. cnf -- user = mysql -- basedir =/usr/local/mysql -- datadir =/data/3307/data

11. Start mysql

#/Data/3306/mysql start
#/Data/3307/mysql start
# Netstat-lntp | grep 330 # Check whether the process is started

12. log on to mysql

① # Mysql-S/data/3306/mysql. sock # the newly installed mysql has no login password.

# If not, check the mysql-error.log logs under the/data/3306/log directory and troubleshoot the errors one by one

If the logon is successful, modify the logon password.

Mysql> update mysql. user set password = password ("123456") where user = 'root ';
Mysql> flush privileges;

We do not recommend that you change the password in the shell environment. Otherwise, you only need to view the command history to see the password. You can also use shell, but remember to keep a clear history of commands.

② Similarly, use the above method to change the login password of 3307
# Mysql-S/data/3307/mysql. sock

Mysql> update mysql. user set password = password ("123456") where user = 'root ';
Mysql> flush privileges;

③ Write the changed password back to the mysql Startup File (otherwise, enter the password every time you start, close, or restart mysql)

# Sed-I's/mysql_pwd = \ "\"/mysql_pwd = \ "123456 \"/G'/data/3306/mysql
# Sed-I's/mysql_pwd = \ "\"/mysql_pwd = \ "123456 \"/G'/data/3307/mysql

You can also manually modify

# Vim/data/3306/mysql

# Vim/data/3307/mysql

13. Restart mysql

#/Data/3306/mysql restart

#/Data/3307/mysql restart

# Netstat-lntp | grep 330

14. knowledge points

When entering mysql, remember to add-S to specify the mysql socket path

# Mysql-uroot-p-S/data/3307/mysql. sock

The following command can smoothly shut down mysql

# Mysql admin-uroot-p123456-S/data/3306/mysql. sock shutdown

This article permanently updates the link address: