xtrabackup源码安装

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效率优化技巧 ⛵