X
  • MySQL |
    February 9, 2015

BACKUP SERVER command for MySQL with Query Rewrite Pre-Parse Plugin

Few times I heard from MySQL users, customers and Sales consultants about their wish to have SQL command, allowing to backup MySQL server. I asked why calling mysqbackup or mysqldump does not work for them and these people had reasons. One of them is security: this way you don't need to give shell access to MySQL server for the person who is supposed to do backup.

MySQL does not have such a command, except for MySQL Cluster. There are instead multiple programs which can make backup if run externally, such as mysqlbackup of mysqldump.

That time I wrote a UDF which runs any external command (https://github.com/svetasmirnova/run_external). So customers could run something similar to:

mysql> SELECT run_external(concat('$HOME/build/mysql-trunk/bin/mysqldump  --socket=', @@socket, ' --all-databases > /Users/sveta/src/BACKUPDIR/plugin_test/backup_test.sql'))\G
*************************** 1. row ***************************
run_external(concat('$HOME/build/mysql-trunk/bin/mysqldump  --socket=', @@socket, ' --all-databases > /Users/sveta/src/BACKUPDIR/plugin_test/backup_test.sql')): 1
1 row in set (0.54 sec)


Or

mysql> SELECT run_external(concat('mysqlbackup --backup-dir="$HOME/src/BACKUPDIR/plugin_test/meb" --socket=', @@socket, ' backup'))\G
*************************** 1. row ***************************

run_external(concat('mysqlbackup --backup-dir="$HOME/src/BACKUPDIR/plugin_test/meb" --socket=', @@socket, ' backup')): 1
1 row in set (2.14 sec)


But this is not so nice looking like BACKUP SERVER or BACKUP DATABASE statements. Unfortunately few years ago I could do nothing with it: MySQL does not have pluggable parsers and does not allow to modify built-in parser unless you want to maintain one more MySQL fork forever. Of course, users could use MySQL Proxy, but this is yet again one external tool they are trying to get rid off.

In year 2014 things changed. MySQL Optimizer Team introduced  Query Rewrite Pre-Parse and Post-Parse Plugins. So I decided to make a try.

Since I am crafting completely new SQL command I use Pre-Parse plugin.

Writing Pre-parse plugins is pretty easy. Just take as example rewrite_example plugin and its only source file rewrite_example.cc: change mysql_declare_plugin, so it is not rewrite_example, but your plugin, and re-define rewrite_lower function:

mysql_declare_plugin(mysqlbackup_plugin)
{
  MYSQL_REWRITE_PRE_PARSE_PLUGIN,
  &mysqlbackup_plugin_descriptor,
  "mysqlbackup_plugin",
  "Sveta Smirnova",
  "Plugin which provides SQL interface for MySQL Enterprise Backup and mysqldump",
  PLUGIN_LICENSE_GPL,
  mysqlbackup_plugin_init,
  NULL,
  0x0001,                                       /* version 0.0.1      */
  NULL,                                         /* status variables   */
  mysqlbackup_plugin_vars,                      /* system variables   */
  NULL,                                         /* config options     */
  0,                                            /* flqgs              */
}
mysql_declare_plugin_end;


In my case I called function perform_backup (because I don't rewrite query to lowercase, but perform backup), so I need to change mysqlbackup_plugin_descriptor as well:

static st_mysql_rewrite_pre_parse mysqlbackup_plugin_descriptor= {
  MYSQL_REWRITE_PRE_PARSE_INTERFACE_VERSION,    /* interface version          */
  perform_backup,                               /* performs backup            */
  free_resources,                               /* frees allocated resources  */
};

static int perform_backup(Mysql_rewrite_pre_parse_param *param)
{
  if (0 == strcasecmp("backup server", param->query))
  {
    if (!backup_dir_value)
    {
      const char* newq= "SELECT 'You must set global variable mysqlbackup_plugin_backup_dir before running this command!'";
      param->rewritten_query= new char[strlen(newq) + 1];
      param->rewritten_query_length=strlen(newq);
      strncpy(param->rewritten_query, newq, param->rewritten_query_length + 1);
    }
    else
    {
      ostringstream oss;
      switch(backup_tool_name) {
      case MYSQLBACKUP:
        oss << "SELECT '" << supported_tools[backup_tool_name] << " not supported yet.'";
        param->rewritten_query_length= strlen(oss.str().c_str());
        param->rewritten_query= new char[param->rewritten_query_length + 1];
        strncpy(param->rewritten_query, oss.str().c_str(), param->rewritten_query_length + 1);
        break;
      case MYSQLDUMP:
        cerr << "Processing mysqldump" << endl;
        if (backup_tool_basedir_value && 0 < strlen(backup_tool_basedir_value))
          oss << "SELECT run_external(concat('" << backup_tool_basedir_value << "/" << supported_tools[backup_tool_name] << " " << backup_tool_options_value << " --socket=', @@socket, ' --all-databases > " << backup_dir_value << "/backup_', date_format(now(), '%Y-%m-%e_%H:%i:%s'), '.sql'))";
        else
          oss << "SELECT run_external(concat('" << supported_tools[backup_tool_name] << " " << backup_tool_options_value << " --socket=', @@socket, ' --all-databases > " << backup_dir_value << "/backup_', date_format(now(), '%Y-%m-%e_%H:%i:%s'), '.sql'))";
cerr << oss.str() << endl;
        param->rewritten_query_length= strlen(oss.str().c_str());
        param->rewritten_query= new char[param->rewritten_query_length + 1];
        strncpy(param->rewritten_query, oss.str().c_str(), param->rewritten_query_length + 1);
        break;
        }
      }
      param->flags|= FLAG_REWRITE_PLUGIN_QUERY_REWRITTEN;
    }
  return 0;
}


Disclaimer: Although this tool claims to support both mysqldump and mysqlbackup commands, currently only mysqldump is supported, because mysqlbackup cannot backup 5.7 server yet. And query rewrite plugin exists in version 5.7 only.

I also added 4 variables, allowing to specify mysqlbackup_plugin_backup_tool (currently only mysqldump is supported), mysqlbackup_plugin_backup_dir: required variable, pointing to the place where backup should be stored; mysqlbackup_plugin_backup_tool_basedir - path to directory where backup tool located and mysqlbackup_plugin_backup_tool_options which is used to specify non-default options:

static MYSQL_SYSVAR_STR(backup_dir, backup_dir_value, PLUGIN_VAR_MEMALLOC, "Default directory where to store backup", NULL, NULL, NULL);
static MYSQL_SYSVAR_ENUM(backup_tool, backup_tool_name, PLUGIN_VAR_RQCMDARG, "Backup tool. Possible values: mysqldump|mysqlbackup", NULL, NULL, MYSQLDUMP, &supported_tools_typelib);
static MYSQL_SYSVAR_STR(backup_tool_basedir, backup_tool_basedir_value, PLUGIN_VAR_MEMALLOC, "Base dir for backup tool. Default: \"\"", NULL, NULL, "");
static MYSQL_SYSVAR_STR(backup_tool_options, backup_tool_options_value, PLUGIN_VAR_MEMALLOC, "Options for backup tool", NULL, NULL, "");

static struct st_mysql_sys_var *mysqlbackup_plugin_vars[] = {
    MYSQL_SYSVAR(backup_dir),
    MYSQL_SYSVAR(backup_tool),
    MYSQL_SYSVAR(backup_tool_basedir),
    MYSQL_SYSVAR(backup_tool_options),
    NULL
};


And now we can compile and run the tool: change directory to the top directory of MySQL source, run cmake, then make.

You still need to install plugin: INSTALL PLUGIN mysqlbackup_plugin SONAME 'mysqlbackup_plugin.so';

Then you only need to set backup_dir location, provide path to backup tool if it  is not in your PATH, add user, password and other options you want mysqldump to use either under [client]/[mysqldump] section of your configuration file or as mysqlbackup_plugin_backup_tool_options value, then run BACKUP SERVER command:

mysql> set global mysqlbackup_plugin_backup_dir='$HOME/src/BACKUPDIR/plugin_test';
Query OK, 0 rows affected (0.00 sec)

mysql> backup server;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| run_external(concat('mysqldump  --socket=', @@socket, ' --all-databases > $HOME/src/BACKUPDIR/plugin_test/backup_', date_format(now(), '%Y-%m-%e_%H:%i:%s'), '.sql')) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                     1 |     
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.60 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                          |       
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'backup server' rewritten to 'SELECT run_external(concat('mysqldump  --socket=', @@socket, ' --all-databases > $HOME/src/BACKUPDIR/plugin_test/backup_', date_format(now(), '%Y-%m-%e_%H:%i:%s'), '.sql'))' by plugin: mysqlbackup_plugin. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


Voi-la! You just created backup file in the directory specified without accessing any command line shell!

All code is available at GitHub.

UDF: https://github.com/svetasmirnova/run_external
Plugin: https://github.com/svetasmirnova/mysqlbackup_plugin

One more disclaimer: This code is not for production use, but for illustrating idea of
how to use Query Rewrite plugins for crafting your own SQL. It has serious security issues! For example, although only user with SUPER privilege can modify global variables there is still potential security risk if an attacker has access to these variables and can modify in way which the program does not expect. There is no checks for, say, if value of mysqlbackup_plugin_backup_dir or mysqlbackup_tool_basedir_value are really directories and not paths to a tool which does not perform backup. Also value of option mysqlbackup_plugin_backup_tool_options can be crafted in risky way too. Be really careful if you decide to use it not for educational purpose only.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services