单台mysql 既做主，又是从

amwdnuff

单台mysql 既做主，又是从



授权：
GRANT all,super ON *.* TO backup@192.168.1.5 identified by '1234';
起停脚本
server.sh:
#!/bin/sh
/server/mysql1/bin/mysqld_multi  --defaults-extra-file=/server/mysql1/my.cnf  $1  1-2


my.cnf:
[mysqld_multi]

mysqld = /server/mysql1/bin/mysqld_safe

mysqladmin = /server/mysql1//bin/mysqladmin

user = root



[mysqld1]

port            = 3306
socket          = /server/mysql1/var/mysql/mysql.sock
pid-file = /server/mysql1/var/mysql.pid
datadir = /server/mysql1/var
skip-locking
net_buffer_length = 1M
back_log = 50
max_connecti** = 500
max_connect_errors = 10
table_cache = 200
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log_slow_queries
long_query_time = 2
log_long_format
key_buffer_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 64M
myisam_max_extra_sort_file_size = 64M
myisam_repair_threads = 1
myisam_recover
skip-federated
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 128M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table=1
innodb_file_io_threads = 4
innodb_thread_concurrency = 4
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 120
server-id=1
log-bin
#sql-bin-update-same
binlog-do-db=test1
binlog-do-db=test2


[mysqld2]
port            = 3017
socket           = /server/mysql1/var1/mysql1.sock
pid-file = /server/mysql1/var1/mysql1.pid
datadir = /server/mysql1/var1
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
server-id=1000
master-host=192.168.1.5
master-user=backup
master-password=1234
master-port=3016
master-connect-retry=60
#replicate-do-db=test1
#replicate-do-db=test2
replicate-ignore-db=mysql
log-bin
log-slave-updates


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout