mysql多实例安装

mysql下载和环境准备

这边编译mysql代码包的时候，会用到cmake，在官网下载下这俩个安装包，并安装ncurses-devel libaio-devel环境

wget https://cdn.mysql.com//Downloads/MySQL-5.5/mysql-5.5.58.tar.gz
wget https://cmake.org/files/v2.8/cmake-2.8.8.tar.gz
yum -y install ncurses-devel libaio-devel

　　

 进行安装

tar -zxvf cmake-2.8.8.tar.gz
cd cmake-2.8.8
./configure
gmake && gmake install

tar -zxvf mysql-5.5.58.tar.gz
cd mysql-5.5.58  

#然后我们这边用cmake来进行一下编译

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 \
-DENABLE_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 \
-DENABLE_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0

检查下是否有错误
echo $? 
0

如果是0代表没有错误进行安装
make && make install

安装完成后，做一下软连接
ln -s /application/mysql-5.5.32/ /application/mysql

 

mysql编译安装完成，我们这边来创建mysql用户和mysql用户组

groupadd mysql
useradd mysql -s /sbin/nologin -M -g mysql

创建mysql多实例的目录

mkdir -p /data/{3306,3307}/data
tree /data/
    /data/
    ├── 3306
    │   └── data
    └── 3307
        └── data

创建配置文件my.cnf，这边可以从support-files中来拷贝，也可以用复制我这边的配置文件。注意这边server-id要不一样。

[client]
port = 3306  
socket = /data/3306/mysql.sock  
  
[mysqld]  
user = mysql  
port = 3306  
socket = /data/3306/mysql.sock  
skip-external-locking  
key_buffer_size = 16K  
max_allowed_packet = 8M  
table_open_cache = 4  
sort_buffer_size = 1M  
join_buffer_size = 1M  
read_buffer_size = 1M  
read_rnd_buffer_size = 1M  
net_buffer_length = 2K  
thread_stack = 128K  
basedir = /application/mysql
datadir= /data/3306/data 
open_files_limit = 1024  
back_log = 600  
max_connections = 800  
max_connect_errors = 300  
table_cache = 614  
external-locking = FALSE  
thread_cache_size = 100  
thread_concurrency = 2  
query_cache_size = 2M  
query_cache_limit = 1M  
query_cache_min_res_unit = 2k  
thread_stack = 192K  
transaction_isolation = READ-COMMITTED  
tmp_table_size = 2M  
long_query_time = 1  
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  
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  
# Don't listen on a TCP/IP port at all. This can be a security enhancement,  
# if all processes that need to connect to mysqld run on the same host.  
# All interaction with mysqld must be made via Unix sockets or named pipes.  
# Note that using this option without enabling named pipes on Windows  
# (using the "enable-named-pipe" option) will render mysqld useless!  
#  
#skip-networking  
server-id = 1  
  
# Uncomment the following if you want to log updates  
#log-bin=mysql-bin  
  
# binary logging format - mixed recommended  
#binlog_format=mixed  
  
# Causes updates to non-transactional engines using statement format to be  
# written directly to binary log. Before using this option make sure that  
# there are no dependencies between transactional and non-transactional  
# tables such as in the statement INSERT INTO t_myisam SELECT * FROM  
# t_innodb; otherwise, slaves may diverge from the master.  
#binlog_direct_non_transactional_updates=TRUE  
  
  
# Uncomment the following if you are using InnoDB tables  
#innodb_data_home_dir = /mysql/mysql-5.5.32/data  
#innodb_data_file_path = ibdata1:10M:autoextend  
#innodb_log_group_home_dir = /mysql/mysql-5.5.32/data  
# You can set .._buffer_pool_size up to 50 - 80 %  
# of RAM but beware of setting memory usage too high  
#innodb_buffer_pool_size = 16M  
#innodb_additional_mem_pool_size = 2M  
# Set .._log_file_size to 25 % of buffer pool size  
#innodb_log_file_size = 5M  
#innodb_log_buffer_size = 8M  
#innodb_flush_log_at_trx_commit = 1  
#innodb_lock_wait_timeout = 50  
  
  
#innodb_additional_mem_pool_size = 4M  
#innodb_buffer_pool_szie = 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  
  
[mysql]  
no-auto-rehash  
  
[myisamchk]  
key_buffer_size = 8M  
sort_buffer_size = 8M  
  
[mysqlhotcopy]  
interactive-timeout  
  
[mysqld_safe]  
log-error = /data/3306/mysql_3306.err  
pid-file = /data/3306/mysqld.pid

创建启动文件mysql，以3306实例启动脚本为例:

#!/bin/bash  
  
#init  
port=3306  
mysql_user="root"  
mysql_pwd="123456"
CmdPath="/application/mysql/bin"  
mysql_sock="/data/${port}/mysql.sock"  
  
#startup  
function_start_mysql()  
{  
    if [ ! -e "$mysql_sock" ]; then  
        printf "Starting MySQL...\n"  
        /bin/bash ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1>/dev/null &  
    else  
        printf "MySQL is Running...\n"  
        exit  
    fi  
}  
  
#stop  
function_stop_mysql()  
{  
    if [ ! -e "$mysql_sock" ]; then  
        printf "MySQL is stopped..\n"  
    exit  
    else  
    printf "Stopping MySQL...\n"  
    ${CmdPath}/mysqladmin -u${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown  
    fi  
}  
  
#restart  
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

创建3306,和3307的配置文件和启动文件后，目前文件情况如下

tree /data/
/data/
├── 3306
│   ├── data
│   ├── my.cnf
│   └── mysql
└── 3307
    ├── data
    ├── my.cnf
    └── mysql

 授权目录一下mysql的目录， 授权mysql用户访问data目录，并且让启动脚本有执行的权限

chown -R mysql.mysql /data/
find /data/ -type f -name "mysql" | xargs chmod +x

 初始化多实例数据库文件，并启动

 

cd /application/mysql/scripts/
chmod +x mysql_install_db
./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

/data/3306/mysql start
Starting MySQL...
/data/3307/mysql start
Starting MySQL...

 

　　

 

验证是否正常启动

[root@master]# netstat -anpt |grep 330
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      2779/mysqld         
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3884/mysqld     

我这边遇到一个报错，说mysqld_safe error:log-error set to '/data/3306/mysql_3306.err',however file don't exists. Create writable for user 'mysql'.

原因是没有找到mysql_3306.err，或者你的这个文件权限不对不可被mysql账户写入

解决办法：

echo "" > /data/3306/mysql_3306.err
chown -R mysql:mysql /data/3306/mysql_3306.err

然后就可以启动mysql了。