Jump to content

Import Big file Size of Database to MySQL

Navid
Navid
in MariaDB/MySQL

Featured Replies

Posted
comment_726

HI, I would to Import and big database file around 2.5 G, to a user account(website) kindly guide on how can I cope with please.

Note: I used this command : mysql -u alemaren_gild -p alemaren_statute < alemaren_english.sql but it's not working and I getting access dined error.

waiting for you help

thanks.

comment_728

You could try using phpMyAdmin by editing the config file at /usr/local/cwpsrv/var/services/pma/config.inc.php to specify an upload directory: 

/**
 * Directories for saving/loading files from server
 */
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

If you move your file there you should be able to select it from the phpMyAdmin panel when doing an import.  Just make sure you have the appropriate file ownerships for the phpMyAdmin user and that you are importing into the correct user account database.

Note: the config file changes could get overwritten on updates.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Go to topic listing

Posts

  • Viable migration script for CWP server migration available?

    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?

    Ling,

  • Viable migration script for CWP server migration available?

    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.

    Sandeep B.,

  • Viable migration script for CWP server migration available?

    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

    Ling,

  • Viable migration script for CWP server migration available?

    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.

    Ling,

  • Viable migration script for CWP server migration available?

    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?

    Ling,