MySQL主从及主主环境部署

主从同步

主机环境

mysql的安装可以参考：https://www.cnblogs.com/brianzhu/p/8575243.html
Centos7版本
master：192.168.192.128
slave: 192.168.192.129

注意下面几点：
1）要保证同步服务期间之间的网络联通。即能相互ping通，能使用对方授权信息连接到对方数据库（防火墙开放3306端口）。
2）关闭selinux。
3）同步前，双方数据库中需要同步的数据要保持一致。这样，同步环境实现后，再次更新的数据就会如期同步了.

实现过程

为了测试效果，先在master机器上创建数据库并插入一些数据
创建数据库
mysql> create database brian character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

切到新创建的数据中创建表结构
mysql> use brian;
Database changed
mysql> create table if not exists haha (id int(10) primary key auto_increment,name varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)

连续插入两条数据
mysql> insert into brian.haha values(1,"zhujingzhi"),(2,"xiaozhi");
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看表中的数据
mysql> select * from brian.haha;
+----+------------+
| id | name       |
+----+------------+
|  1 | zhujingzhi |
|  2 | xiaozhi    |
+----+------------+
2 rows in set (0.00 sec)

------------------------------------------------------------------------------------------------------
温馨提示：
修改库或表的字符集
mysql> alter database brian default character set utf8;                      # 修改brian库的字符集
mysql> alter table brian.haha default character set utf8;                    # 修改brian.haha表的字符集
    
添加主键
mysql> Alter table brian.haha add primary key(id);                           # brian.haha表的id添加主键
mysql> Alter table brian.haha change id id int(10) not null auto_increment;  # 自增长属性
    
删除主键时要先删除自增长，再删除主键
mysql> Alter table brian.haha change id id int(10);                          # 删除自增长
mysql> Alter table brian.haha drop primary key;                              # 删除主建
------------------------------------------------------------------------------------------------------

下面是master数据库上的操作：
1）设置master数据库的my.cnf文件（在[mysqld]配置区域添加下面内容）
[root@master1 ~]# vim /etc/my.cnf
.........
server-id=1                     #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin               #开启bin-log，并指定文件目录和文件名前缀
binlog-do-db=brian              #需要同步的数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
binlog-ignore-db=mysql          #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1                 #确保binlog日志写入后与硬盘同步
binlog_checksum = crc32         #跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed           #bin-log日志文件格式，设置为MIXED可以防止主键重复。
validate_password_policy=0      #指定密码策略
validate_password = off         #禁用密码策略

----------------------------------------------------------------------------------------------------------------------------------------------------------------
温馨提示：在主服务器上最重要的二进制日志设置是sync_binlog，这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上，即使服务器崩溃也会把事件写入日志中。
sync_binlog这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于"sync_binlog"参数的各种设置的说明如下：
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倍甚至更多。
----------------------------------------------------------------------------------------------------------------------------------------------------------------

2）导出master数据库多余slave数据库中的数据，然后导入到slave数据库中。保证双方在同步环境实现前的数据一致。
导出数据库之前先锁定数据库
mysql> flush tables with read lock; # 数据库只读锁定命令，防止导出数据库的时候有数据写入。unlock tables命令解除锁定
Query OK, 0 rows affected (0.00 sec)    

导出master数据库中多余的brian库(master数据库的root用户登陆密码：12345)
[root@master1 ~]# mysqldump -uroot brian -p12345 > /opt/brian.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
上面给出了一个警告，意思是在命令行使用密码是不安全的(引起的原因是我们在-p后面直接写上了密码)
温馨提示：在生产环境中当然是不建议直接在命令行写密码的

将导出的sql文件上传到slave机器上
[root@master1 ~]# scp /opt/brian.sql root@192.168.192.129:/opt/
[root@master1 ~]# rsync -e "ssh -p22" -avpgolr /opt/brian.sql 192.168.192.129:/opt/    # rsync的方式需要服务器上安装rsync包(yum -y install rsync)

3）设置数据同步用户并赋予权限
mysql> grant replication slave,replication client,select on *.* to slave@'192.168.192.%' identified by 'Slave@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

--------------------------------------------------------------------------------
温馨提示：
权限查看方式
mysql> show grants;
mysql> show grants for slave@'192.168.192.%';
--------------------------------------------------------------------------------

4）查看主服务器master状态(注意File与Position项，从服务器需要这两项参数)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      630 | brian        | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


下面是slave数据库上的操作：
1）设置slave数据库的my.cnf配置文件
[root@node1 ~]# vim /etc/my.cnf
........
server-id=2                     #数据库唯一ID，主从的标识号绝对不能重复
log-bin=mysql-bin               #启动MySQ二进制日志系统
replicate-do-db=brian           #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
replicate-ignore-db=mysql       #不同步mysql系统数据库
slave-skip-errors = all         #跳过所有的错误错误，继续执行复制操作
validate_password_policy=0      #指定密码策略
validate_password = off         #禁用密码策略

-----------------------------------------------------------------------------------------------
温馨提示：
当只针对某些库的某张表进行同步时，如下，只同步brian库的haha表和zjz库的heihei表：
replicate-do-db = brian
replicate-wild-do-table = brian.haha       //当只同步几个或少数表时，可以这样设置。注意这要跟上面的库指定配合使用；
replicate-do-db = zjz
replicate-wild-do-table = brian.heihei      //如果同步的库的表比较多时，就不能这样一一指定了，就把这个选项配置去掉，直接根据指定的库进行同步。
-----------------------------------------------------------------------------------------------


2）在slave数据库中导入从master传过来的数据
先创建一个brian空库，否则下面导入数据时会报错说此库不存在
mysql> create database brian character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

切到新创建的数据中
mysql> use brian;
Database changed
导入sql文件(下面会出现很多OK的输出)
mysql> source /opt/brian.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
...........

3）配置主从同步指令
设置之前先把从库停掉
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

配置主从
mysql> change master to master_host='192.168.192.128',master_port=3306,master_user='slave',master_password='Slave@123',master_log_file='mysql-bin.000001',master_log_pos=630; 
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启动主从
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看同步状态(Slave_IO_Running和Slave_SQL_Running为YES表示主从配置成功)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.192.128
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 630
               Relay_Log_File: node1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: brian
          Replicate_Ignore_DB: mysql

查看slave数据库中的数据情况
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| brian              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from brian.haha;
+----+------------+
| id | name       |
+----+------------+
|  1 | zhujingzhi |
|  2 | xiaozhi    |
+----+------------+
2 rows in set (0.00 sec)

下面测试下Mysql主从同步的效果
现在主数据库上写入新数据
mysql> insert into brian.haha values(200,"zjz");           
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
上面错误的原因是因为刚刚在导出的时候加锁了还未解锁

解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

插入数据
mysql> insert into brian.haha values(200,"zjz");
Query OK, 1 row affected (0.00 sec)

然后在slave数据库上查看，发现master上新写入的数据已经同步过来了
mysql> select * from brian.haha;
+-----+------------+
| id  | name       |
+-----+------------+
|   1 | zhujingzhi |
|   2 | xiaozhi    |
| 200 | zjz        |
+-----+------------+
3 rows in set (0.00 sec)

至此，主从同步环境已经实现！

温馨提示：如果是新部署的环境，什么数据都没有的情况下，也不需要执行导入导出，直接配置主从就行，没有要指定的同步那个库，也可以在my.cnf中修改

主主同步

主机环境

主主复制的缺点和解决方法

根据上面的主从环境部署，master和slave已经实现同步，即在master上写入新数据，自动同步到slave。而从库只能读不能写，一旦从库有写入数据，就会造成主从数据不一致！
下面就说下Mysql主主复制环境，在slave上更新数据时，master也能自动同步过来。
---------------------------------------------------------------------------
温馨提示：
在做主主同步前，提醒下需要特别注意的一个问题：
主主复制和主从复制有一些区别，因为多主中都可以对服务器有写权限，所以设计到自增长重复问题，例如：
出现的问题（多主自增长ID重复）
1）首先在A和B两个库上创建test表结构;
2）停掉A，在B上对数据表test(存在自增长属性的ID字段)执行插入操作，返回插入ID为1;
3）然后停掉B，在A上对数据表test(存在自增长属性的ID字段)执行插入操作，返回的插入ID也是1;
4）然后同时启动A,B，就会出现主键ID重复

解决方法：
只要保证两台服务器上的数据库里插入的自增长数据不同就可以了
如：A插入奇数ID，B插入偶数ID，当然如果服务器多的话，还可以自定义算法，只要不同就可以了
在下面例子中，在两台主主服务器上加入参数，以实现奇偶插入！
记住:在做主主同步时需要设置自增长的两个相关配置，如下：
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，以此类推.....
这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。（针对的是有自增长属性的字段）
---------------------------------------------------------------------------

实现过程

1）在master上的my.cnf配置
[root@master1 ~]# vim /etc/my.cnf
.........
server-id=1                     #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin               #开启bin-log，并指定文件目录和文件名前缀
binlog-ignore-db=mysql          #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1                 #确保binlog日志写入后与硬盘同步
binlog_checksum = crc32         #跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed           #bin-log日志文件格式，设置为MIXED可以防止主键重复。
auto-increment-increment = 2    #自增长字段每次递增2
auto-increment-offset = 1       #自增长字段从1开始
slave-skip-errors = all         #跳过所有的错误错误，继续执行复制操作
validate_password_policy=0      #指定密码策略
validate_password = off         #禁用密码策略

重启MySQL
[root@master1 ~]# systemctl restart mysqld

数据同步授权（iptables防火墙开启3306端口，要确保对方机器能使用下面权限连接到本机mysql）
mysql> grant replication slave,replication client,select on *.* to slave@'192.168.192.%' identified by 'Slave@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁；锁住后，就不能往表里写数据，但是重启mysql服务后就会自动解锁！
注意该参数设置后，如果自己同步对方数据，同步前一定要记得先解锁！
mysql> flush tables with read lock;    
Query OK, 0 rows affected (0.00 sec)

查看服务器master状态(注意File与Position项，从服务器需要这两项参数)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


2）在slave上的my.cnf配置
[root@node1 ~]# vim /etc/my.cnf
.........
server-id=2                     #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin               #开启bin-log，并指定文件目录和文件名前缀
binlog-ignore-db=mysql          #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1                 #确保binlog日志写入后与硬盘同步
binlog_checksum = crc32         #跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed           #bin-log日志文件格式，设置为MIXED可以防止主键重复。
auto-increment-increment = 2    #自增长字段每次递增2
auto-increment-offset = 2       #自增长字段从1开始
slave-skip-errors = all         #跳过所有的错误错误，继续执行复制操作
validate_password_policy=0      #指定密码策略
validate_password = off         #禁用密码策略

重启MySQL
[root@node1 ~]# systemctl restart mysqld

数据同步授权（iptables防火墙开启3306端口，要确保对方机器能使用下面权限连接到本机mysql）
同理，slave也要授权给master机器远程同步数据的权限
mysql> grant replication slave,replication client,select on *.* to slave@'192.168.192.%' identified by 'Slave@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁；锁住后，就不能往表里写数据，但是重启mysql服务后就会自动解锁！
注意该参数设置后，如果自己同步对方数据，同步前一定要记得先解锁！
mysql> flush tables with read lock;    
Query OK, 0 rows affected (0.00 sec)

查看服务器slave状态(注意File与Position项，从服务器需要这两项参数)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      640 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3）执行主张同步操作
先在slave数据库上做同步master的设置。（确保slave上要同步的数据，提前在master上存在。最好双方数据保持一致）
mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.192.128',master_user='slave',master_password='Slave@123',master_log_file='master-bin.000003',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.192.128
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: node1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

这样就实现了slave－>master的同步环境


再在master数据库上做同步slave的设置。（确保slave上要同步的数据，提前在master上存在。最好双方数据保持一致）
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.192.129',master_user='slave',master_password='Slave@123',master_log_file='mysql-bin.000002',master_log_pos=640;  
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.192.129
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 640
               Relay_Log_File: master1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

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


4）最后测试下Mysql主主同步的效果
在master上写入新数据
mysql> select * from brian.haha;
+-----+------------+
| id  | name       |
+-----+------------+
|   1 | zhujingzhi |
|   2 | xiaozhi    |
| 200 | zhujingzhi |
+-----+------------+
3 rows in set (0.01 sec)

mysql> insert into brian.haha values(211,"xiaotiantian");
Query OK, 1 row affected (0.01 sec)

在slave数据库中查看，发现master新写入的数据已经同步过来了
mysql> select * from brian.haha;
+-----+--------------+
| id  | name         |
+-----+--------------+
|   1 | zhujingzhi   |
|   2 | xiaozhi      |
| 200 | zhujingzhi   |
| 211 | xiaotiantian |
+-----+--------------+
4 rows in set (0.00 sec)


在slave上删除数据
mysql> delete from brian.haha where id=200;
Query OK, 1 row affected (0.00 sec)

在master数据库中查看
mysql> select * from brian.haha;
+-----+--------------+
| id  | name         |
+-----+--------------+
|   1 | zhujingzhi   |
|   2 | xiaozhi      |
| 211 | xiaotiantian |
+-----+--------------+
3 rows in set (0.00 sec)

至此，主主同步环境已经实现！　　

posted @ 2018-12-21 10:55 Brian_Zhu 阅读(4129) 评论(1) 编辑收藏举报

刷新页面返回顶部

Brian Zhu

成功不是将来才有的，而是从决定去做的那一刻起，持续累积而成。

MySQL主从及主主环境部署

主从同步

主机环境

实现过程

主主同步

主机环境

主主复制的缺点和解决方法

实现过程

公告