MYSQL数据库多实例配置步骤详解

[root@Mysql ~]# yum install vim gcc gcc-c++ wget tree lsof ncurses-devel cmake libaio* -y #依赖库
[root@Mysql ~]# /usr/sbin/useradd mysql -s /sbin/nologin -M    #mysql用户
[root@Mysql ~]# cd /byrd/tools/
[root@Mysql tools]# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.37.tar.gz
[root@Mysql tools]# tar zxf mysql-5.5.37.tar.gz
[root@Mysql tools]# cd mysql-5.5.37/
[root@Mysql mysql-5.5.37]# cmake . -DCMAKE_INSTALL_PREFIX=/byrd/server/mysql-5.5.37 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DENABLED_LOCAL_INFILE=ON -DWITH_READLINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1
[root@Mysql mysql-5.5.37]# make && make install
[root@Mysql mysql-5.5.37]# ln -s /byrd/server/mysql-5.5.37/ /usr/local/mysql
[root@Mysql ~]# cd /usr/local/mysql/
[root@Mysql ~]# mkdir -p /data/{3306,3307}/data
[root@Mysql ~]# chown -R mysql.mysql /data
[root@Mysql mysql-5.5.37]#  /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3306/data/ --user=mysql
[root@Mysql mysql-5.5.37]# ll /data/3306/data/
total 12
drwx------. 2 mysql root  4096 Jun 15 22:09 mysql
drwx------. 2 mysql mysql 4096 Jun 15 22:36 performance_schema
drwx------. 2 mysql root  4096 Jun 15 22:09 test
[root@Mysql mysql]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3307/data/ --user=mysql
[root@Mysql mysql-5.5.37]# ll /data/3307/data/
total 12
drwx------. 2 mysql root  4096 Jun 15 22:37 mysql
drwx------. 2 mysql mysql 4096 Jun 15 22:37 performance_schema
drwx------. 2 mysql root  4096 Jun 15 22:37 test
[root@Mysql mysql-5.5.37]# cp /byrd/tools/mysql-5.5.37/support-files/my-medium.cnf /data/3307/my.cnf
[root@Mysql mysql-5.5.37]# cp /byrd/tools/mysql-5.5.37/support-files/my-medium.cnf /data/3306/my.cnf
[root@Mysql mysql-5.5.37]# sed -i 's#/tmp/mysql.sock#/data/3306/mysql.sock#g' /data/3306/my.cnf
[root@Mysql mysql-5.5.37]# sed -i 's#3306#3307#g' /data/3307/my.cnf
[root@Mysql mysql-5.5.37]# sed -i 's#= 1#= 2#g' /data/3307/my.cnf
[root@Mysql ~]# cat >>/data/3306/my.cnf < > 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
> log-error=/data/3306/mysql_3306.err
> BYRD
[root@Mysql ~]# cat >>/data/3307/my.cnf < > [mysqld_safe]
> 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
> log-error=/data/3307/mysql_3307.err
> BYRD
[root@Mysql mysql]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
[root@Mysql mysql]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &
[root@Mysql 3307]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name       
tcp        0      0 :::3307                     :::*                        LISTEN      8046/mysqld           
tcp        0      0 :::3306                     :::*                        LISTEN      4943/mysqld        
[root@Mysql 3307]#

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 = /usr/local/mysql
datadir = /data/3306/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
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
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
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
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 1
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3306/mysql_oldboy3306.err
pid-file=/data/3306/mysqld.pid

测试登陆：

[root@Mysql 3306]# /usr/local/mysql/bin/mysql -S /data/3306/mysql.sock
mysql> create database d3306;
Query OK, 1 row affected (0.53 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| d3306              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.13 sec)
[root@Mysql 3306]# /usr/local/mysql/bin/mysql -S /data/3306/mysql.sock
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.04 sec)