设置 MySql 数据同步Word文档下载推荐.docx

1、在mysqld配置项中加入下面配置：server-id=1log-bin#设置需要记录log 可以设置log-bin=/var/log/mysqlbak/mysqllog 设置日志文件的目录，#其中mysqllog是日志文件的名称，mysql将建立不同扩展名，文件名为mysqllog的几个日志文件。binlog-do-db=juson_w6xx #指定需要日志的数据库重起数据库服务。用 show master status show slave status 命令看日志情况。mysql slave stop; change master to Master_Log_File=mysqlbakl

2、og.000002 ,Master_Log_Pos=1388; slave start; show slave statusG change master to MASTER_HOST=,MASTER_USER=backup,MASTER_PASSWORD=,Master_Log_File=mysqlbaklog.000002,Master_Log_Pos=106390;change master to master_host=,master_user=,master_password=,master_log_file=209mysqlbaklog.000001,master_log_pos=

3、1745;B为slave修改B mysql的my.ini文件。server-id=2master-host=61.160.200.204master-user=backup #同步用户帐号master-password=1234master-port=3306master-connect-retry=60 预设重试间隔60秒replicate-do-db=juson_w6xx 告诉slave只做juson_w6xx数据库的更新slave-skip-errors=allrelay-log-purge=1重起数据库用show slave status看同步配置情况。注意：由于设置了slave的配置

4、信息，mysql在数据库目录下生成master.info所以如有要修改相关slave的配置要先删除该文件。否则修改的配置不能生效。双机互备模式如果在A加入slave设置，在B加入master设置，则可以做B-A的同步。在A的配置文件中 mysqld 配置项加入以下设置：master-host=61.160.200.209master-user=backupreplicate-do-db=juson_w6xxmaster-connect-retry=10在B的配置文件中 mysqld 配置项加入以下设置：log-bin=mysqllog binlog-do-db=juson_w6xx当有错误产生

5、时*.err日志文件。同步的线程退出，当纠正错误后要让同步机制进行工作，运行slave start重起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-t

6、able = w6xx_yuming_links#61.160.200.209 数据库调整1.2边数据库配置文件作备份2.2边原始数据库文件作备份1,主从不能同步:show slave status;报错:Error xxx dosnt exist且show slave statusG:Slave_SQL_Running: NOSeconds_Behind_Master: NULL解决方法:stop slave;set global sql_slave_skip_counter =1 ;start slave;之后Slave会和Master去同步 主要看:Slave_IO_Running: Y

7、esSeconds_Behind_Master是否为0，0就是已经同步了2,还需要做的一些优化与监视:show full processlist; /查看mysql当前同步线程号skip-name-resolve /跳过dns名称查询，有助于加快连接及同步的速度max_connections=1000 /增大Mysql的连接数目，（默认100）max_connect_errors=100 /增大Mysql的错误连接数目,（默认10）查看日志一些命令1, show master statusG; 在这里主要是看log-bin的文件是否相同。 show slave statusG; 在这里主要是看

8、: Slave_IO_Running=Yes Slave_SQL_Running=Yes 如果都是Yes,则说明配置成功.2,在master上输入show processlistG; SHOW PROCESSLISTG * 1. row * Id: 2 User: root Host: localhost:32931 db: Command: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog to be updated Info: 如果出现Command: Binlog Dump,则说明配置

9、成功.stop slave #停止同步start slave #开始同步，从日志终止的位置开始更新。SET SQL_LOG_BIN=0|1 #主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。RESET MASTER #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTERRESET SLAVE #从机运行，清除日志同步位置标志，并重新生成master.info虽然重新生成了master.

10、info,但是并不起用，最好，将从机的mysql进程重启一下，LOAD TABLE tblname FROM MASTER #从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值LOAD DATA FROM MASTER #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。CHANGE MASTER TO m

11、aster_def_list #在线改变一些主机设置，多个用逗号间隔,比如CHANGE MASTER TO MASTER_HOST=, MASTER_USER=replication MASTER_PASSWORD=bigs3cretMASTER_POS_WAIT（） #从机运行SHOW MASTER STATUS #主机运行，看日志导出信息SHOW SLAVE HOSTS #主机运行，看连入的从机的情况。SHOW SLAVE STATUS （slave）SHOW MASTER LOGS （master）SHOW BINLOG EVENTS IN logname FROM pos LIMIT

12、offset, rows PURGE MASTER LOGS TO ; PURGE MASTER LOGS BEFORE dateMy.cfglog-bin=204mysqlbakmysqlloglog-slave-updates log-bin=209mysqlbaklogmaster-connect-retry=60log-slave-updates主主循环同步配置1 授权用户（1）A服务器： grant replication slave,file on *.* to backup1192.168.1.12 identified by 123456Query OK, 0 rows aff

13、ected （0.00 sec）（2）B服务器：backup2192.168.1.10 flush privileges;然后都停止MYSQL 服务器。2 配置文件在两个机器上的my.ini里面都开启二进制日志。（1）A服务器my.ini#服务器编号。server-id = 1log-bin=mysql-bin#启用从属服务器上的日志功能，使这台计算机可以用来构成一个镜像链（A-B-C）#从库是否只读，0表示可读写，1表示只读read-only=1#只复制某个表#replicate-do-table=tablename#只复制某些表（可用匹配符）#replicate-wild-do-table

14、=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#从服务器主机，用于show slave hosts生成从库清单report-host=slave-1#即不管发

15、生什么错误，镜像处理工作也继续进行#每经过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 =

16、2report-host=slave-23进入MYSQL的SHELL flush tables with read lockG show master statusG File: mysql-bin.000001 Position: 98 Binlog_Do_DB: backup Binlog_Ignore_DB: mysql1 row in set （0.00 sec）完成以上两步后，然后备份自己的数据，保持两个机器的数据一致。4 在各自机器上执行CHANGE MASTER TO命令 change master to - master_host= master_user= master_pa

17、ssword= master_log_file=mysql-bin.000001 master_log_pos=598;Query OK, 0 rows affected （0.01 sec） start slave;192.168.1. 10 master_log_pos=98;5 查看各自机器上的IO进程和SLAVE进程是否都开启在A，B服务器中分别执行如下操作： 如果下面三项显示为这样，则表示已经启动正常。Slave_IO_State: Waiting for master to send event Slave_IO_Running:还可以在A，B服务器中分别执行如下操作：（1） A服

18、务器： show processlistG* 1. row * 1 system userCommand: Connect 2130* 2. row * 1223 Has read all relay log; waiting for the slave I/O thread to update it* 3. row * 3 localhost test Query 0 show processlist* 4. row * 4 backup2 192.168.1.12:3307 1398 waiting for binlog to be updated4 rows in set （0.00 sec）（2） B服务器 19* 2. row *