第一部分搭建主主复制
1.1、创建复制帐号
主服务器mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*TO rep@’192.168.174.135′ IDENTIFIED BY ‘rep’;
另一个主服务器
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*TO rep@’192.168.174.139′ IDENTIFIED BY ‘rep’;
1.2、配置master
[client]
port = 3306
#socket = /tmp/mysql.sock
[mysqld_safe]
#socket = /tmp/mysql.sock
#nice = 0
[mysqld]
sql_mode=’ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’
user = mysql
#socket = /tmp/mysql.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
log-error=/var/lib/mysql/centos3.err
pid-file=/var/lib/mysql/centos3.pid
# tmp dir settings ##
tmpdir = /tmp
tmp_table_size = 64M
max_heap_table_size=64M
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/slow.log
log-output = FILE
## character set ##
character-set-server = utf8
init_connect =’set names utf8′
skip-character-set-client-handshake = 1
lower_case_table_names=1
skip-log-warnings
skip-name-resolve
open_files_limit=65535
max_connections = 1000
max_connect_errors = 1000
wait_timeout = 864000
interactive_timeout=864000
connect_timeout = 5
max_allowed_packet = 16M
#table_cache = 256
thread_cache_size = 32
thread_concurrency = 8
#key_buffer_size = 16M #8M
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
innodb_fast_shutdown = 1
innodb_buffer_pool_size =200M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
#innodb_thread_concurrency = 24
#innodb_commit_concurrency = 24
#innodb_file_io_threads = 4
#innodb_read_io_threads = 8
#innodb_write_io_threads = 8
#innodb_log_file_size = 128M
#innodb_log_files_in_group = 3
#innodb_max_dirty_pages_pct = 90
# * Query Cache Configuration
query_cache_limit = 2M
query_cache_size = 16M
query_cache_type = 1
log-bin=centos3-bin
log-bin-index=centos3-bin.index
binlog_format=mixed
expire_logs_days = 15
binlog_cache_size = 1M
max_binlog_size = 128M
log_bin_trust_function_creators=1
#######################SERVER ID########################
server-id=1
log_slave_updates = 1
relay-log = centos3-relay-log.log
relay-log-index = centos3-relay-log.index
#######################MASTER########################
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
#max_relay_log_size = 50000000
[mysqldump]
quick
1.3 配置从主
[client]
port = 3306
#socket = /tmp/mysql.sock
[mysqld_safe]
#socket = /tmp/mysql.sock
#nice = 0
[mysqld]
sql_mode=’ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’
user = mysql
#socket = /tmp/mysql.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
log-error=/var/lib/mysql/centos4.err
pid-file=/var/lib/mysql/centos4.pid
# tmp dir settings ##
tmpdir = /tmp
tmp_table_size = 64M
max_heap_table_size=64M
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/slow.log
log-output = FILE
## character set ##
character-set-server = utf8
init_connect =’set names utf8′
skip-character-set-client-handshake = 1
lower_case_table_names=1
skip-log-warnings
skip-name-resolve
open_files_limit=65535
max_connections = 1000
max_connect_errors = 1000
wait_timeout = 864000
interactive_timeout=864000
connect_timeout = 5
max_allowed_packet = 16M
#table_cache = 256
thread_cache_size = 32
thread_concurrency = 8
#key_buffer_size = 16M #8M
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
innodb_fast_shutdown = 1
innodb_buffer_pool_size =200M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
#innodb_thread_concurrency = 24
#innodb_commit_concurrency = 24
#innodb_file_io_threads = 4
#innodb_read_io_threads = 8
#innodb_write_io_threads = 8
#innodb_log_file_size = 128M
#innodb_log_files_in_group = 3
#innodb_max_dirty_pages_pct = 90
# * Query Cache Configuration
query_cache_limit = 2M
query_cache_size = 16M
query_cache_type = 1
log-bin=centos4-bin
log-bin-index=centos4-bin.index
binlog_format=mixed
expire_logs_days = 15
binlog_cache_size = 1M
max_binlog_size = 128M
log_bin_trust_function_creators=1
#######################SERVER ID########################
server-id=2
log_slave_updates = 1
relay-log = centos4-relay-log.log
relay-log-index = centos4-relay-log.index
#######################MASTER########################
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
#max_relay_log_size = 50000000
[mysqldump]
quick
1.4 重启主主 及 从主mysql服务
service mysqld restart
注意：从主要先备份mysql db 的5个innodb table,后面恢复热备数据库时用到，不然虽然启动成功，但错误日志会报错
脚本如下：
人之所以能，是相信能。
