MySQL同主机不同数据库的复制命令-mysql教程-PHP中文网

MySQL同主机不同数据库的复制命令

php中文网

发布： 2016-06-07 15:46:40

原创

1255人浏览过

MySQL同主机不同数据库的复制命令：注意运行在Terminal中，不运行在MySQL命令行中。 1 mysqldump Portal_DEV - u root - ppassword1$ -- add - drop - table | mysql Portal_Optimize - u root - ppassword1$ 在复制过程中，出现如下报错： 1 ERROR 1153 ( 0

MySQL同主机不同数据库的复制命令：注意运行在Terminal中，不运行在MySQL命令行中。

<span>1</span><span> mysqldump </span><span>Portal_DEV</span><span>-</span><span>u root </span><span>-</span><span>ppassword1$ </span><span>--</span><span>add</span><span>-</span><span>drop</span><span>-</span><span>table </span><span>|</span><span> mysql </span><span>Portal_Optimize</span><span>-</span><span>u root </span><span>-</span><span>ppassword1$</span>

登录后复制

在复制过程中，出现如下报错：

<span>1</span><span> ERROR </span><span>1153</span><span>(</span><span>08S01</span><span>)</span><span> at line </span><span>1160</span><span>:</span><span>Got</span><span> a packet bigger than </span><span>'</span><span>max_allowed_packet</span><span>'</span><span> bytes</span>
<span>2</span><span> mysqldump</span><span>:</span><span>Got</span><span> errno </span><span>32</span><span>on</span><span> write</span>

登录后复制

需要将my.cnf中的max_allowed_packet 放大，我本机默认是1M，放大至32M。重启MySQL。

OK. 以上方案适用于小数据量的数据库。下面来个大容量（1G以上）的。

<span>1</span><span>  mysqldump </span><span>-</span><span>uroot </span><span>-</span><span>ppassword </span><span>--</span><span>quick </span><span>Portal_DEV</span><span>|</span><span> gzip </span><span>></span><span>Portal_DEV</span><span>.</span><span>contents</span><span>.</span><span>gz</span>

登录后复制

通过以上命令，将Portal_DEV数据库导成Portal_DEV.contents.gz压缩文件。

<span>gunzip </span><span><</span><span>Portal_DEV</span><span>.</span><span>contents</span><span>.</span><span>gz </span><span>|</span><span> mysql </span><span>-</span><span>uroot </span><span>-</span><span>ppassword </span><span>Portal_Optimize</span>

登录后复制

然后通过以上命令，将Portal_DEV导入Portal_Optimize数据库。

Perfect, it's done!!!

来自：http://www.cnblogs.com/droko/archive/2011/10/25/2224130.html