MYSQL_MULTI 的配置安例

环境:
一个新安装的数据库。

1. 关闭数据库。
[root@mysql_source ~]# mysqladmin -uroot -p shutdown
Enter password: 
[root@mysql_source ~]# ps -elf | grep mysql
0 S root     25343 25311  0  80   0 - 25824 pipe_w 21:52 pts/1    00:00:00 grep mysql

2. COPY另一个实例所需要的文件。
[root@mysql_source data]# cd /data/mysql
[root@mysql_source mysql]# ls
mysql_3306
[root@mysql_source mysql]# cp -r mysql_3306 mysql_3307
[root@mysql_source mysql]# chown -R mysql:mysql mysql_3307
[root@mysql_source mysql]# chmod -R 775 mysql_3307

3. 修改配置文件:
[client]
default-character-set = utf8

[mysqld_safe]
open_files_limit               = 8192                                
user                           = mysql
log-error                      = error.log

[mysqld]
max_allowed_packet             = 16M

character_set_server           = utf8                                
# collation_server               = utf8_general_ci

max_connections                = 300                                
max_user_connections           = 150                                  
thread_cache_size              = 200                                

basedir                         = /usr/local/mysql/
# Query Cache
query_cache_type               = 0                                     
innodb_status_file             = 1  


# Session variables
sort_buffer_size               = 1M                                 
tmp_table_size                 = 32M                               

read_buffer_size               = 128k                              
read_rnd_buffer_size           = 256k                             
join_buffer_size               = 128k                        


table_definition_cache         = 400                           
table_open_cache               = 400                                 


# MySQL error log
log_error                      = error.log
log_warnings                   = 2

# Slow Qury Log
slow_query_log_file            = slow.log
slow_query_log                 = 0
log_queries_not_using_indexes  = 1
long_query_time                = 0.5
min_examined_row_limit         = 100


# General Query Log
general_log_file               = general.log
general_log                    = 0


# Binary logging and Replication
binlog_cache_size              = 1M
binlog_stmt_cache_size         = 1M                                  
max_binlog_size                = 128M                               
sync_binlog                    = 0                                 
expire_logs_days               = 5                                   
binlog_format                  =  MIXED                              
#binlog_row_image               = MINIMAL                             

# Slave variables
log_slave_updates              = 1                                
read_only                      = 0                                   
skip_slave_start               = 0                                   

#skip_name_resolve              = 1                                   

# MyISAM variables
key_buffer_size                = 8M                                  
myisam_recover                 = BACKUP,FORCE

# MEMORY variables

max_heap_table_size            = 64M                                


# InnoDB variables
innodb_data_file_path=ibdata1:100M:autoextend 
innodb_buffer_pool_size        = 2G                                
innodb_file_per_table          = 1                                  
# innodb_buffer_pool_instances   = <n>                               
# innodb_write_io_threads        = 8                                 
# innodb_read_io_threads         = 8                                 
# innodb_io_capacity             = 1000                              

innodb_flush_log_at_trx_commit = 1                                 
innodb_log_buffer_size         = 8M                                                           
innodb_log_file_size           = 100M                               
innodb_log_files_in_group               =3 

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /data/mysql/mysql_mutli.log

[mysqld3306]
# Connection and Thread variables
port                           = 3306
socket                         = /tmp/mysql_3306.sock
datadir                         = /data/mysql/mysql_3306/data
tmpdir                          = /data/mysql/mysql_3306/tmp
server_id                      = 103306                                                          #ip最后一段+端口号
log_bin                        = /data/mysql/mysql_3306/logs/mysql-bin

[mysqld3307]
# Connection and Thread variables
port                           = 3307
socket                         = /tmp/mysql_3307.sock
datadir                         = /data/mysql/mysql_3307/data
tmpdir                          = /data/mysql/mysql_3307/tmp
server_id                      = 103307                                                          #ip最后一段+端口号
log_bin                        = /data/mysql/mysql_3307/logs/mysql-bin

[mysql]

no_auto_rehash
max_allowed_packet             = 16M
prompt                         = '(testing)\u@\h [\d]> '
pager="less -i -n -S"
default_character_set          = utf8                                


[mysqldump]
max_allowed_packet             = 16M


4. 启动数据库:
[root@mysql_source bin]# ./mysqld_multi --defaults-file=/etc/my.cnf start 
[root@mysql_source bin]# ./mysqld_multi --defaults-file=/etc/my.cnf report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

如果要实现用MYSQLD_MULTI来关库就需要在添加一个可以关库权限的用户, 然后将配置修改在MYSQL_UTILTI配置选项下:
例:
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /data/mysql/mysql_mutli.log
user=xxx
password=xxx

  

posted @ 2014-06-12 00:44 SMALL-D 阅读(...) 评论(...)  编辑 收藏