MySQL database multi-instance configuration steps detailed

[Root@mysql ~]# yum install vim gcc gcc-c++ wget tree lsof ncurses-devel cmake-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 June 22:09 MySQL
DRWX------. 2 MySQL mysql 4096 June 22:36 Performance_schema
DRWX------. 2 MySQL root 4096 June 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 June 22:37 MySQL
DRWX------. 2 MySQL mysql 4096 June 22:37 Performance_schema
DRWX------. 2 MySQL root 4096 June 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 <<byrd
> 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 <<byrd
> [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 configuration file content:

[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

Test Login:

[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)