MySQL variables store data, label data, and let developers create more accurate and efficient code by turning long and complicated strings of characters into one simple variable. This article will explore user-defined variables. User-defined variables let us execute various data sets with one command and use this data whenever needed. Mastering variables in MySQL is […]
The post MySQL Variables – Definition and Examples appeared first on Devart Blog.
One thing I commonly hear when working with my clients is “I want to change my DDL strategy in order to avoid locking in my database! The last time I used the same old method I ended up in a metadata lock situation!”
I agree that metadata locks can be painful, but unfortunately, it’s completely unavoidable, and changing from one tool to another won’t help with this. That said, it’s still worth it to examine how metadata locks work and what the impact is for each of the common tools and processes. In doing so we will see that all these tools will require metadata locks, but knowing more about how they work and how the use locking can help us determine the right tool for your specific use case.
Any time you make a change to a table a metadata lock is needed to ensure consistency between the table itself and MySQL’s data dictionary. In order for MySQL to establish this lock it has to wait for any query against the table in …
[Read more]What is dual password in MYSQL and how it works was already covered by my colleague Brian Sumpter in Using MySQL 8 Dual Passwords.
However, let me do a brief recap here about it.
Dual password is the MySQL mechanism that allows you to keep two passwords active at the same time. This feature is part of a more extended set of password management features implemented in MySQL 8 to enforce better security and secrets management, like:
The most important and requested features are the password expiration and …
[Read more]Vitess is a solution that allows you to infinitely scale MySQL while providing clients and apps with a single logical view of the fleet of MySQL instances comprising any number of Keyspaces and Shards. Vitess also provides the cluster and data management tools that make it possible to manage a massive cluster and perform complex workflows using VReplication, such as: Moving tables into Vitess or between keyspaces Resharding to adjust to changes in data size and load Materialized views and rollups for data analytics and data locality Online schema changes that are trackable, cancellable, revertible, and retryable Why a Diff Tool?
Now that OpenLampTech is one year old, I have a point to prove. That I can consistently provide the MySQL, PHP, and LAMP stack communities with a valuable newsletter publication. Challenge accepted. This week’s issue is full of great content so give it a read!
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
In OpenLampTech issue #53, we have some fantastic content covering:
AlmaLinux doesn’t natively support MySQL Workbench but these notes will help you install it. The great news is that MySQL Workbench works perfectly once you’ve installed all the dependent libraries. It’ll look like the following:
Disclaimer of sorts:
AlmaLinux is an open-source, community-driven project that intends to fill the gap left by the demise of the CentOS stable release. AlmaLinux is a 1:1 binary compatible fork of RHEL® 9 and it is built by the AlmaLinux OS Foundation as a standalone, completely free OS. The AlmaLinux OS Foundation will support future RHEL® releases by updating AlmaLinux. Ongoing development efforts are governed by the members of the community.
You can download MySQL Workbench from the following website:
https://dev.mysql.com/downloads/workbench
When you open this page, select the Red Hat Enterprise Linux 9 / Oracle Linux 9 (x86, 64-bit), RPM Package from the dropdown …
[Read more]When you think about IOPS, you probably think about writes because MySQL write I/O has a long tradition of optimization, benchmarking, new algorithms, new storage engines, and so forth. There’s no shortage of material on MySQL write I/O; just two examples from Percona are Scaling IO-Bound Workloads for MySQL in the Cloud and Tuning MySQL/InnoDB Flushing for a Write-Intensive Workload. But in this short blog post I highlight two other, less common aspects of MySQL I/O: reads and surprises.
Taking a MySQL backup using Percona XtraBackup (PXB) consists of basically two steps: 1) take the backup and 2) prepare the backup.
Briefly speaking, taking a backup means that PXB will copy all of the files from your instance and transfer them to another location. While it does the copy, it spawns a thread that will monitor the InnoDB redo log (WAL/transaction log) and store a copy of all the new redo log entries generated by the server during the backup.
Before restoring the backup into a new instance, users have to prepare the backup. This operation is the same as the crash recovery steps that the MySQL server does after a server crash.
It consists of reading all the redo log entries into memory, categorizing them by space id and page id, reading the relevant pages into memory, and checking the LSN number on the page and on …
[Read more]What is dual password in MYSQL and how it works was already covered by my colleague Brian Sumpter here (https://www.percona.com/blog/using-mysql-8-dual-passwords/).
However let me do a brief recap here about it.
Dual password is the MySQL mechanism that allows you to keep two passwords active at the same time. This feature is part of a more extended set of Password management features implemented in MySQL 8 to enforce better security and secrets management, like:
The most important and requested features …
[Read more]Having a backup of your database is like insurance, you have to pay a monthly price to ensure you have a service available when you need to. When talking about backups, the storage required to keep your backups is what comes into factor when talking about price, the bigger your backup, or the bigger the retention period, the more it will cost.
Compressing your backups is a common practice to reduce this cost. Currently, Percona XtraBackup (PXB) has support for two compression algorithms: quicklz (which is an abandoned project and will soon be deprecated in PXB) and LZ4.
Today we are glad to introduce support for a new compression algorithm in Percona XtraBackup 8.0.30 – …
[Read more]