2022云计算国赛真题: ansible部署galera高可用数据库集群

```
使用centos7.9镜像创建4台云主机,设置ansible节点主机名为ansible,另外3台云主机分别为node1,node2,node3,在ansible节点的/root目录下创建ansible工作目录cscc_galera,在该目录下编写install_galera.yaml剧本文件,在node1,node2,node3上部署Mariadb_galera高可用数据库集群,设置数据库密码为123456(软件包使用mariadb.tar中的mariadb-repo安装mariadb服务).
```

节点规划
               主机名        ip地址
ansible节点    ansible       192.168.100.10
node1节点      node1        192.168.100.20
node2节点      node2        192.168.100.30
node3节点      node3        192.168.100.40



1. 设置主机名
[root@localhost ~]# hostnamectl set-hostname ansible
[root@localhost ~]# su
[root@ansible ~]# 

[root@localhost ~]# hostnamectl set-hostname node1
[root@localhost ~]# su
[root@node1 ~]# 

[root@localhost ~]# hostnamectl set-hostname node2
[root@localhost ~]# su
[root@node2 ~]# 

[root@localhost ~]# hostnamectl set-hostname node3
[root@localhost ~]# su
[root@node3 ~]# 


2. 将ip地址映射成主机名(其他3台节点配置一样,此处省略)
[root@ansible ~]# vi /etc/hosts
192.168.100.10 ansible
192.168.100.20 node1
192.168.100.30 node2
192.168.100.40 node3
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6


3. 关闭防火墙和selinux(其他3台节点配置一样,此处省略)
[root@ansible ~]# systemctl stop firewalld && setenforce 0


4. ansible节点部署ansible服务(通过CRT远程工具将提供的ansible软件包上传到ansible节点)
[root@ansible ~]# tar -xvf ansible.tar.gz

删除centos自带的源并配置本地yum源安装ansible服务
[root@ansible ~]# mv /etc/yum.repos.d/* /home/
[root@ansible ~]# vi /etc/yum.repos.d/local.repo
[ansible]
name=ansible
baseurl=file:///root/ansible
gpgcheck=0
enabled=1

[root@ansible ~]# yum install ansible -y

检查是否安装完成
[root@ansible ~]# ansible --version
ansible 2.9.10
  config file = /etc/ansible/ansible.cfg
  configured module search path = [u'/root/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python2.7/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 2.7.5 (default, Oct 14 2020, 14:45:30) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)]


5. 配置ansible节点可以免密登录其他3台node节点
[root@ansible ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:svV0oqhTRNGB/tH37Xi49/OUg+AFu2XlxcKhsyXm+wI root@ansible
The key's randomart image is:
+---[RSA 2048]----+
|      .+..    .  |
|      o .    o o |
|     o   . .= +.o|
|      o . .o+=o..|
|     ...S.o++= o |
|      .=.+Eo*.o o|
|     .o . .+.. *.|
|    ..      ..oo=|
|    ..       .o+*|
+----[SHA256]-----+


[root@ansible ~]# ssh-copy-id root@node1
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host 'node1 (192.168.100.20)' can't be established.
ECDSA key fingerprint is SHA256:sGY4R7darwNovS7pGeQNm9FzQjanFFmnwJHO5PRo0RE.
ECDSA key fingerprint is MD5:fa:5d:85:af:69:0d:63:78:ac:4e:9a:5b:c1:a6:b2:60.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@node1's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'root@node1'"
and check to make sure that only the key(s) you wanted were added.


[root@ansible ~]# ssh-copy-id root@node2
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host 'node2 (192.168.100.30)' can't be established.
ECDSA key fingerprint is SHA256:sGY4R7darwNovS7pGeQNm9FzQjanFFmnwJHO5PRo0RE.
ECDSA key fingerprint is MD5:fa:5d:85:af:69:0d:63:78:ac:4e:9a:5b:c1:a6:b2:60.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@node2's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'root@node2'"
and check to make sure that only the key(s) you wanted were added.


[root@ansible ~]# ssh-copy-id root@node3
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host 'node3 (192.168.100.40)' can't be established.
ECDSA key fingerprint is SHA256:sGY4R7darwNovS7pGeQNm9FzQjanFFmnwJHO5PRo0RE.
ECDSA key fingerprint is MD5:fa:5d:85:af:69:0d:63:78:ac:4e:9a:5b:c1:a6:b2:60.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@node3's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'root@node3'"
and check to make sure that only the key(s) you wanted were added.


6. 修改ansible文件配置组

删除该文件里面的所有内容添加以下新的内容
[root@ansible ~]# vi /etc/ansible/hosts 
[node]
node1
node2
node3

[node1]
node1

[node2]
node2

[node3]
node3


7. 解压mariadb.tar软件包将mariadb-repo源拷贝到node1,node2,node3节点的/root目录下
[root@ansible ~]# tar -xvf mariadb.tar 
mariadb/
mariadb/mariadb-repo.tar.gz
mariadb/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
mariadb/schema.xml
[root@ansible ~]# tar -xvf mariadb/mariadb-repo.tar.gz 
[root@ansible ~]# scp -r mariadb-repo/ root@node1:/root
[root@ansible ~]# scp -r mariadb-repo/ root@node2:/root
[root@ansible ~]# scp -r mariadb-repo/ root@node3:/root

8. 创建ansible执行的工作目录编写执行playbook剧本的相关文件
[root@ansible ~]# mkdir cscc_galera
[root@ansible ~]# cd cscc_galera/


创建local.repo文件
[root@ansible cscc_galera]# vi local.repo
[centos]
name=centos
baseurl=file:///opt/centos   #自行挂载centos7.9镜像到/opt目录
gpgcheck=0
enabled=1
[mariadb]
name=mariadb
baseurl=file:///root/mariadb-repo
gpgcheck=0
enabled=1

创建server.cnf文件
[root@ansible cscc_galera]# vi server.cnf
[server]
[mysqld]
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://node1,node2,node3
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=0
[embedded]
[mariadb]
[mariadb-10.3]

创建playbook剧本文件
[root@ansible cscc_galera]# vi install_galera.yaml
---
- hosts: all
  remote_user: root
  tasks:
  - name: rm repo
    shell: rm -rf /etc/yum.repos.d/* 
  - name: copy repo
    copy: src=local.repo dest=/etc/yum.repos.d/
  - name: install mariadb
    yum: name=mariadb-server
  - name: start mariadb
    service: name=mariadb state=started enabled=yes
  - name: init_mysql
    shell: mysqladmin -uroot password 123456
  - name: stop mariadb
    service: name=mariadb state=stopped
  - name: copy server.cnf
    copy: src=server.cnf dest=/etc/my.cnf.d/server.cnf
- hosts: node1
  remote_user: root
  tasks:
  - name: start mariadb
    shell: /usr/sbin/mysqld --wsrep-new-cluster -u root &
- hosts: node2,node3
  remote_user: root
  tasks:
  - name: start mariadb
    shell: systemctl start mariadb


9. 执行剧本安装galera集群
[root@ansible cscc_galera]# ansible-playbook install_galera.yaml 
[WARNING]: Found both group and host with same name: node1
[WARNING]: Found both group and host with same name: node3
[WARNING]: Found both group and host with same name: node2

PLAY [all] *******************************************************************************************************

TASK [Gathering Facts] *******************************************************************************************
ok: [node3]
ok: [node2]
ok: [node1]

TASK [rm repo] ***************************************************************************************************
[WARNING]: Consider using the file module with state=absent rather than running 'rm'.  If you need to use command
because file is insufficient you can add 'warn: false' to this command task or set 'command_warnings=False' in
ansible.cfg to get rid of this message.
changed: [node2]
changed: [node3]
changed: [node1]

TASK [copy repo] *************************************************************************************************
changed: [node3]
changed: [node2]
changed: [node1]

TASK [install mariadb] *******************************************************************************************
changed: [node2]
changed: [node3]
changed: [node1]

TASK [start mariadb] *********************************************************************************************
changed: [node1]
changed: [node2]
changed: [node3]

TASK [init_mysql] ************************************************************************************************
changed: [node2]
changed: [node1]
changed: [node3]

TASK [stop mariadb] **********************************************************************************************
changed: [node1]
changed: [node2]
changed: [node3]

TASK [copy server.cnf] **********************************************************************************************
changed: [node1]
changed: [node2]
changed: [node3]

PLAY [node1] *****************************************************************************************************

TASK [Gathering Facts] *******************************************************************************************
ok: [node1]

TASK [start mariadb] *********************************************************************************************
changed: [node1]

PLAY [node2,node3] ***********************************************************************************************

TASK [Gathering Facts] *******************************************************************************************
ok: [node2]
ok: [node3]

TASK [start mariadb] *********************************************************************************************
changed: [node2]
changed: [node3]

PLAY RECAP *******************************************************************************************************
node1                      : ok=9    changed=7    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   
node2                      : ok=9    changed=7    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   
node3                      : ok=9    changed=7    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   

[root@ansible cscc_galera]# 


10. 验证,检查wsrep_cluster_size的值是否为3/wsrep_ready的状态是否为ON

[root@ansible cscc_galera]# ssh node1
Last login: Fri Aug 26 05:01:52 2022 from 192.168.100.1
[root@node1 ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.23-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 status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]> show status like 'wsrep_ready';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready   | ON    |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]>