多实例安装mysql

2019独角兽企业重金招聘Python工程师标准>>>

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