MySQL

Honestly, if no one ever read the OpenLampTech newsletter, that’s fine because I always do! Hey everyone, here is your dose of PHP/MySQL content to help you learn, grow, and maintain your sanity for the week. Enjoy! Image by Click on ... [More] , consider Thank you!  from Pixabay The Newsletter for PHP and MySQL Developers As always, I’ve aggregated the best content I found this week for your reading experience. We are looking at articles on: A case for WP(.)comWhat is mysqld?Writing good controllersCodeIgniter 4 form helper libraryPHP tricky true false tests And much more…Do you need to learn MySQL? Just starting and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don’t know where to start. Learn more about the premium blog posts as I develop and release them. Tell me more! Get your very own subscription I publish the OpenLampTech newsletter every Friday morning and only repost it here on the following Monday. Why wait? Get your subscription now and have OpenLampTech each week in your inbox when it’s published. Learn more here. Similar reading Enjoy any of these past issues and do check the OpenLampTech publication page for those standalone articles published apart from the weekly newsletter. Book Recommendation – SQL AntipatternsOpenLampTech issue #15 – CodeIgniter select() methods in depthMySQL ROW_NUMBER() window function with CASE Expression Enjoying the content? Help support my work with a donation in my Tip Jar today. Tip Jar Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. 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.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.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 Newsletter for PHP and MySQL Developers The post OpenLampTech issue #16 – Substack Repost appeared first on Digital Owl's Prose. [Less]

When using cloud native application architectures, Functions have an important role for the business logic without managing any infrastructure. Functions is a serverless platform powered by the Fn Project open source engine. These Functions are ... [More] mostly used to create and run different tasks. They can be executed using Fn Project CLI, OCI CLI or a HTTP request. HTTP requests are useful to invoke Functions from a different service. Usually use to schedule the execution of the function according to a time schedule. Currently, Oracle Cloud Infrastructure does not provide a native mechanism to create time based schedules to execute some jobs like invoking a Function directly or via a HTTP request. There are some workarounds (see this post) but they won’t be compatible with today’s topic. The Task The first thing we need to define is the job of our function. We also need to define some variables and their scope. The end goal of our function is that when it is invoked, a dump instance will be executed for a MySQL Database Service Instance and the logical dump will be stored in Object Storage. Of course MySQL Shell will be used to perform the logical dump. The Variables A a Function (that is part of an Application), can receive information using a Configuration Variable or by parsing a received JSON document (body). Configuration variables can be defined at the Application level (parent of the functions) or a the Function level. However every time the function is invoked, those configuration settings are not changed. They are similar to static variables. Dynamic variables (data sent as JSON in the body) can be sent every time at each execution. To be able to perform a MySQL Shell Dump of a MySQL Database Instance to Object Storage, the following information is required: mds_host: the host of MySQL Database Service Instance (can be the IP)mds_port: the port where MySQL is listening (usually 3306)mds_user: the MySQL user that will be used to perform the dumpmds_password: the password of the MySQL usermds_name: the name of the MDS instance (we only use it for the backup’s name, no check is performed)obs_bucket: the Object Storage bucket’s nameobs_namesapce: the Object Storage’s namespaceoci_fingerprint: MySQL Shell requires a OCI config file, therefore we need several info to create it in the serverless instance, we need the user’s API Key fingerprintoci_region: the Regionoci_user: the OCID of the useroci_tenancy: the tenancy’s OCIDoci_key: the key file base64 encoded stringSome variables won’t change each time we will invoke the Function. Those variables will become config variables: obs_bucketobs_namespaceoci_fingerprintoci_regionoci_useroci_tenancyoci_keyAll the other ones will be part of the data we sent each time we execute our function. This will allow us to dump multiple MySQL Database Instances using the same Function. Application It’s time to create our Application ! In OCI Console, we go in Functions Applications and we create a new one: We need to provide a name and very important select the VCN used by our MDS instance(s): As soon as the application is ACTIVE (very quickly): we can follow the information in Getting Started‘s section: We launch Cloud Shell and we cut and paste the commands: Don’t forget to generate your Auth Token (point 5) and save it. In Cloud Shell we can list our apps and we should see the one we created: cloudshell:~ (us-ashburn-1)$ fn list apps NAME ID mds_logical_dump ocid1.fnapp.oc1....3uwiq We then initiate our function (mysqlshell_dump) using python: cloudshell:~ (us-ashburn-1)$ fn init --runtime python mysqlshell_dump Creating function at: ./mysqlshell_dump Function boilerplate generated. func.yaml created. Dockerfile Now, we need to create a custom Dockerfile because the default one doesn’t include MySQL Shell. So in Cloud Shell, we will change directory to mysqlshell_dump and create a Dockerfile containing the following content: FROM fnproject/python:3.8-dev as build-stage WORKDIR /function ADD requirements.txt /function/ RUN pip3 install --target /python/ --no-cache --no-cache-dir -r requirements.txt && rm -fr ~/.cache/pip /tmp* requirements.txt func.yaml Dockerfile .venv && chmod -R o+r /python ADD . /function/ RUN rm -fr /function/.pip_cache FROM fnproject/python:3.8 WORKDIR /function COPY --from=build-stage /python /python COPY --from=build-stage /function /function RUN chmod -R o+r /function && mkdir -p /home/fn && chown fn /home/fn RUN rpm -U https://repo.mysql.com/mysql-community-minimal-release-el8.rpm \ && rpm -U https://repo.mysql.com/mysql80-community-release-el8.rpm RUN microdnf install -y mysql-shell RUN microdnf install -y glibc-all-langpacks ENV PYTHONPATH=/function:/python ENTRYPOINT ["/python/bin/fdk", "/function/func.py", "handler"] The Function The function’s code is defined in func.py. We replace its content with the following code: import io import import io import json import logging import subprocess import os import base64 from fdk import response from datetime import datetime def handler(ctx, data: io.BytesIO = None): try: cfg = ctx.Config() obs_bucket = cfg["bucket"] obs_namespace = cfg["namespace"] oci_fingerprint = cfg["oci_fingerprint"] oci_region = cfg["oci_region"] oci_user = cfg["oci_user"] oci_tenancy = cfg["oci_tenancy"] oci_key = cfg["oci_key"] except (Exception, ValueError) as ex: logging.getLogger().error('ERROR: Missing configuration key', ex) raise try: body = json.loads(data.getvalue()) mds_host = body.get("mds_host") mds_port = body.get("mds_port") mds_user = body.get("mds_user") mds_pwd = body.get("mds_password") mds_name = body.get("mds_name") backup_name=mds_name.replace(".","_") backup_name=backup_name.replace(" ","_") backup_name="{}_{}".format(backup_name, datetime.utcnow().strftime("%Y%m%d%H%M")) logging.getLogger().info('oci_fingerprint: {}'.format(oci_fingerprint)) except (Exception, ValueError) as ex: logging.getLogger().error('ERROR: Missing parameter', ex) raise try: with open('/tmp/.oci_config', 'w') as f: f.write('[DEFAULT]\n') f.write('user={}\n'.format(oci_user)) f.write('fingerprint={}\n'.format(oci_fingerprint)) f.write('tenancy={}\n'.format(oci_tenancy)) f.write('region={}\n'.format(oci_region)) f.write('key_file=/tmp/key.pem\n') with open('/tmp/key.pem', 'w') as g: g.write(base64.b64decode(oci_key.encode('ascii')).decode('ascii')) except (Exception, ValueError) as ex: logging.getLogger().error('ERROR: Problem creating OCI config', ex) raise logging.getLogger().info("Inside Python MDS logical dump function") os.system('export LC_ALL="en_US.UTF-8"') my_env = os.environ.copy() my_env["MYSQLSH_USER_CONFIG_HOME"]="/tmp" shell_cmd = subprocess.Popen(['/usr/bin/mysqlsh','{}@{}:{}'.format(mds_user, mds_host, mds_port),'--force', '--log-file=/tmp/shell.log', '--password={}'.format(mds_pwd),'--','util','dump_instance', backup_name,'--osBucketName={}'.format(obs_bucket),'--osNamespace={}'.format(obs_namespace), '--ociConfigFile=/tmp/.oci_config', '--compatibility=strip_definers,strip_restricted_grants' ], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE, env=my_env) logging.getLogger().info("Subprocess called") output, errors = shell_cmd.communicate() logging.getLogger().info("Subprocess communicated") shell_cmd.wait() logging.getLogger().info("Subprocess waited") logging.getLogger().info("Output: {}".format(output)) logging.getLogger().info("Errors: {}".format(errors)) return response.Response( ctx, response_data=json.dumps( {"message": "MDS Dump in progress: {}".format(backup_name)}), headers={"Content-Type": "application/json"} ) This is what we have now in our function directory on Cloud Shell: cloudshell:mysqlshell_dump (us-ashburn-1)$ ls Dockerfile func.py func.yaml requirements.txt And we can deploy the application: Configuration Variables We can set the configuration variables directly from Cloud Shell or using OCI Console: fn config app mds_logical_dump oci_region "us-ashburn-1" fn config app mds_logical_dump bucket "lefred-bucket" fn config app mds_logical_dump namespace "i...j" fn config app mds_logical_dump oci_fingerprint "58:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:4b" fn config app mds_logical_dump oci_user "ocid1.user.oc1..aa...vqa" fn config app mds_logical_dump oci_tenancy "ocid1.tenancy.oc1..aa...2a" fn config app mds_logical_dump oci_key "LS0tLS1...0tLQ==" For the oci_key in base64, this is what I do to generate the string of the key.pem file: $ python >>> import base64 >>> a=""" ... ... ... -----END PRIVATE KEY-----""" >>> base64.b64encode(a.encode('ascii')).decode('ascii') Once the variables have been added using Cloud Shell, we can see them in OCI Console too: We could also have added them directly from OCI Console. Invoking the Function The first time, the best way to invoke the function is to use again Cloud Shell and the Fn Project CLI: cloudshell:mysqlshell_dump (us-ashburn-1)$ echo -n '{"mds_host": "10.0.1.15", "mds_user": "admin", "mds_port": "3306", "mds_password": "my password", "bucket": "lefred-bucket", "namespace": "i.....j", "mds_name": "my_mds"}' | fn invoke mds_logical_dump mysqlshell_dump And we can verify in Object Storage the dump in our bucket: The second possibility is to use OCI’s CLI with the OCID of the function: API Gateway Now that our function works we can create an API Gateway. For the example I will use a public API to start a logical dump from anywhere: We can now create a deployment for our function: And we specify the function we want to use and the method it supports. As we need to pass JSON information related to the instance we want to dump, I use the POST method: As soon as the deployment is active, we can test it: We use the endpoint‘s url like this: Conclusion Now we can use this API with any external project or home made (like cronjobs) solution to trigger a MySQL Shell logical dump of our MDS Instance to Object Storage. This is useful when you want to use CloudNative Serverless solution. If you have a large database and you want to perform regular logical dumps of if, you might not benefit from MySQL Shell Dump & Load utility’s speed and parallelism as the deployed instance can have a maximum of 2GB of RAM (defined in func.yaml). A powerful dedicated compute instance for MySQL Shell is then recommended. Enjoy using MySQL in OCI. [Less]

I’ve been publishing more articles outside of the OpenLampTech newsletter, directly to the Substack page. In this post, I’m sharing a recent post featuring one of my favorite SQL books… Image by Dariusz Sankowski from Pixabay  The ... [More] Newsletter for PHP and MySQL Developers We all have our favorite books that we like to talk about and share. I have one in particular that I think will help you so much in your programming and development growth, whatever path that may be. While this book isn’t the new and shiny object of many of its counterparts, it is one foundation piece of learning material (in my opinion) if you are working with SQL databases. For more information, read the OpenLampTech publication piece, Book Recommendation – SQL Antipatterns. Do you need to learn MySQL? Just starting and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don’t know where to start. Learn more about the premium blog posts as I develop and release them. Tell me more! Similar Reading You may also like any of the below-referenced articles: MySQL SHOW TABLESPHP array_diff()MySQL ROW_NUMBER() with CASE ExpressionLike what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. 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.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.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 Newsletter for PHP and MySQL Developers The post SQL Antipatterns – OpenLampTech Publication Repost appeared first on Digital Owl's Prose. [Less]

Supercharge your productivity in MySQL Workbench with these ‘click and paste’ SQL code snippets available for any table in a schema. Continue reading and see the ‘Staple Five‘… Do you need to learn MySQL? Just starting and confused about how ... [More] to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don’t know where to start. Learn more about the premium blog posts as I develop and release them. Tell me more! Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! We type enough code as it is. If I can find a way to type less code, then sign me up. That’s one (of the many) reasons I love PHP, for its scripting capabilities. But, that’s another discussion for another day. In the meantime though, you can read more about it here… The Newsletter for PHP and MySQL Developers Code Snippets with MySQL Workbench Each of the 5 following snippets are created exactly the same: Right-click on the target tableClick on the Copy to Clipboard menu itemMouse over to the desired snippet item and click that snippet menu itemThe MySQL code is now copied to your clipboard and can be pasted wherever you need it1. Select All Statement This MySQL code snippet creates a fully qualified SELECT query: 2. Create Statement With this MySQL code snippet, we have the full CREATE TABLE DDL statement: 3. Delete Statement For the DELETE MySQL code snippet, a WHERE clause is included in the code: 4. Insert Statement All column names and the accompanying VALUES clause are included in the INSERT statement MySQL code snippet: 5. Update Statement Each table column is specified in the UPDATE MySQL code snippet: I hope these snippets are useful for you going forward during your development time in MySQL Workbench. Tell me any of your favorite MySQL Workbench shortcuts in the comments below. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. 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.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.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 Newsletter for PHP and MySQL Developers The post Quick SQL Snippets in MySQL Workbench appeared first on Digital Owl's Prose. [Less]

 MDS-Heatwave Data Analysis using ZeppelinThe Complete Guide to access MDS with HeatWave by Zeppelin  In this guide we will walk through how to install and access MDS with Heatwave. 1.       What is MDS and HeatWave ? 2.       What is Zeppelin  ? ... [More] 3.       Environment details 4.       How to Install Zeppelin ? 5.       Network Consideration 6.       How to access HeatWave 7.       MDS Data Analysis using Zeppelin 8.       Conclusion Overview of Zeppelin and MySQL Database Service with HeatWave We are living in the data world and as the data is increasing with large velocity, it is important to get the results quicker until earlier MySQL has challenged to produce results against larger data size in faster way how ever there is “paradigm shift” on current solutions of MySQL and  Now, MySQL (only in PaaS model of OCI) comes with HeatWave ,which is a distributed, scalable, shared-nothing, in-memory, hybrid columnar, query processing engine designed for extreme performance. It is enabled when you add a heatwave cluster to a MySQL DB System. So MySQL database service(MDS) will give faster results and  then needed some data analysis tool to make meaningful of the data , get more insight of data. In this blog let me introduce Apache Zeppelin for MDS data analysis.  Apache Zeppelin , an open source multipurpose notebook which help users to represent and analyze your data in the form of graphs or charts so that it help the organization to take quick decision.  We will explore each item in details…  Overall, My Idea is to show you quick demo , how easily you can connect MDS through Zeppelin. Zepplein can be installed anywhere (any public/private  cloud  , On-Premises).  What is MDS and Heatwave ? Oracle MySQL Database Service(MDS) is a fully managed database service that lets developers quickly develop and deploy secure, cloud native applications using the world’s most popular open source database.  MySQL Database Service is the only MySQL cloud service with an integrated, high performance, in-memory query accelerator - Heatwave. It enables customers to run sophisticated analytics directly against their operational MySQL databases—eliminating the need for complex, time-consuming, and expensive data movement and integration with a separate analytics database. MySQL Database Service is 100% built, managed, and supported by the OCI and MySQL engineering teams. more info:- https://www.oracle.com/mysql/ Heatwave:- https://www.oracle.com/mysql/heatwave MDS Business benefits :- https://mysqlsolutionsarchitect.blogspot.com/2022/02/understanding-mysql-database-servicemds.html    What is Zeppelin ? Zeppelin is web-based notebook that enables data-driven, interactive  data analytics and collaborative documents with language like SQL, Scala, Python, R and more. Things like data ingestion, data exploration, data visualization, and data analytics can be done in the zeppelin notebook. Features of Apache Zeppelin v  Data Ingestion. v  Data Discovery v  Data Analytics v  Data Visualization and Collaboration  More info:- https://zeppelin.apache.org/  Network Consideration Make sure the port of zeppelin 8080 is whitelisted in your environment and in case you are using Oracle Cloud Infrastructure (OCI) then make sure ingress rule is configured to white list the compute instance where Zeppelin is installed and also compute instance is able to ping pong MDS instance. To access the Zeppelin, always access with proper user with Interpreter access otherwise any user can access your Zeppelin, Sometime anonymous user get into the portal of Zeppelin by entering the Zeppelin public IP address but however if your interpreter is restricted with particular user then your workspaces is safer. Hence, It is important to have data source authorization in Apache Zeppelin https://zeppelin.apache.org/docs/0.10.0/setup/security/datasource_authorization.html Note:- I couldn’t dig much in details about security and my major focused on where you are , how are you accessing the Zeppelin , how eill have seamless experiences with MDS and HeatWave and do awesome visualization and data analysisInstallation of Zeppelin In this blog, installation of Zeppelin will be on Oracle Cloud Infrastructure (https://www.oracle.com/in/cloud/) With below specifications Step 1 #Install JDK sudo yum install java-11-openjdk-devel Step 2:- Download the Zeppelin by using below command wget https://dlcdn.apache.org/zeppelin/zeppelin-0.10.0/zeppelin-0.10.0-bin-all.tgz Step 3 :- create user and give the permission sudo adduser -d /home/opc/zeppelin -s /sbin/nologin zeppelin sudo chown -R zeppelin:zeppelin /home/opc/zeppelin Step 4:- rename zeppelin site template to zeppelin site cd /home/opc/zeppelin/conf sudo cp zeppelin-site.xml.template zeppelin-site.xml sudo cp zeppelin-site.xml.template zeppelin-site.xml Step 5 :- #Start Zeppelin Connect Zeppelin http://::8080/#/ Connect MDS and Heatwave through Zeppelin Download MySQL Connector/J https://dev.mysql.com/downloads/connector/j/  rpm -ivh  mysql-connector-java-8.0.28-1.el7.noarch.rpm warning: mysql-connector-java-8.0.28-1.el7.noarch.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY error: Failed dependencies:         java-headless >= 1:1.8.0 is needed by mysql-connector-java-1:8.0.28-1.el7.noarch [  Fix:- yum -y install java-headless  Create a MySQL Interpreter #Create directory called mds under zeppelin’s interpreter folder # mkdir mds #move the “mysql-connector-java.jar” file into MDS folder  #cp /usr/share/java/mysql-connector-java.jar /home/opc/zeppelin/interpreter/mds/   Navigate to the  Interpreter http://:8080/#/interpreter Search for MDS interpreter and fill below details   Finally once changes are final then it looks like below Access data from MDS and HeatwavePlease ensure MDS and Heatwave is up and running.Create a notebook and access MDS with Heatwave via ZeppelinAwesome! , Zeppelin Connected to MDSLet’s do data analysis by using zeppelin In this demonstration assume that MDS with Heatwave is up and running and data is loaded into HeatWave. If you wanted to follow quick start demo , how to load data into HeatWave. https://docs.oracle.com/en-us/iaas/mysql-database/doc/heatwave.html#GUID-700248EF-4614-49CD-888F-920F4C66CD4C  Schema used in the demo is “AirportDB” with 50 GB database size. Command to load first time data into the HeatWave:- run Auto Parallel Load to load airportdb data into HeatWave: CALL sys.heatwave_load(JSON_ARRAY('airportdb'), NULL); ##Run Below SQL statement to generate reportuse airportdb;  SELECT airline.airlinename, SUM(booking.price) as price_tickets, count(*) as nb_tickets FROM booking, flight, airline, airport_geo WHERE booking.flight_id=flight.flight_id AND airline.airline_id=flight.airline_id AND flight.from=airport_geo.airport_id AND airport_geo.country = "UNITED STATES" GROUP BY airline.airlinename ORDER BY nb_tickets desc, airline.airlinename limit 10; More info about Heatwave:-  https://www.oracle.com/mysql/heatwave/ Conclusion Apache Zeppelin is a kind of tool, which makes Data Scientist life smooth, they can do everything they need in one place. Things like data ingestion, data exploration, data visualization, and data analytics can be done in the zeppelin notebook and MDS HeatWave is a massively parallel, high performance, in-memory query accelerator for Oracle MySQL Database Service that accelerates MySQL performance by orders of magnitude for analytics and mixed workloads and cost lesser than Specialist analytics product like Amazon Redshift, Aurora ,Snowflake ,Azure Synpase ,google Big Query etc. [Less]

A little bit ago, I released a blog post comparing the backup performance of different MySQL tools such as mysqldump, the MySQL Shell feature called Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. You can find the first analysis here: ... [More] Backup Performance Comparison: mysqldump vs. MySQL Shell Utilities vs. mydumper vs. mysqlpump vs. XtraBackup However, we know the backups are just the first part of the story. What about the restore time? And which tool performs better for the complete operation (backup+restore)? Let’s see the results and the conclusion in the following sections. Benchmark Results I ran the benchmark on an m5dn.8xlarge instance, with 128GB RAM, 32 vCPU, and two io1 disks of 600GB (one for backup and the other one for MySQL data) with 5000 provisioned IOPS. The MySQL version was 8.0.26 and configured with 89Gb of the buffer pool, 20Gb of redo log, and a sample database of 96 GB (more details below). When we sum the backup time and the restore time, we can observe the results in the chart below: And if we analyze the chart without mysqldump to have a better idea of how the other tools performed: The backup size created by each tool: Note that the backup size of XtraBackup (without compression) is the size of the datadir without the binary logs. Next, we can see the backup time: And the restore time: Analyzing The Results When we sum backup and restore times, we observe that the fastest tool is Percona XtraBackup. The main point of XtraBackup is not even the speed but its capacity to perform PITR backups. Also, the tool supports compression and encryption. We can also observe that mydumper/myloader and MySQL Shell utilities produce good results in both phases. The difference from Xtrabackup is that both tools perform logical backups, which means that these tools connect to MySQL and extract the data to dump files. Because they have to extract data from MySQL, these tools are more sensitive for the MySQL configuration and backup/restore parametrization. For example, MyDumper/MyLoader has some extra options that can improve the backup and restore performance, such as --rows, --chunk-filesize, and --innodb-optimize-keys. Note that  XtraBackup, MyDumper, and mysqldump support stream restore, reducing overall timing to perform the backup and restore operation.  The tool that has the most inconsistent behavior is mysqlpump where the tool can make speedy backups, but the restore performance is terrible since it is single-threaded the same way as mysqldump.  Based on the tests, we can observe that compression, TLS, socket, or TCP/IP do not significantly impact the time needed to perform the whole operation. Because there is no significant impact, tools that can perform compression and use TLS like MySQL Shell, mydumper/myloader, and XtraBackup have a good advantage since their backups are safer and use less disk space (less disk space = fewer costs). The trade-off between the features of these tools and the time spent to backup and restore the database is something that all DBAs should evaluate. And to answer some questions/comments about this topic: The difference you see between MySQL Shell and mydumper can be explained by the use of SSL in one and clear transfer in the other. Encryption has a cost, unfortunately.  A: Indeed, SSL has a cost. However, when we put the security benefits of the SSL and consider the whole process, it is a small cost (in the same way as compression). Does XtraBackup support ZSTD?  A: At this moment, no. However, there is a feature request for this (you can follow the JIRA ticket to receive updates about it): https://jira.percona.com/browse/PXB-2669 Is there any difference substituting mysqldump | gzip with a different compression tool? A: The difference is neglectable piping with gzip or sending the uncompressed dump to the disk. The mysqldump tool is the most inefficient option due to its single-thread nature, severely impacting performance. Because of its single-thread nature, the tool cannot extract maximum performance from hardware resources (in particular I/O). How is the performance impact on MySQL when running the backups? A: Unfortunately, I did not measure this. Based on my experience, there is a dedicated replica server for backup most of the time. If the MySQL community is interested in this test, I can write another post about this (leave in the comments your opinion).  It is possible to squeeze more juice from MySQL in the restore phase. We can take some actions like disabling the binary log and making asynchronous writes. You can check the advice (pros and cons) in these two blog posts: https://www.percona.com/blog/2020/05/14/tuning-mysql-innodb-flushing-for-a-write-intensive-workload/ https://www.percona.com/blog/2014/05/23/improve-innodb-performance-write-bound-loads/ To conclude, this blog post is intended to give an overall idea of how these tools perform. I tried to stick with the default options of each tool (except the number of threads) to keep the test as fair as possible. Also, time is not the only thing that companies consider to adopt a backup method (security, encryption, and data protection are very important). In my daily tasks, I use mydumper/myloader and XtraBackup because I’m more familiar with the commands, and I have used them for a long time. However, I would advise keeping an eye on the MySQL Shell utilities since it is becoming a fascinating tool to perform many tasks (backup and restore have excellent results). Hardware and Software Specs These are the specs of the benchmark: 32 CPUs 128GB Memory 2x io1 disks 600 GB with 5000 IOPS each Centos 7.9 MySQL 8.0.26 MySQL shell 8.0.26 mydumper 0.11.5 – gzip mydumper 0.11.5 – zstd Xtrabackup 8.0.26 Useful Resources Finally, you can reach us through the social networks, our forum, or access our material using the links presented below: Blog  Solution Briefs White Papers Ebooks Technical Presentations archive Videos/Recorded Webinars Forum Knowledge Base (Percona Subscriber exclusive content) [Less]

Sit back, grab your beverage of choice, and get ready for a deep-dive issue of OpenLampTech, the newsletter for PHP/MySQL developers. This month’s featured piece covers select-like methods in the CodeIgniter 4 Query Builder class. Thanks for being ... [More] here to read the publication The Newsletter for PHP and MySQL Developers Take your time with this week’s issue of the OpenLampTech featured piece. There is original and curated content covering: CodeIgniter 4 Query Builder select() methodsThe Apache .htaccess fileHead-to-head review of popular PHP frameworksPHP version and framework benchmarkingMySQL and phpMyAdmin with DockerAnd much more…Early bird gets the worm Be sure and get your free subscription for OpenLampTech so you never miss out on an issue Do you need to learn MySQL? Just starting and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don’t know where to start. Learn more about the premium blog posts as I develop and release them. Tell me more! Previous OpenLampTech Newsletters Enjoy any of the below past issues of the OpenLampTech newsletter and publication page: MySQL LAG() Window FunctionOpenLampTech issue #12 – My RTC InterviewPHP array_diff() function – A handy use case learnedSupport my blog, newsletter, and content with a Tip today. Thank you Tip Jar Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. 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.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.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 Newsletter for PHP and MySQL Developers The post OpenLampTech issue #15 – CodeIgniter 4 select() methods in depth appeared first on Digital Owl's Prose. [Less]

As of MySQL 8.0.18, EXPLAIN ANALYZE is an indispensable tool for understanding query execution because it breaks down the query execution stage of response time by measuring each step of the query execution plan. The information is illuminating, but ... [More] the output is not intuitive: it requires practice and some understanding of how MySQL executes queries beyond the table join order shown by traditional EXPLAIN output. This blog post closely examines three different examples of EXPLAIN ANALYZE output. [Less]

In this blog post, we will learn how to group sets of values into a comma-separated list, for each group, using the MySQL GROUP_CONCAT() function. Continue reading for more information… Do you need to learn MySQL? Just starting and confused ... [More] about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don’t know where to start. Learn more about the premium blog posts as I develop and release them. Tell me more! Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! Get tailored articles with Refind delivered each day in your inbox. Refind is part of my daily reading habit. Make it part of yours by using my referral link. At no additional cost to you, I will be eligible for a premium subscription with more sign-ups from my link. “The essence of the web, every morning in your inbox. Subscribe for free“ The Newsletter for PHP and MySQL Developers I’m using this fictitious ‘group_member’ table for the query data and example. There are 4 distinct groups: 100, 200, 300, and 400 respectively. Table group_member data To place each ‘member_name’ alphabetically in its respective group, we can use the GROUP_CONCAT() function and GROUP BY the ‘group_num’ column, specifying a comma as the SEPARATOR value: MySQL GROUP_CONCAT() function query. Comma-separated list of names using GROUP_CONCAT() Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! I’ve included a short video clip of the above code in action so you can see how it works: Show your support and appreciation for my content by tossing any of your spare change in my Tip Jar. Tip Jar Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. 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.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.The Newsletter for PHP and MySQL Developers 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 Create a comma-separated list with MySQL GROUP_CONCAT() appeared first on Digital Owl's Prose. [Less]

MySQL is a traditional open source relational database that goes well with many well-established applications. Find out more about its features and use cases in our latest blog post.