Category Archives: sys admin

Connecting to an AWS RDS Instance via an AWS EC2 to Convert MyISAM Tables to InnoDB

So you’ve moved over your MySQL DB to RDS and you need to do some maintenance on it. Maybe you are noticing alerts in the RDS portal like this one
Maybe you are migrating an existing MySQL DB to RDS and its setup using the MyISAM DB engine. AWS recommends to only use the InnoDB storage engine so if you have a MySQL DB that has MyISAM you should consider converting those tables to InnoDB. Its okay if you do this after moving to RDS. AWS mentions that keeping MyISAM tables may lead to unreliable behavior when restoring from backups as MyISAM does not support reliable crash recovery.

In my case, I had already moved my WordPress DB to Amazon RDS. I do not have the DB publicly accessable and you shouldn’t either especially if the front end server is on ec2. If you are wondering about RDS security, Hexatier has a good article on securing Amazon RDS here. Keep in mind your RDS and EC2 security configuration when attempt to connect to do maintenance on your DB. My first
step was to connect to my EC2 instance in the same security group as my RDS DB to use the mysql client to modify the MyISAM tables.

To connect to a remote MySQL DB:
mysql -u dbusername -p -h dbhost

After connecting run this command to convert all dbname MyISAM Tables to InnoDB


ref here

-> ;
|   |
| ALTER TABLE dbname.wp_commentmeta ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_comments ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_contact_form_7 ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_links ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_ngg_album ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_ngg_gallery ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_ngg_pictures ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_options ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_postmeta ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_posts ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_term_relationships ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_term_taxonomy ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_terms ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_usermeta ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_users ENGINE=InnoDB; |

So now just run the above MySQL commands to convert each table to InnoDB.
After converted in the event of a DB crash you should now be supported by the InnoDB DB engine reliable crash recovery feature.

Migrating a Linux OpenVZ Virtual Server (VPS) from vePortal to SolusVM

To start export an OpenVZ virtual server from the vePortal client portal:

1.      Login to the users veportal account with the username and password:
2.      Visit the Backup Functions tab
3.     Click New Backup, make sure the Backup limit is not reached under VPS Resource Limits on the left hand information pane.
4.      Wait until backup completes (this may take awhile)
5.     Click Download Backups in the Backup Functions Tab
6.      Now you should have a tar.gz file for the VPS backup. This is the data you will be migrating. You’ll need to FTP this somewhere soon.

Important: Take note of the OS and arch of the VM you have just exported. You’ll need to create the same container in your SolusVM master.

To import (create db entries in solusvm) a OpenVZ virtual server from the admincp do the following. (Ref:[]=import&s[]=openvz)

7.     First enable Import Mode from the admincp go to Configuration » Settings » Other and tick Import Mode   ON . Click Update.
8.     Select Virtual Servers » Add Virtual Server
9.     Select OpenVZ
10.     Select the Node & Plan then click Continue
11.     Enter the container id of the virtual server you want to import (as show under vzlist -a on the host node) in the Container ID field. Also go back to Virtual Servers -> List Virtual Servers and pick the next highest number in the VMID. If the greatest take number of an active VPS is 118, you will use 119.
12.     Fill in the rest of the fields, select the client, give it a hostname, and choose the OS and Architecture of the VM that you exported (important to match the same arch and close version).
13.     Click the Create Virtual Server button
14.      Save the virtual server details, click continue.
15.     Now if you are only importing this VPS,  DISABLE Import Mode from the admincp go to Configuration » Settings » Other and tick Import Mode  OFF .

Now you have the shell of the VM and its created in SolusVM! Half way there. Time to move your data over.

Move and prepare your data
16.      Take your tar.gz from the export and FTP it somewhere secure.
17.     If the file has a ‘-‘ before the filename, remove that dash. Linux doesn’t particularly like files that start with a -.
Ex:     -backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar.gz

18.     SSH to the SolusVM slave where you setup this VPS in step 10.
19.     Change directory to /vz/private

Note, there is no directory for the VPS you just created in SolusVM

[code lang=”bash”]
[root@reek ~]# cd /vz/private/
[root@reek private]# ls
113 117 118
[root@reek private]#

20.     So lets create the folder for the VPS we are migrating.
[code lang=”bash”]
[root@reek private]# mkdir 119
[root@reek private]# ls -al
total 24
drwx——.  6 root root    4096 Jun 26 20:10 .
drwxr-xr-x.  9 root root    4096 Jun 16 23:24 ..
drwxr-xr-x  23 root root    4096 Jun 26 02:04 113
drwxr-xr-x  20 root solusvm 4096 Jun 25 10:44 117
drwxr-xr-x  21 root root    4096 Jun 25 11:05 118
drwxr-xr-x   2 root root    4096 Jun 26 20:10 119
21.      Make sure perms and ownership are OK
22.      Change to the directory you just created and wget the tar.gz that you uploaded in step 16
[code lang=”bash”]
[root@reek private]# cd 119
[root@reek 119]# ls
[root@reek 119]# pwd
[root@reek 119]# wget[/code]
Again, make sure you removed the – in the front of the filename. You can do this now on the destination server if you want too.
[code lang=”bash”]
[root@reek 119]# ls

23.       I ran into some errors when trying to untar the tar.gz, so follow these commands:
[code lang=”bash”]
[root@reek 119]# gunzip backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar.gz
gzip: backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar.gz: decompression OK, trailing garbage ignored
[root@reek 119]# tar xf backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar
[root@reek 119]#
24.      Okay, so note the VMID of the export and the new VPS are different in this case (3005136 and 119). So we need to manually move files after they have been uncompressed.[code lang=”bash”]
[root@reek 119]# ls
backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar etc vz
[root@reek 119]#
25.      Drill down in the uncompressed files (/vz/private/YOUROLDVEID)
[code lang=”bash”]
[root@reek 3005136]# pwd
[root@reek 3005136]# ls aquota.user bin boot dev etc home lib lib64 media mnt opt proc root sbin selinux srv sys tmp usr var
[root@reek 3005136]#
[/code] Here we have found what we are looking for! Our VMs data.
26.      You want to move all of these files to the /vz/private/NEWVEID directory. In our case /vz/private/119/
27.      So move those..

[code lang=”bash”]
[root@reek 3005136]# mv * /vz/private/119/
mv: overwrite `/vz/private/119/etc’? y
mv: cannot move `etc’ to `/vz/private/119/etc’: Directory not empty
[root@reek 3005136]# ls
[root@reek 3005136]#
[/code] It gave me an error because there was already an etc/ in the /vz/private/119 dir. So remove that old etc and put the one from the decompressed file. Make sure files in both seems fine before removing and moving…
[code lang=”bash”]
[root@reek 3005136]# rm -rf /vz/private/119/etc/
[root@reek 3005136]# ls
[root@reek 3005136]# mv etc/ /vz/private/119/
[root@reek 3005136]#
29.      Now we are in business and almost done. Drill down into our target directory (/vz/private/119). Make sure everything looks good. Compare dirs with other VMs you have that are working if you need a sanity check.
[code lang=”bash”]
[root@reek 3005136]# ls
[root@reek 3005136]# cd ..
[root@reek private]# cd ..
[root@reek vz]# cd ..
[root@reek 119]# ls aquota.user backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar bin boot dev etc home lib lib64 media mnt opt proc root sbin selinux srv sys tmp usr var vz
[root@reek 119]#
[root@reek 119]# pwd
30.     Remove the vz folder, we don’t need that anymore.
[code lang=”bash”]
[root@reek 119]# rm -rf vz/
31.      Almost there. Try and start your new VPS.
[code lang=”bash”]
[root@reek 119]# vzctl start 119
Container config file does not exist
[root@reek 119]#

Create the VMs config file
32.      It wont start without a config file! You’ll need to make one. Its easy, lets do it now.

[code lang=”bash”]
[root@reek 119]# cd /etc/vz/conf/
[root@reek conf]# ls
0.conf              102.conf.destroyed  104.conf.destroyed  106.conf.destroyed  108.conf.destroyed  110.conf.destroyed  112.conf.destroyed  114.conf.destroyed  116.conf.destroyed  118.conf            120.conf.destroyed  ve-105.conf-sample  ve-107.conf-sample  ve-basic.conf-sample  ve-vswap-1024m.conf-sample  ve-vswap-256m.conf-sample  ve-vswap-4g.conf-sample    ve-vswap-solus.conf-sample 101.conf.destroyed  103.conf.destroyed  105.conf.destroyed  107.conf.destroyed  109.conf.destroyed  111.conf.destroyed  113.conf            115.conf.destroyed  117.conf            119.conf.destroyed  121.conf.destroyed  ve-106.conf-sample  ve-108.conf-sample  ve-light.conf-sample  ve-vswap-1g.conf-sample     ve-vswap-2g.conf-sample    ve-vswap-512m.conf-sample
[root@reek conf]#
33.       Copy a config file of an existing running VPS. I’ll use 118.
[code lang=”bash”]
[root@reek conf]# cp 118.conf 119.conf
34.      Edit that config file. You’ll need to update just a few fields towards the bottom. OS template, IP, and Hostname. Make sure these match with the data you gave SolusVM when you created the VM (Hostname, IP) and from the export (OS+arch).
[code lang=”bash”]
[root@reek conf]# vi 119.conf
35.    Try and start your VPS it should come right up
[code lang=”bash”]
[root@reek conf]# vzctl start 119
Starting container…
stat(/vz/root/119): No such file or directory
stat(/vz/root/119): No such file or directory
stat(/vz/root/119): No such file or directory
Initializing quota …
Container is mounted
Adding IP address(es):
Setting CPU limit: 400
Setting CPU units: 1000
Setting CPUs: 4
Container start in progress…
[root@reek conf]# vzlist -a
113         79 running                             
117         16 running
118         86 running
119         19 running
[root@reek conf]#
36.       Check in the SolusVM master your VM should be green and running fine.
Don’t forget to remove the backup file that you used.

Comment on this post if I missed anything or if you are having any issues:).