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
[root@localhost data]# mysql -S /data/3307/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
缺点耦合性太高
转载于:https://my.oschina.net/goudingcheng/blog/551941