DBA（七）：PXC、MySQL存储引擎
生活随笔
收集整理的這篇文章主要介紹了
DBA（七）：PXC、MySQL存储引擎
小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
PXC
PXC概述
PXC介紹
- Percona XtrDB Cluster（簡稱PXC）
- 是基于Galera的mysql奧可用集群解決方案
- Galera Cluster是Codership公司開發(fā)的一套免費(fèi)開源的高可用方案
- PXC集群主要由兩部分組成：==Percona Server with XtraDB ==和 Write Set Replication patches（同步、多主復(fù)制插件）
- 官網(wǎng)地址
PXC特點(diǎn)
服務(wù)端口
| 3306 | 數(shù)據(jù)庫服務(wù)端口 |
| 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 | 集群服務(wù)程序 |
pxc軟件包提取碼：t2yg
環(huán)境準(zhǔn)備
配置主機(jī)名與ip地址綁定
配置服務(wù)器192.168.4.71
配置服務(wù)器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配置服務(wù)器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在任意一臺服務(wù)器上ping 對方的主機(jī)名，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服務(wù)器安裝軟件包，軟件包之間有依賴，注意一定要按照順序安裝
[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我們可以寫一個(gè)簡單的腳本 [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執(zhí)行腳本，記住一定要在pxc目錄下進(jìn)行！！！ [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主機(jī)執(zhí)行腳本，安裝軟件 [root@host72 ~]# cd /opt/pxc [root@host72 pxc]# ./pxc.sh ----------- [root@host72 ~]# cd /opt/pxc [root@host72 pxc]# ./pxc.sh配置服務(wù)
1.修改數(shù)據(jù)庫服務(wù)運(yùn)行參數(shù)配置文件mysqld.cnf71主機(jī)： [root@host71 pxc]# ls /etc/percona-xtradb-cluster.conf.d/ #配置文件的所在目錄 mysqld.cnf #數(shù)據(jù)庫服務(wù)運(yùn)行參數(shù)的配置文件 mysqld_safe.cnf #Percona Server 5.7配置文件 wsrep.cnf #PXC集群配置文件[root@host71 pxc]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf6 [mysqld]7 server-id=1 #只需要修改server-id，每臺主機(jī)不允許重復(fù) 8 datadir=/var/lib/mysql #數(shù)據(jù)庫目錄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 #啟用鏈?zhǔn)綇?fù)制14 expire_logs_days=7 #日志文件保留天數(shù)72主機(jī)： [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不允許重復(fù) datadir=/var/lib/mysql #數(shù)據(jù)庫目錄 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 #啟用鏈?zhǔn)綇?fù)制 expire_logs_days=7 #日志文件保留天數(shù)73主機(jī)： [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不允許重復(fù) datadir=/var/lib/mysql #數(shù)據(jù)庫目錄 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 #啟用鏈?zhǔn)綇?fù)制 expire_logs_days=7 #日志文件保留天數(shù)======================================================== 2.修改Percona Server 5.7配置文件mysqld_safe.cnf 三臺數(shù)據(jù)庫服務(wù)器，使用默認(rèn)配置即可[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.cnf71主機(jī)： [root@host71 pxc]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf8 wsrep_cluster_address=gcomm://192.168.4.71,192.168.4.72,192.168.4.73 #數(shù)據(jù)庫服務(wù)器成員列表25 wsrep_node_address=192.168.4.71 #本機(jī)主機(jī)IP27 wsrep_cluster_name=pxc-cluster #集群名稱，3臺主機(jī)必須相同30 wsrep_node_name=pxcnode71 #本機(jī)主機(jī)名，可以不用和本機(jī)主機(jī)名一樣39 wsrep_sst_auth="sstuser:123qqq...A" #全量同步時(shí)的用來連接自己用戶名和密碼72主機(jī)： [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.7325 wsrep_node_address=192.168.4.72 #本機(jī)主機(jī)IP27 wsrep_cluster_name=pxc-cluster #集群名稱，3臺主機(jī)必須相同30 wsrep_node_name=pxcnode71 #本機(jī)主機(jī)名，可以不用和本機(jī)主機(jī)名一樣39 wsrep_sst_auth="sstuser:123qqq...A" #全量同步時(shí)的用來連接自己用戶名和密碼73主機(jī)： [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.7325 wsrep_node_address=192.168.4.73 #本機(jī)主機(jī)IP27 wsrep_cluster_name=pxc-cluster #集群名稱，3臺主機(jī)必須相同30 wsrep_node_name=pxcnode71 #本機(jī)主機(jī)名，可以不用和本機(jī)主機(jī)名一樣39 wsrep_sst_auth="sstuser:123qqq...A" #全量同步時(shí)的用來連接自己用戶名和密碼啟動服務(wù)
1.初始化集群，啟動集群服務(wù)71主機(jī)： [root@host71 ~]# systemctl start mysql@bootstrap.service #啟動集群服務(wù)，首次啟動服務(wù)時(shí)間較長 [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 #查看數(shù)據(jù)庫管理員初始密碼 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"; #添加授權(quán)用戶，全量同步用戶的權(quán)限[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 #數(shù)據(jù)庫服務(wù)端口 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 #增量同步端口 #全量同步和增量同步端口只有在進(jìn)行操作時(shí)才可以看得到端口======================================================= 2.啟動數(shù)據(jù)庫服務(wù) 啟動72與73主機(jī)的數(shù)據(jù)庫服務(wù)，會自動同步71主機(jī)的root初始密碼以及授權(quán)用戶sstuser72主機(jī)： 一定要記得關(guān)閉防火墻，selinux設(shè)為寬松模式！！ [root@host72 ~]# ls /var/lib/mysql [root@host72 ~]# systemctl start mysql #此時(shí)初始化會自動全量同步wsrep.cnf文件，由于71主機(jī)已經(jīng)配置完成，所以會自動全量同步71主機(jī)的數(shù)據(jù)庫 [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主機(jī)： [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.查看集群信息 啟動數(shù)據(jù)庫服務(wù)，在任意一臺主機(jī)查看數(shù)據(jù)都可以 [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 #集群服務(wù)器臺數(shù) wsrep_cluster_status Primary #集群狀態(tài) wsrep_connected ON #連接狀態(tài) wsrep_ready ON #服務(wù)狀態(tài)======================================================= 2.訪問集群，存取數(shù)據(jù) 添加訪問數(shù)據(jù)的連接用戶 （在任意一臺服務(wù)器上添加都可以，另外的2臺服務(wù)器會自動同步授權(quán)用戶）72主機(jī)授權(quán)： [root@host72 ~]# mysql -uroot -p123456 -e 'grant all on gamedb.* to admin@"%" identified by "123456"' #添加授權(quán)用戶71主機(jī)查看： [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主機(jī)查看： [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 | +-------+--------------------------------------------------------------------------------------------- 客戶端連接集群存取數(shù)據(jù)（連接任意一臺數(shù)據(jù)庫服務(wù)器的IP地址都可以）連接71主機(jī)的服務(wù)器 [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)' #插入數(shù)據(jù) 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 #此時(shí)會報(bào)錯(cuò)，要求PXC集群必須要有主鍵 [root@host50 ~]# mysql -h192.168.4.71 -uadmin -p123456 MySQL [(none)]> alter table gamedb.t1 add x int primary key auto_increment first; #添加一個(gè)主鍵字段 Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0MySQL [(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); #插入數(shù)據(jù) 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) #根據(jù)服務(wù)器的活著的臺數(shù)進(jìn)行自增的數(shù)量在72和73主機(jī)數(shù)據(jù)庫服務(wù)器查看數(shù)據(jù)： [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主機(jī)服務(wù)器： [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.停止數(shù)據(jù)庫服務(wù) 停止3臺數(shù)據(jù)庫服務(wù)器的任意一臺主機(jī)的數(shù)據(jù)庫都不會影響數(shù)據(jù)的存取停止71主機(jī)的數(shù)據(jù)庫服務(wù)： 注：71主機(jī)開啟服務(wù)命令是systemctl start mysql@bootstrap.service ，停止時(shí)也要這樣的格式 [root@host71 ~]# systemctl stop mysql@bootstrap.service [root@host71 ~]# ss -nutlp | grep 3306 [root@host71 ~]# ss -nutlp | grep 4567客戶端50連接集群中的任意一臺依舊可以存儲數(shù)據(jù) 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主機(jī)的mysql服務(wù)啟動 [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依舊可以正常存儲數(shù)據(jù) 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集群信息的命令（在任何一臺數(shù)據(jù)庫服務(wù)器上都可以查）以72主機(jī)為例 [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服務(wù)軟件自帶的功能程序，處理表的處理器
- 不同的存儲引擎有不同的功能和數(shù)據(jù)存儲方式
- MySQL服務(wù)內(nèi)置程序，每種程序的功能都不一樣，希望向表中存儲數(shù)據(jù)時(shí)，使用mysql某種功能
- MySQL 5.0/5.1 ----> MyISAM
- MYSQL 5.5/56 ----> InnoDB
MySQL體系結(jié)構(gòu)
配置存儲引擎
修改表存儲引擎
1.查看存儲引擎 查看數(shù)據(jù)庫服務(wù)默認(rèn)使用的存儲引擎 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) 默認(rèn)使用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.修改表的存儲引擎創(chuàng)建表時(shí)指定存儲引擎，未指定時(shí)，使用默認(rèn)存儲引擎 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: 0mysql> 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 #查看數(shù)據(jù)庫目錄中g(shù)amedb庫下的t2表的存儲引擎為MYI db.opt t1.frm t1.ibd t2.frm t2.MYD t2.MYI (ps： mysql> alter table gamedb.t2 engine=myisam; （如果在PXC的環(huán)境下，就需要在主配置文件mysqld.cnf中設(shè)置pxc_strict_mode = ENFORCING 或者 MASTER，但PXC集群里的表必須是innodb存儲引擎，只有innodb支持事務(wù)，否則會鎖表，無法進(jìn)行同步） 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 )修改服務(wù)的存儲引擎
設(shè)置數(shù)據(jù)庫服務(wù)使用的存儲引擎 修改/etc/my.cnf配置文件 [root@host51 ~]# vim /etc/my.cnf [mysqld] default-storage-engine=myisam #將數(shù)據(jù)庫的默認(rèn)引擎修改為myisam [root@host51 gamedb]# ls db.opt t1.frm t1.ibd t3.frm [root@host51 gamedb]# systemctl restart mysqld存儲引擎的特點(diǎn)
Myisam存儲引擎
- 主要特點(diǎn)
- 支持表級鎖
- 不支持事務(wù)、事務(wù)回滾、外鍵
- 表文件
innodb存儲引擎
- 主要特點(diǎn)
- 支持行級鎖定
- 支持事務(wù)、事務(wù)回滾、外鍵
- 表文件
- 事務(wù)日志文件
事務(wù)與事務(wù)回滾
- 事務(wù)：
- 從與數(shù)據(jù)庫建立連接開始，執(zhí)行SQL命令（select update insert delete等）操作數(shù)據(jù)，斷開連接的過程被稱為事務(wù)
- 事務(wù)回滾：
- 在對innodb存儲引擎的表操作的過程中，任何一部分沒有操作成功，會恢復(fù)之前所有的操作
MySQL鎖機(jī)制
-
加鎖
-
是解決對表的并發(fā)訪問的沖突的問題
-
鎖粒度（鎖的范圍）
- 表級鎖：簡稱表鎖，對整張表加鎖（myisam）
- 行級鎖：簡稱行鎖，僅對被訪問的行分別加鎖（innodb）
- 鎖類型
- 讀鎖（select）：又稱共享鎖，支持并發(fā)讀，當(dāng)?shù)谝粋€(gè)客戶端進(jìn)行查數(shù)據(jù)時(shí)，其他的客戶端也可以同時(shí)進(jìn)行查操作
- 寫鎖（insert ，update ，delete）：又稱互斥鎖，排它鎖，是獨(dú)占鎖，上鎖期間其他線程不能毒鏢或者寫表，在第一個(gè)客戶端進(jìn)行寫入數(shù)據(jù)時(shí)，不論其他的客戶端進(jìn)行讀操作還是寫操作都會被拒絕，只有當(dāng)?shù)谝粋€(gè)客戶端釋放寫鎖才可以進(jìn)行操作
- 查看當(dāng)前鎖狀態(tài)
事務(wù)特性（ACID）
- Atomic：原子性
- 事務(wù)的整個(gè)操作是一個(gè)整體，不可分割，要么全部成功，要么全部失敗
- Consistency：一致性
- 事務(wù)操作的前后，表中的記錄沒有任何的變化
- Isolation：隔離性
- 事務(wù)操作是相互隔離不受影響的
- Durability：持久性
- 數(shù)據(jù)一單提交，不可改變，永久改變表數(shù)據(jù)
工作建表時(shí)，如何決定表使用哪種存儲引擎？
- 查詢訪問多的表，適合使用myisam存儲引擎，可以節(jié)省系統(tǒng)CPU硬件資源
- 寫操作多的表，適合使用innodb存儲引擎，可以加大并發(fā)訪問量
總結(jié)
以上是生活随笔為你收集整理的DBA（七）：PXC、MySQL存储引擎的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： DBA（六）：MHA集群
- 下一篇： NoSQL（一）：NoSQL数据库、re