mysql 5.7.20主从配置_mysql5.7.20主从和主主搭建

环境描述

mysql主从和主主同步

注意事项：

1)同步服务器网络联通，ping通，对方授权信息连接到对方数据库(防火墙开发3306)

2)关闭SELinux

3)同步前，双方数据库需要同步数据保持一致。

主从复制实现过程，test测试

master

>create database test character SET utf8 COLLATE utf8_general_ci;

>use test

>insert into test.haha values(1,"xxx"),(2,"xx");

>select * from test.haha;

修改库或表的字符集

mysql> alter database huanqiu default character set utf8;    //修改huanqiu库的字符集

mysql> alter table huanqiu.haha default character set utf8;  //修改huanqiu.haha表的字符集

添加主键

mysql> Alter table huanqiu.haha add primary key(id);     //将huanqiu.haha表的id添加主键

mysql> Alter table huanqiu.haha change id id int(10) not null auto_increment;  //自增长属性

删除主键时要先删除自增长，再删除主键

mysql> Alter table huanqiu.haha change id id int(10);    //删除自增长

mysql> Alter table huanqiu.haha drop primary key;     //删除主建

master数据库的操作

vi /etc/my.cnf

server-id=1

log-bin=mysql-bin             开启bin-log，指定文件目录和文件名前缀

binlog-ignore-db=mysql         不同步mysql系统数据库，如果多个不同库，就此格式另写几行，也可以一行，中间逗号分开。

binlog-do-db=test            需要同步的数据库，如果多个库，就此格式另写，如果不指明，去掉，表示同步所有库，除了ignore忽略的库

sync_binlog = 1                确保binlog日志写入后与硬盘同步

binlog_format = mixed          bin-log日志文件格式，设置为MIXED可以防止主键重复

binlog_checksum = crc32        跳过现有采用的checksum事件，mysql5.6版本后是binlog_checksum=crc32,低版本是none

server-id=1

log-bin=mysql-bin

binlog-ignore-db=mysql

binlog-do-db=test

sync_binlog = 1

binlog_format = mixed

binlog_checksum = crc32

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

主服务器上最重要的二进制日志设置是sync_binlog,mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上，

即使服务器崩溃也会把事件写入日志中。

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

导出master数据库多余slave数据库中的数据，然后导入到slave数据库中。

保证双方在同步环境实现前的数据一致

导出数据库之前先锁定数据库

mysql> flush tables with read lock;    #

数据库只读锁定命令，防止导出数据库的时候有数据写入。

mysql> unlock tables命令解除锁定

导出master数据库中多余的test库(master数据库的root用户登陆密码：123456)

mysqldump -uroot test -p123456 >/opt/test.sql

rsync  -e "ssh -p22" -avpgolr /opt/test.sql 182.148.15.237:/opt/

将导出的sql文件上传到slave机器上

设置数据同步权限

> grant replication slave,replication client on *.* to slave@'182.148.15.237' identified by "slave@123";

> flush privileges;

权限查看方式

show grants;

show grants for slave@'182.148.115.237';

查看主服务器master状态

show master status;

slave 库上的操作

1)设置slave数据库的my.cnf配置文件

vim /etc/my.cnf

server-id=2                       #设置从服务器id，必须于主服务器不同

log-bin=mysql-bin               #启动MySQ二进制日志系统

replicate-do-db=test              #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步(除了ignore忽略的库)。

replicate-ignore-db=mysql          #不同步mysql系统数据库

slave-skip-errors = all           #跳过所有的错误错误，继续执行复制操作

server-id=2

log-bin=mysql-bin

replicate-do-db=test

replicate-ignore-db=mysql

slave-skip-errors = all

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

当只针对某些库的某张表进行同步时，如下，只同步test库的haha表和huanpc库的heihei表

replicate-do-db = test

replicate-wild-do-table = test.haha

当只同步几个或少数表时，可以这样设置。注意这要跟上面的库指定配合使用

replicate-do-db = huanpc

replicate-wild-do-table = huanpc.heihei

如果同步的库的表比较多时，就不能这样一一指定了，就把这个选项配置去掉，直接根据指定的库进行同步。

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

在slave数据库中导入从master传过来的数据。

> CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;

先创建一个test空库，否则下面导入数据时会报错说此库不存在。

> use test；

> source /opt/test.sql;   ＃导入master中多余的数据

配置主从同步指令

> stop slave;   ＃执行同步前，要先关闭slave

>在主mysql中查看 show master status；

>change  master to master_host='182.148.15.238',master_user='slave',master_password='slave@123',master_log_file='mysql-bin.000007',master_log_pos=120;

> start slave;

>show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 182.148.15.238

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 120

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 279

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes            成功后IO和SQL为yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql

.............

Seconds_Behind_Master: 0

测试下Mysql主从同步的效果

现在主数据库上写入新数据

mysql> unlock tables;    #解锁，否则新数据无法写入

mysql> insert into test.haha values(100,"anhui");、

然后在slave数据库上查看，发现master上新写入的数据已经同步过来了

mysql> select * from test.haha;

如果reboot后，可能同步起不来，IO线程和SQL线程显示为NO，可能发生了回滚

从最新的binlog日志开始重新配置master_log_file=最新前面的日志

change master to ......

主主复制实现过程

主主是为了实现数据的双向同步，利于做灾备和负载均衡。

从服务器到低要和哪个主同步，如果那个主挂了了，底下的从服务器是否能自动向另外一个主进行同步？

在这个过程中又是如何保证数据的完整的完整性呢？

这个过程中使用高可用MYSQL+KEEPALIVED

在做主主同步时需要设置自增长的两个相关配置，如下：

auto_increment_offset

表示自增长字段从那个数开始，取值范围是1 .. 65535。这个就是序号。如果有n台mysql机器，则从第一台开始分为设1，2...n

auto_increment_increment

表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535。如果有n台mysql机器，这个值就设置为n。

主主同步配置时，需要将两台服务器的：

auto_increment_increment     增长量都配置为2

auto_increment_offset        分别配置为1和2。这是序号，第一台从1开始，第二台就是2，以此类推.....

这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。(针对的是有自增长属性的字段)

记录主主同步操作过程

maseter

vi /usr/local/mysql/my.cnf

server-id = 1

log-bin = mysql-bin

binlog-ignore-db = mysql,information_schema

sync_binlog = 1

binlog_checksum = none

binlog_format = mixed

auto-increment-increment = 2

auto-increment-offset = 1

slave-skip-errors = all

/etc/init.d/mysql restart

数据同步授权

> grant replication slave,replication client on *.* to slave@'182.148.15.237' identified by "slave@123";

> flush privileges;

最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁；锁住后，就不能往表里写数据，但是重启mysql服务后就会自动解锁

> FLUSH TABLES WITH READ LOCK;    //注意该参数设置后，如果自己同步对方数据，同步前一定要记得先解锁！

> show master status;

slave数据库上

vi /etc/my.cnf

server-id = 2

log-bin = mysql-bin

binlog-ignore-db = mysql,information_schema

sync_binlog = 1

binlog_checksum = none

binlog_format = mixed

auto-increment-increment = 2

auto-increment-offset = 2

slave-skip-errors = all

/etc/init.d/mysql restart

slave也要授权给master机器远程同步数据的权限

> grant replication slave ,replication client on *.* to slave@'182.148.15.238' identified by "slave@123";

> flush privileges;

> FLUSH TABLES WITH READ LOCK;

> show master status;

执行主张同步操作

slave数据库上做同步master的设置。(确保slave上要同步的数据，提前在master上存在。最好双方数据保持一致)

> unlock tables;     //先解锁，将对方数据同步到自己的数据库中

> slave stop；

> change master to master_host='182.148.15.238',master_user='slave',master_password='slave@123',master_log_file='master-bin.000001',master_log_pos=1970;

> start slave;

> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 182.148.15.238

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1970

Relay_Log_File: mysql-relay-bin.000003

Relay_Log_Pos: 750

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

再在master数据库上做同步slave的设置

和上面一样，改下master_host=182.148.15.237 master_log_file master_log_pos

show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 182.148.15.237

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 4136

Relay_Log_File: mysql-relay-bin.000003

Relay_Log_Pos: 750

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

..................

这样就实现了master－>slave的同步环境。至此，主主双向同步环境已经实现！

最后测试下Mysql主主同步的效果

在master上写入新数据

> select * from huanqiu.haha;

> insert into huanqiu.haha values(15,"guocongcong");

在slave数据库中查看，发现master新写入的数据已经同步过来了

> select * from huanqiu.haha;

在slave上删除数据

> delete from huanqiu.haha where id=100;

在master数据库中查看

> select * from huanqiu.haha;