本次安装环境为：

系 统：centos 6 X86_X64

内 存：不低于4G

mysql 版本：mysql-boost-5.7.18.tar.gz

本文主要讲解在同主机上部署mysql多实例

门户网站应用mysql 多实例场景

百度搜索引擎的数据库就是多实例，一般是从库，列如某个部门的例子，IBM 48核CPU。内存96G，跑3-4个实例；sina 网也是用的多实例，内存48G左右。门户网站使用多实例的目的是配硬件好的服务器，节省idc机柜的空间，同时，跑多实例让硬件资源不浪费，针对一般的服务器最多4个实例即可，1-2两个最合适。

多实例可以是多个配置文件对应多实例也可以一个配置文件对应多个实例

多实例缺点：

1、耦合度太高，一个配置文件不好管理。工作开发和运维的统一原则；降低耦合度

mysql 相关软件包：

yum -y install ncurses-devel libaio-devel cmake

安装需要mysql 需要创建用户和组

useradd mysql -s /sbin/nologin -M

下载mysql 5.7 安装包

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.18.tar.gz

解压安装包

tar -zxf mysql-boost-5.7.18.tar.gz && cd mysql-5.7.18

cmake\

-DCMAKE_INSTALL_PREFIX=/u01/app/mysql/db\

-DSYSCONFDIR=/u01/app/mysql/db/etc\

-DWITH_BOOST=./boost/boost_1_59_0 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DWITH_INNODB_MEMCACHED=1 \

-DWITH_READLINE=1\

-DENABLED_LOCAL_INFILE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DEXTRA_CHARSETS=all\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci

make -j 24

make install

#创建数据目录

mkdir -p /data/mysql/engine/innolog

mkdir -p /data/mysql/data

mkdir -p /data/mysql/engine/innodata

mkdir -p /data/mysql/engine/undo

mkdir -p /data/mysql/log/mysql-binlog

mkdir -p /data/mysql/log/mysql-relaylog

chown mysql. /data -R

#创建mysql 配置文件目录

mkdir /u01/app/mysql/db/etc

配置my.cnf

[mysqld]

#-------------------------------------basic_my.conf-------------------------------------------------------------

#5.7

#author: duxing

#modify: 20160902

#version: 0.8

#------------------basic-------------------------------#

user=mysql

basedir = /u01/app/mysql/db

datadir = /data/mysql/data

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

socket=/data/mysql/mysql.sock

default-storage-engine = INNODB

transaction_isolation = REPEATABLE-READ

binlog_cache_size = 32M

max_binlog_size=512M

binlog_format=row

character-set-server = utf8

port=3306

server-id= 2

#sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'

explicit_defaults_for_timestamp = true

log-error = /data/mysql/mysql-error.log

slow_query_log

long_query_time = 10

log_warnings

#------------------basic-------------------------------#

#---------------directroy------------------------------#

innodb_log_group_home_dir=/data/mysql/engine/innolog

innodb_data_home_dir=/data/mysql/engine/innodata

innodb_data_file_path = ibdata1:500M;ibdata2:150M:autoextend

innodb_undo_directory=/data/mysql/engine/undo

log-bin = /data/mysql/log/mysql-binlog/mysql-binlog

log-bin-index = /data/mysql/log/mysql-binlog/mysql-binlog.index

expire-logs-days=5

relay_log = /data/mysql/log/mysql-relaylog/mysql-relay-bin

relay_log_index= /data/mysql/log/mysql-relaylog/mysql-relay-bin.index

#relay_log_info_file=/data/mysql/log/mysql-relaylog/relay-log.info

#---------------directroy------------------------------#

#----------------engine--------------------------------#

innodb_file_per_table=1

innodb_api_enable_binlog=1

innodb_buffer_pool_size=256M

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 16M

innodb_log_file_size = 512M

innodb_log_files_in_group = 4

innodb_max_dirty_pages_pct = 75

innodb_lock_wait_timeout = 120

innodb_buffer_pool_instances=2

innodb_force_recovery=0#recover

innodb_purge_threads=4#undo purge

innodb_write_io_threads = 16

innodb_read_io_threads = 16

innodb_io_capacity=1200

innodb_old_blocks_pct=37

innodb_old_blocks_time=1000

innodb_undo_tablespaces=12

innodb_undo_logs=40

innodb_commit_concurrency= 1000#commit/s

innodb_thread_concurrency = 1000 #use for cpu mutex

innodb_thread_sleep_delay= 200#ms delay time

#innodb_use_native_aio=1

#----------------engine--------------------------------#

# ------------global cache -------------------------#

read_buffer_size = 32M

read_rnd_buffer_size = 128M

sort_buffer_size = 64M

join_buffer_size = 64M

query_cache_size = 128M

query_cache_limit = 4M

tmp_table_size = 128M

# ------------global cache -------------------------#

#----------------replicat-----------------------------#

#read_only=1

relay_log_purge=0

log_slave_updates=1

slave_parallel_workers=4

log_bin_trust_function_creators=1

binlog_checksum = CRC32

slave_allow_batching = 1

master_verify_checksum = 1

slave_sql_verify_checksum = 1

binlog_rows_query_log_events = 1

gtid-mode=on

enforce-gtid-consistency=1

sync-master-info=1

relay_log_purge = 1#relaylog的自动修复

relay_log_recovery = 1

master_info_repository = TABLE

relay_log_info_repository = TABLE

#set global rpl_semi_sync_master_enabled = 1

#set global rpl_semi_sync_slave_enabled =1

#rpl_semi_sync_master_timeout=200

#----------------replicat-----------------------------#

#-----------------thread------------------------------#

table_open_cache = 2048#reduce open descriptor

thread_stack = 256K# *thead_num < useable mem

thread_cache_size= 128#reuse thread in mem

#-----------------thread------------------------------#

#----------------network-----------------------------#

back_log= 512

#skip-networking

#skip-grant-tables

skip-name-resolve

max_connections = 3000

max_connect_errors = 30

table_open_cache= 4096

max_allowed_packet = 64M

max_heap_table_size = 512M

#----------------network-----------------------------#

#-----------------------timeout------------------------#

connect-timeout = 10

wait-timeout = 8000

interactive-timeout = 8000

slave-net-timeout= 60

net_read_timeout = 30

net_write_timeout= 60

net_retry_count = 10

net_buffer_length= 16384

#-----------------------timeout------------------------#

[mysql]

auto-rehash

[client]

socket=/data/mysql/mysql.sock

port=3306

删除 /etc/my.cnf

/u01/app/mysql/db/etc

初始化之前需要查看error 日志

cat /data/mysql/mysql-error.log

cd /u01/app/mysql/db

./bin/mysqld --initialize --basedir=/u01/app/mysql/db --datadir=/data/mysql/data --user=mysql
# --initialize-insecure option 使用这个初始化数据库之后不再需要登录后进行alter user

#cat /data/mysql/mysql-error.log

/u01/app/mysql/db/bin/mysqld_safe --skip-grant-tables &

update mysql.user set authentication_string=password('123.com') where user='root' and Host = 'localhost';

#启动mysql 服务

/u01/app/mysql/db/support-files/mysql.server restart

##需要重新更改密码否则无法正常使用库

alter user 'root'@'localhost' identified by '123.com';

#设置开机自启动

cp /u01/app/mysql/db/support-files/mysql.server /etc/init.d/mysql.server-5.7

chmod +x /etc/init.d/mysql.server-5.7

chkconfig --addmysql.server-5.7

#设置环境变量

vim ~/.bashrc

#set mysql

NHOME=/u01/app/mysql/db

PATH=$PATH:$NHOME/bin

source ~/.bashrc

mysql 多实例可以写两个不同的配置文件与不同的端口 初始化库时候直接更改即可