mysql multinom_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 =

# 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