第一种是运用多个设置文件启动差别的历程来完成多实例,这类体式格局的上风逻辑简朴,设置简朴,瑕玷是治理起来不太轻易。
第二种是经由过程官方自带的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...\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=\\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,如果在长途能够经由过程差别的端口衔接上去坐治理操纵。其他的和单实例的治理没什么区分!
人人在治理多实例的话能够运用mysqlmanager实例治理器,治理器来会比较方面,限于篇幅,这里就不在多做引见!
相干引荐:
Linux下装置Mysql多实例作为数据备份服务器完成多主到一从多实例
因为第一次打仗LINUX,花了三天时候才算有所造诣,发出来愿望能够给大伙带来轻易......
MySQL多实例解说
mysql多实例,每一个MySQL多实例解说实例的datadir,pid,port,socket应该是差别的,在Unix中,在你启动客户端之前......
以上就是MySQL多实例的设置和治理细致的细致内容,更多请关注ki4网别的相干文章!