#mysqlbinlog | Explore Tumblr posts and blogs

computingpostcom · 3 years ago

Text

In this guide we shall cover the installation, configuration and usage of MariaDB 10.8 on CentOS 7 / RHEL 7. MariaDB is a ‘drop in’ replacement for MySQL. At the time of its launch, it was 99.9% compatible, and essentially a branch of MySQL. But logically, over time, the two database management systems have diverged and now there are hundreds of incompatibilities between MySQL and MariaDB databases. MariaDB is the default database solution in CentOS 7 / RHEL 7 Linux systems. This guide should only be used for new installations and not for upgrading from an older release of MariaDB server. Also note this guide is applicable for single node database server setups using MariaDB. If you’re interested in a highly available, multi-node deployment, consider using MariaDB Galera Cluster setup. Some of the good features and improvements in MariaDB 10.8 are; InnoDB redo log improvements – autosize innodb_buffer_pool_chunk_size and Improve the redo log for concurrency Lag free ALTER TABLE in replication – ALTER TABLE now gets replicated and starts executing on replicas when it starts executing on the primary, not when it finishes. Auto create partition – You can now use an AUTO keyword to inform the server to create more history partitions automatically JSON Histograms – Histograms in the statistics tables are more precise and stored as JSON, not binary Descending indexes – Individual columns in the index can now be explicitly sorted in the ascending or descending order. Spider Storage Engine Improvements – You can now declare Spider connections using the REMOTE_SERVER, REMOTE_DATABASE, and REMOTE_TABLE attributes and not abuse the COMMENT field for that. mysqlbinlog GTID support – mariadb-binlog (previously mysqlbinlog) now supports both filtering events by GTID ranges through –start-position and –stop-position, and validating a binary log’s ordering of GTIDs through –gtid-strict-mode Improved i18n support on Windows – On Windows 10+ systems, several problems with Unicode input and output in client were fixed Stored Procedures INOUT Parameters – Added support support for IN, OUT and INOUT parameter qualifiers And many Misc. features added as well Install MariaDB 10.8 on CentOS 7 / RHEL 7 The steps provided in this section should help you with the installation, configuration and usage of MariaDB 10.8 on CentOS 7 / RHEL 7 Linux. The installation will fail if you have any other MySQL based database management system on your OS. Ensure the system is clean before you proceed with this guide. Update and reboot your system. sudo yum -y update sudo reboot -f Wait for your system to be rebooted then start the installation using the next step. Step 1) Add MariaDB YUM repository We’ll use official MariaDB YUM repository for RHEL based systems which is added by running the following commands. curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup sudo bash mariadb_repo_setup --mariadb-server-version=10.8 The script automates the process of setting up repository and importing MariaDB GPG keys. [info] Checking for script prerequisites. [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo [info] Adding trusted package signing keys... /etc/pki/rpm-gpg ~ ~ [info] Successfully added trusted package signing keys [info] Cleaning package cache... Loaded plugins: fastestmirror Cleaning repos: base droplet-agent extras mariadb-main mariadb-maxscale mariadb-tools updates Cleaning up list of fastest mirrors List all available repositories on the system $ sudo yum clean all $ sudo yum repolist Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: packages.oit.ncsu.edu * extras: mirror.facebook.net * updates: mirrors.tripadvisor.com repo id repo name status

base/7/x86_64 CentOS-7 - Base 10072 droplet-agent/x86_64 DigitalOcean Droplet Agent 11 extras/7/x86_64 CentOS-7 - Extras 512 mariadb-main MariaDB Server 84 mariadb-maxscale MariaDB MaxScale 4 mariadb-tools MariaDB Tools 16 updates/7/x86_64 CentOS-7 - Updates 4050 repolist: 14749 Step 2) Install MariaDB 10.8 on CentOS 7 / RHEL 7 MariaDB 10.8 packages are available on the repo we just added. Let’s install server and client packages. sudo yum install MariaDB-server MariaDB-client MariaDB-backup Confirm the version of MariaDB server and client packages being installed and proceed if okay. Dependencies Resolved ====================================================================================================================================================================================================== Package Arch Version Repository Size ====================================================================================================================================================================================================== Installing: MariaDB-backup x86_64 10.8.3-1.el7.centos mariadb-main 7.0 M MariaDB-client x86_64 10.8.3-1.el7.centos mariadb-main 15 M MariaDB-compat x86_64 10.8.3-1.el7.centos mariadb-main 2.2 M replacing mariadb-libs.x86_64 1:5.5.68-1.el7 MariaDB-server x86_64 10.8.3-1.el7.centos mariadb-main 26 M Installing for dependencies: MariaDB-common x86_64 10.8.3-1.el7.centos mariadb-main 81 k boost-program-options x86_64 1.53.0-28.el7 base 156 k galera-4 x86_64 26.4.11-1.el7.centos mariadb-main 9.9 M libaio x86_64 0.3.109-13.el7 base 24 k

libpmem x86_64 1.5.1-2.1.el7 base 59 k lsof x86_64 4.87-6.el7 base 331 k pcre2 x86_64 10.23-2.el7 base 201 k perl x86_64 4:5.16.3-299.el7_9 updates 8.0 M perl-Carp noarch 1.26-244.el7 base 19 k perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k perl-DBI x86_64 1.627-4.el7 base 802 k perl-Data-Dumper x86_64 2.145-3.el7 base 47 k perl-Encode x86_64 2.51-7.el7 base 1.5 M perl-Exporter noarch 5.68-3.el7 base 28 k perl-File-Path noarch 2.09-2.el7 base 26 k perl-File-Temp noarch 0.23.01-3.el7 base 56 k perl-Filter x86_64 1.49-3.el7 base 76 k perl-Getopt-Long noarch 2.40-3.el7 base 56 k perl-HTTP-Tiny noarch 0.033-3.el7 base 38 k perl-IO-Compress noarch 2.061-2.el7 base 260 k perl-Net-Daemon noarch 0.48-5.el7 base 51 k perl-PathTools x86_64 3.40-5.el7 base 82 k perl-PlRPC noarch 0.2020-14.el7 base 36 k perl-Pod-Escapes noarch 1:1.

04-299.el7_9 updates 52 k perl-Pod-Perldoc noarch 3.20-4.el7 base 87 k perl-Pod-Simple noarch 1:3.28-4.el7 base 216 k perl-Pod-Usage noarch 1.63-3.el7 base 27 k perl-Scalar-List-Utils x86_64 1.27-248.el7 base 36 k perl-Socket x86_64 2.010-5.el7 base 49 k perl-Storable x86_64 2.45-3.el7 base 77 k perl-Text-ParseWords noarch 3.29-4.el7 base 14 k perl-Time-HiRes x86_64 4:1.9725-3.el7 base 45 k perl-Time-Local noarch 1.2300-2.el7 base 24 k perl-constant noarch 1.27-2.el7 base 19 k perl-libs x86_64 4:5.16.3-299.el7_9 updates 690 k perl-macros x86_64 4:5.16.3-299.el7_9 updates 44 k perl-parent noarch 1:0.225-244.el7 base 12 k perl-podlators noarch 2.5.1-3.el7 base 112 k perl-threads x86_64 1.87-4.el7 base 49 k perl-threads-shared x86_64 1.43-6.el7 base 39 k socat x86_64 1.7.3.2-2.el7 base 290 k Transaction Summary ====================================================================================================================================================================================================== Install 4 Packages (+42 Dependent packages) Total download size: 73 M Is this ok [y/d/N]: y With the rpm command package details can be queried. $ rpm -qi MariaDB-server Name : MariaDB-server Version : 10.8.3 Release : 1.el7.centos Architecture: x86_64 Install Date: Wed Jul 13 22:21:21 2022

Group : Applications/Databases Size : 129149443 License : GPLv2 Signature : DSA/SHA1, Thu May 19 12:55:05 2022, Key ID cbcb082a1bb943db Source RPM : MariaDB-server-10.8.3-1.el7.centos.src.rpm Build Date : Wed May 18 13:26:15 2022 Build Host : centos74-amd64 .... Step 3) Configure MariaDB 10.8 on CentOS 7 / RHEL 7 Start and enable mariadb database service $ sudo systemctl enable --now mariadb Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service. Check service status if it’s indeed running. $ systemctl status mariadb ● mariadb.service - MariaDB 10.8.3 database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled) Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf Active: active (running) since Wed 2022-07-13 22:23:07 UTC; 24s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Process: 8189 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 8163 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS) Process: 8161 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Main PID: 8174 (mariadbd) Status: "Taking your SQL requests now..." CGroup: /system.slice/mariadb.service └─8174 /usr/sbin/mariadbd Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] InnoDB: log sequence number 45505; transaction id 14 Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] Plugin 'FEEDBACK' is disabled. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] Server socket created on IP: '0.0.0.0'. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] Server socket created on IP: '::'. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] InnoDB: Buffer pool(s) load completed at 220713 22:23:07 Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] /usr/sbin/mariadbd: ready for connections. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: Version: '10.8.3-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server Jul 13 22:23:07 centos7.example.io systemd[1]: Started MariaDB 10.8.3 database server. ... Client version checking with mariadb command: $ mariadb -V mariadb Ver 15.1 Distrib 10.8.3-MariaDB, for Linux (x86_64) using readline 5.1 Use the mariadb-secure-installation script to secure your MariaDB database server $ sudo mariadb-secure-installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password or using the unix_socket ensures that nobody can log into the MariaDB root user without the proper authorisation. You already have your root account protected, so you can safely answer 'n'. Switch to unix_socket authentication [Y/n] y Enabled successfully! Reloading privilege tables.. ... Success! You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! Access to MariaDB shell should only be possible with a password $ mysql -u root ERROR 1698 (28000): Access denied for user 'root'@'localhost' $ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 14 Server version: 10.8.3-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT VERSION (); +----------------+ | VERSION () | +----------------+ | 10.8.3-MariaDB | +----------------+ 1 row in set (0.000 sec) Step 4) Create a test database/user in MariaDB We can create a test database with the command below: MariaDB [(none)]> CREATE DATABASE mydb; Query OK, 1 row affected (0.001 sec) Check if the database has been created by showing all available databases. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | mydb | +--------------------+ 5 rows in set (0.000 sec) If this setup process progressed as anticipated you should have MariaDB 10.8 installed and running on CentOS 7 / RHEL 7 server or Desktop machine. If you encounter any issues let us know in the comments section.

0 notes

globalmediacampaign · 4 years ago

Text

MySQL 101: Basic MySQL Server Triage

So your MySQL server has crashed. What do you do now? When a server is down, in my opinion, there are two steps that are essential and both are extremely important and neither should be neglected: Save diagnostic information for determining the root cause analysis (RCA). Get the server back up and running. Too many people rush to Step #2 and lose pertinent diagnostics from Step #1. Likewise, too many people will spend too much time on Step #1 and delay getting to Step #2 and restoring service. The goal is to collect diagnostics as quickly as possible for later review while getting service restored as fast as possible. As a Technical Account Manager (TAM) and assisting on server restoration calls, I have seen both issues at play. Technical resources have a tendency to get so bogged down in trying to understand the cause of the server outage that they forget that the downtime is costing the business money. The desire to crawl through server logs, review metrics, pour-over system metrics, and so on, can be too tempting for some who are concerned that important diagnostic data will be lost when service is restored. This is a valid concern, but there must be a middle ground. Conversely, many, especially those in management, will demand service is restored immediately to continue business functions. Of course, after the service is back up, the demand for an RCA will come. Sadly, many metrics, and some logs, are lost when a server is bounced. Below are basic guidelines on what metrics to collect for MySQL. The steps are in no particular order. Save a copy of the MySQL Error Log.sudo cp /path/to/datadir/*.log /some/where/safe Make a copy of the MySQL configuration file.sudo cp /path/to/my.cnf /some/where/safe Make a copy of system logs and save them somewhere on persistent storage in a location that will not be overwritten. Consider doing something like the following on Linux:sudo cp /var/log/syslog /some/where/safe/syslog sudo cp /var/log/messages /some/where/safe/messages sudo journalctl -e > /some/where/safe/journalctl.txt If MySQL is running still and you can log in, get some MySQL metrics. You will want to save the output into files somewhere.sudo mysqladmin -i10 -c10 proc > /some/where/safe/mysql_procs.txt mysql> SHOW GLOBAL VARIABLES; sudo mysqladmin -i10 -c10 ext > /some/where/safe/mysql_ext.txt mysql> SHOW ENGINE INNODB STATUSG If MySQL is running and you have Percona Toolkit, you should collect some pt-stalk output.sudo ./pt-stalk --no-stalk --iterations=2 --sleep=30 --dest=/some/where/safe -- --user=root --password=; If you have space and time, a copy of the database files (data directory in MySQL) could be helpful. Certainly, for many installations, getting all of the data files will be impossible. If it is a small database and space and time allow, it can be best to get all the files just in case.sudo cp -R /path/to/datadir /some/where/safe/datadir Copy database logs and save them somewhere safe for later review. Systems like Percona XtraDB Cluster (PXC) will create GRA files during an issue which can be really helpful to look at to determine the root cause. By combining the GRA header file with the contents of the GRA log files, you can use the mysqlbinlog command to get the records of transactions causing issues. More information can be found in one of our older blogs herePercona XtraDB Cluster (PXC): what about GRA_*.log files?.sudo cp /path/to/data/dir/GRA* /some/where/safe/datadir/ Save system metrics pertaining to CPU, I/O, and memory usage:sudo mpstat -a 1 60 > /some/where/safe/mpstat.txt sudo vmstat 1 60 > /some/where/safe/vmstat.txt sudo iostat -dmx 1 60 > /some/where/safe/iostat.txt Save system info.sudo cat /proc/cpuinfo > /some/where/safe/cpuinfo.txt If you have Percona Toolkit, the following would be very helpful:sudo pt-summary > /some/where/safe/pt-summary.txt sudo pt-mysql-summary > /some/where/safe/pt-mysql-summary.txt Get hardware diagnostics.# disk info sudo df -k > /some/where/safe/df_k.txt sudo lsblk -o KNAME,SCHED,SIZE,TYPE,ROTA > /some/where/safe/lsblk.txt sudo lsblk --all > $PTDEST/lsblk-all; # lv/pv/vg only for systems with LVM sudo lvdisplay --all --maps > /some/where/safe/lvdisplau-all-maps.txt sudo pvdisplay --maps > /some/where/safe/pvdisplay-maps.txt sudo pvs -v > /some/where/safe/pvs_v.txt sudo vgdisplay > /some/where/safe/vgdisplay.txt # nfsstat for systems with NFS mounts sudo nfsstat -m > /some/where/safe/nfsstat_m.txt sudo nfsiostat 1 120 > /some/where/safe/nfsiostat.txt # Collect hardware information sudo dmesg > /some/where/safe/dmesg.txt sudo dmesg -T free -m > /some/where/safe/dmesg_free.txt sudo dmesg -T > /some/where/safe/dmesg_t.txt sudo ulimit -a > /some/where/safe/ulimit_a.txt sudo cat /proc/sys/vm/swappiness > /some/where/safe/swappiness sudo numactl --hardware > /some/where/safe/numactl-hardware.txt It goes without saying, it would be best to script the above into a useful bash script you can run when there is an issue. Just be sure to test the script in advance of an issue. Again, the goal is to preserve useful diagnostic data that could be useful for determining the root cause of the issue at a later time after the service is restored. Just don’t get caught up in looking through the above diagnostics! Certainly, more data is better but the above is a great starting point. As time goes on, you may realize you wish you had other metrics and can add them to your script or Standard Operating Procedure (SOP). Naturally, adding monitoring like Percona Monitoring and Management (PMM) would be a great option that can save you a lot of time and collect even more trends over time which can be extremely helpful. With the above diagnostics, you would have a ton of information in the event of an issue to find the root cause. Now, you can sort through the diagnostics. Of course, if you need help with that, Percona can help you here as well. https://www.percona.com/blog/2021/04/07/mysql-101-basic-mysql-server-triage/

0 notes

ore-no · 11 years ago

Text

MySQLをリカバリしたよ日記ドラフト

・NagiosのAlertが届く、HTTPチェックに失敗・Apacheが死んだかー？と思って見に行く・ロードアベレージはそこまで高くないけどrailsがエラー吐きまくってる・DBに接続できないとか。・MySQLを再起動してみる・帰ってこなくなった・エラー見るとinnodbなんてエンジンは知らんと出てきていた・ibdata1が壊れたっぽい・退避させてもだめ・辞書操作ができないと言われてるみたい、show tableはできるけどselectやら一切のテーブルに対するSQLが流れなくなる・EBSスナップショットから復旧決定、当日7時のスナップショットをEBSに起こす・/var/lib/mysqlへマウントして復旧するか、しなかった・バイナリログがあるね、これをRollfowardして障害前まで復旧できそうだ・ゆかいな氏とペアトラシュ・7時前のバイナリログの位置はどこだっけ・どっから流せばいいんだー・mysqlbinlogが大量すぎてしぬ、lessが動かないっぽい・復旧できない位置と実際のデータの状態を見てみる（DELETEならデータがまだあるはずとか、そういう確認）・show master statusで最後のポジションが分かった（そういえばレプリケーションの時必要だよね、これなんだー・mysqlbinlog --start-position="939393939393" db > hoge.sql ・インポート…刺さった！？・なんか今さっき更新されたっぽいんだけど、ポート変えたよね・database.yml見たら…ウワァーUNIXSOCKET経由でアクセスしているゥゥゥ・crontab全部止めよう（白目・EBSから再度復帰させて、リトライ・うまくいきましたー（88888

#EBS #MySQL #mysqlbinlog #リカバリー #トラブルシューティング

0 notes

globalmediacampaign · 4 years ago

Text

Percona XtraBackup Point-In-Time Recovery for the Single Database

Recovering to a particular time in the past is called Point-In-Time Recovery (PITR). With PITR you can rollback unwanted DELETE without WHERE clause or any other harmful command. PITR with Percona XtraBackup is pretty straightforward and perfectly described in the user manual. You need to restore the data from the backup, then apply all binary logs created or updated after the backup was taken, but skip harmful event(s). However, if your data set is large you may want to recover only the affected database or table. This is possible but you need to be smart when filtering events from the binary log. In this post, I will show how to perform such a partial recovery using Percona XtraBackup, mysql command-line client, and mysqlbinlog programs only. There is an alternative approach that involves creating a fake source server, that is described in MySQL Point in Time Recovery the Right Way. You may consider it, especially if you need to apply changes to a single table. Percona XtraBackup Point-In-Time Recovery For our example we will create data first, then run DROP and DELETE commands on two different tables. Then we will rollback these commands. First, let’s assume we have a server with two databases: test and sbtest. We are using GTIDs and row-based binary log format. We also run the server with the option innodb_file_per_table=1 and all our InnoDB tables use individual tablespaces. Otherwise, the individual restore method would not work.mysql> show tables from sbtest; +------------------+ | Tables_in_sbtest | +------------------+ | sbtest1 | | sbtest2 | | sbtest3 | | sbtest4 | | sbtest5 | | sbtest6 | | sbtest7 | | sbtest8 | +------------------+ 8 rows in set (0.00 sec) mysql> show tables from test; +----------------+ | Tables_in_test | +----------------+ | bar | | baz | | foo | +----------------+ 3 rows in set (0.00 sec)We will experiment with tables foo and bar. We assume that at the time of our first backup, each of the tables contained five rows. Tables in the database sbtest also contain data, but it does not really matter for our experiment.mysql> select count(*) from foo; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from bar; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from baz; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)Since we want to restore individual tables, we need to make a preparation before taking a backup: store database structure. We will do it with help of the mysqldump command. In this example, I store structure per database to make partial PITR easier, but you are free to use the option --all-databases.mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events test > test_structure.sql mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events sbtest > sbtest_structure.sqlThen we are ready to take the backup.xtrabackup --parallel=8 --target-dir=./full_backup --backupI am using the option --parallel to speed up the backup process. Now let’s do some testing. First, let’s update rows in the table foo.mysql> update foo set f1=f1*2; Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from foo; +----+------+ | id | f1 | +----+------+ | 1 | 2 | | 2 | 4 | | 3 | 6 | | 4 | 8 | | 5 | 10 | +----+------+ 5 rows in set (0.00 sec)And then drop it and delete all rows from the table bar.mysql> drop table foo; Query OK, 0 rows affected (0.02 sec) mysql> delete from bar; Query OK, 5 rows affected (0.01 sec)Finally, let’s insert a few rows into the tables bar and baz.mysql> insert into bar(f1) values(6),(7),(8),(9),(10); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into baz(f1) values(1),(2),(3),(4),(5); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0Assume that the DROP TABLE and DELETE command was an accident and we want to restore the state of the tables foo and bar as they were before these unlucky statements. First, we need to prepare the backup. Since we are interested in restoring only tables in the database test we need to prepare the backup with a special option --export that exports tablespaces in a way that they could be later imported:xtrabackup --prepare --export --target-dir=./full_backupNow the directory for the database test contains not only table definition files (.frm, only before 8.0) and tablespace files (.ibd) but also configuration files (.cfg). Since we want all changes that happened after backup and before the problematic DROP TABLE and DELETE statements were applied, we need to identify which binary log and position were actual at the backup time. We can find it in the xtrabackup_binlog_info file:$ cat full_backup/xtrabackup_binlog_info master-bin.000004 1601 0ec00eed-87f3-11eb-acd9-98af65266957:1-56Now we are ready to perform restore. First, let’s restore the table foo from the backup. Restoring individual tablespaces requires the ALTER TABLE ... IMPORT TABLESPACE command. This command assumes that the table exists in the server. However, in our case, it was dropped and therefore we need to re-create it. We will recreate the full database test from the file test_structure.sql Since we do not want these administrative tasks to be re-applied, I suggest disabling binary logging for the session which will recreate the database structure.mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source test_structure.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ....Once tables are recreated discard their tablespaces. I will show an example for the table foo. Adjust the code for the rest of the tables.mysql> alter table foo discard tablespace; Query OK, 0 rows affected (0.01 sec)Then, in another terminal, copy the tablespace and configuration files from the backup to the database directory:cp full_backup/test/foo.{ibd,cfg} var/mysqld.1/data/test/And, finally, import the tablespace:mysql> alter table foo import tablespace; Query OK, 0 rows affected (0.05 sec)Repeat for the other tables in the database test. Now you can enable binary logging back. You can do the same task in a script. For example:for table in `mysql test --skip-column-names --silent -e "show tables"` > do > mysql test -e "set sql_log_bin=0; alter table $table discard tablespace" > cp full_backup/test/$table.{ibd,cfg} var/mysqld.1/data/test/ > mysql test -e "set sql_log_bin=0; alter table $table import tablespace" > doneOur tables are recovered but do not have the updates made after the backup.mysql> select * from foo; +----+------+ | id | f1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+------+ 5 rows in set (0.00 sec) mysql> select * from bar; +----+------+ | id | f1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+------+ 5 rows in set (0.00 sec) mysql> select * from baz; Empty set (0.00 sec)Therefore, we need to restore data from the binary logs. To do it we first need to identify the GTID of the disaster event. It can be done if we dump all binary logs updated and created after backup into a dump file and then search for the DROP TABLE and DELETE statements and skipping them. First, let’s check which binary logs do we have.mysql> show binary logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 1527476 | | master-bin.000002 | 3035 | | master-bin.000003 | 1987 | | master-bin.000004 | 2466 | | master-bin.000005 | 784 | +-------------------+-----------+ 5 rows in set (0.00 sec)So we need to parse them, starting from the log master-bin.000004 and position 1601:mysqlbinlog --start-position=1601 -vvv --base64-output=decode-rows --database=test master-bin.000004 master-bin.000005 > binlog_test.sqlI used options -vvv that prints SQL representation of row events, so we can find the one which we want to skip and --base64-output=decode-rows to not print row events at all. We will not use this file for the restore, only for searching the DROP TABLE and DELETE events. Here they are, at the positions 2007 and 2123, with GTID 0ec00eed-87f3-11eb-acd9-98af65266957:58 and 0ec00eed-87f3-11eb-acd9-98af65266957:59# at 2007 #210321 13:29:58 server id 1 end_log_pos 2123 CRC32 0xd1eb9854 Query thread_id=138 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1616322598/*!*/; DROP TABLE `foo` /* generated by server */ /*!*/; # at 2123 #210321 13:30:08 server id 1 end_log_pos 2188 CRC32 0xfc9b2088 GTID last_committed=7 sequence_number=8 rbr_only=yes original_committed_timestamp=0 immediate_commit_timestamp=0 transaction_length=0 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=0 (1970-01-01 02:00:00.000000 EET) # immediate_commit_timestamp=0 (1970-01-01 02:00:00.000000 EET) /*!80001 SET @@session.original_commit_timestamp=0*//*!*/; /*!80014 SET @@session.original_server_version=0*//*!*/; /*!80014 SET @@session.immediate_server_version=0*//*!*/; SET @@SESSION.GTID_NEXT= '0ec00eed-87f3-11eb-acd9-98af65266957:59'/*!*/; # at 2188 #210321 13:30:08 server id 1 end_log_pos 2260 CRC32 0x1d525b11 Query thread_id=138 exec_time=0 error_code=0 SET TIMESTAMP=1616322608/*!*/; BEGIN /*!*/; # at 2260 #210321 13:30:08 server id 1 end_log_pos 2307 CRC32 0xb57ecb73 Table_map: `test`.`bar` mapped to number 226 # at 2307 #210321 13:30:08 server id 1 end_log_pos 2387 CRC32 0x6770a7e2 Delete_rows: table id 226 flags: STMT_END_F ### DELETE FROM `test`.`bar` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=1 is_null=0 */ ### DELETE FROM `test`.`bar` ### WHERE ...Note that decoded row event contains a DELETE command for each affected row. We may also find to which binary log this event belongs if search for the "Rotate to" event. In our case “Rotate to master-bin.000005” happened after the found positions, so we only need file master-bin.000004 In your case, you may need to skip events from the previous log files too. So to restore the data we need to run mysqlbinlog one more time, this time with parameters:mysqlbinlog --start-position=1601 --exclude-gtids=0ec00eed-87f3-11eb-acd9-98af65266957:58-59 --database=test --skip-gtids=true master-bin.000004 master-bin.000005 > binlog_restore.sqlI removed options -vvvbecause we are not going to examine this restore file and option --base64-output=decode-rows because we need row events to present in the resulting file. I also used option --exclude-gtids=0ec00eed-87f3-11eb-acd9-98af65266957:58-59 to exclude GTIDs that we do not want to re-apply. We also need to use --skip-gtids=true because otherwise updates will be skipped since such GTIDs already exist on the server. Now binlog_restore.sql contains all updates to the database test made after the backup and before the DROP statement. Let’s restore it.mysql test < binlog_restore.sqlRestore went successfully. Our tables have all past updates.mysql> select * from foo; +----+------+ | id | f1 | +----+------+ | 1 | 2 | | 2 | 4 | | 3 | 6 | | 4 | 8 | | 5 | 10 | +----+------+ 5 rows in set (0.01 sec) mysql> select count(*) from bar; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from baz; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) Conclusion You may save the time required for PITR if use the per-database restore method. However, you need to take into account the following considerations: mysqlbinlog does not support filtering per table, therefore you either need to restore the full database or use a fake server method, described in MySQL Point in Time Recovery the Right Way. Per-database filters depend on the USE statement in the statement-based binary log format. Therefore option --database can only be considered safe with a row-based format. If you do not use GTID you still can use this method. You will need to combine options --start-position and --stop-position to skip the event. Percona XtraBackup is a free, open source database backup solution for Percona Server for MySQL and MySQL. https://www.percona.com/blog/2021/04/02/percona-xtrabackup-point-in-time-recovery-for-the-single-database/

0 notes

globalmediacampaign · 4 years ago

Text

Point-In-Time Recovery in Kubernetes Operator for Percona XtraDB Cluster – Architecture Decisions

Point-In-Time Recovery (PITR) for MySQL databases is an important feature that is essential and covers common use cases, like a recovery to the latest possible transaction or roll-back the database to a specific date before some bad query was executed. Percona Kubernetes Operator for Percona XtraDB Cluster (PXC) added support for PITR in version 1.7, and in this blog post we are going to look into the technical details and decisions we made to implement this feature. Architecture Decisions Store Binary Logs on Object Storage MySQL uses binary logs to perform point-in-time recovery. Usually, they are stored locally along with the data, but it is not an option for us: We run the cluster and we cannot rely on a single node’s local storage. The cloud-native world lives in an ephemeral dimension, where nodes and pods can be terminated and S3-compatible storage is a de facto standard to store data. We should be able to recover the data to another Kubernetes cluster in case of a disaster. We have decided to add a new Binlog Uploader Pod, which connects to the available PXC member and uploads binary logs to S3. Under the hood, it relies on the mysqlbinlog utility. Use Global Transaction ID Binary logs on the clustered nodes are not synced and can have different names and contents. This becomes a problem for the Uploader, as it can connect to different PXC nodes for various reasons. To solve this problem, we decided to rely on Global Transaction ID (GTID). It is a unique transaction identifier, but it is unique not only to the server on which it originated, but is unique across all servers in a given replication topology. With the GTID captured in binary logs, we can identify any transaction not depending on the filename or its contents. This allows us to continue streaming binlogs from any PXC member at any moment. User-Defined Functions We have a unique identifier for every transaction, but the mysqlbinlog utility still doesn’t have the functionality to determine which binary log file contains which GTID. We decided to extend MySQL with few User Defined Functions and added them to Percona Server for MySQL and Percona XtraDB Cluster versions 8.0.21. get_gtid_set_by_binlog() This function returns all GTIDs that are stored inside the given binlog file. We put the GTID setlist to a new file next to the binary log on S3. get_binlog_by_gtid_set() This function takes GTID set as an input and returns a binlog filename which is stored locally. We use it to figure out which GTIDs are already uploaded and which binlog to upload next. Have open source expertise you want to share? Submit your talk for Percona Live ONLINE 2021! Find the node with the oldest binary log Our quality assurance team caught a bug before the release which can happen in the cluster only: Add a new node to the Percona XtraDB Cluster (for example scale up from 3 to 5 nodes). Binlog Uploader Pod tries to execute get_binlog_by_gtid_set on the new node but gets the error. 2021/01/19 11:23:19 ERROR: collect binlog files: get last uploaded binlog name by gtid set: scan binlog: sql: Scan error on column index 0, name "get_binlog_by_gtid_set('a8e657ab-5a47-11eb-bea2-f3554c9e5a8d:15')": converting NULL to string is unsupportedThe error is valid, as this node is new and there are no binary log files that have the GTID set that Uploader got from S3. If you look into this pull request, the quick patch is to always pick the oldest node in the array or in other words the node, which most likely would have the binary logs we need. In the next release of the Operator, we add more sophisticated logic, to discover the node which has the oldest binary logs for sure. Storageless binlog uploader The size of binlogs depends on the cluster usage patterns, so it is hard to predict the size of the storage or memory required for them. We decided to take this complexity away by making our Binary Log Uploader Pod completely storageless. Mysqlbinlog can store remote binlog only into files, but we need to put them to S3. To get there we decided to use a named pipe or FIFO special file. Now mysqlbinlog utility loads the binary log file to a named pipe, our Uploader reads it and streams the data directly to S3. Also, storageless design means that we never store any state between Uploader restarts. Basically, state is not needed, we only need to know which GTIDs are already uploaded and we have this data on a remote S3 bucket. Such design enables the continuous upload flow of binlogs. Binlog upload delay S3 protocol expects that the file is completely uploaded. If the file upload is interrupted (let’s say Uploader Pod is evicted), the file will not be accessible/visible on S3. Potentially we can lose many hours of binary logs because of such interruptions. That’s why we need to split the binlog stream into files and upload them separately. One of the options that users can configure when enabling point-in-time recovery in Percona XtraDB Cluster Operator is timeBetweenUploads. It sets the number of seconds between uploads for Binlog Uploader Pod. By default, we set it to 60 seconds, but it can go down to one second. We do not recommend setting it too low, as every invocation of the Uploader leads to FLUSH BINARY LOGS command execution on the PXC node. We need to flush the logs to close the binary log file to upload it to external storage, but doing it frequently may negatively affect IO and as a result database performance. Recovery It is all about recovery and it has two steps: Recover the cluster from a full backup Apply binary logs We already have the functionality to restore from a full backup (see here), so let’s get to applying the binary logs. First, we need to figure out from which GTID set we should start applying binary logs – in other words: where do we start?. As we rely on the Percona XtraBackup utility to take full MySQL backups, what we need to do is read the xtrabackup_info file which has lots of useful metadata. We already have this file on S3 near the full backup. Second, find the binlog which has the GTID set we need. As you remember, we store a file with binlog’s GTID sets on S3 already, so it boils down to reading these files. Third, download binary logs and apply them. Here we rely on mysqlbinlog as well, which has the flags we need, like –stop-datetime – which stops recovery when the event with a specific timestamp is caught in the log. Conclusion MySQL is more than 25 years old and has a great tooling ecosystem established around it, but as we saw in this blog post, not all these tools are cloud-native ready. Percona engineering teams are committed to providing users the same features across various environments, whether it is a bare-metal installation in the data center or cutting edge Kubernetes in the cloud. https://www.percona.com/blog/2021/02/24/point-in-time-recovery-in-kubernetes-operator-for-percona-xtradb-cluster-architecture-decisions/

0 notes

globalmediacampaign · 4 years ago

Text

Fastest Parallel replication method in MySQL 8.

From MySQL 5.7, we had a Multi-threaded Slave (MTS) Applier mechanism called LOGICAL_CLOCK to overcome the problems of parallel replication within a database. To further improve the parallelisation mechanism, from MySQL 8 (5.7.22) we have write-set replication, so before going further , lets look at the difference between Logical clock (LC) and Writeset. LOGICAL_CLOCK Transactions that are part of the same binary log group commit on a master are applied in parallel on a slave. The dependencies between transactions are tracked based on their timestamps to provide additional parallelisation where possible. WRITESET Write-set is a mechanism to track independent transactions that can be executed in parallel in the slave. Parallelising on write sets has potentially much more parallelism than logical_clock ,since it does not depend on the commit history, and as such, applying binary logs on a slave, it mean that replication can become much faster when compared to other parallel mechanism. Below are the variables related write-set are : mysql> show variables where variable_Name in ('transaction_write_set_extraction', 'binlog_transaction_dependency_tracking'); +----------------------------------------+--------------+ | Variable_name | Value | +----------------------------------------+--------------+ | binlog_transaction_dependency_tracking | COMMIT_ORDER | | transaction_write_set_extraction | XXHASH64 | +----------------------------------------+--------------+ transaction_write_set_extraction : specifies the algorithm used to hash the writes extracted during a transaction. binlog_format must be set to ROW to change the value of this system variable. XXHASH64 is the most recommended method. Note : The variable transaction_write_set_extraction is not enabled by default in MySQL 5.7.22 and above. binlog_transaction_dependency_tracking : specifies the source of dependency information that the source records in the binary log to help replicas determine which transactions can be executed in parallel. The possible values are: COMMIT_ORDER: Dependency information is generated from the source’s commit timestamps. This is the default. WRITESET: Dependency information is generated from the source’s write set, and any transactions that write different tuples can be can be made parallel. in other words the data is parallelised the transactions has not touched or modified the same data row. WRITESET_SESSION: Dependency information is generated from the source’s write set, and any transactions that write different tuples can be made parallel, with the exception that no two updates from the same session can be reordered. now we will dive into testing environment and look at how the write-set parallelises the replication?. I have installed latest MySQL 8.0.23 version for testing purpose mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.23 | +-----------+ 1 row in set (0.00 sec) In order to enable the write-set parallelism ,Binlog_transacion_dependency_tracking is to be set from COMMIT_ORDER (default) to WRITESET mysql> SET GLOBAL binlog_transaction_dependency_tracking = WRITESET; Query OK, 0 rows affected (0.00 sec) Creating a test database and table. mysql> create database mydbops_test; Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE writeset_test ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `city` varchar(50) NOT NULL); Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> show binary logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 1527 | No | | binlog.000002 | 2291 | No | | binlog.000003 | 1659 | No | +---------------+-----------+-----------+ 3 rows in set (0.00 sec) let us insert some sample data into the table writeset_test table. mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> insert into writeset_test (city) values ('Delhi'); Query OK, 1 row affected (0.00 sec) mysql> insert into writeset_test (city) values ('Mangalore'); Query OK, 1 row affected (0.01 sec) mysql> insert into writeset_test (city) values ('Kolkata'); Query OK, 1 row affected (0.00 sec) mysql> insert into writeset_test (city) values ('Kanpur'); Query OK, 1 row affected (0.01 sec) to observe the behaviour of WRITESET , let us decode the binlog and see the how the above inserted transactions will be parallelised. [root@vm8 mysql]# mysqlbinlog binlog.000004 | grep last_ | sed -e 's/server id.*last/[...] last/' -e 's/.rbr_only.*/ [...]/' #210119 3:59:07 [...] last_committed=0 sequence_number=1 [...] #210119 4:03:26 [...] last_committed=1 sequence_number=2 [...] #210119 4:03:34 [...] last_committed=1 sequence_number=3 [...] #210119 4:04:11 [...] last_committed=1 sequence_number=4 [...] #210119 4:17:49 [...] last_committed=1 sequence_number=5 [...] As we can see from the above logs , the transaction from sequence_number 2-5 will be executed parallel in slave, as the all the transactions are independent and non conflicting to each other. When the same the row is modified or deleted from multiple transaction , those transactions will not be part of WRITESET mysql> delete from writeset_test where id=6; Query OK, 1 row affected (0.00 sec) mysql> insert into writeset_test (id,city) values (6,'Kanpur'); Query OK, 1 row affected (0.01 sec) #210119 11:22:40 [...] last_committed=4 sequence_number=5 [...] #210119 11:22:41 [...] last_committed=5 sequence_number=6 [...] In above example, the id 6 is deleted and inserted back , as this rows will conflicted with 2 transactions, it will not replicated in parallel. WRITESET_SESSION Now lets look at another mode Binlog_transacion_dependency_tracking is WRITESET_SESSION When we want to restrict the two transaction from same session should not be executed on slave when slave-preserve-commit-order option is not enabled , then WRITESET_SESSION mode can be used, which basically never allows the transactions from the same session to be executed in parallel. lets look at example to see the behaviour of WRITESET_SESSION , now we already now that the 2 transactions in same session cannot be parallelised , so the will execute the the queries from 2 different session and see the results by decode the the binlog Session 1 mysql> insert into mydbops_test.writeset_test (city) values ('Ahmedabad'); Query OK, 1 row affected (0.01 sec) mysql> update writeset_test set city='Mangalore' where id=13; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #210119 12:41:30 [...] last_committed=1 sequence_number=2 [...] #210119 12:42:22 [...] last_committed=2 sequence_number=3 [...] In session 1 ,even the transactions are on non-conflicting rows, it cannot be parallelised. Session 2 mysql> delete from writeset_test where id=11; Query OK, 1 row affected (0.01 sec) mysql> insert into mydbops_test.writeset_test (city) values ('Patna'); Query OK, 1 row affected (0.00 sec) #210119 12:41:30 [...] last_committed=1 sequence_number=2 [...] #210119 12:42:22 [...] last_committed=2 sequence_number=3 [...] #210119 12:44:34 [...] last_committed=1 sequence_number=4 [...] #210119 12:45:13 [...] last_committed=4 sequence_number=5 [...] from session 2 , the transaction 2(from session1) and transaction 3 are taken in write-set which will can be executed in parallel. Writeset based replication is the fastest replication method in MySQL. It ensures the faster synchronisation of Replica set by utilising the underlying the hardware efficiently. Limitations fo Writeset Supports only InnoDB . Ensure tables have Primary keys. Foreign key tables will not take benefit of WRITESET mode. Slave_parallel_workers should be greater than zero. Larger value of binlog_transaction_dependency_history_size will keep more row hashes in memory. The writset replication needs fine tuning based on the workload to reap a very optimal performance out of it. https://mydbops.wordpress.com/2021/02/14/writeset-replication-in-mysql-8/

0 notes

globalmediacampaign · 4 years ago

Text

MySQL Backup and Recovery Best Practices

In this blog, we will review all the backup and restore strategies for MySQL, the cornerstones of any application. There are a few options, depending on your topology, MySQL versions, etc. And based on that, there are some questions we need to ask ourselves to make sure we make the right choices. How many backups we need to keep safe, or what’s the best retention policy for us? This means the number of backups to safeguard, whether local or remote (external fileserver, cloud). The retention policy can be daily, weekly, or monthly, depending on the free space available. What is the Recovery Time Objective? The Recovery Time Objective (RTO) refers to the amount of time that may pass during a disruption before it exceeds the maximum allowable threshold specified in the Business Continuity Plan. The key question related to RTO is, “How quickly must the data on this system be restored?” What is the Recovery Point Objective? The Recovery Point Objective (RPO) is the duration of time and service level within which a business process must be stored after a disaster in order to avoid unacceptable consequences associated with a break in continuity. The key question related to RPO is, “How much data can we lose?” Different Types of Backups There are two backup types: physical and logical. Physical (Percona XtraBackup, RDS/LVM Snapshots, MySQL Enterprise Backup), and also you can use cp or rsync command lines to copy the datadir as long as mysql is down/stopped. Logical (mysqldump, mydumper, mysqlpump, mysql shell only for mysql 8) Also is recommended to take a copy of binlog files, why? Well, this will help us to recover until the last transaction. Why are backups needed? Backups are needed in case of multiple problems: Host Failure: We can get multiple problems from disks stalled or broken disks. Also from cloud services, our DB instance can be broken and it’s non-accessible. Corrupted Data: This can happen on a power outage, MySQL wasn’t able to write correctly and close the file, sometimes when MySQL starts again it cannot start due to corrupted data and the crash recovery process cannot fix it. Inconsistent Data: When a human mistake, delete/update erroneous data over the primary or replica node. DataCenter Failure: power outage or internet provider issues. Legislation/Regulation: provide consistent business value and customer satisfaction. Now let me explain those different types of backups mentioned above, but before I continue, it’s important to configure a new and dedicated replica node for backups purposes, due to the high CPU load to avoid any issue on any other replica node (AKA backup server). Logical Backup This is a dump from logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements). This is recommended to be used against smaller amounts of data. The disadvantage of this method is slower (backup and restore) if you compare it with physical backups. Using mydumper you can backup and restore a single database or a single table if it’s needed, and this is useful to copy some data to a different environment to run tests. Also, mydumper can take a consistent (as long as all the tables are InnoDB engine) backup and provides accurate master and slave log positions. The output is larger than for physical backup, particularly when saved in text format, but it can be compressed on the fly depending on the software you are using. Mydumper can compress and mysqldump needs to add a pipe to redirect the output to gzip, for example. Logical backups are used to address data corruption or the need to restore a subset of tables. Physical (Raw) Backup In short, this consists of exact copies of database directories and files. This can be a copy for all or a part from MySQL datadir directory. This kind of backup is most used to restore or create a new replica node easily and quickly and is used to address host failure. It’s recommended to restore using the same MySQL version. I recommend using Percona XtraBackup because it can include any related files such as configuration files like cnf config files. Snapshot Backups Some file system implementations enable “snapshots” to be taken. These provide logical copies of the file system at a given point in time, without requiring a physical copy of the entire file system. MySQL itself does not provide the capability for taking file system snapshots but it is available using third-party solutions such as LVM or ZFS. The disadvantage is that sometimes physical backups do not compress much, because data is usually in a binary format and sometimes the table is already compressed. Binary Log Backups Binlog backups specifically address RPO. Binary log files contain records of each SQL query executed that made changes. From MySQL 5.6 on, you can use mysqlbinlog to stream binary logs from a remote server. You can combine binlog backups with Percona XtraBackup or mydumper backup to allow restoration up to the end of the most-recently-backed-up binary log. Incremental / Differential Backups An incremental backup is a backup of everything that has changed since the last backup (a binary log backup is a special case of an incremental backup). This is a very good option if the dataset size is huge, as you can take a full backup at the beginning of the week and run incremental backups per day. Also, the backup size is smaller than the full backup. The main risks associated with incremental backups are: – A single corrupt incremental backup may invalidate all the others – Incremental backups typically negatively affect the RTO For a differential backup, it copies the differences from your last backup, and the advantage is that a lot of data does not change from one backup to the next, so the result can be significantly smaller backups. This saves disk space. Percona XtraBackup supports both incremental and differential backups. Offsite Storage It’s highly recommended to copy all the backup methods to another place, like the cloud or an external file server, so in case of host failure or data center failure, you have another copy. Not all the backup files need to be uploaded to the cloud, sometimes the time you need to spend in the download is bigger than the time consumed in the recovery process. A good approach is to keep 1-7 days locally on the backup server in case a fast recovery is needed, and this depends on your business regulations. Encryption Backups have sensitive data, so it’s highly recommended to encrypt, especially for offsite storage. This adds more time when you need to restore a backup but it keeps your data safe. GPG is a good option to encrypt backups, and if you use this option or some other alternative, don’t forget to get a copy of the keys/passphrase. If you lose it, your backups will be useless. Restore Testing Depending on your business, it��s highly recommended to test your backups at least once per month. This action validates your backups are not corrupted and it provides critical metrics on recovery time. This process should be automated to get the full backup, restore it, and finally configure this server as a replica from the current primary or another replica. This is good as well to validate that the replication process has no errors. Many customers are using this methodology to refresh their QA/STG environment to have fresh data from production backups. In addition to the above, it is recommended to create a manual or automated restore documentation process to keep all the steps together, so in case of disaster, you can follow it without wasting time. Retention Requirements Last but not least, it is very important to keep multiple copies of different backup types. Our best recommendation is: One or two physical backups locally on the backup server (as long as space allows it). Seven daily and four weekly logical backups locally on the backup server. 30 days of binlog backups locally on the backup server. For offsite backups (like S3, Google Cloud, etc.), keep monthly backups for one year or more. For local backups, keep in mind you will need a minimum of 2.5 times the current dataset size as free disk space to save/meet these retention policies. Don’t forget to encrypt all the backup types! Legal or regulatory requirements may also dictate how long data must be archived. Percona Can Help Percona can help you choose, implement, and optimize the most appropriate MySQL backup and recovery solution for your MySQL ecosystem. If your current solution unexpectedly fails, we can facilitate your recovery with onsite, remote, or emergency consulting services. We can also help you take steps to prevent another occurrence. Every situation is unique and we will work with you to create the most effective solution for your business. Contact Us https://www.percona.com/blog/2021/01/12/mysql-backup-and-recovery-best-practices/

0 notes