Restore a single MySQL Table

If you ever had a problem with a MySQL Table being foobar because of a human error (or other bug), you might need to be able to restore only 1 single MySQL table.
Restoring an entire Database may cause other data loss, and this is of course something we don’t want.

So, if you have a mysql dump available, you can ‘filter’ our that specific table by using the sed command.

Let say the name of your table is tableToRestore and the file dbdump.sql is the file containing your database backup:

This will copy in the file dbdump.sql what is located between CREATE TABLE tableToRestore and the next CREATE TABLE corresponding to the next table.

You can then adjust the file dbdump_tableToRestore.sql which contains the structure of the table tableToRestore, and the data (a list of INSERT commands).

Migrating a DNS server to PowerDNS

Today I had to migrate a customer’s DNS server to a new server… This because the old server (a very old plesk instance) was end of life.

As I have very good experiences with PowerDNS, we decided to migrate from Bind (Named) to PowerDNS.

PowerDNS provides you with the utilities to do so, but I did not find a straight forward way to export/import zones from Bind into PowerDNS.

Installation of PowerDNS and PowerAdmin (a PowerDNS Gui) is out of the scope of this document, so I assume you already have a working PowerDNS server.

These are the steps I had to perform to migrate from the Plesk 8.2 server to PowerDNS:

1) First of all, you need to allow zone transfer (AXFR) on your bind server from your new PowerDNS server IP. You need to change the contents of /etc/named.conf in something like this (add the allow-transfer lines in the Options block.

2) Reload or restart named to take effect:

3)Test a zone transfer, eg:

4) Now that you know zone transfer is working, create a list of domains that your Bind server is ‘serving’.  In my case i have to remove some extra .lock and .saved_by_psa files from my listing. If you use this tutorial, your command can look differently of course.

If needed: check if the domains in bind still use your old nameservers… (do not polute your new powerDNS server :))

Then delete all domains from /root/domainlist.dns if they not valid anymore
5) Import your zones by:
a) Create Zone Import SQL scripts

From these generated files, remove duplicate SOA records from all import files:

If needed: change TTLs:

Depending if your imported zones need to be set as NATIVE or MASTER

Change extra stuff with sed, like hostmaster and so on

b) Test import into mysql

c) Verify in poweradmin and drop the imported zone (or you’ll get an import error on the next step, which is not bad or something… but hey :))

d) Import all your zones, if you like what you have done:

 

Puppet: Add MySQL User

Want to add a user to MySQL that you can use for eg backups, monitoring,… or any other tasks this user needs to perform. (in this example a root user)

Create a small definition

This

From your nodes.pp you can call this by:

 

You can always use the Puppetlabs-mysql module of course!
Check here: https://github.com/puppetlabs/puppetlabs-mysql