mysql 主从配置 一主一从 、互为主从

一、一主一从 配置

1 环境：centos7

	192.168.10.134 主
    192.168.10.135 从

2 配置过程：

两台主机上分别安装 mariadb mariadb-server

yum install mariadb mariabdb-server -y

2.1. master：

192.168.10.134 主机修改配置文件：
在[mysqld] 下加入:
log-bin=jfedu-bin
server-id=1
relay-log=mariadb-relay-bin 该配置的作用请参加另一篇文章：https://blog.csdn.net/weixin_42808782/article/details/115414513

[root@node1 mariadb]# cat /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

log-bin=jfedu-bin
server-id=1
relay-log=mariadb-relay-bin

[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

启动服务:
systemctl start mariadb
登录数据库：
mysql
确认同步是否已开启：ON表示已开启

MariaDB [(none)]> show variables like '%log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
| sql_log_bin   | ON    |
+---------------+-------+
2 rows in set (0.00 sec)

log_bin和sql_log_bin的作用和区别：
log_bin 作用：

• 数据恢复 当出现故障或误操作时可以借助二进制文件恢复数据
• 数据同步 主服务器上所有的操作都在记录日志中，从服务器可以根据该日志来进行，以确保两个同步。因此，我们经常做的mysql-salva也是利用master的二进制日志来和master数据一致的。
  sql_log_bin 作用
• sql_log_bin 是动态变量，修改该变量时，可以只对当前会话生效（Session），也可以是全局的（Global），当全局修改这个变量时，只会对新的会话生效 （这意味当对当前会话也不会生效），因此一般全局修改了这个变量后，都要把原来的所有连接 kill 掉
  sql_log_bin的值是1|0, 当sql_log_bin的值为0的时候，本次连接mysql的session里面所输入的语句都不会被计入bin_log里面，也不会被丛库执行

之后进行授权操作：

MariaDB [(none)]> grant replication slave on *.* to jfedu@192.168.10.135 identified by "cui0116";
MariaDB [(none)]> flush privileged;

检查状态：

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

MariaDB [(none)]> 

2.2 从库 192.168.10.135 执行步骤：

MariaDB [(none)]> change master to master_host="192.168.10.134", master_user="jfedu", master_password="cui0116", master_log_file="jfedu-bin.000001", master_log_pos=473;
MariaDB [(none)]> slave start;
MariaDB [jfedu_tb2]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.134
                  Master_User: jfedu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: jfedu-bin.000001
          Read_Master_Log_Pos: 898
               Relay_Log_File: mariadb-relay-bin.000004
                Relay_Log_Pos: 529
        Relay_Master_Log_File: jfedu-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: 898
              Relay_Log_Space: 825
              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
1 row in set (0.00 sec)

status 结果中 Slave_IO_Running: 和 Slave_SQL_Running: 均为Yes 才是正常的；

2.3 主从同步测试

在主库 192.168.10.134 上创建数据库jfedu_tb ,并创建表 jfedu、插入数据：

MariaDB [(none)]> use jfedu_tb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [jfedu_tb]> show tables;
+--------------------+
| Tables_in_jfedu_tb |
+--------------------+
| jfedu              |
+--------------------+
1 row in set (0.00 sec)

MariaDB [jfedu_tb]> select * from jfedu;
+------+----------+
| id   | name     |
+------+----------+
|    3 | xiaoming |
+------+----------+
1 row in set (0.00 sec)

MariaDB [jfedu_tb]> 

从库 192.168.10.135 上查看数据已同步过来了：

MariaDB [jfedu_tb]> select * from jfedu;
+------+----------+
| id   | name     |
+------+----------+
|    3 | xiaoming |
+------+----------+
1 row in set (0.00 sec)

MariaDB [jfedu_tb]> 

二、互为主从 配置

1 环境：centos7

	192.168.10.134 主、从
    192.168.10.135 从、主

2 配置过程：

步骤在上述一主一从的基础上（其实就是master1-slave1的配置），增加如下步骤，其实就是增加了把135作为主、134作为备的步骤（master2-slave2的配置）：

2.1. master 2的配置

192,.168.10.135 作为主，my.cnf 中增加log-bin配置,log-bin=jfedu-zhu2：

[root@node2 mysql]# cat /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=2
log-bin=jfedu-zhu2
[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

登录master2，授权：

MariaDB [jfedu_tb]> grant replication slave on *.* to "jfedu-cong2"@"192.168.10.134" identified by "cui0116";
MariaDB [jfedu_tb]> flush privileges;
MariaDB [jfedu_tb]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| jfedu-zhu2.000001 |     563|              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [jfedu_tb]> 

2.2 slave2 的配置：

MariaDB [(none)]> change master to master_host="192.168.10.135", master_user="jfedu", master_password="cui0116", master_log_file="jfedu-zhu2.000001 ", master_log_pos=563;
MariaDB [(none)]> slave start;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.135
                  Master_User: jfedu-cong2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: jfedu-zhu2.000001
          Read_Master_Log_Pos: 563
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 1166
        Relay_Master_Log_File: jfedu-zhu2.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: 1119
              Relay_Log_Space: 1462
              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
1 row in set (0.00 sec)

2.3. master2-slave2 测试：

在master2 135 主机上创建数据库jfedu_tb2 ,创建表 student 并插入数据：

MariaDB [jfedu_tb2]> select * from student_info;
+-------+----------+
| sd_id | sd_name  |
+-------+----------+
|   222 | xiaowang |
+-------+----------+
1 row in set (0.00 sec)

MariaDB [jfedu_tb2]> 

在 slave2 134主机上验证数据是否已同步：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jfedu_tb           |
| jfedu_tb2          |
| mysql              |
| performance_schema |
| test               |
| zabbix             |
+--------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> use jfedu_tb2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [jfedu_tb2]> select * from 
Display all 752 possibilities? (y or n)
MariaDB [jfedu_tb2]> select * from student_info;
+-------+----------+
| sd_id | sd_name  |
+-------+----------+
|   222 | xiaowang |
+-------+----------+
1 row in set (0.00 sec)

MariaDB [jfedu_tb2]>