Shulou(Shulou.com)06/01 Report--

There are many ways for MySQL to replicate a database. Choose the corresponding method according to different scenes.

When writing can be stopped, data consistency does not need to be guaranteed, and speed is needed, physical replication can be done. The following script is given:

#! / bin/bashdatadir=/data/mysqlexport PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin:/usr/local/mysql/binusage () {echo-e "Usage: $0 db1 db2\ nCopy db1 to db2.\ nNote: if db2 exists,drop and recreate it." exit 1} if [$#-ne 2]; then usage Fi [- d ${datadir} / $2] & & rm-rf ${datadir} / $2/*mysql-- login-path=recover-e "drop database if exists $2X create database $2;" for i in `ls-1 ${datadir} / $1 / | grep .ibd | awk-F'.'{print $1} '`domysql-- login-path=recover-e "create table $2.Secreti like $1.accouniscape alter TABLE $2.Secreti DISCARD TABLESPACE;flush tables $1.principi for export System cp-pf ${datadir} / $1 import tablespace;analyze table I. {ibd,cfg} ${datadir} / $2max; unlock tables; "mysql-- login-path=recover-e" set foreign_key_checks=0;alter table $2.Secreti import tablespace;analyze table $2.flexii; "donerm-rf ${datadir} / * cfgexit 0"

The specific implementation can be found in the official documentation.

The implementation is simple, but the process finds something interesting.

At the beginning, the script did not have the phrase [- d ${datadir} / $2] & & rm-rf ${datadir} / $2max *. There is nothing wrong with executing the script for the first time. However, when the source library is modified and executed again, the target library is locked. Check the process to find System lock, and the error log prompts Orphan table encountered during DROP DATABASE. This is possible if 'db2/t1.frm' was lost.

This MySQL service cannot shutdown properly. Restart the process by dropping the kill.

After restarting the service, the target library still cannot be deleted. All files in the target library directory need to be deleted, and the target library can be deleted.

The above problems occur in the 5.6 series, but not in the 5.7 and 8.0 series.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.