mysql一主两从

mysql一主两从部署

服务器规划

ip	主机名	规格
11.0.1.52	controller	1c4g50g
11.0.1.46	node1	1c4g50g
11.0.1.47	node2	1c4g50g
11.0.1.48	node3	1c4g50g

linux 部署

三个节点操作 前提要关闭防火墙和selinux

# 配置hosts
11.0.1.46 node1
11.0.1.47 node2
11.0.1.48 node3
# 解压 mariadb-repo.tar.gz
[root@node1 ~] tar xvf mariadb-repo.tar.gz
# 配置 mariadb.repo
[root@node1 ~] cat > /etc/etc/yum.repos.d/mariadb.repo < eof
[centos]
name = centos
baseurl = http://11.0.1.1/centos/
gpgcheck=0
enabled=1
[mariadb]
name = MariaDB
baseurl = file:///root/mariadb-repo
gpgcheck=0
enabled=1
eof
# 清凉缓存 
[root@node1 ~] yum clean all && yum makecache
# 下载 mariadb-server,galera
[root@node1 ~] yum install -y mariadb-server galera
# 配置 mariadb 开机自启
[root@node1 ~] systemctl enable mariadb --now
# 关闭mariadb 
[root@node1 ~]systemctl stop mariadb
# 配置server.cnf
[root@node1 ~] cat > /etc/my.cnf.d/server.cnf <eof
[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]
eof

在node1节点执行集群初始化

[root@node1 ~] galera_new_cluster

在 node2，node3 重启 mariadb 服务

[root@node2 ~] systemctl start mariadb
[root@node3 ~] systemctl start mariadb

在 node 1 节点设置密码

[root@node1 ~] mysqladmin -uroot password 123456

检查集群效果

查看集群状态

[root@node1 ~]# ss -tunlp|grep -e  4567 -e  3306
tcp    LISTEN     0      128       *:4567                  *:*                   users:(("mysqld",pid=2675,fd=11))
tcp    LISTEN     0      80        *:3306                  *:*                   users:(("mysqld",pid=2675,fd=30))

查看是否启用galera插件

[root@node1 ~]# mysql -uroot -p123456 -e 'show status like "wsrep_ready";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready   | ON    |
+---------------+-------+

目前集群机器数

[root@node1 ~]# mysql -uroot -p123456 -e 'show status like "wsrep_cluster_size";'
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

查看连接的主机

[root@node1 ~]# mysql -uroot -p123456 -e 'show status like "wsrep_incoming_addresses";'
+--------------------------+----------------------------------------------+
| Variable_name            | Value                                        |
+--------------------------+----------------------------------------------+
| wsrep_incoming_addresses | 11.0.1.46:3306,11.0.1.48:3306,11.0.1.47:3306 |
+--------------------------+----------------------------------------------+

测试集群mariad数据是否同步

[root@node1 ~]# mysql -uroot -p123456 -e 'create database lizk;'
[root@node1 ~]# mysql -uroot -p123456 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lizk               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@node2 ~]# mysql -uroot -p123456  -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lizk               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@node3 ~]# mysql -uroot -p123456  -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lizk               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

ansible部署 mysql一主两从

先配置 ansible 进入 /data/asnible/mariadb-cluster 创建以下文件

# hosts
11.0.1.46 node1
11.0.1.47 node2
11.0.1.48 node3

# mariadb.repo
[centos]
name = centos
baseurl = http://11.0.1.1/centos/
gpgcheck=0
enabled=1
[mariadb]
name = MariaDB
baseurl = file:///root/mariadb-repo
gpgcheck=0
enabled=1

# 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]

# cscc_install.yaml
---
- hosts: all
  remote_user: root
  tasks:
  - name: copy hosts
    copy: src=hosts dest=/etc/hosts
  - name: rm repo
    shell: rm -rf /etc/yum.repos.d/*
  - name: scp mariadb-repo.tar.gz
    copy: src=mariadb-repo.tar.gz dest=/root
  - name: tar mariadb-repo
    shell: tar xvf mariadb-repo.tar.gz
  - name: copy repo
    copy: src=mariadb.repo dest=/etc/yum.repos.d/
  - name: clean and makecache repo
    shell: yum clean all && yum makecache
  - name: install mariadb,galera
    yum: name=mariadb-server,galera
  - name: start mariadb
    service: name=mariadb state=started enabled=yes
  - 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:  galera_new_cluster
- hosts: node2,node3
  remote_user: root
  tasks:
  - name: start mariadb
    service: name=mariadb state=started
- hosts: node1
  remote_user: root
  tasks:
  - name: set db password
    shell: mysqladmin -uroot password 123456

ansible 执行效果

上面用到 包可以去资源里面下载