Transferring Databases via SSH Command Line

Command Line Fu has a solution for those who have SSH access and need to move a database:

To create a new database with a different name: 

ssh -C user@newhost "mysql -uUSER -pPASS -e 'create database NEW_DB_NAME;'" && mysqldump --force --log-error=mysql_error.log -uUSER -pPASS OLD_DB_NAME | ssh -C user@remotehost "mysql -uUSER -pPASS NEW_DB_NAME"

To use the same database name for target as the source: 

mysqldump --databases --force --log-error=/root/mysql_error.log -uUSER -pPASS OLD_DB_NAME | ssh -C user@newhost "mysql -uUSER -pPASS"

If you’re using SSH on a non-standard port (recommended), you’ll need to add a parameter for it in your code after the ssh command, such as -p XXX, where “XXX” is replaced by your port number.

Addon Vs. Parked Domains in cPanel

Where is the info for Addon Domains stored in Apached? Parked domains? cPanel Docs gives us an answer:

Addon Domains Parked Domains
Main domain appears in the address bar Yes No
Apache directive used VirtualHost ServerAlias
Separate logs Yes No
Separate stats Yes No
Treated as a subdomain (other than URL) Yes No
Ideal for multiple domains sharing the same address No Yes

Deleting an addon domain won’t delete the files or databases associated with that domain. A parked domain, of course, won’t have files or databases associated with it.

Mass Delete Pending Spam Comments from WordPress Database

If you’ve got 14,500 and some pending spam comments in your WP database, I feel your pain. I just transferred a WP site in exactly that state.

Fortunately, you don’t have to scroll through hundreds of pages of the comments moderation que, clicking away to get rid of the junk. A simple line of SQL is all you need to conquer the  spam dragon in a matter of mere seconds. Pop this query into something like PHPmyAdmin where you can run SQL statements directly, and you’re golden. 

DELETE FROM wp_comments WHERE comment_approved = '0'

Ideally, run this on the source database before transfer to make your life easier. Why would you want to move thousands of spam comments anyway, right? As a plus, you may be able to import your database using PHPmyAdmin if you can get your database dump to a reasonable file size via deleting an out-of-control spamfest.

Note:  This command will delete ALL unapproved comments. No second chances, review or confirmation. Make sure that’s your goal before you go running SQL statements all willy-nilly. The management is not responsible for outcomes. Use the force wisely, Luke.

Restarting Haldaemon from the Command Line

Have a need to restart Haldaemon? I did, p0inted out from the ConfigServer Firewall (CSF) error report:

The file system shows this process is running an executable file that has been deleted. This typically happens when the original file has been replaced by a new file when the application is updated. To prevent this being reported again, restart the process that runs this excecutable file. See csf.conf and the PT_DELETED text for more information about the security implications of processes running deleted executable files.

From command line prompt: /etc/init.d/haldaemon restart

At least, that’s where it was on my system.

Moving WordPress Installation to New Domain – SQL

A few database changes need to be done when moving WP from one domain to another; information below taken from a very  helpful article at My Digital Life:

To update WordPress options with the new blog location, use the following SQL command:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

After that you will need to fix URLs of the WordPress posts and pages, which translated from post slug, and stored in database wp_posts table as guid field. The URL values in this field are stored as absolute URLs instead of relative URLs, so it needs to be changed with the following SQL query:

UPDATE wp_posts SET guid = replace(guid, 'http://www.old-domain.com','http://www.new-domain.com');

If you have linked internally within blog posts or pages with absolute URLs, these links will point to wrong locations after you move the blog location. Use the following SQL commands to fix all internal links to own blog in all WordPress posts and pages:

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');

Default New Account Address of Server IP/~username Not Working?

If you cannot access your sites via http://ipaddress/~username (usually used as a temporary address pending domain propagation for new hosting clients), check that the Apache module mod_userdir, if enabled, has an exception configured for the default virtual host.

End transmission.

cPanel /WHM Server Default Parking Page

To set up a default parking page for cPanel/WHM server, you need to place your default files in the proper place.

Check /usr/local/apache/htdocs for when a domain resolves to your server but does not actually exist in the Apache configuration file. If you just need a park page set up you can place it in that directory and name it index.php, index.html, etc.

As always, your configuration may vary.

Solving FTP Deletion Error “550 Prohibited file name”

Trying to delete a file via FTP and unable to? Getting a “550 Prohibited file name” error? Annoying! But also very easy to fix. Create a php file called “unlink.php” with this code, replacing the “YOUR-FILENAME-HERE” with–what else?–your undeleteable file name. 

<?php unlink('YOUR-FILENAME-HERE');?>

Upload it to the directory with the file in question, and visit the script in your web browser. It will remove the previously unruly file. ((Thanks to irasmith’s forum post for this solution.))

PHP noobs, please remember to have NO blank spaces before or after the beginning of your code in PHP files, or you’re liable to get a “Cannot modify header information/Headers already sent” error. You can create the file in a text program and simply rename it with a .php extension.

Setting Up Wildcard DNS for WPMU on cPanel

Allowing people to set up blogs for WordPress MU at blog.domain.tld requires installing your WPMU in the site root and setting up wildcard DNS. In setting up my first WPMU, I ran across this tutorial by Matt Mullenweg of WordPress fame, linked from the WordPress MU docs.

Since I’m not the queen of DNS or anything, I checked with my admin folks to make sure I got the Virtual Host entries correct. I found out this is extremely easy to do if you have a cPanel setup.

Here’s How from my SysAdmin:

To add wildcard DNS and vhost settings in one swoop, simply add a new sub domain to the account and name it ‘*’ (the asterisk character). Make sure it’s document root is pointed to the same directory that your WordPress MU is setup in and cPanel will add everything else to all of the configuration files.

Sure enough, worked like a charm, took 30 seconds, and didn’t require anything scary like restarting Apache–there is no time that runs slower in the time-space continuum than waiting for httpd to restart, man.