初始化多实例mysql_Mysql 多实例配置与初始化

#my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysql]

prompt="\\u@\\h \\D \\R:\\m:\\s [\\d]>

#pager="less -i -n -S"

#tee=/data/mysql/mysql_3306/data/query.log

no-auto-rehash

[mysqld_multi]

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

mysqladmin = /usr/local/mysql/bin/mysqladmin

log = /opt/mysql/mysqld_multi.log

[mysqld]

#misc

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql_3306/data

port = 3306

socket = /tmp/mysql.sock

event_scheduler = 0

#timeout

interactive_timeout = 300

wait_timeout = 300

#character set

character-set-server = utf8

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

skip-name-resolve = 1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = slow.log

log-error = error.log

log_warnings = 2

pid-file = mysql.pid

long_query_time = 1

#log-slow-admin-statements = 1

#log-queries-not-using-indexes = 1

log-slow-slave-statements = 1

#binlog

binlog_format = mixed

server-id = 10518

log-bin = mybinlog

binlog_cache_size = 4M

max_binlog_size = 1G

max_binlog_cache_size = 2G

sync_binlog = 0

expire_logs_days = 10

#relay log

skip_slave_start = 1

max_relay_log_size = 1G

relay_log_purge = 1

relay_log_recovery = 1

log_slave_updates

#slave-skip-errors=1032,1053,1062

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 2M

join_buffer_size = 2M

thread_cache_size = 256

query_cache_size = 0

query_cache_type = 0

query_cache_limit = 256K

query_cache_min_res_unit = 512

thread_stack = 192K

tmp_table_size = 96M

key_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 32M

#myisam

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

#innodb

innodb_buffer_pool_size = 100M

innodb_buffer_pool_instances = 1

innodb_data_file_path = ibdata1:1G:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 64M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

innodb_rollback_on_timeout

innodb_status_file = 1

innodb_io_capacity = 2000

transaction_isolation = READ-COMMITTED

innodb_flush_method = O_DIRECT

#端口号为3306的实例特殊配置

[mysqld3306]

port =3306

server-id=203306

#指定本实例相应版本的basedir和datadir

basedir= /usr/local/mysql

datadir =/data/mysql/dao_3306/data

socket = /tmp/mysql.sock

#重新配置这几个选项，不与全局配置一样，会直接覆盖上面的全局设置

innodb_buffer_pool_size = 100m

#transaction_isolation = REPEATABLE-READ

[mysqld3308]

port=3308

server-id=203308

#binlog-do-db=db01

basedir= /usr/local/mysql

datadir = /data/mysql/dao_3308/data

socket = /tmp/mysql_3308.sock

#重新配置这几个选项，不与全局配置一样，会直接覆盖上面的全局设置

innodb_buffer_pool_size = 100m

innodb_flush_log_at_trx_commit = 2

sync_binlog = 0

[mysqld3309]

port=3309

server-id=203309

#binlog-do-db=db01

basedir= /usr/local/mysql

datadir = /data/mysql/mysql_3309/data

socket = /tmp/mysql_3309.sock

#重新配置这几个选项，不与全局配置一样，会直接覆盖上面的全局设置

innodb_buffer_pool_size = 100m

innodb_flush_log_at_trx_commit = 2

sync_binlog = 0

初始化数据库

必须要进入到Mysql 安装目录来运行命令

[mysql@server ~]$ cd /usr/local/mysql

[mysql@server mysql]$ scripts/mysql_install_db --datadir=/data/mysql/dao_3306/data/

Installing MySQL system tables...

OK

Filling help tables...

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'

/usr/local/mysql/bin/mysqladmin -u root -h server password 'new-password'

Alternatively you can run:

/usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default. This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

使用mysqld_multi来管理mysql 实例

[mysql@server mysql]$ mysqld_multi start 3308

[mysql@server mysql]$ mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3308 is running

[mysql@server mysql]$ mysqld_multi stop 3308

[mysql@server mysql]$ mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld3309 is not running

同时也可以跟踪/mysqld_multi.log

[root@server ~]# tail -f /data/mysql/mysqld_multi.log

Stopping MySQL servers

141110 23:08:13 mysqld_safe mysqld from pid file /data/mysql/dao_3308/data/mysql.pid ended

mysqld_multi log file version 2.16; run: Mon Nov 10 23:08:24 2014

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld3309 is not running