Posts tagged MySQL

Hiding MySQL/PostgreSQL password from the “ps” command

If you want to run a database tool for MySQL from the command line it would seem you cannot avoid including the password using a –password argument. The problem is that the whole command line while the command is being run will be visible to any other user of the same server using the “ps” command.

The solution is to specify the password in a “option” file (as MySQL calls it). This is a file named .my.cnf (note the dot at the beginning) in your account’s root folder (ie: /home/myname/ or /root/ for the root user) with the following contents:

[client]
password=password

Of course replace the “password” with your real password. You should als make this file only readable by yourself with the following command:

chmod 600 .my.cnf

That ensures that nobody else can open the file (well except root if you are not an admin, but the root user would have access to everything anyway).

Now whenever you run a MySQL tool (mysql, mysqldump, etc) the password from the option file will be automatically used. If your username matches the MySQL username you also don’t need to specify a username. This can be very useful especially as root, when you need to do daily backups using mysqldump for example.

This tricks works in a similar way for PostgreSQL: put a file named .pgpass with the following contents:

hostname:port:database:username:password

You can use an asterisk (*) to match “any” value (wildcard), for example for port or database.

Multiple DNS servers with PowerDNS and MySQL replication

With DNS it is essential to have at least two and preferably more DNS servers for your domains in geographically separated locations. Putting all your DNS servers on the same server is asking for trouble: even if your server goes down for only a little while (like a reboot) some visitors may perceive your sites due to negative DNS caching (where a visitor’s ISP resolving DNS server will “remember” your site “does not exist” for a while).

There are of course several commercial DNS hosting providers that can solve this problem for you, but most of these charge by how much DNS traffic your domains generate. For certain types of very popular sites (like image & file hosting sites) that may be costly because of the level of DNS traffic they generate. Or perhaps you simply want to maintain your own DNS servers.

The solution is to have several DNS servers powered by PowerDNS using MySQL as a backend, and synchronizing the DNS servers not using any DNS specific mechanism but simply through MySQL replication.  As the main DNS server you could use your own server, and as secondary servers you can use other servers or cheap VPS servers.

As PowerDNS supports caching using MySQL as a backend is not going to be a performance issue unless you really have a lot of different domains you want to provider DNS for (and in that case, just get beefier hardware or more servers). For information on how to setup PowerDNS with MySQL, see the official documentation.

To setup MySQL replication I recommend this guide, although the part on the first page about getting a snapshot of the master server (using the lock/unlock commands) is a bit obsolete if you used InnoDB to create the tables for PowerDNS. With InnoDB you can get a snapshot without any interruption with a single command:

mysqldump --opt --single-transaction --flush-logs --master-data=1 pdns > pdns.sql

The “master-data=1” bit even includes the right CHANGE MASTER command in the dumped SQL file, so you don’t need to manually specify the master position and only need load the SQL dump and restart the slave. Be aware though that MySQL replication might sometimes break (for example if one of the servers was uncleanly reboot) so occasionally check if MySQL replication is still working from phpMyAdmin or using the SHOW SLAVE STATUS command. For DNS troubleshooting I highly recommend intoDNS.com.

I apologize this post is not a straight-forward how-to, but hopefully it will point you in the right direction.

Go to Top