设置 MySql 数据同步.docx

1、设置 MySql 数据同步设置 MySql 数据同步A ip:61.160.200.204B ip:61.160.200.209show slave status G;show master status G;SHOW PROCESSLISTGA:设置1.增加一个用户最为同步的用户帐号：GRANT FILE ON *.* TO backup61.160.200.209 IDENTIFIED BY 1234 mysql GRANT REPLICATION SLAVE ON *.* TO repl% IDENTIFIED BY slavepass; 2.增加一个数据库作为同步数据库：/create

2、 database backupB:设置1.增加一个用户最为同步的用户帐号：GRANT FILE ON *.* TO backup61.160.200.204 IDENTIFIED BY 12342.增加一个数据库作为同步数据库：/create database backup主从模式：A-BA为master修改A mysql的my.ini文件。在mysqld配置项中加入下面配置：server-id=1log-bin#设置需要记录log 可以设置log-bin=/var/log/mysqlbak/mysqllog 设置日志文件的目录，#其中mysqllog是日志文件的名称，mysql将建立不同扩

3、展名，文件名为mysqllog的几个日志文件。binlog-do-db=juson_w6xx #指定需要日志的数据库重起数据库服务。用 show master status show slave status 命令看日志情况。mysql slave stop;mysql change master to Master_Log_File=mysqlbaklog.000002 ,Master_Log_Pos=1388;mysql slave start;mysql show slave statusGmysql change master to MASTER_HOST=61.160.200.209

4、,MASTER_USER=backup,MASTER_PASSWORD=1234,Master_Log_File=mysqlbaklog.000002,Master_Log_Pos=106390;change master to master_host=61.160.200.209,master_user=backup,master_password=1234,master_log_file=209mysqlbaklog.000001,master_log_pos=1745;B为slave修改B mysql的my.ini文件。在mysqld配置项中加入下面配置：server-id=2maste

5、r-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的配置信息，mysql在数据库目录下生成master.info所以如有要修改相关slave的配置要先

6、删除该文件。否则修改的配置不能生效。双机互备模式如果在A加入slave设置，在B加入master设置，则可以做B-A的同步。在A的配置文件中 mysqld 配置项加入以下设置：master-host=61.160.200.209master-user=backupmaster-password=1234replicate-do-db=juson_w6xxmaster-connect-retry=10slave-skip-errors=allrelay-log-purge=1在B的配置文件中 mysqld 配置项加入以下设置：log-bin=mysqllog binlog-do-db=juson

7、_w6xx注意：当有错误产生时*.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 不复制某个库

8、binlog-ignore-table = w6xx_yuming_links#61.160.200.209 数据库调整1.2边数据库配置文件作备份2.2边原始数据库文件作备份change master to master_host=61.160.200.209,master_user=backup,master_password=1234,master_log_file=209mysqlbaklog.000001,master_log_pos=1745;1,主从不能同步:show slave status;报错:Error xxx dosnt exist且show slave statusG

9、:Slave_SQL_Running: NOSeconds_Behind_Master: NULL解决方法:stop slave;set global sql_slave_skip_counter =1 ;start slave;之后Slave会和Master去同步 主要看:Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master是否为0，0就是已经同步了2,还需要做的一些优化与监视:show full processlist; /查看mysql当前同步线程号skip-name-resolve /跳过dns名称查询，有助于

10、加快连接及同步的速度max_connections=1000 /增大Mysql的连接数目，(默认100)max_connect_errors=100 /增大Mysql的错误连接数目,(默认10)查看日志一些命令1, show master statusG; 在这里主要是看log-bin的文件是否相同。 show slave statusG; 在这里主要是看: Slave_IO_Running=Yes Slave_SQL_Running=Yes 如果都是Yes,则说明配置成功.2,在master上输入show processlistG; mysql SHOW PROCESSLISTG * 1.

11、row * Id: 2 User: root Host: localhost:32931 db: NULL Command: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL 如果出现Command: Binlog Dump,则说明配置成功. stop slave #停止同步start slave #开始同步，从日志终止的位置开始更新。SET SQL_LOG_BIN=0|1 #主机端运行，需要super权限，用来开停日志，随意开停，会造成主机

12、从机数据不一致，造成错误SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。RESET MASTER #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTERRESET SLAVE #从机运行，清除日志同步位置标志，并重新生成master.info虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下，LOAD TABLE tblname FROM MASTER #从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，

13、需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值LOAD DATA FROM MASTER #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值CHANGE MASTER TO master_def_list #在线改变一些主机设置，多个用逗号间隔,比如CH

14、ANGE 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 offset, rows PURGE MASTER LOGS TO logn

15、ame ; PURGE MASTER LOGS BEFORE dateMy.cfgserver-id=1log-bin=204mysqlbakmysqllogbinlog-do-db=juson_w6xxmaster-host=61.160.200.209master-user=backupmaster-password=1234replicate-do-db=juson_w6xxmaster-connect-retry=10log-slave-updates My.cfglog-bin=209mysqlbaklogbinlog-do-db=juson_w6xxserver-id=2maste

16、r-host=61.160.200.204master-user=backupmaster-password=1234master-port=3306master-connect-retry=60replicate-do-db=juson_w6xxlog-slave-updates主主循环同步配置1 授权用户（1）A服务器：mysql grant replication slave,file on *.* to backup1192.168.1.12 identified by 123456;Query OK, 0 rows affected (0.00 sec) （2）B服务器：mysql

17、grant replication slave,file on *.* to backup2192.168.1.10 identified by 123456;Query OK, 0 rows affected (0.00 sec)mysql flush privileges;Query OK, 0 rows affected (0.00 sec)然后都停止MYSQL 服务器。2 配置文件在两个机器上的my.ini里面都开启二进制日志。（1）A服务器my.ini#服务器编号。server-id = 1log-bin=mysql-bin#启用从属服务器上的日志功能，使这台计算机可以用来构成一个镜

18、像链(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%#不复制某个

19、库replicate-ignore-db=mysql#复制完的sql语句是否立即从中继日志中清除，1表示立即清除relay-log-purge = 1#从服务器主机，用于show slave hosts生成从库清单report-host=slave-1#即不管发生什么错误，镜像处理工作也继续进行slave-skip-errors=all#每经过n次日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)。n=1是最安#全的做法，但效率最低。#默认设置是n=0，意思是由操作系统来负责二进制日志文件的同步工作。sync_binlog=1#全局增量值。auto_increment_increme

20、nt=2#全局增量起始值。一般地，需要设置auto_increment_increment和auto_increment_offset是#在多台主mysql同时工作的情况下,进行分表处理才需要的。auto_increment_offset=1 （2）B服务器my.ini#服务器编号server-id = 2log-bin=mysql-bin#启用从属服务器上的日志功能，使这台计算机可以用来构成一个镜像链(A-B-C)log-slave-updates#从库是否只读，0表示可读写，1表示只读read-only=1#只复制某个表#replicate-do-table=tablename#只复制某些

21、表（可用匹配符）#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#从服务器主机，用于show slave hos

22、ts生成从库清单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同时工作的情况下,进行分表处理才需要的。a

23、uto_increment_offset=13进入MYSQL的SHELL（1）A服务器：mysql flush tables with read lockGQuery OK, 0 rows affected (0.00 sec)mysql show master statusG File: mysql-bin.000001 Position: 98 Binlog_Do_DB: backup Binlog_Ignore_DB: mysql1 row in set (0.00 sec)（2）B服务器：mysql flush tables with read lockGQuery OK, 0 row

24、s affected (0.00 sec)mysql 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命令（1）A服务器：mysql change master to - master_host=192.168.1.12, - master_user=backup2, - master_p

25、assword=123456, - master_log_file=mysql-bin.000001, - master_log_pos=598;Query OK, 0 rows affected (0.01 sec)mysql start slave;Query OK, 0 rows affected (0.00 sec)（2）B服务器：mysql change master to - master_host=192.168.1. 10, - master_user=backup1, - master_password=123456, - master_log_file=mysql-bin.

26、000001, - master_log_pos=98;Query OK, 0 rows affected (0.01 sec)mysql start slave;Query OK, 0 rows affected (0.00 sec)5 查看各自机器上的IO进程和SLAVE进程是否都开启在A，B服务器中分别执行如下操作： mysqlshow slave status G; 如果下面三项显示为这样，则表示已经启动正常。Slave_IO_State: Waiting for master to send event Slave_IO_Running: YesSlave_SQL_Running:

27、Yes 还可以在A，B服务器中分别执行如下操作：（1） A服务器：mysql show processlistG* 1. row * Id: 1 User: system user Host: db: NULLCommand: Connect Time: 2130 State: Waiting for master to send event Info: NULL* 2. row * Id: 2 User: system user Host: db: NULLCommand: Connect Time: 1223 State: Has read all relay log; waiting f

28、or the slave I/O thread to update it Info: NULL* 3. row * Id: 3 User: root Host: localhost db: testCommand: Query Time: 0 State: NULL Info: show processlist* 4. row * Id: 4 User: backup2 Host: 192.168.1.12:3307 db: NULLCommand: Binlog Dump Time: 1398 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL4 rows in set (0.00 sec) （2） B服务器mysql show processlistG* 1. row * Id: 19 User: system user Host: db: NULLCommand: Connect Time: 2130 State: Waiting for master to send event Info: NULL* 2. row *