环境:
一个新安装的数据库。
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