mysql 配置多个hostname,一台主机配置多个mysqld实例

配置选项说明

mysqld_multi：配置mysqld_multi命令参数信息

client：客户端参数信息

mysqld：每个实例配置的参数

mysql：命令行工具mysql配置参数

mysqld_safe：mysqld启动的配置参数

mysqldump：数据备份时参数

myisamchk：myisamchk命令配置参数

编辑配置文件my.cnf# vim /etc/my.cnf

[mysqld_multi]

mysqld=/usr/local/mysql-5.5.14/bin/mysqld_safe

mysqladmin=/usr/local/mysql-5.5.14/bin/mysqladmin

user=admin

password=mysqladmin

log=/var/log/multi.log

[client]

[mysqld3306]

user            = mysql

port            = 3306

socket          = /data/mydata3306/mysql.sock

pid-file        = /data/mydata3306/mysql.pid

datadir         = /data/mydata3306

skip-external-locking

skip-name-resolve

key_buffer_size = 16K

max_allowed_packet = 1M

table_open_cache = 4

sort_buffer_size = 64K

read_buffer_size = 256K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 128K

server-id       = 3306

log-bin=mysql-bin-3306

log-bin-index=mysql-bin-3306.index

binlog_format=row

[mysqld3307]

user            = mysql

port            = 3307

socket          = /data/mydata3307/mysql.sock

pid-file        = /data/mydata3307/mysql.pid

datadir         = /data/mydata3307

tmpdir          = /data/mydata3307

skip_external_locking

skip-name-resolve

back_log        = 100

default-storage-engine = INNODB

collation = utf8_general_ci

max_connections = 800

max_connect_errors = 100000

interactive_timeout = 172800

connect_timeout = 10

max_allowed_packet = 4M

max_heap_table_size = 128M

tmp_table_size = 128M

max_length_for_sort_data = 4096

net_buffer_length = 8K

sort_buffer_size = 8M

join_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 2M

table_cache = 1024

thread_cache_size = 64

thread_concurrency = 8

query_cache_type = 0

query_cache_size = 64M

query_cache_limit = 1M

log-error = /data/mydata3307/error.log

log_warnings

long_query_time = 1

slow_query_log

slow_query_log_file = /data/mydata3307/slow-query.log

#log_slow_queries = /data/mysqldata3307/log/slow-query.log

log_queries_not_using_indexes

binlog_cache_size = 8M

max_binlog_size = 512M

log-bin = /data/mydata3307/mysql-bin-3307

log-bin-index = /data/mydata3307/mysql-bin-3307.index

expire_logs_days = 3

server-id               = 3307

key_buffer_size = 32M

bulk_insert_buffer_size = 16M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

innodb_file_per_table

innodb_flush_method=O_DIRECT

[mysqldump]

quick

max_allowed_packet = 1G

[mysqld_safe]

open-files-limit = 8192

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

[mysqlhotcopy]

interactive-timeout

初始化多库实例# ./scripts/mysql_install_db --datadir=/data/mydata3306 --user=mysql // 初始化3306

# ./scripts/mysql_install_db --datadir=/data/mydata3307 --user=mysql // 初始化3307

多实例命令# ./bin/mysqld_multi start 3306,3307 //启动

# ./bin/mysqld_multi report 3306,3307 // 查看实例状态

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3307 is running

赋权限# mysql -uroot -P3306 -S /data/mydata3306/mysql.sock

>grant shutdown on *.* to 'admin'@'localhost' identified by 'mysqladmin';

# mysql -uroot -P3307 -S /data/mydata3307/mysql.sock

>grant shutdown on *.* to 'admin'@'localhost' identified by 'mysqladmin';

本文来自投稿，不代表访得立场，如若转载，请注明出处：http://www.found5.com//view/691.html