sudo mysqldump -h [host] -u [username] -p[password] [database] --tables [table1 table2 ...] --lock-tables=false | gzip > dump.sql.gz
SET foreign_key_checks = 0;
TRUNCATE TABLE users;
SET foreign_key_checks = 1;
sudo mysqlbinlog -v --start-position={START_POS} --base64-output=DECODE-ROWS {FULL_PATH_TO_BINLOG_RELAYLOG_FILE} > {FULL_PATH_TO_DECODED_FILE}
Далее можно открыть DECODED_FILE через vim:
Функция вырезает все что находит от between_start до between_end и возвращает в виде результат. пригодится для ненормализованных данных в виде строки
CREATE FUNCTION `find_between_text`(`raw_data` TEXT, `between_start` TEXT, `between_end` TEXT)
RETURNS text CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
RETURN SUBSTRING( raw_data,
LOCATE(between_start, raw_data) + CHAR_LENGTH(between_start),
LOCATE(between_end, raw_data, LOCATE(between_start, raw_data) + CHAR_LENGTH(between_start))-(LOCATE(between_start, raw_data) + CHAR_LENGTH(between_start))
);
END
Пример использования
select find_between_text(raw_data_str,'<tag>','</tag>') as found_text from table_test
Необходимо указать критерии для поиска проблемных запросов:
SET @runTime = 2;
SET @selectPattern = "%";
SET @cUser = "user";
SET @cState = "Sleep";
-- Процедура для создания и выполнения kill-запросов
DELIMITER $$
DROP PROCEDURE IF EXISTS totalKILL $$
CREATE PROCEDURE totalKILL(runTime INT, selectPattern VARCHAR(255), cUser VARCHAR(255), cState VARCHAR(255))
BEGIN
-- Стартовые директивы
DECLARE finished INTEGER DEFAULT 0;
DECLARE oneKillQuery VARCHAR(255) DEFAULT '';
DECLARE curs CURSOR FOR SELECT kill_query FROM mysql_kill_list;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
-- Ищем необходимые PID's, формируем KILL-запросы
DROP TABLE IF EXISTS mysql_kill_list;
SET @stmt = CONCAT("CREATE TEMPORARY TABLE mysql_kill_list AS
SELECT CONCAT('KILL ', id, ';') 'kill_query'
FROM information_schema.processlist
WHERE `info` LIKE ('", selectPattern, "')
AND `time` >= ", runTime, "
AND user LIKE ('", cUser, "')
AND state LIKE ('", cState, "')");
PREPARE Q FROM @stmt;
EXECUTE Q;
DEALLOCATE PREPARE Q;
-- Выполняем каждый kill-запрос по отдельности
OPEN curs;
killQueries: LOOP
FETCH curs
INTO oneKillQuery;
IF finished = 1
THEN
LEAVE killQueries;
END IF;
SET @killQuery = oneKillQuery;
PREPARE Q FROM @killQuery;
EXECUTE Q;
DEALLOCATE PREPARE Q;
END LOOP killQueries;
CLOSE curs;
-- удаляем временную таблицу
DROP TABLE mysql_kill_list;
END $$
DELIMITER ;
-- запускаем процедуру с необходимыми параметрами
CALL totalKILL(@runTime, @selectPattern, @cUser, @cState);
-- удаляем процедуру
DROP PROCEDURE totalKILL;
Иногда хочется обновить все модели БД одной коммандой. Для этого написал небольшую комманду для этого и запускаю ее каждый раз после миграций
./yii db
обновит все базовые модели и если нужно создаст потомков в @app/models/
class DbController extends Controller
{
protected $tablePrefix;
protected $tables;
public function init()
{
$this->tablePrefix = \Yii::$app->db->tablePrefix;
$this->tables = \Yii::$app->db->createCommand('show tables')->queryColumn();
parent::init();
}
public function actionIndex()
{
$prefix = $this->tablePrefix;
foreach ($this->tables as &$table) {
$name = str_replace($prefix, '', $table);
$modelName = Inflector::camelize($name);
$baseModelName = 'Base' . $modelName;
$command = './yii gii/model --useTablePrefix=1 --tableName=' . $table . ' --modelClass=' . $baseModelName . ' --interactive=0 --enableI18N=1 --overwrite=1';
@exec($command);
$modelFile = \Yii::getAlias('@app/models/' . $modelName . '.php');
if (!file_exists($modelFile)) {
$this->createFile($modelFile, $modelName, $baseModelName);
}
}
}
private function createFile($modelFile, $modelName, $baseModelName)
{
$date = \Yii::$app->formatter->asDatetime(time(), 'MM/dd/yyyy hh:mm:ss');
$template = <<<TEMPLATE
<?php
/**
* Created by DBCommand.
* User: Any User
* Date: {$date}
*
*/
namespace app\models;
class {$modelName} extends {$baseModelName}
{
}
TEMPLATE;
$fp = fopen($modelFile, 'w+');
fputs($fp, $template);
fclose($fp);
}
}
Находит дублирующие записи в таблице
SELECT *
FROM `users`
WHERE CONCAT( `users`.`name` , `users`.`surname` )
IN (
SELECT CONCAT( `users`.`name` , `users`.`surname` ) AS x
FROM `users`
GROUP BY x
HAVING COUNT(x) >1
)
LIMIT 0 , 30
Полезно, когда долго не можешь понять, почему же вдруг не срабатывают внешние ограничения. Да и просто полезно.
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='my_db_name'
AND TABLE_TYPE='BASE TABLE'
AND ENGINE IN ('MARIA','MyISAM')
SQL запрос осуществляет сортировку по возрастанию и при этом нули будут в конце.
SELECT * FROM `table` ORDER BY CASE WHEN `field_name`=0 THEN 1 ELSE 0 END, `field_name`;
Удобный запрос для вывода поля с идентичными значениями в колонке needed_column
и кол-во повторов
SELECT `needed_column`, COUNT( * ) FROM `your_table` GROUP BY `needed_column` HAVING COUNT( * ) >1;