文档章节

mysql多实例的作用和问题

泡海椒
 泡海椒
发布于 2016/01/03 15:46
字数 1767
阅读 58
收藏 0

[root@localhost ~]# sudo su -
[root@localhost ~]# pkill mysqld
[root@localhost ~]# ps -ef |grep mysql
root      2936  2910  0 04:16 pts/0    00:00:00 grep mysql
[root@localhost ~]# rm -f /etc/init.d/mysqld
[root@localhost ~]# mkdir -p /data/{3306,3307}/data
[root@localhost ~]tree /data
/data
|--3306
|  |--data数据文件
|  |--my.cnf
|  |--mysql启动文件
|--3307
|  |--data
|  |--my.cnf
|  |--mysql

3306 my.cnf


[client]
port = 3306
socket =/data/3306/mysql.socket
[mysqld]
datadir=/data/3306/data
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
port = 3306
socket = /data/3306/mysql.sock
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 1
server-id = 1
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 256M
[mysql]
no-auto-rehash
prompt=\\u@\\d \\R:\\m>
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
3307 my.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock
[mysqld]
datadir=/data/3307/
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
port = 3307
socket = /data/3307/mysql.sock
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 1
server-id = 3
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 256M
[mysql]
no-auto-rehash
prompt=\\u@\\d \\R:\\m>
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192



 ./mysql_install_db --basedir=/application/mysql --datadir=/data/3308/data --user=mysql

mysqld_safe --defaults-file=/data/3308/my.cnf 2>&1 > /dev/null &
mysql -uroot -S /data/3308/mysql.sock




shell>mysqld_multi start 17
shell>mysqld_multi stop 8,10-13
mysqld_multi --config-file=/data/mysql/my_multi.cnf star 1,2,3,4,5,6
mysql多实例的作用和问题
有效的利用服务器资源
节约资源
问题(并发的时候会消耗资源,导致其他实例资源有问题)
一个机器打开多个端口运行多个mysql进程共用一套安装程序,配置文件、启动可以相同和不同,数据文件不同2
使用场景
并发访问不大
资金紧张的公司
门户网站01 :56
[root@localhost ~]# sudo su -
[root@localhost ~]# pkill mysqld
[root@localhost ~]# ps -ef |grep mysql
root      2936  2910  0 04:16 pts/0    00:00:00 grep mysql
[root@localhost ~]# rm -f /etc/init.d/mysqld
[root@localhost ~]# mkdir -p /data/{3306,3307}/data
[root@localhost ~]tree /data
/data
|--3306
|  |--data数据文件
|  |--my.cnf
|  |--mysql启动文件
|--3307
|  |--data
|  |--my.cnf
|  |--mysql



[client]
port = 3306
socket =/data/3306/mysql.socket
[mysqld]
user=mysql
port=3306
log_error=/data/3306/error.log
slow-query-log=/data/3306/slow.log
pid-file=/data/3306/mysql.pid
log-bin=/data/3306/mysql-bin
relay-log=/data/3306/relay-log.info
datadir=/data/3306/data
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
port = 3306
socket = /data/3306/mysql.sock
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 1
server-id = 1
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 256M
[mysql]
no-auto-rehash
prompt=\\u@\\d \\R:\\m>
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192


3307



[client]
port = 3307
socket = /data/3307/mysql.sock
[mysqld]
datadir=/data/3307/data
log_error=/data/3307/error.log
slow-query-log=/data/3307/slow.log
pid-file=/data/3307/mysql.pid
log-bin=/data/3307/mysql-bin
relay-log=/data/3307/relay-log.info
user       = mysql
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
port = 3307
socket = /data/3307/mysql.sock
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
binlog_format=mixed
slow_query_log
long_query_time = 1
server-id = 3
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 256M
[mysql]
no-auto-rehash
prompt=\\u@\\d \\R:\\m>
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192


[root@localhost 3307]# tree /data
[root@localhost 3306]# tree /data
/data
├── 3306
│?? ├── data
│?? ├── my.cnf
│?? └── mysql
└── 3307
    ├── data
    ├── my.cnf
    └── mysql

    
    [root@localhost 3307]# chown -R mysql.mysql /data
[root@localhost 3306]# find /data/ -type f -name "mysql" |xargs ls -l
-rwxrwxrwx. 1 root  root   0 12月 26 22:45 /data/3306/mysql
-rwxrwxrwx. 1 mysql mysql 21 12月 26 22:39 /data/3307/mysql
[root@localhost 3306]# tail -1 /etc/profile
export PATH=/application/mysql/bin:$PATH

[root@localhost 3306]# cd /application/mysql/scripts

[root@localhost 3306]# cd /application/mysql/scripts
[root@localhost scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK
Please report any problems with the /application/mysql/scripts/mysqlbug script!


[root@localhost scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
[root@localhost scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK

Please report any problems with the /application/mysql/scripts/mysqlbug script!

[root@localhost data]# cd /data/3306/data
[root@localhost data]# tree
.
├── mysql
│?? ├── columns_priv.frm
│?? ├── columns_priv.MYD
│?? ├── columns_priv.MYI
│?? ├── db.frm
│?? ├── db.MYD
│?? ├── db.MYI
│?? ├── event.frm
│?? ├── event.MYD
│?? ├── event.MYI
│?? ├── func.frm
│?? ├── func.MYD
│?? ├── func.MYI
│?? ├── general_log.CSM
│?? ├── general_log.CSV
│?? ├── general_log.frm
│?? ├── help_category.frm
│?? ├── help_category.MYD
│?? ├── help_category.MYI
│?? ├── help_keyword.frm
│?? ├── help_keyword.MYD
│?? ├── help_keyword.MYI
│?? ├── help_relation.frm
│?? ├── help_relation.MYD
│?? ├── help_relation.MYI
│?? ├── help_topic.frm
│?? ├── help_topic.MYD
│?? ├── help_topic.MYI
│?? ├── host.frm
│?? ├── host.MYD
│?? ├── host.MYI
│?? ├── ndb_binlog_index.frm
│?? ├── ndb_binlog_index.MYD
│?? ├── ndb_binlog_index.MYI
│?? ├── plugin.frm
│?? ├── plugin.MYD
│?? ├── plugin.MYI
│?? ├── proc.frm
│?? ├── proc.MYD
│?? ├── proc.MYI
│?? ├── procs_priv.frm
│?? ├── procs_priv.MYD
│?? ├── procs_priv.MYI
│?? ├── proxies_priv.frm
│?? ├── proxies_priv.MYD
│?? ├── proxies_priv.MYI
│?? ├── servers.frm
│?? ├── servers.MYD
│?? ├── servers.MYI
│?? ├── slow_log.CSM
│?? ├── slow_log.CSV
│?? ├── slow_log.frm
│?? ├── tables_priv.frm
│?? ├── tables_priv.MYD
│?? ├── tables_priv.MYI
│?? ├── time_zone.frm
│?? ├── time_zone_leap_second.frm
│?? ├── time_zone_leap_second.MYD
│?? ├── time_zone_leap_second.MYI
│?? ├── time_zone.MYD
│?? ├── time_zone.MYI
│?? ├── time_zone_name.frm
│?? ├── time_zone_name.MYD
│?? ├── time_zone_name.MYI
│?? ├── time_zone_transition.frm
│?? ├── time_zone_transition.MYD
│?? ├── time_zone_transition.MYI
│?? ├── time_zone_transition_type.frm
│?? ├── time_zone_transition_type.MYD
│?? ├── time_zone_transition_type.MYI
│?? ├── user.frm
│?? ├── user.MYD
│?? └── user.MYI
├── performance_schema
│?? ├── cond_instances.frm
│?? ├── db.opt
│?? ├── events_waits_current.frm
│?? ├── events_waits_history.frm
│?? ├── events_waits_history_long.frm
│?? ├── events_waits_summary_by_instance.frm
│?? ├── events_waits_summary_by_thread_by_event_name.frm
│?? ├── events_waits_summary_global_by_event_name.frm
│?? ├── file_instances.frm
│?? ├── file_summary_by_event_name.frm
│?? ├── file_summary_by_instance.frm
│?? ├── mutex_instances.frm
│?? ├── performance_timers.frm
│?? ├── rwlock_instances.frm
│?? ├── setup_consumers.frm
│?? ├── setup_instruments.frm
│?? ├── setup_timers.frm
│?? └── threads.frm
└── test

3 directories, 90 files



    mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &
    mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &
    mysqld_multi --defaults-extra-file=/data/3307/my.cnf start 3307
    [root@localhost 3307]#  netstat -lntup|grep 330
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      4975/mysqld         
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3211/mysqld         

[root@localhost data]# mysql -S /data/3306/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> system ls
data       my.cnf  mysql-3307.log    mysql-bin.000002  mysql.pid   slow.log
error.log  mysql   mysql-bin.000001  mysql-bin.index   mysql.sock
mysql> system -S /data/3307/mysql.sock
[root@localhost 3307]#  find /data -type f -name "mysql" -exec chmod 700 {} \;
[root@localhost 3307]#  find /data -type f -name "mysql" -exec chown root.root {} \;



mysql服务没有起来
可能造成的原因
1.如果没有显示mysql端口,请稍微等几秒钟再看, mysql服务启动有些慢
2.如果还不行,请查看错误日志,错误日志路径在my.cnf里

多实例停止mysql服务实质
mysqladmin -uroot -padmin -S /data/3306/mysql.socket shutdown
mysqladmin -uroot -padmin -S /data/3307/mysql.socket shutdown
mysqladmin -uroot -padmin -S /data/3307/mysql.socket shutdown

shell>mysqld_multi start 17
shell>mysqld_multi stop 8,10-13
mysqld_multi --config-file=/data/mysql/my_multi.cnf star 1,2,3,4,5,6
缺点耦合性太高
[root@localhost 3307]# mysql -uroot -padmin -S /data/3307/mysql.sock
增加一个mysql实例

[root@localhost 3307]# cd ~
[root@localhost ~]# mkdir /data/3308/data -p
[root@localhost ~]# cp /data/3306/my.cnf /data/3308/
[root@localhost ~]# cp /data/3306/mysql /data/3308/
[root@localhost ~]# chown -R mysql.mysql /data/3308
[root@localhost ~]# cd /data/3308
[root@localhost 3308]# ls
data  my.cnf  mysql
[root@localhost 3308]# vi my.cnf server-id 4
[root@localhost 3308]# vi my.cnf
:g/3306/s//3308/g
ot@localhost 3308]# cd /application/mysql/scripts/
[root@localhost scripts]#
[root@localhost scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3308/data --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK
mysqld_safe --defaults-file=/data/3308/my.cnf 2>&1 > /dev/null &
[root@localhost scripts]# netstat -lntup|grep 330
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      4975/mysqld         
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3211/mysqld         
[root@localhost scripts]# cd /data/3308
[root@localhost 3308]# ls
data  error.log  my.cnf  mysql
[root@localhost 3308]# less error.log
[root@localhost 3308]# netstat -lntup|grep 330
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      4975/mysqld         
tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      6294/mysqld         
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3211/mysqld         
 

#!/bin/sh
case $1 in
   start ) /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &;;
   stop)  /usr/local/mysql/bin/mysqladmin -uroot -p -h127.0.0.1 -S /data/3307/mysql.socket shutdown;;
esac

 

© 著作权归作者所有

上一篇: 3306
下一篇: ubantu安装java
泡海椒
粉丝 11
博文 282
码字总数 288344
作品 0
成都
程序员
私信 提问
MySQL5.5 多实例安装

一.多实例介绍 1.1什么是MySQL多实例 简单的说,就是在一台服务器上开启不同的端口,运行多个MySQL服务进程,这些服务进程通过 不同的socket监听不同的服务端口来提供各自的服务 1.2MySQL多实...

飞天喜欢yu
2017/07/29
0
0
MySQL多实例(一)

一、基本概念 1、MySQL多实例 就是在一台机器上面开启多个不同的端口,运行多个MySQL服务进程。这些MySQL多实例公用一套安装程序,使用不同的(也可以是相同的)配置文件,启动程序,数据文件。...

技术小阿哥
2017/11/28
0
0
mysql 多实例应用配置部署指南

一,Mysql 多实例的作用的问题 1,有效利用服务器的资源 当单个服务器资源有剩余的时候,可以充分利用剩余的资源以提供更多的服务 2,节约服务器的资源 当公司资源紧张,但是数据库各自需要独...

crazy_charles
2016/06/08
0
0
MYSQL企业级应用(一)安装与部署

1.1 mysql介绍mysql属于传统关系型的数据库产品,它开放式架构使得用户选择性很强,同时社区开发与维护人员众多,其功能稳定,性能卓越,且在遵守GPL协议的前提下,可以免费使用与修改,也为...

余8520
2017/12/26
0
0
运维的福音,深度剖析腾讯云自动运维平台

嘉宾介绍:祝海强,腾讯高级工程师。8年数据库经历,曾就职于第九城市、返利网任高级DBA。目前负责腾讯云CDB for MySQL运维团队,对MySQL、MSSQL等数据库运维、调优诊断具有丰富的经验。 一、...

OSC源创君
2016/11/17
3.4K
7

没有更多内容

加载失败,请刷新页面

加载更多

任务调度-第三方库Quartz实现分布式任务管理与调度

1. 为什么要用第三方库Quartz来实现分布式任务管理和调度? 首先管理的目的是通过集群多节点的管理提供容错,调度的目的是保证同一任务只会被完整执行一次;之前分享过的任务调度-单体应用定...

秋日芒草
8分钟前
0
0
Mysql Explain Type

前言 当我们执行sql,一般都会用Explain来查看sql的效率如何。今天在看sql执行效率的时候,忘记了其中Type的意思,现在在此记录一下。 效率 这里的type指的是访问类型,各个效率高低如下: ...

无敌小杰杰
16分钟前
0
0
外部浏览器网页复制公众号无法自动唤起微信并关注怎么办?

现在有很多用户在外部浏览器网页复制公众号时无法自动唤起微信并关注,这是因为第三方浏览器打开微信的接口,微信只给部分合作平台开放了接口权限,任何第三方想调用只能是通过一些技术手段来...

qjniop
21分钟前
0
0
建造者模式

建造者模式(Builder Pattern) 也叫生成器模式,其定义如下: Separate the construction of a complex object from its representation so that the same construction process can create d......

无知的小狼
26分钟前
0
0
距离计算方法

1、欧式距离(欧几里得距离) 欧式距离是最易理解的距离定义,即各坐标点的坐标之差的平方和相加,然后开根号。 二维平面上点 与点 之间的距离公式是: n维空间上点 和点 之间的距离公式是:...

城北徐公美
28分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部