MySQL

MySQL is an open-source relational database management system (RDBMS), it’s widely used and part of the popular LAMP/LEMP stacks. The ... Read more The post How to Install MySQL Database on Ubuntu 20.04 appeared first on RoseHosting.

The new MyDumper 0.11.3 version, which includes many new features and bug fixes, is now available.  You can download the code from here. We are very proud to announce that we were able to achieve the two main objectives for the milestone ZSTD and ... [More] Stream support!  We added four packages with ZSTD support because not all the distributions have support for v1.4 or higher. Package libzstd is required to use ZSTD compression. ZSTD Bullseye package is only available with libraries for Percona Server for MySQL 8.0. There are two main use cases for the Stream functionality: Importing while you are exporting Remote backups The drawback is that it relies on the network throughput as we are using a single thread to send the files that have been closed. We are going to explain how this functionality works in another blog post! Enhancement: [ Enhancement ] Backup and restore from stream #403 #110 #100 Adding support for ZSTD #336 #134 Bug/Fixes: Escape double and float values because of -0 #326 #30 Fixing const issues after merge zstd #444 #134 WITH_ZSTD needs to be set before being used #442 Adding better error handling #441 #440 Revert #426 #433 Database schema creation control added #432 #431 Adding anonymized function [Phase I] #427 #428 Fixing comment error log in restore_data_in_gstring_from_file #426 Adding LC_ALL to allow multi-byte parameters #415 #199 Needs to notify main thread to go ahead when “–tables-list” is used without “-B”! #396 #428 Refactoring: Fixing headers #425 sync_before_add_index is not needed anymore #416 Using generic functions to build filenames #414 Documentation: Modify the log of error #430 Fixing readme #420 Warning for inconsistencies using multisource #417 #144 docs: add brew build dependencies instruction #412 [Document] add example #408 #407 Question Addressed: [BUG] Can’t connect to MySQL server using host and port. #434 Could not execute query: Unknown error #335 Download MyDumper 0.11.3 Today!   MyDumper has its own merch now, too. By shopping for the merch you contribute to the project. Check it out!  [Less]

The new MyDumper 0.11.3 version, which includes many new features and bug fixes, is now available.  You can download the code from here. We are very proud to announce that we were able to achieve the two main objectives for the milestone ZSTD and ... [More] Stream support!  We added four packages with ZSTD support because not all the distributions have support for v1.4 or higher. Package libzstd is required to use ZSTD compression. ZSTD Bullseye package is only available with libraries for Percona Server for MySQL 8.0. There are two main use cases for the Stream functionality: Importing while you are exporting Remote backups The drawback is that it relies on the network throughput as we are using a single thread to send the files that have been closed. We are going to explain how this functionality works in another blog post! Enhancement: [ Enhancement ] Backup and restore from stream #403 #110 #100 Adding support for ZSTD #336 #134 Bug/Fixes: Escape double and float values because of -0 #326 #30 Fixing const issues after merge zstd #444 #134 WITH_ZSTD needs to be set before being used #442 Adding better error handling #441 #440 Revert #426 #433 Database schema creation control added #432 #431 Adding anonymized function [Phase I] #427 #428 Fixing comment error log in restore_data_in_gstring_from_file #426 Adding LC_ALL to allow multi-byte parameters #415 #199 Needs to notify main thread to go ahead when “–tables-list” is used without “-B”! #396 #428 Refactoring: Fixing headers #425 sync_before_add_index is not needed anymore #416 Using generic functions to build filenames #414 Documentation: Modify the log of error #430 Fixing readme #420 Warning for inconsistencies using multisource #417 #144 docs: add brew build dependencies instruction #412 [Document] add example #408 #407 Question Addressed: [BUG] Can’t connect to MySQL server using host and port. #434 Could not execute query: Unknown error #335 Download MyDumper 0.11.3 Today! [Less]

Issue #3 of OpenLamp.tech, the newsletter for PHP/MySQL developers, is now available. This is the deep-dive/featured piece edition, 5 MySQL String Functions You Should Know, and you don’t want to miss it… Image by Harish Sharma from Pixabay  ... [More] Deep Dive – Feature Edition On a monthly basis, in addition to the weekly curated content, OpenLamp.tech features a deep-dive or feature piece on one of the technologies I am learning and interested in (SQL, PHP, CodeIgniter, WordPress) This week’s issue is jam-packed full of great content! 5 MySQL String Functions You Should Know – featured piece.Curated content on: MySQL transactions and isolation levels, the role of PHP in WordPress, a mega-list of books for learning PHP development.Additional mentions on interesting newsletters, freelancer tools, and much more.Don’t wait on me each week… Before you jump to reading this latest issue, why not sign up (it’s free) and have it delivered to your inbox each week? It’s super-easy to join: The Newsletter for PHP and MySQL Developers By signing up, you now don’t have to wait on me to cross-post the latest issue of OpenLamp.tech. If you enjoy my writing and blog and want to support my work, you can drop some spare change in my Tip Jar. Thank you so much! Tip Jar How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post OpenLamp.tech issue #3 – 5 MySQL String Functions You Should Know appeared first on Digital Owl's Prose. [Less]

Issue #3 of OpenLamp.tech, the newsletter for PHP/MySQL developers, is now available. This is the deep-dive/featured piece edition, 5 MySQL String Functions You Should Know, and you don’t want to miss it… Image by Harish Sharma from Pixabay  ... [More] Deep Dive – Feature Edition On a monthly basis, in addition to the weekly curated content, OpenLamp.tech features a deep-dive or feature piece on one of the technologies I am learning and interested in (SQL, PHP, CodeIgniter, WordPress) This week’s issue is jam-packed full of great content! 5 MySQL String Functions You Should Know – featured piece.Curated content on: MySQL transactions and isolation levels, the role of PHP in WordPress, a mega-list of books for learning PHP development.Additional mentions on interesting newsletters, freelancer tools, and much more.Don’t wait on me each week… Before you jump to reading this latest issue, why not sign up (it’s free) and have it delivered to your inbox each week? It’s super-easy to join: The Newsletter for PHP and MySQL Developers By signing up, you now don’t have to wait on me to cross-post the latest issue of OpenLamp.tech. If you enjoy my writing and blog and want to support my work, you can drop some spare change in my Tip Jar. Thank you so much! Tip Jar How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post OpenLamp.tech issue #3 – 5 MySQL String Functions You Should Know appeared first on Digital Owl's Prose. [Less]

We received today the notification for FOSDEM’s organization that the MySQL DevRoom is accepted for the 2022’s edition ! The MySQL DevRoom will take place on February 6th, 2022. It’s a Sunday. Like previous edition, FOSDEM will be an online ... [More] event. Due to the very short deadlines, this year an external committee cannot be created to validate, sort and vote for the proposed talks. We will propose the best agenda respecting the ideas and precepts that have made our MySQL DevRoom one of the most popular room of FOSDEM for so many years. The MySQL Developer room focuses on MySQL and its Ecosystem. The call for papers for the MySQL DevRoom is now officially open ! Same rules as last year: For obvious reasons, this year the event will not take place in a physical location but online.The reference time will be Brussels local time (CET).Talks will be pre-recorded, and streamed during the event – Before proposing a session, please make sure you will have the facilities (camera, microphone, network) and time to prepare, upload and review a recording during the first two weeks of January if your proposal is accepted.Q&A session will be live after your talk.A platform will be provided for people watching to join a common chat.A platform will be provided for people watching to submit questions.These rules may change as they are not yet confirmed by FOSDEM’s organization. Key dates: Because creating such event is not easy, because of the indecisive situation, etc… we are now bound by time. Submission deadline: 31st December 2021Announcement of selected talks: as soon as possibleSubmission of recorded talks: to be determinedDevroom date: Sunday 6th February 2022How to submit: For this edition, we decided to have a mix of sessions. We are accepting talks of 15, 25 and 40 minutes, with time for questions afterward. The Q&A session will be live. Please, don’t forget to specify the duration in your submission. You might be aware that the usual standard length for MySQL Devroom is 25 minutes and the majority of accepted talks will certainly be in that range but we are open to exception (explain why in your submission). All submissions have to be made in the Pentabarf event planning tool:penta.fosdem.org/submission/FOSDEM22 Please note that currently, FOSDEM didn’t officially open the submissions, so the link won’t allow you to submit anything. Please be patient, we will inform you as soon as the submission from is available. While creating your proposal, please provide the title of your talk, a short abstract, some information about yourself (eg. name, bio, photo, contact information, but remember that your profile might be already stored in Pentabarf). It’s very important that you select the right track “MySQL devroom” and don’t forget to specify the duration. And as usual, marketing and sales speeches are not welcome. Please focus on the engineering side, the operations and of course the developers. We want to hear how you are hacking MySQL and develop around it ! Recording of Talks: The talks in the MySQL developer room as in other rooms must be pre-recorded. Speakers have to submit a recording of their talk to the devroom in Pentabarf (if the talk is accepted and when recorded, not during the proposal) and be available for Q&A during the event. By participating in the event you agree to the publication of your recordings, slides and other content provided under the same license as all FOSDEM content (CC-BY). In the “Submission notes” field in Pentabarf, please indicate that you agree to the publications. For example: “If my speech is accepted for FOSDEM MySQL Devroom, I agree to the publication of my recordings, slides provided under the same license as all FOSDEM content (CC-BY). Sincerely, Name”. Thank you, and see you soon virtually during FOSDEM 2022 ! Don’t wait too much and submit as soon as possible. [Less]

We received today the notification for FOSDEM’s organization that the MySQL DevRoom is accepted for the 2022’s edition ! The MySQL DevRoom will take place on February 6th, 2022. It’s a Sunday. Like previous edition, FOSDEM will be an online ... [More] event. Due to the very short deadlines, this year an external committee cannot be created to validate, sort and vote for the proposed talks. We will propose the best agenda respecting the ideas and precepts that have made our MySQL DevRoom one of the most popular room of FOSDEM for so many years. The MySQL Developer room focuses on MySQL and its Ecosystem. The call for papers for the MySQL DevRoom is now officially open ! Same rules as last year: For obvious reasons, this year the event will not take place in a physical location but online.The reference time will be Brussels local time (CET).Talks will be pre-recorded, and streamed during the event – Before proposing a session, please make sure you will have the facilities (camera, microphone, network) and time to prepare, upload and review a recording during the first two weeks of January if your proposal is accepted.Q&A session will be live after your talk.A platform will be provided for people watching to join a common chat.A platform will be provided for people watching to submit questions.These rules may change as they are not yet confirmed by FOSDEM’s organization. Key dates: Because creating such event is not easy, because of the indecisive situation, etc… we are now bound by time. Submission deadline: 31st December 2021Announcement of selected talks: as soon as possibleSubmission of recorded talks: to be determinedDevroom date: Sunday 6th February 2022How to submit: For this edition, we decided to have a mix of sessions. We are accepting talks of 15, 25 and 40 minutes, with time for questions afterward. The Q&A session will be live. Please, don’t forget to specify the duration in your submission. You might be aware that the usual standard length for MySQL Devroom is 25 minutes and the majority of accepted talks will certainly be in that range but we are open to exception (explain why in your submission). All submissions have to be made in the Pentabarf event planning tool:penta.fosdem.org/submission/FOSDEM22 Submit now() :D While creating your proposal, please provide the title of your talk, a short abstract, some information about yourself (eg. name, bio, photo, contact information, but remember that your profile might be already stored in Pentabarf). It’s very important that you select the right track “MySQL devroom” and don’t forget to specify the duration. And as usual, marketing and sales speeches are not welcome. Please focus on the engineering side, the operations and of course the developers. We want to hear how you are hacking MySQL and develop around it ! Recording of Talks: The talks in the MySQL developer room as in other rooms must be pre-recorded. Speakers have to submit a recording of their talk to the devroom in Pentabarf (if the talk is accepted and when recorded, not during the proposal) and be available for Q&A during the event. By participating in the event you agree to the publication of your recordings, slides and other content provided under the same license as all FOSDEM content (CC-BY). In the “Submission notes” field in Pentabarf, please indicate that you agree to the publications. For example: “If my speech is accepted for FOSDEM MySQL Devroom, I agree to the publication of my recordings, slides provided under the same license as all FOSDEM content (CC-BY). Sincerely, Name”. Thank you, and see you soon virtually during FOSDEM 2022 ! Don’t wait too much and submit as soon as possible. [Less]

A recommendation we often give to our customers is along the lines of “archive old data” to reduce your database size. There is a tradeoff between keeping all our data online and archiving part of it to cold storage. There could also be legal ... [More] requirements to keep certain data online, or you might want to query old data occasionally without having to go through the hassle of restoring an old backup. In this post, we will explore a very useful feature of AWS RDS/Aurora that allows us to export data to an S3 bucket and run SQL queries directly against it. Archiving Data to S3 Let’s start by describing the steps we need to take to put our data into an S3 bucket in the required format, which is called Apache Parquet. Amazon states the Parquet format is up to 2x faster to export and consumes up to 6x less storage in S3, compared to other text formats. 1. Create a snapshot of the database (or select an existing one) 2. Create a customer-managed KMS key to encrypt the exported data   3. Create an IAM role (e.g. exportrdssnapshottos3role) 4. Create an IAM policy for the export task and assign it to the role{ "Version": "2012-10-17", "Id": "Policy1636727509941", "Statement": [ { "Sid": "Stmt1636727502144", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::123456789:role/service-role/exportrdssnapshottos3role" }, "Action": [ "s3:PutObject", "s3:ListBucket", "s3:GetObject", "s3:DeleteObject", "s3:GetBucketLocation" ], "Resource": [ "arn:aws:s3:::test-athena", "arn:aws:s3:::test-athena/exports/*" ] } ] }5. Optional: Create an S3 bucket (or use an existing one) 6. Set a bucket policy to allow the IAM role to perform the export e.g.:{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "export.rds.amazonaws.com" }, "Action": "sts:AssumeRole" } ] }7. Export the snapshot to Amazon S3 as an Apache Parquet file. You can choose to export specific sets of databases, schemas, or tables   Querying the Archived Data When you need to access the data, you can use Amazon Athena to query the data directly from the S3 bucket. 1. Set a query result location 2. Create an external table in Athena Query editor. We need to map the MySQL column types to equivalent types in ParquetCREATE EXTERNAL TABLE log_requests ( id DECIMAL(20,0), name STRING, is_customer TINYINT, created_at TIMESTAMP, updated_at TIMESTAMP ) STORED AS PARQUET LOCATION 's3://aurora-training-s3/exports/2021/log/' tblproperties ("parquet.compression"="SNAPPY");3. Now we can query the external table from the Athena Query editorSELECT name, COUNT(*) FROM log_requests WHERE created_at >= CAST('2021-10-01' AS TIMESTAMP) AND created_at < CAST('2021-11-01' AS TIMESTAMP) GROUP BY name; Removing the Archived Data from the Database After testing that we can query the desired data from the S3 bucket, it is time to delete archived data from the database for good. We can use the pt-archiver tool for this task. Having a smaller database has several benefits. To name a few: your backup/restore will be faster, you will be able to keep more data in memory so response times improve, you may even be able to scale down your server specs and save some money. Complete the 2021 Percona Open Source Data Management Software Survey Have Your Say! [Less]

Codership is pleased to announce a new release of Galera Manager. This brings the installer to version 1.5 and the actual Galera Manager GUI to 1.4.0. Users will notice some usability improvements like the cluster name being added to the ... [More] authorized_keys generated file, while there are benefits around gmd generating a new key for the updated installation. There is also a port checker, so if ports are used and unavailable, the user will be informed and Galera Manager will refuse to install. In addition, on Debian/Ubuntu where apt/dpkg supports interactivity, we have now ensured that all steps will remain non-interactive.     We now have native support to deploy MariaDB Server 10.6 From the Galera Manager GUI standpoint, we have ensured that job monitoring and charts have better performance. One user experience enhancement for monitored or self-hosted clusters? Galera Manager now checks to ensure it has access to the nodes before it decides to continue (very much like how it does so with AWS). While we are on the topic of AWS, the user experience around EC2 deployment configuration, is also improved. Sizing guides are common database practice, and while less common in the MySQL world, we do have a maximum expected database size field now, for the cluster. In addition, there have been improvements in the deployment logs display, as well as the last transaction number executed on nodes. Please download Galera Manager and we look forward to your feedback. Release Notes We now have native support to deploy MariaDB Server 10.6 Job retention and clean up API Support for access check for self-managed instances Add a cluster name comment for auto-generated public keys in authorized_keys API support for changing database passwords Support for filters in jobs list API Move JWT RSA private signing key from a separate file to the gmd db (--rsa-private-key command line option has no effect now and gmd will generate a new key for the new and updated installations) Improved charts performance Implemented job list pagination Improved job monitoring Implemented access check for monitored and self-hosted clusters before nodes are added or deployed, Galera Manager checks if it has access to the node and returns a reason if check fails in case of DB authentication errors, users are able to try different DB root password in-form Improved UX on EC2 deployment configuration unsupported instance types are now disabled with displaying a reason Improved SSH keys management and security a new private key is always generated by Galera Manager for clusters users are requested to add public key to monitored and self-hosted nodes public keys are visible in node’s and cluster’s configuration page Improved and simplified node and cluster forms Improved deployment logs display Added last transaction number on nodes Added “Max expected DB size” field on cluster Fixes: Fix the Ubuntu AWS image path template (to avoid picking up a wrong image) Fix form validation issues unable to create new cluster if cluster configuration is open in the background unable to add a new node if node configuration is open in the background Fix copy button issue now display a message to user if copy is not working because the application is not served over HTTPS Fix node state display if there are multiple nodes with same name [Less]

Codership is pleased to announce a new release of Galera Manager. This brings the installer to version 1.5 and the actual Galera Manager GUI to 1.4.0. Users will notice some usability improvements like the cluster name being added to the ... [More] authorized_keys generated file, while there are benefits around gmd generating a new key for the updated installation. There is also a port checker, so if ports are used and unavailable, the user will be informed and Galera Manager will refuse to install. In addition, on Debian/Ubuntu where apt/dpkg supports interactivity, we have now ensured that all steps will remain non-interactive. We now have native support to deploy MariaDB Server 10.6 From the Galera Manager GUI standpoint, we have ensured that job monitoring and charts have better performance. One user experience enhancement for monitored or self-hosted clusters? Galera Manager now checks to ensure it has access to the nodes before it decides to continue (very much like how it does so with AWS). While we are on the topic of AWS, the user experience around EC2 deployment configuration, is also improved. Sizing guides are common database practice, and while less common in the MySQL world, we do have a maximum expected database size field now, for the cluster. In addition, there have been improvements in the deployment logs display, as well as the last transaction number executed on nodes. Please download Galera Manager and we look forward to your feedback. Release Notes We now have native support to deploy MariaDB Server 10.6 Job retention and clean up API Support for access check for self-managed instances Add a cluster name comment for auto-generated public keys in authorized_keys API support for changing database passwords Support for filters in jobs list API Move JWT RSA private signing key from a separate file to the gmd db (--rsa-private-key command line option has no effect now and gmd will generate a new key for the new and updated installations) Improved charts performance Implemented job list pagination Improved job monitoring Implemented access check for monitored and self-hosted clusters before nodes are added or deployed, Galera Manager checks if it has access to the node and returns a reason if check fails in case of DB authentication errors, users are able to try different DB root password in-form Improved UX on EC2 deployment configuration unsupported instance types are now disabled with displaying a reason Improved SSH keys management and security a new private key is always generated by Galera Manager for clusters users are requested to add public key to monitored and self-hosted nodes public keys are visible in node’s and cluster’s configuration page Improved and simplified node and cluster forms Improved deployment logs display Added last transaction number on nodes Added “Max expected DB size” field on cluster Fixes: Fix the Ubuntu AWS image path template (to avoid picking up a wrong image) Fix form validation issues unable to create new cluster if cluster configuration is open in the background unable to add a new node if node configuration is open in the background Fix copy button issue now display a message to user if copy is not working because the application is not served over HTTPS Fix node state display if there are multiple nodes with same name [Less]