6.mysql数据库主从复制与高可用集群搭建、ansible自动部署mysql

最新推荐文章于 2024-06-07 14:52:28 发布

果枝

最新推荐文章于 2024-06-07 14:52:28 发布

阅读量786

点赞数

文章标签： mysql linux 运维

本文链接：https://blog.csdn.net/weixin_45599881/article/details/109123448

版权

1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）

#准备环境：两台服务器：
10.0.0.8 	主节点--centos8     
10.0.0.18   从节点--centos8

1.1.主节点配置 10.0.0.8

1.#修改配置文件
[root@centos8 ~]# vim  /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server_id=8
log_bin=/data/logbin/mysql-bin

[root@centos8 ~]# systemctl restart mariadb

#建议优化主和从节点服务器的性能
#下面两项实现的是写日志的时候批量写，还是写一次事务就写一次 
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2
MariaDB [hellodb]> set global sync_binlog=0


MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.001 sec)
MariaDB [hellodb]> show variables like 'sync_binlog';
+---------------------+-------+
| Variable_name    | Value |
+---------------------+-------+
| sync_binlog     | 0   |
|---------------------+-------+
5 rows in set (0.001 sec)


2.#查看当前数据库二进制日志位置
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       713 |
| mysql-bin.000002 |       546 |
| mysql-bin.000003 |      1226 |
| mysql-bin.000004 |       891 |
| mysql-bin.000005 |     26638 |
| mysql-bin.000006 |    936840 |     #当前二进制位置为936840
+------------------+-----------+
6 rows in set (0.000 sec)
#因为同步是从当前位置至以后的数据库内容进行同步，所以当前位置以前的内容要进行备份，然后再导入从服务器



3.#完全备份数据库
[root@centos8 ~]# mysqldump -A --single-transaction --master-data=1 -F > /backup3/all.sql
[root@centos8 ~]# ll /backup3/
total 480
-rw-r--r-- 1 root root 487698 Oct 11 22:18 all.sql


4.#创建授权用户
MariaDB [hellodb]> grant replication slave on *.* to repluser@'10.0.0.%'  identified by 'magedu';
Query OK, 0 rows affected (0.058 sec)

5.#然后将主服务器的完全备份文件，传到从服务器上
[root@centos8 ~]# scp /backup3/all.sql  10.0.0.18:/data
root@10.0.0.18's password: 
all.sql                       100%  476KB  20.3MB/s   00:00

1.2 从节点配置 10.0.0.18

1.#安装数据库 （必须和主节点同一版本）
[root@localhost ~]# yum install  -y mariadb-server 

2.#修改配置文件
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server_id=18

3.#启动数据库
[root@localhost ~]# systemctl start mariadb 

4.#开始数据库同步
4.1#修改从主服务器传过来的数据库完全备份文件，修改以下数据
[root@localhost ~]# vim /data/all.sql  

....
CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',       #要从哪里复制
  MASTER_USER='repluser',    	#授权的账号
  MASTER_PASSWORD='magedu',  	#密码
  MASTER_PORT=3306,					
  MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=385;
  ###MariaDB [hellodb]> help change master to;  #change  mastger to 模板；
....
4.2.#然后进行数据库还原，同时设置好了复制主数据库的配置。
[root@localhost ~]# mysql < /data/all.sql    #数据库还原

4.3 #查看复制信息是否准备好
[root@localhost ~]# mysql  

MariaDB [(none)]> start slave status\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'status' at line 1
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: 
                   Master_Host: 10.0.0.8
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000008
           Read_Master_Log_Pos: 385					 #复制的位置
                Relay_Log_File: mariadb-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mysql-bin.000008
              Slave_IO_Running: No
             Slave_SQL_Running: No
               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: 385
               Relay_Log_Space: 256
               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: NULL    #复制的延迟，主节点和从节点之间
 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: 0
                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_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.001 sec)

4.4#从节点生成的数据信息
[root@localhost ~]# ll /var/lib/mysql/
total 122952
-rw-rw---- 1 mysql mysql    16384 Oct 11 10:34 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Oct 11 10:34 aria_log_control
drwx------ 2 mysql mysql      272 Oct 11 10:51 hellodb
-rw-rw---- 1 mysql mysql      972 Oct 11 10:34 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Oct 11 10:51 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Oct 11 10:51 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Oct 11 10:34 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 Oct 11 10:34 ibtmp1
-rw-rw---- 1 mysql mysql      256 Oct 11 10:51 mariadb-relay-bin.000001 #中继日志
-rw-rw---- 1 mysql mysql       27 Oct 11 10:51 mariadb-relay-bin.index
-rw-rw---- 1 mysql mysql      152 Oct 11 10:51 master.info #复制主节点时候，主节点的信息
-rw-rw---- 1 mysql mysql        0 Oct 11 10:34 multi-master.info
drwx------ 2 mysql mysql     4096 Oct 11 10:51 mysql
srwxrwxrwx 1 mysql mysql        0 Oct 11 10:34 mysql.sock
-rw-rw---- 1 mysql mysql       16 Oct 11 10:34 mysql_upgrade_info
drwx------ 2 mysql mysql       20 Oct 11 10:34 performance_schema
-rw-rw---- 1 mysql mysql       54 Oct 11 10:51 relay-log.info #二进制日志和主节点之间的对应关系
-rw-rw---- 1 mysql mysql    24576 Oct 11 10:34 tc.log

在这里插入图片描述

4.5#开启从服务器io线程和sql线程，同时主节点会自动启动dump线程。

MariaDB [hellodb]> start slave;
Query OK, 0 rows affected (0.002 sec)

从节点线程
在这里插入图片描述

5.#主从复制完成后进行测试

#主服务器创建数据库，从服务器自动同步
#主服务器：
MariaDB [hellodb]> create database db1;
Query OK, 1 row affected (0.001 sec)


#从服务器查看
MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)

#完成

2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）

#1.环境准备
主：10.0.0.8      从1  :  10.0.0.18     从2 :  10.0.0.28

#2.如果主服务器宕机，自动的从从节点中选出新主步骤
如果主服务器8挂了，怎么让从两个从服务器中，选出一个主服务器呢？

1.看谁的数据保存的多，  利用show  slave status\G   查看复制二进制的起始位置。如果相同相同，那么就随便选一个进行以下操作，如果不同，那就选择复制的数据多的一台为主服务器，并进行以下操作。

2.选出主服务器的备选18，然后修改配置文件，开启二进制日志记录，指定server-id

3.因为是把从服务器18转为主，所以说现在这台服务器还存留者从服务器时的信息，所以要清除这些信息，首先关闭两个线程（io_thread，sql_thread）--stop  slave,    然后清除从节点信息  reset   slave  all  。彻底清理从节点信息。利用show  slave   status\G  ;  查看不到信息。    这样这台服务器就成了一台独立的服务器。可以让其他从服务器28指定他为主服务器了 。

4.把另外一台从服务器28，一开始同步已经挂了的主服务器的信息删除清理，先关闭stop  slave； 然后reset  slave  all。

5.因为刚确定两台服务器复制的数据是相同的，所以不用管，如果不相同，则需要把18的数据库信息，备份同步给28服务器。因为现在18为主服务器，所以28要重新指定复制的账号以及位置 。账号在以前主节点上已经复制过来了，所以不要更改 ，查看开始复制位置（show   master  logs ;）然后记住这个位置。

 6.从28上重新建立复制，利用change master  to  更改

CHANGE MASTER TO
  MASTER_HOST='10.0.0.18',
  MASTER_USER='repluser',
  MASTER_PASSWORD='magedu',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=330;

在这里插入图片描述
然后start slave；启动俩个线程。

7.然后进行测试

在18  现在的主上创建数据库进行测试。

在这里插入图片描述
28上进行测试，创建成功。

3、通过 MHA 0.56 搭建一个数据库集群结构

1.准备环境

环境：四台主机
10.0.0.7		centos7	MHA管理端
10.0.0.8		centos8	Master数据库		#如果master宕机，会自动从下面两个slave选中为主节点
10.0.0.18		centos8 Slave1从数据库
10.0.0.28		centos8	Slave2从数据库

2.配置MHA管理端

管理端需要安装两个包：mha4mysql-manager 和 mha4mysql-node

说明: mha4mysql-manager-0.56-0.el6.noarch.rpm 不支持CentOS 8，只支持CentOS7 以下版本
说明: mha4mysql-manager-0.58-0.el7.centos.noarch.rpm ,支持MySQL 5.7 ,但和CentOS8版本
上的Mariadb -10.3.17不兼容

说明本实验用的包是：mha4mysql-manager-0.56-0.el6.noarch.rpm 和 mha4mysql-node-0.56-0.el6.noarch.rpm ，对应的数据库为CentOS8版本上的Mariadb -10.3.17

# https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads   下载路径
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm

管理端安装mha4mysql-manager-0.56-0.el6.noarch.rpm 和 mha4mysql-node-0.56-0.el6.noarch.rpm

[root@mha-manager ~]# yum install  -y mha*.rpm

3.所有mysql节点安装node

在所有MySQL服务器上安装mha4mysql-node包

[root@mha-master ~]# yum install  -y  mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mha-slave1 ~]# yum install  -y  mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mha-slave2 ~]# yum install  -y  mha4mysql-node-0.56-0.el6.noarch.rpm

4.在管理节点建立配置文件

[root@mha-manager ~]#mkdir /etc/mastermha/
[root@mha-manager ~]#vim /etc/mastermha/app1.cnf  #app1 监控一组主从，如果管理端要监控多组主从，则再需要建立app2，文件或app3文件等（文件名字随意）
[server default]	#监控一组主从的服务器信息
user=mhauser        #用于远程连接所监控的一组主从MySQL所有节点的用户,需要有管理员的权限，并且					在所监控的所有节点上都要创建此账号
password=magedu
manager_workdir=/data/mastermha/app1/ 		  #目录会自动生成,无需手动创建--管理目录
manager_log=/data/mastermha/app1/manager.log  #管理日志
remote_workdir=/data/mastermha/app1/
ssh_user=root   		 #用于实现远程ssh基于KEY的连接,访问二进制日志
repl_user=repluser  	 #用于主从复制的用户信息
repl_password=magedu
ping_interval=1   	 #健康性检查的时间间隔，定义多长时间去查看主服务器是否存活，实时监控主服务器状态
master_ip_failover_script=/usr/local/bin/master_ip_failover  #切换VIP的perl脚本
report_script=/usr/local/bin/sendmail.sh  #当执行报警脚本

check_repl_delay=0  #默认如果slave中从库落后主库relaylog超过100M，主库不会选择这个从库为新的master，因为这个从库进行恢复需要很长的时间.通过这个参数，mha触发主从切换的时候会忽略复制的延时，通过check_repl_delay=0这个参数，mha触发主从切换时会忽略复制的延时，对于设置candidate_master=1的从库非常有用，这样确保这个从库一定能成为最新的master

master_binlog_dir=/data/mysql/  #指定二进制日志存放的目录,mha4mysql-manager-0.58必须指定,之前版本不需要指定

[server1]				#主从服务器地址
hostname=10.0.0.8
candidate_master=1  
[server2]
hostname=10.0.0.18
candidate_master=1  #设置为优先候选master，即使不是集群中事件最新的slave,也会优先master，就是你想让谁当master这里就把此参数写在哪个服务器下。
[server3]
hostname=10.0.0.28


===================================================
#上面的模板
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=magedu
ping_interval=1
master_binlog_dir=/data/mysql/
[server1]   
hostname=10.0.0.8
candidate_master=1  
[server2]
hostname=10.0.0.18
candidate_master=1
[server3]
hostname=10.0.0.28

5.实现基于key验证

所有节点实现相互之间ssh key 基于key验证。

[root@mha-manager ~]# ssh-keygen  #生成公钥
[root@mha-manager ~]# ssh-copy-id 127.0.0.1  #把自己的公钥拷贝到本机，让自己成为自己的基于key验证的信息。
[root@mha-manager .ssh]# ls
authorized_keys  id_rsa  id_rsa.pub  known_hosts
#为了方便，操作简单，我把本台机器上的 .ssh 文件夹拷贝到所有主机，意思是所有主机共用一套公钥私钥对，这样就实现了所以节点之间的互相基于key验证
[root@mha-manager ~]# rsync -av .ssh   10.0.0.8:/root/
[root@mha-manager ~]# rsync -av .ssh   10.0.0.18:/root/
[root@mha-manager ~]# rsync -av .ssh   10.0.0.28:/root/

6.实现数据库主从复制

实现master

1.#修改配置文件

[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server_id=8
log_bin
skip_name_resolve=1    #禁止反向解析

2.#创建授权账号

[root@master ~]# mysql 
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     28198 |
| mariadb-bin.000002 |       344 |     #记录下此位置
+--------------------+-----------+
2 rows in set (0.001 sec)

#记住创建的账号和密码，要和/etc/mastermha/app1.cnf  文件中设置的内容一致
MariaDB [(none)]> grant replication slave on *.*  to repluser@'10.0.0.%' identified by 'magedu';     

#创建所有人权限的账户，要和/etc/mastermha/app1.cnf中user账号一致，因为此账号以后要去连接每一个从节点，修改上面的设置，所以要有管理员权限。
MariaDB [(none)]> grant all on *.*  to mhauser@'10.0.0.%' identified by 'magedu';
Query OK, 0 rows affected (0.001 sec)

实现slave

#slave1  从节点1 配置
[root@slave1 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server_id=18
log_bin				#开启二进制
read-only			#因为是从节点，开启只读，如果master宕机，此slave选为主，那么刚才主中创建的超级权限用户会把此选项设置为关闭，因为要给下面的从进行复制
relay_log_purge=0	#开启中继日志不清除，不会自动清理中继日志，默认用完中继日志就会清理掉 
skip_name_resolve=1 #禁止进行反向解析，不做ip地址到主机名的解析，如果不加这一项 的话，就会默认把ip地址解析成名称，而在主从复制的时候，创建 的授权账号是ip地址的形式，所以连接时可能出现问题。不能够识别 ，连接失败 

#进行同步复制
[root@slave1 ~]# mysql   
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.8',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='magedu',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000002',
    ->   MASTER_LOG_POS=344;
MariaDB [(none)]> start slave;



#slave2   从节点2配置同slave1
[root@slave2 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server_id=18
log_bin
read-only
relay_log_purge=0
skip_name_resolve=1
[root@slave2 ~]# systemctl start mariadb.service 

[root@slave2]# mysql   
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.8',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='magedu',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000002',
    ->   MASTER_LOG_POS=344;
MariaDB [(none)]> start slave;

7.检查MHA环境

#检查环境
[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
[root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf
#查看状态
[root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf

实例：

#检查ssh连接验证消息

[root@mha-manager ~]# masterha_check_ssh   --conf=/etc/mastermha/app1.cnf 
Thu Oct 15 17:13:41 2020 - [info] All SSH connection tests passed successfully.

#检查复制信息
[root@mha-manager ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
MySQL Replication Health is OK.

8.启动MHA

#开启MHA，默认是前台运行
[root@mha-manager ~]# masterha_manager --conf=/etc/mastermha/app1.cnf

#后台运行
[root@mha-manager ~]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null

#查看mha状态 
masterha_check_status --conf=/etc/mastermha/app1.cnf

9.拍错日志

tail -f /data/mastermha/app1/manager.log

10.模拟故障

#当 master down机后,mha自动退出
[root@mha-manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf
 Wed Jun 17 10:02:58 2020 - [warning] Global configuration file
/etc/masterha_default.cnf not found. Skipping.
 Wed Jun 17 10:02:58 2020 - [info] Reading application default configuration
from /etc/mastermha/app1.cnf..
 Wed Jun 17 10:02:58 2020 - [info] Reading server configuration from
/etc/mastermha/app1.cnf..
 Wed Jun 17 10:06:37 2020 - [warning] Global configuration file
/etc/masterha_default.cnf not found. Skipping.
 Wed Jun 17 10:06:37 2020 - [info] Reading application default configuration
from /etc/mastermha/app1.cnf..
 Wed Jun 17 10:06:37 2020 - [info] Reading server configuration from
/etc/mastermha/app1.cnf..

查看日志信息

[root@mha-manager ~]#cat /data/mastermha/app1/manager.log

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JeCWqt6D-1602849528730)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201015192110578.png)]$

在这里插入图片描述

根据MHA原理，主master宕机后，会自动提升其中一从节点为主。最终通过日志发现新的主节点为10.0.0.18

11.进行验证

#slave1 10.0.0.18上进行验证
MariaDB [(none)]> show slave status\G
Empty set (0.000 sec)

在这里插入图片描述

#slave1 10.0.0.18从节点上
#并且把从节点配置的只读模式，自动关闭 
MariaDB [(none)]> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+
1 row in set (0.000 sec)

#证明主节点切换成功

在slave2 10.0.0.28上验证

在这里插入图片描述
因为slave1 10.0.0.18 被提升为主服务器，所以在数据库中创建新数据库中进行验证。观察slave2 10.0.0.28的状态。

注意：
在这里插入图片描述

4、实战案例：Percona XtraDB Cluster(PXC 5.7）

一、实验环境

pxc1:  10.0.0.27   centos7
pxc2:  10.0.0.37   centos7
pxc3:  10.0.0.47   centos7
pxc4:  10.0.0.57   centos7

二、实验步骤

（1）安装 Percona XtraDB Cluster 5.7

#配置清华大学yum源
[15:40:11 root@pxc1 ~]#cat /etc/yum.repos.d/pxc.repo 
[perconal]
name=percona_repo
baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0
#将yum源拷贝到其他两个节点
[15:43:37 root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.37:/etc/yum.repos.d/
[15:43:37 root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.47:/etc/yum.repos.d/
#在三个节点都安装PXC 5.7
[15:43:37 root@pxc1 ~]#yum install Percona-XtraDB-Cluster-57 -y
[15:28:39 root@pxc2 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc3 ~]# yum install Percona-XtraDB-Cluster-57 -y

（2）在各个节点上分别配置mysql及集群配置文件

/etc/my.cnf为主配置文件，当前版本中，其余的配置文件都放在/etc/percona-xtradb-cluster.conf.d目录里，包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf 三个文件

#修改PXC的配置文件
[16:11:47 root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.27,10.0.0.37,10.0.0.47          #三个节点的IP

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# Slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
#wsrep_node_address=192.168.70.63
wsrep_node_address=10.0.0.27                                       #各个节点，指定自已的IP
# Cluster name
wsrep_cluster_name=pxc-magedu-cluster

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc-cluster-node-1                                  #各个节点，指定自已节点名称

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
wsrep_sst_auth="bxl:123456"                                        #取消注释或者改为自己的密码

[16:09:38 root@pxc2 ~]#cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.27,10.0.0.37,10.0.0.47

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# Slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
#wsrep_node_address=192.168.70.63
wsrep_node_address=10.0.0.37
# Cluster name
wsrep_cluster_name=pxc-magedu-cluster

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc-cluster-node-2

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
wsrep_sst_auth="bxl:123456"

[root@pxc3 ~]# cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.27,10.0.0.37,10.0.0.47

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# Slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
#wsrep_node_address=192.168.70.63
wsrep_node_address=10.0.0.47
# Cluster name
wsrep_cluster_name=pxc-magedu-cluster

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc-cluster-node-3

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
wsrep_sst_auth="bxl:123456"

（3）启动第一个节点

[root@pxc3 ~]# systemctl start mysql@bootstrap.service
#查看root密码
[root@pxc3 ~]# grep "temporary password" /var/log/mysqld.log
2020-10-17T08:22:46.574773Z 1 [Note] A temporary password is generated for root@localhost: +?tLc;9/f7Pf
[root@pxc3 ~]# mysql -p'+?tLc;9/f7Pf'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.31-34-57-log

Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
#修改root密码
mysql> alter user 'root'@'localhost' identified by 'magedu'
    -> ;
Query OK, 0 rows affected (0.00 sec)
#创建相关用户并授权
mysql> CREATE USER 'bxl'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bxl'@'localhost';
Query OK, 0 rows affected (0.00 sec)

（4）启动其他节点

[root@pxc1 ~]# systemctl start mysql
[16:15:27 root@pxc2 ~]#systemctl start mysql

（5）查看集群状态，验证集群是否成功

#在任意节点，查看集群状态，只要在第一个节点更改数据库root密码其他节点均可同步
[16:51:13 root@pxc2 ~]#mysql -uroot -pmagedu
mysql> show variables like 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name   | Value              |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-2 |
+-----------------+--------------------+
1 row in set (0.00 sec)
mysql> show variables like 'wsrep_node_address';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| wsrep_node_address | 10.0.0.37 |
+--------------------+-----------+
1 row in set (0.00 sec)
#在任意节点查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
#在任意节点创建数据库
#节点2创建数据库
mysql> create database testdb1;
Query OK, 1 row affected (0.00 sec)
#节点1查询
[root@pxc1 ~]# mysql -uroot -pmagedu
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.31-34-57-log Percona XtraDB Cluster (GPL), Release rel34, Revision 7359e4f, WSREP version 31.45, wsrep_31.45

Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
+--------------------+
5 rows in set (0.00 sec)
#同时在三个节点创建数据库只有一个成功，无需担心复制冲突

（6）pxc集群加入新节点

#在PXC集群中再加一台新的主机PXC4：10.0.0.57
[17:25:11 root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y
[17:30:45 root@pxc4 ~]#cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.27,10.0.0.37,10.0.0.47,10.0.0.57

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# Slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
#wsrep_node_address=192.168.70.63
wsrep_node_address=10.0.0.57
# Cluster name
wsrep_cluster_name=pxc-magedu-cluster

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc-cluster-node-4

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
wsrep_sst_auth="bxl:123456"
启动服务
[17:30:54 root@pxc4 ~]#systemctl start mysql
[17:32:08 root@pxc4 ~]#mysql -uroot -pmagedu
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.31-34-57-log Percona XtraDB Cluster (GPL), Release rel34, Revision 7359e4f, WSREP version 31.45, wsrep_31.45

Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 4     |
+--------------------+-------+
1 row in set (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
+--------------------+
5 rows in set (0.00 sec)

（7）在pxc集群中修复故障节点

#在任意节点停止服务
[17:43:45 root@pxc4 ~]#systemctl stop mysql.service 
#在其它任意节点查看wsrep_cluster_size变量少了一个节点
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.01 sec)
mysql> create database testdb2;
Query OK, 1 row affected (0.01 sec)
#在其它任意节点可看到数据已同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
| testdb2            |
+--------------------+
6 rows in set (0.00 sec)
#将停止的节点启动，发现数据已经同步
[17:48:45 root@pxc4 ~]#systemctl start mysql
[17:48:45 root@pxc4 ~]#mysql -uroot -pmagedu
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.31-34-57-log Percona XtraDB Cluster (GPL), Release rel34, Revision 7359e4f, WSREP version 31.45, wsrep_31.45

Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
| testdb2            |
+--------------------+
6 rows in set (0.00 sec)

mysql>

5、通过 ansible 部署二进制 mysql 8

[root@centos8 ~]#vim install_mysql8.yml 

---
- hosts: dbsrv                                                                                                                                                                                                                                    
  remote_user: root
  gather_facts: no

  tasks:
    - name: add group mysql
      group: name=mysql state=present
    - name: add user mysql
      user: name=mysql state=present group=mysql
    - name: Install packeges
      yum: name=libaio,numactl-libs,libaio,ncurses-compat-libs
    - name: decompression
      unarchive: src=/root/mysql-8.0.21-linux-glibc2.12-x86_64.tar.xz dest=/usr/local
    - name: create symbolic link
      file: src=/usr/local/mysql-8.0.21-linux-glibc2.12-x86_64 dest=/usr/local/mysql state=link
    - name: modify path
      shell: echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
    - name: modify path
      shell: source /etc/profile.d/mysql.sh
    - name: create my.cnf
      shell: echo -e '[mysqld]\nserver-id=1\nlog-bin\ndatadir=/data/mysql\nsocket=/data/mysql/mysql.sock\nlog-error=/data/mysql/mysql.log\npid-file=/data/mysql/mysql.pid\n[client]\nsocket=/data/mysql/mysql.sock'> /etc/my.cnf
    - name: install mysql
      shell: mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
    - name: creat mysqld
      shell: cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
    - name: set auto run
      shell: chkconfig --add mysqld
    - name: start mysql
      service: name=mysqld state=started
    - name: modify password
      shell: mysql -e "alter user root@localhost identified by '123456'"

果枝

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
2
评论
6.mysql数据库主从复制与高可用集群搭建、ansible自动部署mysql

1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）#准备环境：两台服务器：10.0.0.8 主节点--centos8 10.0.0.18 从节点--centos81.1.主节点配置 10.0.0.81.#修改配置文件[root@centos8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf[mysqld]server_id=8log_bin=/data/logbin/mysql-bin[r
复制链接

扫一扫