【无标题】

mysqldump -u root -p --default-character-set=utf8 wisdombmsdb --ignore-table=wisdombmsdb.eqp_record_history --ignore-table=wisdombmsdb.sys_oper_log > C:\db.sql

DELIMITER $$

CREATE PROCEDURE ReplaceIPValue(IN oldIP VARCHAR(64), IN newIP VARCHAR(64), IN dbName VARCHAR(64))
BEGIN
DECLARE tableName VARCHAR(64);
DECLARE columnName VARCHAR(64);
DECLARE done INT DEFAULT FALSE;
DECLARE columnsCursor CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = dbName AND
(DATA_TYPE = ‘char’ OR DATA_TYPE = ‘varchar’ OR DATA_TYPE = ‘text’ OR DATA_TYPE = ‘mediumtext’ OR DATA_TYPE = ‘longtext’);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN columnsCursor;

CREATE TEMPORARY TABLE IF NOT EXISTS IpFieldsToUpdate (
TableName VARCHAR(64),
ColumnName VARCHAR(64)
);

REPEAT

FETCH columnsCursor INTO tableName, columnName;
IF NOT done THEN
SET @sql = CONCAT('SELECT COUNT(*) INTO @cnt FROM ', dbName, '.', tableName, ' WHERE ‘, columnName, ’ LIKE "%’, oldIP, ‘%" LIMIT 1’);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

– 如果字段中有匹配的记录，则记录表名和字段名
IF @cnt > 0 THEN
SET @insertSql = CONCAT(‘INSERT INTO IpFieldsToUpdate (TableName, ColumnName) VALUES ("’, tableName, ‘", "’, columnName, ‘")’);
PREPARE insertStmt FROM @insertSql;
EXECUTE insertStmt;
DEALLOCATE PREPARE insertStmt;

– 更新记录中的IP地址
SET @updateSql = CONCAT('UPDATE ', dbName, '.', tableName, ' SET ‘, columnName, ’ = REPLACE(’, columnName, ‘, "’, oldIP, ‘", "’, newIP, ‘“) WHERE ‘, columnName, ’ LIKE "%’, oldIP, '%”’);
PREPARE updateStmt FROM @updateSql;
EXECUTE updateStmt;
DEALLOCATE PREPARE updateStmt;
END IF;
END IF;
UNTIL done END REPEAT;

-- 清理临时表和关闭游标
DROP TEMPORARY TABLE IF EXISTS IpFieldsToUpdate;

CLOSE columnsCursor;
END$$

DELIMITER ;

– 调用存储过程，替换旧 IP 和新 IP 以及数据库名
CALL ReplaceIPValue(‘192.168.59.130’, ‘192.168.11.26’, ‘wisdombmsdb’);

crontab -e
0 */2 * * * docker restart wisdombms-modules-driver