多实例mysql的安装和管理
来源:互联网 发布:淘宝天猫优惠券微信群 编辑:程序博客网 时间:2024/06/16 17:53
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
- 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
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
- /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
添加到自动启动
- 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实例管理器,管理器来会比较方面,限于篇幅,这里就不在多做介绍!
from :http://blog.chinaunix.net/uid-25723371-id-3438628.html- 多实例mysql的安装和管理
- 多实例mysql的安装和管理
- 多实例mysql的安装和管理
- mysql多实例的安装和管理(一台服务器上运行两个mysql实例)
- mysql多实例的安装和管理(一台服务器上运行两个mysql实例)
- mysql多实例的配置和管理
- mysql多实例的配置和管理
- MySQL多实例管理
- mysq多实例安装和管理 [二] -- 官方自带的mysqld_multi方式安装管理
- mysql 多实例安装
- mysql多实例安装
- mysql多实例安装
- Mysql多实例安装
- Mysql 多实例安装
- Mysql多实例安装
- Mysql多实例安装
- MySQL多实例安装
- 多实例MySQL安装
- bug:java.lang.UnsupportedClassVersionError:不支持的运行环境 原因:
- 【分享】Stanford Dataset全集之Collaboration networks
- ORACLE数据缓冲区DB cache
- android截图工具类
- Apache-POI操作Excel的一些小技巧
- 多实例mysql的安装和管理
- 解决绑定数据后列顺序打乱问题
- tomcat启动时自动运行代码
- SQL也能玩递归
- window cmd 命令
- 40多个关于人脸检测/识别的API、库和软件
- iOS 消息通知-NSNotification
- Datastage 分区partition
- hibernate Java项目中自动创表、插入、表不存在等