# System Environment

[root@db01 ~]# cat /etc/redhat-release;uname -r;uname -m
CentOS release 6.8 (Final)
2.6.32-642.el6.x86_64
x86_64

# Software Acquisition

Link: https://pan.baidu.com/s/12haBWs4wbUUMMNuBiZa5mA
 Extraction code: gr0i

# Create mysql users

[root@db01 ~]# useradd -u 800 -s /sbin/nologin -M mysql
[root@db01 ~]# id mysql
uid=800(mysql) gid=800(mysql) groups=800(mysql)

Create directories for 3306 and 3307 instances

[root@db01 ~]# mkdir /data/330{6,7}/{data,errlog,binlog,slowlog,relaylog} -p
[root@db01 ~]# tree /data/
/data/
|-- 3306
|   |-- binlog
|   |-- data
|   |-- errlog
|   |-- relaylog
|   `-- slowlog
`-- 3307
    |-- binlog
    |-- data
    |-- errlog
    |-- relaylog
    `-- slowlog
 
12 directories, 0 files

# Create / apps directory

[root@db01 ~]# mkdir /apps
[root@db01 ~]# ls -ld /apps/
drwxr-xr-x 2 root root 4096 Oct 22 19:43 /apps/

Upload package, decompress, move, soft link

[root@db01 ~]# rz
[root@db01 ~]# ll mysql-5.5.32-linux2.6-x86_64.tar.gz 
-rw-r--r-- 1 root root 186722932 Sep 19 00:09 mysql-5.5.32-linux2.6-x86_64.tar.gz
[root@db01 ~]# tar xf mysql-5.5.32-linux2.6-x86_64.tar.gz -C /apps/
[root@db01 ~]# mv /apps/mysql-5.5.32-linux2.6-x86_64/ /apps/mysql-5.5.32     
[root@db01 ~]# ln -sv /apps/mysql-5.5.32/ /apps/mysql 
`/apps/mysql' -> `/apps/mysql-5.5.32/'
[root@db01 ~]# chown -R mysql:mysql /apps/mysql/

Replace the path in mysqld_safe file

[root@db01 ~]# sed -i 's#/usr/local/#/apps/#g' /apps/mysql/bin/mysqld_safe
[root@db01 ~]# grep "apps" /apps/mysql/bin/mysqld_safe
if echo '/apps/mysql/share' | grep '^/apps/mysql' > /dev/null
relpkgdata=`echo '/apps/mysql/share' | sed -e 's,^/apps/mysql,,' -e 's,^/,,' -e 's,^,./,'`
relpkgdata='/apps/mysql/share'
MY_BASEDIR_VERSION='/apps/mysql'
ledir='/apps/mysql/bin'
DATADIR=/apps/mysql/data
elif test -x /apps/mysql/bin/my_print_defaults
print_defaults="/apps/mysql/bin/my_print_defaults"
elif test -x /apps/mysql/bin/mysql_print_defaults
print_defaults="/apps/mysql/bin/mysql_print_defaults"
plugin_dir='/apps/mysql/lib/plugin'

Initialize 3307 instances and 3307 instances

[root@db01 ~]# /apps/mysql/scripts/mysql_install_db --user=mysql --basedir=/apps/mysql --datadir=/data/3306/data/
[root@db01 ~]# /apps/mysql/scripts/mysql_install_db --user=mysql --basedir=/apps/mysql --datadir=/data/3307/data/

Configuration file for # configuration 3306 instance (/data/3306/my.cnf)

[client]
 port                  = 3306
 socket                = /data/3306/mysql.sock
 default-character-set = utf8
 
[mysql]
 no-auto-rehash
 
[mysqld]
 ##Conventional configuration       
 user                  = mysql
 port                  = 3306
 character-set-server  = utf8
 basedir               = /apps/mysql
 datadir               = /data/3306/data
 socket                = /data/3306/mysql.sock
 pid-file              = /data/3306/mysql.pid
 
 ##Server id configuration
 server-id = 1
 
 ##Binlog log configuration
 log-bin               = on
 binlog_format         = statement
 binlog_cache_size     = 1M
 max_binlog_cache_size = 1M
 max_binlog_size       = 2G
 expire_logs_days      = 7
 log-bin               = /data/3306/binlog/mysql-bin
 
 ##Slow query log configuration
 log_queries_not_using_indexes
 long_query_time       = 1
 log-slow-queries      = /data/3306/slowlog/slow.log
 
 ##Relay log configuration
 relay-log             = /data/3306/relaylog/relay-bin
 relay-log-info-file   = /data/3306/relaylog/relay-log.info
 
 ##Error log configuration
 log-error = /data/3306/errlog/mysql_err.log
 
 ##Optimization configuration  
 skip-external-locking
 skip-name-resolv
 open_files_limit          = 1024
 max_connections           = 800
 max_connect_errors        = 3000
 max_allowed_packet        = 8M
 
 back_log                  = 600
 table_cache               = 614
 external-locking          = FALSE
 sort_buffer_size          = 2M
 join_buffer_size          = 1M
 thread_cache_size         = 100
 thread_concurrency        = 2
 
 query_cache_size          = 2M
 query_cache_limit         = 1M
 query_cache_min_res_unit  = 2k
 thread_stack              = 192k
 
 tmp_table_size            = 2M
 max_heap_table_size       = 2M
 key_buffer_size           = 16M
 read_buffer_size          = 1M
 read_rnd_buffer_size      = 1M
 bulk_insert_buffer_size   = 1M
 lower_case_table_names    = 1
 slave-skip-errors         = 1032,1062,1007
 
 innodb_additional_mem_pool_size  = 2M
 innodb_buffer_pool_size          = 2M
 innodb_file_per_table            = 0
 innodb_data_file_path            = ibdata1:256M:autoextend
 innodb_file_io_threads           = 4
 innodb_thread_concurrency        = 8
 innodb_flush_log_at_trx_commit   = 2
 innodb_log_buffer_size           = 2M
 innodb_log_file_size             = 2M
 innodb_log_files_in_group        = 3
 innodb_max_dirty_pages_pct       = 90
 innodb_lock_wait_timeout         = 120
 innodb_file_per_table            = 0
 
[mysqldump]
 quick
 max_allowed_packet = 2M

Write the startup script for 336 instances (/ data/3306/mysqld)

#!/bin/bash
#
# Define variables
RETVAL=0
Port=3306
User=root
Pass=chenliang
Pid=/data/3306/mysql.pid 
Sock=/data/3306/mysql.sock
My=/data/3306/my.cnf
Path=/apps/mysql-5.5.32/bin
 
# Determine the user to execute
if [ $UID -ne $RETVAL ];then
   echo "Must be root to run scripts"
   exit 1
fi
 
# Load the local functions 
[ -f /etc/init.d/functions ]&& source /etc/init.d/functions
 
# Define functions
start(){
    if [ ! -f "$Pid" ];then
     $Path/mysqld_safe --defaults-file=$My >/dev/null 2>&1 &
     RETVAL=$?
     if [ $RETVAL -eq 0 ];then
        action "Start MySQL [3306]" /bin/true
       else
        action "Start MySQL [3306]" /bin/false
       fi
    else
     echo "MySQL 3306 is running"
     exit 1
    fi
    return $RETVAL
}
 
stop(){
    if [ -f "$Pid" ];then
     $Path/mysqladmin -u$User -p$Pass -S $Sock shutdown
     RETVAL=$?
        if [ $RETVAL -eq 0 ];then
          action "Stop MySQL[3306]" /bin/true
         else
          action "Stop MySQL[3306]" /bin/false
       fi           
      else
       echo "MySQL [3306] is not running"
       exit 1
    fi
    return $RETVAL
}
 
status(){
    if [ -f "$Pid" ];then
       echo "MySQL [3306] is running"
      else
       echo "MySQL [3306] is not running"
    fi
    return $RETVAL
}
 
# Case call functions
case "$1" in
  start)
    start
    RETVAL=$?
    ;;
  stop)
    stop
    RETVAL=$?
    ;;
  restart)
    stop
    sleep 5
    start
    RETVAL=$?
    ;;
  status)
    status
    RETVAL=$?
    ;;
  *)
    echo "USAGE:$0{start|stop|restart|status}"
    exit 1
esac
 
# Scripts return values
exit $RETVAL

[root@db01 ~]# chmod 700 /data/3306/mysqld 
[root@db01 ~]# ll /data/3306/mysqld 
-rwx------ 1 chenliang chenliang 1546 Oct 22 19:53 /data/3306/mysqld

Configuration file for # configuration 3307 instance

[root@db01 ~]# cp -a /data/3306/my.cnf /data/3307/
[root@db01 ~]# sed -i 's#3306#3307#g' /data/3307/my.cnf
[root@db01 ~]# sed -i 's#server-id = 1#server-id = 2#g' /data/3307/my.cnf

Write startup script for 337 instances

[root@db01 ~]# cp -a /data/3306/mysqld /data/3307/
[root@db01 ~]# sed -i 's#3306#3307#g' /data/3307/mysqld

# Change / data/330{6,7}/ Directory owner / group to mysql

[root@db01 ~]# 
chown -R mysql:mysql /data/330{6,7}/

# Configure PATH environment variables

[root@db01 ~]# echo "PATH=/apps/mysql/bin:$PATH" >>/etc/bashrc 
[root@db01 ~]# source /etc/bashrc 
[root@db01 ~]# echo $PATH
/apps/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

# Set aliases for mysql commands

[root@db01 ~]# echo "alias mysql='mysql -A --default-character-set=utf8'" >>/etc/bashrc 
[root@db01 ~]# source /etc/bashrc 
[root@db01 ~]# alias mysql
alias mysql='mysql -A --default-character-set=utf8'

Start up 3307 instances and 3307 instances, and add boot self-start service

[root@db01 ~]# /data/3306/mysqld start
Start MySQL [3306]                                         [  OK  ]
[root@db01 ~]# /data/3307/mysqld start
Start MySQL [3307]                                         [  OK  ]
[root@db01 ~]# netstat -lntup|grep 330*
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      6715/mysqld
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      7522/mysqld
[root@db01 ~]# echo -ne "\n# Boot start mysql 3306 service. USER:chenliang TIME:2018-10-22\n/data/3306/mysqld start\n" >>/etc/rc.local 
[root@db01 ~]# echo -ne "\n# Boot start mysql 3306 service. USER:chenliang TIME:2018-10-22\n/data/3307/mysqld start\n" >>/etc/rc.local 
[root@db01 ~]# tail -5 /etc/rc.local
# Boot start mysql 3306 service. USER:chenliang TIME:2018-10-22
/data/3306/mysqld start
 
# Boot start mysql 3306 service. USER:chenliang TIME:2018-10-22
/data/3307/mysqld start

Set the root user password of 3306 and 3307 instances to chenliang

[root@db01 ~]# mysqladmin password chenliang -S /data/3306/mysql.sock 
[root@db01 ~]# mysqladmin password chenliang -S /data/3307/mysql.sock

# See if the corresponding instance can be logged in

[root@db01 ~]# mysql -uroot -p -S /data/3306/mysql.sock -e "show databases;"
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[root@db01 ~]# mysql -uroot -p -S /data/3307/mysql.sock -e "show databases;"
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+