I Use This!
Activity Not Available

News

Analyzed about 1 month ago. based on code collected about 1 month ago.
Posted 23 days ago by Brad
in this tutorial, we’ll cover learn how to string replace in golang.The strings.Replace() function is an inbuilt function in golang for replacing a substring with a string. I have already shared tutorial about that Golang Regex Replace Example. The ... [More] strings.Replace() function returns a copy of the provided string with the first n non-overlapping instances of […] The post String Replace Golang with Example appeared first on GolangLearn. [Less]
Posted 2 months ago by Brad
dig Library is a mobile library. Provide injection of dependencies (dependency injection) The toolbox should be built around the reflection. It has been realized. It helps to resolve the object graph during process startup. Many items will be engaged ... [More] in the project, and their interdependencies may look like this. pckg1-->pckg2;pckg1-->pckg3;pckg2-->pckg4;pckg3-->pckg4; pckg4 is an item. The […] The post Golang dig – A better way to Manage Dependency appeared first on GolangLearn. [Less]
Posted 4 months ago by MySQL Performance Blog
KMIP (Key Management Interoperability Protocol) is an open standard developed by OASIS (Organization for Advancement of Structured Information Standards) for the encryption of stored data and cryptographic key management. Percona Server for MySQL ... [More] 8.0.27 and Percona XtraBackup 8.0.27 now include a KMIP keyring plugin to enable the exchange of cryptographic keys between a key management server and the database for encryption purposes. The procedure to use them with HashiCorp Vault Enterprise is described below. Install Hashicorp Vault Enterprise We will first install Hashicorp Vault Enterprise on Ubuntu Linux “Bionic” and then enable the KMIP secrets engine. The KMIP secrets engine is only available with the Enterprise version of HashiCorp Vault, hence a valid license for it is required. Add HashiCorp repository and install enterprise vault package:curl -fsSL https://apt.releases.hashicorp.com/gpg | sudo apt-key add - sudo apt-add-repository "deb [arch=amd64] https://apt.releases.hashicorp.com $(lsb_release -cs) main" sudo apt-get update && sudo apt-get install vault-enterpriseExport the license as an environment variable:export VAULT_LICENSE=XXXXCreate a configuration file to be used with the vault, vault_config.hcl:disable_mlock = true default_lease_ttl = "24h" max_lease_ttl = "24h" storage "file" {   path    = "/home/manish.chawla/vault/data" } listener "tcp" {   address     = "127.0.0.1:8200"   tls_cert_file = "/home/manish.chawla/test_mode/certificates/vault.crt"   tls_key_file = "/home/manish.chawla/test_mode/certificates/vault.key" }Note: Vault root certificates and key need to be created separately and are not covered here. Start vault server with the configuration file:vault server -config=$HOME/vault_config.hcl 2>&1 &Note: To configure and start the vault using systemd, refer to the instructions here. Initialize the vault:vault operator init -address=https://127.0.0.1:8200This will generate five unseal keys and the initial root token.Unseal Key 1: rf4C6gY87tN0UkJbJY96Aq4+Zext1YqgwaDnm+0gBBAH Unseal Key 2: f9KA7EdlF391cIUzDqaS1N21JjML/36sZFl1x/OfCPn4 Unseal Key 3: 8Nh3EFYF7S0S9qqzdUIilPPRZRmWaDG+3El4rr4FmZNX Unseal Key 4: iJaCKBIzxulLvA/6vbF3fRK1RXVZ0zLEZoVdlv/s13Sc Unseal Key 5: rA4pwT6EZLwmVXPQJfU9fjgeGwPaHl260qM9CVNiUw13 Initial Root Token: s.WXEZ26Yb3MtvzbvNMMIG8bveUnseal the vault. Use any three unseal keys to unseal the vault. Three keys are required to unseal the vault.vault operator unseal -address=https://127.0.0.1:8200 rf4C6gY87tN0UkJbJY96Aq4+Zext1YqgwaDnm+0gBBAH Key                Value ---                ----- Seal Type          shamir Initialized        true Sealed             true Total Shares       5 Threshold          3 Unseal Progress    1/3 Unseal Nonce       aa1a25c8-aa90-49b3-b127-875524de38f8 Version            1.9.3+ent Storage Type       file HA Enabled         false vault operator unseal -address=https://127.0.0.1:8200 f9KA7EdlF391cIUzDqaS1N21JjML/36sZFl1x/OfCPn4 Key                Value ---                ----- Seal Type          shamir Initialized        true Sealed             true Total Shares       5 Threshold          3 Unseal Progress    2/3 Unseal Nonce       aa1a25c8-aa90-49b3-b127-875524de38f8 Version            1.9.3+ent Storage Type       file HA Enabled         false vault operator unseal -address=https://127.0.0.1:8200 8Nh3EFYF7S0S9qqzdUIilPPRZRmWaDG+3El4rr4FmZNX Key             Value ---             ----- Seal Type       shamir Initialized     true Sealed          false Total Shares    5 Threshold       3 Version         1.9.3+ent Storage Type    file Cluster Name    vault-cluster-7d9b43de Cluster ID      c047dcb5-5038-5a29-f4af-47c1ad560f9c HA Enabled      falseThe vault is unsealed. To use the vault in any terminal, run:export VAULT_ADDR=https://127.0.0.1:8200 export VAULT_TOKEN=s.WXEZ26Yb3MtvzbvNMMIG8bve export VAULT_CACERT=/home/manish.chawla/test_mode/certificates/root.cer Configure KMIP Secrets Engine in Vault Enable KMIP secrets engine:vault secrets enable kmip Success! Enabled the kmip secrets engine at: kmip/View the secrets list:vault secrets list Path          Type         Accessor              Description ----          ----         --------              ----------- cubbyhole/    cubbyhole    cubbyhole_ec12856f    per-token private secret storage identity/     identity     identity_12d9670d     identity store kmip/         kmip         kmip_5fb3d4c6         n/a sys/          system       system_1733eece       system endpoints used for control, policy and debuggingChange the kmip server listening address and port:vault write kmip/config listen_addrs=0.0.0.0:5696 Success! Data written to: kmip/configNote: Here kmip is the default path of the secret engine and not the type of the engine. By default, the kmip generates certificates in EC(Elliptic Curve). We need RSA for MySQL, so specify the certificate type (tls_ca_key_type) and bits (tls_ca_key_bits) to configure the kmip server.vault write kmip/config tls_ca_key_type="rsa" tls_ca_key_bits=2048 Success! Data written to: kmip/configvault read kmip/config Key                            Value ---                            ----- default_tls_client_key_bits    256 default_tls_client_key_type    ec default_tls_client_ttl         336h listen_addrs                   [0.0.0.0:5696] server_hostnames               [localhost] server_ips                     [127.0.0.1 ::1] tls_ca_key_bits                2048 tls_ca_key_type                rsa tls_min_version                tls12The KMIP secrets engine uses scopes to partition object storage into multiple named buckets. Within a scope, roles can be created with a set of allowed operations that the particular role can perform. Create a scope:vault write -f kmip/scope/my-service Success! Data written to: kmip/scope/my-serviceCreate a role within the scope, specifying the set of operations to allow or deny.vault write kmip/scope/my-service/role/admin operation_all=true Success! Data written to: kmip/scope/my-service/role/adminClient Certificate Generation for the scope and role created above. Retrieve the generated CA certificate:vault read kmip/caCopy and save the CA certificate as ca.pem. Generate a certificate in PEM format, and save it in a JSON file named credential.json.vault write -format=json \     kmip/scope/my-service/role/admin/credential/generate \     format=pem > credential.jsonExtract the certificate from the credential.json using jq tool and save it in a file named cert.pem.jq -r .data.certificate < credential.json > cert.pemExtract the private key from the credential.json using jq tool and save it in a file named key.pem.jq -r .data.private_key < credential.json > key.pemThe KMIP configuration is now complete. Percona Server for MySQL 8.0.27 Configuration for KMIP This section describes the KMIP configuration in Percona Server for MySQL. KMIP is configured as a component in Percona Server for MySQL. Create the global manifest file(mysqld.my) in the mysqld installation directory.{   "components": "file://component_keyring_kmip" }Create the global configuration file, component_keyring_kmip.cnf in the directory, where the component_keyring_kmip library resides.{ "path": "/home/manish.chawla/keyring_kmip", "server_addr": "0.0.0.0", "server_port": "5696", "client_ca": "/home/manish.chawla/cert.pem", "client_key": "/home/manish.chawla/key.pem", "server_ca": "/home/manish.chawla/ca.pem" }Note: SElinux/AppArmor rules may have to be adjusted, so that Percona Server for MySQL and Percona XtraBackup can access the certificates. Initialize and start mysqld with encryption options(add in my.cnf): --innodb-undo-log-encrypt --innodb-redo-log-encrypt --binlog-encryption --default-table-encryption=ON --log-replica-updates --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row --source-verify-checksum=ON --binlog-checksum=CRC32 --table-encryption-privilege-check=ONCheck the KMIP component status:8.0.27>SELECT * FROM performance_schema.keyring_component_status; +---------------------+------------------------------+ | STATUS_KEY | STATUS_VALUE | +---------------------+------------------------------+ | Component_name | component_keyring_kmip | | Author | Percona Corporation | | License | GPL | | Implementation_name | component_keyring_kmip | | Version | 1.0 | | Component_status | Active | | Server_addr | 0.0.0.0 | | Server_port | 5696 | | Client_ca | /home/manish.chawla/cert.pem | | Client_key | /home/manish.chawla/key.pem | | Server_ca | /home/manish.chawla/ca.pem | | Object_group | | +---------------------+------------------------------+Create some encrypted tables and add data in the Percona Server for MySQL. Backup and Restore of Percona Server for MySQL 8.0.27 Using Percona XtraBackup 8.0.27 This section describes the procedure for taking backup and restore of Percona Server for MySQL 8.0.27 when the KMIP component is enabled and the KMIP vault server is running. Percona XtraBackup reads the KMIP configuration in Percona Server for MySQL automatically, and it is not required to pass this information separately. Take full backup:xtrabackup --user=backup --password=* --backup --target-dir=backup_directoryPrepare full backup:xtrabackup --prepare --target_dir=backup_directoryStop Percona Server for MySQL and move the data directory to another location. Disable SElinux/AppArmor before restoring the backup. Restore full backup:xtrabackup --copy-back --target-dir=backup_directoryChange the ownership of the copied files in the Percona Server for MySQL data directory to the MySQL user. Start Percona Server for MySQL and check the data. Enable SElinux/AppArmor, if disabled previously. [Less]
Posted 4 months ago by Joshua Otwell
Being mindful of NULL values is a good practice. NULLs can slip up on you and give you a heck of a time (especially in calculations) if you aren’t careful. In this post, we will see examples of NULLtransformation with the MySQL COALESCE() function. ... [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! Image by Gerd Altmann from Pixabay  The Newsletter for PHP and MySQL Developers MySQL COALESCE() function accepts multiple arguments and returns the first non-NULL value. If there aren’t any non-NULLs, the COALESCE() returns NULL. 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“ Read the entire post, Transform NULL values with the MySQL COALESCE function to learn how to substitute NULL values for something more meaningful in query results. 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! Show your support for my blog and content by tossing any spare change you have in my Tip Jar. Tip Jar 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!!! Similar reading If you enjoyed this article, you may also like any of these posts. Share them along as well. Thank you MySQL IFNULL()SUBSTRING_INDEX()Recursive CTE Fun in MySQLMySQL SHOW TABLESLearn about my personal backend web developer journey where I share much of my goals and commitment to learning to code. Read this interview. 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 MySQL NULL value transformation with COALESCE() – Medium repost appeared first on Digital Owl's Prose. [Less]
Posted 4 months ago by Jean-François Gagné
When Index Condition Pushdown is used, you cannot trust rows examined as reported by the slow query log (nor in some Performance Schema tables) and you cannot trust the global status Innodb_rows_read (nor the InnoDB Metrics dml_reads).  These can ... [More] lead to an incorrect query complexity analysis, to a mistaken estimation of the total load of a MySQL server, and to a lot of wasted time.  As we go [Less]
Posted 4 months ago by Joshua Otwell
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]
Posted 4 months ago by Frederic Descamps
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]
Posted 4 months ago by Joshua Otwell
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]
Posted 4 months ago by Joshua Otwell
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]
Posted 4 months ago by Chandan Kumar
 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]