Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. Introduction to InnoDB ClusterIf you have not heard about MySQL InnoDB Cluster MySQL, InnoDB Cluster is a built-in high-availability solution for MySQL. The key benefit over old high-availability solutions is that InnoDB Cluster is built into MySQL and supported on all platforms where MySQL is supported.The key components of MySQL InnoDB Cluster:- MySQL Group Replication- MySQL Shell- MySQL RouterMySQL Group Replication is a plugin that makes sure that; data is distributed to all nodes, conflicts are handled and also handle recovery.MySQL Shell makes is easy to configure and administer your InnoDB Cluster.MySQL Router is the last part of InnoDB cluster, it's a lightweight middleware that provides transparent routing between the application and back-end MySQL Servers part of group replication.If you want to get started with InnoDB Cluster:- https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-userguide.html- https://github.com/wwwted/MySQL-InnoDB-Cluster-3VM-Setup- https://lefred.be/content/category/mysql/innodb-cluster/Clone a test/development environment from InnoDB ClusterMost users need some way to periodically refresh test and development environments from production. In the past many users used traditional replication, setting up a MySQL slave on a file-system on-top of LVM to leverage snapshots to create new test/developer environments.For InnoDB Cluster we can use MySQL Clone (available by MySQL 8.0.l7 or later) to creating a test or development environment much easier.Steps needed on InnoDB Cluster (create a dedicated clone user for the donor):CREATE USER clone_user@'%' IDENTIFIED BY "clone_password";GRANT BACKUP_ADMIN ON *.* to clone_user@%;GRANT SELECT ON performance_schema.* TO clone_user@%;GRANT EXECUTE ON *.* to clone_user@%;You might limit the clone user to only be able to connect from some sub-net and not from all host (%). The InnoDB Cluster must run MySQL 8.0.17 or later.Next we need to create the test or development server.1) Start a new MySQL instance (using MySQL 8.0.17 or later).2) Provision data using clone:INSTALL PLUGIN CLONE SONAME "mysql_clone.so";INSTALL PLUGIN group_replication SONAME 'group_replication.so';SET GLOBAL clone_valid_donor_list = "10.0.0.17:3306";CREATE USER clone_user@localhost IDENTIFIED BY "clone_password";GRANT CLONE_ADMIN ON *.* to clone_user@localhost;SET global log_error_verbosity=3;CLONE INSTANCE FROM clone_user@10.0.0.17:3306 identified by "clone_password";.. after restart disable GR plugin (lots of errors in error log)UNINSTALL PLUGIN group_replication;There are some controls in the MySQL Server that forces us to load the group replication plugin prior to executing the clone command. If you do not load the group replication plugin you will get an error like: ERROR 3870 (HY000): Clone Donor plugin group replication is not active in RecipientOf course, the final step will be to clean/wash the data before handing over the MySQL instance for test or development, this step is not covered by this blog but using our masking and De-Identification can be used to solve this step ;)I have tested above procedures using MySQL 8.0.18.Happy Clustering!
  2. In an LSM deletes are fast for the deleter but can make queries that follow slower. The problem is that too many tombstones can get in the way of a query especially a range query.A tombstone must remain in the LSM tree until any keys it deletes have been removed from the LSM. For example, if there is a tombstone for key "ABC" on level 1 of the LSM tree and a live value for that key on level 3 then that tombstone cannot be removed. It is hard to make the check (does live key exist below me) efficient.I haven't read much about optimizing for tombstones in an LSM not named RocksDB. Perhaps I have not tried hard to find such details. Maybe this is something that LSM engine developers should explain more in public.Confirming whether a tombstone can be droppedThis is based on code that I read ~2 years ago. Maybe RocksDB has changed today.Tombstones are dropped during compaction. The question is how much work (CPU and IO) you are willing to spend to determine whether a tombstone can be dropped. LevelDB and RocksDB favor performance over exactness. By this I mean they spend less CPU and no IO on the "can I drop this tombstone" check. The check is simple today. If there is an LSM level (or sorted run) below (older) that has a key range which overlaps the tombstone key then the tombstone won't be dropped. And in most workloads this means that tombstones aren't dropped until they reach the max LSM level -- because there usually is overlap.An LSM could spend a bit more CPU and check bloom filters for the levels that overlap with the tombstone. That might allow tombstones to be dropped earlier. An even more expensive check would be to use more CPU and possibly IO to confirm whether the level that overlaps with the tombstone really has that key. This can make compaction much slower.Fast pathThe SingleDelete API in RocksDB makes it easier to drop tombstones. If you respect what the API requires then tombstones can be dropped quickly -- without spending IO or CPU. SingleDelete makes it easy to drop tombstones for a given key when those tombstones meet during compaction. They don't do anything for the case above where the tombstone is on one level and the live key might be on a lower level.MyRocks magicMyRocks has some clever code that does extra work to remove tombstones from an SST when the SST has too many tombstones. Configuration options for this are mentioned in this post. Percona has some docs on rocksdb_compaction_sequential_deletes. And I am sure there is a slide deck from my favorite MyRocks expert. Maybe he will share that with me.
  3. In my previous post (Puzzled by MySQL Replication), I describe a weird, but completely documented, behavior of replication that had me scratching my head for hours because it was causing data corruption.  I did not give too many details then as I also wanted allowing you to scratch your head if you wished.  In this post, I describe this behavior in more details. But first I need to apologize to
  4. We will have a look at a new feature in MySQL 8.0 called binlog encryption. This feature is available from the MySQL version 8.0.14 or above. Our previous blogs discussed about table space encryption in MySQL and Percona servers. In Mydbops, we are giving high importance about achieving security compliances. The binary log records changes made to the databases so that it can be used to replicate the same to the slaves and also for the point in time recovery (PITR). So, it means that if someone has access to the binary logs, they can reproduce our entire database in many forms. As a DBA, we need to make sure that the binary log files are protected from users who are having access to the file system and also, log files need to be encrypted to follow the security compliance requirements by some clients. These new features came as a rescue to satisfy those requirements. Now we will have a look at how to use and maintain it. Note: To explore this feature, I have installed MySQL 8.0.18 in our testing environment. Below are the topics that we are going to discuss in this blog: Enabling Encryption Rotating Key Manually High Level Architecture To access the encrypted binary log To decrypt the encrypted binary log Enabling Encryption: Let’s start this by enabling the binlog encryption. Let us list down the current binary logs and their status.  mysql> show binary logs; +---------------+-----------+-----------+ | Log_name      | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 |       474 | No | +---------------+-----------+-----------+ 1 row in set (0.02 sec) MySQL Server has one binary log and it is not encrypted it is not encrypted by default. To proceed further, make sure you are having SUPER or BINLOG_ENCRYPTION_ADMIN privilege for your user to enable the encryption online.  mysql> show global variables like 'binlog_encryption'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | binlog_encryption | OFF   | +-------------------+-------+ 1 row in set (0.00 sec) mysql> set global binlog_encryption=on; Query OK, 0 rows affected (0.18 sec) mysql> show global variables like 'binlog_encryption'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | binlog_encryption | ON    | +-------------------+-------+ 1 row in set (0.17 sec) mysql> show binary logs; +---------------+-----------+-----------+ | Log_name      | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 |       497 | No | | binlog.000002 |       199 | No | | binlog.000003 |       667 | Yes | +---------------+-----------+-----------+ 3 rows in set (0.03 sec) Now, we can observe the latest binary log is encrypted. If the encrypted binary logs were the only one to be present in the server we can safely purge(remove) them.  mysql> purge binary logs to 'binlog.000003'; Query OK, 0 rows affected (0.07 sec) mysql> show binary logs; +---------------+-----------+-----------+ | Log_name      | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000003 |       667 | Yes | +---------------+-----------+-----------+ 1 row in set (0.00 sec) Currently, the server has encrypted binary logs. Also, we can observe the increase in the size of the encrypted binary log (empty) when compared with unencrypted ones. It is because of the 512 bytes encrypted header and the header is never replicated.  Rotating Encryption Key: The encryption of binary logs is made and it is time to rotate the encryption key. This must be done periodically to comply with security compliances. We can rotate the encryption key manually.  mysql> alter instance rotate binlog master key; Query OK, 0 rows affected (0.36 sec) mysql> show binary logs; +---------------+-----------+-----------+ | Log_name      | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000003 |       711 | Yes | | binlog.000004 |       667 | Yes | +---------------+-----------+-----------+ 2 rows in set (0.00 sec) The below process is performed by the server while rotating the key. The binary and relay log files are flushed (rotated). A new binary log encryption key is generated with the next available sequence number, stored on the keyring, and used as the new binary log master key. Binary log encryption keys that are no longer in use for any files after the re-encryption process are removed from the keyring. Also, we are having one more variable binlog_rotate_encryption_master_key_at_startup which ensures the binary log encryption key has to be rotated at server startup. High-Level Architecture: Binlog Encryption is designed to use two-tier encryption.  File Passwords Replication Encryption key  The File Password is used to encrypt/decrypt binary log content and Replication Encryption Key is used to encrypt/decrypt the file password in the encrypted binary log header. A single replication encryption key may be used to encrypt/decrypt many binary and relay log files passwords, while a file password is intended to encrypt/decrypt a single binary or relay log file. Multiple file passwords rely on the single encryption key whereas file password is used for the individual log files. Okay, what happens if the encryption key REK1 is rotated? The server generates a new replication encryption key REKj and it iterates over all encrypted log files to re-encrypt their password (Iterate from the last file to the first file ) by overwriting the encrypted file header with the new one. So, my new encryption key can decrypt all the available file passwords. Compare the encrypted log file with unencrypted one: The only difference is the binlog header.  Here is the detailed header of the encrypted binlog: Magic Number : It is needed to distinguish from an encrypted to an unencrypted binary log files. An encrypted binlog file has 0xFD62696E . An unencrypted binlog file : 0xFE62696E Form an encrypted log file: [root@testing mysql]# hexdump binlog.000003 -n 4 -e '/1 "%02X"' FD62696E Form an unencrypted log file: [root@gr1 mysql]# hexdump binlog.000001 -n 4 -e '/1 "%02X"' FE62696E Replication logs encryption version : The current value is 1. It might change in the future. Replication encryption key ID:  The key ID of the replication master key that encrypted the file password. It is a combination of MySQLReplicationKey, UUID, and SEQ_NO. It can be identified in the following format. MySQLReplicationKey_{UUID}_{SEQ_NO} Where MySQLReplicationKey is the prefix       UUID is the MySQL server’s UUID that generated the key           SEQ_NO is the global replication master key sequence number. Here is an example: |<-----Keyword------|<---------------UUID--------------->|SEQ_NO| Keyring key ID for 'binlog.000003' is 'MySQLReplicationKey_d1deace2-24cc-11ea-a1db-0800270a0142_2' Now Let us try rotating the encryption key. mysql> alter instance rotate binlog master key; Query OK, 0 rows affected (0.12 sec) After successful rotation, the encryption key is  Keyring key ID for 'binlog.000003' is 'MySQLReplicationKey_d1deace2-24cc-11ea-a1db-0800270a0142_3' We can observe that My SEQ_NO is incremented by 1. It will keep going on all the encryption key rotations. Encrypted File Password:  It is used to generate the key for encrypting/decrypting the binary log content. IV for encrypting file password:          It is used with the replication master key to encrypt the file password. Padding:         Unused header space will be filled with 0. How to access the encrypted binary log using mysqlbinlog utility ? Sometimes, It is necessary to decode the binary log for PITR / to get to know the write pattern. Using mysqlbinlog, we can’t directly decode the encrypted binlog as it is not having access to the keyring file. For example: [root@testing mysql]# mysqlbinlog binlog.000003 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ERROR: Reading encrypted log files directly is not supported. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@testing mysql]# But it is possible to get it by making a request to the MySQL server [root@testing mysql]# mysqlbinlog -R --host localhost --user root binlog.000003 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #191222 15:28:57 server id 1  end_log_pos 124 CRC32 0xcb35f1c9 Start: binlog v 4, server v 8.0.18 created 191222 15:28:57 BINLOG ' uYv/XQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgHJ8TXL '/*!*/; # at 124 #191222 15:28:57 server id 1  end_log_pos 155 CRC32 0xb7909c71 Previous-GTIDs # [empty] # at 155 #191223  1:55:30 server id 1  end_log_pos 199 CRC32 0x46148c5f Rotate to binlog.000004  pos: 4 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; How to decrypt an encrypted binary log file? The manual decryption of encrypted binary log is possible when the value of the key that encrypted its file password is known. We can use this amazing post https://mysql.wisborg.dk/2019/01/28/automatic-decryption-of-mysql-binary-logs-using-python/ to decrypt the binary log. To use this script, we need only have access to the keyring file. Note: Even if we do not have enabled binary log on the server, we can still use it to encrypt relay log files on the slave servers.  ]
  5. We know the MySQL Shell is the advanced client tool for communicate to the MySQL server . MySQL Shell has lot of features like InnoDB Cluster control , InnoDB ReplicaSet, MySQL Shell utilities , MySQL server management etc … Today I came to know, MySQL shell helps lot in monitoring as well ( query, threads, resource consumption , locking ) . In this blog I am going to explain how to use the MySQL Shell for monitor your server . MySQL Shell provides two hot commands \show and \watch for monitor the MySQL server and report generating purpose . \show : Execute the report with the provided options \watch : Execute the report in loop with provided options \show with thread example : \show with query example : You can execute any query within the double quotes . \show with threads example : As I showned in the screenshot there are two types in threads . –foreground –background Similarly you can use the \watch command to execute the reports in loop . All good, now I am going to show some examples, How to find the top three MySQL threads which consuming more memory for the particular user ? tid : thread idcid : connection idmemory : the number of bytes allocated by the threadstarted : time when thread started to be in its current stateuser : the user who issued the statement, or NULL for a background thread cmd : \show threads –foreground -o tid,cid,user,memory,started –order-by=memory –desc –where “user = ‘app_user'” –limit=3 2. How to find the blocking and blocked threads ? Consider I started the below transaction in a terminal 1 but not committed , At terminal 2, I am trying to update the same value , root@localhost:sakthi>update sakthi_j set id=10; now, lets execute the \show with the required options , tidle : the time the thread has been idlenblocked : the number of other threads blocked by the threadnblocking : the number of other threads blocking the thread After commit the transaction there is no blocking transactions . Make sense ? cmd : \show threads –foreground -o tid,cid,tidle,nblocked,nblocking,digest,digesttxt –where “nblocked=1 or nblocking=1” 3. How to find the top 10 threads, which used huge IO events ? ioavgltncy : the average wait time per timed I/O event for the threadioltncy : the total wait time of timed I/O events for the threadiomaxltncy : the maximum single wait time of timed I/O events for the threadiominltncy : the minimum single wait time of timed I/O events for the threadnio : the total number of I/O events for the thread cmd : \show threads –foreground -o tid,ioavgltncy,ioltncy,iomaxltncy,iominltncy,nio –order-by=nio –desc  –limit=10 Like this way, you can find more details about query statistics , JOIN informations , system resource utilisation etc … I hope this blog will helps someone who is looking MySQL Shell for effectively handle the MySQL server . Will come up with my next blog soon … Thanks !!!