多執行個體mysql的安裝和管理
http://blog.chinaunix.net/uid-20639775-id-3438560.html
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=\\[email protected]\\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=\\[email protected]\\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=\\[email protected]\\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...\n"
- /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...\n"
- /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...\n"
- 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...\n"
- /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...\n"
- /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...\n"
- 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...\n"
- /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...\n"
- /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...\n"
- 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=\\[email protected]\\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,如果在遠端可以通過不同的端口連接配接上去坐管理操作。其他的和單執行個體的管理沒什麼差別! 大家在管理多執行個體的話可以使用mysqlmanager執行個體管理器,管理器來會比較方面,限于篇幅,這裡就不在多做介紹!