因工作测试工作需要,需在同一台服务器上开启多个端口进行主从复制,工作思路是这样,3006用来接收程序的更改操作,3007用来提供客户端查询资料请求。
服务器操作系统为centos 4.6服务器版,在同一服务上开启多个mysql端有两种方式,一种是在编译的时候指定特定端口,第二是修改配置文件来达到监听多个端口的目的,因为我们是在想在测试环境下检验主从复制的性能,因此我这里是介绍第二种方法:通过修改配置文件来使mysql监听多个端口。以下是配置文件的详细内容。mysql安装配置论坛上有很多资料,我这里不介绍,同理,每项配置选项的意义我也没有做任何解释,毕竟,要想弄好mysql,这样选项意义你必须有清醒的认识。
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 64M
default-storage-engine=innodb
default-character-set=utf8
sort_buffer_size = 6M
net_buffer_length = 16K
read_buffer_size = 1M
read_rnd_buffer_size = 6M
myisam_sort_buffer_size = 6M
net_read_timeout = 150
net_write_timeout = 150
max_connections = 300
wait_timeout=600
join_buffer_size= 6M
# Query Cache Configuration
query_cache_limit = 1M
query_cache_size = 16M
tmp_table_size = 64M
# Table Cache
table_cache = 512
# Thread Cache
# Set the SQL mode to strict
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
lower_case_table_names=1
# Slow Query Log Configuration
#log_slow_queries = /usr/local/mysql/var/mysql-slow.log
#long_query_time = 10
binlog_cache_size = 1000000
max_binlog_size = 100000000
log-bin = /usr/local/mysql/var/mysqllog
log-error = /usr/local/mysql/var/mysqllog.err
log = /usr/local/mysql/var/mysqllog.log
binlog-do-db=“写上你需要同步的数据库”
# set bin log can log function
log_bin_trust_function_creators = 1
server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/var/
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 10M
innodb_log_file_size = 64M
innodb_log_buffer_size = 5M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 150
[mysqldump]
quick
max_allowed_packet = 256M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
##以下是需添加的内容,引用官方对mysqld_multi的解析
#mysqld_multi可以管理多个帧听不同Unix套接字文件和TCP/IP端口的连接的mysqld
进程。它可以启动或停止服务器,或报告它们的当前状态。程序寻找my.cnf中的[mysqldN]组(或--config-file选项指定的文件)。N
可以为任何正整数。在下面的讨论中该数字指选项组号,或GNR。组号区别各选项组,并用作mysqld_multi的参数来指定想要启动、停止哪个服务器或获取哪个服务器的状态报告。这些组中的选项与将用来启动mysqld的[mysqld]组中的相同。([mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
#以下是第二个监听端口的配置选项,大部分与原始端口相同,我们只需更改几个地方的选项即可,所以
##们可先将原始配置文件复制再粘贴上来,然后再针对特定选项进行修改。
# The MySQL server
[mysqld2]
#在同一服务器上指定多端口的格式,在配置主从复制的时候,最好是为二进制日志,一般日志与错误日志
#及慢查询等日志分别指定到专用的日志文件名以下用设置第二个监听端的server id,端口及相关日志文件 #名
port = 3307
socket = /tmp/mysql.sock2
log =/usr/local/mysql/var2/host26.log
log-error =/usr/local/mysql/var2/host26.err
user = mysql
datadir =/usr/local/mysql/var2
pid-file =/usr/local/mysql/var2/host26.pid
skip-locking
key_buffer = 16M
max_allowed_packet = 256M
table_cache = 64
#max_join_size = 1024M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-character-set = utf8
server-id = 3307
master-host = 192.168.10.26
master-user = replicate
master-password = zfy1107
master-port = 3306
replicate-do-db=写上你需同步的数据库名
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/var2/
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 10M
innodb_log_file_size = 64M
innodb_log_buffer_size = 5M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 150
[mysqldump]
quick
max_allowed_packet = 256M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
配置好后,用下列命令启动3007端口
/usr/local/mysql/bin/mysqld_multi --user=mysql start 2
netstat -nltup|grep 3307来检查端是否启动,如果没有,请检 相应出错日志信息
mysql --socket=/tmp/mysql.sock2进入3307数据库进管理,你也可以使用3307端口进行连接。
show slave status用来查看同步是否有异常,否则,请注意3306的错误日志,检查同步出现故障的原因。如果一切正常恭喜你,你成功了。
上面的mysql版本是5.0系列,但针对mysql5.1.28则应注意。5.1.28编译安装好后(即使是你编译时指定了支持innodb选项),默认是也不支持innobase(innodb)的,并且编译好并在安装启动前你不能在配置文件中开启innodb的相关配置选项,你在不开启innodb相关选项初实化mysql(也就是说启动mysql)后,你才能在配置文件中打开innodb的相关配置选项。
阅读(2833) | 评论(0) | 转发(0) |