Optimize and Repair MySQL Database via CLI - command line

I really look for an automated server migration script, alone the installation of all the software installed on the source server would take me days, function not guranteed. The current status is, that I tried that already, and the result is: CWP working, transferred websites not working fully, email not working.
That can not be the solution
There must be a working transfer script out there, no matter what you have on the source server, just using rsync and changing IP adress/hostname everywhere it is referred to.
Am I the only one who has this problem?

I usually install cwp first and do this and it works every time, different hardware doesn’t matter and the kernel will take care of it.
in some cases efi boot cause issues
in this situation move the users manually

Red Hat Customer Portal
How to easily migrate local users and groups from one sys...
All local users and groups (within a certain UID/GID number range) need to be migrated from one old system to another freshly-installed system without manually creating said users on the new system.

that is what I have right now, anyone may find something I overlooked….
At least it is rebootable, though database and cwp NOT working at all, nor websites.
#!/bin/bash # This script runs on the target machine # Define the old and new IPv4 addresses OLD_IP="99.888.77.666" NEW_IP="55.444.33.222" # Define the old and new hostnames OLD_HOST="vmi123.dumbhost.net" NEW_HOST="vmi234.smarthost.net" SSH_PORT="1234" # Port used for SSH on both servers login with same public key certificate # Set variables for source and destination server SERVER1_IP=${OLD_IP} SERVER2_IP=${NEW_IP} SERVER1_HOSTNAME=${OLD_HOST} SERVER2_HOSTNAME=${NEW_HOST} # Directory to search in (set to / for entire system) SEARCH_DIR="/" # List of directories to search (excluding system files in /proc, /sys, /dev, /run, /tmp) SAFE_DIRECTORIES=( "/etc" "/var" "/home" "/opt" "/srv" "/usr" "/etc/csf" "/etc/amavisd" "/etc/systemd" "/etc/clamd.d" "/etc/cron.d" "/etc/cron.daily" "/etc/cron.hourly" "/etc/cron.weekly" "/etc/cron.monthly" "/etc/dnf" "/etc/dovecot" "/etc/fail2ban" "/etc/ImageMagick-6" "/etc/logrotate.d" "/etc/mail" "/etc/modpobe.d*" "/etc/monit.d" "/etc/netdata*" "/etc/opendkim" "/etc/opt" "/etc/pam.d" "/etc/pki" "/etc/postfix" "/etc/redis" "/etc/yum" "/etc/yum.conf" "/etc/yum.repos.d" ) # List of files in etc to transfer SAFE_ETC_FILES=( amavisd.conf clamd.conf cron.deny crontab freshclam.conf mailname motd named.conf opendkim.conf #group #group- #passwd #passwd- redis.conf redis-sentinel.conf ) # Function to check if file is readable and writable is_accessible_file() { local file="$1" if [ ! -r "$file" ] || [ ! -w "$file" ]; then return 1 # not readable or writable fi return 0 # file is accessible } # Function to replace IP and hostname in a file replace_in_file() { local file="$1" local old_value="$2" local new_value="$3" if grep -q "$old_value" "$file"; then sudo sed -i "s|$old_value|$new_value|g" "$file" echo "Replaced '$old_value' in: $file" fi } # Ensure ssh authentication works with no prompt (test connection) echo "Testing SSH connection to ${SERVER1_IP}..." if ! ssh -p ${SSH_PORT} root@${SERVER1_IP} "echo SSH connection successful"; then echo "SSH connection to ${SERVER1_IP} failed. Check your configuration." exit 1 fi systemctl stop mariadb || systemctl stop mysql # Step 2: Sync System Files with Full Attribute Preservation echo "Starting file sync from ${SERVER1_HOSTNAME} to ${SERVER2_HOSTNAME} with attribute preservation..." sleep 5 rsync -aHAXSzlpog --super --filter='-x security.selinux' --progress \ -e "ssh -p ${SSH_PORT}" \ --exclude='/root/.ssh/' \ --exclude='/etc/' \ --exclude='.trash/' \ --exclude='/dev/' \ --exclude='/boot/' \ --exclude='/lib/' \ --exclude='/mybackups/' \ --exclude='/mybackups_stage/' \ --exclude='/proc/' \ --exclude='/sys/' \ --exclude='/tmp/' \ --exclude='/mnt/' \ --exclude='/media/' \ --exclude='/lost+found' \ --exclude='/usr/lib/firmware/' \ --exclude='/usr/share/' \ --exclude='/swapfile' \ root@${SERVER1_IP}:/* / if [ $? -ne 0 ]; then echo "Rsync encountered errors during file transfer. Please check and retry." exit 1 fi echo "Completed file sync from ${SERVER1_HOSTNAME} to ${SERVER2_HOSTNAME} with no errors." echo "Copying root batch files, Firewall Rules, important etc dirs and motd from ${SERVER1_HOSTNAME} to ${SERVER2_HOSTNAME}..." sleep 5 for etcfile in "${SAFE_ETC_FILES[@]}"; do sudo scp -P ${SSH_PORT} root@${SERVER1_IP}:/etc/$etcfile /etc done sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/csf /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/amavisd /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/systemd /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/clamd.d /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/cron.d /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/cron.daily /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/cron.hourly /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/cron.weekly /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/cron.monthly /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/dnf /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/dovecot /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/fail2ban /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/ImageMagick-6 /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/logrotate.d /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/mail /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/modpobe.d /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/monit.d /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/netdata /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/opendkim /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/opt /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/pam.d /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/pki /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/postfix /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/redis /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/yum /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/yum.conf /etc sudo scp -r -P ${SSH_PORT} root@${SERVER1_IP}:/etc/yum.repos.d /etc if [ $? -ne 0 ]; then echo "scp encountered errors during file transfer. Please check and retry." exit 1 fi echo "File transfer from ${SERVER1_HOSTNAME} to ${SERVER2_HOSTNAME} completed with no errors." # Step 4: Update Hostname and IP References in System Files echo "Updating hostname and IP references on ${SERVER2_HOSTNAME}..." sleep 5 # Update /etc/hosts sed -i "s/${SERVER1_IP}/${SERVER2_IP}/g" /etc/hosts sed -i "s/${SERVER1_HOSTNAME}/${SERVER2_HOSTNAME}/g" /etc/hosts # Update /etc/hostname hostnamectl set-hostname ${SERVER2_HOSTNAME} echo "${SERVER2_HOSTNAME}" > /etc/hostname # Replace old hostname and IP in key configuration directories find /etc -type f -exec sed -i "s/${SERVER1_IP}/${SERVER2_IP}/g" {} \; find /etc -type f -exec sed -i "s/${SERVER1_HOSTNAME}/${SERVER2_HOSTNAME}/g" {} \; echo "Updating hostname and IP references with no errors." service mariadb enable service cwpsrv enable service mariadb start || service mysql start sleep 5 # Step 1: Migrate Databases with IP and Hostname Replacement echo "Installing current MariaDB for upgrade ..." echo "Dumping databases on ${SERVER1_HOSTNAME} and updating IP and hostname references..." DB_DUMP="/tmp/db_backup.sql" ssh -p ${SSH_PORT} root@${SERVER1_IP} "mysqldump --all-databases > ${DB_DUMP}" scp -P ${SSH_PORT} root@${SERVER1_IP}:${DB_DUMP} ${DB_DUMP} # Replace old IP and hostname references in the dump file sed -i "s/${SERVER1_IP}/${SERVER2_IP}/g" ${DB_DUMP} sed -i "s/${SERVER1_HOSTNAME}/${SERVER2_HOSTNAME}/g" ${DB_DUMP} # Import the modified dump into the new server echo "Importing updated database dump into ${SERVER2_HOSTNAME}..." mysql < ${DB_DUMP} rm -f ${DB_DUMP} if [ $? -ne 0 ]; then echo "Data base import unsuccessful. Please check and retry." exit 1 fi echo "Imported updated database successfully." yum -y upgrade # Step 5: Starting IP and hostname replacement echo "Starting IP and hostname replacement process..." sleep 5 # Loop through each directory in SAFE_DIRECTORIES for dir in "${SAFE_DIRECTORIES[@]}"; do echo "Searching in directory: $dir" # Find files in the current directory (excluding system files) find "$dir" -type f \ ! -path "/proc/*" ! -path "/sys/*" ! -path "/dev/*" ! -path "/run/*" ! -path "/tmp/*" ! -name "servermigrate.sh"\ ! -name "*.log" ! -name "*.gz" ! -name "*.bak" ! -name "*.swp" ! -name "*.tar" ! -name "new_ip_change.sh"\ ! -name "*.iso" | while read -r file; do # Check if the file is accessible (readable and writable) is_accessible_file "$file" || continue # Replace IP address replace_in_file "$file" "$OLD_IP" "$NEW_IP" # Replace hostname replace_in_file "$file" "$OLD_HOST" "$NEW_HOST" done done echo "IP address and hostname replacement completed for all files." sleep 5 yum -y upgrade # Step 6: Final Verification echo "Migration completed. Verify all services and configurations on ${SERVER2_HOSTNAME} (${SERVER2_IP})." sleep 10 echo "rebooting in 60 s, Press CTRL-Z to stop." sleep 10 echo "rebooting in 50 s, Press CTRL-Z to stop." sleep 10 echo "rebooting in 40 s, Press CTRL-Z to stop." sleep 10 echo "rebooting in 30 s, Press CTRL-Z to stop." sleep 10 echo "rebooting in 20 s, Press CTRL-Z to stop." sleep 10 echo "rebooting in 10 s, Press CTRL-Z to stop." sleep 10 reboot

also if you just rsync etc your system wont boot anymore, as target server has different hardware for sure.
Think it needs a more granular response.

Thanks Sandeep
Seems you forgot CWP itself in this approach, as well as other installed software.
Can you advise a script doing all this and running out of the box. Already did something similar but always got stuck up to now.
Th farthest I got was CWP running by manual install and websites running partially but not all links working.
But I really need a one to one copy by rsync, yet take care of the different IP and hostnames, and hardware specifics (shouldnt rsync /boot….). Thought it was less difficult, but no one a solution? Seems I am the first one to migrate a CWP server? No working script out there?