1+x云计算平台运维与开发职业技能等级证书（中级）- 主从数据库配置

双节点配置

配置yum源（双节点）

[root@db1 ~]# cat /etc/yum.repos.d/local.repo 
[centos]
name=centos
baseurl=http://172.16.100.66/centos
gpgcheck=0
enabled=1
[mall]
name=mall
baseurl=http://172.16.100.66/gpmall-repo
gpgcheck=0
enabled=1

配置host解析(双节点)

[root@db1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.200.30 db1
192.168.200.40 db2

安装mariadb服务(双节点)

yum install -y mariadb mariadb-server

启动数据库（双节点）

systemctl start mariadb  #启动数据库
systemctl enable mariadb  #开机自启

初始化数据库（双节点）

mysql_secure_installation 
密码设置123456

修改配制文件

db1

[root@db1 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = 30

db2

[root@mysql2 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = 40

重启mariadb

systemctl restart mariadb

db1配置

在主节点db1虚拟机上使用mysql命令登录MariaDB数据库，授权在任何客户端机器上可以以root用户登录到数据库

[root@db1 ~]# mysql -uroot –p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 137
Server version: 10.3.18-MariaDB-log 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)]> grant all privileges  on *.* to root@'%' identified by "123456";

在主节点db1数据库上创建一个user用户让从节点db2连接，并赋予从节点同步主节点数据库的权限，命令如下

MariaDB [(none)]> grant replication slave on *.* to 'user'@'db2' identified by '123456';

db2配置

配置从节点db2同步主节点db1

在从节点db2虚拟机上使用mysql命令登录MariaDB数据库，配置从节点连接主节点的连接信息
master_host为主节点主机名db1，master_user为在步骤（4）中创建的用户user，命令如下

[root@db2 ~]# mysql -uroot –p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 88
Server version: 10.3.18-MariaDB-log 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)]>  change master to master_host='db1',master_user='user',master_password='123456';

配置完毕主从数据库之间的连接信息之后，开启从节点服务。使用命令show slave status\G;并查看从节点服务状态，如果Slave_IO_Running和Slave_SQL_Running的状态都为YES，则从节点服务开启成功。查询结果如下图所示

MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: db1
                   Master_User: user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000003
           Read_Master_Log_Pos: 856
                Relay_Log_File: mysql2-relay-bin.000007
                 Relay_Log_Pos: 1069
         Relay_Master_Log_File: mysql-bin.000003
              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: 856
               Relay_Log_Space: 1379
               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: 30
                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
              Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 1
1 row in set (0.001 sec)

验证

db1

[root@db1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 5.5.65-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)]> create database test;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table demotables(id int not null primary key,name varchar(10),addr varchar(20));
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> insert into demotables values(1,'zhangsan','lztd');
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from demotables;
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | zhangsan | lztd |
+----+----------+------+
1 rows in set (0.00 sec)

db2

[root@db2 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 5.5.65-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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use test;
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 [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demotables     |
+----------------+
1 row in set (0.00 sec)
MariaDB [test]> select * from demotables;
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | zhangsan | lztd |
+----+----------+------+
1 rows in set (0.00 sec)