Content tagged with: mysql

Using awk, grep, sed, and mysqldump to script and backup your MySQL databases on the command line

Submitted by Eric on Wed, 08/25/2010 - 20:56

In this tutorial, I'll show how you can use awk, grep, and sed (my favorite command line tools) to backup and archive your MySQL databases. This can be useful to schedule a cron job, transfer your databases to another server, or any other type of scripting.

First, you'll have to get acquainted with connecting to and dumping your database on the command line. Depending on your user, credentials, and where the databases are located, your command might look something like this. Please note, there is no space between the password and the "-p" flag.

$ mysqldump -u user -pPASSWORD -h hostname database > database.sql

To simplify my example, I'm going to shorten the mysqldump command to the follow.

$ mysqldump database > database.sql

Now that we're MySQL command line pros, I'll break down each command. I'll start by showing all the databases.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases"
+---------------------+
| Database            |
+---------------------+
| customers           |
| db_pics_ericlondon  |
| db_thedrupalblog_d6 |
| drupal              |
| drupal-pics         |
| drupalmusicproject  |
| itunes              |
+---------------------+

Now, I'll "pipe" the output from the previous command into awk to show the first column data.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}'
Database
customers
db_pics_ericlondon
db_thedrupalblog_d6
drupal
drupal-pics
drupalmusicproject
itunes

And use grep to remove the first line that says "Database".

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$
customers
db_pics_ericlondon
db_thedrupalblog_d6
drupal
drupal-pics
drupalmusicproject
itunes

And use sed to build the mysqldump command. This one is kinda tricky, sorry. As you can see, I also embedded the date command in there to generate today's date in the format: YYYYMMDD.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$ | sed 's/\(.*\)/mysqldump \1 > \1.'$(date +"%Y%m%d")'.sql/'
mysqldump customers > customers.20100825.sql
mysqldump db_pics_ericlondon > db_pics_ericlondon.20100825.sql
mysqldump db_thedrupalblog_d6 > db_thedrupalblog_d6.20100825.sql
mysqldump drupal > drupal.20100825.sql
mysqldump drupal-pics > drupal-pics.20100825.sql
mysqldump drupalmusicproject > drupalmusicproject.20100825.sql
mysqldump itunes > itunes.20100825.sql

Lastly, if everything looks good, you can pipe the output back to the command line.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$ | sed 's/\(.*\)/mysqldump \1 > \1.'$(date +"%Y%m%d")'.sql/' | sh

Eric-Londons-MacBook-Pro:backup Eric$ ls -1
customers.20100825.sql
db_pics_ericlondon.20100825.sql
db_thedrupalblog_d6.20100825.sql
drupal-pics.20100825.sql
drupal.20100825.sql
drupalmusicproject.20100825.sql
itunes.20100825.sql

You could even take this one step further and pipe the output through gzip to compress the dumps :)

Add new comment

Exporting iTunes data into MySQL and creating SQL to show top rated albums

Submitted by Eric on Sat, 06/19/2010 - 17:51

In anticipation of my new iPhone 4 (and a larger hard drive), I decided to write a script to help choose which albums I should include. Through iTunes you can export your library as text, which can then be imported into MySQL, and SQL can be executed to show.. well, whatever your heart desires!

I started by exporting my entire music library, and chose Plain Text as the format. This iTunes functionality creates a tab separated list of columns and data.

I then created a new MySQL database and table (SEE: TABLE_NAME) to contain my iTunes song data. I also added an additional column as a primary key, called sql_id.

Next, I created a PHP script to parse the text file, dynamically add the columns to my table, and import all my song data. This code uses PEAR's DB library for the database access layer.

<?php
// define table name
define('TABLE_NAME', 'itunes');

// create database connection
require_once('DB.php');
$dsn = 'mysqli://db_user:db_password@db_host/database_name';
$DB =& DB::connect($dsn);
if (DB::isError($DB)) {
  die($DB->getMessage());
}
$DB->setFetchMode(DB_FETCHMODE_ASSOC);

// load text file
$file = file_get_contents('Music.txt');

// explode on new line
$file = explode("\r", $file);

// get a list of existing columns
$sql = "show columns in " . TABLE_NAME;
$result = $DB->getAll($sql);
$existing_columns = array();
foreach ($result as $key => $value) {
  $existing_columns[] = $value['Field'];
}

// create a variable to contain sql column names
$sql_column_name = array();

// loop through each line in the file
foreach ($file as $key => $value) {

  // explode on tab to get column list
  $exploded = explode("\t", $value);

  // check for first row, which contains column headers
  if ($key == 0) {

    // loop through column list and ensure they exist
    foreach ($exploded as $new_table) {
    
      // create a new column name without spaces
      $new_table = str_replace(' ' , '_', $new_table);

      // check if the new column should be added    
      if (!in_array($new_table, $existing_columns)) {
           
        // define sql to add new column
        $sql = "alter table " . TABLE_NAME . " add column `$new_table` varchar(255) default null";
        $result = $DB->query($sql);
        
        // check for error
        if (!$result) {
          echo "<pre>" . print_r($result, true) . "</pre>";
          die;
        }
                
        // define sql to add index
        $sql = "alter table " . TABLE_NAME . " add index `index_$new_table` ($new_table)";
        $result = $DB->query($sql);
        
        // check for error
        if (!$result) {
          echo "<pre>" . print_r($result, true) . "</pre>";
          die;          
        }
      
      } // end if
      
      $sql_column_names[] = $new_table;
    
    } // end foreach
      
  } // end if ($key[0])
  else {
    
    // prepare values to insert
    $insert_values = array();
    foreach ($exploded as $k => $v) {
      $insert_values[$k] = $DB->quoteSmart($v);
    }
    
    // define SQL to insert data into table
    $sql = "insert into " . TABLE_NAME . " (" . implode(',', $sql_column_names) . ") values (" . implode(',', $insert_values) . ")";
    
    // execute sql
    $result = $DB->query($sql);
    
    // check for SQL error
    if (!$result) {
      echo "<pre>" . print_r($result, true) . "</pre>";
    }
  
  }

}
?>

Now, all my iTunes data is accessible by SQL, sweet. I decided to create a view showing my albums with an average ranking.

create view album_rankings as
select Artist, Album, Genre, avg(My_Rating) as album_rating, count(*) as countX
from itunes
where Album != 'misc'
group by Artist, Album
order by avg(My_Rating) desc, countX desc

And finally, I executed the following SQL to show my highest rated albums.

select *
from album_rankings
where countX > 3

Top Rated Albums

2 comments

Setting up a multi-site Drupal 6 installation with shared databases and single sign-on

Submitted by Eric on Mon, 09/07/2009 - 13:33

In this blog entry, I'll explain how I setup a multi-site Drupal 6 installation with shared databases and single sign-on. This will enable you to store your users (and other desired tables) in a separate database, and share them across multiple sites.

Before installing Drupal I created 3 databases, added 2 mysql users, and granted permissions. Both mysql users will have access to the shared database which will contain the shared tables:

$ mysql
# create new databases:
mysql> create database drupal_ms_1;
mysql> create database drupal_ms_2;
mysql> create database drupal_ms_shared;

# create new user "drupal_ms_1" and grant privileges to the databases:
mysql> grant all privileges on drupal_ms_1.* to 'drupal_ms_1'@'localhost' identified by 'drupal_ms_1';
mysql> grant all privileges on drupal_ms_shared.* to 'drupal_ms_1'@'localhost' identified by 'drupal_ms_1';

# create new user "drupal_ms_2" and grant privileges to the databases:
mysql> grant all privileges on drupal_ms_2.* to 'drupal_ms_2'@'localhost' identified by 'drupal_ms_2';
mysql> grant all privileges on drupal_ms_shared.* to 'drupal_ms_2'@'localhost' identified by 'drupal_ms_2';

For my setup I wanted to use a single Drupal filesystem, so I created a name-based Apache virtualhost to host both domain names (ms1.erl.dev & ms2.erl.dev):

NameVirtualHost *:80

<VirtualHost *:80>
ServerName ms1.erl.dev
ServerAlias ms2.erl.dev
DocumentRoot /var/www/vhosts/ms.erl.dev/httpdocs
ErrorLog logs/ms.erl.dev-error_log
CustomLog logs/ms.erl.dev-access_log common
</VirtualHost>

I downloaded and unpacked the Drupal installation file:

$ cd /var/www/vhosts
$ mkdir ms.erl.dev
$ cd ms.erl.dev
$ wget http://ftp.drupal.org/files/projects/drupal-6.13.tar.gz
$ tar -xzf drupal-6.13.tar.gz
$ mv drupal-6.13 httpdocs

I setup 2 new sites folders in the sites folder, and copied the default.settings.php file into my first site:

$ cd httpdocs/sites
$ mkdir ms1.erl.dev
$ mkdir ms2.erl.dev
$ cp default/default.settings.php ms1.erl.dev

At this point my databases and filesystem were ready to go so I installed Drupal for the first site (ms1.erl.dev). During installation, I entered "localhost" for the database host, drupal_ms_1 as the database, and drupal_ms_1 as the database user. I left the db_prefix setting blank (we'll change that later).

Now that Drupal was installed and the tables were created, it was time to modify the installation to enable single sign-on and shared databases.

You'll need to decide which tables to share across the sites. I decided on users, sessions, and authmap (but you could add more as desired, like roles, profile_*, etc). I used the following commands to transfer the desired tables from the drupal_ms_1 database into the shared database, remove the tables from drupal_ms_1, and then replicated drupal_ms_1 to drupal_ms_2:

$ mysqldump drupal_ms_1 users sessions authmap | mysql drupal_ms_shared
$ mysql drupal_ms_1
mysql> drop table authmap, sessions, users;
mysql> exit
$ mysqldump drupal_ms_1 | mysql drupal_ms_2

Next, I modified the settings.php for my first site (sites/ms1.erl.dev/settings.php) to enable the shared database and single sign-on configuration. The $db_prefix variable is used to specify a different database using the [DATABASE].[TABLE] syntax. You'll also need to add the $cookie_domain variable to ensure the cookie set by Drupal will work across both domain names.

<?php
// Replace:
$db_prefix = '';
// With:
$db_prefix = array(
  'default' => '',
  'users' => 'drupal_ms_shared.',
  'sessions' => 'drupal_ms_shared.',
  'authmap' => 'drupal_ms_shared.'
);
// Add:
$cookie_domain = '.erl.dev';
?>

I then copied the ms1.erl.dev settings.php file into my second sites folder. Edit the new ms2.erl.dev/settings.php $db_url variable to point to the drupal_ms_2 database (and update the user and password as well).

$ cp ms1.erl.dev/settings.php ms2.erl.dev

After you delete your domain cookies everything should be working! To test this out, log into your first site and then open a new window/tab and go to your second site. You should be logged in as the same user. If you log out from one site, you should also be logged out from the other site.

7 comments

How to dump and compress all mysql databases on a server into separate files using PHP and PEAR

Submitted by Eric on Mon, 06/22/2009 - 21:20

Here's a quick code snippet I just wrote to dump and compress (gzip) all mysql databases on a server into separate files using PHP and PEAR.

<?php
// include PEAR DB library
require_once('DB.php');

// define the DSN in an array
// NOTE: user must have access to all databases
$dsn = array(
  'phptype' => 'mysql',
  'username' => 'YOURUSER',
  'password' => 'YOURPASSWORD',
  'hostspec' => 'HOSTNAME', // localhost?
);

// instantiate a PEAR DB object
$DB =& DB::connect($dsn);

// check for an error
if (DB::isError($DB)) die($DB->getMessage());

// set the DB fetch mode to associative
$DB->setFetchMode(DB_FETCHMODE_ASSOC);

// define sql statement
$sql = "show databases";

// fetch sql result
$databases = $DB->getAll($sql);

// loop through results
foreach ($databases as $index => $result) {

  // define the mysqldump command
  $command = "mysqldump -u " . $dsn['username'] . " "
    . "-p" . $dsn['password'] . " "
    . "-h " . $dsn['hostspec'] . " "
    . $result['Database'] . " | gzip > "
    . $result['Database'] . ".sql.gz";

  // execute command
  `$command`;
    
}
?>

Add new comment

Migrating a Drupal site from development to production

Submitted by Eric on Thu, 03/12/2009 - 10:31

It's important to treat content management systems like any other application and segment development, test, and production. Before adding content and attaching files to nodes, it's very important to set the file system path correctly and establish a deployment procedure.

One way to make the transition from development to production a smooth process is to use the same directory for your files. If you are using one Drupal installation for each project sites/default/files will work fine. If you set your file system path specific to your development hostname (for example: site/dev.HOSTNAME.com/files), you'll be in trouble when you transition to a new hostname.

Here's an example process to show how you could transition a site from development to test (or any other environment).

1. Migrate your MySQL database to the new server. Each environment should use its own database.

# connect to mysql as a privileged user to create a new database and user
$ mysql -u USER -pPASSWORD -h NEWSERVERHOST
> create database NEWDATABASE;
> GRANT ALL PRIVILEGES ON NEWDATABASE.* TO 'NEWUSER'@'localhost' IDENTIFIED BY 'NEWPASSWORD';
> exit;

# backup database
$ mysqldump -u USER -pPASSWORD DATABASE > DATABASE.sql

# import database
$ mysql -u NEWUSER -pNEWPASSWORD -h NEWSERVERHOST NEWDATABASE < DATABASE.sql

2. Copy your Drupal installation to the new server. Hopefully, you're using subversion (or some other version control software) to deploy these files.

3. Create a new folder in your sites directory for the new hostname. Copy your existing settings.php into the new folder. Edit this file and update the MySQL connection string to match your new database, user, and password.

4. Create a new Apache virtual host for your new hostname and direct DNS accordingly.

Now, if DNS has been setup for your new hostname, the site should be up and running on the new server with a new hostname.

Additional thoughts: It's very important to avoid hard coding hostnames and absolute paths in your links, code, and themes. I promise, it will make your deployment process a nightmare.

2 comments