mysql的多實例有兩種方式可以實現，兩種方式各有利弊。
-
第一種是使用多個配置文件啟動不同的進程來實現多實例，這種方式的優勢邏輯簡單，配置簡單，缺點是管理起來不太方便。
-
第二種是通過官方自帶的mysqld_multi使用單獨的配置文件來實現多實例，這種方式定制每個實例的配置不太方面，優點是管理起來很方便，集中管理。
下面就分別來實戰這兩種多實例的配置文件和管理
先來學習第一種使用多個配置文件啟動多個不同進程的情況：
環境介紹：
mysql 版本：5.1.50
操作系統：SUSE 11
mysql實例數：3個
實例占用端口分別為：3306、3307、3308
創建mysql用戶：
/usr/sbin/groupadd?mysql /usr/sbin/useradd?-g?mysql?mysql
編譯安裝mysql：
tar?xzvf?mysql-5.1.50.tar.gz cd?mysql-5.1.50 ./configure?'--prefix=/usr/local/mysql'?'--with-charset=utf8'?'--with-extra-charsets=complex'?'--with-pthread'?'--enable-thread-safe-client'?'--with-ssl'?'--with-client-ldflags=-all-static'?'--with-mysqld-ldflags=-all-static'?'--with-plugins=partition,innobase,blackhole,myisam,innodb_plugin,heap,archive'?'--enable-shared'?'--enable-assembler' make make?install
初始化數據庫：
/usr/local/mysql/bin/mysql_install_db?--basedir=/usr/local/mysql?--datadir=/data/dbdata_3306?--user=mysql /usr/local/mysql/bin/mysql_install_db?--basedir=/usr/local/mysql?--datadir=/data/dbdata_3307?--user=mysql /usr/local/mysql/bin/mysql_install_db?--basedir=/usr/local/mysql?--datadir=/data/dbdata_3308?--user=mysql
創建配置文件
vim /data/dbdata_3306/my.cnf
3306的配置文件如下：
[client] port?=?3306 socket?=?/data/dbdata_3306/mysql.sock [mysqld] datadir=/data/dbdata_3306/ skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port?=?3306 socket?=?/data/dbdata_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
vim /data/dbdata_3307/my.cnf
3307的配置文件如下：
[client] port?=?3307 socket?=?/data/dbdata_3307/mysql.sock [mysqld] datadir=/data/dbdata_3307/ skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port?=?3307 socket?=?/data/dbdata_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?=?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
vim /data/dbdata_3308/my.cnf
3308的配置文件如下：
[client] port?=?3308 socket?=?/data/dbdata_3308/mysql.sock [mysqld] datadir=/data/dbdata_3308/ skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port?=?3308 socket?=?/data/dbdata_3308/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
創建自動啟動文件
vim /data/dbdata_3306/mysqld
3306的啟動文件如下：
#!/bin/bash mysql_port=3306 mysql_username="admin" mysql_password="password" function_start_mysql() { printf?"Starting?MySQL... " /bin/sh?/usr/local/mysql/bin/mysqld_safe?--defaults-file=/data/dbdata_${mysql_port}/my.cnf?2>&1?>?/dev/null?& } function_stop_mysql() { printf?"Stoping?MySQL... " /usr/local/mysql/bin/mysqladmin?-u?${mysql_username}?-p${mysql_password}?-S?/data/dbdata_${mysql_port}/mysql.sock?shutdown } function_restart_mysql() { printf?"Restarting?MySQL... " function_stop_mysql function_start_mysql } function_kill_mysql() { kill?-9?$(ps?-ef?|?grep?'bin/mysqld_safe'?|?grep?${mysql_port}?|?awk?'{printf?$2}') kill?-9?$(ps?-ef?|?grep?'libexec/mysqld'?|?grep?${mysql_port}?|?awk?'{printf?$2}') } case?$1?in start) function_start_mysql;; stop) function_stop_mysql;; kill) function_kill_mysql;; restart) function_stop_mysql function_start_mysql;; *) echo?"Usage:?/data/dbdata_${mysql_port}/mysqld?{start|stop|restart|kill}";; esac
vim /data/dbdata_3307/mysqld
3307的啟動文件如下：
#!/bin/bash mysql_port=3307 mysql_username="admin" mysql_password="password" function_start_mysql() { printf?"Starting?MySQL... " /bin/sh?/usr/local/mysql/bin/mysqld_safe?--defaults-file=/data/dbdata_${mysql_port}/my.cnf?2>&1?>?/dev/null?& } function_stop_mysql() { printf?"Stoping?MySQL... " /usr/local/mysql/bin/mysqladmin?-u?${mysql_username}?-p${mysql_password}?-S?/data/dbdata_${mysql_port}/mysql.sock?shutdown } function_restart_mysql() { printf?"Restarting?MySQL... " function_stop_mysql function_start_mysql } function_kill_mysql() { kill?-9?$(ps?-ef?|?grep?'bin/mysqld_safe'?|?grep?${mysql_port}?|?awk?'{printf?$2}') kill?-9?$(ps?-ef?|?grep?'libexec/mysqld'?|?grep?${mysql_port}?|?awk?'{printf?$2}') } case?$1?in start) function_start_mysql;; stop) function_stop_mysql;; kill) function_kill_mysql;; restart) function_stop_mysql function_start_mysql;; *) echo?"Usage:?/data/dbdata_${mysql_port}/mysqld?{start|stop|restart|kill}";; esac
vim /data/dbdata_3308/mysqld
3308的啟動文件如下：
#!/bin/bash mysql_port=3308 mysql_username="admin" mysql_password="password" function_start_mysql() { printf?"Starting?MySQL... " /bin/sh?/usr/local/mysql/bin/mysqld_safe?--defaults-file=/data/dbdata_${mysql_port}/my.cnf?2>&1?>?/dev/null?& } function_stop_mysql() { printf?"Stoping?MySQL... " /usr/local/mysql/bin/mysqladmin?-u?${mysql_username}?-p${mysql_password}?-S?/data/dbdata_${mysql_port}/mysql.sock?shutdown } function_restart_mysql() { printf?"Restarting?MySQL... " function_stop_mysql function_start_mysql } function_kill_mysql() { kill?-9?$(ps?-ef?|?grep?'bin/mysqld_safe'?|?grep?${mysql_port}?|?awk?'{printf?$2}') kill?-9?$(ps?-ef?|?grep?'libexec/mysqld'?|?grep?${mysql_port}?|?awk?'{printf?$2}') } case?$1?in start) function_start_mysql;; stop) function_stop_mysql;; kill) function_kill_mysql;; restart) function_stop_mysql function_start_mysql;; *) echo?"Usage:?/data/dbdata_${mysql_port}/mysqld?{start|stop|restart|kill}";; esac
啟動3306、3307、3308的mysql
/data/dbdata_3306/mysqld?start /data/dbdata_3307/mysqld?start /data/dbdata_3308/mysqld?start
更改原來密碼（處于配置文件考慮，還需要刪除系統中沒有密碼的帳號，這里省略了）：
/usr/local/mysql/bin/mysqladmin?-uroot?password?'password'?-S?/data/dbdata_3306/mysql.sock /usr/local/mysql/bin/mysqladmin?-uroot?password?'password'?-S?/data/dbdata_3307/mysql.sock /usr/local/mysql/bin/mysqladmin?-uroot?password?'password'?-S?/data/dbdata_3308/mysql.sock
登錄測試并創建關閉mysql的帳號權限，mysqld腳本要用到！
/usr/local/mysql/bin/mysql?-uroot?-ppassword?-S?/data/dbdata_3308/mysql.sock GRANT?SHUTDOWN?ON?*.*?TO?'admin'@'localhost'?IDENTIFIED?BY?'password'; flush?privileges; /usr/local/mysql/bin/mysql?-uroot?-ppassword?-S?/data/dbdata_3308/mysql.sock GRANT?SHUTDOWN?ON?*.*?TO?'admin'@'localhost'?IDENTIFIED?BY?'password'; flush?privileges; /usr/local/mysql/bin/mysql?-uroot?-ppassword?-S?/data/dbdata_3308/mysql.sock GRANT?SHUTDOWN?ON?*.*?TO?'admin'@'localhost'?IDENTIFIED?BY?'password'; flush?privileges;
創建了admin帳號以后腳本的stop功能和restart功能就正常了！
更改環境變量
vim?/etc/profile?添加下面一行內容 PATH=${PATH}:/usr/local/mysql/bin/ source?/etc/profile
添加到自動啟動
vim?/etc/init.d/boot.local /data/dbdata_3306/mysqld?start /data/dbdata_3307/mysqld?start /data/dbdata_3308/mysqld?start
如果是rhel或者centos系統的話自啟動文件/etc/rc.local
?管理的話，在本地都是采用 -S /data/dbdata_3308/mysql.sock，如果在遠程可以通過不同的端口連接上去坐管理操作。其他的和單實例的管理沒什么區別！
再來看第二種通過官方自帶的mysqld_multi來實現多實例實戰：
這里的mysql安裝以及數據庫的初始化和前面的步驟一樣，就不再贅述。
mysqld_multi的配置
vim /etc/my.cnf
[mysqld_multi] mysqld?=?/usr/local/mysql/bin/mysqld_safe mysqladmin?=?/usr/local/mysql/bin/mysqladmin user?=?admin password?=?password [mysqld1] socket?=?/data/dbdata_3306/mysql.sock port?=?3306 pid-file?=?/data/dbdata_3306/3306.pid datadir?=?/data/dbdata_3306 user?=?mysql skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 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 [mysqld2] socket?=?/data/dbdata_3307/mysql.sock port?=?3307 pid-file?=?/data/dbdata_3307/3307.pid datadir?=?/data/dbdata_3307 user?=?mysql skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 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 [mysqld3] socket?=?/data/dbdata_3308/mysql.sock port?=?3308 pid-file?=?/data/dbdata_3308/3308.pid datadir?=?/data/dbdata_3308 user?=?mysql skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 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
mysqld_multi啟動
/usr/local/mysql/bin/mysqld_multi?start?1 /usr/local/mysql/bin/mysqld_multi?start?2 /usr/local/mysql/bin/mysqld_multi?start?3
或者采用一條命令的形式：
/usr/local/mysql/bin/mysqld_multi?start?1-3
更改原來密碼（處于安全考慮，還需要刪除系統中沒有密碼的帳號，這里省略了）：
/usr/local/mysql/bin/mysqladmin?-uroot?password?'password'?-S?/data/dbdata_3306/mysql.sock /usr/local/mysql/bin/mysqladmin?-uroot?password?'password'?-S?/data/dbdata_3307/mysql.sock /usr/local/mysql/bin/mysqladmin?-uroot?password?'password'?-S?/data/dbdata_3308/mysql.sock
登錄測試并創建admin密碼(停止mysql的時候需要使用到)
/usr/local/mysql/bin/mysql?-uroot?-ppassword?-S?/data/dbdata_3308/mysql.sock GRANT?SHUTDOWN?ON?*.*?TO?'admin'@'localhost'?IDENTIFIED?BY?'password'; flush?privileges; /usr/local/mysql/bin/mysql?-uroot?-ppassword?-S?/data/dbdata_3308/mysql.sock GRANT?SHUTDOWN?ON?*.*?TO?'admin'@'localhost'?IDENTIFIED?BY?'password'; flush?privileges; /usr/local/mysql/bin/mysql?-uroot?-ppassword?-S?/data/dbdata_3308/mysql.sock GRANT?SHUTDOWN?ON?*.*?TO?'admin'@'localhost'?IDENTIFIED?BY?'password'; flush?privileges;
更改環境變量
vim?/etc/profile PATH=${PATH}:/usr/local/mysql/bin/ source?/etc/profile
添加到自動啟動
vim?/etc/init.d/boot.local /usr/local/mysql/bin/mysqld_multi?start?1-3
如果是rhel或者centos系統的話自啟動文件/etc/rc.local
管理的話，在本地都是采用 -S /data/dbdata_3308/mysql.sock，如果在遠程可以通過不同的端口連接上去坐管理操作。其他的和單實例的管理沒什么區別！
大家在管理多實例的話可以配置文件manager實例管理器，管理器來會比較方面，限于篇幅，這里就不在多做介紹！
相關推薦：
由于第一次接觸LINUX，花了三天時間才算有所成就，發出來希望可以給大伙帶來方便……
mysql多實例,每個MySQL多實例講解實例的datadir,pid,port,socket應該是不同的，在Unix中，在你啟動客戶端之前……
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
