两台mysql服务器多实例间的主主循环同步.docx
《两台mysql服务器多实例间的主主循环同步.docx》由会员分享，可在线阅读，更多相关《两台mysql服务器多实例间的主主循环同步.docx（17页珍藏版）》请在冰豆网上搜索。
两台mysql服务器多实例间的主主循环同步
两台mysql服务器多实例间的主主同步配置
最近因为工作需要：
在两台生产中的服务器上面测试mysql的主主循环同步，重点是不要影响正在运行的mysql服务。
查了很多文档，因为所有的都比较笼统，步骤不够详细等等原因，做了很长时间，不过最后在自己的修改下终于成功了。
下面就来详细介绍一下配置过程。
首先介绍一下环境：
1、mysql服务器192.168.50.119mysql版本5.0.77（服务器上的mysql服务正在运行）
2、mysql服务器192.168.50.104mysql版本5.1.31（服务器上的mysql服务正在运行）
3、mysql的安装路径：
/usr/local/mysql
4、实验目标：
119服务器的第二个实例mysql3307与104服务器的第二个实例mysql3307之间实现主主同步。
注意：
其他版本mysql可能会有点不一样，切记：
遇到报错就看日志。
一、多实例配置步骤：
1、登陆119服务器另外复制一个实例改名为：
mysql3307
#cd/usr/local/
#cp-Rmysqlmysql3307
#
2、创建mysql3307的数据库存放文件夹
#mkdir/home/mysql3307
3、初始化数据库
#/usr/local/mysql/bin/mysql_install_db--basedir=/usr/local/mysql3307--datadir=/home/mysql3307--user=mysql
4、创建配置f文件，因为文件比较重要，现将其全部附上
Mysql5.0.77（119服务器）的配置文件内容：
本配置内容包含了主主同步配置。
#ExampleMySQLconfigfileformediumsystems.
#
#Thisisforasystemwithlittlememory（32M-64M）whereMySQLplays
#animportantpart,orsystemsupto128MwhereMySQLisusedtogetherwith
#otherprograms（suchasawebserver）
#
#MySQLprogramslookforoptionfilesinasetof
#locationswhichdependonthedeploymentplatform.
#Youcancopythisoptionfiletooneofthose
#locations.Forinformationabouttheselocations,see:
#
#
#Inthisfile,youcanusealllongoptionsthataprogramsupports.
#Ifyouwanttoknowwhichoptionsaprogramsupports,runtheprogram
#withthe"--help"option.
#ThefollowingoptionswillbepassedtoallMySQLclients
[client]
#password=your_password
#port=3306
#socket=/tmp/mysql.sock
#Herefollowsentriesforsomespecificprograms
#TheMySQLserver
[mysqld]
port=3307
socket=/tmp/mysql3307.sock
skip-external-locking
pid-file=/home/mysql3307/mysql.pid
log-error=/home/mysql3307/mysql3307.err
key_buffer_size=16M
max_allowed_packet=1M
#table_open_cache=64
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
user=mysql
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
#Don'tlistenonaTCP/IPportatall.Thiscanbeasecurityenhancement,
#ifallprocessesthatneedtoconnecttomysqldrunonthesamehost.
#AllinteractionwithmysqldmustbemadeviaUnixsocketsornamedpipes.
#NotethatusingthisoptionwithoutenablingnamedpipesonWindows
#（viathe"enable-named-pipe"option）willrendermysqlduseless!
#
#skip-networking
#ReplicationMasterServer（default）
#binaryloggingisrequiredforreplication
log-bin=mysql-bin
#binaryloggingformat-mixedrecommended
#binlog_format=mixed
#requireduniqueidbetween1and2^32-1
#defaultsto1ifmaster-hostisnotset
#butwillnotfunctionasamasterifomitted
server-id=23307
#ReplicationSlave（commentoutmastersectiontousethis）
#
#Toconfigurethishostasareplicationslave,youcanchoosebetween
#twomethods:
#
#1）UsetheCHANGEMASTERTOcommand（fullydescribedinourmanual）-
#thesyntaxis:
#
#CHANGEMASTERTOMASTER_HOST=,MASTER_PORT=,
#MASTER_USER=,MASTER_PASSWORD=;
#
#whereyoureplace,,byquotedstringsand
#bythemaster'sportnumber（3306bydefault）.
#
#Example:
#
#CHANGEMASTERTOMASTER_HOST='125.564.12.1',MASTER_PORT=3306,
#MASTER_USER='joe',MASTER_PASSWORD='secret';
#
#OR
#
#2）Setthevariablesbelow.However,incaseyouchoosethismethod,then
#startreplicationforthefirsttime（evenunsuccessfully,forexample
#ifyoumistypedthepasswordinmaster-passwordandtheslavefailsto
#connect）,theslavewillcreateamaster.infofile,andanylater
#changeinthisfiletothevariables'valuesbelowwillbeignoredand
#overriddenbythecontentofthemaster.infofile,unlessyoushutdown
#theslaveserver,deletemaster.infoandrestarttheslaverserver.
#Forthatreason,youmaywanttoleavethelinesbelowuntouched
#（commented）andinsteaduseCHANGEMASTERTO（seeabove）
#
#requireduniqueidbetween2and2^32-1
#（anddifferentfromthemaster）
#defaultsto2ifmaster-hostisset
#butwillnotfunctionasaslaveifomitted
#server-id=2
#
#Thereplicationmasterforthisslave-required
#master-host=
#
#Theusernametheslavewilluseforauthenticationwhenconnecting
#tothemaster-required
#master-user=
#
#Thepasswordtheslavewillauthenticatewithwhenconnectingto
#themaster-required
#master-password=
#
#Theportthemasterislisteningon.
#optional-defaultsto3306
#master-port=
#
#binarylogging-notrequiredforslaves,butrecommended
#log-bin=mysql-bin
#UncommentthefollowingifyouareusingInnoDBtables
#innodb_data_home_dir=/usr/local/mysql/var
#innodb_data_file_path=ibdata1:
10M:
autoextend
#innodb_log_group_home_dir=/usr/local/mysql/var
#Youcanset.._buffer_pool_sizeupto50-80%
#ofRAMbutbewareofsettingmemoryusagetoohigh
#innodb_buffer_pool_size=16M
#innodb_additional_mem_pool_size=2M
#Set.._log_file_sizeto25%ofbufferpoolsize
#innodb_log_file_size=5M
#innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_lock_wait_timeout=50
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#RemovethenextcommentcharacterifyouarenotfamiliarwithSQL
#safe-updates
[myisamchk]
key_buffer_size=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
Mysql5.1.31（104服务器）的配置文件内容：
本配置内容包含了主主同步配置。
#ExampleMySQLconfigfileformediumsystems.
#
#Thisisforasystemwithlittlememory（32M-64M）whereMySQLplays
#animportantpart,orsystemsupto128MwhereMySQLisusedtogetherwith
#otherprograms（suchasawebserver）
#
#MySQLprogramslookforoptionfilesinasetof
#locationswhichdependonthedeploymentplatform.
#Youcancopythisoptionfiletooneofthose
#locations.Forinformationabouttheselocations,see:
#
#
#Inthisfile,youcanusealllongoptionsthataprogramsupports.
#Ifyouwanttoknowwhichoptionsaprogramsupports,runtheprogram
#withthe"--help"option.
#ThefollowingoptionswillbepassedtoallMySQLclients
[client]
#password=your_password
#port=3306
#socket=/tmp/mysql.sock
#Herefollowsentriesforsomespecificprograms
#TheMySQLserver
[mysqld]
port=3307
socket=/tmp/mysql3307.sock
skip-external-locking
pid-file=/home/mysql3307/mysql.pid
log-error=/home/mysql3307/mysql3307.err
key_buffer_size=16M
max_allowed_packet=1M
table_open_cache=64
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
user=mysql
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
#Don'tlistenonaTCP/IPportatall.Thiscanbeasecurityenhancement,
#ifallprocessesthatneedtoconnecttomysqldrunonthesamehost.
#AllinteractionwithmysqldmustbemadeviaUnixsocketsornamedpipes.
#NotethatusingthisoptionwithoutenablingnamedpipesonWindows
#（viathe"enable-named-pipe"option）willrendermysqlduseless!
#
#skip-networking
#ReplicationMasterServer（default）
#binaryloggingisrequiredforreplication
log-bin=mysql-bin
#binaryloggingformat-mixedrecommended
binlog_format=mixed
#requireduniqueidbetween1and2^32-1
#defaultsto1ifmaster-hostisnotset
#butwillnotfunctionasamasterifomitted
server-id=13307
#ReplicationSlave（commentoutmastersectiontousethis）
#
#Toconfigurethishostasareplicationslave,youcanchoosebetween
#twomethods:
#
#1）UsetheCHANGEMASTERTOcommand（fullydescribedinourmanual）-
#thesyntaxis:
#
#CHANGEMASTERTOMASTER_HOST=,MASTER_PORT=,
#MASTER_USER=,MASTER_PASSWORD=;
#
#whereyoureplace,,byquotedstringsand
#bythemaster'sportnumber（3306bydefault）.
#
#Example:
#
#CHANGEMASTERTOMASTER_HOST='125.564.12.1',MASTER_PORT=3306,
#MASTER_USER='joe',MASTER_PASSWORD='secret';
#
#OR
#
#2）Setthevariablesbelow.However,incaseyouchoosethismethod,then
#startreplicationforthefirsttime（evenunsuccessfully,forexample
#ifyoumistypedthepasswordinmaster-passwordandtheslavefailsto
#connect）,theslavewillcreateamaster.infofile,andanylater
#changeinthisfiletothevariables'valuesbelowwillbeignoredand
#overriddenbythecontentofthemaster.infofile,unlessyoushutdown
#theslaveserver,deletemaster.infoandrestarttheslaverserver.
#Forthatreason,youmaywanttoleavethelinesbelowuntouched
#（commented）andinsteaduseCHANGEMASTERTO（seeabove）
#
#requireduniqueidbetween2and2^32-1
#（anddifferentfromthemaster）
#defaultsto2ifmaster-hostisset
#butwillnotfunctionasaslaveifomitted
#server-id=2
#
#Thereplicationmasterforthisslave-required
#master-host=
#
#Theusernametheslavewilluseforauthenticationwhenconnecting
#tothemaster-required
#master-user=
#
#Thepasswordtheslavewillauthenticatewithwhenconnectingto
#themaster-required
#master-password=
#
#Theportthemasterislisteningon.
#optional-defaultsto3306
#master-port=
#
#binarylogging-notrequiredforslaves,butrecommende
升级会员 