MySQL 5.6 Replication
打开mysql主页，满篇介绍mysql5.6版本有多好，多牛。后来浏览了5.6的更新说明，说是强化了replication,还有人测试开启replication对性能影响不大，不像以前，影响性能明显。反而性能更好？那个叫mysql中国的网站测试说的。官网有说多线程啥的进行复制，好吧。我信了。但是安装网上老的配置方法配置主从模式失败，服务起不来，说找不到pid什么文件，错误已经忘啦~~不好意思。 于是乎，在官方下载最新的安装文档...全英文...一口一口的啃。 终于在1个小时前配置好了，是双主互备模式。master==master. 整理下配置方法。 安装mysql5.6.9(源码下载那个网站没有提供最新的5.6.10版本，而我又不想装RPM包，你懂的）。安装在这里略过，只要看解压后里面的INSTALL文件安装提示来就可以了。 我把mysql安装到了/usr/local/mysql目录，装完之后，有个my.cnf在/usr/local/mysql目录下面。 这个就是配置文件了，打开一看，里面就有一行...
-----------------下面我们开始配置-------------
两台服务器：mysql-m1 192.168.0.140 mysql-m2 192.168.0.141 打开mysql-m1的my.cnf文件，添加如下代码：binlog-format=ROW log-slave-updates=true gtid-mode=on # GTID only enforce-gtid-consistency=true # GTID only master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1server-id=1 report-port=3306 port=3306 log-bin=binlog report-host=192.168.0.140肯定有人好奇，为啥要加这些代码？好吧，我也不知道，官方就这么说的。（开玩笑了）。我把个个参数的意思原汁原味的写出来：•binlog-format: row-based replication is selected in order to test all of the MySQL 5.6 optimisations•log-slave-updates, gtid-mode, enforce-gtid-consistency, report-port and report-host: used to enable Global Transaction>•master-info-repository and>the crash-safe binlog/slave functionality (storing the information in transactional tables rather than flat files)•sync-master-info: set to 1 to ensure that no information is lost•slave-parallel-workers: sets the number of parallel threads to be used for applying received replication events when this server acts as a slave. A value of 0 would turn off themultithreaded slave functionality; if the machine has a lot of cores and you are using many databases within the server then you may want to increase this value in order to better exploitmulti-threaded replication•binlog-checksum,master-verify-checksumand slave-sql-verify-checksum: used to enable all of the replication checksum checks•binlog-rows-query-log-events: enables informational log events (specifically, the original SQL query) in the binary log when using row-based replication –thismakes troubleshooting simpler•log-bin: The server cannot act as a replication master unless binary logging is enabled. If you wish to enable a slave to assume the role of master at some point in the future (i.e. in the event of a failover or switchover), you also need to configure binary logging. Binary logging must also be enabled on the slave(s) when using Global Transaction>•server-id: The server_id variable must be unique amongst all servers in the replication topology and is represented by a positive integer value from 1 to 232好了，上面的参数都知道什么意思了吧。接下来，我们同样设置第二台服务器：binlog-format=ROWlog-slave-updates=truegtid-mode=on # GTID onlyenforce-gtid-consistency=true # GTID onlymaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1server-id=2report-port=3306port=3306log-bin=binlogreport-host=192.168.0.141注意，server-id=2,另外，report-host也改下。这两个配置文件改好之后重启服务器。重启完服务器之后，登录第二台服务器mysql-m2登录mysql mysql -u root -p
输入完用户名和密码之后：
> CHANGE MASTER TO MASTER_HOST=192.168.0.140, MASTER_USER='repl_user', MASTER_PASSWORD='billy';> START SLAVE;这样主从模式就做好了主-----》从-----------------------------------我们在第一台服务器上设置可远程登录账户：先登录mysql服务器：>Grant all privileges on *.* to 'admin'@'%'>红色字体分别为账户和密码。同样的，第二台服务器也这么操作。然后，我们在主服务器（mysql-m1)的test数据库下面建立一个表测试同步情况：登录mysql服务器：mysql -u root -p>use test;(装好后，mysql默认自带）。>create table abc(a int,b int,c int);创建好后插入数据。>insert into abc values(1,2,3);多执行几次然后select * from abc;查看数据插入进去了没有。(我后面有自己插入了几行）。mysql> select * from acc;+------+------+------+| a | b | c |+------+------+------+| 1 | 2 | 3 || 1 | 2 | 3 || 1 | 2 | 3 || 1 | 2 | 3 || 1 | 2 | 3 || 1 | 2 | 3 || 2 | 2 | 2 || 2 | 2 | 2 || 2 | 2 | 2 |+------+------+------+登录mysql-m2，查看是否有数据同步过来。同步过来了就是ok的了。-------------------------------------官方的文档只说了主从模式，我查了一下，要做双主模式，必须开启log-slave-updates=true这个选项。我看了看两台服务器的配置文件都有这个。然后呢，我自己试了一下。登录主服务器---mysql-m1登录mysql----mysql -u root -p输入密码执行：> CHANGE MASTER TO MASTER_HOST=192.168.0.141, MASTER_USER='admin', MASTER_PASSWORD='123456';> START SLAVE;没想到，真的就可以，没报错。>show slave status\G;两台服务器都能查询出来信息。===================总结=================官方这个文档我是明白了。它让每个slave都有当master的机会，如果一个master宕机了，执行：> CHANGE MASTER TO MASTER_HOST=192.168.0.*, MASTER_USER='repl_user', MASTER_PASSWORD='billy';> START SLAVE;这个操作，只要换个IP地址，可以把任何一台从机变成主机，当主机启动之后，再执行：> CHANGE MASTER TO MASTER_HOST=192.168.0.MASTER_IP, MASTER_USER='repl_user', MASTER_PASSWORD='billy';> START SLAVE;这样主从切换来回自如。不过，我真的不知道类似于heartbeat的功能有木有~~~~我不像业务中断，难道要在master上面做heartbeat?
页:
[1]