CentOS7,MySQL主从配置和读写分离(MySQL主从、MySQL读写分离、MySQL分布式、数据库读写分离、主从配置)(亲测)

开源中国社区最新推荐博客 / 2018-11-02 16:33

一、实验目标
搭建两台MySQL服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作。

二、测试环境
主数据库: CentOS7, MySQL15.1 , 192.168.190.133
从数据库: CentOS7, MySQL15.1 , 192.168.190.134

三、主从配置步骤

1、确保主数据库与从数据库里的数据一样
例如:主数据库里的a的数据库里有b,c,d表,那从数据库里的就应该有一个模子刻出来的a的数据库和b,c,d表
我这里在两台MySQL上都创建了个名为“test”的数据库来测试,如图:

2、在主数据库里创建一个同步账号

1)每个从数据库会使用一个MySQL账号来连接主数据库,所以我们要在主数据库里创建一个账号,并且该账号要授予 REPLICATION SLAVE 权限,你可以为每个从数据库分别创建账号,当然也可以用同一个!)

2)你可以用原来的账号不一定要新创账号,但你应该注意,这个账号和密码会被明文存放在master.info文件中,因此建议单独创一个只拥有相关权限的账号,以减少对其它账号的危害!)

3)创建新账号使用“CREATE USER”,给账号授权使用“GRANT”命令,如果你仅仅为了主从复制创建账号,只需要授予REPLICATION SLAVE权限。

4)下面来创建一个账号,账号名:repl,密码:repl123,只允许192.168.190.的IP段登录,如下:

    mysql> CREATE USER ‘repl’@’192.168.190.%’ IDENTIFIED BY ‘repl123’;
    mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.190.%’;

5)如果开发防火墙,可能要配置下端口,如下:

    firewall-cmd –zone=public –add-port=3306/tcp –permanent
    firewall-cmd –reload

3、配置主数据库

1)要主数据库,你必须要启用二进制日志(binary logging),并且创建一个唯一的Server ID,这步骤可能要重启MySQL。
2)主服务器发送变更记录到从服务器依赖的是二进制日志,如果没启用二进制日志,复制操作不能实现(主库复制到从库)。
3)复制组中的每台服务器都要配置唯一的Server ID,取值范围是1到(232)−1,你自己决定取值。
4)配置二进制日志和Server ID,你需要关闭MySQL和编辑my.cnf或者my.ini文件,在 [mysqld] 节点下添加配置。
5)下面是启用二进制日志,日志文件名以“mysql-bin”作为前缀,Server ID配置为1,如下:

    [mysqld]
    log-bin=mysql-bin
    server-id=1
    #网络上还有如下配置
    #binlog-do-db=mstest //要同步的mstest数据库,要同步多个数据库,就多加几个replicate-db-db=数据库名 #binlog-ignore-db=mysql //要忽略的数据库

提示1:如果你不配置server-id或者配置值为0,那么主服务器将拒绝所有从服务器的连接。
提示2:在使用InnoDB的事务复制,为了尽可能持久和数据一致,你应该在my.cnf里配置innodb_flush_log_at_trx_commit=1 和 sync_binlog=1;
For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should useinnodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file.
提示3:确保主服务器里的skip-networking选项未启用,如果网络被禁用,你的从服务器将不能与主服务器通信并且复制失败。

注意:
实际操作发现 /etc/my.cnf文件和教材说的不一样,可能我装的是MariaDB,如下:
e173c3fefb8e9d600fd5730116178bf2073.jpg

目测文件被放到了 /etc/my.cnf.d 目录里,如下:

6ef2259c002e2d73f9e200a2c5440434045.jpg

在 /etc/my.cnf.d/server.cnf 增加相关配置,如下

重启mariadb,如图:

29a98abdc6cad12f2701f815b592eeb6a61.jpg

查看主服务器状态,mysql> show master status;, 如图:

9f65009f5b984e22803e6a3e34394c5602c.jpg

(注意:记录好File和Position,后面要用)

4、配置从数据库

1)从服务器,同理,要分配一个唯一的Server ID,需要关闭MySQL,修改好后再重启,如下:

    [mysqld]
    server-id=2
    #可以指定要复制的库
    replicate-do-db = test #在master端不指定binlog-do-db,在slave端用replication-do-db来过滤
    replicate-ignore-db = mysql #忽略的库
    #网上还有下面配置
    #relay-log=mysqld-relay-bin

提示1:如果有多个从服务器,每个服务器的server-id不能重复,跟IP一样是唯一标识,如果你没设置server-id或者设置为0,则从服务器不会连接到主服务器。
提示2:一般你不需要在从服务器上启用二进制日志,如果你在从服务器上启用二进制日志,那你可用它来做数据备份和崩溃恢复,或者做更复杂的事情(比如这个从服务器用来当作其它从服务器的主服务器)
3b7b56140cb0dea1d5609005f681d561ee0.jpg

这是主服务器192.168.190.133

d5b1ffc988882e180f943794b91b444bf4e.jpg

副服务器192.168.190.134配置, MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=245;这个两个参数一定要对应主机的

290b2806708001a4ad1d832de1b843959d8.jpg

fafd1af18ca3b562e52da94269d79cf69ab.jpg

5、测试数据同步

测试,连接主服务器 192.168.190.133,添加了表 t_dnat,

CREATE TABLE `t_dnat` (

`id` int ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,

`sourceAddress` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘来源地址’ ,

`sourceNetmask` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘来源掩码位数’ ,

`sourceAddresstype` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘来源地址类型,1:单ip,2:ip段’ ,

`targetAddress` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘目的地址’ ,

`targetNetmask` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘目的掩码位数’ ,

`targetPort` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘目的端口’ ,

`targetAddresstype` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘目的地址类型,1:单ip,2:ip段’ ,

`nicName` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘网卡’ ,

`targetIpConverTo` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘目的的地址转换为’ ,

`targetPortConverTo` varchar ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘目的地址转换端口’ ,

`sort` int ( 11 ) NOT NULL COMMENT ‘排序’ ,

`isLog` tinyint ( 2 ) NULL DEFAULT 1 COMMENT ‘是否记录日志,0否,1是’ ,

`status` tinyint ( 2 ) NULL DEFAULT NULL COMMENT ‘状态:1:启用 0:不启用’ ,

`dnatName` varchar ( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘名称’ ,

PRIMARY KEY ( `id` ) USING BTREE,

UNIQUE INDEX `sort` ( `sort` ) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic ;

 

— —————————-

— Records of t_dnat

— —————————-

INSERT INTO `t_dnat` VALUES ( 5 , ‘172.168.2.8’ , ’25’ , ‘1’ , ‘172.168.2.188’ , ’26’ , ’80’ , ‘1’ , , ‘192.168.2.105’ , ’80’ , 2 , 1 , 1 , ‘test1’ );

INSERT INTO `t_dnat` VALUES ( 6 , ‘172.168.2.100’ , ‘172.168.2.200’ , ‘2’ , ‘192.168.2.100’ , ‘192.168.2.200’ , ’80’ , ‘2’ , , ‘172.16.2.105’ , ’80’ , 3 , 1 , 1 , ‘test2’ );

INSERT INTO `t_dnat` VALUES ( 13 , , , ‘1’ , ‘192.168.1.56’ , ’24’ , ’80’ , ‘1’ , , ‘192.168.1.57’ , ’80’ , 1 , 0 , 1 , ‘test201710131516’ );

 

SET FOREIGN_KEY_CHECKS = 1 ;

b755390cd343791c028fddad7aa1ca09184.jpg
1c36d979612bb3796955a8524e869db65a7.jpg

然后再连接上 192.168.190.134,发现也自己同步创建了表 t_dnat,如图:

4fee975e27bfb1779e5ae101fcc185fba14.jpg
9b58cb37c9cf92bc1c07c991cfa3e3f27da.jpg

至此,主从的配置已经完成了, 目前是在从库里面配置复制“test”这个库,

如果要添加其它库,可以在主服务器中添加“binlog-do-db”配置,或者在从服务器中添加“replicate-do-db”配置。

223916_bL9y_2663968.jpg

Shared via Inoreader