☰

在同一台服务器内克隆 INNODB 表的最快方法是什么？

tag-icon • tag-icon mysql backup
在同一台服务器内克隆 INNODB 表的最快方法是什么？

我们的开发服务器是生产服务器的复制从属服务器。我们有一个脚本，开发人员可以使用它针对新数据运行其应用程序/错误修复。该脚本如下所示：

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 表。

谢谢！

答案1

在你的脚本中

echo "SET autocommit=0" | mysql ${conn}
echo "SET unique_checks=0" | mysql ${conn}
echo "SET foreign_key_checks=0" | mysql ${conn}

每次执行命令时，由于数据库连接终止，该命令都会立即撤消。如果您将所有命令放在一个文件中并执行该文件，则您禁用的所有选项都会在整个会话中保持关闭状态。

我重写了脚本，将所有命令放入单个文件中，就像 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}

试一试 ！！！

相关内容