MySQL多实例玩法

mysql多实例：
开启多个不同的服务端口，运行多个mysql服务进程。
应用场景：业务访问量不太大,节约成本

cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/bin/mysql_safe
mysqladmin = /usr/bin/mysqladmin
user = mysql
[mysqld1]
socket = /var/lib/mysql/mysql.sock
port  = 3306
pid-file = /var/lib/mysql/mysql.pid
datadir = /var/lib/mysql
user = mysql

[mysql2]
socket = /mnt/data/mysql/mysql.sock
port  = 3307
pid-file = /mnt/data/mysql/mysql.pid
datadir = /mnt/data/mysql
user = mysql

[mysql3]
socket = /mnt/data1/mysql/mysql.sock
port  = 3308
pid-file = /mnt/data1/mysql/mysql.pid
datadir = /mnt/data1/mysql
user = mysql

启动：
mysqld_multi  --config-file=/data/mysql/my_multi.cnf start 1,2,3


缺点：耦合性太高

  安装多实例mysql：
一，按装好mysql依赖：
yum -y install *-devel 

二，二进制安装：（编译安装）


mkdir /date/{3306,3307}/

cat /data/3306/mysql.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[msyqld]
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
table_cahe = 614
external-locking = FALST
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
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
key_buffer_size = 16M
read_buffer_size = 1M
read_md_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,1032
relicate-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_ji3306.err
pid-file=/data/3306/mysqld.pid





cat /data/3307/mysql.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock

[mysql]
no-auto-rehash

[msyqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3307/data
open_files_limit = 1024
back_log = 600 
(同上配置)


多实例启动文件mysql服务：
mysqld_safe  --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
mysqld_safe  --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null  &

停止：
msyqladmin -u root -p123456 -S /data/3306/mysql.sock shutdown

授权：
chown -R mysql.mysql  /data/ 

初始化：
mysql_install_db --basedir=/application/mysql  --datadir=/data/3306/data  --user=mysql


登陆数据库：
mysql -S /data/3306/mysql.sock
mysql -S /data/3307/mysql.sock