Archive for May, 2017


There may be some situations where you have to move the location of MySQL, for example, if you’re out of disk space or perhaps looking to host it on another device to increase performance. Whatever the reason, moving MySQL is simple and has no impact  on cPanel’s functionality.

 

1.Create a backup

Please make full database backup(including system tables) before moving your data directory. This action will prevent data losing in case if something goes wrong.

# tar -cvf mysql.tar /var/lib/mysql

2.Edit the my.cnf file

# vi /etc/my.cnf

Now in the mysqld section add the following. Don’t restart MySQL after adding new entry.

datadir=/home/mysql

3.Create the new MySQL data directory

# mkdir /home/mysql

4.Now migrate the data to the new location using rsync command.

# nohup rsync -avp /var/lib/mysql/ /home/mysql

The nohup will keep rsync running even when your session with the server end, the other part “

# tail -f nohup.out

Notice that you have to do the syncing process twice because when moving large size of data can take some time to complete and the tables may have changed in between. When we run it the second time we hopefully get it so that when the switch over happens there is very little, if any, lost data. If you can afford the downtime simply shut down MySQL before running this command.If you cannot though running it twice then quickly copy/pasting the other commands is a valid substitute.

5.Typically you want to stop MySQL for syncing data completely.

# /etc/init.d/mysqld stop

6.Start the re-sync process once again to copy data’s completely.

# rsync -avp --delete /var/lib/mysql/ /home/mysql/

7. Change ownership of new created MySQL data directory to MySQL.

# chown -R mysql:mysql /home/mysql/

8. Now, re-link the socket file to /tmp:

# rm -rf /tmp/mysql.sock
# ln -sf /home/mysql/mysql.sock /tmp/mysql.sock

9. Since you already added the data directory entry to my.cnf , all you need to do is restart again and everything should be working.

# /etc/init.d/mysqld start

Check whether your MySQL logs are written at the new location (Eg: /home/mysql/hostname.err)

10. Create a sample database named “test” for checking.

# mysqladmin create test

11. Check whether the new database is created at new data directory.

# ls -d /home/mysql/test

12. After confirming everything works properly.You can remove the old data directory.

# rm -rf  /var/lib/mysql

Is it possible?

Yeah, definitely! You can create a complete cPanel account backup by excluding the home directory. This is very important, because, if the home directory of cPanel accounts have a large amount of data, this will increase the total time for the account migration.

Generating backup and restoring it for a large account is the much time-consuming process. If you need a faster solution, exclude the home directory then complete the account migration without its home directory. After that start syncing the home directory from source to destination server 🙂

cPanel provides necessary scripts to do this kind of operations via server CLI (command line interface). Here I am listing those scripts for account migration.

Command to create a cPanel backup file

/scripts/pkgacct

Package account.


Important cPanel scripts

How to create cPanel backup without its home directory?

/scripts/pkgacct --skiphomedir user

Replace user with cPanel user name.

The cPanel script to restore an account is pasted below:

/scripts/restorepkg $backup.file.name.

I guess you all are familiar with those scripts. Here we go!!


This is a simple for loop to generate backups, sync that to remote host and remove that backup file from the source server after syncing them to remote host.

Here is the script for doing so:

for x in `\ls -A1 /var/cpanel/users/`; do /scripts/pkgacct --skiphomedir ${x} ; rsync -aP /home/cpmove-${x}.tar.gz -e ssh root@destination.server:/home/ ; rm -f /home/cpmove-${x}.tar.gz ; done

Replace destination.server with the IP address of destination server.

If you want to maintain those backups on the source server, remove the last entry.

for x in `\ls -A1 /var/cpanel/users/`; do /scripts/pkgacct --skiphomedir ${x} ; rsync -aP /home/cpmove-${x}.tar.gz -e ssh root@NEW-SERVER-IP:/home/ ; done

Yeah, you have successfully created backup files and synced those files to the remote server. Now you need to restore those backups to the destination server.

Execute the following command on destination server.

for x in `ls -A1 /home/cpmove*` ; do /scripts/restorepkg /home/${x} ; done

That’s it!!!

Wait wait, where is home directories? Yeah you need to sync home directories to destination server. Syncing directories is not a big task. Please execute the following command:

rsync -aP /home/ -e ssh root@destination.server:/home/

Rsync commands in details

Yup! You’ve successfully completed the account migration.
Change the name servers of those domains to load it from the new server.