mariadb集群部署

转发自公众号: 士全 专注: 云计算,区块链分享
原文链接(就是引流)::https://mp.weixin.qq.com/s?__biz=Mzg3ODg4NDU1NQ==&mid=2247483702&idx=1&sn=a1b59feef13cc930bf3cc19620210bdd&chksm=cf0da292f87a2b84ce23130ae1439c27fc8e486cf90068bdf75f50750ca1b2a54136c4f67856#rd

1.整体流程

  • 环境:Ubuntu 22.04(或者 Ubuntu 20.04)
  • 手动部署(3台或及以下)
    • 安装必要软件
    • 配置数据库
    • 重启服务并启动集群
  • ansible部署(3台以上)
    • 安装必要软件
    • 主要配置文件
      • hosts
      • mariadb_galera.yml
    • 部署
  • 验证
  • 集群恢复

2.方式1: 手动部署流程

2.1 安装必要软件(每台机器执行)

apt-get install -y mariadb-client mariadb-server rsync

2.2 修改配置文件

  • 修改节点名称和节点IP地址即可(vim /etc/my.conf.d/server.cnf)
[mariadb]
bind-address = 0.0.0.0
max_connections = 1000
datadir = /var/lib/mysql
#socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mariadb/mariadb.log
pid-file = /run/mariadb/mariadb.pid
[galera]
wsrep_on = ON
wsrep_node_name = "local_node_name"   # 修改本节点名称
wsrep_node_address = "local_node_address" # 修改本节点IP地址
wsrep_cluster_name = "mariadb_galera_cluster"
wsrep_provider = /usr/lib/galera/libgalera_smm.so  # 不通版本及硬件galera所在程序所在位置可能不通,例如大多数教程上的:/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://controller1,controller2,controller3" # 根据集群数量调整
binlog_format = row
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_slave_threads = 4
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1024M
wsrep_sst_method = rsync

2.3 启动集群

  • 初始化每台maraiadb节点
# 设置mysql密码
export mysqlpasswd=123456
mysql -u root -p$mysqlpasswd -e "use mysql;ALTER USER 'root'@'localhost' IDENTIFIED BY '$mysqlpasswd';"
mysql -u root -p$mysqlpasswd -e "grant all privileges on *.* to 'root'@'%' identified by '$mysqlpasswd;"
systemctl stop mariadb
  • 主节点执行
galera_new_cluster
  • 非主节点执行
systemctl start mariadb

3.方式1:ansible批量部署

  • 安装基础软件
apt-get ansible sshpass
  • 3.1./etc/ansible/ansible.cfg
mkdir /etc/ansible/
tee /etc/ansible/ansible.cfg <<- 'EOF'
[defaults]
host_key_checking=False
EOF
  • 3.2.hosts文件
[all]
controller1 ansible_host=192.168.75.11
controller2 ansible_host=192.168.75.12
controller3 ansible_host=192.168.75.13
[all:vars]
ansible_user=emcloud
ansible_ssh_pass=em!@#123
ansible_sudo_pass=em!@#123
mysqlpasswd=em@mysql
  • 3.3./etc/my.conf.d/server.cnf或/etc/mysql/conf.d/mysql.cnf(centos配置文件为cfg后缀)
[server]
[mariadb]
bind-address = 0.0.0.0
max_connections = 1000
datadir = /var/lib/mysql
#socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mariadb/mariadb.log
pid-file = /run/mariadb/mariadb.pid
[galera]
wsrep_on = ON
wsrep_node_name = "local_node_name"   # 修改本节点名称
wsrep_node_address = "local_node_address" # 修改本节点IP地址
wsrep_cluster_name = "mariadb_galera_cluster"
wsrep_provider = /usr/lib/galera/libgalera_smm.so  # 不通版本及硬件galera所在程序所在位置可能不通,例如大多数教程上的:/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://controller1,controller2,controller3" # 根据集群数量调整
binlog_format = row
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_slave_threads = 4
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1024M
wsrep_sst_method = rsync
[mariadb]
  • 3.4.install-mariadb-cluster.yml文件
 ---
 - hosts: all # 定义所要操作的主机组
   become: yes
   gather_facts: false
   remote_user: emcloud # 定义ssh的用户名
   tasks:
    - name: install mariadb
      shell: apt-get install -y mariadb-client mariadb-server rsync
    - name: start mariadb
      shell: systemctl start mariadb
    - name: password mariadb
      shell: mysql -u root -p{{ mysqlpasswd }} -e "use mysql;ALTER USER 'root'@'localhost' IDENTIFIED BY '{{ mysqlpasswd }}';"
    - name: grant all
      shell: mysql -u root -p{{ mysqlpasswd }} -e "grant all privileges on *.* to 'root'@'%' identified by '{{ mysqlpasswd }};"
    - name: stop mariadb
      shell: systemctl stop mariadb
    - name: cp config
      copy: src=/root/ansible/openstack/etc/mariadb/openstack.cfg dest=/etc/mysql/conf.d/openstack.cnf
    - name: modified config file node_address
      shell: sed -i 's|local_node_address|{{ ansible_host }}|g' /etc/mysql/conf.d/openstack.cnf
    - name: modified config file node_name
      shell: sed -i 's|local_node_name|{{ inventory_hostname }}|g' /etc/mysql/conf.d/openstack.cnf
    - name: start mariadb controller1
      shell: galera_new_cluster
      when: ansible_fqdn=='controller1'
    - name: start mariadb controller2
      shell: systemctl start mariadb
      when: ansible_fqdn=='controller2'
    - name: start mariadb controller3
      shell: systemctl start mariadb
      when: ansible_fqdn=='controller3'

3.5 部署并验证

ansible-playbook install-mariadb-cluster.yml -i hostspath -b

4 验证(查看集群数量)

root@shqiuan: / # mysql -uroot -p123456 -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

5 修复集群

  • 当所有节点挂掉时,为保证数据为最新的,需要将最后一台down掉的机器做为主节点方可成功修复
# 主节点执行
galera_new_cluster
# 非主节点执行
systemctl start mariadb
```## 1.整体流程
+ 环境:Ubuntu 22.04(或者 Ubuntu 20.04)
+ 手动部署(3台或及以下)
  + 安装必要软件
  + 配置数据库
  + 重启服务并启动集群
+ ansible部署(3台以上)
  + 安装必要软件
  + 主要配置文件
    + hosts
    + mariadb_galera.yml
  + 部署
+ 验证
+ 集群恢复
## 2.方式1: 手动部署流程
### 2.1 安装必要软件(每台机器执行)
```bash
apt-get install -y mariadb-client mariadb-server rsync

2.2 修改配置文件

  • 修改节点名称和节点IP地址即可(vim /etc/my.conf.d/server.cnf)
[mariadb]
bind-address = 0.0.0.0
max_connections = 1000
datadir = /var/lib/mysql
#socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mariadb/mariadb.log
pid-file = /run/mariadb/mariadb.pid
[galera]
wsrep_on = ON
wsrep_node_name = "local_node_name"   # 修改本节点名称
wsrep_node_address = "local_node_address" # 修改本节点IP地址
wsrep_cluster_name = "mariadb_galera_cluster"
wsrep_provider = /usr/lib/galera/libgalera_smm.so  # 不通版本及硬件galera所在程序所在位置可能不通,例如大多数教程上的:/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://controller1,controller2,controller3" # 根据集群数量调整
binlog_format = row
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_slave_threads = 4
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1024M
wsrep_sst_method = rsync

2.3 启动集群

  • 初始化每台maraiadb节点
# 设置mysql密码
export mysqlpasswd=123456
mysql -u root -p$mysqlpasswd -e "use mysql;ALTER USER 'root'@'localhost' IDENTIFIED BY '$mysqlpasswd';"
mysql -u root -p$mysqlpasswd -e "grant all privileges on *.* to 'root'@'%' identified by '$mysqlpasswd;"
systemctl stop mariadb
  • 主节点执行
galera_new_cluster
  • 非主节点执行
systemctl start mariadb

3.方式1:ansible批量部署

  • 安装基础软件
apt-get ansible sshpass
  • 3.1./etc/ansible/ansible.cfg
mkdir /etc/ansible/
tee /etc/ansible/ansible.cfg <<- 'EOF'
[defaults]
host_key_checking=False
EOF
  • 3.2.hosts文件
[all]
controller1 ansible_host=192.168.75.11
controller2 ansible_host=192.168.75.12
controller3 ansible_host=192.168.75.13
[all:vars]
ansible_user=emcloud
ansible_ssh_pass=em!@#123
ansible_sudo_pass=em!@#123
mysqlpasswd=em@mysql
  • 3.3./etc/my.conf.d/server.cnf或/etc/mysql/conf.d/mysql.cnf(centos配置文件为cfg后缀)
[server]
[mariadb]
bind-address = 0.0.0.0
max_connections = 1000
datadir = /var/lib/mysql
#socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mariadb/mariadb.log
pid-file = /run/mariadb/mariadb.pid
[galera]
wsrep_on = ON
wsrep_node_name = "local_node_name"   # 修改本节点名称
wsrep_node_address = "local_node_address" # 修改本节点IP地址
wsrep_cluster_name = "mariadb_galera_cluster"
wsrep_provider = /usr/lib/galera/libgalera_smm.so  # 不通版本及硬件galera所在程序所在位置可能不通,例如大多数教程上的:/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://controller1,controller2,controller3" # 根据集群数量调整
binlog_format = row
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_slave_threads = 4
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1024M
wsrep_sst_method = rsync
[mariadb]
  • 3.4.install-mariadb-cluster.yml文件
 ---
 - hosts: all # 定义所要操作的主机组
   become: yes
   gather_facts: false
   remote_user: emcloud # 定义ssh的用户名
   tasks:
    - name: install mariadb
      shell: apt-get install -y mariadb-client mariadb-server rsync
    - name: start mariadb
      shell: systemctl start mariadb
    - name: password mariadb
      shell: mysql -u root -p{{ mysqlpasswd }} -e "use mysql;ALTER USER 'root'@'localhost' IDENTIFIED BY '{{ mysqlpasswd }}';"
    - name: grant all
      shell: mysql -u root -p{{ mysqlpasswd }} -e "grant all privileges on *.* to 'root'@'%' identified by '{{ mysqlpasswd }};"
    - name: stop mariadb
      shell: systemctl stop mariadb
    - name: cp config
      copy: src=/root/ansible/openstack/etc/mariadb/openstack.cfg dest=/etc/mysql/conf.d/openstack.cnf
    - name: modified config file node_address
      shell: sed -i 's|local_node_address|{{ ansible_host }}|g' /etc/mysql/conf.d/openstack.cnf
    - name: modified config file node_name
      shell: sed -i 's|local_node_name|{{ inventory_hostname }}|g' /etc/mysql/conf.d/openstack.cnf
    - name: start mariadb controller1
      shell: galera_new_cluster
      when: ansible_fqdn=='controller1'
    - name: start mariadb controller2
      shell: systemctl start mariadb
      when: ansible_fqdn=='controller2'
    - name: start mariadb controller3
      shell: systemctl start mariadb
      when: ansible_fqdn=='controller3'

3.5 部署并验证

ansible-playbook install-mariadb-cluster.yml -i hostspath -b

4 验证(查看集群数量)

root@shqiuan: / # mysql -uroot -p123456 -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

5 修复集群

  • 当所有节点挂掉时,为保证数据为最新的,需要将最后一台down掉的机器做为主节点方可成功修复
# 主节点执行
galera_new_cluster
# 非主节点执行
systemctl start mariadb
``

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值