MariaDB互为主从配置

MariaDB数据库主从配置

环境

服务器1：Centos7.5 MariaDBV10 192.168.0.1
服务器2：Centos7.5 MariaDBV10 192.168.0.2

mysql配置

修改192.168.0.1服务器中的配置文件，命令：vim /etc/my.cnf，输入下列代码：

[root@localhost ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

########新增配置开始########
server-id=1                                           #自定义，与其他服务器不能一样
binlog-ignore-db = mysql                              #不同步的数据库名称
binlog-ignore-db = information_schema                 #不同步的数据库名称
log-bin=master-bin                                    #master信息log文件
relay-log=relay-bin                                   #slave信息log文件
sync-binlog = 1                                       #控制数据库的binlog刷到磁盘上去,1表示每次都提交
########新增配置结束########

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid


#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

备注：

1.每个服务器都需要添加server_id配置，各个服务器的server_id需要保证唯一性，实践中通常设置为服务器IP地址的最后一位

2.新增配置内容，必须在[mysqld]下，否则启动slave时报错：The server is not configured as slave; fix in config file or with CHANGE MASTER TO,log-error文件提示：Server id not set, will not start slave

重启mariadb服务

[root@localhost ~]# systemctl restart mariadb

或者

[root@localhost ~]# service   mariadb   restart       #需添加mariadb.service服务

清空master和slave日志

登陆数据库

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> reset master;
MariaDB [(none)]> reset slave;

##创建用户
服务器A(192.168.0.1)给服务器B(192.168.0.2)创建用户：

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'192.168.0.2' IDENTIFIED BY 'slaveuser';

服务器B(192.168.0.2)给服务器A(192.168.0.1)创建用户：

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'192.168.0.1' IDENTIFIED BY 'slaveuser';

分别给A，B服务器表锁定

MariaDB [(none)]> flush tables with read lock;

查看A，B服务器主机状态

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

根据主设备状态File和Position字段配置从设备服务器
A(192.168.0.1)服务器配置：

MariaDB [(none)]> change master to master_host='192.168.0.2',master_user='slaveuser',master_password='slaveuser',master_log_file='master-bin.000003',master_log_pos=584;
#master_host   B服务器的IP

B(192.168.0.2)服务器配置：

MariaDB [(none)]> change master to master_host='192.168.0.1',master_user='slaveuser',master_password='slaveuser',master_log_file='master-bin.000003',master_log_pos=584;
#master_host   A服务器的IP

##启动slave服务

MariaDB [(none)]> start slave;

##查看slave服务状态
A服务器（192.168.0.1）：

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.0.2
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes                          #  IO和SQL都为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: 245
              Relay_Log_Space: 245
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

最后需要解锁表

MariaDB [(none)]> unlock tables;

至此，MariaDB数据库主从配置全部完成。
遇到的问题

1.slave start 启动slave时报错，该问题为配置信息未写在[mysqld]下面。

2.问题1修改后，重新启动，仍然报错：

报错：ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log
错误日志：/var/log/mariadb/mariadb.log
[root@localhost ~]# cat /var/log/mariadb/mariadb.log
160915 12:52:02 [ERROR] Failed to open the relay log './mariadb-relay-bin.000001' (relay_log_pos 4)
160915 12:52:02 [ERROR] Could not find target log during relay log initialization
上网搜索解决方案：
 1.删除/var/lib/mysql/路径下the ‘master.info’ ‘mysqld-relay-bin.*’ ‘relay-log.info’ ‘relay-log-index.*’ 
    命令：rm -rf master.info,rm -rf *relay*
 2.重启服务：
    命令：[root@localhost mysql]# systemctl restart mariadb.service
 3.登陆mariadb:
    命令[root@localhost mysql]# mysql -u root -p
 4.MariaDB [(none)]> flush logs;         ##刷新日志
 5.MariaDB [(none)]> reset slave;        ##重置slave
 6.重新设置主从复制关系：
     MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.2',MASTER_USER='root', MASTER_PASSWORD='admin',MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS= 254;

++++++++++++++++++++++++++追加内容++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1.主服务器上File，Position两字段需要在从服务器上配置使用
启动后Slave_IO_Running: No；可刷新主服务器的log，重新获取file和position命令如下：

MariaDB [(none)]>  flush logs;
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+--------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+-------------------+----------+--------------+--------------------------+
| master-bin.000002 |      908 |              | mysql,information_schema |
+-------------------+----------+--------------+--------------------------+

从服务器重新授权

MariaDB [(none)]> change master to master_host='192.168.0.2',master_user='slaveuser',master_password='slaveuser',master_log_file='master-bin.000002',master_log_pos=908;

2.一台主设备向从设备同步数据时报错，提示“主键重复”。
该问题在网上通过skip方案解决

MariaDB [(none)]> stop slave;
MariaDB [(none)]> set global sql_slave_skip_counter=1; 
MariaDB [(none)]> start slave;

可以同步。

PS，网上查看还看到另一种解决方法
在报错的服务器的my.cnf配置文件中[mysqld]字段添加
binlog_format=mixed
该方法没试过。