一、Mariadb介绍

MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可 MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。在存储引擎方面,使用XtraDB(英语:XtraDB)来代替MySQLInnoDBMariaDBMySQL的创始人Michael Widenius(英语:Michael Widenius)主导开发,他早前曾以10亿美元的价格,将自己创建的公司MySQL AB卖给了SUN,此后,随着SUN被甲骨文收购,MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字

主从关系表示:主负责写入 从负责备份或者读取 单主机存在单点故障 需要添加一个备份机 也可以写入 但是不允许出现数据不一致的情况 设计如下
20171120112401476.png

二、双主多从安装

1、安装mariadb

参考博客:

2、主从环境

192.168.8.179(主)
192.168.8.191(从)
192.168.8.192(从)
192.168.8.193(主备)

3、配置一主多从

参考博客:

》》》主机179 配置

拷贝完整配置文件

cp -r /usr/share/mysql/my-large.cnf  /etc/my.cnf

修改my.cnf

server-id = 1 #数据库表示 默认是开启的
log-bin =  mysql-bin  #开启日志记录 默认是开启的 如果改行被注释 不可能是master show master status肯定是空
binlog-ignore-db = mysql   #忽略同步的数据库 
binlog-ignore-db = information_schema  #忽略同步的数据库
expire_logs_days  = 365         #日志文件过期天数,默认是 0,表示不过期
auto_increment_increment=2        #设定为主服务器的数量,防止auto_increment字段重复
auto_increment_offset=1           #自增长字段的初始值,在多台master环境下,不会出现自增长ID重
log-slave-updates=on      #表示把从库的写操作记录到binlog中,缺少之后,双主创建失败。双主同步时该项必须有

重启启动mariadb

systemctl restart mariadb

登录mysql

mysql -uroot -ppasswd

创建同步账户

grant replication slave on *.* to 'slave'@'%' identified by 'test';  

查看master状态(配置文件配置了log-bin就是master了)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000002 |      342 |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)

》》》主机193 配置

拷贝完整配置文件

cp -r /usr/share/mysql/my-large.cnf  /etc/my.cnf

修改my.cnf

server-id = 2 #数据库表示 默认是开启的
log-bin =  mysql-bin  #开启日志记录 默认是开启的 如果改行被注释 不可能是master show master status肯定是空
binlog-ignore-db = mysql   #忽略同步的数据库 
binlog-ignore-db = information_schema  #忽略同步的数据库
expire_logs_days  = 365         #日志文件过期天数,默认是 0,表示不过期
auto_increment_increment=2        #设定为主服务器的数量,防止auto_increment字段重复
auto_increment_offset=2           #自增长字段的初始值,在多台master环境下,不会出现自增长ID重
log-slave-updates=on      #表示把从库的写操作记录到binlog中,缺少之后,双主创建失败。双主同步时该项必须有

重启启动mariadb

systemctl restart mariadb

登录mysql

mysql -uroot -ppasswd

创建同步账户

grant replication slave on *.* to 'slave'@'%' identified by 'test';  

查看master状态(配置文件配置了log-bin就是master了)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000001 |      328 |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)

两台主服务器已经配置好了,现在需要将数据进行同步

设置同步
主服务器1:179服务器:

change master to master_host='192.168.8.193',master_user='slave',master_password='test',master_log_file='mysql-bin.000001',master_log_pos=328;

启动slave命令

MariaDB [(none)]> start slave;

执行完查看slave状态

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.193
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 512
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 739
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 512
              Relay_Log_Space: 1052
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

ERROR: No query specified

主服务器2:193服务器:

change master to master_host='192.168.8.179',master_user='slave',master_password='test',master_log_file='mysql-bin.000002',master_log_pos=342;

启动slave命令

MariaDB [(none)]> start slave;

执行完查看slave状态

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.179
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 526
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 526
              Relay_Log_Space: 868
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

ERROR: No query specified

如此双主便已经搭建好了

从数据库的配置可参考博客:

将191配置为179的slave,将192配置为193的slave

最后修改:2019 年 01 月 02 日 12 : 25 AM
如果觉得我的文章对你有用,请随意赞赏