當前位置:首頁 » 操作系統 » xtrabackup源碼安裝

xtrabackup源碼安裝

發布時間: 2023-02-01 01:52:51

1. mysql pxc程序要做哪些修改

PXC簡介
Percona XtraDB Cluster(簡稱PXC集群)提供了MySQL高可用的一種實現方法。
1.集群是有節點組成的,推薦配置至少3個節點,但是也可以運行在2個節點上。
2.每個節點都是普通的mysql/percona伺服器,可以將現有的資料庫伺服器組成集群,反之,也可以將集群拆分成單獨的伺服器。
3.每個節點都包含完整的數據副本。
PXC集群主要由兩部分組成:Percona Server with XtraDB和Write Set Replication patches(使用了Galera library,一個通用的用於事務型應用的同步、多主復制插件)。
PXC特性:
1,同步復制,事務要麼在所有節點提交或不提交。
2,多主復制,可以在任意節點進行寫操作。
3,在從伺服器上並行應用事件,真正意義上的並行復制。
4,節點自動配置,數據一致性,不再是非同步復制。
PXC劣勢:
1、 當前版本(5.6.20)的復制只支持InnoDB引擎,其他存儲引擎的更改不復制。然而,DDL(Data Definition Language) 語句在statement級別被復制,並且,對mysql.*表的更改會基於此被復制。例如CREATE USER...語句會被復制,但是 INSERT INTO mysql.user...語句則不會。(也可以通過wsrep_replicate_myisam參數開啟myisam引擎的復制,但這是一個實驗性的參數)。
2、PXC集群一致性控制機制,事有可能被終止,原因如下:集群允許在兩個節點上同時執行操作同一行的兩個事務,但是只有一個能執行成功,另一個會被終止,集群會給被終止的客戶端返回死鎖錯誤(Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)).
3、寫入效率取決於節點中最弱的一台,因為PXC集群採用的是強一致性原則,一個更改操作在所有節點都成功才算執行成功。
原理描述
分布式系統的CAP理論:
C 一致性,所有的節點數據一致
A 可用性,一個或者多個節點失效,不影響服務請求P 分區容忍性,節點間的連接失效,仍然可以處理請求任何一個分布式系統,需要滿足這三個中的兩個安裝部署
環境描述
三個node節點
node #1
hostname: percona1
IP: 192.168.100.7
node #2
hostname: percona2
IP: 192.168.100.8
node #3
hostname: percona3
IP: 192.168.100.9
基礎環境包
可以選擇源碼或者yum,在此使用yum安裝。
三個node節點都要執行以下操作。
基礎環境
yum -y groupinstall Base Compatibility libraries Debugging Tools Dial-up Networking suppport Hardware monitoring utilities Performance Tools Development tools組件安裝
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm -yyum install Percona-XtraDB-Cluster-55 -y
資料庫配置
選擇一個node作為名義上的master,咱們以node1為master,以下操作只在node1上執行。
只需要修改mysql的配置文件--/etc/my.cnf
說明:這里的IP地址是內網地址。
[root@i-kysyolko ~]# cat /etc/my.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.100.7,192.168.100.8,192.168.100.9# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW
# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=192.168.100.7
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_centos_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
[mysqld_safe]
pid-file = /run/mysqld/mysql.pid
syslog
!includedir /etc/my.cnf.d
啟動資料庫
CentOS6:/etc/init.d/mysql bootstrap-pxc
CentOS7:systemctl start [email protected]配置資料庫
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+| Variable_name | Value |
+----------------------------+--------------------------------------+| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |...
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
...
| wsrep_cluster_size | 1 #主要看這里 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
...
| wsrep_ready | ON |
+----------------------------+--------------------------------------+40 rows in set (0.01 sec)
# 資料庫用戶名密碼的設置
mysql@percona1> UPDATE mysql.user SET password=PASSWORD("Passw0rd") where user='root';# 創建、授權、同步賬號
mysql@percona1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cret';mysql@percona1> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';mysql@percona1> FLUSH PRIVILEGES;
node2、node3節點配置
接下來進行其它節點的配置,上面的組件都已經安裝完成。現在直接進行資料庫的配置。
只需要修改第26行,當前node的IP地址。兩個node都是只是修改這個地址即可。
wsrep_node_address=192.168.100.8
[root@i-kysyolko ~]# cat /etc/my.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.100.7,192.168.100.8,192.168.100.9# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW
# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=192.168.100.8
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_centos_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
[mysqld_safe]
pid-file = /run/mysqld/mysql.pid
syslog
!includedir /etc/my.cnf.d
啟動資料庫
CentOS6:/etc/init.d/mysql start
CentOS7:systemctl start mysql.service
說明:
1、除了名義上的master之外,其它的node節點只需要啟動mysql即可。
2、節點的資料庫的登陸和master節點的用戶名密碼一致,自動同步。所以其它的節點資料庫用戶名密碼無須重新設置。
測試
在任意一個node上,進行操作,然後去其它的節點上看是否取得相同的結果

2. 基於Xtrabackup8的Mysql定時全量,增量備份及恢復實戰演練

所有mysql實例皆為MYSQL8版本,使用的Xtrabackup備份組件為xtrabackup8.

生產mysql使用基於percona的分支,相對於原版mysql多了一些性能調教和監控視圖,版本為:percona-server-server-8.0.22,備份相關工具對mysql8的官方版本也是完全兼容的.percona的分支相關信息: https://www.percona.com/software/mysql-database/percona-server

生產環境mysql一共3個實例,使用MGR組成集群以實現靈活的高可用與讀寫分離.並由前置的proxysql進行數據的路由與轉發.

模擬故障為:在生產環境mysql8 的MGR集群完全不可用,有前一天的Xtrabackup全量備份和增量備份,需要從之前的全量和增量備份完整恢復到故障最近的時間點.本次故障恢復的要求是讀取前一天的所有增量和全量數據並恢復.快速重組生產MGR集群.

在這里會用ansible腳本快速搭建3個mysql實例,以模擬生產環境(略過).

backup_func.sh :

策略是每天的0:30做一次全量備份,之後每兩個小時的半點會做一次增量備份.

注: 本次恢復屬於完整的生產環境集群恢復,下面的 獲取備份文件 , 准備備份 , 開始恢復 相關流程,需要在每一台伺服器上執行,實際操作中,如果只需要恢復並查看數據則只做一個點就可以了.

這里取的是前一天的打過包的備份文件,根據備份腳本的規則,每天凌晨的全量備份之前,會自動將前一天的全量備份和增量備份目錄全部打包並以前一天的日期命名:

20210609.tar.gz

將其scp到待恢復的伺服器上並解壓:

解壓後的目錄結構:

確保需要恢復的伺服器有mysql實例,xtrabackup8工具已安裝,由於備份是經過壓縮的,確保qpress也已安裝.

由於備份腳本在備份時使用了 --compress 指令,在恢復備份前,需要先解壓縮備份,

這里將所有增量和全量備份路徑均執行一下解壓縮指令:

在同時存在全量和增量備份需要合並的情況下,准備備份時需要帶上 --apply-log-only 參數,但是要注意在准備最後一個增量備份的時候,不需要加該參數.

以上操作會將所有的增量備份合並到全量備份中.

根據各自安裝時指定的相關路徑去刪除數據.(data,binglog,logs,undolog),一般在my.cnf中有指定

如果my.cnf不是在默認路徑(/etc/my.cnf),需要指定一下mysql配置文件的路徑: --defaults-file=${DB_CONF}

至此,備份數據在單節點上的恢復已經完成了.

查看下最後一份增量備份里才會有的一些數據

先確保同樣的mysql恢復操作分別在三台伺服器上執行完成.(如果在新建伺服器上恢復MGR集群,一定要檢查my.cnf中MGR集群相關配置,比如 loose-group_replication_local_address , loose-group_replication_group_seeds , loose-group_replication_start_on_boot )

master節點啟動:
MGR的三台節點的權重實際上是一樣的,選擇其中的一台做master即可.

mster節點啟動完成後,再分別在兩台slave節點啟動MGR:

由於3台mysql實例的數據是一樣的,節點間狀態同步迅速就OK了.

至此,3台mysql的MGR狀態均為 online ,整個集群啟動完成,全演練流程結束.

3. MySQL 常用備份工具流程解析

下面我們就看一下常見的備份工具,以及目前最流行的 Percona XtraBackup 的備份流程。

MySQL 常見的備份工具主要分為三種:

這里先說一下 binlog 備份,它只是把 binlog 又復制了一份,並且需要在邏輯備份或者物理備份的基礎上才能進行數據恢復,無法單獨進行數據恢復。

mysqlmp 備份出的文件就是 sql 文件,其核心就是對每個表執行 select ,然後轉化成相應的 insert 語句。mysqlmp 的備份流程大致如下:

從上面可以看出在 mysqlmp 備份期間,備份到某個資料庫時,該資料庫下的表都會處於只讀狀態,無法對表進行任何變更,直到該庫下的表備份完畢,這對於線上環境一般是無法接受的。若是指定了--master-data或者 --mp-slave 則會在備份開始時加全局讀鎖(FLUSH TABLES WITH READ LOCK),直到備份結束。當然我們可以選一個從庫進行備份,這樣就不會影響線上業務。另外使用 mysqlmp 備份還有一個最大的好處,因為備份出來的是 sql 語句,所以它支持跨平台和跨版本的數據遷移或者恢復,這是物理備份無法做到的。

但是也正是因為 mysqlmp 備份出來的是 sql 語句,在使用時要更加註意,否則可能會釀成大禍。例如,使用 mysqlmp 常見的問題有:

所以使用 mysqlmp 時一定要了解各個選項的作用,以及確認備份出來的 sql 文件里會有什麼操作,會對現有數據造成什麼影響。

Mymper 原理與 Mysqlmp 原理類似,最大的區別是引入了多線程備份,每個備份線程備份一部分表,當然並發粒度可以到行級,達到多線程備份的目的。這里不再單獨介紹。

Percona XtraBackup 是 Percona 公司開發的一個用於 MySQL 資料庫物理熱備的備份工具,是基於 InnoDB 的崩潰恢復功能來實現的。它的基本工作原理如下:

Percona XtraBackup 在進行恢復時會應用拷貝的 redo log ,應用已提交的事務,回滾未提交的事物,將資料庫恢復到一致性狀態。因為 Percona XtraBackup 備份出來的是物理文件,所以在使用備份出的文件進行恢復或者遷移時,不會像 mysqlmp 那樣會存在很多問題。

使用 XtraBackup 備份時根據備份參數設置不同,對資料庫的變更會造成不同程度的影響,具體影響會在下文分析。

通過對比發現,XtraBackup 具有對資料庫影響小,且能快速恢復的優點,在日常備份中是首選;mysqlmp 使用相對更加靈活,但是使用是要注意對資料庫原有數據的影響。

備份策略主要有:全量備份和增量備份,再加上 binlog 備份。

目前去哪兒網資料庫備份主要採用 XtraBackup 全量備份 +binlog 備份。資料庫的重要級別不同,全量備份的頻率不同。備份程序主要架構如下:

說明:

Percona XtraBackup 是目前備份 MySQL 使用最廣泛的工具。在備份過程中,資料庫可以進行正常的讀寫或者其他變更操作,但是偶爾也會遇見備份引起的元數據鎖,或提交事務時發現被 binlog lock 阻塞等情況。下面我們就看一下 Percona XtraBackup 的備份流程和加鎖時機。

說明:以下對 Percona XtraBackup 的分析都是基於 2.4.23 的版本,其他版本會略有差別,但是關鍵步驟基本相同。

XtraBackup 在備份開始時,會創建一個後台線程,專門用於拷貝資料庫的 redo log 。首先 XtraBackup 會掃描每組 redo log 的頭部,找出當前的 checkpoint lsn ,然後從該 lsn 後順序拷貝所有的 redo log ,包括後續新產生的 redo log 。該線程會一直持續到將非事務表完全拷貝完成,才會安全退出。備份日誌輸出中會記錄拷貝開始時的 checkpoint lsn 。日誌輸出如下:

在拷貝ibd文件之前,會先掃描資料庫的數據文件目錄,獲取ibdata1,undo tablespaces及所有的ibd文件列表,並會記錄相應的 space id,因為在恢復時需要這些 space id來找到對應 doublewrite buffer里頁面的內容,以及對應的redo log條目。然後開始循環拷貝ibdata1,undo tablespaces及所有的ibd文件。
這里可通過設置--parallel進行多線程備份,提高物理文件的拷貝效率。不設置則默認為1。

在所有ibd文件拷貝完成後,XtraBackup開始備份非ibd文件。這一部分的邏輯比較復雜,因為備份非ibd文件前需要加鎖,具體是否會加鎖主要受到--no-lock 參數設置的影響。

若是設置了--no-lock為TRUE,則不會使用"FLUSH TABLES WITH READ LOCK"去加全局讀鎖,但是若備份過程中對non-InnoDB表執行了DDL或者DML操作, 這會導致備份的不一致,恢復出來的數據就會有問題。所以是不建議將--no-lock為TRUE,默認值是FALSE,也就是在不指定該選項的情況下會在備份非ibd文件前加全局讀鎖。

下面我們結合源碼來看看判斷是否加全局鎖這部分的具體流程邏輯:

流程圖如下:

總結來看:

1)若--no-lock為FALSE(默認值),則先施加全局讀鎖,然後再進行拷貝文件,另外若 --safe-slave-backup 設置為TRUE ,則會在加全局鎖之前關閉SQL_THREAD線程;

2)若--no-lock為TRUE,則不會施加鎖,直接進行拷貝文件。

加鎖的邏輯主要由lock_tables_maybe實現,先看一下lock_tables_maybe源代碼,如下:

lock_tables_maybe 函數簡化處理流程如下:

1)若備份實例上已經加鎖( LOCK TABLES FOR BACKUP / FLUSH TABLES WITH READ LOCK)或者設置lock-ddl-per-table 則直接返回;

2)若支持備份鎖,則執行LOCK TABLES FOR BACKUP;

3)若不支持備份鎖,則執行 FLUSH TABLES WITH READ LOCK。根據相應選項設置,在執行該操作前會判斷是否有執行中的DDL/DML,以及等待超時時間,是否kill 對應的未結束的事務等。

從上文中我們還看到一個參數--safe-slave-backup ,該參數的主要作用是:

若是在從庫執行的備份操作時設置了該參數,可以防止因從庫同步主庫操作,而導致XtraBackup長時間請求不到鎖而造成備份失敗。

若是設置了 --safe-slave-backup 為TRUE,那麼會執行"STOP SLAVE SQL_THREAD",並等待Slave_open_temp_tables 為零才開始拷貝非 ibd 文件,Slave_open_temp_tables 為零說明SQL thread執行的事務都已經完成,這樣就能保證備份的一致性。並且此時也不會有在執行的事務阻塞 XtraBackup 施加全局鎖。

備份完非 ibd 文件後,將會備份 slave 和 binlog 信息。

mysql-bin.000004 2004 6b7bda9f-15f0-11ec-ba14-fa163ea367a4:1-83,9841546e-15f0-11ec-9557-fa163e736db4:1

需要注意,在支持備份鎖的實例上備份,指定了 --slave-info 或--binlog-info 均會先施加 binlog 備份鎖( LOCK BINLOG FOR BACKUP),這會阻塞任何會更改 binlog 位點的操作。

備份完資料庫的所有文件和binlog等相關信息,備份工作就基本完成了,之後主要執行的操作如下:

1)執行"FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS",將所有的redo log刷盤;

2)停止redo log復制線程;

3)釋放全局讀鎖(備份鎖),binlog鎖;

4)開啟SQL_THREAD;

5)拷貝ib_buffer_pool和ib_lru_mp文件;

6)生成配置文件backup-my.cnf;

7)列印備份信息到xtrabackup_info文件,這些信息主要包含備份時使用的參數信息,備份起止時間,binlog位點信息,以及將會回到的lsn點。

下面是xtrabackup_info記錄的部分內容:

加鎖對應的函數是 mdl_lock_tables ,釋放鎖對應的函數是 mdl_unlock_all,主要是執行COMMIT,結束 mdl_lock_tables 中開啟的顯式事務,來釋放MDL鎖。mdl_lock_tables 流程如下:

上面參數--lock-ddl和--lock-ddl-per-table是在 Percona XtraBackup 2.4.8 之後添加的,因為 MySQL 5.7 新增了一個叫做 Sorted Index Builds 的功能,這會導致某些 DDL 操作不記錄重做日誌而導致備份失敗。使用--lock-ddl或--lock-ddl-per-table 就會在備份開始時施加鎖,阻止 DDL 操作。

另外,若備份時指定了--lock-ddl或--lock-ddl-per-table,則在備份非 ibd 文件時就不是再有加鎖操作。

注意:LOCK TABLES FOR BACKUP和LOCK BINLOG FOR BACKUP 語句只有在支持備份鎖的實例上才會執行,Percona Server for MySQL已經在 5.6.16-64.0 版本開始支持這種更加輕量的備份鎖。

Q1: 使用 XtraBackup 備份的文件進行恢復時,恢復到哪個時間點? A1:恢復到執行 LOCK BINLOG FOR BACKUP 或 FLUSH TABLES WITH READ LOCK 的時間點,因為這時任何改變 binlog 位點的操作都會被阻塞,redo log和binlog 是一致的。

Q2: 在開啟 binlog 的情況下,MySQL 的奔潰恢復是同時依賴 binlog 和 redo log 這兩種日誌的,為什麼XtraBackup 不用備份binlog?

A2:因為在備份中有執行LOCK BINLOG FOR BACKUP/FLUSH TABLES WITH READ LOCK,阻止了任何改變binlog位點的操作,這樣只需要根據redo log將有commit log 的事務提交,沒有commit log的事務進行回滾即可。

Q3: 使用Percona XtraBackup備份完成後redo的位點是和binlog是一樣還是比binlog多一些?

A3:通過分析備份流程可以發現備份 binlog 位點信息(加binlog鎖)是發生在停止 redo 拷貝線程前,而釋放鎖是在停止 redo 拷貝線之後,所以 redo log 會多一些。鎖住了 binlog 保證了在該 binlog 位點前已經提交的事務的 redo log 都有 commit log 的信息,未提交的事物也就沒有對應的 commit log 的信息,即便在鎖住 binlog 後有 Innodb 表新的 DML 產生的 redo log ,但是事務無法提交,也就沒有 commit log 的信息的,最後在回放的過程中對沒有 commit log 的事務進行回滾就可以了。

Q4:Percona XtraBackup什麼時候會加鎖,以及影響加鎖時間長度的因素有哪些?

A4:上面進行了分析,加鎖操作只在備份非 ibd 文件時執行,加鎖時長主要和非事務表的數量和大小有關,非事務表的數量越多,體積越大,拷貝文件所用的時間越長,那麼加鎖時間也就越長。也會和 redo log 生成的速度有關,只是 redo log 刷盤受到多個因素的影響,未及時刷盤的 redo log 一般很小。

Q5:Percona XtraBackup 和mysqlmp選擇哪個更好?

A5:通過上面的的解析,若是整個實例備份,首先選擇 Percona XtraBackup ,因為對資料庫的影響最小。若只是備份某個庫表,這個就要視數據量而定,若數據量不大可以使用 mysqlmp 。注意,對資料庫做備份時最好選擇業務連接最少的從庫,因為備份也會消耗一定的資源,避免影響業務。

4. Xtrabackup 能不能做單庫的備份恢復

大數據量備份與還原,始終是個難點。當MYSQL超10G,用mysqlmp來導出就比較慢了。在這里推薦xtrabackup,這個工具比mysqlmp要快很多。 一、Xtrabackup介紹 1、Xtrabackup是什麼 Xtrabackup是一個對InnoDB做數據備份的工具,支持在線熱備份(備份時不影響數據讀寫),是商業備份工具InnoDB Hotbackup的一個很好的替代品。 Xtrabackup有兩個主要的工具:xtrabackup、innobackupex 1、xtrabackup只能備份InnoDB和XtraDB兩種數據表,而不能備份MyISAM數據表 2、 innobackupex是參考了InnoDB Hotbackup的innoback腳本修改而來的.innobackupex是一個perl腳本封裝,封裝了xtrabackup。主要是為了方便的 同時備份InnoDB和MyISAM引擎的表,但在處理myisam時需要加一個讀鎖。並且加入了一些使用的選項。如slave-info可以記錄備份恢 復後,作為slave需要的一些信息,根據這些信息,可以很方便的利用備份來重做slave。 2、Xtrabackup可以做什麼 : 在線(熱)備份整個庫的InnoDB、 XtraDB表 在xtrabackup的上一次整庫備份基礎上做增量備份(innodb only) 以流的形式產生備份,可以直接保存到遠程機器上(本機硬碟空間不足時很有用) MySQL資料庫本身提供的工具並不支持真正的增量備份,二進制日誌恢復是point-in-time(時間點)的恢復而不是增量備份。 Xtrabackup工具支持對InnoDB存儲引擎的增量備份,工作原理如下: (1)首先完成一個完全備份,並記錄下此時檢查點的LSN(Log Sequence Number)。 (2)在進程增量備份時,比較表空間中每個頁的LSN是否大於上次備份時的LSN,如果是,則備份該頁,同時記錄當前檢查點的LSN。 首 先,在logfile中找到並記錄最後一個checkpoint(「last checkpoint LSN」),然後開始從LSN的位置開始拷貝InnoDB的logfile到xtrabackup_logfile;接著,開始拷貝全部的數據文 件.ibd;在拷貝全部數據文件結束之後,才停止拷貝logfile。 因為logfile裡面記錄全部的數據修改情況,所以,即時在備份過程中數據文件被修改過了,恢復時仍然能夠通過解析xtrabackup_logfile保持數據的一致。 因為innobackupex支持innodb,myisam,所以本文說一下,怎麼使用innobackupex。 二,安裝xtrabackup 1、下載地址 /downloads/XtraBackup/ 2、安裝 根據需求,選擇不同的版本,我選擇的是rpm安裝包,如果報以下錯誤 復制代碼 代碼如下: [root@localhost xtrabackup]# rpm -ivh percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm warning: percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY error: Failed dependencies: perl(Time::HiRes) is needed by percona-xtrabackup-2.2.4-5004.el6.x86_64 解決辦法: 復制代碼 代碼如下: [root@localhost xtrabackup]# yum -y install perl perl-devel lio lio-devel perl-Time-HiRes perl-DBD-MySQL //安裝依賴包 [root@localhost xtrabackup]# rpm -ivh percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm //重新安裝 warning: percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY Preparing... ########################################### [100%] 1:percona-xtrabackup ########################################### [100%]

5. 本機運行的MySQL 資料庫 如何安全的備份/還原

一般是即時備份。做主從。或者是每天增量備份。
本文是在linux下,mysql 4.1.14版本下測試的,經過適當修改可能適合mysql 4.0,5.0及其其他版本.

本文適合於沒有啟動復制功能的mysql,如果啟動了復制,可能不需要採取這種備份策略或者需要修改相關參數.

每個人的備份策略都可能不同,所以請根據實際情況修改,做到舉一反三,不要照搬照抄,可能會造成不必要的損失.

希望你明白這個腳本要干什麼工作!

腳本描述

每7天備份一次所有數據,每天備份binlog,也就是增量備份.

(如果數據少,每天備份一次完整數據即可,可能沒必要做增量備份)

作者對shell腳本不太熟悉,所以很多地方寫的很笨 :)

開啟 bin log

在mysql 4.1版本中,默認只有錯誤日誌,沒有其他日誌.可以通過修改配置打開bin log.方法很多,其中一個是在/etc/my.cnf中的mysqld部分加入:

[mysqld]
log-bin

這個日誌的主要作用是增量備份或者復制(可能還有其他用途).

如果想增量備份,必須打開這個日誌.

對於資料庫操作頻繁的mysql,這個日誌會變得很大,而且可能會有多個.

在資料庫中flush-logs,或者使用mysqladmin,mysqlmp調用flush-logs後並且使用參數delete-master-logs,這些日誌文件會消失,並產生新的日誌文件(開始是空的).

所以如果從來不備份,開啟日誌可能沒有必要.

完整備份的同時可以調用flush-logs,增量備份之前flush-logs,以便備份最新的數據.

完整備份腳本

如果資料庫數據比較多,我們一般是幾天或者一周備份一次數據,以免影響應用運行,如果數據量比較小,那麼一天備份一次也無所謂了.

#!/bin/sh

# mysql data backup script
# by scud
# 2005-10-30
#
# use mysqlmp --help,get more detail.
#

BakDir=/backup/mysql
LogFile=/backup/mysql/mysqlbak.log

DATE=`date +%Y%m%d`

echo " " >> $LogFile
echo " " >> $LogFile
echo "-------------------------------------------" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile
echo "--------------------------" >> $LogFile

cd $BakDir

DumpFile=$DATE.sql
GZDumpFile=$DATE.sql.tgz

mysqlmp --quick --all-databases --flush-logs
--delete-master-logs --lock-all-tables
> $DumpFile

echo "Dump Done" >> $LogFile

tar czvf $GZDumpFile $DumpFile >> $LogFile 2>&1

echo "[$GZDumpFile]Backup Success!" >> $LogFile

rm -f $DumpFile

#delete previous daily backup files:採用增量備份的文件,如果完整備份後,則刪除增量備份的文件.
cd $BakDir/daily

rm -f *

cd $BakDir

echo "Backup Done!"

echo "please Check $BakDir Directory!"

echo " it to your local disk or ftp to somewhere !!!"

ls -al $BakDir
上面的腳本把mysql備份到本地的/backup/mysql目錄,增量備份的文件放在/backup/mysql/daily目錄下.

注意:上面的腳本並沒有把備份後的文件傳送到其他遠程計算機,也沒有刪除幾天前的備份文件:需要用戶增加相關腳本,或者手動操作.

增量備份

增量備份的數據量比較小,但是要在完整備份的基礎上操作,用戶可以在時間和成本上權衡,選擇最有利於自己的方式.

增量備份使用bin log,腳本如下:

#!/bin/sh

#
# mysql binlog backup script
#

/usr/bin/mysqladmin flush-logs

DATADIR=/var/lib/mysql
BAKDIR=/backup/mysql/daily

###如果你做了特殊設置,請修改此處或者修改應用此變數的行:預設取機器名,mysql預設也是取機器名
HOSTNAME=`uname -n`

cd $DATADIR

FILELIST=`cat $HOSTNAME-bin.index`

##計算行數,也就是文件數
COUNTER=0
for file in $FILELIST
do
COUNTER=`expr $COUNTER + 1 `
done

NextNum=0
for file in $FILELIST
do
base=`basename $file`
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $COUNTER ]
then
echo "skip lastest"
else
dest=$BAKDIR/$base
if(test -e $dest)
then
echo "skip exist $base"
else
echo "ing $base"
cp $base $BAKDIR
fi
fi
done

echo "backup mysql binlog ok"
增量備份腳本是備份前flush-logs,mysql會自動把內存中的日誌放到文件里,然後生成一個新的日誌文件,所以我們只需要備份前面的幾個即可,也就是不備份最後一個.
因為從上次備份到本次備份也可能會有多個日誌文件生成,所以要檢測文件,如果已經備份過,就不用備份了.

注:同樣,用戶也需要自己遠程傳送,不過不需要刪除了,完整備份後程序會自動生成.

訪問設置

腳本寫完了,為了能讓腳本運行,還需要設置對應的用戶名和密碼,mysqladmin和mysqlmp都是需要用戶名和密碼的,當然可以寫在腳本中,但是修改起來不太方便,假設我們用系統的root用戶來運行此腳本,那麼我們需要在/root(也就是root用戶的home目錄)創建一個.my.cnf文件,內容如下

[mysqladmin]
password =password
user= root
[mysqlmp]
user=root
password=password
注:設置本文件只有root可讀.(chmod 600 .my.cnf )

此文件說明程序使用mysql的root用戶備份數據,密碼是對應的設置.這樣就不需要在腳本里寫用戶名和密碼了.

自動運行

為了讓備份程序自動運行,我們需要把它加入crontab.

有2種方法,一種是把腳本根據自己的選擇放入到/etc/cron.daily,/etc/cron.weekly這么目錄里.
一種是使用crontab -e放入到root用戶的計劃任務里,例如完整備份每周日凌晨3點運行,日常備份每周一-周六凌晨3點運行.

6. mysql如何備份資料庫

可以用mysqlmp工具

簡單用例說明:

  1. 導入、導出資料庫

    導出: mysqlmp -uroot db1 > db1.sql (注db1為database名)

    導入:mysql -uroot test < db1.sql(注test為database名,將db1中所有的表及數據導入到test資料庫)

  2. 導入、導出表

導出:mysqlmp -uroot db1 tb1 tb2>tables.sql(注db1為database名,tb1 tb2為要導出的表列表,中間用空格隔開)

導入:mysql -uroot test < tables.sql(將db1資料庫中的tb1和tb2表導入到test資料庫)

常見參數:

--all-databases,-A

導出全部資料庫。

mysqlmp-uroot-p--all-databases

--all-tablespaces,-Y

導出全部表空間。

mysqlmp-uroot-p--all-databases--all-tablespaces

--no-tablespaces,-y

不導出任何錶空間信息。

mysqlmp-uroot-p--all-databases--no-tablespaces

--add-drop-database

每個資料庫創建之前添加drop資料庫語句。

mysqlmp-uroot-p--all-databases--add-drop-database

--add-drop-table

每個數據表創建之前添加drop數據表語句。(默認為打開狀態,使用--skip-add-drop-table取消選項)

mysqlmp-uroot-p--all-databases(默認添加drop語句)

mysqlmp-uroot-p--all-databases–skip-add-drop-table(取消drop語句)

--databases,-B

導出幾個資料庫。參數後面所有名字參量都被看作資料庫名。

mysqlmp-uroot-p--databasestestmysql

--no-data,-d

不導出任何數據,只導出資料庫表結構。

mysqlmp-uroot-p--host=localhost--all-databases--no-data

--host,-h

需要導出的主機信息

mysqlmp-uroot-p--host=localhost--all-databases

--password,-p

連接資料庫密碼

--port,-P

連接資料庫埠號

--set-charset

添加'SETNAMESdefault_character_set'到輸出文件。默認為打開狀態,使用--skip-set-charset關閉選項。

mysqlmp-uroot-p--host=localhost--all-databases

mysqlmp-uroot-p--host=localhost--all-databases--skip-set-charset

--tables

覆蓋--databases(-B)參數,指定需要導出的表名。

mysqlmp-uroot-p--host=localhost--databasestest--tablestest

--user,-u

指定連接的用戶名。

詳見網路:mysqlmp

http://ke..com/link?url=XuLpf2TAU9ieoA3_

Mysqlmp參數大全(參數來源於mysql5.5.19源碼)

http://hi..com/ququ_s/item/e45e35e204193af62b09a43d

7. pgsql的主鍵存儲方式

PostgreSQL的穩定性極強,Innodb等索引在崩潰,斷電之類的災難場景下 抗擊打能力有了長足進步,然而很多 MqSQL用戶 都遇到過 Server級的資料庫丟失的場景 -- MySQL系統庫是 MyISAM,相比之下,PG資料庫這方面要更好一些。

任何系統都有它的性能極限,在高並發讀寫,負載逼近極限下,PG的性能指標仍可以位置雙曲線甚至對數曲線,到 頂峰之後不在下降,而MySQL明顯出現一個波峰後下滑(5.5版本 之後,在企業級版本中有個插件可以改善很多,不過需要付費)。

PG多年來在 GIS(地理信息)領域處於優勢地位,因為它有豐富的幾何類型,PG有大量字典,數組,bitmap等數據類型,相比之下 MySQL就差很多, Instagram就是因為 PG的空間資料庫 擴展 POSTGIS遠遠強於 MySQL的 my spatial 而採用 PgSQL的。

PG的「無鎖定」特性非常突出,甚至包括 vacuum這樣的整理數據空間的操作,這個和PGSQL的MVCC實現有關系。

PG可以使用函數 和 條件索引,這使得 PG資料庫的調優非常靈活, MySQL就沒有這個功能,條件索引在 web應用中 很重要。

PG有極其強悍的 SQL編程能力(9.x 圖靈完備,支持遞歸!),有非常豐富的統計函數和統計語法支持,比如分析函數(Oracle的叫法,PG里叫Window函數),還可以用多種語言來寫存儲過程,對於 R的支持也很好。這一點MySQL就差很多,很多分析功能都不支持,騰訊內部的存儲主要是 MySQL,但是數據分析主要是 Hadoop+ PgSQL。

PG的有多種集群架構可以選擇,plproxy可以之hi語句級的鏡像或分片,slony可以進行欄位級的同步配置,standby 可以構建 WAL文件級或流式的讀寫分離集群,同步頻率和集群策略調整方便。

一般關系型資料庫字元串有長度限制 8k 左右,無限長 TEXT類型的功能受限,只能作為外部大數據訪問。而 PG 的 TEXT 類型 可以直接訪問且無長度限制, SQL語法內置 正則表達式,可以索引,還可以全文檢索,或使用 xml xpath。用 PG的話,文檔資料庫都可以省了。

PgSQL對於 numa 架構的支持比 MySQL強一些,比 MySQL對於讀的性能更好一些, PgSQL提交可以完全非同步提交,而 MySQL的內存表不夠實用(因為表鎖的原因)。

pgsql除了存儲正常的數據類型外,還支持存儲
array,不管是一維數組還是多維數組均支持。
json和jsonb,相比使用 text存儲要高效很多。
json和 jsonb在更高的層面上看起來幾乎是一樣的,但是存儲實現上是不同的。
json存儲完的文本,json列會每次都解析存儲的值,它不支持索引,但 可以為創建表達式索引。
jsonb存儲的二進制格式,避免了重新解析數據結構。它支持索引,這意味著 可以不使用指定索引就能查詢任何路徑。
當我們比較寫入數據速度時,由於數據存儲 的方式的原因,jsonb會比 json 稍微的慢一點。json列會每次都 解析存儲的值,這意味著鍵的順序要和輸入的 時候一樣。但是 jsonb不同,以二進制格式存儲且不保證鍵的順序。因此如果有軟體需要依賴鍵的順序,jsonb可能不是最佳選擇。使用 jsonb的優勢還在於可以輕易的整合關系型數據和非關系型 數據 ,PostgreSQL對於 mongodb這類資料庫是一個不小的威脅,畢竟如果一個表中只有一列數據的類型是半結構化的,沒有必要為了遷就它而整個表的設計都採用 schemaless的結構。

1. CPU限制
PGSQL
沒有CPU核心數限制,有多少CPU核就用多少

MySQL
能用128核CPU,超過128核用不上
2. 配置文件參數
PGSQL
一共有255個參數,用到的大概是80個,參數比較穩定,用上個大版本配置文件也可以啟動當前大版本資料庫

MySQL
一共有707個參數,用到的大概是180個,參數不斷增加,就算小版本也會增加參數,大版本之間會有部分參數不兼容情況
3. 第三方工具依賴情況
PGSQL
只有高可用集群需要依靠第三方中間件,例如:patroni+etcd、repmgr

MySQL
大部分操作都要依靠percona公司的第三方工具(percona-toolkit,XtraBackup),工具命令太多,學習成本高,高可用集群也需要第三方中間件,官方MGR集群還沒成熟
4. 高可用主從復制底層原理
PGSQL
物理流復制,屬於物理復制,跟SQL Server鏡像/AlwaysOn一樣,嚴格一致,沒有任何可能導致不一致,性能和可靠性上,物理復制完勝邏輯復制,維護簡單

MySQL
主從復制,屬於邏輯復制,(sql_log_bin、binlog_format等參數設置不正確都會導致主從不一致)
大事務並行復制效率低,對於重要業務,需要依賴 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比較和修復主從一致
主從復制出錯嚴重時候需要重搭主從
MySQL的邏輯復制並不阻止兩個不一致的資料庫建立復制關系
5. 從庫只讀狀態
PGSQL
系統自動設置從庫默認只讀,不需要人工介入,維護簡單

MySQL
從庫需要手動設置參數super_read_only=on,讓從庫設置為只讀,super_read_only參數有bug,鏈接:https://jiahao..com/s?id=1636644783594388753&wfr=spider&for=pc
6. 版本分支
PGSQL
只有社區版,沒有其他任何分支版本,PGSQL官方統一開發,統一維護,社區版有所有功能,不像SQL Server和MySQL有標准版、企業版、經典版、社區版、開發版、web版之分
國內外還有一些基於PGSQL做二次開發的資料庫廠商,例如:Enterprise DB、瀚高資料庫等等,當然這些只是二次開發並不算獨立分支

MySQL
由於歷史原因,分裂為三個分支版本,MariaDB分支、Percona分支 、Oracle官方分支,發展到目前為止各個分支基本互相不兼容
Oracle官方分支還有版本之分,分為標准版、企業版、經典版、社區版
7. SQL特性支持
PGSQL
SQL特性支持情況支持94種,SQL語法支持最完善,例如:支持公用表表達式(WITH查詢)

MySQL
SQL特性支持情況支持36種,SQL語法支持比較弱,例如:不支持公用表表達式(WITH查詢)

關於SQL特性支持情況的對比,可以參考:http://www.sql-workbench.net/dbms_comparison.html
8. 主從復制安全性
PGSQL
同步流復制、強同步(remote apply)、高安全,不會丟數據
PGSQL同步流復制:所有從庫宕機,主庫會罷工,主庫無法自動切換為非同步流復制(非同步模式),需要通過增加從庫數量來解決,一般生產環境至少有兩個從庫
手動解決:在PG主庫修改參數synchronous_standby_names ='',並執行命令: pgctl reload ,把主庫切換為非同步模式
主從數據完全一致是高可用切換的第一前提,所以PGSQL選擇主庫罷工也是可以理解

MySQL
增強半同步復制 ,mysql5.7版本增強半同步才能保證主從復制時候不丟數據
mysql5.7半同步復制相關參數:
參數rpl_semi_sync_master_wait_for_slave_count 等待至少多少個從庫接收到binlog,主庫才提交事務,一般設置為1,性能最高
參數rpl_semi_sync_master_timeout 等待多少毫秒,從庫無回應自動切換為非同步模式,一般設置為無限大,不讓主庫自動切換為非同步模式
所有從庫宕機,主庫會罷工,因為無法收到任何從庫的應答包
手動解決:在MySQL主庫修改參數rpl_semi_sync_master_wait_for_slave_count=0
9. 多欄位統計信息
PGSQL
支持多欄位統計信息

MySQL
不支持多欄位統計信息
10. 索引類型
PGSQL
多種索引類型(btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap,部分索引,表達式索引)

MySQL
btree 索引,全文索引(低效),表達式索引(需要建虛擬列),hash 索引只在內存表
11. 物理表連接演算法
PGSQL
支持 nested-loop join 、hash join 、merge join

MySQL
只支持 nested-loop join
12. 子查詢和視圖性能
PGSQL
子查詢,視圖優化,性能比較高

MySQL
視圖謂詞條件下推限制多,子查詢上拉限制多
13. 執行計劃即時編譯
PGSQL
支持 JIT 執行計劃即時編譯,使用LLVM編譯器

MySQL
不支持執行計劃即時編譯
14. 並行查詢
PGSQL
並行查詢(多種並行查詢優化方法),並行查詢一般多見於商業資料庫,是重量級功能

MySQL
有限,只支持主鍵並行查詢
15. 物化視圖
PGSQL
支持物化視圖

MySQL
不支持物化視圖
16. 插件功能
PGSQL
支持插件功能,可以豐富PGSQL的功能,GIS地理插件,時序資料庫插件, 向量化執行插件等等

MySQL
不支持插件功能
17. check約束
PGSQL
支持check約束

MySQL
不支持check約束,可以寫check約束,但存儲引擎會忽略它的作用,因此check約束並不起作用(mariadb 支持)
18. gpu 加速SQL
PGSQL
可以使用gpu 加速SQL的執行速度

MySQL
不支持gpu 加速SQL 的執行速度
19. 數據類型
PGSQL
數據類型豐富,如 ltree,hstore,數組類型,ip類型,text類型,有了text類型不再需要varchar,text類型欄位最大存儲1GB

MySQL
數據類型不夠豐富
20. 跨庫查詢
PGSQL
不支持跨庫查詢,這個跟Oracle 12C以前一樣

MySQL
可以跨庫查詢
21. 備份還原
PGSQL
備份還原非常簡單,時點還原操作比SQL Server還要簡單,完整備份+wal歸檔備份(增量)
假如有一個三節點的PGSQL主從集群,可以隨便在其中一個節點做完整備份和wal歸檔備份

MySQL
備份還原相對不太簡單,完整備份+binlog備份(增量)
完整備份需要percona的XtraBackup工具做物理備份,MySQL本身不支持物理備份
時點還原操作步驟繁瑣復雜
22. 性能視圖
PGSQL
需要安裝pg_stat_statements插件,pg_stat_statements插件提供了豐富的性能視圖:如:等待事件,系統統計信息等
不好的地方是,安裝插件需要重啟資料庫,並且需要收集性能信息的資料庫需要執行一個命令:create extension pg_stat_statements命令
否則不會收集任何性能信息,比較麻煩

MySQL
自帶PS庫,默認很多功能沒有打開,而且打開PS庫的性能視圖功能對性能有影響(如:內存佔用導致OOM bug)
23. 安裝方式
PGSQL
有各個平台的包rpm包,deb包等等,相比MySQL缺少了二進制包,一般用源碼編譯安裝,安裝時間會長一些,執行命令多一些

MySQL
有各個平台的包rpm包,deb包等等,源碼編譯安裝、二進制包安裝,一般用二進制包安裝,方便快捷
24. DDL操作
PGSQL
加欄位、可變長欄位類型長度改大不會鎖表,所有的DDL操作都不需要藉助第三方工具,並且跟商業資料庫一樣,DDL操作可以回滾,保證事務一致性

MySQL
由於大部分DDL操作都會鎖表,例如加欄位、可變長欄位類型長度改大,所以需要藉助percona-toolkit裡面的pt-online-schema-change工具去完成操作
將影響減少到最低,特別是對大表進行DDL操作
DDL操作不能回滾
25. 大版本發布速度
PGSQL
PGSQL每年一個大版本發布,大版本發布的第二年就可以上生產環境,版本迭代速度很快
PGSQL 9.6正式版推出時間:2016年
PGSQL 10 正式版推出時間:2017年
PGSQL 11 正式版推出時間:2018年
PGSQL 12 正式版推出時間:2019年

MySQL
MySQL的大版本發布一般是2年~3年,一般大版本發布後的第二年才可以上生產環境,避免有坑,版本發布速度比較慢
MySQL5.5正式版推出時間:2010年
MySQL5.6正式版推出時間:2013年
MySQL5.7正式版推出時間:2015年
MySQL8.0正式版推出時間:2018年
26. returning語法
PGSQL
支持returning語法,returning clause 支持 DML 返回 Resultset,減少一次 Client <-> DB Server 交互

MySQL
不支持returning語法
27. 內部架構
PGSQL
多進程架構,並發連接數不能太多,跟Oracle一樣,既然跟Oracle一樣,那麼很多優化方法也是相通的,例如:開啟大頁內存

MySQL
多線程架構,雖然多線程架構,但是官方有限制連接數,原因是系統的並發度是有限的,線程數太多,反而系統的處理能力下降,隨著連接數上升,反而性能下降
一般同時只能處理200 ~300個資料庫連接
28. 聚集索引
PGSQL
不支持聚集索引,PGSQL本身的MVCC的實現機制所導致

MySQL
支持聚集索引
29. 空閑事務終結功能
PGSQL
通過設置 idle_in_transaction_session_timeout 參數來終止空閑事務,比如:應用代碼中忘記關閉已開啟的事務,PGSQL會自動查殺這種類型的會話事務

MySQL
不支持終止空閑事務功能
30. 應付超大數據量
PGSQL
不能應付超大數據量,由於PGSQL本身的MVCC設計問題,需要垃圾回收,只能期待後面的大版本做優化

MySQL
不能應付超大數據量,MySQL自身架構的問題
31. 分布式演進
PGSQL
HTAP資料庫:cockroachDB、騰訊Tbase
分片集群: Postgres-XC、Postgres-XL

MySQL
HTAP資料庫:TiDB
分片集群: 各種各樣的中間件,不一一列舉
32. 資料庫的文件名和命名規律
PGSQL
PGSQL在這方面做的比較不好,DBA不能在操作系統層面(停庫狀態下)看清楚資料庫的文件名和命名規律,文件的數量,文件的大小
一旦操作系統發生文件丟失或硬碟損壞,非常不利於恢復,因為連名字都不知道
PGSQL表數據物理文件的命名/存放規律是: 在一個表空間下面,如果沒有建表空間默認在默認表空間也就是base文件夾下,例如:/data/base/16454/3599
base:默認表空間pg_default所在的物理文件夾
16454:表所在資料庫的oid
3599:就是表對象的oid,當然,一個表的大小超出1GB之後會再生成多個物理文件,還有表的fsm文件和vm文件,所以一個大表實際會有多個物理文件
由於PGSQL的數據文件布局內容太多,大家可以查閱相關資料
當然這也不能全怪PGSQL,作為一個DBA,時刻做好資料庫備份和容災才是正道,做介質恢復一般是萬不得已的情況下才會做

MySQL
資料庫名就是文件夾名,資料庫文件夾下就是表數據文件,但是要注意表名和資料庫名不能有特殊字元或使用中文名,每個表都有對應的frm文件和ibd文件,存儲元數據和表/索引數據,清晰明了,做介質恢復或者表空間傳輸都很方便
33. 許可權設計
PGSQL
PGSQL在許可權設計這塊是比較坑爹,拋開實例許可權和表空間許可權,PGSQL的許可權層次有點像SQL Server,db=》schema=》object
要說許可權,這里要說一下Oracle,用Oracle來類比
在ORACLE 12C之前,實例與資料庫是一對一,也就是說一個實例只能有一個資料庫,不像MySQL和SQL Server一個實例可以有多個資料庫,並且可以隨意跨庫查詢
而PGSQL不能跨庫查詢的原因也是這樣,PGSQL允許建多個資料庫,跟ORACLE類比就是有多個實例(之前說的實例與資料庫是一對一)
一個資料庫相當於一個實例,因為PGSQL允許有多個實例,所以PGSQL單實例不叫一個實例,叫集簇(cluster),集簇這個概念可以查閱PGSQL的相關資料
PGSQL裡面一個實例/資料庫下面的schema相當於資料庫,所以這個schema的概念對應MySQL的database
注意點:正因為是一個資料庫相當於一個實例,PGSQL允許有多個實例/資料庫,所以資料庫之間是互相邏輯隔離的,導致的問題是,不能一次對一個PGSQL集簇下面的所有資料庫做操作
必須要逐個逐個資料庫去操作,例如上面說到的安裝pg_stat_statements插件,如果您需要在PGSQL集簇下面的所有資料庫都做性能收集的話,需要逐個資料庫去執行載入命令
又例如跨庫查詢需要dblink插件或fdw插件,兩個資料庫之間做查詢相當於兩個實例之間做查詢,已經跨越了實例了,所以需要dblink插件或fdw插件,所以道理非常簡單
許可權操作也是一樣逐個資料庫去操作,還有一個就是PGSQL雖然像SQL Server的許可權層次結構db=》schema=》object,但是實際會比SQL Server要復雜一些,還有就是新建的表還要另外授權
在PGSQL裡面,角色和用戶是一樣的,對新手用戶來說有時候會傻傻分不清,也不知道怎麼去用角色,所以PGSQL在許可權設計這一塊確實比較坑爹

MySQL
使用mysql庫下面的5個許可權表去做許可權映射,簡單清晰,唯一問題是缺少許可權角色
user表
db表
host表
tables_priv表
columns_priv表

1. 架構對比
Mysql:多線程
PostgreSql:多進程
多線程架構和多進程架構之間沒有絕對的好壞,例如oracle在unix上是多進程架構,在windows上是多線程架構。

2. 對存儲過程及事務的支持能力
MySql對於無事務的MyISAM表,採用表鎖定,一個長時間運行的查詢很可能會長時間的阻礙,而PostgreSQL不會尊在這種問題。
PostgreSQL支持存儲過程,要比MySql好,具備本地緩存執行計劃的能力。

3. 穩定性及性能
高並發讀寫,負載逼近極限下,PG的性能指標仍可以維持雙曲線甚至對數曲線,到頂峰之後不再下降,而 MySql 明顯出現一個波峰後下滑(5.5版本後Mysql企業版有優化,需要付費)
MySql的InnoDB引擎,可以充分優化利用系統的所有內存,超大內存下PG對內存使用的不那麼充分(需要根據內存情況合理分配)。

4. 高可用
InnoDB的基於回滾實現的 MVCC 機制,對於 PG 新老數據一起放的基於 XID 的 MVCC機制,是占優的。新老數據一起存放,需要定時觸發 VACUUM,會帶來多餘的 IO 和資料庫對象加鎖開銷,引起資料庫整理的並發能力下降。而且 VACUUM 清理不及時,還可能會引發數據膨脹

5. 數據同步方式:
Mysql到現在也是非同步復制,pgsql可以做到同步、非同步、半同步復制。
Mysql同步是基於binlog復制,屬於邏輯復制,類似於oracle golden gate,是基於stream的復制,做到同步很困難,這種方式更加適合非同步復制;
Pgsql的同是基於wal,屬於物理復制,可以做到同步復制。同時,pgsql還提供stream復制。
Mysql的復制可以用多級從庫,但是在9.2之前,PgSql不能用從庫帶從庫。
Pgsql的主從復制屬於物理復制,相對於Mysql基於binlog的邏輯復制,數據的一致性更加可靠,復制性能更高,對主機性能的影響也更小。

6. 許可權控制對比
MySql允許自定義一套不同的數據級、表級和列的許可權,運行指定基於主機的許可權
Mysql的merge表提供了 一個獨特管理多個表的方法。myisampack可以對只讀表進行壓縮,以後仍然可以直接訪問該表中的行。

7. SQL語句支持能力
PG有極其強悍的 SQL 編程能力(9.x 圖靈完備,支持遞歸!),有非常豐富的統計函數和統計語法支持,例如分析函數(Oracle的叫法,PG里叫window函數)
支持用多種語言來寫存儲過程,對於R的支持也很好。這一點上Mysql就差的很遠,很多分析功能都不支持。
PgSql對表名大小寫的處理,只有在Sql語句中,表明加雙引號,才區分大小寫。
在Sql的標准實現上要比Mysql完善,而且功能實現比較嚴謹。
對表連接支持比較完整,優化器的功能比較完整,支持的索引類型很多,復雜查詢能力較強。
Mysql採用索引組織表,這種存儲方式非常適合基於主鍵匹配的查詢、刪改操作,但是對表結果設計存在約束;
Mysql的Join操作的性能非常的差,只支持Nest Join,所以一旦數據量大,性能就非常的差。PostgresSQL除了支持 Nest Join 和 Sort Merge Join,PostgreSQL還支持正則表達式查詢,MySql不支持。

8. 數據類型支持能力
PostgreSQL可以更方便的使用UDF(用戶定義函數)進行擴展。
有豐富的幾何類型,實際上不止集合類型,PG有大量的字典、數組、bitmap等數據類型,因此PG多年來在 GIS 領域處於優勢地位。相比之下Mysql就差很多,instagram就是因為PG的空間數據擴展 PostGIS遠遠強於 MySql的 my spatial 而採用 PgSql的。Mysql中的空間數據類型有4種,分別是 CEOMETRY、POINT、LINESTRING、POLYGON,其空間索引只能在存儲引擎為 MyiSam的表中創建,用SPATIAL關鍵字進行擴展,使得能夠用於創建正規索引類型的語法創建空間索引。創建空間索引的列,必須將其聲明為NOT NULL。不同的存儲親情有差別。MyISAM和InnoDB 都支持 spatial extensions,但差別在於:如果使用MyISAM,可以建立 spatial index,而 InnoDB是不支持的。
pgsql對json支持比較好,還有很逆天的fdw功能,就是把別的資料庫中的表當自己的用。
pgsql的欄位類型支持的多,有很多mysql沒有的類型,但是實際中有時候用到。
一半關系型資料庫的字元串長度8k左右,無限長的 TEXT 類型的功能受限,只能作為外部帶數據訪問。而 PG 的 TEXT 類型可以直接訪問,SQL 語法內置正則表達式,可以索引,還可以全文檢索,或使用 xml xpath。用 PG 的話,文檔資料庫都可以省了。
postgresql 有函數,用於報表、統計很方便
PG支持 R-Trees這樣可擴展的索引類型,可以方便的處理一些特殊數據。
PG可以使用函數和條件所以,使得資料庫的調優非常靈活,mysql就沒有這個功能,條件索引在web應用中很重要。

9. 如可過程容錯能力
大批量數據入庫,PostgreSql要求所有的數據必須完全滿足要求,有一條錯誤,整個數據入庫過程失敗。MySql無此問題。

10. 表組織方式
pgsql用繼承的方式實現分區表,讓分區表的使用不方便且性能差,這點比不上mysql。
pg主表採用堆表存放,MySQL採用索引組織表,能夠支持比MySql更大的數據量。
MySql分區表的實現要優於PG的基於繼承表的分區實現,主要體現在分區個數達到成千上萬後的處理性能差異很大。

11. 開發結構
對於web應用來所,mysql 5.6 的內置 MC API 功能很好用,PgSQL差一些。
PG的「無鎖定」特性非常突出,甚至包括 vacuum 這樣的整理數據空間的操作,這個和 PGSQL的 MVCC 實現有關系。

好文要頂 關注我 收藏該文
茄子777
粉絲 - 0 關注 - 0
+加關注
00
« 上一篇: 多線程中的wait與join
» 下一篇: 負載均衡相關
posted @ 2022-11-02 16:20 茄子777 閱讀(55) 評論(0) 編輯 收藏 舉報
刷新評論刷新頁面返回頂部
登錄後才能查看或發表評論,立即 登錄 或者 逛逛 博客園首頁
【推薦】阿里雲新人特惠,爆款雲伺服器2核4G低至0.46元/天
【推薦】雙十一同價!騰訊雲雲伺服器搶先購,低至4.2元/月
編輯推薦:
· 一個有趣的 nginx HTTP 400 響應問題分析
· 誰說.NET沒有GC調優?只改一行代碼就讓程序不再佔用內存
· 為什麼標准庫的模板變數都是 inline 的
· .net 如何優雅的使用 EFCore
· 在 C# 中使用 Halcon 開發視覺檢測程序
閱讀排行:
· Entity Framework Core 7中高效地進行批量數據插入
· 除了 filter 還有什麼置灰網站的方式?
· 快速繪制流程圖「GitHub 熱點速覽 v.22.47」
· 使用.NET7和C#11打造最快的序列化程序-以MemoryPack為例
· 私藏!資深數據專家SQL效率優化技巧 ⛵

熱點內容
電腦ftp服務如何禁用 發布:2024-03-29 13:24:48 瀏覽:331
驅動精靈驅動解壓 發布:2024-03-29 13:07:49 瀏覽:564
學編程好學嗎 發布:2024-03-29 13:07:34 瀏覽:439
python保存mp3文件 發布:2024-03-29 12:47:10 瀏覽:150
win10怎麼配置jdk8 發布:2024-03-29 12:47:09 瀏覽:535
解壓軟體java 發布:2024-03-29 12:40:32 瀏覽:282
長安cs35壓縮比 發布:2024-03-29 12:39:58 瀏覽:176
java中編譯器默認導入jdk包 發布:2024-03-29 12:23:26 瀏覽:365
中山大學資料庫 發布:2024-03-29 12:20:44 瀏覽:695
創造與魔法哪個腳本不要錢 發布:2024-03-29 12:20:38 瀏覽:441