def _reset_root_password(self):
     #Create temp file with reset root password
     with tempfile.NamedTemporaryFile() as fp:
         fp.write(RESET_ROOT_MYSQL_COMMAND)
         fp.flush()
         utils.execute_with_timeout("sudo", "chmod", "a+r", fp.name)
         self._spawn_with_init_file(fp)

 def _get_actual_db_status(self):
     global MYSQLD_ARGS
     try:
         out, err = utils.execute_with_timeout(
             "/usr/bin/mysqladmin",
             "ping", run_as_root=True)
         LOG.info("Service Status is RUNNING.")
         return rd_models.ServiceStatuses.RUNNING
     except ProcessExecutionError as e:
         LOG.error("Process execution ")
         try:
             out, err = utils.execute_with_timeout("/bin/ps", "-C",
                                                   "mysqld", "h")
             pid = out.split()[0]
             # TODO(rnirmal): Need to create new statuses for instances
             # where the mysql service is up, but unresponsive
             LOG.info("Service Status is BLOCKED.")
             return rd_models.ServiceStatuses.BLOCKED
         except ProcessExecutionError as e:
             if not MYSQLD_ARGS:
                 MYSQLD_ARGS = load_mysqld_options()
             pid_file = MYSQLD_ARGS.get('pid_file',
                                        '/var/run/mysqld/mysqld.pid')
             if os.path.exists(pid_file):
                 LOG.info("Service Status is CRASHED.")
                 return rd_models.ServiceStatuses.CRASHED
             else:
                 LOG.info("Service Status is SHUTDOWN.")
                 return rd_models.ServiceStatuses.SHUTDOWN

    def execute_restore(self, context, backup_id, restore_location):

        try:
            LOG.debug("Cleaning out restore location: %s", restore_location)
            utils.execute_with_timeout("sudo", "chmod", "-R", "0777", restore_location)
            utils.clean_out(restore_location)

            LOG.debug("Finding backup %s to restore", backup_id)
            backup = DBBackup.find_by(id=backup_id)

            LOG.debug("Getting Restore Runner of type %s", backup.backup_type)
            restore_runner = self._get_restore_runner(backup.backup_type)

            LOG.debug("Getting Storage Strategy")
            storage_strategy = get_storage_strategy(CONF.storage_strategy, CONF.storage_namespace)(context)

            LOG.debug("Preparing storage to download stream.")
            download_stream = storage_strategy.load(context, backup.location, restore_runner.is_zipped)

            with restore_runner(restore_stream=download_stream, restore_location=restore_location) as runner:
                LOG.debug("Restoring instance from backup %s to %s", backup_id, restore_location)
                content_size = runner.restore()
                LOG.info("Restore from backup %s completed successfully to %s", backup_id, restore_location)
                LOG.info("Restore size: %s", content_size)

                utils.execute_with_timeout("sudo", "chown", "-R", "mysql", restore_location)

        except Exception as e:
            LOG.error(e)
            LOG.error("Error restoring backup %s", backup_id)
            raise

        else:
            LOG.info("Restored Backup %s", backup_id)

    def _spawn_with_init_file(self, temp_file):
        child = pexpect.spawn("sudo mysqld_safe --init-file=%s" %
                              temp_file.name)
        try:
            i = child.expect(['Starting mysqld daemon'])
            if i == 0:
                LOG.info("Starting mysqld daemon")
        except pexpect.TIMEOUT as e:
            LOG.error("wait_and_close_proc failed: %s" % e)
        finally:
            try:
                # There is a race condition here where we kill mysqld before
                # the init file been executed. We need to ensure mysqld is up.
                utils.poll_until(mysql_is_running,
                                 sleep_time=RESET_ROOT_SLEEP_INTERVAL,
                                 time_out=RESET_ROOT_RETRY_TIMEOUT)
            except exception.PollTimeOut:
                raise RestoreError("Reset root password failed: "
                                   "mysqld did not start!")

            LOG.info("Root password reset successfully!")
            LOG.info("Cleaning up the temp mysqld process...")
            child.delayafterclose = 1
            child.delayafterterminate = 1
            child.close(force=True)
            utils.execute_with_timeout("sudo", "killall", "mysqld")

 def _post_restore(self):
     utils.execute_with_timeout("sudo", "chown", "-R", "-f",
                                "mysql", self.restore_location)
     self._delete_old_binlogs()
     self._reset_root_password()
     app = dbaas.MySqlApp(dbaas.MySqlAppStatus.get())
     app.start_mysql()

    def start_mysql(self, update_db=False):
        LOG.info(_("Starting mysql..."))
        # This is the site of all the trouble in the restart tests.
        # Essentially what happens is that mysql start fails, but does not
        # die. It is then impossible to kill the original, so

        self._enable_mysql_on_boot()

        try:
            utils.execute_with_timeout("sudo", "/etc/init.d/mysql", "start")
        except exception.ProcessExecutionError:
            # it seems mysql (percona, at least) might come back with [Fail]
            # but actually come up ok. we're looking into the timing issue on
            # parallel, but for now, we'd like to give it one more chance to
            # come up. so regardless of the execute_with_timeout() respose,
            # we'll assume mysql comes up and check it's status for a while.
            pass
        if not self.status.wait_for_real_status_to_change_to(
                rd_models.ServiceStatuses.RUNNING,
                self.state_change_wait_time, update_db):
            LOG.error(_("Start up of MySQL failed!"))
            # If it won't start, but won't die either, kill it by hand so we
            # don't let a rouge process wander around.
            try:
                utils.execute_with_timeout("sudo", "pkill", "-9", "mysql")
            except exception.ProcessExecutionError, p:
                LOG.error("Error killing stalled mysql start command.")
                LOG.error(p)
            # There's nothing more we can do...
            self.status.end_install_or_restart()
            raise RuntimeError("Could not start MySQL!")

 def _replace_mycnf_with_template(self, template_path, original_path):
     if os.path.isfile(template_path):
         utils.execute_with_timeout("sudo", "mv", original_path,
             "%(name)s.%(date)s" % {'name': original_path,
                                    'date': date.today().isoformat()})
         utils.execute_with_timeout("sudo", "cp", template_path,
                                    original_path)

 def stop_mysql(self, update_db=False):
     LOG.info(_("Stopping mysql..."))
     utils.execute_with_timeout("sudo", "/etc/init.d/mysql", "stop")
     if not self.status.wait_for_real_status_to_change_to(
             rd_models.ServiceStatuses.SHUTDOWN,
             self.state_change_wait_time, update_db):
         LOG.error(_("Could not stop MySQL!"))
         self.status.end_install_or_restart()
         raise RuntimeError("Could not stop MySQL!")

 def _replace_mycnf_with_template(self, template_path, original_path):
     LOG.debug("replacing the mycnf with template")
     LOG.debug("template_path(%s) original_path(%s)"
               % (template_path, original_path))
     if os.path.isfile(template_path):
         utils.execute_with_timeout(
             "sudo", "mv", original_path,
             "%(name)s.%(date)s" % {'name': original_path,
                                    'date': date.today().isoformat()})
         utils.execute_with_timeout("sudo", "cp", template_path,
                                    original_path)

 def _write_temp_mycnf_with_admin_account(self, original_file_path,
                                          temp_file_path, password):
     utils.execute_with_timeout("sudo", "chmod", "0711", MYSQL_BASE_DIR)
     mycnf_file = open(original_file_path, 'r')
     tmp_file = open(temp_file_path, 'w')
     for line in mycnf_file:
         tmp_file.write(line)
         if "[client]" in line:
             tmp_file.write("user\t\t= %s\n" % ADMIN_USER_NAME)
             tmp_file.write("password\t= %s\n" % password)
     mycnf_file.close()
     tmp_file.close()

 def _enable_mysql_on_boot(self):
     '''
     # This works in Debian Squeeze, but Ubuntu Precise has other plans.
     # Use update-rc.d to enable or disable mysql at boot.
     # update-rc.d is idempotent; any substitute method should be, too.
     flag = "enable" if enabled else "disable"
     LOG.info("Setting mysql to '%s' in rc.d" % flag)
     utils.execute_with_timeout("sudo", "update-rc.d", "mysql", flag)
     '''
     LOG.info("Enabling mysql on boot.")
     conf = "/etc/init/mysql.conf"
     command = "sudo sed -i '/^manual$/d' %(conf)s"
     command = command % locals()
     utils.execute_with_timeout(command, with_shell=True)

 def _spawn_with_init_file(self, temp_file):
     child = pexpect.spawn("sudo mysqld_safe --init-file=%s" %
                           temp_file.name)
     try:
         i = child.expect(['Starting mysqld daemon'])
         if i == 0:
             LOG.info("Root password reset successfully!")
     except pexpect.TIMEOUT as e:
         LOG.error("wait_and_close_proc failed: %s" % e)
     finally:
         LOG.info("Cleaning up the temp mysqld process...")
         child.delayafterclose = 1
         child.delayafterterminate = 1
         child.close(force=True)
         utils.execute_with_timeout("sudo", "killall", "mysqld")

def get_auth_password():
    pwd, err = utils.execute_with_timeout("sudo", "awk",
        "/password\\t=/{print $3}", "/etc/mysql/my.cnf")
    if err:
        LOG.err(err)
        raise RuntimeError("Problem reading my.cnf! : %s" % err)
    return pwd.strip()

 def _disable_mysql_on_boot(self):
     """
     There is a difference between the init.d mechanism and the upstart
     The stock mysql uses the upstart mechanism, therefore, there is a
     mysql.conf file responsible for the job. to toggle enable/disable
     on boot one needs to modify this file. Percona uses the init.d
     mechanism and there is no mysql.conf file. Instead, the update-rc.d
     command needs to be used to modify the /etc/rc#.d/[S/K]##mysql links
     """
     LOG.info("Disabling mysql on boot.")
     conf = "/etc/init/mysql.conf"
     if os.path.isfile(conf):
         command = '''sudo sh -c "echo manual >> %(conf)s"'''
         command = command % locals()
     else:
         command = "sudo update-rc.d mysql disable"
     utils.execute_with_timeout(command, shell=True)

    def start_mysql(self, update_db=False):
        LOG.info(_("Starting mysql..."))
        # This is the site of all the trouble in the restart tests.
        # Essentially what happens is thaty mysql start fails, but does not
        # die. It is then impossible to kill the original, so

        try:
            utils.execute_with_timeout("sudo", "/etc/init.d/mysql", "start")
        except ProcessExecutionError:
            # If it won't start, but won't die either, kill it by hand so we
            # don't let a rouge process wander around.
            try:
                utils.execute_with_timeout("sudo", "pkill", "-9", "mysql")
            except ProcessExecutionError, p:
                LOG.error("Error killing stalled mysql start command.")
                LOG.error(p)
            # There's nothing more we can do...
            raise RuntimeError("Can't start MySQL!")

def mysql_is_running():
    try:
        out, err = utils.execute_with_timeout(
            "/usr/bin/mysqladmin",
            "ping", run_as_root=True, root_helper="sudo")
        LOG.info("The mysqld daemon is up and running.")
        return True
    except exception.ProcessExecutionError:
        LOG.info("Waiting for mysqld daemon to start")
        return False

    def wipe_ib_logfiles(self):
        """Destroys the iblogfiles.

        If for some reason the selected log size in the conf changes from the
        current size of the files MySQL will fail to start, so we delete the
        files to be safe.
        """
        LOG.info(_("Wiping ib_logfiles..."))
        for index in range(2):
            try:
                utils.execute_with_timeout("sudo", "rm", "%s/ib_logfile%d"
                                           % (MYSQL_BASE_DIR, index))
            except ProcessExecutionError as pe:
                # On restarts, sometimes these are wiped. So it can be a race
                # to have MySQL start up before it's restarted and these have
                # to be deleted. That's why its ok if they aren't found.
                LOG.error("Could not delete logfile!")
                LOG.error(pe)
                if "No such file or directory" not in str(pe):
                    raise

    def _write_mycnf(self, update_memory_mb, admin_password):
        """
        Install the set of mysql my.cnf templates from dbaas-mycnf package.
        The package generates a template suited for the current
        container flavor. Update the os_admin user and password
        to the my.cnf file for direct login from localhost
        """
        LOG.info(_("Writing my.cnf templates."))
        if admin_password is None:
            admin_password = get_auth_password()

        # As of right here, the admin_password contains the password to be
        # applied to the my.cnf file, whether it was there before (and we
        # passed it in) or we generated a new one just now (because we didn't
        # find it).

        LOG.debug(_("Installing my.cnf templates"))
        pkg.pkg_install("dbaas-mycnf", self.TIME_OUT)

        LOG.info(_("Replacing my.cnf with template."))
        template_path = DBAAS_MYCNF % update_memory_mb
        # replace my.cnf with template.
        self._replace_mycnf_with_template(template_path, ORIG_MYCNF)

        LOG.info(_("Writing new temp my.cnf."))
        self._write_temp_mycnf_with_admin_account(ORIG_MYCNF, TMP_MYCNF,
                                                  admin_password)
        # permissions work-around
        LOG.info(_("Moving tmp into final."))
        utils.execute_with_timeout("sudo", "mv", TMP_MYCNF, FINAL_MYCNF)
        LOG.info(_("Removing original my.cnf."))
        utils.execute_with_timeout("sudo", "rm", ORIG_MYCNF)
        LOG.info(_("Symlinking final my.cnf."))
        utils.execute_with_timeout("sudo", "ln", "-s", FINAL_MYCNF, ORIG_MYCNF)
        self.wipe_ib_logfiles()

 def get_filesystem_volume_stats(self, fs_path):
     out, err = utils.execute_with_timeout(
         "stat",
         "-f",
         "-t",
         fs_path)
     if err:
         LOG.error(err)
         raise RuntimeError("Filesystem not found (%s) : %s"
                            % (fs_path, err))
     stats = out.split()
     output = {'block_size': int(stats[4]),
               'total_blocks': int(stats[6]),
               'free_blocks': int(stats[7]),
               'total': int(stats[6]) * int(stats[4]),
               'free': int(stats[7]) * int(stats[4])}
     output['used'] = int(output['total']) - int(output['free'])
     return output

def get_engine():
        """Create the default engine with the updated admin user"""
        #TODO(rnirmal):Based on permissions issues being resolved we may revert
        #url = URL(drivername='mysql', host='localhost',
        #          query={'read_default_file': '/etc/mysql/my.cnf'})
        global ENGINE
        if ENGINE:
            return ENGINE
        #ENGINE = create_engine(name_or_url=url)
        pwd, err = utils.execute_with_timeout("sudo", "awk",
            "/password\\t=/{print $3}", "/etc/mysql/my.cnf")
        if not err:
            ENGINE = create_engine("mysql://%s:%[email protected]:3306" %
                                   (ADMIN_USER_NAME, pwd.strip()),
                                   pool_recycle=7200, echo=True,
                                   listeners=[KeepAliveConnection()])
        else:
            LOG.error(err)
        return ENGINE