-
MySQL Password Rotation with AWS
If you run MySQL on Amazon RDS and use passwords, I sure hope you frequently rotate those passwords (among many other security best practices). But if you don’t rotate them, I might know why: it’s quite difficult to set up when you really get into it. It seems easy at first, but if you’ve ever tried you know how quickly it becomes complicated. At least, that was my experience. So to help others overcome the challenges and do frequent password rotation, I wrote up a long read: MySQL Password Rotation with AWS Secrets Manager and Lambda.
-
MySQL Password Rotation with AWS Secrets Manager and Lambda
MySQL Password Rotation with AWS Secrets Manager and Lambda MySQL password rotation using Amazon RDS for MySQL, AWS Secrets Manager, and AWS Lambda is a complex challenge to automate at scale. It appears easy at first—just two services and some IAM resources, right? But actual implementation quickly reveals a significant depth of considerations, choices, trade-offs, and technical problems. This page is a detailed guide to implementing MySQL 5.7 password rotation—fully automated at scale—using AWS RDS, Secrets Manager, and Lambda, and Terraform for cloud infrastructure.
-
Percona XtraBackup: Introducing Support For Storage Class in xbcloud
A lot of elements take part in a good backup strategy. Two of them are:
Where to store your backups – Never on the same server. Preferable not in the same datacenter. Ideally far enough so you can restore in case of a natural disaster (region flood, extended local power outage, etc…).
Retention period – The longer, the better.
Those points relate directly to costs. Storing a backup on an off-site facility cost money. Keeping the backups available to fulfill your retention period policy costs money.
The main cloud providers have different layers of storage often called Storage Class. Each layer has its own particularities and policies, but they come with an important factor – Cost!
We are happy to announce that starting at Percona XtraBackup 2.4.21 and 8.0.22, our cloud upload tool – xbcloud – has the ability to upload backups setting a specific storage class, where the user can choose a more cost-effective class for its backups. Currently, Amazon S3 and Google Cloud providers have the ability to set a different storage class.
As an example, for a database of 500G running a full backup every day, the potential savings are as follow:
Amazon S3:
Standard Storage Class – costs $0.021 per GB * 500GB * 365 days = $3,832.50
Glacier Deep Archive Storage Class – costs $0.00099 per GB * 500GB * 365 days = $180.68 (a difference of $3,651.83)
Google Cloud:
Standard Storage Class – costs $0.020 per GB * 500GB * 365 days = $3,650.00
Archive Storage Class – costs $0.0012 per GB * 500GB * 365 days = $219.00 (a difference of $3,431.00)
Storage class is controlled by a new parameter that varies depending on the provider:
Amazon S3 Glacier Deep Archive:xtrabackup [...] | xbcloud [...] --s3-region=SAME_REGION_AS_BUCKET --s3-storage-class=DEEP_ARCHIVE
Google Cloud Archive:xtrabackup [...] | xbcloud [...] --google-storage-class=ARCHIVE
Note that the above representation is meant to illustrate the potential cost savings. Please refer to your provider documentation for more information about each storage class, its proprieties (such as retrieval time), and the full list of supported storage classes.
In summary, storage classes can bring significant cost savings to stored backups. Users can now set it at the moment of streaming and benefit from a more cost-effective way of storing backups in the cloud.
Thanks to Benoît Knecht for providing the initial patch for this feature.
-
Manage MySQL Database Service (MDS) with Python
You only have to choose your favorite flavour to setup, administer, configure, backup or attach an analytics cluster to your MDS DB System, as Oracle maintains SDKs in several languages to integrate your software with Oracle Cloud Infrastructure.
And in particular today I’d like to test the SDK for Python, you can find an introduction here (but you have also SDKs for Java, Go, Ruby, Javascript, .NET). Read an introduction to SDKs.
First of all let’s create a virtual environment (venv) to work in a containerized environment.
bash-3.2$ python3 -m venv .
bash-3.2$ ll
total 8
drwxr-xr-x 14 mortensi staff 448 20 Dic 09:30 bin
drwxr-xr-x 2 mortensi staff 64 20 Dic 09:30 include
drwxr-xr-x 3 mortensi staff 96 20 Dic 09:30 lib
-rw-r--r-- 1 mortensi staff 75 20 Dic 09:30 pyvenv.cfg
Use the venv:
bash-3.2$ source bin/activate
(Software) bash-3.2$
Install Python SDK from Python package Index.
(Software) bash-3.2$ pip install oci
Collecting oci
Downloading oci-2.26.0-py2.py3-none-any.whl (7.2 MB)
|████████████████████████████████| 7.2 MB 6.1 MB/s
Collecting certifi
Downloading certifi-2020.12.5-py2.py3-none-any.whl (147 kB)
|████████████████████████████████| 147 kB 5.8 MB/s
Collecting cryptography==3.2.1
Downloading cryptography-3.2.1-cp35-abi3-macosx_10_10_x86_64.whl (1.8 MB)
|████████████████████████████████| 1.8 MB 5.7 MB/s
Collecting configparser==4.0.2
Using cached configparser-4.0.2-py2.py3-none-any.whl (22 kB)
Collecting python-dateutil<3.0.0,>=2.5.3
Using cached python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB)
Collecting pytz>=2016.10
Using cached pytz-2020.4-py2.py3-none-any.whl (509 kB)
Collecting pyOpenSSL<=19.1.0,>=17.5.0
Downloading pyOpenSSL-19.1.0-py2.py3-none-any.whl (53 kB)
|████████████████████████████████| 53 kB 2.3 MB/s
Collecting six>=1.4.1
Downloading six-1.15.0-py2.py3-none-any.whl (10 kB)
Collecting cffi!=1.11.3,>=1.8
Using cached cffi-1.14.4-cp39-cp39-macosx_10_9_x86_64.whl (177 kB)
Collecting pycparser
Using cached pycparser-2.20-py2.py3-none-any.whl (112 kB)
Installing collected packages: certifi, six, pycparser, cffi, cryptography, configparser, python-dateutil, pytz, pyOpenSSL, oci
Successfully installed certifi-2020.12.5 cffi-1.14.4 configparser-4.0.2 cryptography-3.2.1 oci-2.26.0 pyOpenSSL-19.1.0 pycparser-2.20 python-dateutil-2.8.1 pytz-2020.4 six-1.15.0
WARNING: You are using pip version 20.2.3; however, version 20.3.3 is available.
You should consider upgrading via the '/Users/mortensi/Software/bin/python3 -m pip install --upgrade pip' command.
(Software) bash-3.2$
And check that installation is all right.
(Software) bash-3.2$ pip list
Package Version
--------------- ---------
certifi 2020.12.5
cffi 1.14.4
configparser 4.0.2
cryptography 3.2.1
oci 2.26.0
pip 20.2.3
pycparser 2.20
pyOpenSSL 19.1.0
python-dateutil 2.8.1
pytz 2020.4
setuptools 49.2.1
six 1.15.0
And get also information about the SDK package.
(Software) bash-3.2$ pip show oci
Name: oci
Version: 2.26.0
Summary: Oracle Cloud Infrastructure Python SDK
Home-page: https://oracle-cloud-infrastructure-python-sdk.readthedocs.io/en/latest/index.html
Author: Oracle
Author-email: joe.levy@oracle.com
License: Universal Permissive License 1.0 or Apache License 2.0
Location: /Users/mortensi/Software/lib/python3.9/site-packages
Requires: pyOpenSSL, certifi, python-dateutil, pytz, cryptography, configparser
Required-by:
Now it’s time to make a test. Create a hello-world-like user.py file and edit:
import oci
config = oci.config.from_file("~/.oci/config", "DEFAULT")
identity = oci.identity.IdentityClient(config)
user = identity.get_user(config["user"]).data
print(user)
And test all works nicely (you must have set the right OCI SDK configuration file in “~/.oci/config”, read the how to here).
(Software) bash-3.2$ python user.py
{
"capabilities": {
"can_use_api_keys": true,
"can_use_auth_tokens": true,
"can_use_console_password": false,
"can_use_customer_secret_keys": true,
"can_use_o_auth2_client_credentials": true,
"can_use_smtp_credentials": true
},
"compartment_id": "ocid1.tenancy.oc1..[...]",
"defined_tags": {},
"description": "[...]",
[...]
"time_created": "2019-04-14T21:04:04.293000+00:00"
}
All good so far? Now time to start something interesting to manage our MySQL DB System! Check the API here. Let’s say we urgently need a new DB System in an existing subnet which we can access from an existing bastion host. Here’s what we would do.
Create a CreateDbSystemDetails with all the information needed to request OCI to create a MySQL DB System
Pass it to create_db_system
Find an example here. And here’s a working script with required parameters
import oci
config = oci.config.from_file("~/.oci/config", "DEFAULT")
mysql_client = oci.mysql.DbSystemClient(config)
COMPARTMENT_ID="ocid1.compartment..."
SHAPE_NAME="VM.Standard.E2.1"
SUBNET_ID="ocid1.subnet..."
ADMIN_USERNAME="root"
ADMIN_PASSWORD="Oracle1*"
STORAGE_SIZE=50
AD="EOuL:US-ASHBURN-AD-1"
# Send the request to service, some parameters are not required, see API
# doc for more info
create_db_system_response = mysql_client.create_db_system(
create_db_system_details=oci.mysql.models.CreateDbSystemDetails(
compartment_id=COMPARTMENT_ID,
shape_name= SHAPE_NAME,
subnet_id=SUBNET_ID,
admin_username=ADMIN_USERNAME,
admin_password=ADMIN_PASSWORD,
data_storage_size_in_gbs=STORAGE_SIZE,
availability_domain=AD))
# Get the data from response
print(create_db_system_response.data)
Which, once launched will produce:
{
"analytics_cluster": null,
"availability_domain": "EOuL:US-ASHBURN-AD-1",
"backup_policy": {
"defined_tags": null,
"freeform_tags": null,
"is_enabled": true,
"retention_in_days": 7,
"window_start_time": "00:00"
},
"channels": [],
"compartment_id": "ocid1.compartment.oc1...",
"configuration_id": "ocid1.mysqlconfiguration...",
"data_storage_size_in_gbs": 50,
"defined_tags": {
"Oracle-Tags": {
"CreatedBy": "oracleidentitycloudservice/...",
"CreatedOn": "2020-12-23T18:01:18.203Z"
}
},
"description": null,
"display_name": "mysqldbsystem20201223180120",
"endpoints": [],
"fault_domain": null,
"freeform_tags": {},
"hostname_label": null,
"id": "ocid1.mysqldbsystem.oc1.iad....",
"ip_address": null,
"is_analytics_cluster_attached": false,
"lifecycle_details": null,
"lifecycle_state": "CREATING",
"maintenance": {
"window_start_time": "MONDAY 07:35"
},
"mysql_version": null,
"port": null,
"port_x": null,
"shape_name": "VM.Standard.E2.1",
"source": null,
"subnet_id": "ocid1.subnet...",
"time_created": "2020-12-23T18:01:20.148000+00:00",
"time_updated": "2020-12-23T18:01:20.148000+00:00"
}
Now you can interrogate OCI about this DB System create_db_system_response object to understand when the instance is ready from lifecycle_state. You can use this other script passing the DB System id returned from the DB System creation response, for instance.
import oci
config = oci.config.from_file("~/.oci/config", "DEFAULT")
mysql_client = oci.mysql.DbSystemClient(config)
get_db_system_response = mysql_client.get_db_system("ocid1.mysqldbsystem...")
# Get the data from response
print(get_db_system_response.data)
When the instance is ready (“lifecycle_state”: “ACTIVE”), you are ready to use the instance.
This concludes this brief how-to administer your OCI MDS DB Systems straight from your intranet, no VPN needed. Easier, impossible.
The post Manage MySQL Database Service (MDS) with Python appeared first on mortensi.
-
InnoDB Clone and page tracking
First we will talk about some of the other internal users of the technology that underpins the InnoDB Clone. MySQL Enterprise Backup (MEB) is an enterprise offering that provides backup and recovery for MySQL. Among various types of backups available, the following two types are of interest to us:
Full Backup – A backup that backs up the entire MySQL instance – all the tables in each MySQL database.…
Facebook
Twitter
LinkedIn
|
News Feeds