DBA（七）：PXC、MySQL存储引擎

最新推荐文章于 2023-01-09 18:10:31 发布

哦吼吼吼tian

最新推荐文章于 2023-01-09 18:10:31 发布

阅读量302

点赞数

本文链接：https://blog.csdn.net/tian1345/article/details/104704560

版权

PXC

PXC概述

PXC介绍

Percona XtrDB Cluster（简称PXC）

是基于Galera的mysql奥可用集群解决方案
Galera Cluster是Codership公司开发的一套免费开源的高可用方案
PXC集群主要由两部分组成：==Percona Server with XtraDB ==和 Write Set Replication patches（同步、多主复制插件）
官网地址

PXC特点

数据强一致性、无同步延迟
没有主从切换操作，无需使用虚拟IP
支持InnoDB存储引擎
多线程复制
部署使用简单
支持节点自动加入，无需手动拷贝数据

服务端口

端口	说明
3306	数据库服务端口
4444	SST端口
4567	集群通信端口
4568	IST端口
SST	State Snapshot TRansfer 全量同步
IST	Incremental State Transfer 增量同步

部署PXC

软件介绍

软件	作用
percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm	在线热备程序
qpress-1.1-14.11.x86_64.rpm	递归压缩程序
Percona-XtraDB-Cluster-server-57-5.7.25-31.35.1.el7.x86_64.rpm	集群服务程序

pxc软件包提取码：t2yg

环境准备

配置主机名与ip地址绑定
配置服务器192.168.4.71

[root@host71 ~]#  vim /etc/hosts
192.168.4.71     host71
192.168.4.72     host72
192.168.4.73     host73 
:wq
[root@host71 ~]#hostnamectl set-hostname  host71

配置服务器192.168.4.72

[root@host72~]#  vim /etc/hosts
192.168.4.71     host71
192.168.4.72     host72
192.168.4.73     host73 
:wq
[root@host72 ~]#hostnamectl set-hostname  host72

配置服务器192.168.4.73

[root@host73~]#  vim /etc/hosts
192.168.4.71     host71
192.168.4.72     host72
192.168.4.73     host73 
:wq
[root@host73 ~]#hostnamectl set-hostname  host73

在任意一台服务器上ping 对方的主机名，ping通为配置成功。

[root@host71 ~]# ping -c 2  host71  #成功
PING host71 (192.168.4.71) 56(84) bytes of data.
64 bytes from host71 (192.168.4.71): icmp_seq=1 ttl=255 time=0.011 ms
64 bytes from host71 (192.168.4.71): icmp_seq=2 ttl=255 time=0.020 ms
--- host71 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 999ms
rtt min/avg/max/mdev = 0.011/0.015/0.020/0.006 ms
-------------------------------------------------------------------
[root@host71 ~]# ping -c 2  host72 #成功
PING host72 (192.168.4.72) 56(84) bytes of data.
64 bytes from host72 (192.168.4.72): icmp_seq=1 ttl=255 time=0.113 ms
64 bytes from host72 (192.168.4.72): icmp_seq=2 ttl=255 time=0.170 ms
--- host72 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1000ms
rtt min/avg/max/mdev = 0.113/0.141/0.170/0.030 ms
------------------------------------------------------------------
[root@host71 ~]# ping -c 2  host73 #成功
PING host73 (192.168.4.73) 56(84) bytes of data.
64 bytes from host73 (192.168.4.73): icmp_seq=1 ttl=255 time=0.198 ms
64 bytes from host73 (192.168.4.73): icmp_seq=2 ttl=255 time=0.155 ms
--- host73 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1000ms
rtt min/avg/max/mdev = 0.155/0.176/0.198/0.025 ms

安装软件包

在192.168.4.71服务器安装软件包，软件包之间有依赖，注意一定要按照顺序安装

[root@host71 ~]# cd /opt
[root@host71 opt]# ls
pxc
[root@host71 opt]# cd pxc/
[root@host71 ~]# ls
libev-4.15-1.el6.rf.x86_64.rpm
percona-release-0.1-4.noarch.rpm
percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar
qpress-1.1-14.11.x86_64.rpm

我们可以写一个简单的脚本
[root@host71 pxc]# vim  pxc.sh 
#!/bin/bash
rpm  -ivh  libev-4.15-1.el6.rf.x86_64.rpm       #安装依赖
yum  -y  install  percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
rpm  -ivh  qpress-1.1-14.11.x86_64.rpm           #安装依赖
tar  -xvf  Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar
yum  -y  install  Percona-XtraDB-Cluster-*.rpm

执行脚本，记住一定要在pxc目录下进行！！！
[root@host71 pxc]# scp /opt/pxc.sh   root@192.168.4.72:/opt/pxc
[root@host71 pxc]# scp /opt/pxc.sh   root@192.168.4.73:/opt/pxc

在72与73主机执行脚本，安装软件
[root@host72 ~]# cd /opt/pxc
[root@host72 pxc]# ./pxc.sh
-----------
[root@host72 ~]# cd /opt/pxc
[root@host72 pxc]# ./pxc.sh

配置服务

1.修改数据库服务运行参数配置文件mysqld.cnf

71主机：
[root@host71 pxc]# ls /etc/percona-xtradb-cluster.conf.d/     #配置文件的所在目录
mysqld.cnf                 #数据库服务运行参数的配置文件
mysqld_safe.cnf            #Percona Server 5.7配置文件
wsrep.cnf                  #PXC集群配置文件

[root@host71 pxc]# vim  /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
  6 [mysqld]
  7 server-id=1                              #只需要修改server-id，每台主机不允许重复    
  8 datadir=/var/lib/mysql                   #数据库目录
  9 socket=/var/lib/mysql/mysql.sock         #socket文件
 10 log-error=/var/log/mysqld.log            #日志文件
 11 pid-file=/var/run/mysqld/mysqld.pid      #pid文件
 12 log-bin                                  #启用binlog日志
 13 log_slave_updates                        #启用链式复制
 14 expire_logs_days=7                       #日志文件保留天数

72主机：
[root@host72 pxc]# cd  /etc/percona-xtradb-cluster.conf.d/
[root@host72 percona-xtradb-cluster.conf.d]# vim  mysqld.cnf
[mysqld]
server-id=2                                 #server-id不允许重复
datadir=/var/lib/mysql                      #数据库目录
socket=/var/lib/mysql/mysql.sock            #socket文件
log-error=/var/log/mysqld.log               #日志文件
pid-file=/var/run/mysqld/mysqld.pid         #pid文件
log-bin                                     #启用binlog日志
log_slave_updates                           #启用链式复制
expire_logs_days=7                          #日志文件保留天数

73主机：
[root@host73 pxc]# cd  /etc/percona-xtradb-cluster.conf.d/
[root@host73 percona-xtradb-cluster.conf.d]# vim  mysqld.cnf
[mysqld]
server-id=3                                 #server-id不允许重复
datadir=/var/lib/mysql                      #数据库目录
socket=/var/lib/mysql/mysql.sock            #socket文件
log-error=/var/log/mysqld.log               #日志文件
pid-file=/var/run/mysqld/mysqld.pid         #pid文件
log-bin                                     #启用binlog日志
log_slave_updates                           #启用链式复制
expire_logs_days=7                          #日志文件保留天数

========================================================
2.修改Percona Server 5.7配置文件mysqld_safe.cnf
三台数据库服务器，使用默认配置即可

[root@host71 percona-xtradb-cluster.conf.d]# vim  mysqld_safe.cnf
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid      #pid文件位置及名称
socket   = /var/lib/mysql/mysql.sock       #socket文件位置及名称
nice     = 0

=========================================================
3.修改PXC集群配置文件wsrep.cnf

71主机：
[root@host71 pxc]# vim  /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
 8 wsrep_cluster_address=gcomm://192.168.4.71,192.168.4.72,192.168.4.73       #数据库服务器成员列表
 25 wsrep_node_address=192.168.4.71        #本机主机IP
 27 wsrep_cluster_name=pxc-cluster         #集群名称，3台主机必须相同
 30 wsrep_node_name=pxcnode71              #本机主机名，可以不用和本机主机名一样
 39 wsrep_sst_auth="sstuser:123qqq...A"    #全量同步时的用来连接自己用户名和密码

72主机：
[root@host72 ~]# cd /etc/percona-xtradb-cluster.conf.d/
[root@host72 percona-xtradb-cluster.conf.d]# vim  wsrep.cnf 
 8 wsrep_cluster_address=gcomm://192.168.4.71,192.168.4.72,192.168.4.73
 25 wsrep_node_address=192.168.4.72        #本机主机IP
 27 wsrep_cluster_name=pxc-cluster         #集群名称，3台主机必须相同
 30 wsrep_node_name=pxcnode71              #本机主机名，可以不用和本机主机名一样
 39 wsrep_sst_auth="sstuser:123qqq...A"    #全量同步时的用来连接自己用户名和密码

73主机：
[root@host73 ~]# cd /etc/percona-xtradb-cluster.conf.d/
[root@host73 percona-xtradb-cluster.conf.d]# vim  wsrep.cnf 
 8 wsrep_cluster_address=gcomm://192.168.4.71,192.168.4.72,192.168.4.73
 25 wsrep_node_address=192.168.4.73         #本机主机IP
 27 wsrep_cluster_name=pxc-cluster          #集群名称，3台主机必须相同
 30 wsrep_node_name=pxcnode71               #本机主机名，可以不用和本机主机名一样
 39 wsrep_sst_auth="sstuser:123qqq...A"     #全量同步时的用来连接自己用户名和密码

启动服务

1.初始化集群，启动集群服务

71主机：
[root@host71 ~]# systemctl   start  mysql@bootstrap.service     #启动集群服务，首次启动服务时间较长
[root@host71 ~]# ls  /var/lib/mysql
auto.cnf         grastate.dat       ibdata1          mysql.sock          server-key.pem
ca-key.pem       gvwstate.dat       ib_logfile0      mysql.sock.lock     sys
ca.pem           host71-bin.000001  ib_logfile1      performance_schema  xb_doublewrite
client-cert.pem  host71-bin.000002  ibtmp1           private_key.pem
client-key.pem   host71-bin.index   mysql            public_key.pem
galera.cache     ib_buffer_pool     mysqld_safe.pid  server-cert.pem

[root@host71 ~]# grep  "password"  /var/log/mysqld.log    #查看数据库管理员初始密码
2020-02-25T15:32:48.840428Z 1 [Note] A temporary password is generated for root@localhost: lpjtsWsq:45y
[root@host71 ~]# mysql -uroot  -p'lpjtsWsq:45y'                    #使用初始密码登录
mysql> alter user  root@"localhost"  identified  by "123456";    #修改登录密码
Query OK, 0 rows affected (0.00 sec)

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

mysql> grant  reload ,lock tables ,replication  client ,process  on *.*  to  sstuser@"localhost"  identified  by  "123qqq...A";        #添加授权用户，全量同步用户的权限


[root@host71 ~]# mysql -uroot  -p123456                    #使用修改后的密码登录 
mysql> select  user , host from  mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| sstuser       | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

[root@host71 ~]# ss  -nutlp  |  grep 3306      #数据库服务端口
tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=15113,fd=32))
[root@host71 ~]# ss  -nutlp  |  grep 4567      #集群通信端口
tcp    LISTEN     0      128       *:4567                  *:*                   users:(("mysqld",pid=15113,fd=11))
[root@host71 ~]# ss  -nutlp  |  grep 4444       #全量同步端口
[root@host71 ~]# ss  -nutlp  |  grep 4568      #增量同步端口
#全量同步和增量同步端口只有在进行操作时才可以看得到端口

=======================================================
2.启动数据库服务
启动72与73主机的数据库服务，会自动同步71主机的root初始密码以及授权用户sstuser

72主机：
一定要记得关闭防火墙，selinux设为宽松模式！！
[root@host72 ~]# ls  /var/lib/mysql
[root@host72 ~]# systemctl  start mysql    #此时初始化会自动全量同步wsrep.cnf文件，由于71主机已经配置完成，所以会自动全量同步71主机的数据库
[root@host72 ~]# ls  /var/lib/mysql
auto.cnf           ibdata1                 public_key.pem
ca-key.pem         ib_logfile0             server-cert.pem
ca.pem             ib_logfile1             server-key.pem
client-cert.pem    ibtmp1                  sys
client-key.pem     innobackup.move.log     xb_doublewrite
galera.cache       innobackup.prepare.log  xtrabackup_binlog_pos_innodb
grastate.dat       mysql                   xtrabackup_galera_info
gvwstate.dat       mysql.sock              xtrabackup_info
host72-bin.000001  mysql.sock.lock         xtrabackup_master_key_id
host72-bin.index   performance_schema
ib_buffer_pool     private_key.pem

[root@host72 ~]# ss  -nutlp |  grep 3306
tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=15831,fd=35))
[root@host72 ~]# ss  -nutlp |  grep 4567
tcp    LISTEN     0      128       *:4567                  *:*                   users:(("mysqld",pid=15831,fd=11))
[root@host72 ~]# mysql -uroot  -p123456
mysql> select user ,host  from  mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| sstuser       | localhost |
+---------------+-----------+
4 rows in set (0.01 sec)

73主机：
[root@host73 ~]# ls /var/lib/mysql
[root@host73 ~]# systemctl  start mysql
[root@host73 ~]# ls /var/lib/mysql
auto.cnf           ibdata1                 public_key.pem
ca-key.pem         ib_logfile0             server-cert.pem
ca.pem             ib_logfile1             server-key.pem
client-cert.pem    ibtmp1                  sys
client-key.pem     innobackup.move.log     xb_doublewrite
galera.cache       innobackup.prepare.log  xtrabackup_binlog_pos_innodb
grastate.dat       mysql                   xtrabackup_galera_info
gvwstate.dat       mysql.sock              xtrabackup_info
host73-bin.000001  mysql.sock.lock         xtrabackup_master_key_id
host73-bin.index   performance_schema
ib_buffer_pool     private_key.pem
[root@host73 ~]# ss  -nutlp |  grep 3306
tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=15796,fd=34))
[root@host73 ~]# ss  -nutlp |  grep 4567
tcp    LISTEN     0      128       *:4567                  *:*                   users:(("mysqld",pid=15796,fd=11))
[root@host73 ~]# mysql -uroot -p123456
mysql> select  user ,host from  mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| sstuser       | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

测试配置

1.查看集群信息
启动数据库服务，在任意一台主机查看数据都可以
[root@host71 ~]# mysql -uroot -p123456
wsrep_incoming_addresses  192.168.4.71:3306,192.168.4.72:3306,192.168.4.73:3306    #集群成员列表 
wsrep_cluster_size       3                #集群服务器台数
wsrep_cluster_status   Primary            #集群状态                           
wsrep_connected        ON                 #连接状态
wsrep_ready             ON                #服务状态

=======================================================
2.访问集群，存取数据
添加访问数据的连接用户 （在任意一台服务器上添加都可以，另外的2台服务器会自动同步授权用户）

72主机授权：
[root@host72 ~]# mysql  -uroot  -p123456  -e 'grant all  on  gamedb.*  to admin@"%"  identified by "123456"'     #添加授权用户

71主机查看：
[root@host71 ~]# mysql  -uroot  -p123456   -e 'select  user from mysql.user  where user="admin"'         
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+
| user  |
+-------+
| admin |
+-------+

73主机查看：
[root@host73 ~]#  mysql  -uroot  -p123456   -e 'select  user from mysql.user  where user="admin"'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+
| user  |
+-------+
| admin |
+-------+

---------------------------------------------------------------------------------------------
客户端连接集群存取数据（连接任意一台数据库服务器的IP地址都可以）

连接71主机的服务器
[root@host50 ~]# mysql -h192.168.4.71  -uadmin  -p123456  -e 'create  database  gamedb'               #建库
[root@host50 ~]# mysql -h192.168.4.71  -uadmin  -p123456  -e 'create  table  gamedb.t1(id  int)'         #建表
[root@host50 ~]# mysql -h192.168.4.71  -uadmin  -p123456  -e 'insert  into gamedb.t1  values(111)'    #插入数据
ERROR 1105 (HY000) at line 1: Percona-XtraDB-Cluster prohibits use of DML command on a table (gamedb.t1) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER
#此时会报错，要求PXC集群必须要有主键
[root@host50 ~]# mysql -h192.168.4.71  -uadmin  -p123456
MySQL [(none)]> alter table  gamedb.t1  add  x int  primary key auto_increment  first;     #添加一个主键字段
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [(none)]> desc gamedb.t1;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| x     | int(11) | NO   | PRI | NULL    | auto_increment |
| id    | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
MySQL [(none)]> insert  into  gamedb.t1(id) values(111);  #插入数据
Query OK, 1 row affected (0.02 sec)

MySQL [(none)]> insert  into  gamedb.t1(id) values(222);
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> insert  into  gamedb.t1(id) values(333);
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> insert  into  gamedb.t1(id) values(444);
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> insert  into  gamedb.t1(id) values(555);
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> select *  from gamedb.t1;
+----+------+
| x  | id   |
+----+------+
|  1 |  111 |
|  4 |  222 |
|  7 |  333 |
| 10 |  444 |
| 13 |  555 |
+----+------+
5 rows in set (0.01 sec)
#根据服务器的活着的台数进行自增的数量

在72和73主机数据库服务器查看数据：
[root@host50 ~]# mysql -h192.168.4.72  -uadmin  -p123456
MySQL [(none)]> select *  from gamedb.t1;
+----+------+
| x  | id   |
+----+------+
|  1 |  111 |
|  4 |  222 |
|  7 |  333 |
| 10 |  444 |
| 13 |  555 |
+----+------+
5 rows in set (0.00 sec)

连接73主机服务器：
[root@host50 ~]# mysql -h192.168.4.73  -uadmin  -p123456
MySQL [(none)]> select *  from gamedb.t1;
+----+------+
| x  | id   |
+----+------+
|  1 |  111 |
|  4 |  222 |
|  7 |  333 |
| 10 |  444 |
| 13 |  555 |
+----+------+
5 rows in set (0.00 sec)

测试高可用

1.停止数据库服务
停止3台数据库服务器的任意一台主机的数据库都不会影响数据的存取

停止71主机的数据库服务：
注：71主机开启服务命令是systemctl   start  mysql@bootstrap.service ，停止时也要这样的格式
[root@host71 ~]# systemctl  stop mysql@bootstrap.service
[root@host71 ~]# ss  -nutlp |  grep 3306
[root@host71 ~]# ss  -nutlp |  grep 4567

客户端50连接集群中的任意一台依旧可以存储数据
MySQL [(none)]> insert  into gamedb.t1  values(14 , 666);
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> insert  into gamedb.t1(id)  values(789);
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> insert  into gamedb.t1(id)  values(545);
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> select *  from gamedb.t1;
+----+------+
| x  | id   |
+----+------+
|  1 |  111 |
|  4 |  222 |
|  7 |  333 |
| 10 |  444 |
| 13 |  555 |
| 14 |  666 |
| 16 |  789 |
| 18 |  545 |
+----+------+
8 rows in set (0.00 sec)

将71主机的mysql服务启动
[root@host71 ~]# systemctl  start  mysql
[root@host71 ~]# ss  -nutlp |  grep 3306
tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=16750,fd=47))
[root@host71 ~]# ss  -nutlp |  grep 4567
tcp    LISTEN     0      128       *:4567                  *:*                   users:(("mysqld",pid=16750,fd=11))

客户端50依旧可以正常存储数据
MySQL [(none)]> insert  into gamedb.t1  values(20 , 444);
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> insert  into gamedb.t1(id)  values( 444);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert  into gamedb.t1(id)  values( 895);
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> insert  into gamedb.t1(id)  values( 577);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> select *  from gamedb.t1;
+----+------+
| x  | id   |
+----+------+
|  1 |  111 |
|  4 |  222 |
|  7 |  333 |
| 10 |  444 |
| 13 |  555 |
| 14 |  666 |
| 16 |  789 |
| 18 |  545 |
| 20 |  444 |
| 21 |  444 |
| 24 |  895 |
| 27 |  577 |
+----+------+
12 rows in set (0.01 sec)

查看PXC集群信息的命令（在任何一台数据库服务器上都可以查）

以72主机为例
[root@host72 ~]# mysql -uroot  -p123456
mysql> show status  like "%wsrep%";
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid           | 18ffd162-57e4-11ea-b3d3-0e404deeeef5 |
| wsrep_protocol_version           | 9                                    |
| wsrep_last_applied               | 14                                   |
| wsrep_last_committed             | 14                                   |
| wsrep_replicated                 | 4                                    |
| wsrep_replicated_bytes           | 1144                                 |
| wsrep_repl_keys                  | 10                                   |
| wsrep_repl_keys_bytes            | 176                                  |
| wsrep_repl_data_bytes            | 691                                  |
| wsrep_repl_other_bytes           | 0                                    |
| wsrep_received                   | 12                                   |
| wsrep_received_bytes             | 2677                                 |
| wsrep_local_commits              | 3                                    |
| wsrep_local_cert_failures        | 0                                    |
| wsrep_local_replays              | 0                                    |
| wsrep_local_send_queue           | 0                                    |
| wsrep_local_send_queue_max       | 1                                    |
| wsrep_local_send_queue_min       | 0                                    |
| wsrep_local_send_queue_avg       | 0.000000                             |
| wsrep_local_recv_queue           | 0                                    |
| wsrep_local_recv_queue_max       | 1                                    |
| wsrep_local_recv_queue_min       | 0                                    |
| wsrep_local_recv_queue_avg       | 0.000000                             |
| wsrep_local_cached_downto        | 3                                    |
| wsrep_flow_control_paused_ns     | 0                                    |
| wsrep_flow_control_paused        | 0.000000                             |
| wsrep_flow_control_sent          | 0                                    |
| wsrep_flow_control_recv          | 0                                    |
| wsrep_flow_control_interval      | [ 141, 141 ]                         |
| wsrep_flow_control_interval_low  | 141                                  |
| wsrep_flow_control_interval_high | 141                                  |
| wsrep_flow_control_status        | OFF                                  |
| wsrep_cert_deps_distance         | 2.083333                             |
| wsrep_apply_oooe                 | 0.000000                             |
| wsrep_apply_oool                 | 0.000000                             |
| wsrep_apply_window               | 1.000000                             |
| wsrep_commit_oooe                | 0.000000                             |
| wsrep_commit_oool                | 0.000000                             |
| wsrep_commit_window              | 1.000000                             |
| wsrep_local_state                | 4                                    |
| wsrep_local_state_comment        | Synced                               |
| wsrep_cert_index_size            | 5                                    |
| wsrep_cert_bucket_count          | 22                                   |
| wsrep_gcache_pool_size           | 5928                                 |
| wsrep_causal_reads               | 0                                    |
| wsrep_cert_interval              | 0.000000                             |
| wsrep_open_transactions          | 0                                    |
| wsrep_open_connections           | 0                                    |
| wsrep_ist_receive_status         |                                      |
| wsrep_ist_receive_seqno_start    | 0                                    |
| wsrep_ist_receive_seqno_current  | 0                                    |
| wsrep_ist_receive_seqno_end      | 0                                    |
| wsrep_incoming_addresses         | 192.168.4.73:3306,192.168.4.72:3306  |
| wsrep_cluster_weight             | 2                                    |
| wsrep_desync_count               | 0                                    |
| wsrep_evs_delayed                |                                      |
| wsrep_evs_evict_list             |                                      |
| wsrep_evs_repl_latency           | 0/0/0/0/0                            |
| wsrep_evs_state                  | OPERATIONAL                          |
| wsrep_gcomm_uuid                 | 2560479c-57e7-11ea-87db-13b99098305f |
| wsrep_cluster_conf_id            | 4                                    |
| wsrep_cluster_size               | 2                                    |
| wsrep_cluster_state_uuid         | 18ffd162-57e4-11ea-b3d3-0e404deeeef5 |
| wsrep_cluster_status             | Primary                              |
| wsrep_connected                  | ON                                   |
| wsrep_local_bf_aborts            | 0                                    |
| wsrep_local_index                | 1                                    |
| wsrep_provider_name              | Galera                               |
| wsrep_provider_vendor            | Codership Oy <info@codership.com>    |
| wsrep_provider_version           | 3.35(rddf9876)                       |
| wsrep_ready                      | ON                                   |
+----------------------------------+--------------------------------------+
71 rows in set (0.02 sec)

mysql> select  @@hostname;
+------------+
| @@hostname |
+------------+
| host72     |
+------------+
1 row in set (0.00 sec)

MySQL存储引擎

MySQL存储引擎概述

作为可插拔式的组件提供

MySQL服务软件自带的功能程序，处理表的处理器
不同的存储引擎有不同的功能和数据存储方式
MySQL服务内置程序，每种程序的功能都不一样，希望向表中存储数据时，使用mysql某种功能
MySQL 5.0/5.1 ----> MyISAM
MYSQL 5.5/56 ----> InnoDB

MySQL体系结构

在这里插入图片描述

配置存储引擎

修改表存储引擎

1.查看存储引擎
查看数据库服务默认使用的存储引擎
mysql> show  variables  like "%version%";     #查看存储引擎的版本
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.17                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.17-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)

mysql> show  engines;               #列出可用的存储引擎类型
+--------------------+---------+---------------------------------------------------------
| Engine             | Support | Comment                                                 
+--------------------+---------+---------------------------------------------------------
| CSV                | YES     | CSV storage engine                                      
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                      
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disap
| MyISAM             | YES     | MyISAM storage engine                                   
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking
| ARCHIVE            | YES     | Archive storage engine                                  
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                   
| FEDERATED          | NO      | Federated MySQL storage engine                          
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary table
+--------------------+---------+---------------------------------------------------------
9 rows in set (0.00 sec)
默认使用InnoDB存储引擎，只有InnoDB支持外键

===================================================================
		
2.查看已有表使用的存储引擎

mysql> show create table gamedb.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `x` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) DEFAULT NULL,
  PRIMARY KEY (`x`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
#gamedb.t1表的存储引擎为innodb

===================================================================

3.修改表的存储引擎

创建表时指定存储引擎，未指定时，使用默认存储引擎
mysql> create  table  gamedb.t3  (age int)engine=memory;
Query OK, 0 rows affected (0.03 sec)
mysql> show  create  gamedb.t3  \G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'gamedb.t3' at line 1
mysql> show  create table gamedb.t3  \G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `age` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-------------------------------------------------------------------------------------------------------
修改已有表的存储引擎
mysql> create table gamedb.t2(name  char(10));
Query OK, 0 rows affected (0.03 sec)

mysql> show  create table gamedb.t2  \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

[root@host51 mysql]# cd /var/lib/mysql/gamedb/
[root@host51 gamedb]# ls
db.opt  t1.frm  t1.ibd  t2.frm  t2.ibd
mysql> alter table  gamedb.t2  engine=myisam;    #修改t2表的存储引擎为myisam
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table gamedb.t2 \G            #查看表的存储引擎
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `name` char(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1    
1 row in set (0.00 sec)

[root@host51 gamedb]# ls               #查看数据库目录中gamedb库下的t2表的存储引擎为MYI
db.opt  t1.frm  t1.ibd  t2.frm  t2.MYD  t2.MYI  

(ps：
mysql> alter table  gamedb.t2  engine=myisam;  （如果在PXC的环境下，就需要在主配置文件mysqld.cnf中设置pxc_strict_mode = ENFORCING 或者 MASTER，但PXC集群里的表必须是innodb存储引擎，只有innodb支持事务，否则会锁表，无法进行同步）
ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits changing storage engine of a table (gamedb.t2) from transactional to non-transactional with pxc_strict_mode = ENFORCING or MASTER
)

修改服务的存储引擎

设置数据库服务使用的存储引擎
修改/etc/my.cnf配置文件
[root@host51 ~]# vim  /etc/my.cnf
[mysqld]
default-storage-engine=myisam    #将数据库的默认引擎修改为myisam
[root@host51 gamedb]# ls
db.opt  t1.frm  t1.ibd    t3.frm
[root@host51 gamedb]# systemctl  restart  mysqld

存储引擎的特点

Myisam存储引擎

主要特点

支持表级锁
不支持事务、事务回滚、外键

表文件

- 表名.frm       #表结构
- 表名.MYI       #索引
- 表名.MYD       #数据

innodb存储引擎

主要特点

支持行级锁定
支持事务、事务回滚、外键

表文件

- 表名.frm
- 表名.ibd

事务日志文件

- ibdata1
- ib_logfile0
- ib_logfile1

事务与事务回滚

事务：
从与数据库建立连接开始，执行SQL命令（select update insert delete等）操作数据，断开连接的过程被称为事务
事务回滚：
在对innodb存储引擎的表操作的过程中，任何一部分没有操作成功，会恢复之前所有的操作

MySQL锁机制

加锁
是解决对表的并发访问的冲突的问题
锁粒度（锁的范围）

表级锁：简称表锁，对整张表加锁（myisam）
行级锁：简称行锁，仅对被访问的行分别加锁（innodb）

锁类型

读锁（select）：又称共享锁，支持并发读，当第一个客户端进行查数据时，其他的客户端也可以同时进行查操作
写锁（insert ，update ，delete）：又称互斥锁，排它锁，是独占锁，上锁期间其他线程不能毒镖或者写表，在第一个客户端进行写入数据时，不论其他的客户端进行读操作还是写操作都会被拒绝，只有当第一个客户端释放写锁才可以进行操作

查看当前锁状态

mysql > show status like "table_locl%";

事务特性（ACID）

Atomic：原子性

事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败

Consistency：一致性

事务操作的前后，表中的记录没有任何的变化

Isolation：隔离性

事务操作是相互隔离不受影响的

Durability：持久性

数据一单提交，不可改变，永久改变表数据

工作建表时，如何决定表使用哪种存储引擎？

查询访问多的表，适合使用myisam存储引擎，可以节省系统CPU硬件资源
写操作多的表，适合使用innodb存储引擎，可以加大并发访问量

我们可以开三个终端做实验，三个终端都远程同一台主机，以root身份登录

首先，在第一个终端在已有库内创建innodb存储引擎的表
mysql> create   table  db5.t1(id int)engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> desc db5.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> show create  db5.t1  \G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'db5.t1' at line 1
mysql> show create table  db5.t1  \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

[root@host51 ~]# mysql  -uroot  -p123456
mysql> show variables  like "autocommit";   #此时查看自动提交功能，默认是打开的
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)
mysql> set  autocommit=off;    #取消自动提交，只对当前终端有效，其他连接依旧是自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> show variables  like "autocommit";   #查看提交状态
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

在第一个终端的表内写入数据
mysql> insert into db5.t1  values("222");
Query OK, 1 row affected (0.00 sec)

mysql> select *  from db5.t1;
+------+
| id   |
+------+
|  222 |
+------+
1 row in set (0.00 sec)

在第二个和第三个终端分别查看，发现该表内没有任何数据
mysql> select *  from db5.t1;
Empty set (0.00 sec)

mysql> select  *  from  db5.t1;
Empty set (0.00 sec)

此时，在第一个终端手动提交
mysql> commit;                 #提交数据
Query OK, 0 rows affected (0.00 sec)
现在其他两个终端都能看到第一个终端写入的数据
mysql> select *  from db5.t1;
+------+
| id   |
+------+
|  222 |
+------+
1 row in set (0.00 sec)


测试回滚，只要在提交之前回滚数据，数据都不会存入到表中 
mysql> insert into db5.t1  values("456");
Query OK, 1 row affected (0.00 sec)

mysql> select *  from db5.t1;
+------+
| id   |
+------+
|  222 |
|  456 |
+------+
2 rows in set (0.00 sec)

mysql> rollback;                    #数据回滚
Query OK, 0 rows affected (0.00 sec)

mysql> select *  from db5.t1;
+------+
| id   |
+------+
|  222 |
+------+
1 row in set (0.00 sec)


测试数据的一致性
在第一个终端直接写入数据提交
mysql> insert into db5.t1  values("456");
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select *  from db5.t1;
+------+
| id   |
+------+
|  222 |
|  456 |
+------+
2 rows in set (0.00 sec)
此时，把第二个终端也设置为取消自动提交
mysql> set  autocommit=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)
在第二个终端删除数据
mysql> delete  from  db5.t1  where id=222;
Query OK, 1 row affected (0.00 sec)

mysql> select *  from db5.t1;
+------+
| id   |
+------+
|  456 |
+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
再次去第一个和第三个终端查看
此时，第一个终端能看到两次的数据，因为第一个终端设置了取消自动提交
mysql> select *  from db5.t1;
+------+
| id   |
+------+
|  222 |
|  456 |
+------+
2 rows in set (0.00 sec)
而第三个终端看到的是最终的结果
mysql> select  *  from  db5.t1;
+------+
| id   |
+------+
|  456 |
+------+
1 row in set (0.00 sec)

哦吼吼吼tian

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DBA（七）：PXC、MySQL存储引擎

PXCPXC概述PXC介绍Percona XtrDB Cluster（简称PXC）是基于Galera的mysql奥可用集群解决方案Galera Cluster是Codership公司开发的一套免费开源的高可用方案PXC集群主要由两部分组成：==Percona Server with XtraDB ==和 Write Set Replication patches（同步、多...
复制链接

扫一扫