File: //opt/lc/lwdbadmin/bin/lwmysrvadm
#!/usr/libexec/platform-python
# -*- coding: utf-8 -*-
import argparse
import subprocess
import os
import time
import re
import string
import random
import sys
from pathlib import Path
import configparser
# Add the library path to sys.path
sys.path.append('/opt/lc/lwdbadmin/lib')
# service imports
from lwdbadmin.mysql.lwmysrvadm import *
# Use subprocess.DEVNULL instead of opening /dev/null
devnull = subprocess.DEVNULL
def partitioning():
try:
subprocess.run(["lvcreate", "-Wy", "-Zy", "-y", "--name", "lv_mysql", "--size", "50G", "vg_system"], check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
subprocess.run(["mkfs.xfs", "/dev/mapper/vg_system-lv_mysql"], check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
with open("/etc/fstab", "a") as f:
f.write("\n/dev/mapper/vg_system-lv_mysql /var/lib/mysql xfs defaults,noatime 0 0\n")
subprocess.run(["mount", "/var/lib/mysql"], check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
Path("/var/lib/mysql/data").mkdir(parents=True, exist_ok=True)
os.chown("/var/lib/mysql/data", 27, 27)
except subprocess.CalledProcessError as e:
print(f"Error during partitioning: {e}")
raise
def createuser():
try:
subprocess.run(["groupadd", "-g", "27", "-o", "-r", "mysql"], check=False, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
subprocess.run(["useradd", "-M", "-r", "-d", "/var/lib/mysql", "-s", "/sbin/nologin", "-c", "MySQL server", "-g", "mysql", "mysql"], check=False, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
except subprocess.CalledProcessError as e:
print(f"Error creating user: {e}")
def get_mysql_version():
try:
result = subprocess.run(['mysql', '--version'], stdout=subprocess.PIPE, stderr=subprocess.PIPE, check=True, universal_newlines=True)
version_string = result.stdout.strip()
match = re.search(r'Distrib\s+(\d+\.\d+)', version_string)
if match:
version_number = match.group(1).replace('.', '')
return int(version_number[:2])
else:
raise ValueError("Unable to parse MySQL version")
except subprocess.CalledProcessError:
raise RuntimeError("Failed to execute 'mysql --version' command")
except ValueError as e:
print(f"Error parsing MySQL version: {e}")
raise
raise ValueError(f"Error parsing MySQL version: {str(e)}")
def bootstrap():
def generate_random_string(choices, length):
return ''.join(random.SystemRandom().choice(choices) for _ in range(length))
user_list = string.ascii_lowercase + string.digits
pwd_list = string.ascii_letters + string.digits + "!@%*-_+.,"
user = generate_random_string(user_list, 15)
password = generate_random_string(pwd_list, 18)
# Load version variable with MySQL version.
try:
mysql_version = get_mysql_version()
print(f"MySQL version: {mysql_version}")
except (RuntimeError, ValueError) as e:
print(f"Error: {e}")
sys.exit(1)
if mysql_version >= 57:
script_create_root = """CREATE USER '{user}'@'localhost' IDENTIFIED BY '{password}'; GRANT ALL PRIVILEGES ON *.* TO '{user}'@'localhost' WITH GRANT OPTION;
CREATE USER '{user}'@'127.0.0.1' IDENTIFIED BY '{password}'; GRANT ALL PRIVILEGES ON *.* TO '{user}'@'127.0.0.1' WITH GRANT OPTION;
CREATE USER '{user}'@'::1' IDENTIFIED BY '{password}'; GRANT ALL PRIVILEGES ON *.* TO '{user}'@'::1' WITH GRANT OPTION;""".format(**locals())
else:
script_create_root = """INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv) VALUES ('localhost','{user}',PASSWORD('{password}'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv) VALUES ('127.0.0.1','{user}',PASSWORD('{password}'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv) VALUES ('::1','{user}',PASSWORD('{password}'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');""".format(**locals())
script = f"""FLUSH PRIVILEGES;
DROP DATABASE IF EXISTS test;
TRUNCATE TABLE mysql.user;
TRUNCATE TABLE mysql.db;
TRUNCATE TABLE mysql.proxies_priv;
{script_create_root}
CREATE DATABASE IF NOT EXISTS teste;
CREATE TABLE IF NOT EXISTS teste.teste(teste VARCHAR(50) NOT NULL);
TRUNCATE TABLE teste.teste;
INSERT INTO teste.teste values ('Locaweb');
CREATE USER 'teste'@'%' IDENTIFIED BY '*1A2FA58B8ADDA83A100686FB4FACC2AFF1316FEA';
INSERT INTO mysql.db VALUES ('%', 'teste', 'teste', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
FLUSH PRIVILEGES;
"""
try:
if mysql_version >= 57:
subprocess.run('mysqld --initialize-insecure --user=mysql', shell=True, check=True)
time.sleep(30)
subprocess.run('systemctl start mysql', shell=True, check=True)
time.sleep(30)
subprocess.run(["mysql", "--user=root", "--skip-password"], input=script.encode(), check=True)
else:
subprocess.run(["mysqld", "--bootstrap", "--user=mysql"], input=script.encode(), check=True)
with open("/root/.my.cnf", "w") as f:
f.write(f"[client]\nuser={user}\npassword=\"{password}\"\n")
with open("/etc/locaweb/lwdbadmin/mysql.cnf", "w") as f:
f.write(f"[MySQL]\nhost: localhost\nuser: {user}\npass: {password}\nsocket:\n")
subprocess.run('/usr/bin/lwmyauth', shell=True, check=True)
except subprocess.CalledProcessError as e:
print(f"Error during bootstrap: {e}")
sys.exit(1)
def bootstrap_proxysql():
def generate_random_string(choices, length):
return ''.join(random.SystemRandom().choice(choices) for _ in range(length))
user_list = string.ascii_lowercase + string.digits
pwd_list = string.ascii_letters + string.digits + "%_."
user = generate_random_string(user_list, 15)
password = generate_random_string(pwd_list, 18)
monitor_user = generate_random_string(user_list, 15)
monitor_password = generate_random_string(pwd_list, 18)
try:
script = """SET admin-hash_passwords='true';
SET admin-admin_credentials='{user}:{password}';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;
SET mysql-interfaces='0.0.0.0:3306';
SET mysql-monitor_username='{monitor_user}';
SET mysql-monitor_password='{monitor_password}';
SAVE MYSQL VARIABLES TO DISK;
""".format(**locals())
# This below are the default admin credentials when proxysql is installed. We will change it now.
comm = ["mysql", "-h127.0.0.1", "-P6032", "-uadmin", "-padmin"]
p = subprocess.Popen(comm, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
_ = p.communicate(script)
retcode = p.returncode
stdout = _[0]
stderr = _[1]
if retcode != 0:
raise RuntimeError("Failed to bootstrap proxysql, error message: %s" % stderr)
else:
with open("/etc/locaweb/lwdbadmin/proxysql.cnf", "w") as f:
f.write("[proxysql]\n")
f.write("host=127.0.0.1\n")
f.write("port=6032\n")
f.write("user={}\n".format(user))
f.write("password={}\n".format(password))
f.write("monitor_user={}\n".format(monitor_user))
f.write("monitor_password={}\n".format(monitor_password))
print("ProxySQL bootstrap finished sucessfully.")
except Exception as e:
print(e)
finally:
restart_proxysql()
def restart_proxysql():
try:
user = getvalue("user")
password = getvalue("password")
host = getvalue("host")
port = int(getvalue("port"))
script = "PROXYSQL RESTART;"
result = subprocess.run(
["mysql", f"--host={host}", f"--port={port}", f"--user={user}", f"--password={password}"],
input=script.encode(),
capture_output=True,
text=True,
check=True
)
print("ProxySQL bootstrap finished successfully.")
except subprocess.CalledProcessError as e:
print(f"Failed to bootstrap proxysql, error message: {e.stderr}")
except Exception as e:
print(f"Error: {e}")
def getvalue(name):
config = configparser.ConfigParser()
if Path("/etc/default/locaweb/description/reseller").exists():
config_file = "/etc/locaweb/lwdbadmin/proxysql.cnf"
section = "proxysql"
else:
config_file = "/root/.my.cnf"
section = "client"
config.read(config_file)
ret = config.get(section, name)
return ret.strip('"')
return ret
# service program
def parse_args():
parser = argparse.ArgumentParser(description="Manage MySQL Locaweb Service")
parser.add_argument('--version', action='version', version='%(prog)s 2.3')
# comandos
subparsers = parser.add_subparsers(dest="command")
create_parser = subparsers.add_parser('create', help='create a new service')
create_parser.add_argument('dataset', type=str, help="Dataset to create the service")
create_parser.add_argument('datasetbackup', type=str, help="Dataset Backup to create the service")
create_parser.add_argument('name', type=str, help="Service name")
create_parser.add_argument('serviceip', type=str, help="Service address")
attach_parser = subparsers.add_parser('attach', help='attach an existing service to this host')
attach_parser.add_argument('dataset', type=str, help="Dataset to create the service")
attach_parser.add_argument('datasetbackup', type=str, help="Dataset to create the service")
attach_parser.add_argument('name', type=str, help="Service name")
attach_parser.add_argument('--force', action="store_true", help="Attach a service even if the service is hosted by another machine")
detach_parser = subparsers.add_parser('detach', help='detach a service from this host')
detach_parser.add_argument('name', type=str, help="Service name")
getips_parser = subparsers.add_parser('list', help="list ips from machines")
getips_parser.add_argument("--serviceip", action="store_true")
getips_parser.add_argument("--names", action="store_true")
getips_parser.add_argument("--service")
testlock_parser = subparsers.add_parser('test-lock', help="Test if this service is supposed to run in this machine")
testlock_parser.add_argument("name", type=str, help="Service name")
# comandos fpm = g2 centos 7
fpm_parser = subparsers.add_parser('fpm', help='Dedicated services')
fpm_parser.add_argument('--bootstrap', action="store_true")
fpm_parser.add_argument("--lvm", action="store_true")
fpm_parser.add_argument("--user", action="store_true")
fpm_parser.add_argument("--password", action="store_true")
return parser.parse_args()
if __name__ == "__main__":
args = parse_args()
# FPM
if args.command == "fpm":
if args.lvm and not os.path.ismount("/var/lib/mysql"):
partitioning()
createuser()
if args.bootstrap:
# If reseller machine, bootstrap proxysql instead
if os.path.exists("/etc/default/locaweb/description/reseller"):
bootstrap_proxysql()
else:
bootstrap()
if args.user:
ret = getvalue("user")
print(ret)
if args.password:
ret = getvalue("password")
print(ret)
else:
# Shared Services
if args.command == "detach":
detach(args.name)
elif args.command == 'list':
for name, d in getservices():
if args.serviceip:
serviceip = getsrvips(d)
ret = []
if args.names:
ret.append(name)
if args.serviceip:
ret.append(serviceip.split('/')[0])
print(("\t".join(ret)))
elif args.command == "test-lock":
for name, d in getservices():
if name == args.name:
fname = os.path.join(d, "config", "host")
try:
trylock(fname, False)
sys.exit(0)
except Exception as e:
print(e)
print("If you want that this machine hosts this service, change de /var/lib/mysql/config/host file")
break
sys.exit(0)
else:
dataset ="{0}.fs.locaweb.com.br:/storage/{1}".format(
args.dataset.lower(),
args.dataset.upper())
datasetbackup ="{0}.fs.locaweb.com.br:/storage/{1}".format(
args.datasetbackup.lower(),
args.datasetbackup.upper())
if ('BBFS' in dataset) or ('BDFS' in datasetbackup):
print("Verify the dataset order: lwmysrvadm attach dataset datasetbackup name")
sys.exit(0)
if args.command == "create":
create(args.name, dataset, datasetbackup, args.serviceip)
args.force = False
created = True
attach(args.name, dataset, datasetbackup, args.force, created)
if args.command == "attach":
attach(args.name, dataset, datasetbackup, args.force)