PXC 、 MySQL存储引擎

1 PXC集群 71 72 73
1pxc软件介绍
2pxc特点
3拓扑结构
4构建pxc集群
4.1 在所有服务器安装pxc软件（71/72/73）
]#cd pxc
]# 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
]# ls /etc/percona-xtradb-cluster.conf.d/
mysqld.cnf mysqld_safe.cnf wsrep.cnf

		4.2 在3台数据库服务器分别修改配置文件

]# cd /etc/percona-xtradb-cluster.conf.d/
71】# vim mysqld.cnf
server-id=71
:wq

72】#vim mysqld.cnf
server-id=72
:wq

73】# vim mysqld.cnf
server-id=73
:wq

71】# 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.71
27 wsrep_cluster_name=pxc-cluster
30 wsrep_node_name=pxc-cluster-node-71
39 wsrep_sst_auth=“sstuser:123qqq…A”
:wq

72】#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
27 wsrep_cluster_name=pxc-cluster
30 wsrep_node_name=pxc-cluster-node-72
39 wsrep_sst_auth=“sstuser:123qqq…A”
:wq

73】# 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
27 wsrep_cluster_name=pxc-cluster
30 wsrep_node_name=pxc-cluster-node-73
39 wsrep_sst_auth=“sstuser:123qqq…A”
:wq

4.3 在任意1台数据库服务器初始化集群
注意：关闭3台数据库服务器的 selinux 和 firewalld
[root@pxcnode71 ~]#systemctl start mysql@bootstrap.service
[root@pxcnode71 ~]# netstat -utnlp | grep 3306
tcp6 0 0 :::3306 ::😗 LISTEN 8502/mysqld
[root@pxcnode71 ~]# netstat -utnlp | grep 4567
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 8502/mysqld
[root@pxcnode71 ~]# ls /var/log/mysqld.log
/var/log/mysqld.log
[root@pxcnode71 ~]# grep password /var/log/mysqld.log
2020-08-26T14:22:55.211844Z 1 [Note] A temporary password is generated for root@localhost: qkJwa%qAb0N=
[root@pxcnode71 ~]# mysql -uroot -p’qkJwa%qAb0N=’
mysql> alter user root@“localhost” identified by “123456”;
Query OK, 0 rows affected (0.08 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”;
mysql> exit
Bye
[root@pxcnode71 ~]# mysql -uroot -p123456
mysql> show databases;
mysql> exit
[root@pxcnode71 ~]# mysql -usstuser -p123qqq…A #测试授权用户sstuser

		4.4 启动另外2台主机的数据库服务

[root@pxcnode72 ~]#systemctl start mysql

		4.5 查看数据库服务器状态

[root@pxcnode72 ~]# yum -y install net-tools
[root@pxcnode72 ~]#netstat -utnlp | grep 3306
[root@pxcnode72 ~]#netstat -utnlp | grep 4567
[root@pxcnode72 ~]#mysql -uroot -p123456
mysql> select user from mysql.user where user=“sstuser”;

[root@pxcnode73 ~]# yum -y install net-tools
[root@pxcnode73 ~]#netstat -utnlp | grep 3306
[root@pxcnode73~]#netstat -utnlp | grep 4567
[root@pxcnode73 ~]#mysql -uroot -p123456
mysql> select user from mysql.user where user=“sstuser”;

		4.6 查看主机的集群状态

show status like “%wsrep%”;

排错方法：
查看日志报错信息 grep -i “error” /var/log/mysqld.log

启动失败的原因：
检查修改的配置文件 mysqld.conf 和 wsrep.conf
没有关闭所有数据库服务器的 selinux 和 firewalld

重新初始化集群
]#rm -rf /var/lib/mysql/*
]#systemctl start mysql@bootstrap.service

启动mysql服务
]#rm -rf /var/lib/mysql/*
]#systemctl start mysql

71 初始化失败 解决 办法
71]#
rm -rf /var/lib/mysql/*
rm -rf /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf
systemctl start mysql@bootstrap.service

或者

先把mysql的进程杀了，然后把/var/lib/mysql下面又清了一遍，删了一下mysqld_safe.cnf 再执行 初始化

		4.7 客户端连接集群中的任意主机存取数据
			4.7.1 在任意1台数据库服务器添加用户给客户端连接使用，另外2台会自动同步添加的用户到本机

[root@pxcnode73 ~]# mysql -uroot -p123456 -e ‘grant all on bbsdb.* to yaya@"%" identified by “123456”’
[root@pxcnode71 ~]# mysql -uroot -p123456 -e ‘show grants for yaya@"%"’
mysql: [Warning] Using a password on the command line interface can be insecure.
±------------------------------------------------+
| Grants for yaya@% |
±------------------------------------------------+
| GRANT USAGE ON . TO ‘yaya’@’%’ |
| GRANT ALL PRIVILEGES ON bbsdb.* TO ‘yaya’@’%’ |
±------------------------------------------------+
[root@pxcnode71 ~]#
[root@pxcnode72 ~]# mysql -uroot -p123456 -e ‘show grants for yaya@"%"’
mysql: [Warning] Using a password on the command line interface can be insecure.
±------------------------------------------------+
| Grants for yaya@% |
±------------------------------------------------+
| GRANT USAGE ON . TO ‘yaya’@’%’ |
| GRANT ALL PRIVILEGES ON bbsdb.* TO ‘yaya’@’%’ |
±------------------------------------------------+
[root@pxcnode71 ~]#

[root@host50 ~]# mysql -h192.168.4.71 -uyaya -p123456 -e ‘create database bbsdb’
[root@host50 ~]# mysql -h192.168.4.71 -uyaya -p123456 -e ‘create table bbsdb.b(id int primary key auto_increment , name char(10))’

[root@host50 ~]# mysql -h192.168.4.72 -uyaya -p123456 -e ‘insert into bbsdb.b(name)values(“bob”)’
[root@host50 ~]# mysql -h192.168.4.73 -uyaya -p123456 -e ‘insert into bbsdb.b(name)values(“tom”)’

[root@host50 ~]#
[root@host50 ~]# mysql -h192.168.4.71 -uyaya -p123456 -e ‘select * from bbsdb.b’

		4.8 测试集群的高可用

停止集群中任意1台主机的数据库服务，都不会影响到另外2台主机
[root@pxcnode71 ~]# systemctl stop mysql@bootstrap.service
[root@pxcnode72 ~]# systemctl stop mysql
[root@host50 ~]# mysql -h192.168.4.73 -uyaya -p123456 -e ‘select * from bbsdb.b’
[root@host50 ~]# mysql -h192.168.4.73 -uyaya -p123456 -e ‘insert into bbsdb.b(name)values(“aaaa”)’

		4.9 测试服务器自动恢复 

[root@pxcnode71 ~]# systemctl start mysql
[root@pxcnode72 ~]# systemctl start mysql
[root@host50 ~]# mysql -h192.168.4.71 -uyaya -p123456 -e ‘select * from bbsdb.b’
[root@host50 ~]# mysql -h192.168.4.72 -uyaya -p123456 -e ‘select * from bbsdb.b’

2 MySQL存储引擎（难点）理解
2.1 什么是存储引擎（存储引擎介绍） 功能组件之一，访问表是希望使用MySQL的什么功能对表做处理，就调用对应的存储引擎就可以了。

innodb
2 2 MySQL服务的体系结构

2 2 MySQL服务的体系结构（由8部分组成）
组件的名称和功能介绍
1 连接池 检查硬件资源 和 用户访问权限

	2 管理工具  安装mysql服务软件后自带的安装命令  mysql   mysqladmin    mysqldump   mysqlbinlog  

	3  sql接口  把执行的SQL命令 发送给mysql服务的进程处理
	4  分析器    分析执行的SQL命令的语法格式和访问的数据是否有权限
	5  优化器    对执行的SQL命令优化处理

	6  缓存空间  查询缓存，存储曾经查找过的数据，存储空间从物理划分的

默认 MySQL服务查询缓存没有开启。

	7 存储引擎，访问表中的数据时，会根据表使用的存储引擎，对数据做不同的处理，每种存储引擎都有各自的功能和数据存储方式。建表时可以根据需要指定表使用的存储引擎。（存储引擎，就是MySQL服务提供的各自功能）

	8 文件系统  数据库服务器的硬盘（文件系统类型 ext2/3/4 、   xfs ）



2.3 MySQL服务处理查询访问的过程
    1  客户端 ------> 数据库服务器
	2  执行select 命令
	3  MySQL服务 先访问 查询缓存 如果有数据 直接在查询缓存提前数据回复客户端，反之， 到硬盘里查找数据，把找到的数据 先 保存到查询缓存 再回复给客户端.


2.4 存储引擎管理
	查看数据库服务默认使用的存储引擎

mysql> show engines;

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

mysql> show create table 库.表 \G
mysql> show create table db1.t10 \G

	修改数据库服务默认使用的存储引擎

]# vim /etc/my.cnf
[mysqld]
default-storage-engine=“myisam”
:wq
[root@host50 ~]# systemctl restart mysqld
[root@host50 ~]# mysql -uroot -p123qqq…A -e ’ create table db1.a2(id int)’

[root@host50 ~]# mysql -uroot -p123qqq…A -e ‘show create table db1.a2 \G’
*************************** 1. row ***************************
Table: a2
Create Table: CREATE TABLE a2 (
id int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
[root@host50 ~]#

    修改已有表使用的存储引擎
	alter  table  table  库.表  engine=新存储引擎名;	

	建表时，设置表使用的存储引擎
	create  table  库.表（字段列表）engine=存储引擎名;
	mysql> create table db1.c1 (id  int )engine=innodb;


执行查询select数据和存储insert数据的命令时都会对表做访问，根据表使用的存储引擎做不同的处理，每种存储引擎有各自的功能和数据存储方式。

2.5 常用存储引擎特点介绍 (理论)
2.5.1 myisam特点
支持表级锁
不支持外键 事务 事务回滚
每张对应3个表文件
.frm .MYI .MYD
表结构 index 数据
desc 库.表 createt index select * from 库.表

	2.5.2 innodb特点
		支持外键  支持事务 支持事务回滚  支持行级锁
		每张对应2个表文件
		    .frm	      .ibd
		    表结构            index+数据
		   desc 库.表
       专业术语？
      锁        : 加锁的目的 是为了解决 数据并发访问的冲突问题。
       锁粒度：表级锁  行级锁
	表级锁：只要是对表做select 或insert 访问 会把整张表锁上，锁了之后不允许其他的任何访问，只有锁被释放后才会允许其他的访问

	行级锁：对表做select 或insert 访问，仅仅对被访问的行分别加锁，没有被访问的行，不会加所有，允许其他的连接访问。
    锁类型：读锁  和 写锁
	读锁：对表执行select 访问 时加读锁，又叫共享锁，如果在加读锁期间有其他客户端也对表执行select 访问，是允许的，但insert 不允许。

	写锁： 对表做insert 访问时加写锁。又叫排它锁 ，如果在加写锁期间不允许任何连接对表做select 或insert。

pc1 update db1.user set name=“xxx” where id=1;
pc2 update db1.user set name=“yyy” where id=1;

	事务？ 连接数据库服务成功     各种执行各种操作命令   断开连接
				     库.表(innodb)						0  1 2 3 4
	使用事务日志文件记录对innodb存储引擎的表执行的sql操作。
	cd  /var/lib/mysql/
	ib_logfile0   ib_logfile1   ibdata1
	
	事务回滚: 在执行的各种操作命令时，任意一步操作失败，把数据恢复到失败之前的状态。

	事务的特点(ACID)
		原子性   一致性   隔离性    持久性
	事务特点演示？ 

连接1
create table db3.user2(id int)engine=innodb;
set autocommit=off;
insert into db3.user2 values(111);
select * from db3.user2; 自己能看到

连接2
select * from db3.user2; 看不到

连接3
select * from db3.user2;看不到

连接1 commit; 提交

连接2
select * from db3.user2; 看到

连接3
select * from db3.user2;看到

连接1
delete from db3.user2;

连接2
select * from db3.user2; 看到

连接3
select * from db3.user2;看到

连接1
insert into db3.user2 values(8888);

连接2
select * from db3.user2; 看不到

连接3
select * from db3.user2;看不到

连接1 select * from db3.user2; 看到
rollback；
select * from db3.user2; 看到 插入和删除 之前的数据

连接1 delete from db3.user2;
commit;
连接2
select * from db3.user2; 看不到

连接3
select * from db3.user2;看不到

2.6 建表时，如何决定表使用哪种存储引擎？
接受查询select访问多的表，适合使用myisam存储引擎，节省系统的硬件资源
接受写(insert update delete)访问多的表，适合使用innodb存储引擎,并发访问量大。