我们的开发服务器是我们的生产服务器的复制奴隶。 我们有一个脚本，开发人员可以使用它们的脚本，如果他们想运行他们的应用程序/错误修复对新的数据 该脚本如下所示：
dbs=( analytics auth logs users ) server=localhost conn="-h ${server} -u ${username} --password=${password}" # Stop the replication client so we don't encounter weird data. echo "STOP SLAVE" | mysql ${conn} # Bunch of bulk insert optimizations echo "SET autocommit=0" | mysql ${conn} echo "SET unique_checks=0" | mysql ${conn} echo "SET foreign_key_checks=0" | mysql ${conn} # Restore all databases and tables. for sourcedb in ${dbs[*]} do destdb=${prefix}${sourcedb} echo "Dropping database ${destdb}..." echo "DROP DATABASE IF EXISTS ${destdb}" | mysql ${conn} echo "CREATE DATABASE ${destdb}" | mysql ${conn} # First, all the tables. for table in `echo "SHOW FULL TABLES WHERE Table_type <> 'VIEW'" | mysql $conn $sourcedb | tail -n +2`; do if [[ "${table}" != 'BASE' && "${table}" != 'TABLE' && "${table}" != 'VIEW' ]] ; then createTable=`echo "SHOW CREATE TABLE ${table}"|mysql -B -r $conn $sourcedb|tail -n +2|cut -f 2-` echo "Restoring ${destdb}/${table}..." echo "$createTable ;" | mysql $conn $destdb insertData="INSERT INTO ${destdb}.${table} SELECT * FROM ${sourcedb}.${table}" echo "$insertData" | mysql $conn $destdb fi fi done done echo "SET foreign_key_checks=1" | mysql ${conn} echo "SET unique_checks=1" | mysql ${conn} echo "COMMIT" | mysql ${conn} # Restart the replication client echo "START SLAVE" | mysql ${conn} 正如我所提到的，所有这些操作都在同一台服务器中。 有没有更快的方法来克隆我没有看到的表格？ 他们都是INNODB表格。
谢谢！
在你的脚本
echo "SET autocommit=0" | mysql ${conn} echo "SET unique_checks=0" | mysql ${conn} echo "SET foreign_key_checks=0" | mysql ${conn}
每次执行命令时，都会立即撤销，因为数据库连接终止。 如果你把所有的命令放在一个单独的文件中，并且执行了单个文件，那么你所禁用的所有选项都将在整个会话中保持closures状态。
我重写了脚本，把所有的命令放到一个单独的文件中，就像mysqldump一样。 我也改变了表格的写法。 然后，我会运行单个文件：
dbs=( analytics auth logs users ) server=localhost conn="-h ${server} -u ${username} --password=${password}" PREFIX=whatever DUMPFILE=MySQLDataToClone.sql echo "STOP SLAVE;" > ${DUMPFILE} echo "SET autocommit=0;" >> ${DUMPFILE} echo "SET unique_checks=0;" >> ${DUMPFILE} echo "SET foreign_key_checks=0;" >> ${DUMPFILE} for SRCDB in ${dbs[*]} do echo "DROP DATABASE IF EXISTS ${PREFIX}${SRCDB};" >> ${DUMPFILE} echo "CREATE DATABASE ${PREFIX}${SRCDB};" >> ${DUMPFILE} done for SRCDB in ${dbs[*]} do SQLTOGETTABLES="SELECT CONCAT('CREATE TABLE ${PREFIX}',dbtb,' LIKE ',dbtb,'; INSERT INTO ${PREFIX}',dbtb,' SELECT * FROM dbtb;') FROM (SELECT CONCAT(table_schema,'.',table_name) dbtb FROM information_schema.tables WHERE table_schema='${SRCDB}' AND engine IS NOT NULL) A;" mysql $conn -ANe"${SQLTOGETTABLES}" >> ${DUMPFILE} done echo "START SLAVE;" >> ${DUMPFILE} mysql ${conn} < ${DUMPFILE}
试一试 ！！！