Top New Features in MySQL 8.1 database

MySQL 8.1 new features

Here are the top new features in 8.1.0 release of MySQL database version:

• Logging of the shutdown process has been enhanced, with the addition of startup and shutdown messages for the MySQL server, plugins, and components.
• Release MySQL 8.1 extends EXPLAIN FORMAT=JSON with an INTO a user variable where it can be worked with using MySQL JSON functions.
• Added the SHOW PARSE_TREE statement, which shows the JSON-formatted parse tree for a SELECT statement.
  SHOW PARSE_TREE SELECT * FROM t3 WHERE id > 2\G
• Stripping of comments from the mysql client was the default behavior; from MySQL 8.1.0 onwards, the default has changed to preserving such comments.
• Default value for the SOURCE_RETRY_COUNT option of the CHANGE REPLICATION SOURCE TO statement has been changed to 10.
• Added the tls-certificates-enforced-validation system variable, which permits a DBA to enforce certificate validation at server startup or when using the ALTER INSTANCE RELOAD TLS statement to reload certificates at runtime.
• group_replication_set_as_primary() function now waits for ongoing DDL statements such as ALTER TABLE when waiting for all transactions to complete, prior to electing a new primary.
• Added server system variables to control the amount of time MySQL accounts that connect to a MySQL server using LDAP pluggable authentication must wait when the LDAP server is down or unresponsive.
• Added status variables specific to Group Replication plugin that improve diagnosis and troubleshooting of network instabilities, providing statistics about network usage, control messages, and data messages for each group member.

Related MySQL Articles:  MySQL DBA top basic/advanced interview questions - Part 1

MySQL DBA Online Quiz

MySQL/MariaDB Database Administrator Online Quiz [ 2023 ]

[[ Questions/Answers in random order ]]

Related MySQL Articles: MySQL Database Administrator Interview Questions with Answers

Percona Xtrabackup Backup & Recovery

Percona Xtrabackup (PXC Part 4)

XtraBackup is an open-source MySQL hot backup software program.

Features include hot, non-locking backups for InnoDB storage, incremental backups, streaming, parallel-compressed backups, throttling based on the number of I/O operations per second, etc.

Percona XtraBackup is an open-source hot backup utility for MySQL servers that doesn’t lock databases during the backup.

Part 1 of the article - Percona XtraDB Cluster (PXC) 8.0 Installation/Configuration

Part 2 of the article - ProxySQL 2 configuration/installation

Part 3 of the article - Installation & Usage of Percona Toolkit

Part 5 of the article - PMM installation/configuration and dashboards

Percona Xtrabackup (8.0) installation from Repository

Percona provides repositories for yum (RPM packages for Red Hat, CentOS and Amazon Linux AMI) and apt (.deb packages for Ubuntu and Debian) for software such as Percona Server for MySQL, Percona XtraBackup, and Percona Toolkit.

yum list all | grep xtrabackup

dnf install percona-xtrabackup-80 -y

Percona XtraBackup can backup data from InnoDB, XtraDB, MyISAM tables on MySQL 8.0 servers as well as Percona Server for MySQL with XtraDB, Percona Server for MySQL 8.0, and Percona XtraDB Cluster 8.0.

Creating Backup

To create a backup, run xtrabackup with --backup option and specify --target-dir option where the backup will be stored.

xtrabackup --backup --target-dir=/data/backups/

xtrabackup --backup --user=satya --password=Secret9 --target-dir=/path/to/backupdir

xtrabackup --backup --user=satya --password=Secret9 --slave-info --target-dir=/path/to/backupdir

Preparing Backup

After making a backup with the --backup option, you need need to prepare it in order to restore it.

xtrabackup --prepare --target-dir=/data/backups/

xtrabackup --prepare --user=satya --password=Secret9 --target-dir=/path/to/backupdir

Restoring Backup

For convenience, xtrabackup binary has the --copy-back option to copy the backup to the datadir of the server. Backup needs to be prepared before it can be restored.

xtrabackup --copy-back --target-dir=/data/backups/

Incremental Backup

Percona xtrabackup supports incremental backups, which means that they can copy only the data that has changed since the last backup.

DBAs can perform many incremental backups between each full backup, to have 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.

To make an incremental backup, begin with a full backup as usual. The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup’s target directory.

xtrabackup --backup --target-dir=/data/backups/full

xtrabackup --backup --target-dir=/root/backups/full -uroot --pSecret9

Now that you have a full backup, you can make an incremental backup based on it.

xtrabackup --backup --target-dir=/data/backups/incl --incremental-basedir=/data/backups/full

xtrabackup --backup --target-dir=/root/backups/incl --incremental-basedir=/root/backups/full -uroot --pSecret9

Preparing Incremental Backups

The --prepare step for incremental backups is not the same as for full backups. Beginning with the full backup, you can prepare it, and then apply the incremental differences to it.

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/full

xtrabackup --prepare --apply-log-only --target-dir=/root/backups/full -uroot --pSecret9

To apply the first incremental backup to the full backup. Preparing the second/third incremental backup is a similar process.

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/full --incremental-dir=/data/backups/incl

xtrabackup --prepare --apply-log-only --target-dir=/root/backups/full --incremental-dir=/root/backups/incl -uroot --pSecret9

Restoring Incremental Backups

Once prepared incremental backups are the same as the full backups and they can be restored in the same way.

.. shutdown database ..

xtrabackup --copy-back --target-dir=/root/backups/

xtrabackup --copy-back --target-dir=/data/backups/

Related MySQL Articles: Percona Monitoring and Management (PMM)   How to use tools in Percona Toolkit

mysqlserverclone usage in MySQL

MySQL mysqlserverclone utility commands

mysqlserverclone  start another instance of a running server 

mysqlserverclone utility, in MySQL, enables DBAs to clone an existing MySQL server instance to create a new server instance on the same host (does not copy any data).

Usage: mysqlserverclone --server=user:pass@host:port:socket --new-data=/tmp/data2 --new-port=3310 --new-id=12 --root-password=root

mysqlserverclone --help
mysqlserverclone --version

mysqlserverclone --new-port=3310 --server=root:pass@localhost --new-data=/source/test123
mysqlserverclone --new-port=3310 --server=mysql_dba --new-data=/source/test123
mysqlserverclone --new-port=3310 --server=root:pass@localhost --new-data=/source/test123 --root-password=pass --mysqld=--log-bin=mysql-bin

mysqlserverclone --server=root:pass@localhost --new-data=/source/test123 --new-port=3310 --root-password=pass --mysqld=--log-bin=mysql-bin --quiet
mysqlserverclone --new-port=3310 --server=mysql_dba --new-data=/source/test123 --verbose

mysqlserverclone --server=root:mysql@localhost:3306 --new-data="C:/Users/store" --mysqld="--server-id=2 --log-bin --log-slave-updates --gtid-mode=ON --enforce-gtid-consistency" --new-port=13000 --delete-data
mysqlserverclone --basedir=/source/mysql-5.6--new-data=/source/temp_3009 --new-port=3009 --new-id=101 --root=root --mysqld="--log-bin --gtid-mode=on --log-slave-updates --enforce-gtid-consistency --master-info-repository=table --report-host=localhost --report-port=3009" --delete-data

mysqlserverclone Options:
  --version             show program's version number and exit
  --help                 display a help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]].

  --ssl-ca=SSL_CA         path to a file that contains a list of trusted SSL CAs.
  --ssl-cert=SSL_CERT   name of the SSL certificate file to use for   establishing a secure connection.
  --ssl-key=SSL_KEY     name of the SSL key file to use for establishing a secure connection.
  --ssl=SSL                   specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).

  --new-data=NEW_DATA   the full path to the location of the data directory for the new instance. The path size must be smaller or equal than 200 characters.
  --new-port=NEW_PORT   the new port for the new instance - default=3307
  --new-id=NEW_ID           the server_id for the new instance - default=2
  --root-password=ROOT_PASS    password for the root user
  --mysqld=MYSQLD          additional options for mysqld

  -w CMD_FILE, --write-command=CMD_FILE  path to file for writing startup command. For example: start_server1.sh
  -v, --verbose         control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug
  -q, --quiet            turn off all messages for quiet execution.
  --basedir=BASEDIR     the base directory for the server
  --delete-data         delete the folder specified by --new-data if it exists and is not empty.

  --user=USER         user account to launch cloned server. Default is current user.
  --start-timeout=START_TIMEOUT    Number of seconds to wait for server to start. Default = 10.
  --force                  Ignore the maximum path length and the low space checks for the --new-data option.


Related MySQL Articles: MySQL mysqluserclone utility cheatsheet   mysql_install_db utility commands in MySQL

Installing Percona XtraDB Cluster (PXC) 8

Percona XtraDB Cluster (PXC) 8.0 Installation/Configuration - Part 1

Here are the installation/deployment and configuration steps of PXC 8 (Percona XtraDB Cluster) for MySQL, with Galera cluster, on three nodes in a multi-master replication, on Oracle Linux (OEL)/Red Hat (RHEL)/CentOS/Ubuntu/Debian.

Multi-master (active-active) replication allows us to write operations on any node, even if one node fails, we can work on the other nodes. 

  Role Hostname       IP address

master 1     percona1     192.168.1.4

master 2     percona2     192.168.1.5

master 3     percona3     192.168.1.6

Part 2 of the article - ProxySQL 2 configuration/installation

Part 3 of the article - Percona Toolkit Usage and Installation

Part 4 of the article - Percona Xtrabackup Backup & Recovery

Part 5 of the article - Percona Monitoring and Management

Step I. Common Percona XtraDB Cluster installation steps (on all nodes)

Perform below 6 steps on all nodes, in PXC cluster.

i) Update /etc/hosts with all 3 node details

# vi /etc/hosts

192.168.1.4 percona1 percona1.localdomain

192.168.1.5 percona2 percona2.localdomain

192.168.1.6 percona3 percona3.localdomain

ii) Open firewall ports

Percona requires the following ports for the cluster nodes. Create the appropriate firewall rules on Percona nodes.

3306

4444

4567

4568

# firewall-cmd --permanent --add-port={3306/tcp,4444/tcp,4567/tcp,4568/tcp}

# firewall-cmd --reload

# firewall-cmd --list-all

iii) Enable SELinux (Security-Enhanced Linux)

Make sure SELinux is enabled on all Percona nodes.

# cat /etc/sysconfig/selinux

# sestatus

/etc/selinux/config file controls if SELinux is disabled or enabled, and if enabled, whether SELinux operates in enforcing mode or permissive mode.

iv) Add Percona repository

Add percona repository to Linux Distribution, so we can download the latest version of PXC.

For Ubuntu/Debian Operating Systems, please use apt-get instead of yum/dnf.

# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# percona-release enable-only pxc-80 release

# percona-release enable tools release

v) Installing PXC (Percona XtraDB Cluster) from yum/dnf repo

Install the Percona XtraDB Cluster software and including all dependences.

# yum module disable mysql -y

# yum list all | grep percona

# yum install percona-xtradb-cluster

vi) securing MySQL deployment

We need to perform a post-installation task to secure the MySQL deployment. Run the following command and answer the questions accordingly:

# mysql_secure_installation

---

Step II. Configuring Percona XtraDB Cluster on the first node

i) Initial set up

Start the MySQL service for PXC node1.

# systemctl start mysql

To get the temporary root mysql password you need to run the below command.

# grep password /var/log/mysqld.log

ii) Change root password

# mysql -u root -p

Enter the password you got in the above step.

Change the root user password to the MySQL database.

MySQL> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Percona@pxc8';

Verify wsrep parameters in the database.

MySQL> show variables like '%wsrep%';

Stop MySQL server on the first node.

# systemctl stop mysql

iii) Configure PXC Write-Set Replication (WSP) settings

Update my.cnf with custom values according to your requirement.

# cp /etc/my.cnf /etc/my.cnf.orig

# vi /etc/my.cnf

# Cluster name

wsrep_cluster_name=democluster

# Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.1.4,192.168.1.5,192.168.1.6

# First node hostname

wsrep_node_name=percona1

# First node IP address

wsrep_node_address=192.168.1.4

# pxc_encrypt_cluster_traffic is ON by default, in PXC 8. I don't have encryption keys/certificates, so turning OFF encryption

pxc_encrypt_cluster_traffic=OFF

Initialization parameter wsrep_sst_auth is NOT required in Percona XtraDB Cluster version 8. And no need to create a replication user also, because Percona XtraDB Cluster 8.0 automatically creates the system user mysql.pxc.internal.session. During SST, the user mysql.pxc.sst.user and the role mysql.pxc.sst.role are created on the donor node.

iv) Bootstrap the cluster

The below command will start the first node and bootstrap the cluster.

# systemctl start mysql@bootstrap.service

# systemctl status mysql@bootstrap.service

# tail -f /var/log/mysqld.log

---

Step III. Configuring Percona XtraDB Cluster on Second/Third node

i) Configuring PXC Cluster with WSP (Write-Set Replication)

Update my.cnf with custom values as per your requirement, on all other nodes.

# cp /etc/my.cnf /etc/my.cnf.orig

# vi /etc/my.cnf

# Cluster name

wsrep_cluster_name=democluster

# Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.1.4,192.168.1.5,192.168.1.6

# Second/Third node hostname

wsrep_node_name=percona2            # percona3, on third node

# Second/Third node IP address

wsrep_node_address=192.168.1.5      # 192.168.1.6, on third node

# pxc_encrypt_cluster_traffic is ON by default, in PXC 8. I don't have encryption keys/certificates, so turning OFF encryption

pxc_encrypt_cluster_traffic=OFF

ii) Start MySQL to join the cluster

# systemctl start mysql

# systemctl status mysql

# tail -f /var/log/mysqld.log

After MySQL server has been started, the new nodes should receive SST automatically. State Snapshot Transfer (SST) is a full data copy from one node (donor) to the joining node (joiner).

iii) Bouncing MySQL database server on first node

The first node was started in bootstrap mode, you can stop it and start the first node in normal mode.

# systemctl stop mysql@bootstrap.service

# systemctl start mysql

# systemctl status mysql

iv) Verify cluster setup

Cluster status can now be checked on all nodes.

# mysql -uroot -pPercona@pxc8 -e "select * from mysql.wsrep_cluster_members;"

+--------------------------------------+--------------------------------------+-----------+-----------------------+

| node_uuid                            | cluster_uuid                         | node_name | node_incoming_address |

+--------------------------------------+--------------------------------------+-----------+-----------------------+

| 275888d3-fde3-11eb-ba65-6b7198e764f9 | d6a6b71a-fda4-11eb-89f2-f7703766b53c | percona1  | 192.168.1.4:3306      |

| 41261eff-fde9-11eb-b83c-963c55b86b90 | d6a6b71a-fda4-11eb-89f2-f7703766b53c | percona3  | 192.168.1.6:3306      |

| cb8695c7-fde3-11eb-b8a1-d6cf0e345170 | d6a6b71a-fda4-11eb-89f2-f7703766b53c | percona2  | 192.168.1.5:3306      |

The above output shows that all 3 nodes are part of the cluster.

v) Verify default parameter values

By default, you will be seeing below parameter values (and along with other default parameters).

# cat /etc/my.inf

wsrep_provider=/usr/lib64/galera4/libgalera_smm.so

wsrep_sst_method=xtrabackup-v2

pxc_strict_mode=ENFORCING

binlog_format=ROW

innodb_autoinc_lock_mode=2

Step IV. Testing Replication on Percona XtraDB Cluster

To test the replication, let us create a new database on the second node, create a table for that database on the third node, add one record to the table on the first node, and verify on the third or second node.

i) Create a new database on the second node

MySQL@percona2> CREATE DATABASE satyadb;

ii) Create a table on the third node

MySQL@percona3> USE satyadb;

MySQL@percona3> CREATE TABLE employee (emp_id INT PRIMARY KEY, emp_name VARCHAR(50));

iii) Insert records on the first node

MySQL@percona1> INSERT INTO satyadb.employee VALUES (111, 'percona');

iv) Retrieve all the rows from the table, on the second or third node:

MySQL@percona2> SELECT * FROM satyadb.employee;

+---------+-----------+

| emp_id  | emp_name |

+---------+-----------+

|     111 | percona  |

+---------+-----------+

Percona XtraDB Cluster 8.0 is based on Percona Server for MySQL 8.0 embedded with Galera writeset replication API and Galera replication (version 4) library, to form a highly available active-active (master-master) replication for Oracle MySQL based database server.

Percona XtraDB Cluster, a database clustering solution, ensures high availability, prevents downtime and data loss, and provides linear scalability.

Related MySQL Articles:  ProxySQL 2 installation setup   PMM server and client installation

MySQL mysqlpump utility

mysqlpump commands in MySQL

mysqlpump # MySQL 5.7.8 onwards

mysqlpump --help

mysqlpump --version

mysqlpump --all-databases

mysqlpump --all-databases --user=root --password > full_backup.sql

mysqlpump --databases db_name1 db_name2  --user=root --password > partial_backup.sql

mysqlpump --single-transaction --default-parallelism=0

mysqlpump --user=root --password > full_backup.sql

mysqlpump --user=root --password --default-parallelism=4 > full_backup.sql

mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3

mysqlpump --user=root --password --parallel-schemas=db1,db2 --default-parallelism=3 > full_backup.sql

mysqlpump --user=root --password --include-databases=db% --result-file=all_db_backup.sql

mysqlpump --exclude-databases=% --users

mysqlpump --user=root --password --exclude-databases=db1,db2 --result-file=partial_backup.sql

mysqlpump --user=root --password --exclude-tables=t --result-file=partial_backup.sql

mysqlpump --user=root --password --include-events=ev2 --include-routines=p1 --exclude-databases=mysql --result-file=partial_backup.sql

Related MySQL Articles: mysqldump commands in MySQL

MySQL mysqlrplms utility cheatsheet

mysqlrplms utility in MySQL

mysqlrplms - establish multi source/master replication - to set up and start replication from a Slave to Multiple Masters/sources
mysqlrplms --help
mysqlrplms --version
Usage: mysqlrplms --slave=root@localhost:3306 --masters=root@localhost:3310,root@localhost:3311 --rpl-user=rpl:passwd

mysqlrplms --slave=root:root@localhost:3306 --masters=root:root@localhost:3307,root:root@localhost:3308
mysqlrplms --slave=root:root@localhost:3306 --masters=root:root@localhost:3307,root:root@localhost:3308 --report-values=health,gtid,uuid

The mysqlrplms utility is used to setup round robin multi-source replication.
This technique can be a solution for aggregating streams of data from multiple masters for a single slave.
A round-robin scheduling is used to setup replication among the masters and slave.

# Basic multi-source replication setup.
$ mysqlrplms --slave=root:pass@host1:3306 --masters=root:pass@host2:3306,root:pass@host3:3306
$ mysqlrplms --slave=slave10 --masters=master2,master66

# Multi-source replication setup using a different report values.
$ mysqlrplms --slave=root:pass@host1:3306 --masters=master11,master12 --report-values=gtid,uuid
$ mysqlrplms --slave=root:pass@host1:3306 --masters=root:pass@host2:3309,root:pass@host3:3308 --report-values=health,gtid,uuid
$ mysqlrplms --slave=root:pass@host1:3306 --masters=root:pass@host2:3309,root:pass@host3:3308 --report-values=health,uuid --vv --format=FORMAT=tab --interval=10

# Start multi-source replication running as a daemon. (POSIX only)
$ mysqlrplms --slave=root:pass@host1:3306 --masters=root:pass@host2:3306,root:pass@host3:3306 --log=mysql_rplms_daemon.log --pidfile=mysql_rplms_daemon.pid --daemon=start
$ mysqlrplms --slave=root:pass@host1:3306 --masters=root:pass@host2:3306,master4 --log=mysql_rplms_daemon.log --pidfile=mysql_rplms_daemon.pid --daemon=start

# Restart a multi-source replication running as a daemon.
$ mysqlrplms --slave=root:pass@host1:3306 --masters=root:pass@host2:3311,root:pass@host3:3313 --log=mysql_rplms_daemon.log --pidfile=mysql_rplms_daemon.pid --daemon=restart
$ mysqlrplms --slave=slave11 --masters=master3311,master3313 --log=mysql_rplms_daemon.log --pidfile=mysql_rplms_daemon.pid --daemon=restart

# Stop a multi-source replication running as a daemon.
$ mysqlrplms --slave=slave --masters=master1,master2 --log=mysql_rplms_daemon.log --pidfile=rplms_daemon.pid --daemon=stop
$ mysqlrplms --slave=root:pass@host1:3306 --masters=root:pass@host2:3306,root:pass@host3:3306 --log=mysql_rplms_daemon.log --pidfile=mysql_rplms_daemon.pid --daemon=stop
$ mysqlrplms --slave=slave --masters=master1,master2 --log=mysql_rplms_daemon.log --pidfile=rplms_daemon.pid --daemon=stop --verbose --format=FORMAT=csv

mysqlrplms Options:
  --version             show program's version number and exit
  --help                 display a help message and exit
  --license             display program's license and exit
  -i INTERVAL, --interval=INTERVAL   interval in seconds for reporting health. Default = 15 seconds. Lowest value is 5 seconds.
  --switchover-interval=SWITCHOVER_INTERVAL   interval in seconds for switching masters. Default = 60 seconds. Lowest value is 30 seconds.

  --slave=SLAVE             connection information for slave server in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]]
  --masters=MASTERS     connection information for master servers in the form: user[:password]@host[:port][:socket] or login-path[:port][:socket] or config- path[[group]]. List multiple master in comma- separated list.
  --rpl-user=RPL_USER         user and password for the replication user requirement, in the form: user[:password] or login-path. E.g. rpl:passwd
  -b, --start-from-beginning   start replication from the first event recorded in the binary logging of the masters.

  --report-values=REPORT_VALUES    report values used in multi-source replication. It can be health, gtid or uuid. Multiple values can be used separated by commas. The default is health.
  -f FORMAT, --format=FORMAT    display the output in either grid (default), tab, csv, or vertical format
  --daemon=DAEMON      run on daemon mode. It can be start, stop, restart or nodetach.
  --pidfile=PIDFILE          pidfile for running mysqlrplms as a daemon.
  --log=LOG_FILE           specify a log file to use for logging messages
  --log-age=LOG_AGE     specify maximum age of log entries in days. Entries older than this will be purged on startup. Default = 7 days.

  --ssl-ca=SSL_CA         path to a file that contains a list of trusted SSL CAs.
  --ssl-cert=SSL_CERT   name of the SSL certificate file to use for  establishing a secure connection.
  --ssl-key=SSL_KEY     name of the SSL key file to use for establishing a secure connection.
  --ssl=SSL                   specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).
  -v, --verbose             control how much information is displayed. e.g., -v =  verbose, -vv = more verbose, -vvv = debug
  -q, --quiet                  turn off all messages for quiet execution.

The mysqlrplms utility follows these assumptions [or] pre-requisites to use mysqlrplms utility :

• All servers have GTIDs enabled.
• There are no conflicts between transactions from different sources/masters.
• For example, there are no updates to the same object from multiple masters.
• Replication is asynchronous.

Helpful Hints of mysqlrplms
-------------------------------
  - The default report value is 'health'.
    This value can be changed with the --report-values option. It can be 'health', 'gtid' or 'uuid'. Multiple values can be used separated by commas.

  - The default output for reporting health is 'grid'.
    This value can be changed with the --format option. It can be 'grid', 'tab', 'csv' or 'vertical' format.

  - The default interval for reporting health is 15 seconds.
    This value can be changed with the --interval option.

  - The default interval for switching masters is 60 seconds.
    This value can be changed with the --switchover-interval option.


Related MySQL Articles: 

MySQL mysqlbinlog utility

mysql binlog utility cheatsheet

mysql bin log --> binary log processing utility in MySQL database

mysqlbinlog log_file

mysqlbinlog -v log_file
mysqlbinlog -vv log_file

mysqlbinlog logfile_1 logfile_2 ... logfile_n > out.sql
mysqlbinlog logfile_1 logfile_2 ... logfile_n | mysql

mysqlbinlog mysql-binlog.003510 | more

/usr/local/mysql/bin/mysqlbinlog --stop-datetime="2020-11-10 05:00:00" /home/mysql/binlog/mysql-bin.000033

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2020-09-21 16:30:00" /home/mysql/binlog/mysql-bin.000006 /home/mysql/binlog/mysql-bin.000008 /home/mysql/binlog/mysql-bin.000009 | /usr/local/mysql/bin/mysql -u mysqldba -p test

mysqlbinlog -v --base64-output=decode-rows mysql-bin-log.00052

mysqlbinlog --base64-output=decode-rows --verbose /logs/mysql-bin-log.00052 > 52.log

mysqlbinlog --no-defaults --base64-output=decode-rows --verbose --verbose /logs/dba-mysql-bin.0000395 > 395.log

mysqlbinlog --base64-output=never mysqld-bin.000001

mysqlbinlog --base64-output=always mysqld-bin.000001

mysqlbinlog --base64-output=auto mysqld-bin.000001

sudo mysqlbinlog --start-position=3214331 /mysql/mysql_logs/mysql-bin.00920 | more
mysqlbinlog --start-position=12330 mysql-binlog.001002 mysql-binlog.001003 mysql-binlog.001004 | mysql --host=host_name -u root -p

mysqlbinlog --stop-position=15028 mysqld-bin.000001 > upto-15028.out

mysqlbinlog -j 15028 mysqld-bin.000001 > from-15028.out

mysqlbinlog --start-datetime="2021-08-16 15:00:00" mysqld-bin.000001 

mysqlbinlog --stop-datetime="2021-08-16 19:00:00" mysqld-bin.000001 

mysqlbinlog /mysql/binlog/mysql-bin-log.000999 | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | sort | uniq -c | sort -nr

mysqlbinlog --read-from-remote-server --host=host_name --raw mysql-binlog.000130 mysql-binlog.000131 mysql-binlog.000132

mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never 
mysql-binlog.000190

mysqlbinlog -R -h 192.168.101.2 -p mysqld-bin.000001

mysqlbinlog -D mysqld-bin.000001

mysqlbinlog --disable-log-bin mysqld-bin.000001 

mysqlbinlog --debug-check mysqld-bin.000001

mysqlbinlog --debug-info mysqld-bin.000001

mysqlbinlog -o 10 mysqld-bin.000001

mysqlbinlog -r output.log mysqld-bin.000001

mysqlbinlog --no-defaults -vvv --base64-output=decode-rows --include-gtids='bd8eb8-adea-2839a:112' /var/lib/mysql/mysql-bin.0004;

mysqlbinlog --no-defaults -vvv --base64-output=decode-rows --include-gtids='c21907b4-f9d2-11eb-9b8a-080027272b51:123' binlog.000014;

Usage: mysqlbinlog [options] log-files

  -?, --help          Display this help and exit.

  --base64-output=name  Determine when the output statements should be base64-encoded BINLOG statements: 'never' disables it and works only for binlogs without row-based events; 'decode-rows' decodes row events into commented pseudo-SQL statements if the --verbose option is also given; 'auto' prints base64 only when necessary (i.e., for row-based events and format description events).  If no --base64-output[=name] option is given at all, the default is 'auto'.

  --bind-address=name IP address to bind to.

  --character-sets-dir=name  Directory for character set files.

  -d, --database=name List entries for just this database (local log only).

  --rewrite-db=name    Rewrite the row event to point so that it can be applied to a new database

  -#, --debug[=#]      This is a non-debug version. Catch this and exit.

  --debug-check        This is a non-debug version. Catch this and exit.

  --debug-info        This is a non-debug version. Catch this and exit.

  --default-auth=name Default authentication client-side plugin to use.

  -D, --disable-log-bin  Disable binary log. This is useful, if you enabled --to-last-log and are sending the output to the same MySQL server. This way you could avoid an endless loop. You would also like to use it when restoring after a crash to avoid duplication of the statements you already have. NOTE: you will need a SUPER privilege to use this option.

  -F, --force-if-open Force if binlog was not closed properly.  (Defaults to on; use --skip-force-if-open to disable.)

  -f, --force-read    Force reading unknown binlog events.

  -H, --hexdump        Augment output with hexadecimal and ASCII event dump.

  -h, --host=name      Get the binlog from server.

  -i, --idempotent    Notify the server to use idempotent mode before applying Row Events

  -l, --local-load=name           Prepare local temporary files for LOAD DATA INFILE in the specified directory.

  -o, --offset=#      Skip the first N entries.

  -p, --password[=name]  Password to connect to remote server.

  --plugin-dir=name    Directory for client-side plugins.

  -P, --port=#        Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).

  --protocol=name      The protocol to use for connection (tcp, socket, pipe, memory).

  -R, --read-from-remote-server  Read binary logs from a MySQL server. This is an alias for read-from-remote-source=BINLOG-DUMP-NON-GTIDS.

  --read-from-remote-master=name This option is deprecated and will be removed in a future version. Use read-from-remote-source instead.

  --read-from-remote-source=name  Read binary logs from a MySQL server through the COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by setting the option to either BINLOG-DUMP-NON-GTIDS or BINLOG-DUMP-GTIDS, respectively. If --read-from-remote-source=BINLOG-DUMP-GTIDS is combined with --exclude-gtids, transactions are filtered out on the source, to avoid unnecessary network traffic.

  --raw               Requires -R. Output raw binlog data instead of SQL statements, output is to log files.

  -r, --result-file=name   Direct output to a given file. With --raw this is a prefix for the file names.

  --server-id=#        Extract only binlog entries created by the server having the given id.

  --server-id-bits=#  Set number of significant bits in server-id

  --set-charset=name  Add 'SET NAMES character_set' to the output.

  -s, --short-form    Just show regular queries: no extra info and no row-based events. This is for testing only, and should not be used in production systems. If you want to suppress base64-output, consider using --base64-output=never instead.

  -S, --socket=name  The socket file to use for connection.

  --server-public-key-path=name  File path to the server public RSA key in PEM format.

  --get-server-public-key    Get server public key

  --ssl-mode=name      SSL connection mode.

  --ssl-ca=name        CA file in PEM format.

  --ssl-capath=name    CA directory.

  --ssl-cert=name      X509 cert in PEM format.

  --ssl-cipher=name    SSL cipher to use.

  --ssl-key=name      X509 key in PEM format.

  --ssl-crl=name      Certificate revocation list.

  --ssl-crlpath=name  Certificate revocation list path.

  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3

  --ssl-fips-mode=name   SSL FIPS mode (applies only for OpenSSL); permitted values are: OFF, ON, STRICT

  --tls-ciphersuites=name   TLS v1.3 cipher to use.

  --start-datetime=name    Start reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly).

  -j, --start-position=#  Start reading the binlog at position N. Applies to the first binlog passed on the command line.

  --stop-datetime=name  Stop reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should  probably use quotes for your shell to set it properly).

  --stop-never        Wait for more data from the server instead of stopping at the end of the last log. Implicitly sets --to-last-log but instead of stopping at the end of the last log it continues to wait till the server disconnects.

  --stop-never-slave-server-id=#  The server_id that is reported when connecting to a source server when using --read-from-remote-server --stop-never. This option is deprecated and will be removed in a future version. Use connection-server-id instead.

  --connection-server-id=#  The server_id that will be reported when connecting to a source server when using --read-from-remote-server. This option cannot be used together with stop-never-slave-server-id.

  --stop-position=#    Stop reading the binlog at position N. Applies to the last binlog passed on the command line.

  -t, --to-last-log    Requires -R. Will not stop at the end of the requested binlog but rather continue printing until the end of the last binlog of the MySQL server. If you send the output to the same MySQL server, that may lead to an endless loop.

  -u, --user=name     Connect to the remote server as username.

  -v, --verbose        Reconstruct pseudo-SQL statements out of row events.  -v adds comments on column data types.

  -V, --version        Print version and exit.

  --open-files-limit=#    Used to reserve file descriptors for use by this program.

  -c, --verify-binlog-checksum  Verify checksum binlog events.

  --binlog-row-event-max-size=#  The maximum size of a row-based binary log event in bytes. Rows will be grouped into events smaller than this size if possible. This value must be a multiple of 256.

  --skip-gtids        Do not preserve Global Transaction Identifiers; instead make the server execute the transactions as if they were new.

  --include-gtids=name  Print events whose Global Transaction Identifiers were provided.

  --exclude-gtids=name Print all events but those whose Global Transaction Identifiers were provided.

  --print-table-metadata  Print metadata stored in Table_map_log_event

  -C, --compress      Use compression in server/client protocol.

  --compression-algorithms=name    Use compression algorithm in server/client protocol. Valid values are any combination of 'zstd','zlib','uncompressed'.

  --zstd-compression-level=#  Use this compression level in the client/server protocol, in case --compression-algorithms=zstd. Valid range is between 1 and 22, inclusive. Default is 3.

  --require-row-format  Fail when printing an event that was not logged using row format or other forbidden events like Load instructions or the creation/deletion of temporary tables.

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

The following groups are read: mysqlbinlog client

The following options may be given as the first argument:

--print-defaults        Print the program argument list and exit.

--no-defaults            Don't read default options from any option file, except for login file.

--defaults-file=#        Only read default options from the given file #.

--defaults-extra-file=# Read this file after the global files are read.

--defaults-group-suffix=# Also read groups with concat(group, suffix)

--login-path=#          Read this path from the login file.

Variables (--variable-name=value)

and boolean options {FALSE|TRUE}  Value (after reading options)

--------------------------------- ----------------------------------------

base64-output                     (No default value)

bind-address                      (No default value)

character-sets-dir                (No default value)

database                          (No default value)

rewrite-db                        (No default value)

default-auth                      (No default value)

disable-log-bin                   FALSE

force-if-open                     TRUE

force-read                        FALSE

hexdump                           FALSE

host                              (No default value)

idempotent                        FALSE

local-load                        (No default value)

offset                            0

plugin-dir                        (No default value)

port                              0

read-from-remote-server           FALSE

read-from-remote-master           (No default value)

read-from-remote-source           (No default value)

raw                               FALSE

result-file                       (No default value)

server-id                         0

server-id-bits                    32

set-charset                       (No default value)

short-form                        FALSE

socket                            (No default value)

server-public-key-path            (No default value)

get-server-public-key             FALSE

ssl-ca                            (No default value)

ssl-capath                        (No default value)

ssl-cert                          (No default value)

ssl-cipher                        (No default value)

ssl-key                           (No default value)

ssl-crl                           (No default value)

ssl-crlpath                       (No default value)

tls-version                       (No default value)

tls-ciphersuites                  (No default value)

start-datetime                    (No default value)

start-position                    4

stop-datetime                     (No default value)

stop-never                        FALSE

stop-never-slave-server-id        -1

connection-server-id              -1

stop-position                     18446744073709551615

to-last-log                       FALSE

user                              (No default value)

open-files-limit                  64

verify-binlog-checksum            FALSE

binlog-row-event-max-size         4294967040

skip-gtids                        FALSE

include-gtids                     (No default value)

exclude-gtids                     (No default value)

print-table-metadata              FALSE

compress                          FALSE

compression-algorithms            (No default value)

zstd-compression-level            3

require-row-format                FALSE

Related MySQL Articles:  mysqlimport commands  mysql_config_editor commands

MySQL monitoring tool mysql command's cheatsheet

mysql utility commands

mysql ==> Aurora/MySQL/MariaDB monitoring tool

Usage: mysql [OPTIONS] [database]
mysql -V
mysql --help

mysql -uUsername -pPassword -hHostname -Pport
mysql -p
mysql -u root
mysql -upoweearch  -ppowerdb -h10.145.128.34 -P3310
mysql -usfms -h10.172.45.28 -P3306 -psecret

mysql -ureport  -pbug@report -h10.174.0.37 -P3306 -pbug
mysql -h your.aws-rds-endpoint.aws.com -u academy -p rdsappdb
mysql -u mysqldba -pmysqldba -Bse "show slave status\G"
/usr/bin/mysql -u root -p$PASSWD -e 'SHOW SLAVE STATUS\G'
#mysql --login-path=mydb
mysql -u root -p newdatabase < /mysql/utilitites/newdatabase.sql

mysql -ureport  -pbug@report -h10.174.0.37 -P3306 -e "select * from bug where bug_id='60000'"; > c:\bug\bug60000.doc
mysql -u test --xml -e 'SELECT * FROM t1' > t1.xml
/usr/bin/mysql --login-path=mylogin -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running" 
/usr/local/mysql/bin/mysql -uroot -p --socket=/tmp/mysql2.sock

mysql -u myadmin -p myadmin -h slave.xxxxxxxxxxx.us-west-2.rds.amazonaws.com --ssl=false
mysql -h AWS-RDS-endpoint -u user -p'password' --local-infile=true

MySQL > \s
(root@localhost) [(mysql)]> \s

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

The following commands are supported at the mysql> prompt:
?        (\?) Synonym for `help'.
clear  (\c) Clear command.
connect    (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit      (\e) Edit command with $EDITOR.
ego      (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager    (\n) Disable pager, print to stdout.
notee    (\t) Don't write into outfile.
pager      (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print      (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee      (\T) Set outfile [to_outfile]. Append everything into given outfile.
use        (\u) Use another database. Takes database name as argument.
charset    (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash      Enable automatic rehashing. One doesn't need to use  'rehash' to get table and field completion, but startup and reconnecting may take a longer time. Disable with --disable-auto-rehash. (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect.

  --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width.
  -B, --batch          Don't use history file. Disable interactive behavior. (Enables --silent.)
  --bind-address=name IP address to bind to.
  --character-sets-dir=name     Directory for character set files.
  --column-type-info  Display column type information.
  -c, --comments      Preserve comments. Send comments to the server. The default is --skip-comments (discard comments), enable with --comments.
  -C, --compress      Use compression in server/client protocol.

  -#, --debug[=#]      This is a non-debug version. Catch this and exit.
  --debug-check        Check memory and open file usage at exit.
  -T, --debug-info    Print some debug info at exit.
  -D, --database=name Database to use.
  --default-character-set=name       Set the default character set.
  --delimiter=name    Delimiter to be used.
  --enable-cleartext-plugin     Enable/disable the clear text authentication plugin.
  -e, --execute=name  Execute command and quit. (Disables --force and history  file.)
  -E, --vertical      Print the output of a query (rows) vertically.
  -f, --force          Continue even if we get an SQL error.

  -G, --named-commands    Enable named commands. Named commands mean this program's internal commands; see mysql> help . When enabled, the named commands can be used from any line of the query, otherwise only from the first line, before an enter.  Disable with --disable-named-commands. This option is  disabled by default.
  -i, --ignore-spaces Ignore space after function names.
  --init-command=name SQL Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting.
  --local-infile      Enable/disable LOAD DATA LOCAL INFILE.
  -b, --no-beep        Turn off beep on error.
  -h, --host=name      Connect to host.
  -H, --html          Produce HTML output.
  -X, --xml            Produce XML output.

  --line-numbers      Write line numbers for errors. (Defaults to on; use --skip-line-numbers to disable.)
  -L, --skip-line-numbers     Don't write line number for errors.
  -n, --unbuffered    Flush buffer after each query.
  --column-names      Write column names in results. (Defaults to on; use --skip-column-names to disable.)
  -N, --skip-column-names   Don't write column names in results.
  --sigint-ignore      Ignore SIGINT (CTRL-C).
  -o, --one-database  Ignore statements except those that occur while the default database is the one named at the command line.

  --pager[=name]         Pager to use to display results. If you don't supply an option, the default pager is taken from your ENV variable PAGER. Valid pagers are less, more, cat [> filename], etc. See interactive help (\h) also. This option does not work in batch mode. Disable with --disable-pager. This option is disabled by default.
  -p, --password[=name]     Password to use when connecting to server. If password is not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT,/etc/services, built-in default (3306).
  --prompt=name        Set the mysql prompt to this value.
  --protocol=name      The protocol to use for connection (tcp, socket, pipe, memory).

  -q, --quick          Don't cache result, print it row by row. This may slow  down the server if the output is suspended. Doesn't use history file.
  -r, --raw            Write fields without conversion. Used with --batch.
  --reconnect        Reconnect if the connection is lost. Disable with --disable-reconnect. This option is enabled by default. (Defaults to on; use --skip-reconnect to disable.)
  -s, --silent        Be more silent. Print results with a tab as separator,  each row on new line.
  -S, --socket=name    The socket file to use for connection.

  --ssl              Enable SSL for connection (automatically enabled with  other flags).
  --ssl-ca=name        CA file in PEM format (check OpenSSL docs, implies  --ssl).
  --ssl-capath=name    CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name      X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name    SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-crl=name      Certificate revocation list (implies --ssl).
  --ssl-crlpath=name  Certificate revocation list path (implies --ssl).
  --ssl-verify-server-cert Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by  default.
  --ssl-mode=name      SSL connection mode.

  -t, --table          Output in table format.
  --tee=name          Append everything into outfile. See interactive help (\h) also. Does not work in batch mode. Disable with --disable-tee. This option is disabled by default.
  -u, --user=name      User for login if not current user.
  -U, --safe-updates     Only allow UPDATE and DELETE that uses keys.
  -U, --i-am-a-dummy  Synonym for option --safe-updates, -U.
  -v, --verbose        Write more. (-v -v -v gives the table output format).
  -V, --version        Output version information and exit.

  -w, --wait          Wait and retry if connection is down.
  --connect-timeout=# Number of seconds before connection timeout.
  --max-allowed-packet=# The maximum packet length to send to or receive from server.
  --net-buffer-length=# The buffer size for TCP/IP and socket communication.
  --select-limit=#    Automatic limit for SELECT when using --safe-updates.
  --max-join-size=#    Automatic limit for rows in a join when using --safe-updates.

  --secure-auth        Refuse client connecting to server if it uses old  (pre-4.1.1) protocol.  (Defaults to on; use --skip-secure-auth to disable.)
  --server-arg=name   Send embedded server this as a parameter.
  --show-warnings     Show warnings after every statement.
  --plugin-dir=name   Directory for client-side plugins.
  --default-auth=name    Default authentication client-side plugin to use.

  --histignore=name     A colon-separated list of patterns to keep statements from getting logged into mysql history.
  --binary-mode        By default, ASCII '\0' is disallowed and '\r\n' is translated to '\n'. This switch turns off both features, and also turns off parsing of all client commands except \C and DELIMITER, in non-interactive mode (for input piped to mysql or loaded using the 'source' command). This is necessary when processing output from mysqlbinlog that may contain blobs.
  --server-public-key-path=name  File path to the server public RSA key in PEM format.
  --connect-expired-password  Notify the server that this client is prepared to handle expired password sandbox mode.

Related MySQL Articles: mysqlshow utility commands   mysqlimport commands