运维秃头男孩


我变秃了，也变亮了。

Centos 7 + Mariadb主从配置

  • ~4.89K 字

0, 环境

Snipaste_2020-08-07_11-10-43.png

1, 安装Mariadb数据库(所有服务器)

1
2
3
yum install mariadb mariadb-server -y
systemctl start mariadb.service
mysqladmin -u root password 'abc-123'

2, 配置主服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql -uroot -pabc-123

MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by 'abc-123' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> use db1;
Database changed

MariaDB [db1]> create table test (name VARCHAR(20), sex CHAR(1), birth DATE, birthaddr VARCHAR(20));
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)
MariaDB [db1]> quit

配置主节点

1
2
3
4
5
6
vim /etc/my.cnf
# 在[mysqld]配置段下面增加
server-id=1
log_bin=mysql-bin
binlog-do-db=db1
binlog-ignore-db=mysql

Snipaste_2020-08-07_12-23-43.png
重启mariadb

1
systemctl restart mariadb.service

查看是否配置正确, 如果没有下列数据请检查配置是否正确!
记下来下面的File文件名和Position数值, 后面会用到!

1
2
3
4
5
6
7
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 | db1          | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

导出数据库然后恢复到从服务器

1
2
3
4
5
6
7
8
9
10
[root@mysql-master ~]# mysqldump -uroot -p -B db1 > /tmp/db1.sql
Enter password: 
[root@mysql-master ~]# scp /tmp/db1.sql [email protected]:/tmp
The authenticity of host '192.168.137.134 (192.168.137.134)' can't be established.
ECDSA key fingerprint is SHA256:mzSMw1/liF0Hea4L07Wumsuv37nxXhFCWTHns7ZVJdM.
ECDSA key fingerprint is MD5:e6:f2:ae:19:59:e9:b9:a3:c9:bc:21:6b:a4:04:13:e0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.137.134' (ECDSA) to the list of known hosts.
[email protected]'s password: 
db1.sql                                             100% 2000     1.2MB/s   00:00

3, 配置从服务器

1
2
3
4
5
vim /etc/my.cnf
server-id=2
log_bin=mysql-bin
binlog-do-db=db1
binlog-ignore-db=mysql

Snipaste_2020-08-07_12-33-12.png
重启mariadb

1
systemctl restart mariadb.service

配置从节点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
[root@mysql-slave ~]# mysql -u root -p -B db1 < /tmp/db1.sql 
Enter password:
MariaDB [(none)]> change master to master_host='192.168.137.133',master_user='root',master_password='abc-123',master_log_file='mysql-bin.000001',master_log_pos=245;
Query OK, 0 rows affected (0.01 sec)

master_host        -->    主节点ip地址
master_user        -->    主节点mysql用户
master_password    -->    主节点用户密码
master_log_file    -->    主节点File文件名
master_log_pos     -->    主节点Position数值

MariaDB [(none)]> slave start;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.137.133
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 529
        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: 245
              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)

Snipaste_2020-08-07_12-47-36.png
同步测试
GIF 2020-8-7 13-01-14.gif

4, 报错处理

  1. 如果报下面的错, 那么就再重启一下mariadb
    1
    2
    MariaDB [(none)]> slave start;
    ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
  2. 如果报这个错, 那么就重启一下从节点的slave状态
    Snipaste_2020-08-07_13-07-17.png
    1
    2
    3
    4
    5
    MariaDB [db1]> slave stop;
    Query OK, 0 rows affected (0.01 sec)

    MariaDB [db1]> slave start;
    Query OK, 0 rows affected (0.00 sec)
本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可