å顾é¨ç½²MHAé群æ¥éª¤ï¼
1ãåå¤ç¯å¢
1.1Â å ¬å ±é ç½®ï¼å¯ç¨binlogæ¥å¿Â  ç¨æ·ææÂ
ååæ¥å¤å¶æ¨¡å¼Â  ç¦æ¢å é¤ä¸ç»§æ¥å¿æ件
1.2 sshå å¯ç»å½
1.2.1ã æææ°æ®åºæå¡å¨ä¹é´sshå å¯ç»å½
1.2.2ã 管ç主æºè¦sshå å¯ç»å½æææ°æ®åºæå¡å¨
1.3 主ä»åæ¥å¤å¶Â
é ç½®ä¸ä¸»å¤ä»åæ¥ç»æ
2ãé 置管ç主æº
3ãé ç½®æ°æ®åºæå¡å¨
4ãæµè¯é ç½®
5ãå¯å¨ç®¡çæå¡
6ãæµè¯é«å¯ç¨åè½
MHAé群缺ç¹æ»ç»ï¼
å¿ é¡»è¦ævipå°å
å®æºç主æå¡å¨éè¦æå¨æ·»å å°é群é,è¿éè¦æå¨åæ¥å®æºæé´çæ°æ®
管çæå¡åç°ä¸»æå¡å¨å®æºåï¼ä¼è°ç¨æ éåæ¢èæ¬ï¼
ævipå°åé¨ç½²å¨æ°ç主æ°æ®åºæå¡å¨ä¸ã管çæå¡ä¼
èªå¨åæ¢ï¼éè¦æå¨å¯å¨ç®¡çæå¡å¨ï¼æè½çè§æ°ç主æ°æ®æå¡å¨
æ éåæ¢æé´ä¼ææ°æ®ä¸¢å¤±çæ åµ
ä¸Â é¨ç½²PXCé群
1.1  PXCä»ç»
Percona XtraDB Clusterï¼ç®ç§°PXCï¼
æ¯åºäºGaleraçMySQLé«å¯ç¨é群解å³æ¹æ¡
Galera Clusteræ¯Codershipå ¬å¸å¼åçä¸å¥å è´¹å¼æºçé«å¯ç¨æ¹æ¡
PXCé群主è¦ç±ä¸¤é¨åç»æï¼
Percona Server with XtraDBåWrite Set Replication patchesï¼åæ¥ãå¤ä¸»å¤å¶æ件ï¼
å®ç½http://galeracluster.com
 1.2  pxcé群ç¹ç¹
1ãæ°æ®å¼ºä¸è´æ§ãæ åæ¥å»¶è¿
2ã没æ主ä»åæ¢æä½ï¼æ é使ç¨èæIP
3ãæ¯æInnoDBåå¨å¼æ
4ãå¤çº¿ç¨å¤å¶
5ãé¨ç½²ä½¿ç¨ç®å
6ãæ¯æèç¹èªå¨å å ¥ï¼æ éæå¨æ·è´æ°æ®
1.3  ç¸å ³ç«¯å£å·
3306 æ°æ®åºæå¡ç«¯å£
4444 SST 端å£
4567 é群é信端å£
4568 IST 端å£
SST State Snapshot TransferÂ å ¨éåæ¥
IST Incremental State Transfer å¢éåæ¥
 2.4 ç¯å¢åå¤
åå¤3å°æå¡å¨(没æå®è£ è¿ä»»ä½æ°æ®åºæå¡è½¯ä»¶)
é ç½®Ipå°å  192.168.4.71  192.168.4.72  192.168.4.73
æ·è´PXC软件 å°Â 3å°æå¡å¨Â  /root/
äºé ç½®pxcé群
第1æ¥ï¼å®è£ 软件 ï¼3å°ä¸»æºé½è¦å®è£ ï¼
å¿ é¡»æç §é¡ºåºå®è£  å 为软件ä¹é´æä¾èµ
cd pxc/
yum -y install libev-4.15-1.el6.rf.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
yum -y install qpress-1.1-14.11.x86_64.rpm
tar -xf Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar
yum -y install Percona-XtraDB-Cluster-*.rpm
 第2æ¥ï¼ä¿®æ¹é ç½®æ件
2.1 æå®é群ä¸ä¸»æºçserver_id  修æ¹Â mysqld.cnfæ件
2.2 æå®é群信æ¯Â ä¿®æ¹Â wsrep.cnfæ件
ä¿®æ¹71主æºçserver_id
vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
server_id=71
ä¿®æ¹72主æºçserver_id
vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
server_id=72
ä¿®æ¹73主æºçserver_id
vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
server_id=73
ä¿®æ¹71主æºçwsrep.cnf æ件
]# 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
27 wsrep_cluster_name=pxc-cluster
30 wsrep_node_name=pxcnode71
39 wsrep_sst_auth="sstuser:123qqq...A"
wq:
ä¿®æ¹72主æºçwsrep.cnf æ件
]# 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.72
27 wsrep_cluster_name=pxc-cluster
30 wsrep_node_name=pxcnode72
39 wsrep_sst_auth="sstuser:123qqq...A"
:wq
ä¿®æ¹73主æºçwsrep.cnf æ件
]# 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.73
27 wsrep_cluster_name=pxc-cluster
30 wsrep_node_name=pxcnode73
39 wsrep_sst_auth="sstuser:123qqq...A"
:wq
第3æ¥ï¼åå§åé群
说æï¼å¨ä»»æ1å°æå¡å¨ä¸æ§è¡åå§åé群æä½é½å¯ä»¥ï¼ä» éè¦æ§è¡ä¸éï¼
ç»ä¸å¨71 主æºæ§è¡Â ï¼å ·ä½æ¥éª¤å¦ä¸
    1ãå¯å¨æå¡
    2ã使ç¨åå§å¯ç è¿æ¥æå¡
    3ãæ·»å ææç¨æ·sstuser
    4ãæ¥çæ°æ®åºæå¡å¨ç«¯å£Â 3306
    5ãæ¥çé群端å£4567
    6ã管çåroot ç»å½Â 建åºè¡¨åå¨è®°å½
[[email protected] ~]# systemctl start [email protected] åå§åé群
[[email protected] ~]# ls /var/lib/mysql æ¥çæ°æ®æ°æ®åºæ件å表 ææ件说ææå
auto.cnf gvwstate.dat mysqld_safe.pid pxcnode71-bin.000002
ca-key.pem ib_buffer_pool mysql.sock pxcnode71-bin.index
ca.pem ibdata1 mysql.sock.lock server-cert.pem
client-cert.pem ib_logfile0 performance_schema server-key.pem
client-key.pem ib_logfile1 private_key.pem sys
galera.cache ibtmp1 public_key.pem xb_doublewrite
grastate.dat mysql pxcnode71-bin.000001
[[email protected] ~]#
[[email protected] ~]# grep password /var/log/mysqld.log æ¥ç管çååå§åå¯ç
2021-11-22T02:49:33.621967Z 1 [Note] A temporary password is generated for [email protected]: UfjF5UQGb/fq
[[email protected] ~]#
[[email protected] ~]# mysql -uroot -p'UfjF5UQGb/fq' åå§å¯ç ç»å½
mysql> alter user root@"localhost" identified by "123456"; 强å¶ä¿®æ¹å¯ç
mysql> exit æå¼è¿æ¥
Bye
[[email protected] ~]# mysql -uroot -p123456 使ç¨ä¿®æ¹åçå¯ç ç»å½
mysql> grant all on *.* to sstuser@"localhost" identified by "123qqq...A"; æ·»å å
¨éåæ¥ç¨æ·sstuser
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> exit
Bye
[[email protected] ~]# netstat -utnalp | grep 3306 #æ¥çæ°æ®åºæå¡ç«¯å£
tcp6 0 0 :::3306 :::* LISTEN 5359/mysqld
[[email protected] ~]#
[[email protected] ~]# netstat -utnalp | grep 4567 #æ¥çé群端å£
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 5359/mysqld
[[email protected] ~]#
[[email protected] ~]# mysql -uroot -p123456 管çåç»å½ï¼å»ºåºè¡¨åå¨è®°å½
mysql> create database db1;
mysql> create table db1.a(id int primary key auto_increment,age int); pxcé群ä¸ç表å¿
é¡»è¦æä¸»é® èªå¢é¿å¯é
mysql> insert into db1.a(age)values(18),(24),(29);
mysql> select * from db1.a;
+----+------+
| id | age |
+----+------+
| 1 | 18 |
| 2 | 24 |
| 3 | 29 |
+----+------+
3 rows in set (0.00 sec)
mysql>
 第4æ¥ï¼å¨72 å  73 主æºÂ åå«å¯å¨æ°æ®åºæå¡,å¯å¨æå¡åä¼èªå¨åæ¥71主æºçæ°æ®
å¨72主æºå¯å¨mysqlæå¡
[[email protected] ~]# systemctl start mysql
[[email protected] ~]# ls /var/lib/mysql
auto.cnf ib_logfile0 pxcnode72-bin.000001
ca-key.pem ib_logfile1 pxcnode72-bin.index
ca.pem ibtmp1 server-cert.pem
client-cert.pem innobackup.move.log server-key.pem
client-key.pem innobackup.prepare.log sys
db1 mysql xb_doublewrite
galera.cache mysql.sock xtrabackup_binlog_pos_innodb
grastate.dat mysql.sock.lock xtrabackup_galera_info
gvwstate.dat performance_schema xtrabackup_info
ib_buffer_pool private_key.pem xtrabackup_master_key_id
ibdata1 public_key.pem
[[email protected] ~]# netstat -utnalp |grep 3306
tcp6 0 0 :::3306 :::* LISTEN 5443/mysqld
[[email protected] ~]# netstat -utnalp |grep 4567
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 5443/mysqld
tcp 0 0 192.168.4.72:4567 192.168.4.72:41634 CLOSE_WAIT 5443/mysqld
tcp 0 0 192.168.4.72:52306 192.168.4.71:4567 ESTABLISHED 5443/mysqld
[[email protected] ~]#
[[email protected] ~]# mysql -uroot -p123456 -e 'select * from db1.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | age |
+----+------+
| 1 | 18 |
| 2 | 24 |
| 3 | 29 |
+----+------+
[[email protected] ~]#
å¨73主æºå¯å¨mysqlæå¡
[[email protected] pxc]# ls /var/lib/mysql
[[email protected] pxc]#
[[email protected] pxc]# systemctl start mysql
[[email protected] pxc]# ls /var/lib/mysql
auto.cnf ib_logfile0 pxcnode73-bin.000001
ca-key.pem ib_logfile1 pxcnode73-bin.index
ca.pem ibtmp1 server-cert.pem
client-cert.pem innobackup.move.log server-key.pem
client-key.pem innobackup.prepare.log sys
db1 mysql xb_doublewrite
galera.cache mysql.sock xtrabackup_binlog_pos_innodb
grastate.dat mysql.sock.lock xtrabackup_galera_info
gvwstate.dat performance_schema xtrabackup_info
ib_buffer_pool private_key.pem xtrabackup_master_key_id
ibdata1 public_key.pem
[[email protected] pxc]# netstat -utnalp | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 5383/mysqld
[[email protected] pxc]# netstat -utnalp | grep 4567
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 5383/mysqld
tcp 0 0 192.168.4.73:32956 192.168.4.71:4567 ESTABLISHED 5383/mysqld
tcp 0 0 192.168.4.73:4567 192.168.4.73:43872 CLOSE_WAIT 5383/mysqld
tcp 0 0 192.168.4.73:42526 192.168.4.72:4567 ESTABLISHED 5383/mysqld
[[email protected] pxc]#
[[email protected] pxc]# mysql -uroot -p123456 -e 'select * from db1.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | age |
+----+------+
| 1 | 18 |
| 2 | 24 |
| 3 | 29 |
+----+------+
[[email protected] pxc]#
é误ç»ä¸è§£å³åæ³ï¼
1ãæ¥çmysqlæå¡çç¶è¿ç¨pid éè¿kill -9 ææ»ç¶è¿ç¨
]# which pstree || yum -y install psmisc
]# pstree -p | grep mysqld
]# kill -9 pidå·
2ãæ¸
空æ°æ®åºç®å½ rm -rf /var/lib/mysql/*
3ãæ£æ¥ mysqld.cnf å wsrep.cnf æ件çé
置项ç®
4ãæ£æ¥ é²ç«å¢å selinuxæå¡å
³éäºå
å¦æåå§é群失败ï¼å°±éæ°æ§è¡é群åå§å
å¦æå¯å¨MySQLæå¡é误ï¼å°±éæ°æ§è¡å¯å¨mysqlæå¡
æµè¯é群
第1æ¥ï¼è¿æ¥é群ä¸çä»»æ主æºååæ°æ®Â
#å¨ä»»ææå¡å¨æ·»å 客æ·ç«¯è¿æ¥æ°æ®åºæå¡ä½¿ç¨çç¨æ·
#å¨å®¢æ·ç«¯è¿æ¥æ°æ®åºæå¡
#æ¥çæ°æ®æ¯å¦åæ¥
#å¨73ä¸»æº æ·»å yayaç¨æ·
[[email protected] ~]# mysql -uroot -p123456 -e 'grant select,insert on db1.* to yaya@"%" identified by "123456"'
mysql: [Warning] Using a password on the command line interface can be insecure.
[[email protected] ~]#
#客æ·ç«¯50è¿æ¥é群ä¸çä»»æä¸»æº è®¿é®æ°æ®
[[email protected] ~]# mysql -h192.168.4.71 -uyaya -p123456 -e 'select * from db1.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | age |
+----+------+
| 1 | 18 |
| 2 | 24 |
| 3 | 29 |
+----+------+
[[email protected] ~]#
#客æ·ç«¯è¿æ¥72主æº
[[email protected] ~]# mysql -h192.168.4.72 -uyaya -p123456 -e 'insert into db1.a(age) values(33)'
mysql: [Warning] Using a password on the command line interface can be insecure.
[[email protected] ~]#
#客æ·ç«¯è¿æ¥73主æº
[[email protected] ~]# mysql -h192.168.4.73 -uyaya -p123456 -e 'insert into db1.a(age) values(33)'
mysql: [Warning] Using a password on the command line interface can be insecure.
[[email protected] ~]#
[[email protected] ~]# mysql -h192.168.4.71 -uyaya -p123456 -e 'insert into db1.a(age) values(33)'
mysql: [Warning] Using a password on the command line interface can be insecure.
[[email protected] ~]#
#客æ·ç«¯è¿æ¥71
[[email protected] ~]# mysql -h192.168.4.71 -uyaya -p123456 -e 'select * from db1.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | age |
+----+------+
| 1 | 18 |
| 2 | 24 |
| 3 | 29 |
| 4 | 33 |
| 6 | 33 |
| 8 | 33 |
+----+------+
第2æ¥ï¼Â é群ä¸åªè¦æ1å°æ°æ®åºæå¡å¨æ¯æ£å¸¸çï¼å°±è½æä¾åååè½
#åæ¢ä»»æ2å°ä¸»æºçæ°æ®åºæå¡ï¼
#é½å¯ä»¥è®¿é®å©ä¸çä¸å°æå¡å¨ååæ°æ®
[[email protected] ~]# systemctl stop [email protected]
[[email protected] ~]#
#å¨é群ä¸çä»»æ主æºæ¥çé群ç¶æ
[[email protected] ~]# mysql -uroot -p123456 -e 'show status like "%wsrep%"'
wsrep_incoming_addresses | 192.168.4.72:3306,192.168.4.73:3306 |
wsrep_cluster_size | 2
wsrep_cluster_status | Primary |
wsrep_connected | ON
#åæ¢73主æºçæ°æ®åºæå¡
[[email protected] ~]# systemctl stop mysql
[[email protected] ~]#
#å次æ¥çé群ç¶æ ï¼å¹¶å¯¹æ°æ®åºåæä½ï¼insert æselect ï¼
[[email protected] ~]# mysql -uroot -p123456 -e 'show status like "%wsrep%"'
[[email protected] ~]# mysql -uroot -p123456 -e 'insert into db1.a(age) values(100)'
[[email protected] ~]# mysql -uroot -p123456 -e 'insert into db1.a(age) values(100)'
[[email protected] ~]# mysql -uroot -p123456 -e 'insert into db1.a(age) values(100)'
第3æ¥ï¼Â å®æºçæå¡å¨å¯å¨åä¼èªå¨å å ¥é群并åæ¥å®æºæé´çæ°æ®
#åå«å¯å¨71 å 73 主æºçæ°æ®åºæå¡
[[email protected] ~]# systemctl start mysql
[[email protected] ~]# systemctl start mysql
客æ·ç«¯è¿æ¥71 æ 73 æ¥çæ°æ®
[[email protected] ~]# mysql -h192.168.4.73 -uyaya -p123456 -e 'select * from db1.a'
[[email protected] ~]# mysql -h192.168.4.71 -uyaya -p123456 -e 'select * from db1.a'
é便è¿æ¥1å°æ°æ®åºæ¥çé群ç¶æ==ã+
[[email protected] ~]# mysql -uroot -p123456 -e 'show status like "%wsrep%"'
wsrep_incoming_addresses 192.168.4.72:3306,192.168.4.71:3306,192.168.4.73:3306 wsrep_cluster_size 3
äºãmysqlåå¨å¼æ ï¼80%æ¯ç论 çéå¨äºç解ï¼
åå¨å¼æä»ç»
MySQLæå¡è½¯ä»¶èªå¸¦çåè½ç¨åº
å½å¯¹è¡¨éçæ°æ®åselect æinsert 访é®æ¶ï¼
ä¼æ ¹æ®è¡¨ä½¿ç¨çåå¨å¼æ对æ°æ®åå¤çãä¸åçåå¨å¼ææä¸åçåè½åæ°æ®åå¨æ¹å¼ã
ä½ä¸ºå¯ææå¼çç»ä»¶æä¾
MySQLÂ 5.0/5.1Â (MyISAM)Â Â Â Â Â
MySQLÂ 5.5/5.6Â (InnoDB)
MySQLæå¡ä½ç³»ç»æ ï¼mysqlæå¡çåè½åç±»ï¼
1 管çå·¥å ·ï¼Â å®è£ MySQLæå¡è½¯ä»¶åï¼æä¾ç管çå½ä»¤
2è¿æ¥æ± ï¼éªè¯å®¢æ·ç«¯è¿æ¥æ¶ä½¿ç¨çç¨æ·åå¯ç æ¯å¦æ£ç¡®Â åæ¶éªè¯æ°æ®åºæå¡å¨æ¯å¦æmysqldè¿ç¨ç¸åºè¿æ¥
3 SQLæ¥å£: æç¨æ·æ§è¡çSQLå½ä»¤ä¼ éç»æ¬æºçmysqld è¿ç¨
4åæå¨ï¼æ£æ¥SQLå½ä»¤çè¯å¥å对æ°æ®ç访é®æé
5ä¼åå¨ï¼å¯¹è¦æ§è¡ç SQLå½ä»¤åä¼åï¼æ¯å åèªå¨åè½ç¨åºï¼
6æ¥è¯¢ç¼åï¼åååºä¸å®çç©çå å空é´ç»MySQLæå¡åå¨æ¥æ¾è¿çæ°æ®ã
7åå¨å¼æï¼å½å¯¹è¡¨éçæ°æ®åæ¥è¯¢ï¼selectï¼Â æåæä½ï¼insert /update /deleteï¼ä¼è°ç¨åå¨å¼æ对表ä¸çæ°æ®åå¤çï¼è³äºå¦ä½å¤çåå³è¡¨ä½¿ç¨çåå¨å¼æçåè½
8æ件系ç»ï¼é常æçå°±æ¯çµèç硬çÂ
 MySQLæå¡çå·¥ä½è¿ç¨
2.1 å¤çæ¥è¯¢select访é®çå·¥ä½è¿ç¨
第1æ¥ï¼Â 客æ·ç«¯åæå¡å¨åèµ·è¿æ¥è¯·æ±
第2æ¥ï¼Â æå¡å¨æ¥æ¶å°å®¢æ·ç«¯è¿æ¥è¯·æ±å¹¶ååº
第3æ¥ï¼Â
å¦æ客æ·ç«¯æ§è¡çselcet 访é®ï¼å å¨æ¥è¯¢ç¼åéæåæ°æ®åå¤ç»å®¢æ·ç«¯ï¼
å¦ææ°æ®åºæå¡å¨å¨æ¥è¯¢ç¼åé没ææ¾å°ç¨æ·è®¿é®çæ°æ®ï¼è¿
æ¶å°±è¦å°æ°æ®åºæå¡å¨ç表éæ¥æ¾æ°æ®ï¼å¯¹æ°æ®åºç®å½ä¸ç表
å访é®æ¯å°±ä¼è°ç¨è¡¨ä½¿ç¨çåå¨å¼æ对表åå¤çï¼
ç¶åææ¥æ¾å°çæ°æ®å æ¾å°æ¥è¯¢ç¼å å¨åå¤ç»å®¢æ·ç«¯
第4æ¥ï¼æå¼è¿æ¥
å¨æ°æ®åºæå¡å¨æ¥çä¸æ¥è¯¢ç¼åç¸å ³çé 置项
mysql> show variables like "%query_cache%";
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF | é»è®¤å°±æ²¡æå¯ç¨æ¥è¯¢ç¼å
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
 å¤çåå¨ï¼insertï¼è®¿é®çå·¥ä½è¿ç¨
第1æ¥ï¼Â 客æ·ç«¯åæå¡å¨åèµ·è¿æ¥è¯·æ±
第2æ¥ï¼Â æå¡å¨æ¥æ¶å°å®¢æ·ç«¯è¿æ¥è¯·æ±å¹¶ååº
第3æ¥ï¼Â æ ¹æ®è¡¨ä½¿ç¨çåå¨å¼æ 对表ä¸çæ°æ®å对åºçå¤çã
第4æ¥ï¼Â æå¼è¿æ¥
åå¨å¼æ管ç
æ¥çåå¨å¼æ
æ¥çæ°æ®åºæå¡æ¯æçåå¨å¼æåé»è®¤ä½¿ç¨çåå¨å¼æ
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.00 sec)
mysql>
æ¥çå½åå·²æ表使ç¨çåå¨å¼æ
mysql> show create table DB1.t3 \G
ä¿®æ¹åå¨å¼æ
ä¿®æ¹æ°æ®åºæå¡é»è®¤ä½¿ç¨çåå¨å¼æ
[[email protected] ~]#vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
:wq
[[email protected] ~]# systemctl restart mysqld
[[email protected] ~]# mysql -uroot -pNSD2107...a
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | 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 | DEFAULT | 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.00 sec)
mysql>
说æï¼å»ºè¡¨æ¶ï¼ä¸æå®è¡¨ä½¿ç¨çåå¨å¼æï¼ä½¿ç¨é»è®¤åå¨å¼æ
表使ç¨çåå¨å¼æä¸åï¼åå¨æ¹å¼å使ç¨MySQLæå¡çåè½é½ä¸ä¸æ ·
mysql> create database db10;
mysql> create table db10.a(id int);
mysql> show create table db10.a \G
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
建表æ¶æå®è¡¨ä½¿ç¨çåå¨å¼æ
mysql> create table db10.b(name char(10))engine = innodb;
mysql> create table db10.c(addr char(10))engine = memory;
说æ:
memoryåå¨å¼æç表 æ¯ä¸ªè¡¨å¯¹åº1个表æ件
innodbåå¨å¼æç表 æ¯ä¸ªè¡¨å¯¹åº2个表æ件
myisamåå¨å¼æç表 æ¯ä¸ªè¡¨å¯¹åº3个表æ件
mysql> system ls /var/lib/mysql/db10/a.*
/var/lib/mysql/db10/a.frm /var/lib/mysql/db10/a.MYD /var/lib/mysql/db10/a.MYI
mysql> system ls /var/lib/mysql/db10/b.*
/var/lib/mysql/db10/b.frm /var/lib/mysql/db10/b.ibd
mysql>
mysql> system ls /var/lib/mysql/db10/c.*
/var/lib/mysql/db10/c.frm
mysql>
ä¿®æ¹è¡¨ä½¿ç¨çåå¨å¼æï¼ä¸è¬å¨è¡¨åå¨åå¨æ°æ®ä¹åä¿®æ¹ï¼
说æï¼åå¨å¼æä¿®æ¹äºï¼åå¨æ°æ®çä½ç½®ä¹ä¼æ¹åï¼
mysql> alter table db10.c engine=myisam;
mysql> system ls /var/lib/mysql/db10/c.*
/var/lib/mysql/db10/c.frm /var/lib/mysql/db10/c.MYD /var/lib/mysql/db10/c.MYI
mysql>
常ç¨åå¨å¼æç¹ç¹ï¼ç产ç¯å¢ä¸å¸¸ç¨çåå¨å¼æï¼
myisamåå¨å¼æç¹ç¹
æ¯æ表级é ãä¸æ¯æäºå¡ãäºå¡åæ»ãå¤é®
 myisamåå¨å¼æç表 æ¯ä¸ªè¡¨å¯¹åº3个表æ件
 表å.frm  åå¨è¡¨å¤´ä¿¡æ¯Â      mysql> desc  åº.表ï¼
 表å.MYI  åå¨è¡¨ç´¢å¼ä¿¡æ¯Â    mysql> show index from  åº.表;
 表å.MYD  åå¨è¡¨éçæ°æ®Â    mysql> select  * from  åº.表;
innodbåå¨å¼æç¹ç¹
æ¯æè¡çº§ãæ¯æäºå¡ãäºå¡åæ»ãå¤é®
innodbåå¨å¼æç表 æ¯ä¸ªè¡¨å¯¹åº2个表æ件
表å.frm  åå¨è¡¨å¤´ä¿¡æ¯Â      mysql> desc  åº.表ï¼
表å.ibd  åå¨è¡¨çç´¢å¼ä¿¡æ¯+表çæ°æ®ä¿¡æ¯Â    Â
show index from  åº.表; +  select  * from  åº.表;
ä¸ä¸æ¯è¯
说æ: ç»è¡¨å éï¼æ¯ä¸ºäºè§£å³å®¢æ·ç«¯å¹¶å访é®çå²çªé®é¢
éç±»å:æ ¹æ®å¯¹æ°æ®ç访é®ç±»åå é
读éï¼åç§°ä¸ºå ±äº«éï¼å¯¹æ°æ®åæ¥è¯¢select 访é®
  å äºè¯»é表ï¼å 许å¤ä¸ªè®¿é®åæ¶æ¥è¯¢ä¸å¼ ã
 åéï¼å称为æå®éæäºæ¥éï¼å¯¹æ°æ®åå访é®ï¼å访é®é常æå®çæ¯Â insert | delete | update ï¼å äºåéç表ï¼åä¸æ¶é´åªå 许1个è¿æ¥ååæä½ï¼åç»ç读ååé½å¾çå¾ ï¼çå¾ åééæ¾åï¼æå 许åç»ç读æå访é®ã
 éç²åº¦:æçå°±æ¯ç»è¡¨å éçèå´
è¡çº§éï¼Â ä» ä» å¯¹è¢«è®¿é®çè¡åå«å éï¼æ²¡æ被访é®çè¡ä¸å é
表级éï¼Â åªè¦æ¯å¯¹è¡¨å访é®ï¼å°±ä¼ææ´å¼ 表å é(ä¸ç®¡è®¿é®çæ¯1è¡Â è¿æ¯æ´å¤è¡)
 å éæ¼ç¤ºï¼
mysql> show create table db10.b \G
*************************** 1. row ***************************
Table: b
Create Table: CREATE TABLE `b` (
`name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
第ä¸è¿æ¥ ç»è¡¨å é
mysql> lock tables db10.b write;
Query OK, 0 rows affected (0.00 sec)
第äºè¿æ¥ 对å éç表å访é®ï¼å éæé´è®¿é®è¦çå¾
mysql> insert into db10.b values("aa"); çå¾
MySQLæå¡å¤ç
第ä¸è¿æ¥ 解é
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
第äºè¿æ¥ æå
¥å½ä»¤æ§è¡å®æ¯
mysql> insert into db10.b values("aa");
Query OK, 1 row affected (38.17 sec)
mysql> select * from db10.b;
+------+
| name |
+------+
| aa |
+------+
1 row in set (0.00 sec)
äºå¡ç¹æ§
ä»ä¹æ¯äºå¡ï¼
æçæ¯ä¸ç»ä¸å¯åå²çSQLæä½ã
使ç¨Innodb åå¨å¼æç表ææ¯æäºå¡ã
äºå¡ç¨æ¥ç®¡ç对æ°æ®ç insert,update,delete æä½
äºå¡çç¹æ§ç®ç§°ACID ï¼è¡¨çåå¨å¼æå¿ é¡»æ¯innodb ææäºå¡çç¹æ§ï¼
Atomic ï¼ååæ§
ä¸ä¸ªäºå¡ï¼transactionï¼ä¸çæææä½ï¼è¦ä¹å ¨é¨å®æï¼è¦ä¹å ¨é¨ä¸å®æã
Consistency ï¼Â ä¸è´æ§
å¨äºå¡å¼å§ä¹ååäºå¡ç»æ以åï¼æ°æ®åºçå®æ´æ§ä¸ä¼è¢«ç ´åã
 æ§è¡sqlå½ä»¤æ¶ï¼æ²å车å 称为äºå¡å¼å§ä¹å
   æ§è¡sqlå½ä»¤æ¶ï¼æ²å车å 称为äºå¡ç»æ以å
Â
Isolation ï¼é离æ§
æ°æ®åºå 许å¤ä¸ªå¹¶åäºå¡åæ¶å¯¹å ¶æ°æ®è¿è¡è¯»ååä¿®æ¹èäºä¸å½±åã
MySQLæå¡æ¯æ¯æå¤å¹¶è¿æ¥çæå¡ï¼åä¸æ¶å»å¯æ¯åæ¶æ¥æ¶å¤ä¸ªå®¢æ·ç«¯ç访é®ï¼
å¦æ访é®çæ¯innodbåå¨å¼æç表ï¼å½¼æ¤ä¸ç¥éæä½çæ¯å1å¼ è¡¨
Durability ï¼æä¹ æ§
äºå¡å¤çç»æåï¼å¯¹æ°æ®çä¿®æ¹å°±æ¯æ°¸ä¹ çï¼å³ä¾¿ç³»ç»æ éä¹ä¸ä¼ä¸¢å¤±ã
æ§è¡å车åï¼äºå¡å°±ç»æãæ°æ®ä¼æ°¸ä¹ ææã
 äºå¡ç¹æ§æ¼ç¤ºï¼
第1个è¿æ¥ï¼
#å建é¶è¡è¡¨ 并åå¨è®°å½
create table tarena.bank(
id int primary key,
name varchar(20),
balance int
)engine=innodb;
insert into tarena.bank values(1,"jim",10000),(2,"tom",20000);
select * from tarena.bank;
#å
³éèªå¨æ交åè½
set autocommit=0;
show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
#对表ä¸çæ°æ®åä¿®æ¹
update tarena.bank set balance=balance-800 where id = 1;
update tarena.bank set balance=balance+800 where id = 2;
mysql> select * from tarena.bank;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | jim | 9200 |
| 2 | tom | 20800 |
+----+------+---------+
2 rows in set (0.00 sec)
æå¼æ°ç»ç«¯è¿æ¥æ°æ®åºæå¡è®¿é®bank 表
çä¸å°æ°æ®è¢«ä¿®æ¹äº ï¼ å 为 第ä¸ä¸ªè¿æ¥ä¿®æ¹æ°æ® 并没ææ§è¡æ交
mysql> select * from tarena.bank;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | jim | 10000 |
| 2 | tom | 20000 |
+----+------+---------+
2 rows in set (0.00 sec)
åå°ç¬¬1个ç»ç«¯
#åæ»ä¿®æ¹
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
#æ¥çæ°æ®
mysql> select * from tarena.bank;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | jim | 10000 |
| 2 | tom | 20000 |
+----+------+---------+
2 rows in set (0.00 sec)
#æ§è¡äºcommit ä¹åæ æ³åæ» æ°æ®æ°¸ä¹
çæ
mysql> delete from tarena.bank where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.07 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tarena.bank;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | jim | 10000 |
+----+------+---------+
1 row in set (0.00 sec)
mysql>
äºå¡é离级å«
äºå¡é离解å³çé®é¢ï¼è读  ãä¸å¯éå¤è¯»Â ã 幻读Â
è读ï¼Â 读å°äºå ¶ä»äºå¡æªæ交çæ°æ®ï¼è¯»å°çæ°æ®å¹¶ä¸ä¸å®æ¯æç»åå¨å°æ°æ®åºéçæ°æ®ã
å¯éå¤è¯»ï¼å¯éå¤è¯»æçæ¯å¨ä¸ä¸ªäºå¡å ï¼æå¼å§è¯»å°çæ°æ®åäºå¡ç»æåçä»»ææ¶å»è¯»å°çåä¸æ¹æ°æ®é½æ¯ä¸è´çã
ä¸å¯éå¤è¯»ï¼ä¸ä¸ªäºå¡å å读ååä¸æ¡è®°å½ï¼èäºå¡å¨ä¸¤æ¬¡è¯»åä¹é´è¯¥æ°æ®è¢«å ¶å®äºå¡æä¿®æ¹ï¼å两次读åçæ°æ®ä¸åï¼è¿ç§å°±æ¯ä¸å¯éå¤è¯»ã
幻读ï¼ä¸ä¸ªäºå¡æç¸åçæ¥è¯¢æ¡ä»¶éæ°è¯»å以åæ£ç´¢è¿çæ°æ®ï¼å´åç°å ¶ä»äºå¡æå ¥äºæ»¡è¶³å ¶æ¥è¯¢æ¡ä»¶çæ°æ°æ®ï¼è¿ç§ç°è±¡å°±ç§°ä¸ºå¹»è¯»ã
 äºå¡é离级å«
读æªæ交(Read  Uncommitted)ï¼Â æä½çé离级å«,å 许读åå°æªæ交çæ°æ®åæ´;å¯è½ä¼å¯¼è´è读ã幻读ãä¸å¯éå¤å¯¹ã
读æ交(Read  Committed)ï¼å 许并åäºå¡è¯»åå·²ç»æ交çæ°æ®ï¼å¯ä»¥é»æ¢è读ï¼ä½å¹»è¯»æä¸å¯éå¤è¯»ä»æå¯è½åçã
å¯éå¤è¯»(Repeatable Read)ï¼å¯¹åä¸å段çå¤æ¬¡è¯»åç»æé½æ¯ä¸è´ç;é¤éæ°æ®æ¯è¢«æ¬èº«äºå¡èªå·±æä¿®æ¹ï¼å¯ä»¥é»æ¢è读åä¸å¯éå¤è¯»ï¼ä½å¹»è¯»ä»æå¯è½åçã
åºåå(Serializable)ï¼æé«çé离级å«,å®å ¨æä»ACIDçé离级å«ãææçäºå¡ä¾æ¬¡é个æ§è¡ï¼äºå¡ä¹é´å®å ¨ä¸å¯è½äº§çå¹²æ°ã该级å«å¯ä»¥é²æ¢è读åä¸å¯éå¤è¯»ï¼å幻读ã
 é离级å«æ¼ç¤º
第ä¸ä¸ªè¿æ¥:
1Â å ³éèªå¨æ交åè½Â set  autocommit=0;
 2 对innodbåå¨å¼æç表åä¿®æ¹Â update  tarena.bank set balance=20000 where id=1; Â
 3 æ¥çèªå·±çä¿®æ¹Â  select  * from  tarena.bank;
       ï¼æé 没ææ§è¡æ交å½ä»¤ï¼
 第2个è¿æ¥:
1 æ¥çå½åçäºå¡éç¦»çº§å« é»è®¤æ¯å¯éå¤è¯»
select @@tx_isolation;
2 æ§è¡æ¥çè¡¨è®°å½ åç°çä¸å°å¯¹æ¹çä¿®æ¹
select * from tarena.bank;
3 æäºå¡é离级å«ä¿®æ¹ä¸ºè¯»æªæ交
set session transaction isolation level read uncommitted ;
4 æ§è¡æ¥çè¡¨è®°å½ åç°å¯ä»¥çå°å¯¹æ¹çä¿®æ¹
select * from tarena.bank;