1. PXC介绍与群集搭建; 2.数据存储引擎.
一, PXC介绍
1.介绍
PXC(Percona XtraDB Cluster)基于Galara的一台开源软件,应用于解决mysql的高可用集群问题,由Codership公司开发,PXC群集主要由于两部分组成:Percona Server with XtraDB和Write Set Replication patches(同步,多主复制插件).
2.特点:数据高度一致性,没有同步延迟问题;没有主从切换操作,且无需使用虚拟IP;支持InnoDB存储引擎;多线程复制;部署使用简单;支持节点自动加入,无需手动拷贝数据.
3.服务端口:
- 3306 数据库服务端口
- 4444 SST端口
- 4567 集群成员通信端口
- 4568 IST端口
- SST state snapshot transfer 全量同步
- IST incremental state transfer 增量同步
二,搭建PXC集群
1.部署环境
#vim /etc/hosts //使用本机解析主机名
192.168.4.71 pxcnode71
192.168.4.72 pxcnode72
192.168.4.73 pxcnode73
[student@room9pc01 package]$ for i in 71 72 73 //传输软件包到对应虚拟主机
> do
> scp /home/student/bin/package/PXC.tar.gz root@192.168.4.$i:/root/
> done
PXC.tar.gz 100% 196MB 98.0MB/s 00:02
PXC.tar.gz 100% 196MB 147.3MB/s 00:01
PXC.tar.gz 100% 196MB 173.9MB/s 00:01
2.部署PXC
2.1 安装软件
#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-release-0.1-4.noarch.rpm
2.2 查看配置文件
# ls /etc/percona-xtradb-cluster.conf.d //配置文件的目录
mysqld.cnf mysqld_safe.cnf wsrep.cnf
- 说明: mysqld.cnf ---数据库服务运行参数配置文件; mysqld_safe.cnf ---Percona Server5.7配置文件; wsrep.cnf ---PXC集群配置文件
2.3 修改数据库服务运行参数配置文件
71]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=71 //改动id为71
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
72]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=72 //改动id为72
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
73]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=73 //改动id为73
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
2.4 查看Percona Server5.7配置文件
71]# cat /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf
72]# cat /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf
73]# cat /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf
2.5 需改PXC集群配置文件
71]vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.4.71,192.168.4.72,192.168.4.73 //集群成员列表
wsrep_node_address=192.168.4.71 //本机ip地址
wsrep_cluster_name=pxc-cluster //集群名称,3台机器必须一致
wsrep_node_name=pxcnode71 //本机的hostname
wsrep_sst_auth="sstuser:123456" //sst数据同步授权用户
72]vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.4.71,192.168.4.72,192.168.4.73
wsrep_node_address=192.168.4.72
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxcnode72
wsrep_sst_auth="sstuser:123456"
73]vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.4.71,192.168.4.72,192.168.4.73
wsrep_node_address=192.168.4.73
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxcnode73
wsrep_sst_auth="sstuser:123456"
2.6 启动服务,添加授权用户
注意,只在一台机器上启动服务即可,当前以在192.168.4.71上启动服务为例
71]# systemctl start mysql@bootstrap.service //启动PXC服务
[root@pxcnode71 PXC]# grep 'pass' /var/log/mysqld.log
2019-06-21T03:48:41.965682Z 1 [Note] A temporary password is generated for root@localhost: aBZIAuvq<1Ti //查看mysql的初始密码
# mysql -uroot -p'aBZIAuvq<1Ti' //使用初始密码登录
mysql> alter user root@"localhost" identified by '123456'; //更改密码
mysql> grant reload,lock tables,replication client,process on *.* to sstuser@"localhost" identified by '123456'; //授权用户sstuser
2.7 在4.72和4.73的主机上启动mysql服务
72]# systemctl start mysql
73]# systemctl start mysql
2.8 查看集群信息
71]mysql -uroot -p123456 -e 'show status like "%wsrep%"'
72]mysql -uroot -p123456 -e 'show status like "%wsrep%"'
73]mysql -uroot -p123456 -e 'show status like "%wsrep%"'
wsrep_incoming_addresses | 192.168.4.72:3306,192.168.4.73:3306,192.168.4.71:3306
wsrep_cluster_size | 3
wsrep_cluster_status | Primary
wsrep_connected | ONwsrep_ready | ON
3.0 测试集群功能
3.1 在主机4.71上授权,自动同步授权到4.72和4.73.
71]grant all on gamedb.* to yaya@"%" identified by '123456';
3.2 客户端4.50登录到服务器4.72
# mysql -h192.168.4.72 -uyaya -p123456
mysql> create database gamedb; //建库
mysql> create table gamedb.a( //建表
-> id int primary key auto_increment,name char(10));
mysql> insert into gamedb.a values(1,"tom"); //插入数据
mysql> insert into gamedb.a values(2,"jack"); //插入数据
3.3 分别在4.71,4.72,4.73上查看插入的数据
mysql> select * from gamedb.a;
+----+------+
| id | name |
+----+------+
| 1 | tom |
| 2 | jack |
+----+------+
2 rows in set (0.00 sec)
4.0 测试服务器故障,再自动恢复
71]# systemctl stop mysql //停止4.71的服务
mysql> insert into gamedb.a values(3,"tom1"); //客户端写入数据
Query OK, 1 row affected (0.16 sec)
mysql> insert into gamedb.a values(4,"jack1"); //客户端写入数据
Query OK, 1 row affected (0.12 sec)
mysql> select * from gamedb.a; //在服务器4.72和4.71查看插入的数据
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 2 | jack |
| 3 | tom1 |
| 4 | jack1 |
+----+-------+
4 rows in set (0.00 sec)
71]# ]# systemctl start mysql@bootstrap.service //再次启动4.71的集群服务
71]# netstat -anptul|grep :3306 //查看mysql端口
71]# netstat -anptul|grep :4567 //查看集群端口
mysql> select * from gamedb.a; //查看4.71的数据同步状态
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 2 | jack |
| 3 | tom1 |
| 4 | jack1 |
+----+-------+
4 rows in set (0.00 sec)
三,Mysql存储引擎
1.存储引擎?
作为可插拔式的组件提供,mysql自带的功能程序,专属于表的处理器;不同的存储引擎有不同的动能和存储方式.
2.查看数据库服务器可以使用的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)
3.建表时,手动指定存储引擎
mysql> create database db10;
mysql> create table db10.t1(
-> id int,name char(10))engine=myisam; //建表时指定存储引擎为myisam
Query OK, 0 rows affected (0.05 sec)
mysql> show create table t1 G; //查看存储引擎
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 //显示存储引擎
1 row in set (0.01 sec)
4.在数据库服务的主配置文件中修改存储引擎
#vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam //指定存储引擎
# systemctl restart mysqld //重启加载配置
5.对已建表的引擎做修改
Mysql>alter table 库名.表名 engine=引擎类型;
Mysql>alter table db10.t1 engine=myisam;
6.常用存储引擎的特点
Myisam存储引擎: 不支持表级锁;不支持事务,事务回滚,外键.
Myisam的表文件: 表结构-表名.frm; 索引-表名.MYI; 数据-表名.MYD
Innodb存储引擎:支持行级锁定,支持事务,事务回滚,外键.
Innodb的表文件:表名.frm和表名.idb.
Innodb的日志文件:ibdata1,ib_logfile0,ib_logfile1
7.Mysql的锁机制
7.1 锁的力度:表级锁(整张表)与行级锁(单行)
7.2 锁的类型:a.读锁(共享锁),支持并发性读b.写锁,独立的锁,上锁期间其它线程不能读和写.
7.3 查看当前锁的状态
[root@client ~]# mysql -uroot -p123456 -e 'show status like "%table_lock%"'
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| Performance_schema_table_lock_stat_lost | 0 |
| Table_locks_immediate | 88 |
| Table_locks_waited | 0 |
+-----------------------------------------+-------+
8.事务的特性
Atomic原子性,事务的操作是一个整体,不可分割,要么全部成功,要么全部失败.
Consitency一致性,事务操作的前后,表中的记录没有变化.
Isolation隔离性,事务之间的操作是隔离不受彼此影响的.
Durability永久性,数据一旦提交即不可更改.
测试永久性: 开两个相同的终端4.50,在其中一个终端上做如下操作.
mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set autocommit=off; //关闭自动提交功能
mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> create table db10.a4(
-> id int )engine=innodb;
Query OK, 0 rows affected (0.37 sec)
mysql> insert into db10.a4 values(200);
mysql> insert into db10.a4 values(300);
mysql> select * from db10.a4; //在关闭了commit的终端可以查看到插入的数据,而另一终端却不能查看到提交的数据.
+------+
| id |
+------+
| 200 |
| 300 |
+------+
2 rows in set (0.00 sec)
mysql> commit; //执行commit后,在没有关闭commit的终端可以查看到插入的数据.
总结: 那么我们在生产环境中,建表时如何决定使用存储引擎的类型,有以下两个意见:
- 查询操作相对较多的表,适合使用myisam引擎,以节省系统资源
- 写操作相对较多的表,适合使用innodb引擎,以解决并发性访问量大的问题
结束.