Skip to main content

Home/ Larvata/ Group items tagged percona

Rss Feed Group items tagged

Filter: All | Bookmarks | Topics Simple Middle
張 旭

How Percona XtraBackup Works - 0 views

www.percona.com/...how_xtrabackup_works.html

mysql database backup percona xtradb

shared by 張 旭 on 30 Nov 19 - No Cached
  • Percona XtraBackup is based on InnoDB‘s crash-recovery functionality.
  • it performs crash recovery on the files to make them a consistent, usable database again
  • InnoDB maintains a redo log, also called the transaction log. This contains a record of every change to InnoDB data.
  • ...14 more annotations...
  • When InnoDB starts, it inspects the data files and the transaction log, and performs two steps. It applies committed transaction log entries to the data files, and it performs an undo operation on any transactions that modified data but did not commit.
  • Percona XtraBackup works by remembering the log sequence number (LSN) when it starts, and then copying away the data files.
  • Percona XtraBackup runs a background process that watches the transaction log files, and copies changes from it.
  • Percona XtraBackup needs to do this continually
  • Percona XtraBackup needs the transaction log records for every change to the data files since it began execution.
  • Percona XtraBackup uses Backup locks where available as a lightweight alternative to FLUSH TABLES WITH READ LOCK.
  • Locking is only done for MyISAM and other non-InnoDB tables after Percona XtraBackup finishes backing up all InnoDB/XtraDB data and logs.
  • xtrabackup tries to avoid backup locks and FLUSH TABLES WITH READ LOCK when the instance contains only InnoDB tables. In this case, xtrabackup obtains binary log coordinates from performance_schema.log_status
  • When backup locks are supported by the server, xtrabackup first copies InnoDB data, runs the LOCK TABLES FOR BACKUP and then copies the MyISAM tables.
  • the STDERR of xtrabackup is not written in any file. You will have to redirect it to a file, e.g., xtrabackup OPTIONS 2> backupout.log
  • During the prepare phase, Percona XtraBackup performs crash recovery against the copied data files, using the copied transaction log file. After this is done, the database is ready to restore and use.
  • the tools enable you to do operations such as streaming and incremental backups with various combinations of copying the data files, copying the log files, and applying the logs to the data.
  • To restore a backup with xtrabackup you can use the --copy-back or --move-back options.
  • you may have to change the files’ ownership to mysql before starting the database server, as they will be owned by the user who created the backup.
  • 張 旭 on 30 Nov 19
     
    "Percona XtraBackup is based on InnoDB's crash-recovery functionality."
張 旭

Connection and Privileges Needed - 0 views

www.percona.com/...privileges.html

mysql database backup percona xtradb

shared by 張 旭 on 02 Dec 19 - No Cached
  • Percona XtraBackup needs to be able to connect to the database server and perform operations on the server and the datadir when creating a backup, when preparing in some scenarios and when restoring it.
  • When xtrabackup is used, there are two actors involved: the user invoking the program - a system user - and the user performing action in the database server - a database user.
  • these are different users in different places, even though they may have the same username.
  • ...1 more annotation...
  • Once connected to the server, in order to perform a backup you will need READ and EXECUTE permissions at a filesystem level in the server’s datadir.
  • 張 旭 on 02 Dec 19
     
    "Percona XtraBackup needs to be able to connect to the database server and perform operations on the server and the datadir when creating a backup, when preparing in some scenarios and when restoring it. "
crazylion lee

Overview of Different MySQL Replication Solutions - Percona Database Performance Blog - 0 views

www.percona.com/...nt-mysql-replication-solutions

database db mysql replication

shared by crazylion lee on 12 May 17 - No Cached
  • crazylion lee on 12 May 17
     
    "In this blog post, I will review some of the MySQL replication concepts that are part of the MySQL environment (and Percona Server for MySQL specifically). I will also try to clarify some of the misconceptions people have about replication. Since I've been working on the Solution Engineering team, I've noticed that - although information is plentiful - replication is often misunderstood or incompletely understood."
張 旭

The Backup Cycle - Full Backups - 0 views

www.percona.com/...full_backup.html

database mysql backup percona xtradb

shared by 張 旭 on 02 Dec 19 - No Cached
  • xtrabackup will not overwrite existing files, it will fail with operating system error 17, file exists.
  • Log copying thread checks the transactional log every second to see if there were any new log records written that need to be copied, but there is a chance that the log copying thread might not be able to keep up with the amount of writes that go to the transactional logs, and will hit an error when the log records are overwritten before they could be read.
  • It is safe to cancel at any time, because xtrabackup does not modify the database.
  • ...15 more annotations...
  • need to prepare it in order to restore it.
  • Data files are not point-in-time consistent until they are prepared, because they were copied at different times as the program ran, and they might have been changed while this was happening.
  • You can run the prepare operation on any machine; it does not need to be on the originating server or the server to which you intend to restore.
  • you simply run xtrabackup with the --prepare option and tell it which directory to prepare,
  • All following prepares will not change the already prepared data files
  • It is not recommended to interrupt xtrabackup process while preparing backup
  • Backup validity is not guaranteed if prepare process was interrupted.
  • If you intend the backup to be the basis for further incremental backups, you should use the --apply-log-only option when preparing the backup, or you will not be able to apply incremental backups to it.
  • Backup needs to be prepared before it can be restored.
  • xtrabackup --copy-back --target-dir=/data/backups/
  • The datadir must be empty before restoring the backup.
  • MySQL server needs to be shut down before restore is performed.
  • You cannot restore to a datadir of a running mysqld instance (except when importing a partial backup).
  • rsync -avrP /data/backup/ /var/lib/mysql/
  • chown -R mysql:mysql /var/lib/mysql
張 旭

Incremental Backup - 0 views

www.percona.com/...incremental_backup.html

mysql database backup percona xtradb

shared by 張 旭 on 02 Dec 19 - No Cached
  • xtrabackup supports incremental backups, which means that they can copy only the data that has changed since the last backup.
  • You can perform many incremental backups between each full backup, so you can set up a backup process such as a full backup once a week and an incremental backup every day, or full backups every day and incremental backups every hour.
  • each InnoDB page contains a log sequence number, or LSN. The LSN is the system version number for the entire database. Each page’s LSN shows how recently it was changed.
  • ...18 more annotations...
  • In full backups, two types of operations are performed to make the database consistent: committed transactions are replayed from the log file against the data files, and uncommitted transactions are rolled back.
  • You should use the --apply-log-only option to prevent the rollback phase.
  • An incremental backup copies each page whose LSN is newer than the previous incremental or full backup’s LSN.
  • Incremental backups do not actually compare the data files to the previous backup’s data files.
  • you can use --incremental-lsn to perform an incremental backup without even having the previous backup, if you know its LSN
  • Incremental backups simply read the pages and compare their LSN to the last backup’s LSN.
  • without a full backup to act as a base, the incremental backups are useless.
  • The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup’s target directory. This file contains a line showing the to_lsn, which is the database’s LSN at the end of the backup.
  • from_lsn is the starting LSN of the backup and for incremental it has to be the same as to_lsn (if it is the last checkpoint) of the previous/base backup.
  • If you do not use the --apply-log-only option to prevent the rollback phase, then your incremental backups will be useless.
  • run --prepare as usual, but prevent the rollback phase
  • If you restore it and start MySQL, InnoDB will detect that the rollback phase was not performed, and it will do that in the background, as it usually does for a crash recovery upon start.
  • xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \ --incremental-dir=/data/backups/inc1
  • The final data is in /data/backups/base, not in the incremental directory.
  • Do not run xtrabackup --prepare with the same incremental backup directory (the value of –incremental-dir) more than once.
  • xtrabackup --prepare --target-dir=/data/backups/base \ --incremental-dir=/data/backups/inc2
  • --apply-log-only should be used when merging all incrementals except the last one.
  • Even if the --apply-log-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.
crazylion lee

pt-online-schema-change - 0 views

www.percona.com/...pt-online-schema-change.html

mysql lock alter

shared by crazylion lee on 03 Oct 15 - No Cached
  • crazylion lee on 03 Oct 15
     
    "pt-online-schema-change - ALTER tables without locking them"
張 旭

Setup ProxySQL for High Availability (not a Single Point of Failure) - Percona Database... - 0 views

www.percona.com/...ility-not-single-point-failure

mysql database cluster mariadb proxy

shared by 張 旭 on 18 Nov 18 - No Cached
  • ProxySQL doesn’t natively support any high availability solution
  • most common solution is setting up ProxySQL as part of a tile architecture, where Application/ProxySQL are deployed together.
    • 張 旭
      張 旭 on 18 Nov 18
       
      直接把 ProxySQL 跟 App 捆綁發佈
  • If we have 400 instances of ProxySQL, we end up keeping our databases busy just performing the checks.
  • ...5 more annotations...
  • Another possible approach is to have two layers of ProxySQL, one close to the application and another in the middle to connect to the database.
  • creates additional complexity in the management of the platform, and it adds network hops.
  • combining existing solutions and existing blocks: KeepAlived + ProxySQl + MySQL.
  • Keepalived implements a set of checkers to dynamically and adaptively maintain and manage load-balanced server pool according to their health.
  • Keepalived implements a set of hooks to the VRRP finite state machine providing low-level and high-speed protocol interactions.
張 旭

ProxySQL Series : Percona Cluster/MariaDB Cluster (Galera) Read-write Split - Mydbops - 0 views

mydbops.wordpress.com/...luster-galera-read-write-split

mysql database cluster mariadb proxy

shared by 張 旭 on 18 Nov 18 - No Cached
  • PXC / MariaDB Clusters really works better with writes on single ode than multi node writes.
  • proxySQL setup for a cluster in Single-writer mode, Which is the most recommended for Cluster to avoid of conflicts of writes and split-Brain scenarios.
  • listening on ports 6032 for proxysql admin interface and 6033 for MySQL interface by default
  • 張 旭 on 18 Nov 18
     
    "PXC / MariaDB Clusters really works better with writes on single ode than multi node writes. "
張 旭

MySQL 到底能不能放到 Docker 里跑？ - 0 views

mp.weixin.qq.com/s

mysql server docker

shared by 張 旭 on 07 Aug 17 - No Cached
  • 忙碌又容易出错的工作其实是无意义的
  • 单机多实例运行 MySQL
  • MySQL 运行的就是个进程而且对 IO 要求比较高
  • ...12 more annotations...
  • Docker 的资源限制用的就是 cgroups
  • Percona：我们的备份、慢日志分析、过载保护等功能都是基于 pt-tools 工具包来实现的。
  • Consul：分布式的服务发现和配置共享软件
  • 容器调度的开源产品主要有 Kubernetes 和 mesos
  • 适合自己现状的需求才是最好的
  • 有机会做到计算调度和存储调度分离的情况下我们可能会转向 Kubernetes 的方案
  • 根据这个需求按照我们的资源筛选规则 (比如主从不能在同一台机器、内存配置不允许超卖等等)，从现有的资源池中匹配出可用资源，然后依次创建主从关系、创建高可用管理、检查集群复制状态、推送集群信息到中间件 (选用了中间件的情况下) 控制中心、最后将以上相关信息都同步到 CMDB。
    • 張 旭
      張 旭 on 07 Aug 17
       
      感覺用 K8S 就不用那麼麻煩了。
  • 每一个工作都是通过服务端发送消息到 agent，然后由 agent 执行对应的脚本，脚本会返回指定格式的执行结果
  • 备份工具我们是用 percona-xtrabackup
  • zabbix 来实现监控告警
  • grafana 是监控画图界的扛把子，功能齐全的度量仪表盘和图形编辑器，经过简单配置就能完成各种监控图形的展示。
  • (MariaDB 不支持写 table，只能写 file)，极大减少了从库复制带来的 IOPS。
張 旭

ProxySQL Experimental Feature: Native ProxySQL Clustering - Percona Database Performanc... - 0 views

www.percona.com/...ntal-feature-native-clustering

mysql proxysql cluster HA database

shared by 張 旭 on 07 Aug 19 - No Cached
  • several ProxySQL instances to communicate with and share configuration updates with each other.
  • 4 tables where you can make changes and propagate the configuration
  • When you make a change like INSERT/DELETE/UPDATE on any of these tables, after running the command LOAD … TO RUNTIME , ProxySQL creates a new checksum of the table’s data and increments the version number in the table runtime_checksums_values
  • ...2 more annotations...
  • all nodes are monitoring and communicating with all the other ProxySQL nodes. When another node detects a change in the checksum and version (both at the same time), each node will get a copy of the table that was modified, make the same changes locally, and apply the new config to RUNTIME to refresh the new config, make it visible to the applications connected and automatically save it to DISK for persistence.
  • a “synchronous cluster” so any changes to these 4 tables on any ProxySQL server will be replicated to all other ProxySQL nodes.
張 旭

Load balancing with ProxySQL - 0 views

www.percona.com/...proxysql.html

proxysql mysql database HA

shared by 張 旭 on 07 Nov 19 - No Cached
  • accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.
張 旭

单表60亿记录等大数据场景的MySQL优化和运维之道 - 快课网 - 0 views

www.cricode.com/3981.html

mysql system performance HA database

shared by 張 旭 on 18 Nov 15 - No Cached
  • 存储引擎使用InnoDB
  • 变长字符串尽量使用varchar varbinary
  • 不在数据库中存储图片、文件等
  • ...34 more annotations...
  • 库名、表名、字段名、索引名使用小写字母，以下划线分割 ，需要见名知意
  • 所有字段均定义为NOT NULL ，除非你真的想存Null
  • 使用TIMESTAMP存储时间
  • 使用DECIMAL存储精确浮点数，用float有的时候会有问题
  • 单个索引字段数不超过5，单表索引数量不超过5，索引设计遵循B+ Tree索引最左前缀匹配原则
  • 建立的索引能覆盖80%主要的查询，不求全，解决问题的主要矛盾
  • 避免冗余索引
  • 索引这个东西是一把双刃剑，在加速读的同时也引入了很多额外的写入和锁，降低写入能力
  • 字段定义为varchar，但传入的值是个int，就会导致全表扫描，要求程序端要做好类型检查
  • 避免使用大表的JOIN，MySQL优化器对join优化策略过于简单
  • UPDATE、DELETE语句不使用LIMIT ，容易造成主从不一致
  • 高危操作检查，Drop前做好数据备份
  • 日志分析，主要是指的MySQL慢日志和错误日志
  • Percona公司根据Facebook OSC思路，用perl重写了一版，就是我们现在用得很多的pt-online-schema-change，软件本身非常成熟，支持目前主流版本
  • 原生主从同步肯定存在着性能和安全性问题
  • Sharding is very complex, so itʼs best not to shard until itʼs obvious that you will actually need to!
  • 有中间层控制拆分逻辑最好，否则拆分过细管理成本会很高
  • 全量binlog备份
  • xtrabackup热备
  • 采用分布式文件系统存储备份
  • 基于库级别的复制，所以如果你只有一个库，使用这个意义不大
  • 半同步复制，从5.5开始支持
  • 半同步通过从库返回ACK这种方式确认从库收到数据
  • Secondsbehindmaster来判断延时不可靠，在网络抖动或者一些特殊参数配置情况下，会造成这个值是0但其实延时很大了。通过heartbeat表插入时间戳这种机制判断延时是更靠谱的
  • Binlog格式，建议都采用row格式，数据一致性更好
  • 成熟开源事务存储引擎，支持ACID，支持事务四个隔离级别，更好的数据安全性，高性能高并发，MVCC，细粒度锁，支持O_DIRECT
  • 数据安全性至关重要，InnoDB完胜
  • 主流使用TokuDB主要是看中了它的高压缩比
  • TokuDB在测试过程中写入稳定性是非常好的
  • 单表容量在InnoDB下1TB+，使用Tokudb的lzma压缩到80GB
  • 独立写程序好一些，与程序解耦方便后期维护
  • 追踪字段值变化可以通过分析row格式binlog好一些
  • 解决了单表过大恢复时间问题，也支持online DDL
  • 物理备份采用xtrabackup热备方案比较好
1 - 14 of 14
Showing 20▼ items per page