[root ~]# sudo su -
[root ~]# pkill mysqld [root ~]# ps -ef |grep mysql root 2936 2910 0 04:16 pts/0 00:00:00 grep mysql [root ~]# rm -f /etc/init.d/mysqld [root ~]# 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