in mds_plugin/util.py [0:0]
def add_public_endpoint(**kwargs):
"""Creates a public endpoint using MySQL Router on a compute instance
If no id is given, it will prompt the user for the id.
Args:
**kwargs: Optional parameters
Keyword Args:
instance_name (str): Name of the compute instance
db_system_name (str): The new name of the DB System.
db_system_id (str): The OCID of the db_system
private_key_file_path (str): The file path to an SSH private key
shape (str): The name of the shape to use
cpu_count (int): The number of OCPUs
memory_size (int): The amount of memory
mysql_user_name (str): The MySQL user name to use for bootstrapping
public_ip (bool): If set to true, a public IP will be assigned to the compute instance
domain_name (str): The domain name of the compute instance
port_forwarding (bool): Whether port forwarding of MySQL ports should be enabled
mrs (bool): Whether the MySQL REST Service (MRS) should be enabled
ssl_cert (bool): Whether SSL Certificates should be managed
jwt_secret (str): The JWT secret for MRS
compartment_id (str): The OCID of the compartment
config (object): An OCI config object or None.
config_profile (str): The name of an OCI config profile
interactive (bool): Indicates whether to execute in interactive mode
raise_exceptions (bool): If set to true exceptions are raised
return_formatted (bool): If set to true, a list object is returned
Returns:
None
"""
# cSpell:ignore OCPU
instance_name = kwargs.get("instance_name")
db_system_name = kwargs.get("db_system_name")
db_system_id = kwargs.get("db_system_id")
private_key_file_path = kwargs.get(
"private_key_file_path", "~/.ssh/id_rsa")
shape = kwargs.get("shape", "VM.Standard.E4.Flex")
cpu_count = kwargs.get("cpu_count", 1)
memory_size = kwargs.get("memory_size", 16)
mysql_user_name = kwargs.get("mysql_user_name", "dba")
mysql_user_password = core.prompt(
f"Please enter the password for {mysql_user_name}",
{"type": "password"})
public_ip = kwargs.get("public_ip", True)
domain_name = kwargs.get("domain_name")
port_forwarding = kwargs.get("port_forwarding", True)
mrs = kwargs.get("mrs", True)
ssl_cert = kwargs.get("ssl_cert", False)
jwt_secret = kwargs.get("jwt_secret")
compartment_id = kwargs.get("compartment_id")
config = kwargs.get("config")
config_profile = kwargs.get("config_profile")
interactive = kwargs.get("interactive", core.get_interactive_default())
raise_exceptions = kwargs.get("raise_exceptions", not interactive)
return_formatted = kwargs.get("return_formatted", interactive)
# Get the active config and compartment
try:
config = configuration.get_current_config(
config=config, config_profile=config_profile,
interactive=interactive)
compartment_id = configuration.get_current_compartment_id(
compartment_id=compartment_id, config=config)
db_system_id = configuration.get_current_db_system_id(
db_system_id=db_system_id, config=config)
from mds_plugin import compute
import configparser
import io
import time
import hashlib
db_system = mysql_database_service.get_db_system(
db_system_name=db_system_name, db_system_id=db_system_id,
compartment_id=compartment_id, config=config,
interactive=interactive,
return_python_object=True)
if db_system is None:
raise ValueError("No DB System selected."
"Cancelling operation")
if not jwt_secret:
md5 = hashlib.md5()
md5.update(db_system.id.encode())
jwt_secret = md5.hexdigest()
# Get the first active endpoint
endpoints = [e for e in db_system.endpoints if e.status == 'ACTIVE']
if len(endpoints) < 1:
raise Exception(
"This MySQL DB System has no active endpoints assigned.")
endpoint = endpoints[0]
# Get the compute instance (let the user create it
# if it does not exist yet)
jump_host = create_compute_instance_for_endpoint(
instance_name=instance_name,
private_key_file_path=private_key_file_path,
db_system_id=db_system_id,
shape=shape, memory_size=memory_size,
cpu_count=cpu_count,
dns_a_record_notification=ssl_cert,
domain_name=domain_name,
compartment_id=db_system.compartment_id, config=config,
interactive=interactive,
return_python_object=True)
if not jump_host:
raise Exception(f"Compute instance {instance_name} not available."
"Operation cancelled.")
# Get the public IP of the instance
public_ip = compute.get_instance_public_ip(
instance_id=jump_host.id, compartment_id=db_system.compartment_id,
config=config)
if not public_ip:
raise Exception(f"The public IP of the instance {instance_name} "
"could not be fetched.")
sec_lists = compute.get_instance_vcn_security_lists(
instance_id=jump_host.id,
compartment_id=db_system.compartment_id,
config=config, interactive=interactive,
raise_exceptions=raise_exceptions,
return_python_object=True)
if sec_lists is None:
raise Exception(
"The network security lists could not be fetched.")
# Allow traffic on port 80 in order to fetch the certs
if mrs and public_ip:
compute.add_ingress_port_to_security_lists(
security_lists=sec_lists, port=80,
description="MRS HTTP via Router",
compartment_id=compartment_id, config=config,
interactive=interactive,
raise_exceptions=raise_exceptions)
# Open an SSH connection to the instance
if interactive:
print("\nBootstrapping the MySQL Router.\n"
f"Connecting to {instance_name} instance at {public_ip}...")
try:
with compute.SshConnection(
username="opc", host=public_ip,
private_key_file_path=private_key_file_path) as conn:
if interactive:
print(f"Connected to {instance_name} instance at "
f"{public_ip}.")
# Get MySQL Router configuration from remote instance
output = ""
output = conn.execute(
'test -f /etc/mysqlrouter/'
'mysqlrouter.conf && echo "Available"').strip()
if output != "Available":
# If the config is not available yet, give the instance time
# to complete setup
if interactive:
print(
f"Waiting for MySQL Router Configuration to become "
f"available.\nThis can take up to 2 minutes.",
end="")
try:
i = 0
while output != "Available" and i < 25:
output = conn.execute(
'test -f /etc/mysqlrouter/'
'mysqlrouter.conf && echo "Available"').strip()
if output != "Available":
time.sleep(5)
if interactive:
print(".", end="")
i += 1
except:
pass
if output == "":
raise Exception(
"Could not fetch MySQL Router configuration from remote instance.")
if interactive:
print("Bootstrapping MySQL Router against "
f"{mysql_user_name}@{endpoint.ip_address}:{endpoint.port} "
f"using JWT secret {jwt_secret} ...")
(success, output) = conn.executeAndSendOnStdin(
f"sudo mysqlrouter_bootstrap {mysql_user_name}@{endpoint.ip_address}:{endpoint.port} "
f"-u mysqlrouter "
f"--mrs --mrs-global-secret {jwt_secret} " if mrs else ""
"--https-port 8446 "
"--conf-set-option=http_server.ssl=0 "
"--conf-set-option=http_server.port=8446 ",
mysql_user_password)
if not success:
if output:
print(output)
raise Exception("Bootstrap operation failed.")
# Manually fix the MySQL Router config till bootstrap allows to disable SSL
# Load config to in-memory stream
conn.execute(
"sudo cp /etc/mysqlrouter/mysqlrouter.conf /home/opc/mysqlrouter.conf")
conn.execute("sudo chown opc:opc /home/opc/mysqlrouter.conf")
with io.BytesIO() as router_config_stream:
# Get the remote config file
try:
conn.get_remote_file_as_file_object(
"/home/opc/mysqlrouter.conf",
router_config_stream)
except Exception as e:
raise Exception("Could not get router config file. "
f"{str(e)}")
# If there was an error, print it
last_error = conn.get_last_error()
if last_error != "":
raise Exception(f"Could not read router config file. "
f"{last_error}")
# Load CLI config file
router_config = configparser.ConfigParser()
router_config.read_string(
router_config_stream.getvalue().decode("utf-8"))
router_config_stream.close()
# # # Ensure that there is a section with the name of
# # # "routing:classic"
# # if "routing:classic" not in router_config.sections():
# # router_config["routing:classic"] = {}
# # cnf = router_config["routing:classic"]
# # cnf["routing_strategy"] = "round-robin"
# # cnf["bind_address"] = "0.0.0.0"
# # cnf["bind_port"] = "6446"
# # cnf["destinations"] = f"{endpoint.ip_address}:{endpoint.port}"
# # Ensure that there is a section with the name of "http_server"
# if "http_server" not in router_config.sections():
# router_config["http_server"] = {}
cnf = router_config["http_server"]
cnf["port"] = "8446"
cnf["ssl"] = "0"
# cnf["ssl_cert"] = ""
# cnf["ssl_key"] = ""
# cnf["static_folder"] = "/var/run/mysqlrouter/www/"
# # # Ensure that there is a section with the name of "routing:x"
# # if "routing:x" not in router_config.sections():
# # router_config["routing:x"] = {}
# # cnf = router_config["routing:x"]
# # cnf["routing_strategy"] = "round-robin"
# # cnf["bind_address"] = "0.0.0.0"
# # cnf["bind_port"] = "6447"
# # cnf["destinations"] = f"{endpoint.ip_address}:{endpoint.port_x}"
# # # cSpell:ignore mrds SQLR
# # # Ensure that there is a section with the name of
# # # "mysql_rest_service"
# # if "mysql_rest_service" not in router_config.sections():
# # router_config["mysql_rest_service"] = {}
# # cnf = router_config["mysql_rest_service"]
# # cnf["mysql_user"] = "dba"
# # cnf["mysql_password"] = "MySQLR0cks!"
# # cnf["mysql_read_only_route"] = "classic"
# # cnf["mysql_read_write_route"] = "classic"
if interactive:
print("Writing updated MySQL Router configuration file...")
# Write config to in-memory stream
with io.BytesIO() as router_config_bytes_stream:
with io.StringIO() as router_config_stream:
router_config.write(router_config_stream)
# Seek to the beginning of the text stream
router_config_bytes_stream.write(
router_config_stream.getvalue().encode("utf-8"))
router_config_bytes_stream.seek(0)
# Write out new config file to remote instance
try:
conn.put_local_file_object(
router_config_bytes_stream,
"/home/opc/mysqlrouter.conf")
except Exception as e:
raise Exception(
"Could not upload router config file. "
f"{str(e)}")
# Move config to final place and fix privileges
conn.execute(
"sudo cp /home/opc/mysqlrouter.conf /etc/mysqlrouter/mysqlrouter.conf")
conn.execute(
"sudo chown mysqlrouter:mysqlrouter /etc/mysqlrouter/mysqlrouter.conf")
conn.execute("sudo rm /home/opc/mysqlrouter.conf")
# # If there was an error, print it
# last_error = conn.get_last_error()
# if last_error != "":
# raise Exception(
# "Could not upload router config file. "
# f"ERROR: {last_error}")
# Install the SSL certificate
if ssl_cert:
# Restart NGINX
conn.execute("sudo systemctl restart nginx.service")
ssl_cert_created = False
while not ssl_cert_created and domain_name != "":
# Check for one minute if the domain name points to the instance's ip address
if interactive:
print(
f"\nCreating the SSL certificate for {domain_name} ...")
try:
i = 0
while not ssl_cert_created and i < 5:
# cSpell:ignore certbot certonly webroot
# conn.execute(
# f"sudo certbot certonly --webroot -w /usr/share/nginx/html -d {domain_name} --agree-tos "
# "--register-unsafely-without-email --key-type rsa")
output = conn.execute(f"sudo /home/opc/.acme.sh/acme.sh --issue -d {domain_name} "
"--webroot /usr/share/nginx/html "
"--force --server letsencrypt --home /home/opc/.acme.sh")
last_error = conn.get_last_error()
if last_error != "":
if i == 0 and interactive:
print("\nATTENTION: Please create a DNS A record using the following values.\n"
f"Domain: {domain_name}\n"
f"Destination TCP/IP address: {public_ip}")
print(
f"\nWaiting for DNS A record creation ...", end="")
time.sleep(10)
if interactive:
print(".", end="")
else:
ssl_cert_created = True
i += 1
except:
pass
if not ssl_cert_created:
if interactive:
print(f"Failed to create the SSL certificate for {domain_name}. {output} {last_error}\n"
"Please correct the domain name or leave empty to cancel.")
try:
domain_name = core.prompt(
f"Domain Name for {public_ip}: ")
if not domain_name:
if interactive:
print(
"Skipping SSL certificate generation.")
domain_name = ""
continue
except:
domain_name = ""
continue
if ssl_cert_created:
# Install Certificate
conn.execute("sudo mkdir -p /etc/pki/nginx/private")
# cSpell:ignore reloadcmd
output = conn.execute(f"sudo /home/opc/.acme.sh/acme.sh --install-cert -d {domain_name} "
"--key-file /etc/pki/nginx/private/key.pem "
"--fullchain-file /etc/pki/nginx/cert.pem "
'--reloadcmd "sudo systemctl restart nginx.service" '
"--force --home /home/opc/.acme.sh")
last_error = conn.get_last_error()
if last_error != "":
raise Exception(
f"Failed to install SSL certificate. {output} {last_error}")
if interactive:
print("Writing web server configuration...")
# cSpell:ignore letsencrypt fullchain privkey
if domain_name != "":
ssl_cert_path = "/etc/pki/nginx/cert.pem"
ssl_cert_key_path = "/etc/pki/nginx/private/key.pem"
# ssl_cert_path = f"/etc/letsencrypt/live/{domain_name}/fullchain.pem"
# ssl_cert_key_path = f"/etc/letsencrypt/live/{domain_name}/privkey.pem"
else:
ssl_cert_path = "/var/lib/mysqlrouter/router-cert.pem"
ssl_cert_key_path = "/var/lib/mysqlrouter/router-key.pem"
nginx_config = f"""server {{
listen 443 ssl http2;
{f"server_name {domain_name};" if domain_name != "" else ""}
ssl_certificate {ssl_cert_path};
ssl_certificate_key {ssl_cert_key_path};
# Allow large attachments
client_max_body_size 128M;
location / {{
proxy_pass http://127.0.0.1:8446;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
}}
}}
"""
# Write config to in-memory stream
with io.BytesIO() as nginx_config_bytes_stream:
with io.StringIO() as nginx_config_stream:
nginx_config_stream.write(nginx_config)
# Seek to the beginning of the text stream
nginx_config_bytes_stream.write(
nginx_config_stream.getvalue().encode("utf-8"))
nginx_config_bytes_stream.seek(0)
# Write out new config file to remote instance
try:
conn.put_local_file_object(
nginx_config_bytes_stream,
"/home/opc/mrs.nginx.conf")
except Exception as e:
raise Exception(
"Could not upload router config file. "
f"{str(e)}")
# Move config to final place and fix privileges
conn.execute(
"sudo cp /home/opc/mrs.nginx.conf /etc/nginx/conf.d/mrs.nginx.conf")
conn.execute(
"sudo chown root:root /etc/nginx/conf.d/mrs.nginx.conf")
conn.execute("sudo rm /home/opc/mrs.nginx.conf")
# If there was an error, print it
last_error = conn.get_last_error()
if last_error != "":
raise Exception(
"Could not upload web server config file. "
f"ERROR: {last_error}")
if interactive:
print("Opening Firewall ports...")
# Open MySQL Router ports on the firewall
if port_forwarding:
conn.execute(
"sudo firewall-cmd --zone=public --permanent --add-port=6446-6449/tcp")
# If mrs was requested but no public_ip, open the port for a Load Balancer
if mrs and not public_ip:
conn.execute(
"sudo firewall-cmd --zone=public --permanent --add-port=8446/tcp")
conn.execute("sudo firewall-cmd --reload")
# If mrs was requested but no public_ip, open the port for a Load Balancer
# cSpell:ignore semanage mysqld
if mrs and not public_ip:
conn.execute(
"sudo semanage port -a -t mysqld_port_t -p tcp 8446")
if interactive:
print("Restarting MySQL Router...")
# Restart mysqlrouter.service
conn.execute("sudo systemctl restart mysqlrouter.service")
if interactive:
print("Restarting web server...")
# Restart NGINX
conn.execute("sudo systemctl restart nginx.service")
# Add ingress rules for MySQL ports to security list
compute.add_ingress_port_to_security_lists(
security_lists=sec_lists, port=6446,
description="Classic MySQL Protocol RW via Router",
compartment_id=compartment_id, config=config,
interactive=interactive,
raise_exceptions=raise_exceptions)
compute.add_ingress_port_to_security_lists(
security_lists=sec_lists, port=6447,
description="Classic MySQL Protocol RO via Router",
compartment_id=compartment_id, config=config,
interactive=interactive,
raise_exceptions=raise_exceptions)
compute.add_ingress_port_to_security_lists(
security_lists=sec_lists, port=6448,
description="MySQL X Protocol RW via Router",
compartment_id=compartment_id, config=config,
interactive=interactive,
raise_exceptions=raise_exceptions)
compute.add_ingress_port_to_security_lists(
security_lists=sec_lists, port=6449,
description="MySQL X Protocol RO via Router",
compartment_id=compartment_id, config=config,
interactive=interactive,
raise_exceptions=raise_exceptions)
if mrs and not public_ip:
compute.add_ingress_port_to_security_lists(
security_lists=sec_lists, port=8446,
description="MRS HTTP via Router",
compartment_id=compartment_id, config=config,
interactive=interactive,
raise_exceptions=raise_exceptions)
elif mrs:
compute.add_ingress_port_to_security_lists(
security_lists=sec_lists, port=443,
description="MRS HTTPS via Router",
compartment_id=compartment_id, config=config,
interactive=interactive,
raise_exceptions=raise_exceptions)
compute.add_ingress_port_to_security_lists(
security_lists=sec_lists, port=80,
description="MRS HTTP via Router",
compartment_id=compartment_id, config=config,
interactive=interactive,
raise_exceptions=raise_exceptions)
if interactive:
endpoint_address = domain_name if domain_name != "" else public_ip
print("\nNew endpoint successfully created.\n")
if port_forwarding:
print(f" Classic MySQL Protocol: {endpoint_address}:6446\n"
f" MySQL X Protocol: {endpoint_address}:6448\n")
if mrs:
print(
f" MySQL REST Service HTTPS: https://{endpoint_address}/\n")
if port_forwarding:
print(
f"Example:\n mysqlsh mysql://{mysql_user_name}@{endpoint_address}:6446")
if not return_formatted:
return {
"ip": public_ip,
"domainName": domain_name,
"port": 6446,
"port_x": 6447,
"rest_http": 443
}
except Exception as e:
raise Exception(
f"Could not configure the compute instance '{instance_name}' "
f"at {public_ip}.\n{str(e)}")
except Exception as e:
if raise_exceptions:
raise
print(f"ERROR: {str(e)}")