Some Quick cli Syntax for Postgres

How to Dump SQL result to a text file using cli

> psql -U postgres -d [database_name] -c ‘SELECT * FROM users’

How to exit postgres command-line utility psql
Type \q and then press ENTER to quit psql

Reset Root Password on MySQL Database
Notice that in PostgreSQL superuser is called postres (not root). If you forgot superuser password, you can reset it this way:

edit file pg_hba.conf

> sudo vim /etc/postgresql/9.3/main/pg_hba.conf

and find there a line similar to:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                md5

md5 here means that postgres asks for password. If there is this line then replace
md5 with peer, if there is no this line then add it

local   all             postgres                                peer

save the file, exit and reload postgresql service to pick up the updated configuration:

> sudo service postgresql reload

Then you should be able to get access to psql without providing a password this way:

> sudo -u postgres psql

In psql you can change user password using this command:

ALTER USER postgres PASSWORD 'new password';

PostgreSQL performance and monitoring

switch current database

\connect database_name or \c database_name

you can user autocomplete by pressing [tab]

list tables in current database

\dt

create a new database and import tables, data from a dump
in psql

CREATE DATABASE some_database

create a dump (in terminal)

> pg_dump dbname > outfile

You may notice that no password is used. That is because with Postgres you can setup a password file that is used to verify your access as such:

> vim ~/.pgpass

Then (format is hostname:port:database:username:password):

localhost:5432:mydbname:postgres:mypass
[ctrl-x] then confirm with y, then hit [enter]

save and exit

> chmod 600 ~/.pgpass

restore a dump (in terminal)

> psql dbname < infile more details on dump-restore

create a user (it sql)

CREATE USER tester WITH PASSWORD 'test_password';
GRANT ALL PRIVILEGES ON DATABASE "test_database" to tester;

notice difference in quotation marks – in first case it must be single quotation mark ‘ and in the second case – double quotation mark “, otherwise PostgreSQL raises a syntax error

restart PostgreSQL

> sudo service postgresql restart

other regular service actions available too – [force-reload, reload, start, status, stop]

Also for Nginx (unrelated to this post really), Restart Nginx

sudo service nginx restart
other regular service actions available too – [force-reload, reload, start, status, stop]
reload only reloads configuration files, without stopping a service (which restart does)

Related: How to Dump SQL Result in MySQL to a Text File Using cliReset Root Password on MySQL DatabaseSome MySQL cli Syntax

How to Manually Run Cron Tasks

To manually run cron tasks you can use the run-parts command in Linux.

So to run your cron-weekly, for example, to test that a fix you just made runs without error (this is what I just did, in fact)

run-parts /etc/cron-weekly

run-parts will run all the executables in a directory (you must point at the directory). So if you have several files in cron-weekly to run, you can’t just point to one of the files.

You may run into environmental differences running the script as a different user than the cron test runs at, so you can run as that user if needed. You need to be aware this is a quick and simple way of testing part of the process but it doesn’t do a perfect job of testing if it works as a cron task. But it will let you catch some failures quickly and fix them in time for the actual cron task to run. So do check that the everything works after the real cron job runs.

This is just the kind of thing I said I would put in this blog. Simple stuff but things I forget – so I put it here to remember and maybe help out others, like me, that need really basic tips.

If you have a cron task item (or have setup the whole task this way) that is just a script and you just want to test that 1 item you may run the script directly. For example (for a Linux shell script):

sh /etc/cron.weekly/your_crontask_script.sh

Related: Updates Needed When Upgrading from Apache 2.2 to 2.4Rsync to copy Files Between Servers and ComputersBash Profile Adjustments for Scrolling HistoryChecklist: Setting Up a New Domain on VPS

Chrome Remote Desktop

I am more often frustrated by Google the last few years that pleased with them. But they do still provide some pretty awesome tools. For example, Chrome Remote Desktop lets you access a computer over the internet (and lets you to allow another user to access your computer securely over the internet).

Chrome Remote Desktop allows users to remotely access another computer through Chrome browser or a Chromebook. Computers can be made available on an short-term basis for scenarios such as ad hoc remote support, or on a more long-term basis for remote access to your applications and files.

screen shot of chrome remote desktop

Chrome Remote Desktop is fully cross-platform. Provide remote assistance to Windows, Mac and Linux users, or access your Windows (XP and above) and Mac (OS X 10.6 and above) desktops at any time, all from the Chrome browser on virtually any device, including Chromebooks (including Android phones and iPhones). The iPhone app is new.

Some users worry about installing such an app given all the spying and hacking scandals. That is not a completely crazy worry. Google, and others, have been taking advantage of weak user control (and even bugs and work arounds to avoid stated user preferences) to track users and use that information to make money selling ads. With many cool and useful tools there are risk of them being misused. And practices of governments and huge corporations have been so egregious to give a sensible person pause. Still in the right situations this is a pretty cool looking tool (similar things exist but the combination of price [this being free] and simplicity make this interesting).

Related: Chrome Remote Desktop support forumUsing scp (secure copy) to Copy Files Between ComputersUsing Rsync to copy Files Between Servers and ComputersLinux/Ubuntu File and Directory PermissionsGovernments Shouldn’t Prevent Citizens from Having Secure Software Solutions

Compare WordPress Files on Server to Proper WordPress Version

Sadly one of the hassles in managing your own WordPress blog is dealing with people that use your blog to serve spam content. These hacks can insert spam links into your pages and posts or create spam directories that are completely their own content on your domain.

There are many issues to deal with in re-establishing control of your server; but that isn’t the scope of this post.

This is just a tips if you are troubleshooting to try and determine what is going on. Often your server has been hacked to allow uploaded php pages to be added or for WordPress php files to be edited.

One way to track down if the files have been changed or new ones added is to compare the WordPress files on your server to the current files for a fresh WordPress install. This assumes your blog is using the current version, which hopefully it is because on the big improvement WordPress made is to make those updates automatic. That greatly reduces the chance to have WordPress be the vector to infecting your server. If you were using a older version then just compare to the field for that version from the WordPress server.

If you don’t have a current backup I would make a backup before I tried this. Obviously, don’t make any deletions or changes to your server unless you understand what you are doing. You can create big problems for yourself.

You can use the diff command to view the difference between WordPress on your sever and the fresh install from WordPress. I install the new WordPress in a new directory outside public_html. At the cli on a Ubuntu/Linux server:

sudo wget http://wordpress.org/wordpress-4.0.zip
unzip wordpress-4.0.zip
diff -rq wordpress ../public_html/

wordpress-4.0.zip – replace with whatever the version is you are using.
../public_html/blog/ – replace with the path to your blog

Continue reading

Zoom Level on Embedded umap OpenStreetMaps

Curious Cat uses OpenStreetMaps and umap to create my own maps. Then I can embed them on my sites, for example here for Chiang Mai Thailand.

For some reason when you use umap and select the embed option it gives you code that leaves off the zooming altogether. It is an easy fix (and I imagine the code will be fixed making this simple advice obsolete). But for now this is all you need to do.

The url umap adjusts as you zoom the map in your browswer. For example, going to

http://umap.openstreetmap.fr/en/map/chiang-mai_18602

which the Curious Cat Chiang Mai map it will “redirect” to some really far out zoom level

http://umap.openstreetmap.fr/en/map/chiang-mai_18602#6/18.719/99.657

Then you can zoom in a bunch to get the level you want to use to embed.

http://umap.openstreetmap.fr/en/map/chiang-mai_18602#14/18.7851/98.9907

The code created for you to paste into your page is

<iframe width="100%" height="300px" frameBorder="0" src="http://umap.openstreetmap.fr/en/map/chiang-mai_18602?scaleControl=false&miniMap=false&scrollWheelZoom=false&zoomControl=true&allowEdit=false&moreControl=true&datalayersControl=true&onLoadPanel=undefined"></iframe><p><a href="http://umap.openstreetmap.fr/en/map/chiang-mai_18602">See full screen</a></p>

Which doesn’t include any zooming info so you get the super far our zoom default.

You need to add to the url

src="http://umap.openstreetmap.fr/en/map/chiang-mai_18602

so that it includes the zooming info. So it would be

src="http://umap.openstreetmap.fr/en/map/chiang-mai_18602#14/18.7851/98.9907

You can also see the link to “See full screen” isn’t using the zoom settings. If you wanted that link to a zoom setting of your choice you can set that also.

Note the view on your screen and the embedded map isn’t going to be identical. The map will likely cut off some of what you see (due to the sizing of the embedded map). You can also adjust the sizing of the embedded map by adjusting the height (they default to 300px, but you can make it 400px or whatever you want).

Related: Multiple WordPress Blog Networks on One Server – Overcoming ConflictsChecklist for Setting Up a New Domain on VPSLinux/Ubuntu File and Directory Permissions

How to Dump SQL Result to a Text File Using cli

Just a quick tip on how to dump a sql result to a txt file.

mysql -e “select * from [table]” -u[user] -p [database] > sqlresult.txt

For example:

mysql -e "select * from orders where product_id = 15" -uroot -p  > sqlresult.txt

Related: Some MySQL cli Syntax ExamplesBasic MySQL Performance MonitoringMySQL Performance Tuning Tips

Turn It Off and On Again

I had a WordPress blog lose the database connection. As I tried to troubleshoot it I rapidly got to the point of thinking that maybe just rebooting the server would fix things – since no changes had been made that should cause the database connection to be lost. But also I figured I couldn’t be so lucky that such a simple thing would work.

I tried to log into the database using the cli and it was failing. MySQL was still running – according to top. I have automated security updates setup for the VPS server running Ubuntu. My thought now is those updates somehow messed things up to the extent the server database connections somehow wasn’t working. I sure hoped that was the case.

I rebooted and hoped.

After a bit I was able to see that it was working.

When I went to reboot I noticed I hadn’t rebooted in a long time. My guess is I might have the automated security updates setup wrong on this server (maybe it is installing more than just the security updates), I’ll check. I have not had this problem before with other servers, obviously breaking a database in this way would cause lots of problems on production machines so I tend to think it is more likely I messed something up than this is a likely outcome when using automated security updates (but such things are possible which is why I think places with full time system admins and important servers rely on manual updates with professionals watching everything to be sure nothing obvious breaks).

My feeling is the longer you go with not rebooting the server the more likely some issues are to crop up (but also every reboot is more like to result in some broken thing, right then, than doing nothing so it is a tradeoff). And I could be wrong in that feeling, it is just what I guess without much evidence to support my guess.

Anyway I was very happy turning it off and on again worked. Honestly I went ahead and updated the server before I rebooted, but my guess is just turning it off and on again would have worked.

Related: Making Sure You Don’t Run Out of Space on Your VPSBasic MySQL Performance MonitoringWordPress error: Image could not be processed. Please go back and try again.

ModSecurity – Adjustments for WordPress

How to secure an Ubuntu 12.04 LTS server – Part 1 The Basics is a great resource to secure a VPS.

Mod_Security is a web application firewall. I found a couple things to add to my servers.

The same site includes a very good guide to installing it (you also may well want to whitelist Googlebot, instructions in the link). However it blocked my access to one of my sites. You end up just getting the message:

“Forbidden

You don’t have permission to access / on this server.”

There are tips on some edits to include when using WordPress on an Apache server with modsecurity.

One simple action is to include

SecRule SERVER_NAME “[your-domain.com]” phase:1,nolog,allow,ctl:ruleEngine=off

SecRule SERVER_NAME "code.curiouscatnetwork.com" phase:1,nolog,allow,ctl:ruleEngine=off

on the bottom of modsecurity.conf which is found /etc/modsecurity (for me, on Ubunutu 12.04). Then restart Apache

sudo service apache2 restart

and see if the problem goes away. If it does then you have a very good indication modsecurity was blocking access and can continue to narrow the scope of the problem by adding the WordPress whitelist rules in the link above.

Another note, service apache2 start, failed in a non-obvious way to me anyway. For me if I use sudo it works fine. If I don’t it gives odd errors which lead me on a 10 minute wild goose chase before remembering to try sudo.

Related: Upgrading VPS Web Server from Ubuntu 10.04 to 12.04Keeping Your Hosted Ubuntu Web Server Software Up to DateLinux/Ubuntu File and Directory Permissions

How To Transfer Domain Registrar From Godaddy To Gandi.net

I was asked to take over managing a few domains for someone, part of this includes renewing the registrations. As they currently are on Godaddy and having to deal with that system would annoy me and waste my time I am transferring the registrar to Gandi.net.

As part of the transfer process you must make sure the domain is unlocked, which I did. Then you must send yourself the authorization code (for making a transfer). You need to provide this code to the new registrar. Not surprisingly Godaddy attempts to make this difficult. Then the email just says: prog_id=GoDaddy&isc=aaaa111 That will not work. I was able to find a way to download the code for all the domains (you have to create an export list, instead of just viewing it from the domain page). That method provided a 16 digit (alpha and numeric and very occasional special character) codes for all the domains.

Gandi.net makes the transfer process very nice and smooth with a easy to follow user interface. They do cost a bit more than others but they are well worth it in the time you save for most people (if you really want to save every last dollar you can find cheap registrars that are less annoying than Go Daddy).

Related: Overcoming Conflicts with Multiple WordPress Blog Networks on One ServerChecklist for Setting Up a New Domain on VPSSimple Process to Claim Authorship of Pages with Google

Setting Up 2 Factor Authentication for Gmail

Sadly there are significant risks to having valuable information accessible via the internet. Someone with access to your accounts is a huge risk and it can be a huge hassle to deal with what they can do with that access. Email is one of the most critical, there is tons of information you need, and often the email account can be used to get access to many other accounts (obviously financial sites are also critical, as are others). You should set up 2 factor authentication for critical sites.

2 factor authentication is made of up 1 thing you know, and 1 thing you have. So you know your password. The easiest other item is a smart phone (that is the default option for gmail and most places for the thing you have). Some banks and computer networks (intranet access) use security tokens. Either way they give you a code which you then enter after you enter in your username and password.

Gmail offers a app for smart phones that will generate these codes even if you don’t have cell phone service. Also, if you don’t have a smart phone, or lose yours there is a backup thing you can have (pre-printed codes). Make sure you print these and keep them somewhere you can access them (Google suggests your wallet).

With Gmail you will be prompted for your 2nd level authentication if you attempt to access with a computer that has not been authorized previously. If you are using a computer that you logged into your account with and told Google to authorize you will login normally. Every 30 days Google will then have you provide the 2nd level authentication (I think this is right, I might not have it exactly right for when Google asks for 2nd level authentication).

Whatever email account you have I would strongly recommend having 2 factor authentication setup. And you then must be sure that you can gain access. It is a bother but you need to make sure you understand the process and understand how to cope with issues (losing your smart phone, etc.).

Once you setup your account for 2 factor authentication make sure you add a secondary phone number backup (in case you lose your phone or it is stolen). And make sure you print out the backup code.

Process:

Continue reading