linux下mysql的安装以及多实例操作

terwe

一、linux系统基本信息[iyunv@db01 application]# cat /etc/redhat-release
CentOS release 6.7 (Final)
[iyunv@db01 application]# uname -a
Linux db01 2.6.32-573.el6.x86_64 #1 SMP Thu Jul 23 15:44:03 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
二、mysql的安装
[iyunv@db01 application]# yum install ncurses-devel -y
[iyunv@db01 application]# groupadd mysql
[iyunv@db01 application]# useradd mysql -s /sbin/nologin -M -g mysql
[iyunv@db01 application]# cat /etc/passwd|grep mysql
mysql:x:502:502::/home/mysql:/sbin/nologin               
[iyunv@db01 tools]# pwd
/home/oldboy/tools
#上传
#cmake-2.8.8.tar.gz
#mysql-5.5.32.tar.gz
[iyunv@db01 tools]# cd cmake-2.8.8
[iyunv@db01 cmake-2.8.8]# ./configure
[iyunv@db01 cmake-2.8.8]# gmake && gmake install
[iyunv@db01 cmake-2.8.8]#  which cmake           
/usr/local/bin/cmake
[iyunv@db01 cmake-2.8.8]# cd ..
[iyunv@db01 tools]#cd mysql-5.5.32
[iyunv@db01 mysql-5.5.32]#  cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 -DMYSQL_DATADIR=/application/mysql-5.5.32/data -DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0
[iyunv@db01 mysql-5.5.32]# make && make install
[iyunv@db01 /]#ln -s /application/mysql-5.5.32 /application/mysql
[iyunv@db01 /]# cp /home/oldboy/tools/mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf'? Y
[iyunv@db01 /]# cp /home/oldboy/tools/mysql-5.5.32/support-files/mysql.server /etc/init.d/mysqld
[iyunv@db01 /]# ls -ld /application/mysql
lrwxrwxrwx 1 root root 25 Aug 23 19:53 /application/mysql -> /application/mysql-5.5.32
[iyunv@db01 /]# ls -l /application/mysql/
total 76
drwxr-xr-x  2 root root  4096 Aug 23 19:52 bin
-rw-r--r--  1 root root 17987 Jul  2  2013 COPYING
drwxr-xr-x  3 root root  4096 Aug 23 19:52 data
drwxr-xr-x  2 root root  4096 Aug 23 19:53 docs
drwxr-xr-x  3 root root  4096 Aug 23 19:52 include
-rw-r--r--  1 root root  7470 Jul  2  2013 INSTALL-BINARY
drwxr-xr-x  3 root root  4096 Aug 23 19:52 lib
drwxr-xr-x  4 root root  4096 Aug 23 19:52 man
drwxr-xr-x 10 root root  4096 Aug 23 19:52 mysql-test
-rw-r--r--  1 root root  2496 Jul  2  2013 README
drwxr-xr-x  2 root root  4096 Aug 23 19:52 scripts
drwxr-xr-x 27 root root  4096 Aug 23 19:52 share
drwxr-xr-x  4 root root  4096 Aug 23 19:52 sql-bench
drwxr-xr-x  3 root root  4096 Aug 23 19:52 support-files
[iyunv@db01 /]# chmod +x /etc/init.d/mysqld
[iyunv@db01 /]# ls -l /etc/init.d/mysqld
-rwxr-xr-x 1 root root 10934 Aug 23 20:01 /etc/init.d/mysqld
[iyunv@db01 /]# echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
[iyunv@db01 /]# tail -1 /etc/profile
export PATH=/application/mysql/bin:$PATH
[iyunv@db01 /]# source /etc/profile
[iyunv@db01 /]# cd /application/mysql/scripts/
[iyunv@db01 scripts]# ./mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql
[iyunv@db01 /]# /etc/init.d/mysqld start
Starting MySQL... SUCCESS!
[iyunv@db01 /]# netstat -lntup|grep mysqld
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      25844/mysqld  
[iyunv@db01 /]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
[iyunv@db01 /]# netstat -lntup|grep mysqld
三、mysql多实例测试3、1前提条件[iyunv@db01 ~]# grep "db01" /etc/hosts
127.0.0.1   db01 localhost.localdomain localhost4 localhost4.localdomain4
[iyunv@db01 ~]# ls -ld /tmp/
drwxrwxrwt. 4 root root 4096 Aug 23 20:06 /tmp/

[iyunv@db01 /]# unzip data.zip

Archive:  data.zip

   creating: data/3306/

  inflating: data/3306/my.cnf        

  inflating: data/3306/mysql         

   creating: data/3307/

  inflating: data/3307/my.cnf        

  inflating: data/3307/mysql  

3、2data.zip中的内容

3、2、1data/3306/my.cnf

[client]

port            = 3306

socket          = /data/3306/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user    = mysql

port    = 3306

socket  = /data/3306/mysql.sock

basedir = /application/mysql

datadir = /data/3306/data

open_files_limit    = 1024

back_log = 600

max_connections = 800

max_connect_errors = 3000

table_cache = 614

external-locking = FALSE

max_allowed_packet =8M

sort_buffer_size = 1M

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

#default_table_type = InnoDB

thread_stack = 192K

#transaction_isolation = READ-COMMITTED

tmp_table_size = 2M

max_heap_table_size = 2M

long_query_time = 1

#log_long_format

#log-error = /data/3306/error.log

#log-slow-queries = /data/3306/slow.log

pid-file = /data/3306/mysql.pid

log-bin = /data/3306/mysql-bin

relay-log = /data/3306/relay-bin

relay-log-info-file = /data/3306/relay-log.info

binlog_cache_size = 1M

max_binlog_cache_size = 1M

max_binlog_size = 2M

expire_logs_days = 7

key_buffer_size = 16M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 1M

#myisam_sort_buffer_size = 1M

#myisam_max_sort_file_size = 10G

#myisam_max_extra_sort_file_size = 10G

#myisam_repair_threads = 1

#myisam_recover

lower_case_table_names = 1

skip-name-resolve

slave-skip-errors = 1032,1062

replicate-ignore-db=mysql

server-id = 1

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size = 32M

innodb_data_file_path = ibdata1:128M: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 = 4M

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

[mysqld_safe]

log-error=/data/3306/mysql_oldboy3306.err

pid-file=/data/3306/mysqld.pid

3、2、2data/3306/mysql

#!/bin/sh

#init

port=3306

mysql_user="root"

mysql_pwd="oldboy"

CmdPath="/application/mysql/bin"

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

#startup function

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

}

#stop function

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_pwd} -S /data/${port}/mysql.sock shutdown

   fi

}

#restart function

function_restart_mysql()

{

    printf "Restarting MySQL...\n"

    function_stop_mysql

    sleep 2

    function_start_mysql

}

case $1 in

start)

    function_start_mysql

;;

stop)

    function_stop_mysql

;;

restart)

    function_restart_mysql

;;

*)

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

esac

3、2、3data/3307/my.cnf

[client]

port            = 3307

socket          = /data/3307/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user    = mysql

port    = 3307

socket  = /data/3307/mysql.sock

basedir = /application/mysql

datadir = /data/3307/data

open_files_limit    = 1024

back_log = 600

max_connections = 800

max_connect_errors = 3000

table_cache = 614

external-locking = FALSE

max_allowed_packet =8M

sort_buffer_size = 1M

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

#default_table_type = InnoDB

thread_stack = 192K

#transaction_isolation = READ-COMMITTED

tmp_table_size = 2M

max_heap_table_size = 2M

#long_query_time = 1

#log_long_format

#log-error = /data/3307/error.log

#log-slow-queries = /data/3307/slow.log

pid-file = /data/3307/mysql.pid

#log-bin = /data/3307/mysql-bin

relay-log = /data/3307/relay-bin

relay-log-info-file = /data/3307/relay-log.info

binlog_cache_size = 1M

max_binlog_cache_size = 1M

max_binlog_size = 2M

expire_logs_days = 7

key_buffer_size = 16M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 1M

#myisam_sort_buffer_size = 1M

#myisam_max_sort_file_size = 10G

#myisam_max_extra_sort_file_size = 10G

#myisam_repair_threads = 1

#myisam_recover

lower_case_table_names = 1

skip-name-resolve

slave-skip-errors = 1032,1062

replicate-ignore-db=mysql

server-id = 3

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size = 32M

innodb_data_file_path = ibdata1:128M: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 = 4M

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

[mysqld_safe]

log-error=/data/3307/mysql_oldboy3307.err

pid-file=/data/3307/mysqld.pid

3、2、4data/3307/mysql

#!/bin/sh

#init

port=3307

mysql_user="root"

mysql_pwd="oldboy"

CmdPath="/application/mysql/bin"

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

#startup function

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

}

#stop function

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_pwd} -S /data/${port}/mysql.sock shutdown

   fi

}

#restart function

function_restart_mysql()

{

    printf "Restarting MySQL...\n"

    function_stop_mysql

    sleep 2

    function_start_mysql

}

case $1 in

start)

    function_start_mysql

;;

stop)

    function_stop_mysql

;;

restart)

    function_restart_mysql

;;

*)

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

esac

3、3初始化和启动多实例

[iyunv@db01 ~]# cd /application/mysql/scripts/

#下述初始化看到两个OK是正确的

[iyunv@db01 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql

[iyunv@db01 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

[iyunv@db01 scripts]# cd /

[iyunv@db01 /]# chown -R mysql.mysql /data/

[iyunv@db01 /]# ls -ld /data/

drwxr-xr-x 4 mysql mysql 4096 Oct 29  2013 /data/

[iyunv@db01 /]# find /data/ -type f -name "mysql"|xargs chmod +x

[iyunv@db01 /]# find /data/ -type f -name "mysql"|xargs ls -l

-rwx--x--x 1 mysql mysql 1307 Jul 15  2013 /data/3306/mysql

-rwx--x--x 1 mysql mysql 1307 Jul 21  2013 /data/3307/mysql

#降低mysql启动文件的权限，因为有密码，被其他用户看到很不安全。

[iyunv@db01 /]# find /data/ -type f -name "mysql"|xargs chmod 700

[iyunv@db01 /]# find /data/ -type f -name "mysql"|xargs ls -l

-rwx------ 1 mysql mysql 1307 Jul 15  2013 /data/3306/mysql

-rwx------ 1 mysql mysql 1307 Jul 21  2013 /data/3307/mysql

[iyunv@db01 /]# find /data/ -type f -name "mysql"|xargs chown root.root

[iyunv@db01 /]#  find /data/ -type f -name "mysql"|xargs ls -l

-rwx------ 1 root root 1307 Jul 15  2013 /data/3306/mysql

-rwx------ 1 root root 1307 Jul 21  2013 /data/3307/mysql

[iyunv@db01 /]# /data/3306/mysql start

Starting MySQL...

[iyunv@db01 /]# /data/3307/mysql start

Starting MySQL...

#不能启动的话，记得看错误日志

[iyunv@db01 3307]# netstat -lntup|grep mysql

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      40893/mysqld        

tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      41618/mysqld   

[iyunv@db01 /]# pkill mysql

[iyunv@db01 /]# netstat -lntup|grep mysql