• 生活的道路一旦选定,就要勇敢地走到底,决不回头。——左拉
  • 坚强的信心,能使平凡的人做出惊人的事业。——马尔顿
  • 人不可有傲气,但不可无傲骨。 --徐悲鸿
  • 古之立大志者,不惟有超世之才,亦必有坚韧不拔之志。 --苏轼
  • 时间像海绵里的水,只要你愿意挤,总还是有的。 --鲁迅

Mysql5.7安装并配置多源复制(多主一从)

MySQL zkinogg 2年前 (2020-07-31) 261次浏览 0个评论

Mysql5.7安装并配置多源复制(多主一从)

环境准备

主机名 内网ip 外网ip 角色
db01 172.16.1.51 10.0.0.51 主库1
db02 172.16.1.52 10.0.0.52 主库2
db03 172.16.1.53 10.0.0.53 从库

一.二进制安装mysql5.7

##  db01,db02,db03同时执行
[root@db01 ~]# mkdir -p /server/tools
[root@db01 ~]# cd /server/tools/
[root@db01 /server/tools]# rz -E mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@db01 /server/tools]# ls
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

[root@db01 /server/tools]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 
[root@db01 ~]# mkdir /application
[root@db01 /server/tools]# mv mysql-5.7.26-linux-glibc2.12-x86_64  /application/mysql

[root@db01 ~]# rpm -qa |grep mariadb
mariadb-libs-5.5.64-1.el7.x86_64

[root@db01 ~]# yum remove -y mariadb-libs-5.5.64-1.el7.x86_64

[root@db01 ~]# useradd -s /sbin/nologin mysql -M

vim /etc/profile.d/mysql.sh
export PATH=/application/mysql/bin:$PATH
[root@db01 ~]# source /etc/profile
[root@db01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper


 [root@db01 ~]# chown -R mysql.mysql /application/*
 [root@db01 ~]# mkdir /data/mysql/data -p 
 [root@db01 ~]# chown -R mysql.mysql /data
 [root@db01 ~]# yum install -y libaio-devel
 [root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
 
 [root@db01 /etc/init.d]# cp /application/mysql/support-files/mysql.server  /etc/init.d/mysqld 

二.准备配置文件

db01主库1配置文件

[root@db01 ~]# cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
server_id=1
port=3306
log_bin = mysql-bin
sync_binlog = 1
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
port = 3306
default-character-set = utf8mb4

[client]
port = 3306
default-character-set = utf8mb4

db02主库2配置文件

[root@db02 ~]# cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
server_id=2
port=3306
log_bin = mysql-bin
sync_binlog = 1
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
port = 3306
default-character-set = utf8mb4

[client]
port = 3306
default-character-set = utf8mb4

db03从库配置文件

[root@db03 tools]# cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
server_id=3
port=3306
master_info_repository      = table
relay_log_info_repository   = table
report-port = 3306
report-host = 172.16.1.51
replicate-do-db = master1
replicate-do-db = master2
replicate_wild_do_table=master1.%
replicate_wild_do_table=master2.%
#使用replicate-do-db和replicate_wild_do_table的作用是:replicate-do-db配置在MySQL从库的my.cnf文件中,可以指定只复制哪个库的数据。但是这个参数有个问题就是主库如果在其他的schema环境下操作,其binlog不会被从库应用,从而出现异常。原因是设置replicate_do_db或replicate_ignore_db后,MySQL执行sql前检查的是当前默认数据库,所以跨库更新语句在Slave上会被忽略。所以,可以在Slave上使用 replicate_wild_do_table 和 replicate_wild_ignore_table 来解决跨库更新的问题。
[mysql]
port = 3306
default-character-set = utf8mb4

[client]
port = 3306
default-character-set = utf8mb4

三.启动数据库并登录

[root@db01 /etc/init.d]# service mysqld restart
[root@db02 /etc/init.d]# service mysqld restart
[root@db03 /etc/init.d]# service mysqld restart
#初始化时没设置密码,直接回车登录
[root@db01 tools]# mysql -uroot -p
Enter password: 


##查看db01主库binlog信息
mysql> show master status ;
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| mysql-bin.000002 |      154 | test3        | mysql,information_schema,performation_schema,sys |                   |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
1 row in set (0.00 sec)

##查看db02主库binlog信息
mysql> show master status;
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| mysql-bin.000002 |      438 | test4        | mysql,information_schema,performation_schema,sys |                   |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
1 row in set (0.00 sec)

四.配置多源复制

# 1.从库登录数据库
[root@db03 tools]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

# 2.配置change master to多源复制1
mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.16.1.51',
    -> MASTER_USER='slave',
    -> MASTER_PORT=3306,
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=154
    -> FOR CHANNEL 'master1';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.16.1.52',
    -> MASTER_USER='slave',
    -> MASTER_PORT=3306,
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=438
    -> FOR CHANNEL 'master2';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

# 3.开启主从
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


# 4.查看主从状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: db03-relay-bin-master1.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: master1,master2
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: master1.%,master2.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 534


*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.52
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 438
               Relay_Log_File: db03-relay-bin-master2.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: master1,master2
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: master1.%,master2.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 438
              Relay_Log_Space: 534
              Until_Condition: None

五.测试多源复制

# 1.在主库db01创建一些实例数据
mysql> create database master1;
Query OK, 1 row affected (0.00 sec)

mysql> use master1;
Database changed
mysql> CREATE TABLE `test1` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values(1,1);
Query OK, 1 row affected (0.02 sec)


# 2.在主库db02创建一些实例数据
mysql> create database master2;
Query OK, 1 row affected (0.00 sec)

mysql> use master2;
Database changed
mysql> CREATE TABLE `test2` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test2 values(1,1);
Query OK, 1 row affected (0.02 sec)

#3.在从库db03上查看数据是否成功复制
mysql> select * from master1.test1;
+------+-------+
| id   | count |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from master2.test2;
+------+-------+
| id   | count |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set (0.00 sec)
## 多源复制开启成功

总结

多主一从架构带来的好处(个人总结):

一、在从服务器进行数据汇总,如果我们的主服务器进行了分库分表的操作,为了实现后期的一些数据统计功能,往往需要把数据汇总在一起再统计。

二、如果我们想在从服务器时时对主服务器的数据进行备份,在MySQL 5.7之前每一个主服务器都需要一个从服务器,这样很容易造成资源浪费,同时也加大了DBA的维护成本,但MySQL 5.7引入多源复制,可以把多个主服务器的数据同步到一个从服务器进行备份。

三、MySQL 5.7的多源复制,能有效的解决分库分表的数据统计问题,同时也可以实现在一台从服务器对多台主服务器的数据备份。**

四、MySQL 5.7的多源复制的出现,我们就不需要使用MariaDB 的 多主一从的架构了,让很多小伙伴又看到了新的希望。**

喜欢 (0)
[17551054905]
分享 (0)

您必须 登录 才能发表评论!