BLUE

Navigation

Breadcrumbs

/ / opt / dedrads

Create

Upload

Tip: You can also drag & drop files anywhere on this page to upload.

Edit File Size: 9.12 KB

//opt/dedrads/innodb_converter.py

Text Base64

#!/usr/lib/rads/venv/bin/python3
"""Converts all MyISAM tables to InnoDB"""
# import list type
from pathlib import Path
import os
import sys
from argparse import ArgumentParser
import logging
import pymysql

# check for root
if os.getuid() != 0:
    sys.exit('This script must be run as root')

logger = logging.getLogger('innodb_convert.py')
# log to /var/log/innodb_convert.log
logger.setLevel(logging.DEBUG)
# print info and above to log file and console
console_handler = logging.StreamHandler(sys.stderr)
console_handler.setLevel(logging.INFO)
logger.addHandler(console_handler)
file_handler = logging.FileHandler('/var/log/innodb_convert.log')
file_handler.setLevel(logging.DEBUG)
logger.addHandler(file_handler)

def confirm_backups(check: bool, default='N'):
    """Confirms that backups have been taken, with configurable default (Y or N)"""
    if check:
        return
    prompt = f"Have you taken backups? (y/{default}) "
    answer = input(prompt).strip() or default
    if answer.lower() != 'y':
        prompt2 = f"Do you wish to continue anyway? (y/{default}) "
        answer2 = input(prompt2).strip() or default
        if answer2.lower() != 'y':
            sys.exit('Exiting...')

def get_db_config() -> dict:
    """Returns a dictionary of the MySQL configuration"""
    mysql_cnf = Path('/root/.my.cnf')
    if not mysql_cnf.exists():
        logger.error('Error: /root/.my.cnf does not exist')
        sys.exit(1)

my_cnf_data = {}

with open(mysql_cnf, encoding='utf-8') as file:
        for line in file:
            if line.startswith('['):
                section = line.strip('[]\n')
                my_cnf_data[section] = {}
            else:
                key, value = line.strip().split('=')
                value = value.replace("'", "").replace('"', '')
                my_cnf_data[section][key] = value

db_config_dict = {
        'host': 'localhost',
        'user': my_cnf_data['client']['user'],
        'password': my_cnf_data['client']['password'],
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor,
    }
    return db_config_dict

db_config = get_db_config()

def list_databases() -> list:
    """Returns a list of all databases"""
    databases = []
    try:
        with pymysql.connect(**db_config) as connection:
            with connection.cursor() as cursor:
                cursor.execute('SHOW DATABASES')
                databases = cursor.fetchall()
                databases = [x['Database'] for x in databases]
                return databases
    except pymysql.err.MySQLError as e:
        error = f'Error: {e}'
        sys.exit(error)

def list_tables(database: str) -> tuple[str | None, list[str] | None]:
    """Returns a list of all tables in a database"""
    tables = []
    try:
        with pymysql.connect(database=database, **db_config) as connection:
            with connection.cursor() as cursor:
                cursor.execute('SHOW TABLES')
                tables = cursor.fetchall()
                tables = [x['Tables_in_' + database] for x in tables]
                return None, tables
    except pymysql.err.MySQLError as e:
        error = f'Error on {database}: {e}'
        logger.error(error)
        return error, None

def check_engine(database: str, table: str) -> tuple[str | None, str | None]:
    """Returns the engine of a table"""
    try:
        with pymysql.connect(database=database, **db_config) as connection:
            with connection.cursor() as cursor:
                error = None
                cursor.execute('SHOW TABLE STATUS LIKE %s', (table,))
                table_status = cursor.fetchone()
                return error, table_status['Engine']
    except pymysql.err.MySQLError as e:
        error = f'Error on {database}.{table}: {e}'
        logger.error(error)
        return error, None

def convert_table(database: str, table: str, target) -> str | None:
    """Converts a table to InnoDB to the target engine(InnoDB or MyISAM)"""
    try:
        with pymysql.connect(database=database, **db_config) as connection:
            with connection.cursor() as cursor:
                cursor.execute(f'ALTER TABLE {table.replace("`", "``")} ENGINE={target}')
                return None
    except pymysql.err.MySQLError as e:
        error = f'Error: {e}'
        logger.error(error)
        return error

def get_mysql_innodb_settings() -> tuple[list, dict]:
    """Returns the innodb settings from my.cnf and my.cnf.d"""
    conf_dir = Path('/etc/my.cnf.d')
    conf_files = list(conf_dir.glob('*.cnf'))
    main_conf = Path('/etc/my.cnf')
    innodb_settings = {}
    key_errors = []
    conf_files.append(main_conf)
    for file in conf_files:
        innodb_settings[str(file.name)] = {}
        if file.exists():
            with file.open('r', encoding='utf-8') as f:
                for line in f:
                    if line.startswith('innodb_'):
                        try:
                            key, value = line.strip().split('=')
                            key = key.strip()
                        except ValueError:
                            key = line.strip()
                            value = ''
                        for _, settings in innodb_settings.items():
                            if key in settings:
                                error = (
                                    f'Error: {key} is defined in multiple files'
                                )
                                key_errors.append(error)
                        innodb_settings[str(file.name)][key] = value
    return key_errors, innodb_settings

def clean_db_list(databases: list) -> list:
    """Removes system databases from the list"""
    skips = ['information_schema', 'performance_schema', 'mysql', 'sys', 'test']
    for database in skips:
        if database in databases:
            databases.remove(database)
    return databases

def process_databases(databases: list, args) -> list:
    """Processes all databases"""
    errors = []
    if args.myisam:
        current='InnoDB'
        target='MyISAM'
    else:
        current='MyISAM'
        target='InnoDB'
    for database in databases:
        error, tables = list_tables(database)
        if args.table:
            if args.table in tables:
                tables = [args.table]
            else:
                error = f'Error: {args.table} does not exist in {database}'
                logger.error(error)
                errors.append(error)
                continue
        if error:
            errors.append(error)
            continue
        for table in tables:
            error, engine = check_engine(database, table)
            if error:
                errors.append(error)
                continue
            if args.check:
                logger.info('%s.%s is %s', database, table, engine)
                continue
            if engine.lower() == current.lower():
                logger.info('Converting %s.%s to %s', database, table, target)
                error = convert_table(database, table, target)
                if error:
                    errors.append(error)
                    continue
    return errors

def main():
    """Main function"""
    parser = ArgumentParser(description='Converts MyISAM tables to InnoDB')
    parser.add_argument('-a', '--all', help='Select all databases', action='store_true')
    parser.add_argument('-d', '--database', help='Database to convert')
    parser.add_argument('-t', '--table', help='Specify a table to convert. combine with -d')
    parser.add_argument('--check', help='Check selected databases', action='store_true')
    parser.add_argument('--myisam', help='Convert to MyISAM instead of InnoDB', action='store_true')
    args = parser.parse_args()
    if args.all and args.database:
        logger.error('Error: --all and --database are mutually exclusive')
        parser.print_help()
        sys.exit(1)
    elif not args.all and not args.database:
        logger.error('Error: No database specified')
        logger.error('must use either --all (-a) or --database (-d) flags')
        parser.print_help()
        sys.exit(1)
    elif args.table and not args.database:
        parser.error('--table requires --database')

errors = []
    confirm_backups(args.check)
    databases = list_databases()
    databases = clean_db_list(databases)
    if args.database:
        database = args.database
        if database not in databases:
            error = f'Error: {database} does not exist or is a system database'
            logger.error(error)
            sys.exit(error)
        databases = [database]
    errors = process_databases(databases, args)
    key_errors, innodb_settings = get_mysql_innodb_settings()
    if len(errors) > 0 or len(key_errors) > 0:
        logger.error('Errors occurred:')
        for error in errors:
            logger.error("\t%s", error)
        for key_error in key_errors:
            logger.error("\t%s", key_error)
    logger.info('InnoDB settings:')
    for filename, settings in innodb_settings.items():
        logger.info('%s', filename)
        for key, value in settings.items():
            if value == '':
                logger.info('\t%s', key)
            else:
                logger.info('\t%s = %s', key, value)

if __name__ == '__main__':
    main()

Exit & Reset Text mode: syntax highlighting auto-detects file type.

Directory Contents

Dirs: 14 × Files: 95

Sort:

Enable drag-to-move

Name	Size	Perms	Modified	Actions
account_review DIR	-	dr-xr-xr-x	2026-09-01 13:11:29	Edit Download recursive recursive
check_software_mods DIR	-	dr-xr-xr-x	2026-09-01 13:11:29	Edit Download recursive recursive
cms_tools DIR	-	dr-xr-xr-x	2026-09-01 13:11:29	Edit Download recursive recursive
etc DIR	-	dr-xr-xr-x	2026-09-01 13:11:29	Edit Download recursive recursive
extras DIR	-	dr-xr-xr-x	2026-09-01 13:11:29	Edit Download recursive recursive
mailparse DIR	-	dr-xr-xr-x	2026-09-26 06:35:47	Edit Download recursive recursive
mysql DIR	-	dr-xr-xr-x	2026-09-01 13:11:29	Edit Download recursive recursive
nlp_scripts DIR	-	dr-xr-xr-x	2026-09-01 13:11:29	Edit Download recursive recursive
oldrads DIR	-	dr-xr-xr-x	2025-09-18 19:05:24	Edit Download recursive recursive
perl DIR	-	dr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
provision DIR	-	dr-xr-xr-x	2026-09-26 06:35:47	Edit Download recursive recursive
python DIR	-	dr-xr-xr-x	2026-09-01 13:11:29	Edit Download recursive recursive
suspended DIR	-	dr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
__pycache__ DIR	-	dr-xr-xr-x	2026-09-01 13:11:34	Edit Download recursive recursive
account-review	799 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
allfw	6.11 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
alp.py	16.17 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
autossl_sync	6.12 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_apache	8.99 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_bandwidth	4.44 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_boxtrapper	1.64 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_cpu	11.93 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_crons	1.78 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_dcpumon	1.93 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_dns	1.78 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_domlogs	61 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_exim	3.64 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_hacks	22.07 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_imap	5.13 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_io	6.48 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_max_children	15.96 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_mem	1.62 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_misc	1.47 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_pacct	5.08 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_php	4.24 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_pop3	2.24 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_prov.py	4.60 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_server	696 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_size	490 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_software	9.62 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_spamd	2.37 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_traffic	1.89 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
check_user	1.84 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
clean_exim.py	6.24 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
clusterfix	5.22 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cmspass.py	3.88 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cms_check.py	9.59 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cms_counter.py	35.87 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cms_creds	713 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cms_dumpdb	4.38 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cms_pw	1.38 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cpanel-api	6.88 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cpanel_postgres_manager.py	28.32 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cpmerge	4.52 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
cpumon	2.14 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
dcpumon.pl	1.21 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
dedcheck	17.70 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
dns-sync	14.05 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
docroot.py	4.32 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
du-tree	7.28 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
envinfo.py	6.48 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
exclude_rbl.py	5.13 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
extract-vhost	2.63 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
filescan	3.48 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
find_warez	1.43 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
first_setup.py	18.67 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
fixwpcron.py	4.71 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
fix_dns_cluster	353 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
goaccess	1.16 MB	lr-xr-xr-x	2026-08-31 21:56:53	Edit Download recursive recursive
hostsfilemods	4.36 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
imap_io	2.27 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
innodb_converter.py	9.12 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
killall911	530 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
killdns	941 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
lastcommcache.sh	1.34 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
legal_lock_down.sh	7.28 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
lil-cpanel	50.78 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
listacct	4.47 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
mailscan	4.44 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
mail_sources.py	7.23 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
megaclisas-status	37.76 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
migrate2central.sh	14.08 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
modify-account	39.68 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
modsec_disable.py	11.19 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
monarxctl	8.70 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
msp.pl	27.06 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
mysql_dstat	474 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
mysql_selector.py	71.39 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
nlp	4.47 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
procscrape	1.82 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
quarantine	10.82 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
radsfunctions.sh	863 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
recent-cp	15.77 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
remote_dump	17.92 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
reset_cpanel	3.13 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
server-load	2.14 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
show-conns	19.63 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
show-conns-adv.py	53.39 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
sparta.py	373.53 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
sqltop	26.49 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
telcheck	2.35 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
temprootreset	186 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
unsuspend_user	1.28 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
unsusprunner.sh	1.80 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
updatednsadmin	526 B	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
update_spf	14.62 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
upgrade-check	9.00 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
vhost_data.py	6.08 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive
wp-xray.sh	83.80 KB	lr-xr-xr-x	2026-08-31 21:56:13	Edit Download recursive recursive