设置 MySql 数据同步.docx
《设置 MySql 数据同步.docx》由会员分享,可在线阅读,更多相关《设置 MySql 数据同步.docx(16页珍藏版)》请在冰豆网上搜索。
设置MySql数据同步
设置MySql数据同步
Aip:
61.160.200.204
Bip:
61.160.200.209
showslavestatus\G;
showmasterstatus\G;
SHOWPROCESSLIST\G
A:
设置
1.增加一个用户最为同步的用户帐号:
GRANTFILEON*.*TObackup@'61.160.200.209'IDENTIFIEDBY'1234'
mysql>GRANTREPLICATIONSLAVEON*.*TO'repl'@'%'IDENTIFIEDBY'slavepass';
2.增加一个数据库作为同步数据库:
//createdatabasebackup
B:
设置
1.增加一个用户最为同步的用户帐号:
GRANTFILEON*.*TObackup@'61.160.200.204'IDENTIFIEDBY'1234'
2.增加一个数据库作为同步数据库:
//createdatabasebackup
主从模式:
A->B
A为master
修改Amysql的my.ini文件。
在mysqld配置项中加入下面配置:
server-id=1
log-bin
#设置需要记录log可以设置log-bin=/var/log/mysqlbak/mysqllog设置日志文件的目录,
#其中mysqllog是日志文件的名称,mysql将建立不同扩展名,文件名为mysqllog的几个日志文件。
binlog-do-db=juson_w6xx#指定需要日志的数据库
重起数据库服务。
用showmasterstatusshowslavestatus命令看日志情况。
mysql>slavestop;
mysql>changemastertoMaster_Log_File='mysqlbaklog.000002',Master_Log_Pos=1388;
mysql>slavestart;
mysql>showslavestatus\G
mysql>changemastertoMASTER_HOST='61.160.200.209',MASTER_USER='backup',MASTER_PASSWORD='1234',Master_Log_File='mysqlbaklog.000002',Master_Log_Pos=106390;
changemastertomaster_host='61.160.200.209',master_user='backup',master_password='1234',master_log_file='209mysqlbaklog.000001',master_log_pos=1745;
B为slave
修改Bmysql的my.ini文件。
在mysqld配置项中加入下面配置:
server-id=2
master-host=61.160.200.204
master-user=backup#同步用户帐号
master-password=1234
master-port=3306
master-connect-retry=60预设重试间隔60秒
replicate-do-db=juson_w6xx告诉slave只做juson_w6xx数据库的更新
slave-skip-errors=all
relay-log-purge=1
重起数据库
用showslavestatus看同步配置情况。
注意:
由于设置了slave的配置信息,mysql在数据库目录下生成master.info
所以如有要修改相关slave的配置要先删除该文件。
否则修改的配置不能生效。
双机互备模式
如果在A加入slave设置,在B加入master设置,则可以做B->A的同步。
在A的配置文件中mysqld配置项加入以下设置:
master-host=61.160.200.209
master-user=backup
master-password=1234
replicate-do-db=juson_w6xx
master-connect-retry=10
slave-skip-errors=all
relay-log-purge=1
在B的配置文件中mysqld配置项加入以下设置:
log-bin=mysqllog
binlog-do-db=juson_w6xx
注意:
当有错误产生时*.err日志文件。
同步的线程退出,当纠正错误后要让同步机制进行工作,运行slavestart
重起AB机器,则可以实现双向的热备。
测试:
向B批量插入大数据量表AA(1872000)条
A数据库每秒钟可以更新2500条数据。
replicate-rewrite-db=aaa->bbb#要将主服务器的数据库同步到从服务器的指定数据库中
replicate-do-table=bbb.t1#指定需要同步的表
replicate-wild-ignore-table=w6xx_yuming_links不复制某些表
replicate-ignore-db=mysql不复制某个库
binlog-ignore-table=w6xx_yuming_links
########################################################
61.160.200.209数据库调整
1.2边数据库配置文件作备份
2.2边原始数据库文件作备份
changemastertomaster_host='61.160.200.209',master_user='backup',master_password='1234',master_log_file='209mysqlbaklog.000001',master_log_pos=1745;
1,主从不能同步:
showslavestatus;报错:
Errorxxxdosn'texist
且showslavestatus\G:
Slave_SQL_Running:
NO
Seconds_Behind_Master:
NULL
解决方法:
stopslave;
setglobalsql_slave_skip_counter=1;
startslave;
之后Slave会和Master去同步主要看:
Slave_IO_Running:
Yes
Slave_SQL_Running:
Yes
Seconds_Behind_Master是否为0,0就是已经同步了
2,还需要做的一些优化与监视:
showfullprocesslist;//查看mysql当前同步线程号
skip-name-resolve//跳过dns名称查询,有助于加快连接及同步的速度
max_connections=1000//增大Mysql的连接数目,(默认100)
max_connect_errors=100//增大Mysql的错误连接数目,(默认10)
查看日志一些命令
1,showmasterstatus\G;
在这里主要是看log-bin的文件是否相同。
showslavestatus\G;
在这里主要是看:
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
如果都是Yes,则说明配置成功.
2,在master上输入showprocesslist\G;
mysql>SHOWPROCESSLIST\G
***************************1.row***************************
Id:
2
User:
root
Host:
localhost:
32931
db:
NULL
Command:
BinlogDump
Time:
94
State:
Hassentallbinlogtoslave;waitingforbinlogto
beupdated
Info:
NULL
如果出现Command:
BinlogDump,则说明配置成功.
stopslave#停止同步
startslave#开始同步,从日志终止的位置开始更新。
SETSQL_LOG_BIN=0|1#主机端运行,需要super权限,用来开停日志,随意开停,会造成主机从机数据不一致,造成错误
SETGLOBALSQL_SLAVE_SKIP_COUNTER=n#客户端运行,用来跳过几个事件,只有当同步进程出现错误而停止的时候才可以执行。
RESETMASTER#主机端运行,清除所有的日志,这条命令就是原来的FLUSHMASTER
RESETSLAVE#从机运行,清除日志同步位置标志,并重新生成master.info
虽然重新生成了master.info,但是并不起用,最好,将从机的mysql进程重启一下,
LOADTABLEtblnameFROMMASTER#从机运行,从主机端重读指定的表的数据,每次只能读取一个,受timeout时间限制,需要调整timeout时间。
执行这个命令需要同步账号有reload和super权限。
以及对相应的库有select权限。
如果表比较大,要增加net_read_timeout和net_write_timeout的值
LOADDATAFROMMASTER#从机执行,从主机端重新读入所有的数据。
执行这个命令需要同步账号有reload和super权限。
以及对相应的库有select权限。
如果表比较大,要增加net_read_timeout和net_write_timeout的值
CHANGEMASTERTOmaster_def_list#在线改变一些主机设置,多个用逗号间隔,比如
CHANGEMASTERTO
MASTER_HOST='',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret'
MASTER_POS_WAIT()#从机运行
SHOWMASTERSTATUS#主机运行,看日志导出信息
SHOWSLAVEHOSTS#主机运行,看连入的从机的情况。
SHOWSLAVESTATUS(slave)
SHOWMASTERLOGS(master)
SHOWBINLOGEVENTS[IN'logname'][FROMpos][LIMIT[offset,]rows]
PURGE[MASTER]LOGSTO'logname';PURGE[MASTER]LOGSBEFORE'date'
My.cfg
server-id=1
log-bin=204mysqlbakmysqllog
binlog-do-db=juson_w6xx
master-host=61.160.200.209
master-user=backup
master-password=1234
replicate-do-db=juson_w6xx
master-connect-retry=10
log-slave-updates
My.cfg
log-bin=209mysqlbaklog
binlog-do-db=juson_w6xx
server-id=2
master-host=61.160.200.204
master-user=backup
master-password=1234
master-port=3306
master-connect-retry=60
replicate-do-db=juson_w6xx
log-slave-updates
主主循环同步配置
1授权用户
(1)A服务器:
mysql>grantreplicationslave,fileon*.*to'backup1'@'192.168.1.12'identified
by'123456';
QueryOK,0rowsaffected(0.00sec)
(2)B服务器:
mysql>grantreplicationslave,fileon*.*to'backup2'@'192.168.1.10'identified
by'123456';
QueryOK,0rowsaffected(0.00sec)
mysql>flushprivileges;
QueryOK,0rowsaffected(0.00sec)
然后都停止MYSQL服务器。
2配置文件
在两个机器上的my.ini里面都开启二进制日志。
(1)A服务器my.ini
#服务器编号。
server-id=1
log-bin=mysql-bin
#启用从属服务器上的日志功能,使这台计算机可以用来构成一个镜像链(A->B->C)
log-slave-updates
#从库是否只读,0表示可读写,1表示只读
read-only=1
#只复制某个表
#replicate-do-table=tablename
#只复制某些表(可用匹配符)
#replicate-wild-do-table=tablename%
#只复制某个库
replicate-do-db=backup
#只复制某些库
#replicte-wild-do-db=dbname%
#不复制某个表
#replicate-ignore-table=tablename
#不复制某些表
#replicate-wild-ignore-table=tablename%
#不复制某个库
replicate-ignore-db=mysql
#复制完的sql语句是否立即从中继日志中清除,1表示立即清除
relay-log-purge=1
#从服务器主机,用于showslavehosts生成从库清单
report-host=slave-1
#即不管发生什么错误,镜像处理工作也继续进行
slave-skip-errors=all
#每经过n次日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)。
n=1是最安#全的做法,但效率最低。
#默认设置是n=0,意思是由操作系统来负责二进制日志文件的同步工作。
sync_binlog=1
#全局增量值。
auto_increment_increment=2
#全局增量起始值。
一般地,需要设置auto_increment_increment和auto_increment_offset是
#在多台主mysql同时工作的情况下,进行分表处理才需要的。
auto_increment_offset=1
(2)B服务器my.ini
#服务器编号
server-id=2
log-bin=mysql-bin
#启用从属服务器上的日志功能,使这台计算机可以用来构成一个镜像链(A->B->C)
log-slave-updates
#从库是否只读,0表示可读写,1表示只读
read-only=1
#只复制某个表
#replicate-do-table=tablename
#只复制某些表(可用匹配符)
#replicate-wild-do-table=tablename%
#只复制某个库
replicate-do-db=backup
#只复制某些库
#replicte-wild-do-db=dbname%
#不复制某个表
#replicate-ignore-table=tablename
#不复制某些表
#replicate-wild-ignore-table=tablename%
#不复制某个库
replicate-ignore-db=mysql
#复制完的sql语句是否立即从中继日志中清除,1表示立即清除
relay-log-purge=1
#从服务器主机,用于showslavehosts生成从库清单
report-host=slave-2
#即不管发生什么错误,镜像处理工作也继续进行
slave-skip-errors=all
#每经过n次日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)。
n=1是最安#全的做法,但效率最低。
#默认设置是n=0,意思是由操作系统来负责二进制日志文件的同步工作。
sync_binlog=1
#全局增量值。
auto_increment_increment=2
#全局增量起始值。
一般地,需要设置auto_increment_increment和auto_increment_offset是
#在多台主mysql同时工作的情况下,进行分表处理才需要的。
auto_increment_offset=1
3进入MYSQL的SHELL
(1)A服务器:
mysql>flushtableswithreadlock\G
QueryOK,0rowsaffected(0.00sec)
mysql>showmasterstatus\G
File:
mysql-bin.000001
Position:
98
Binlog_Do_DB:
backup
Binlog_Ignore_DB:
mysql
1rowinset(0.00sec)
(2)B服务器:
mysql>flushtableswithreadlock\G
QueryOK,0rowsaffected(0.00sec)
mysql>showmasterstatus\G
File:
mysql-bin.000001
Position:
98
Binlog_Do_DB:
backup
Binlog_Ignore_DB:
mysql
1rowinset(0.00sec)
完成以上两步后,然后备份自己的数据,保持两个机器的数据一致。
4在各自机器上执行CHANGEMASTERTO命令
(1)A服务器:
mysql>changemasterto
->master_host='192.168.1.12',
->master_user='backup2',
->master_password='123456',
->master_log_file='mysql-bin.000001',
->master_log_pos=598;
QueryOK,0rowsaffected(0.01sec)
mysql>startslave;
QueryOK,0rowsaffected(0.00sec)
(2)B服务器:
mysql>changemasterto
->master_host='192.168.1.10',
->master_user='backup1',
->master_password='123456',
->master_log_file='mysql-bin.000001',
->master_log_pos=98;
QueryOK,0rowsaffected(0.01sec)
mysql>startslave;
QueryOK,0rowsaffected(0.00sec)
5查看各自机器上的IO进程和SLAVE进程是否都开启
在A,B服务器中分别执行如下操作:
mysql>showslavestatus\G;
如果下面三项显示为这样,则表示已经启动正常。
Slave_IO_State:
Waitingformastertosendevent
Slave_IO_Running:
Yes
Slave_SQL_Running:
Yes
还可以在A,B服务器中分别执行如下操作:
(1)A服务器:
mysql>showprocesslist\G
***************************1.row***************************
Id:
1
User:
systemuser
Host:
db:
NULL
Command:
Connect
Time:
2130
State:
Waitingformastertosendevent
Info:
NULL
***************************2.row***************************
Id:
2
User:
systemuser
Host:
db:
NULL
Command:
Connect
Time:
1223
State:
Hasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit
Info:
NULL
***************************3.row***************************
Id:
3
User:
root
Host:
localhost
db:
test
Command:
Query
Time:
0
State:
NULL
Info:
showprocesslist
***************************4.row***************************
Id:
4
User:
backup2
Host:
192.168.1.12:
3307
db:
NULL
Command:
BinlogDump
Time:
1398
State:
Hassentallbinlogtoslave;waitingforbinlogtobeupdated
Info:
NULL
4rowsinset(0.00sec)
(2)B服务器
mysql>showprocesslist\G
***************************1.row***************************
Id:
19
User:
systemuser
Host:
db:
NULL
Command:
Connect
Time:
2130
State:
Waitingformastertosendevent
Info:
NULL
***************************2.row**************************