Admittedly, I have been one of the late comers to the Drush revolution. I've heard since it was released how amazing it was, and it's taken me forever to get around to playing with it on my localhost environment to see how it can best be utilized for my own workflow during development. I've been greatly satisfied on how easy it is to update modules, download, install & uninstall modules, and have been trying to dig into the MySQL usage as well, however ran into some issues with my current setup.
The Problem
I'm using XAMPP for linux, on my Ubuntu machine to develop locally, which alone has sped up my development considerably, and makes my life much easier. However, when it comes to MySQL, it is an issue as the setup that is created by XAMPP is not a traditional setup of Apache, MySQL, PHP that you would get by setting it up yourself in a more traditional way. Everything is operated out of the directory at /opt/lampp/ and some traditional commands that are available on my system from the command line like mysql, and mysqldump do not understand that other installation I have in the XAMPP setup.
So when I was running commands using Drush like: $drush sql dump I was getting an error like the following:
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect
Okay, that's a pain in my ass. So I decided with my VERY minimal knowledge of Drush and the underlying code back there that it must be possible for me to set up something that could make that work properly.
The following script in it's part is an addon that takes from the default drush/commands/sql/sql.drush.inc file I reused a lot of code from there obviously, and changed things around to provide the following features for my own purposes:
The Goal
- Allow for mysqldump command to live at /opt/lampp/bin/mysqldump
- Gzip the .sql file automatically at the end of the command
- Avoid having to use --result-file=path/to/where-i-want-my-dump-to-go by automatically providing a file name based on database name & date/time
Oh, and to call this functionality, you can use: drush sql xdump or drush help sql xdump. I have placed these items in a custom file of my own called himerus.drush.inc, which is automatically found by Drush. You may name it whatever you like as long as it ends in .drush.inc, it will be picked up.
The Code
* Implementation of hook_drush_help().
*/
function himerus_drush_help($section) {
switch ($section) {
case 'drush:sql xdump':
return dt('Exports the database using /opt/lampp/bin/mysqldump, then gzips the file.');
}
}
/**
* Implementation of hook_drush_command().
*/
function himerus_drush_command() {
$options['--database'] = 'The DB connection key if using multiple connections in settings.php.';
if (drush_drupal_major_version() >= 7) {
$options['--target'] = 'The name of a target within the specified database.';
}
$items['sql xdump'] = array(
'callback' => 'drush_sql_xdump_execute',
'description' => 'Exports the Drupal DB as SQL using mysqldump.',
'bootstrap' => DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION,
'examples' => array(
'drush sql dump --result-file=../18.sql' => 'Save SQL dump to the directory above Drupal root.',
'drush sql dump --skip-tables-key=common' => 'Skip standard tables. @see example.drushrc.com',
),
'options' => array(
'--result-file' => 'Save to a file. The file should be relative to Drupal root. If you do not define this value, it will be automatically generated.',
'--skip-tables-key' => 'A key in the $skip_tables array. @see example.drushrc.php. Optional.',
'--structure-tables-key' => 'A key in the $structure_tables array. @see example.drushrc.php. Optional.',
) + $options,
);
return $items;
}
/**
* Command callback. Outputs the entire Drupal database in SQL format using mysqldump.
*/
function drush_sql_xdump_execute() {
$exec = drush_sql_xdump();
// Avoid the php memory of the $output array in drush_shell_exec().
$return = drush_op('system', $exec);
return $return;
}
/**
* Build a mysqldump statement.
*
* @param db_spec
* For D5/D6, a $db_url. For D7, a target in the default DB connection.
* @return string
* A mysqldump statement that is ready for executing.
*/
function drush_sql_xdump($db_spec = NULL) {
$skip_tables = $structure_tables = $ignores = array();
// Skip large core tables if instructed. Also used by 'sql load' command.
$key = drush_get_option('skip-tables-key');
$all_skip_tables = drush_get_option('skip-tables', array());
if (array_key_exists($key, $all_skip_tables)) {
$skip_tables = $all_skip_tables[$key];
}
// Skip any structure-tables as well.
$key = drush_get_option('structure-tables-key');
$all_structure_tables = drush_get_option('structure-tables', array());
if (array_key_exists($key, $all_structure_tables)) {
$structure_tables = $all_structure_tables[$key];
$skip_tables += $structure_tables;
}
switch (_drush_sql_get_scheme()) {
case 'mysql':
if (is_null($db_spec)) {
$db_spec = _drush_sql_get_db_spec();
}
$database = $db_spec['database'];
$exec = '/opt/lampp/bin/mysqldump' . (drush_get_context('DRUSH_VERBOSE') ? ' -v' : '');
if ($file = drush_get_option('result-file')) {
$exec .= ' --result-file '. $file;
}
else {
// if a result file wasn't manually defined, I want to just name using my own
// standard for backup dumps
$default_dir = 'backup/mysql/';
$date = date('mdY-Hi');
$file = $default_dir . $database .'_'. $date .'.sql';
$exec .= ' --result-file '. $file;
}
$extra = ' --single-transaction --opt -Q' . _drush_sql_get_credentials($db_spec);
$exec .= $extra;
// Append the ignore-table options.
foreach ($skip_tables as $table) {
$ignores[] = "--ignore-table=$database.$table";
}
$exec .= ' '. implode(' ', $ignores);
// Run mysqldump again and append output if we need some structure only tables.
if (!empty($structure_tables)) {
$exec .= "; /opt/lampp/bin/mysqldump --no-data $extra " . implode(' ', $structure_tables);
if ($file) {
$exec .= " >> $file";
}
}
// adding gzip support
$exec .= "; gzip -9 $file";
break;
}
return $exec;
}
That's it! You can now dump your DB using the default implementation of mysqldump that XAMPP uses, or you may just use parts of this to allow your sql dump to automatically gzip it up, and name it the way you want.
The main meat of the code above that is the most important is below. You can see where I've renamed the mysqldump command to reference the appropriate full path needed to properly execute on my system. Also, the else portion of the conditional is new. This is what is occuring if a --result-file flag is not added to the drush command. This will allow you to name it as you want if you DO provide that flag, or provide a default naming structure so that you don't have to enter it every time. I really wanted this in place for cron job backups. The default format here is database-name_MMDDYYY-HHMM.sql. Once this portion has completed, it will attach the gzip command at the very end before returning the command to the previous function. It also as you can see from here places it in a directory i have in every site which is backup/mysql. A best solution would check to make sure the directory is present first, and create it if not, but for now this doesn't cause me issue.
$exec = '/opt/lampp/bin/mysqldump' . (drush_get_context('DRUSH_VERBOSE') ? ' -v' : '');
if ($file = drush_get_option('result-file')) {
$exec .= ' --result-file '. $file;
}
else {
// if a result file wasn't manually defined, I want to just name using my own
// standard for backup dumps
$default_dir = 'backup/mysql/';
$date = date('mdY-Hi');
$file = $default_dir . $database .'_'. $date .'.sql';
$exec .= ' --result-file '. $file;
}
?>
Conclusion
Mind you, I admit this may not be the cleanest solution for command line junkies, but it was quite nice to dig into Drush a bit, and change something I wanted to in order to make it work as I needed.
I'd be happy to take some feedback on this from any of the Drush experts out there, keeping in mind that I'm "decent" on the command line, but not an expert by any means.
Recommended links
- Creating a "tools" addon for the Admin 2 module
- Provide an alternate image location for Service Links in your Drupal theme
- Fixing duplicate taxonomy terms in a node view using hook_views_pre_render()
- jQuery snippets I can't live without in Drupal
- Overriding Field Handlers in Views with your own custom handler
Video Content
Recent comments
- Thanks man.
2 weeks 8 hours ago - gucci handbags
2 weeks 6 days ago - ckedior is also a great html
4 weeks 8 hours ago - Ace theme
4 weeks 2 days ago - What about php Tags?
6 weeks 14 hours ago - After trying unsuccessfully
7 weeks 2 days ago - Coool it is
7 weeks 6 days ago - Drupal's flexibility
8 weeks 1 day ago - Thank you for the advice. I
8 weeks 6 days ago - omega
9 weeks 3 days ago
I used to use XAMPP but
I used to use XAMPP but switched to the exact same setup I use on my server for consistency. Is there any reason you still use XAMPP? Because a LAMP stack is super simple to install:
apt-get install lamp-server^ php5-gd php5-curl php5-cli
Maybe I'm missing something?
- Sean Bannister
good point on normal lamp stack
For me, the last time I setup a true lamp stack myself was years and years ago when it was still a challenging setup on any system. I really chose xampp just for the convinience of getting it set up super fast after I wiped my main dev machine and went to ubuntu. Sometimes I forget how easy it is to install some things on linux now.
Xampp has been great for this setup, and I've had no complaints other than the mysqldump and mysql commands being not linked where I thought they would be, and I solved that just using aliases, but drush didn't recognize that alias, so I needed this option to make it work in drush.
Will probably be updating to a MacBook Pro soon, and probably set up a more traditional LAMP stack on that as it'll be my dev machine for quite some time after retiring this beast.
Jake Strawn
Drupal Rockstar
You could also provide
You could also provide symlinks where the system is looking to find those commands, or include that path in your $PATH environment variable. either way would let you use those commands without making the modifications you have.
The db dump renaming is cool though, so I'd keep that :D
Post new comment