Sudo Make Install

cPanel typically has some decent documentation that helps you get something done, but with the remote mysql server they really screwed the pooch.   So heres a very rough guide and aggressive way of setting up a remote mysql server with all the data from the local one.

http://docs.cpanel.net/twiki/pub/AllDocumentation/TrainingResources/TrainingSlides07/MySQL_Remote_Server.pdf

Not very helpful is it?   Okay here's how we do this.

DB# yum install mysql server -y

DB#service mysqld stop

Web# scp -r /var/lib/mysql/ root@db:/var/lib/mysql

At this point you have NO idea what the root password on the db mysql server is.

Reset it with this guide > http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Install phpMyAdmin to the DB server so we don’t have to work with 19th century cli tools.

On the Web box go to  SQL > “Setup Remote MySQL server”  then type in the ip and ssh port, select  login as: root and select authentication method as password.  Those are your SSH credentials, you can use ssh keys and all that crap but its only for running a script one time on the remote server.

Once you do that it will fail and be all nasty, don’t get upset its literally just to taunt you.

DB# cat /root/.my.cnf

This will contain a password that you need to setup in a moment. Memorize it or something useful…

Login to phpMyAdmin on the DB server, then click permissions.

Okay so here’s the part where you need to use your brain to make a decision.  I don’t know how your setup works so I can’t really tell you what to do, but what I can show you should put you on the right path.   Essentially the IP you use to SSH over is what cPanel will stick in the config as the DB server address or FQDN (Fully Qualified Domain Name)  So under privileges we need to create a user with the following:

User Name: root

Host: the ip or FQDN you plan on feeding cPanel.  Personally I think IP makes more sense here!

Password: remember what I had you waste your time memorizing earlier? Yeah type that here.

Global Privileges, you need to "check all" this.  This makes the magic happen!

Now go back to cPanel setup for the database and run it again, it should work…

In case you don’t recall or weren’t reading when it was put up, I did an article on why a downloaded my.cnf is a bad idea. It’s time for a bit of appending to that. I am cranking out my usual support tickets helping little old ladies with their sites about their pinochle groups (no not really) when I get a support ticket from a client who is having issues with their databases not working properly.

Naturally, being the curious sort I am and them being a paying client, I log into the server and start examining things. The first thing I do is try to do a database check. This doesn’t go over too well, I get a bunch of errors about “corrupt .frm files.” Naturally my curiosity is piqued at this point, so I go and start looking through /var/lib/mysql/hosts.err file. What is extremely odd here is that there it is super short, and there is nothing there really. At this point I decide to log into mysql and try rotating out the log file. It indicated that the operation had completed successfully, so I am a bit baffled at this point. Looking in the my.cnf, it appears that someone downloaded a web based my.cnf and stuck it on the server. It also appears that the logs are going somewhere else at this point. After examining the correct logs, I find a bunch of errors about the .frms being corrupted. I then start looking for the MYIs so that I can see if I can rebuild the .frms with them. To my dismay I find out there are no MYIs for the DBs here just the .frms.

After updating the client on what I’d found, I start looking over the net for an answer. It turns out that if InnoDB doesn’t have the engine started it will default to MyISAM databases, which is where the .frms came from. Definitely getting closer at this point, so I start looking at the engine statuses and of course InnoDB is disabled.

After this the picture is starting to come into focus a bit more, something in the my.cnf was changed and it broke everything. But what? I had noticed that the server was running MySQL as root when I had done “ps aux | grep mysql” earlier so I had a suspicion. Referencing another server I confirmed what I thought and found out that a line was missing.

user=mysql

The net downloaded configuration didn’t have a set MySQL user, root didn’t have permission to access the file and therefore it wouldn’t load the InnoDB files and in turn the InnoDB engine. A single line was missing and this production server was likely down for hours while they tried to figure out what happened. The internet is not a substitute for knowing what you are doing. Yes it can help you in this regard, but at the same time it is not a substitute for knowledge or competency. I once had an instructor tell me there is no crib sheet for life, it rings as true now as it ever did when he said it.

Well Enom has done it again, they are now offering a new TLD for “.PRO”.  They have some premade domains that you can buy a subdomain on for a discounted price as well.   I went out searching for a few good ones but they were already taken, maybe they will get “.PROS” sometime soon.

In the mean time, check it out: http://www.enom.com/tlds/dot-pro.aspx

I gotta say that over the years I feel that Dell has burned a hole into my heart producing cheapo equipment just to make the sale.   Well in the past 2-3 years it seems that Dell has really kicked the quality of their product line up a few notches.   Today I was working with a client on our Managed Colocation service, we needed to order his servers so we went ahead assembled them on the website then called dell.

To my surprise the business sales rep was quick to load up the saved cart and begin to check it over with us.   After we made sure everything was listed correctly the rep instantly told us that he could save $600 on the R710 and $400 on the R610, We didn’t even begin to haggle him.   After we agreed on that pricing we then made the one and only request to get more for our money, we asked for idrac enterprise on both servers for free, our wish was granted instantly.

Typically we buy all of our servers through a reseller but for this particular client I felt ordering direct would be a much better experience for them and it paid off.  I have to give my congrats to Dell on earning back my trust in there sales team, I’m absolutely happy to work with them and to use there new servers.

On a side note for any one looking at buying a new OEM server the R610/R710 are absolutely phenomenal buys, and they come with the newest intel E5600 series CPUs.
Update: June 17th 2010.

Dell shipped the servers 2 days ago (Wednesday) they some how got the incorrect address and sent them to the entirely wrong state, thankfully we caught it and had them redirected to the correct destination.   I think this is terrible on dells part, not to mention they confirmed the address multiple times.

Update: June 21st 2010.

The servers in fact arrived at the WRONG address.  After several calls to dell with no resolution I contacted one of my Business reps at dell and within an hour received a call with an “upgraded” server being shipped overnight to the CORRECT address.  They added a 2nd CPU and 15k SAS disk vs 7200 SATA Disk.
We will see when it gets here if it was worth the wait, the other server is scheduled to arrive tomorrow without issue.

Backing data up in MySQL is super important. The site content is often replaceable, however your board posts, ecommerce orders, etc. are far far harder to replace, if at all possible. The other nice thing is that with a lot of database driven web sites you retain most content even if you only back up the databases. You can still lose things like images, but by and large your posts will still be there even in event of a crash. Since bad code, etc. happens there are a few reasons I would recommend doing backups of the databases.

• Making server side changes, including configuration tweaks that can influence system stability.
• Making software changes, even adding plugins etc. to your site. I have seen odd things in the past caused by plugins and they can interact together with not always predictable results.
• Periodical backups as part of a data protection plan. I would do more than less, by and large most databases are not huge for the data they contain. I have seen forums with about 100,000 posts have about 2GB of database; not really that much if you think about it.

There are a few ways to do backups, but the most common for people who don’t use command line interface is probably going to be PHPMyAdmin. Quite frankly, I do not like using it for database backup and restorations. There are two reasons for this pertaining to reliability

• If the session timeout of PHP or Apache is too low, you will get an incomplete dump. This means that backup you may have of your databases is all of a sudden found to be truncated at a few megabytes. This is not a big deal for a lot of people with small DBs however if you’re rocking out with 50+ megabyte DBs this can be a huge issue.
• Restoration can be dependent on if the person has large uploads and large timeouts enabled. Since most people have slower upload than down, it can be a real nuisance to get the DB back up to the server and restored. Not only that but If you have a server with low upload size limits and timeouts, it may be impossible to get that DB back up and restored to the system before you hit one of these limits.

The way I honestly prefer to back my databases up is mysqldump; this allows one to have a lot of control over what gets dumped, do a full backup without worrying about doing it individually etc. It also allows us to make a nice easy cron job to do these backups too if we want.

The way we back a particular DB is up is:

mysqldump dbname > backup.sql

This will put the database named dbname into backup.sql. I believe you can separate multiple DBs with a space as well, however I don’t normally use that functionality. Another thing you can do is you can back up everything with:

mysqldump --all-databases > backup.sql

and this will do everything on the server. Handy for migrations, etc. Definitely way easier than trying to do this via a web interface and having 1,000 DBs.

How we restore these is fairly simple as well, we just run the command as follows:

mysql dbname < backup.sql

If you have a full DB dump you wouldn’t specify anything for the name when restoring. Notice how the carrat points in the opposing direction though. Don’t screw this up otherwise you will likely end up with a blank file and have to re-dump or re-move the file. I usually make 2 copies on the destination server especially when it’s not easy to get a second copy. There are other ways of moving the DBs over, such as copying the raw MYI files but that will be another blog.

We’ve been offering VPS for a few months now and have been paying close attention to our competitors. After doing the math several times I’m still baffled as to how people can offer 512mb ram on a VPS and sell it for $10 and make a profit.

Say the each host node has 24GB of ram, this allows for roughly 44 VPS giving the node some free ram for cache. Now 44*10 = 440, $440 is what it cost to maintain a server with 24gb ram + bandwidth, power and high performance disk. Where do you make profit at this price?

And it seems like the cheap host always say “we don’t oversell”, well how do you not oversell something like that, whats the point of running a business if you don’t make any money? A lot of the providers are offering 512MB under xen, As far as I know its a LOT harder to oversell a Xen node than it is OpenVZ.

Regardless, even if you do somehow run this server for less than 300/mo I wouldn’t consider the profit made effective once you factor in paying people to handle support tickets ect.

Keep that in mind when your shopping for a VPS, paying the least you can does not mean your getting a good deal.

http://BeyondVPS.net

Paying attention to your Nagios alerts can pay dividends in regards to server uptime and security. Just as an example, If you have an SSH alert come up it’s likely a very good idea to check your logs for failed login attempts. See cool-one-liners-1/ for more info. Chances are someone is eating your connections up and the monitoring server can’t connect.

Working on servers, people seem to think there is a cheat sheet to it at times. While there are many ways to simplify and automate a lot of configurations on a server, MySQL is not the time nor the place to cut corners. If you bog MySQL in turn the system will feel the wrath of your misconfigurations. Here are a few super common mistakes.

• max_connections set way high-Why people think this is even remotely near a good idea is beyond me. It is common to go out to a server, find out it has either flat out hung or is spitting out of memory errors on the screen and has gone brain dead. Just because you can set your connections to 1500 doesn’t mean it’s a good idea. In fact it’s probably better for MOST people to set their connections to 150. The reason is simple; each connection available uses RAM. If a query comes by and there are 150 connections available, you will probably get an out of connections error and can log into the system, figure out what broke and fix it; If you hang the system you get an out of memory error and no real data to work off of. CPanel sets this to 500 by default, if you are using 500 connections (and the system is still up) YOU PROBABLY HAVE A PROBLEM.
• huge buffer sizes-Guess what if you have 100 megabytes of InnoDB tables setting your innodb_buffer_size to 5GB isn’t going to help you out. Plain and simple. If you start adding more tables you could possibly even run out of RAM (see above.)
• Big RAM-MySQL does not like RAM use above 2GB on 32 bit systems. They do not recommend running it because it can cause stability problems. In turn this means that there is little reason not to run a 64 bit OS any more unless you’re running a low end server. You NT guys are pretty much stuck with 2GB no matter if you’re running X86 or X64. That being said for hard core DB applications you should probably be putting it on a unix based OS anyways.

Besides this there is the single biggest reason of all:You will probably have lousy performance compared to a custom my.cnf. When tuning databases, one needs to keep in mind that the my.cnf  is tailored to a combination of the database content, how the database is used and the server its self. If there was a magical my.cnf that would make any server work great don’t you think that Oracle/Sun/MySQL would have included it with the server software? We will go into a few things in later blogs such as software to help tuning MySQL, what the parameters mean and how to actually tune a database properly.

I’ve often wondered how often this happens but this story is pretty cool.

I’ve been working on some new business solutions for a while now involving heavy Colocated setups. Finally have the foot work done and have started offering a solution called “managed Colocation” to clients. Some of you may have heard of this concept before, RackSpace current offers something slightly similar. RackSpace offers managed Colocation in the sense that you have full control over the hardware and can get custom parts installed but never actually “own” the base system. The idea of Colocation is to rent space and bandwidth to the customer for any gear they want, if you’re not allowing them to have ownership of their servers or hardware then its NOT Colocation!

We’ve began to offer a solution that allows clients to ship us their gear or have us order it directly for them. We then rack it for them and take care of any hardware or extreme software failures that may happen.  We are essentially a babysitter for their hardware, this is extremely valuable to small/medium business where hiring someone to handle it just simply not cost effective.

Colocation Vs. Dedicated

Now here’s a topic a lot of people truly don’t understand and I see them paying WAY more than they need to for servers.  Just recently one of our new clients migrated to our Managed Colocation from Liquid Web, they were paying around $1,800 a month in server rental for 3 mid range boxes.   With managed Colocation they are now paying $700/mo for 5U Rack Space, Bandwidth & Power. They also now own their hardware so you will have to account for failure of disk and power supply’s, thankfully most OEM servers have a 3 year warranty on all components.  Depending on how important your server is you will need to at keep extra disk and power supply’s on hand.  Over the period of 3 years we will have saved this client $30,000.

Interested in our Colocation services?  Give us a call http://beyondhosting.net (1-724-790-4678)