Learn about Centmin Mod LEMP Stack today
Become a Member

Centmin Mod Site Data Migration Guide

Discussion in 'Centmin Mod Insights' started by eva2000, Feb 9, 2017.

Thread Status:
Not open for further replies.
  1. eva2000

    eva2000 Administrator Staff Member

    30,186
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    1:09 AM
    Nginx 1.13.x
    MariaDB 5.5

    Centmin Mod Site & Data Migration



    Centmin Mod users might want to eventually move web hosts or transfer their site data files and databases to a new server. The below manual guide outlines one of the many ways you can transfer data. The guide below utilises SSH key pair created via keygen.sh tool I wrote which sets up the connection between source server (where live data is) and the remote server (your new server) and then uses rsync to transfer selective server and site files. Then you'll use mysqldump to backup data and restore to remote server on the fly. This maybe slower than doing mysqldump to sql file on local server and then transferring the file to remote and then doing restore of the backup sql file. Large MySQL databases may need some tweaking for this part.

    CSF Firewall Configuration



    First, properly whitelist source and remote server's IP address and sshd ports in CSF Firewall.

    On source data server add to /etc/csf/csf.allow the following which whitelists TCP connections to destination default sshd port 22 on remote destination server IP 111.222.333.444. Change sshd port if it is not 22 on remote destination server.
    Code (Text):
    tcp|out|d=22|d=111.222.333.444

    then restart CSF Firewall service
    Code (Text):
    csf -r

    On remote destination server add to /etc/csf/csf.allow which whitelists TCP connections to destination default sshd port 22 from source data server's IP 1.2.3.4
    Code (Text):
    tcp|in|d=22|s=1.2.3.4

    then restart CSF Firewall service
    Code (Text):
    csf -r


    keygen.sh passwordless setup



    Second, setup keygen.sh tool on your source data server (the server which has the original data you want to transfer)
    Code (Text):
    mkdir -p /root/tools
    cd /root/tools
    git clone https://github.com/centminmod/keygen

    To update keygen tool use git
    Code (Text):
    cd /root/tools/keygen
    git stash
    git pull

    Then using keygen.sh tool generation your ssh key pair. You will need your remote server IP address (the IP of the server you want to transfer the source data to).

    Define and set that in variable remoteip and define the default sshd port of remoteip server and a comment to describe the key. You need to type these into your SSH client session to set their values. They will only survive in the current SSH client session. If you exit it, you will need to retype these before you run the rest of the commands outlined below.
    Code (Text):
    remoteip=111.222.333.444
    port=22
    comment=mykey
    remoterootpass=yourremoteserver_rootpassword
    

    Then run keygen.sh tool
    Code (Text):
    cd /root/tools/keygen
    ./keygen.sh gen rsa $remoteip $port root $comment $remoterootpass
    

    First time you connect you'll be asked about authenticity of the remote host ip and get a fingerprint displayed and ask if you are sure you want to continue connecting. Answer yes
    Code (Text):
    The authenticity of host 'remoteipaddress (remoteipaddress)' can't be established.
    ECDSA key fingerprint is 68:8x:2e:6d:c0:c1:7s:81:73:c0:7a:82:45:2e:1c:98.
    Are you sure you want to continue connecting (yes/no)? yes
    

    You'll end up with private and public key files named myX where X is a number which would increment automatically if you re-run this command on same server.
    • private key at /root/.ssh/my1.key
    • public key at /root/.ssh/my1.key.pub
    You'll now be able to ssh into remote server with just specifying the path to your private key you generated
    Code (Text):
    ssh root@$remoteip -p 22 -i ~/.ssh/my1.key

    On your remote server at /root/.ssh you'll have an authorized_keys file now with an entry for the public key identified by the comment you setup earlier i.e. mykey. The entry is the public key contents from your file generated at /root/.ssh/my1.key.pub
    Code (Text):
    cat /root/.ssh/authorized_keys
    ssh-rsa AAAAB3....pOlOCB/UhEQ== mykey


    The keygen.sh script will also test the remote connection by connecting to it using the created SSH key pair identity and list the output for uname -a command and also give you a manual tip to setup /root/.ssh/config for an alias you can use to connect.

    Example below where remote hostname ip is 111.222.333.444:
    Code (Text):
    -------------------------------------------------------------------
    Testing connection
    -------------------------------------------------------------------
    
    ssh root@111.222.333.444 -p 22 -i /root/.ssh/my1.key "uname -a"
    Linux remotehostname 2.6.32-642.13.1.el6.x86_64 #1 SMP Wed Jan 11 20:56:24 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
    
    -------------------------------------------------------------------
    Setup source server file /root/.ssh/config
    -------------------------------------------------------------------
    
    Add to /root/.ssh/config:
    
    Host 111.222.333.444-my1
      Hostname 111.222.333.444
      Port 22
      IdentityFile /root/.ssh/my1.key
      User root
    
    -------------------------------------------------------------------
    Once /root/.ssh/config entry added, can connect via Host label:
     111.222.333.444-my1
    -------------------------------------------------------------------
    
    ssh 111.222.333.444-my1
    

    So now instead of using command
    Code (Text):
    ssh root@$remoteip -p 22 -i ~/.ssh/my1.key

    You can now use
    Code (Text):
    ssh 111.222.333.444-my1
    


    Revoke Public Keys



    After transferring and confirming all data is intact, you want to revoke the public key on the remote server to prevent the source data server from future connections to the new remote server if this is a site/data migration to a new server (remote server).

    To revoke a public key from your remote server so that the source data server can not connect to the remote server anymore, you need to remove the generated public key from remote server's /root/.ssh/authorized_keys file. Again you use the comment i.e. mykey as a filter for sed deletion of the line.
    Code (Text):
    sed -i '/mykey$/d' /root/.ssh/authorized_keys 


    Security



    Apart from revoking the public key outlined above after successful migration, I would also recommend changing remote server's root user password afterwards. (The keygen.sh SSH key pair creation command passes the server root password on command line).

    You'd also want to revoke source server's whitelisted ip on remote server's CSF config at /etc/csf/csf.allow and remove the entry you added earlier
    Code (Text):
    tcp|in|d=22|s=1.2.3.4
    

    then restart CSF firewall
    Code (Text):
    csf -r
    
     
    Last edited: Feb 9, 2017
  2. eva2000

    eva2000 Administrator Staff Member

    30,186
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    1:09 AM
    Nginx 1.13.x
    MariaDB 5.5

    Rsync & mysqldump transfers



    Then next is using the above generated ssh key pair to rsync your site files + mysql databases over to new remote server with remoteip = 111.222.333.44 on sshd port 22

    Define your remoteip, port and privatekey name i.e. my1.key. You need to type these into your SSH client session to set their values. They will only survive in the current SSH client session. If you exit it, you will need to retype these before you run the rest of the commands outlined below.
    Code (Text):
    remoteip=111.222.333.444
    port=22
    privatekey=my1.key
    backupdir=/home/backupsql
    DUMPOPT='-Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob'
    CLIENTOPT='--max_allowed_packet=256M --net_buffer_length=65536'
    mkdir -p $backupdir


    rsync relevant data which assumes on remote destination server, centmin mod is installed but no vhosts or mysql databases have been created. It also assumes /root/.my.cnf is setup for mysql root user password which is automatically done on initial Centmin Mod installs

    file data with rsync options -avzin where n is for dry run so no data is actually transferred. If all the dry runs look good, remove n to become -avzi for the below commands.

    Notes
    • I commented out the transfer of /etc/csf/csf.conf CSF Firewall config file as you may have on source server IPSET disabled but enabled on remote server, so you do not want to disable IPSET on the remote server by transferring the config file for CSF.
    • Also commented out /etc/my.cnf for similar reason due to differences in preset configs between source and remote servers
    • The data migration process can take a very long time for large data set sized site accounts so you will want to run the below SSH based commands in a Screen session or Tmux session which allows the session to survive SSH disconnections. If your ISP or SSH connection is disconnected, you just re-attach the Screen or Tmux session once you log back into SSH and proceed where you left off of.

    dry run with 'n'
    Code (Text):
    cd /home
    rsync -avzin --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" --exclude 'domains/*/log/*' --exclude 'domains/*/backup/*' /home/nginx/ root@$remoteip:/home/nginx
    rsync -avzin --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" --exclude 'conf.d/virtual.conf' /usr/local/nginx/conf/ root@$remoteip:/usr/local/nginx/conf
    rsync -avzin --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /etc/centminmod/custom_config.inc root@$remoteip:/etc/centminmod/custom_config.inc
    rsync -avzin --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /usr/local/etc/php-fpm.conf root@$remoteip:/usr/local/etc/php-fpm.conf
    #rsync -avzin --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /etc/my.cnf root@$remoteip:/etc/my.cnf
    #rsync -avzin --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /etc/csf/csf.conf root@$remoteip:/etc/csf/csf.conf
    rsync -avzin --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /etc/init.d/memcached root@$remoteip:/etc/init.d/memcached

    live run without 'n'
    Code (Text):
    cd /home
    rsync -avzi --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" --exclude 'domains/*/log/*' --exclude 'domains/*/backup/*' /home/nginx/ root@$remoteip:/home/nginx
    rsync -avzi --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" --exclude 'conf.d/virtual.conf' /usr/local/nginx/conf/ root@$remoteip:/usr/local/nginx/conf
    rsync -avzi --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /etc/centminmod/custom_config.inc root@$remoteip:/etc/centminmod/custom_config.inc
    rsync -avzi --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /usr/local/etc/php-fpm.conf root@$remoteip:/usr/local/etc/php-fpm.conf
    #rsync -avzi --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /etc/my.cnf root@$remoteip:/etc/my.cnf
    #rsync -avzi --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /etc/csf/csf.conf root@$remoteip:/etc/csf/csf.conf
    rsync -avzi --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" /etc/init.d/memcached root@$remoteip:/etc/init.d/memcached

    Be sure to update main hostname's nginx vhost after transfer at /usr/local/nginx/conf/conf.d/virtual.conf which may have the new old server's server_name and not new server's server_name or have other settings that differ.

    To read up more on rsync usage see

    MySQL Database Backup with mysqldump



    On the fly piped mysqldump data to remote will bypass saving to local sql dump file on source server. This might be useful if you don't have enough disk space on source server to save the sql backup file. But for large MySQL databases might need some tuning.
    The process can take a very long time for large data set sized site accounts so you will want to run the below SSH based commands in a Screen session or Tmux session which allows the session to survive SSH disconnections. If your ISP or SSH connection is disconnected, you just re-attach the Screen or Tmux session once you log back into SSH and proceed where you left off of.

    To transfer individual database name setup DBNAME variable with mysql database name, remote login to create database name and then mysqldump + pipe to remote db server the data

    on the fly
    Code (Text):
    DBNAME='dbname'
    ssh root@$remoteip -p $port -i ~/.ssh/$privatekey mysqladmin create $DBNAME
    mysqldump $DUMPOPT $DBNAME | ssh root@$remoteip -p $port -i ~/.ssh/$privatekey mysql $CLIENTOPT $DBNAME
    

    list of database names, same as above just space separated list of mysql database names

    on the fly
    Code (Text):
    DBNAME='dbname dbname2 dbname3'
    for db in $DBNAME; do ssh root@$remoteip -p $port -i ~/.ssh/$privatekey mysqladmin create $db; mysqldump $DUMPOPT $db | ssh root@$remoteip -p $port -i ~/.ssh/$privatekey mysql $CLIENTOPT $db; done
    

    Or all mysql databases on server

    on the fly
    Code (Text):
    DBNAME=$(mysql -N -e "show databases;" | grep -Ev '(information_schema|performance_schema|mysql)')
    for db in $DBNAME; do ssh root@$remoteip -p $port -i ~/.ssh/$privatekey mysqladmin create $db; mysqldump $DUMPOPT $db | ssh root@$remoteip -p $port -i ~/.ssh/$privatekey mysql $CLIENTOPT $db; done
    


    save to local sql backup file instead of on the fly you can do this instead for all mysql databases on server
    Code (Text):
    DBNAME=$(mysql -N -e "show databases;" | grep -Ev '(information_schema|performance_schema|mysql)')
    for db in $DBNAME; do ssh root@$remoteip -p $port -i ~/.ssh/$privatekey mysqladmin create $db; mysqldump $DUMPOPT $db > $backupdir/$db.sql; done
    

    You database backups will be saved to source server $backupdir defined above as backupdir=/home/backupsql. So /home/backupsql/dbname.sql.
    Code (Text):
    ls -lah /home/backupsql/
    total 4.0K
    drwxr-xr-x  2 root root   20 Feb  9 05:13 .
    drwxr-xr-x. 6 root root   63 Feb  9 04:21 ..
    -rw-r--r--  1 root root 1.4K Feb  9 05:13 db1.sql
    

    You'll need to transfer these to remote server i.e. via rsync or scp
    Code (Text):
    rsync -avzin --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" ${backupdir}/ root@$remoteip:${backupdir}/
    

    example output from dry run
    Code (Text):
    rsync -avzin --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" ${backupdir}/ root@$remoteip:${backupdir}/
    sending incremental file list
    created directory /home/backupsql
    cd+++++++++ ./
    <f+++++++++ db1.sql
    
    Number of files: 2
    Number of files transferred: 1
    Total file size: 1358 bytes
    Total transferred file size: 1358 bytes
    Literal data: 0 bytes
    Matched data: 0 bytes
    File list size: 33
    File list generation time: 0.001 seconds
    File list transfer time: 0.000 seconds
    Total bytes sent: 48
    Total bytes received: 18
    
    sent 48 bytes  received 18 bytes  3.38 bytes/sec
    total size is 1358  speedup is 20.58 (DRY RUN)
    

    Then live run with 'n' flag in -avzi
    Code (Text):
    rsync -avzi --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" ${backupdir}/ root@$remoteip:${backupdir}/
    

    live run example output
    Code (Text):
    rsync -avzi --progress --stats -e "ssh -p $port -i $HOME/.ssh/$privatekey" ${backupdir}/ root@$remoteip:${backupdir}/
    sending incremental file list
    created directory /home/backupsql
    cd+++++++++ ./
    <f+++++++++ db1.sql
            1358 100%    0.00kB/s    0:00:00 (xfer#1, to-check=0/2)
    
    Number of files: 2
    Number of files transferred: 1
    Total file size: 1358 bytes
    Total transferred file size: 1358 bytes
    Literal data: 1358 bytes
    Matched data: 0 bytes
    File list size: 33
    File list generation time: 0.001 seconds
    File list transfer time: 0.000 seconds
    Total bytes sent: 534
    Total bytes received: 34
    
    sent 534 bytes  received 34 bytes  29.13 bytes/sec
    total size is 1358  speedup is 2.39
    

    To verify list database names on both servers via command
    Code (Text):
    mysql -N -e "show databases;" | grep -Ev '(information_schema|performance_schema|mysql)'
    

    and if used saved sql files to local source server check on both servers
    Code (Text):
    ls -lah /home/backupsql/
    total 12K
    drwxr-xr-x  2 root root 4.0K Feb  9 05:13 .
    drwxr-xr-x. 7 root root 4.0K Feb  9 05:20 ..
    -rw-r--r--  1 root root 1.4K Feb  9 05:13 db1.sql
    


    Restoring MySQL sql backup files



    If you choose to save MySQL database backups to local sql files on source server and transferred them to remote server, you now need to restore the sql files via command below where dbname = your database name. This can take quite a while depending on database size.
    Code (Text):
    CLIENTOPT='--max_allowed_packet=256M --net_buffer_length=65536'
    mysql $CLIENTOPT dbname < /home/backupsql/dbname.sql
    


    Backup MySQL Grant Privileges + MySQL user/passwords



    Note, this won't transfer custom created mysql usernames and passwords created on old server. You can easily backup mysql grants for this

    This command outputs the list of db user grant statements which need replaying on destination mysql server (import into mysql system database)
    Code (Text):
    mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql | while read user host; do if [[ "$user" != 'root' ]]; then mysql --batch --skip-column-names -e "SHOW GRANTS FOR '$user'@'$host'" | sed -e 's|$|;|'; fi; done

    example
    Code (Text):
    mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql | while read user host; do if [[ "$user" != 'root' ]]; then mysql --batch --skip-column-names -e "SHOW GRANTS FOR '$user'@'$host'" | sed -e 's|$|;|'; fi; done
    
    GRANT USAGE ON *.* TO 'dbuser1'@'localhost' IDENTIFIED BY PASSWORD '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7';
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `dbname2`.* TO 'dbuser1'@'localhost';
    

    You'll then need to replay these GRANT sql queries to the new remote server's MySQL server via mysql interactive command or via mysql on the command line.

    MySQL on the command line would be via wrapping the queries in mysql -e "YOUR GRANT CMD" commands.
    Code (Text):
    mysql -e "YOUR GRANT CMD"
    

    So
    Code (Text):
    GRANT USAGE ON *.* TO 'dbuser1'@'localhost' IDENTIFIED BY PASSWORD '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7';
    

    becomes SSH command to run, it will return empty if successful
    Code (Text):
    mysql -e "GRANT USAGE ON *.* TO 'dbuser1'@'localhost' IDENTIFIED BY PASSWORD '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7';"
    

    and
    Code (Text):
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `dbname2`.* TO 'dbuser1'@'localhost';
    

    Due to backslashes having different meaning on SSH command line to mean a command, the GRANT command needs to change and remove the backslashes for
    Code (Text):
    `dbname2`.*
    

    to become
    Code (Text):
    dbname2.*
    

    So becomes SSH command to run, it will return empty if successful
    Code (Text):
    mysql -e "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON dbname2.* TO 'dbuser1'@'localhost';"
    

    To verify, you can list the grant privileges for the user i.e. in this case dbuser1
    Code (Text):
    mysql -e "SHOW GRANTS for 'dbuser1'@'localhost'"
    

    example output
    Code (Text):
    mysql -e "SHOW GRANTS for 'dbuser1'@'localhost'"
    +----------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for dbuser1@localhost                                                                                                                 |
    +----------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'dbuser1'@'localhost' IDENTIFIED BY PASSWORD '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7'                               |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `dbname2`.* TO 'dbuser1'@'localhost' |
    +----------------------------------------------------------------------------------------------------------------------------------------------+
    
     
    Last edited: Jul 8, 2017
    • Winner Winner x 1
  3. eva2000

    eva2000 Administrator Staff Member

    30,186
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    1:09 AM
    Nginx 1.13.x
    MariaDB 5.5

    Summary



    There are many ways to transfer data so the above outlined guide is not the only way to do it. The above guide is recommended to be practiced with test remote servers. I'd maybe schedule once a week or fortnight or month, you can spin up an hourly test VPS server like Linode.com, Vultr.com or DigitalOcean and test this process out. The more you practice, the more it becomes second nature :) ;)

    For Centmin Mod Premium Users, like dbbackup.sh, will eventually have a dedicated cm-transfer.sh tool which could automate the above to allow server data migrations. This requires a lot testing before cm-transfer.sh is available. The testing and bug report/feedback of the above manual steps will also serve as feedback for improvements to cm-transfer.sh routines.
     
    • Like Like x 2
  4. eva2000

    eva2000 Administrator Staff Member

    30,186
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    1:09 AM
    Nginx 1.13.x
    MariaDB 5.5

    Preview Centmin Mod Domain Before DNS Updates



    If you want to preview migrated domains on new centminmod server without updating DNS, on Windows PC edit your windows HOSTS file at c:\Windows\System32\Drivers\etc\hosts (see guide at Modify your hosts file | Knowledge Center | Rackspace Hosting or install Hostman app HostsMan - abelhadigital.com which can manage the file too and allows easy dns flushing when you make changes to the hosts file.

    c:\Windows\System32\Drivers\etc\hosts contents to preview without DNS update
    Code:
    centminmodserver-ip-address domain.com
    to disable preview comment it out and flush dns for your system
    Code:
    #centminmodserver-ip-address domain.com
    Hostsman app to flush dns and edit the c:\Windows\System32\Drivers\etc\hosts file
     
Thread Status:
Not open for further replies.