MySQL数据库迁移


#!/bin/sh

#表结构(排除触发器、数据)
mysqldump -d --skip-triggers yourDBname -h 10.2.20.2 -u root -pyourPassword > yourDBname-1.sql

#数据(排除触发器、表结构)
mysqldump -nt --skip-triggers yourDBname -h 10.2.20.2 -u root -pyourPassword > yourDBname-2.sql

#存储过程、函数(排除触发器、数据、表结构)
mysqldump -Rdnt --skip-triggers yourDBname -h 10.2.20.2 -u root -pyourPassword > yourDBname-3.sql

#事件(排除触发器、数据、表结构)
mysqldump -Ednt --skip-triggers yourDBname -h 10.2.20.2 -u root -pyourPassword > yourDBname-4.sql

#触发器(排除数据、表结构)
mysqldump -dnt yourDBname -h 10.2.20.2 -u root -pyourPassword > yourDBname-5.sql

#删除 DEFINER
sed -i 's/DEFINER=`\w*`@`[^`]*` //g' yourDBname-3.sql
sed -i 's/DEFINER=`\w*`@`[^`]*` //g' yourDBname-4.sql
sed -i 's/DEFINER=`\w*`@`[^`]*` //g' yourDBname-5.sql