分类： LINUX
2010-03-11 12:23:24
的实时同步-双机互备.
一．方案描述：
Mysql的双机热备方案，主要是配合linux系统的热备方案，因为pluswell热备软件（linux版）没有数据镜像的功能，所以只有用mysql自带的热备功能。
二．Mysql的数据库热备配置方法如下：
1，环境：
A机器（双网卡）
系统： Fedora7+mysql5.0.37(系统自带)+SER
主IP地址： 192.168.20.201
心跳ip地址： 10.1.1.201
B机器（双网卡）：
系统： Fedora7+mysql5.0.37(系统自带)+SER
主IP地址： 192.168.20.202
心跳ip地址： 10.1.1.202
2，设置方法：
第一步：
我们用心跳ip地址，且确定同步的数据库为SER数据库。在这两台机器上创建复制数据帐号。
A机器：
Mysql>grant replication slave on *.* to ‘repl’@10.1.1.202 identified by ’123456’;
B机器：
Mysql>grant replication slave on *.* to ‘repl’@10.1.1.201 identified by ’123456’;
第二步：配置mysql.cnf
A机器
【mysqld】
#Replication master
server-id = 1
log-bin
binlog-do-db=ser
binlog-ignore-db=mysql
# Replication slave
master-host=10.1.1.202
master-user=repl
master-password=123456
master-port=3306
master-connect-retry=60
replicate-do-db=ser
B机器
【mysqld】
#Replication master
server-id = 2
log-bin
binlog-do-db=ser
binlog-ignore-db=mysql
# Replication slave
master-host=10.1.1.201
master-user=repl
master-password=123456
master-port=3306
master-connect-retry=60
replicate-do-db=ser
第三步：最后重新启动两台机器的mysql.
#service mysqld restart
=============================================================
附，解释:
1)binlog-do-db=ser表示需要备份的数据库是ser这个数据库,
如果需要备份多个数据库，那么应该写多行,如下所示:
binlog-do-db=backup1
binlog-do-db=backup2
binlog-do-db=backup3
2) server-id=2表示本机器的序号, A,B的server-id不能相同;
3)log-bin表示打开binlog,打开该选项才可以通过I/O写到Slave的relay-log,也是可以进行replication的前提;
其中mysql_binary_log是文件的名称，mysql将建立不同扩展名，文件名为mysql_binary_log的几个日志文件.
4) master-host=10.1.1.201表示A做slave时的master为10.1.1.201;
5) master-user=repl这里表示master上开放的一个有权限的用户,使其可以从slave连接到master并进行复制;
6) master-password=123456表示授权用户的密码;
7) master-port=3306 master上MySQL服务Listen3306端口;
8) master-connect-retry=60同步间隔时间;
9) replicate-do-db=ser表示同步ser数据库;
三．查看状态及调试
1,查看master的状态
SHOW MASTER STATUS;
Position不应为0
2,查看slave的状态
show slave status;
Slave_IO_Running | Slave_SQL_Running这两个字段应为YES|YES.
show processlist;
会有两条记录与同步有关state为Has read all relay log; waiting for the slave I/O thread to update it和s Waiting for master to send event .
3,CHANGE MASTER TO
如果A的Slave未启动,Slave_IO_Running为No.
可能会是B的master的信息有变化,
查看B SHOW MASTER STATUS;
记录下File,Position字段.假设为'test202-bin.000001',98 ;
在A下执行:
mysql>Stop Slave;
mysql>CHANGE MASTER TO
mysql>MASTER_LOG_FILE = ' test202-bin.000001',
mysql>MASTER_LOG_POS = 98 ;
mysql>Start Slave;
5,SET GLOBAL SQL_SLAVE_SKIP_COUNTER =n;
如果A的Slave_SQL_Running为No.
Err文件中记录:
Slave: Error 'Duplicate entry '1' for key 1' on query....
可能是master未向slave同步成功,但slave中已经有了记录。造成的冲突.
可以在A上执行
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start salve;就可以了。
如果Slave_SQL_Running仍然为No.可以考虑试一下如下步骤：
1， 把B机器上的ser数据库中删除上面Err文件中提到的表中的数据。
2， 把两台机器上的/var/lib/mysql/文件夹中的master.info,relay-log.info,机器名开头的文件删除。
3,重启mysql。
======================================================
8G内存配置文件
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 32M
table_cache = 4
sort_buffer_size = 1M
read_buffer_size = 2M
read_rnd_buffer_size = 1M
net_buffer_length = 2K
#thread_stack = 64K
server-id = 1005
#skip-slave-start
##########################################
######MASTER TO MASTER REPLICATION########
##########################################
#replicate-same-server-id = 0
server-id = 0
auto_increment_increment = 2
auto_increment_offset = 1
#
#replicate-do-db = igdb
binlog-do-db = dbname
replicate-do-db = dbname
#
master-host = 192.168.10.1
master-user = repluser
master-password = 1234
master-port = 3306
master-connect-retry = 60
##########################################
#####END MASTER TO MASTER REPLICATION#####
##########################################
##########################################
#####VARIABLES USED BY VidaNetworks#######
##########################################
wait_timeout = 432000
max_connect_errors = 10000000
wait_timeout = 432000
max_connections = 800
##########################################
############END VARIABLE ADD##############
##########################################
innodb_data_file_path = ibdata1:3G:autoextend
innodb_data_home_dir=/var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysql
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_thread_concurrency = 0 # ( cpus + disks ) * 2.4
log-bin = mysql-bin.log
relay-log = mysql-relay-bin.log
slow_query_log
slow_query_log_file = slowquery.log
log-output=FILE
log-slave-updates
# You can set innodb_buffer_pool_size up to 50 - 80 %
set-variable = innodb_buffer_pool_size=6G
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
# Set innodb_log_file_size to 25 % of buffer pool size
set-variable = innodb_log_file_size=1G
set-variable = innodb_log_buffer_size=64M
set-variable = innodb_support_xa=1
set-variable = innodb_additional_mem_pool_size=8M
# This is only supposed to work on Windwows, as Linux has a fixed 4 threads.
set-variable = innodb_lock_wait_timeout=20
set-variable = long_query_time=2
set-variable = query_cache_type=2
set-variable = query_cache_size=512M
set-variable = query_cache_limit=32M
set-variable = general_log=0
set-variable = log-queries-not-using-indexes
default-table_type = InnoDB
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
##DF##no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
