Hướng dẫn copy database to another database mysql - sao chép cơ sở dữ liệu sang cơ sở dữ liệu khác mysql

Như đã đề cập trong câu trả lời của Greg, mysqldump db_name | mysql new_db_name là cách miễn phí, an toàn và dễ dàng để truyền dữ liệu giữa các cơ sở dữ liệu. Tuy nhiên, nó cũng thực sự chậm.

Nếu bạn đang tìm kiếm dữ liệu sao lưu, không đủ khả năng để mất dữ liệu (trong cơ sở dữ liệu này hoặc cơ sở dữ liệu khác) hoặc đang sử dụng các bảng khác ngoài innodb, thì bạn nên sử dụng mysqldump.

Nếu bạn đang tìm kiếm một cái gì đó để phát triển, hãy nhờ tất cả các cơ sở dữ liệu của bạn được sao lưu ở nơi khác, và thoải mái thanh lọc và cài đặt lại mysql (có thể bằng tay) khi mọi thứ sai, thì tôi có thể có giải pháp cho bạn.

Tôi không thể tìm thấy một sự thay thế tốt, vì vậy tôi đã xây dựng một kịch bản để tự làm. Tôi đã dành rất nhiều thời gian để làm điều này để làm việc lần đầu tiên và nó thật sự làm tôi kinh hoàng một chút để thay đổi nó bây giờ. Cơ sở dữ liệu Innodb không có nghĩa là sao chép và dán như thế này. Những thay đổi nhỏ khiến điều này thất bại theo những cách tuyệt vời. Tôi đã không gặp vấn đề gì kể từ khi tôi hoàn thành mã, nhưng điều đó không có nghĩa là bạn sẽ không.

Các hệ thống được thử nghiệm trên (nhưng vẫn có thể thất bại):

• Ubuntu 16.04, mặc định mysql, innodb, các tệp riêng biệt cho mỗi bảng
• Ubuntu 18.04, mặc định mysql, innodb, các tệp riêng biệt cho mỗi bảng

Kể từ đó, chúng tôi đã chuyển sang Docker và một bản sao đơn giản của toàn bộ thư mục dữ liệu MySQL, vì vậy tập lệnh này không còn được duy trì. Rời khỏi nó trong trường hợp nó có thể giúp bất cứ ai trong tương lai.

Những gì nó làm

1. Nhận đặc quyền sudo và xác minh bạn có đủ không gian lưu trữ để sao chép cơ sở dữ liệu
2. Nhận các đặc quyền của MySQL gốc
3. Tạo một cơ sở dữ liệu mới được đặt theo tên của Chi nhánh Git hiện tại
4. Cấu trúc bản sao cho cơ sở dữ liệu mới
5. Chuyển sang chế độ phục hồi cho innodb
6. Xóa dữ liệu mặc định trong cơ sở dữ liệu mới
7. Dừng MySQL
8. Dữ liệu nhân bản vào cơ sở dữ liệu mới
9. Bắt đầu mysql
10. Liên kết dữ liệu đã nhập trong cơ sở dữ liệu mới
11. Chuyển khỏi chế độ phục hồi cho innodb
12. Khởi động lại MySQL
13. Cung cấp quyền truy cập người dùng MySQL vào cơ sở dữ liệu
14. Làm sạch các tệp tạm thời

Cách nó so sánh với mysqldump

Trên cơ sở dữ liệu 3GB, sử dụng mysqldump và mysql sẽ mất 40-50 phút trên máy của tôi. Sử dụng phương pháp này, quá trình tương tự sẽ chỉ mất ~ 8 phút.

Cách chúng tôi sử dụng nó

Chúng tôi đã lưu các thay đổi SQL của chúng tôi cùng với mã của chúng tôi và quy trình nâng cấp được tự động hóa trên cả sản xuất và phát triển, với mỗi bộ thay đổi thực hiện sao lưu cơ sở dữ liệu để khôi phục nếu có lỗi. Một vấn đề chúng tôi gặp phải là khi chúng tôi đang thực hiện một dự án dài hạn với các thay đổi cơ sở dữ liệu và phải chuyển các nhánh ở giữa nó để sửa lỗi hoặc ba lỗi.

Trước đây, chúng tôi đã sử dụng một cơ sở dữ liệu duy nhất cho tất cả các nhánh và sẽ phải xây dựng lại cơ sở dữ liệu bất cứ khi nào chúng tôi chuyển sang một nhánh không tương thích với các thay đổi cơ sở dữ liệu mới. Và khi chúng tôi quay lại, chúng tôi phải chạy lại các bản nâng cấp.

Chúng tôi đã thử mysqldump để nhân đôi cơ sở dữ liệu cho các nhánh khác nhau, nhưng thời gian chờ quá dài (40-50 phút) và chúng tôi không thể làm bất cứ điều gì khác trong thời gian đó.

Giải pháp này đã rút ngắn thời gian nhân bản cơ sở dữ liệu xuống còn 1/5 thời gian (nghĩ rằng cà phê và phòng tắm nghỉ thay vì một bữa trưa dài).

Nhiệm vụ phổ biến và thời gian của họ

Chuyển đổi giữa các nhánh với các thay đổi cơ sở dữ liệu không tương thích mất hơn 50 phút trên một cơ sở dữ liệu duy nhất, nhưng hoàn toàn không có thời gian sau thời gian thiết lập ban đầu với mysqldump hoặc mã này. Mã này chỉ xảy ra nhanh hơn ~ 5 lần so với mysqldump.

Dưới đây là một số nhiệm vụ phổ biến và khoảng thời gian chúng sẽ mất trong mỗi phương pháp:

Tạo nhánh tính năng với các thay đổi cơ sở dữ liệu và hợp nhất ngay lập tức:

• Cơ sở dữ liệu đơn: ~ 5 phút
• Clone với mysqldump: 50-60 phút
• Clone với mã này: ~ 18 phút

Tạo nhánh tính năng với các thay đổi cơ sở dữ liệu, chuyển sang mysqldump db_name | mysql new_db_name3 cho một lỗi, thực hiện chỉnh sửa trên nhánh tính năng và hợp nhất:

• Cơ sở dữ liệu đơn: ~ 60 phút
• Clone với mysqldump: 50-60 phút
• Clone với mã này: ~ 18 phút

Tạo nhánh tính năng với các thay đổi cơ sở dữ liệu, chuyển sang mysqldump db_name | mysql new_db_name3 cho một lỗi, thực hiện chỉnh sửa trên nhánh tính năng và hợp nhất:

• Cơ sở dữ liệu đơn: ~ 60 phút
• Clone với mysqldump: 50-60 phút
• Clone với mã này: ~ 18 phút

Tạo nhánh tính năng với các thay đổi cơ sở dữ liệu, chuyển sang mysqldump db_name | mysql new_db_name3 cho một lỗi, thực hiện chỉnh sửa trên nhánh tính năng và hợp nhất:

Cơ sở dữ liệu đơn: ~ 60 phút

#!/bin/bash
set -e

# This script taken from: https://stackoverflow.com/a/57528198/526741

function now {
    date "+%H:%M:%S";
}

# Leading space sets messages off from step progress.
echosuccess () {
    printf "\e[0;32m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echowarn () {
    printf "\e[0;33m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echoerror () {
    printf "\e[0;31m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echonotice () {
    printf "\e[0;94m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echoinstructions () {
    printf "\e[0;104m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echostep () {
    printf "\e[0;90mStep %s of 13:\e[0m\n" "$1"
    sleep .1
}

MYSQL_CNF_PATH='/etc/mysql/mysql.conf.d/recovery.cnf'
OLD_DB='YOUR_DATABASE_NAME'
USER='YOUR_MYSQL_USER'

# You can change NEW_DB to whatever you like
# Right now, it will append the current git branch name to the existing database name
BRANCH=`git rev-parse --abbrev-ref HEAD`
NEW_DB="${OLD_DB}__$BRANCH"

THIS_DIR=./site/upgrades
DB_CREATED=false

tmp_file () {
    printf "$THIS_DIR/$NEW_DB.%s" "$1"
}
sql_on_new_db () {
    mysql $NEW_DB --unbuffered --skip-column-names -u root -p$PASS 2>> $(tmp_file 'errors.log')
}

general_cleanup () {
    echoinstructions 'Leave this running while things are cleaned up...'

    if [ -f $(tmp_file 'errors.log') ]; then
        echowarn 'Additional warnings and errors:'
        cat $(tmp_file 'errors.log')
    fi

    for f in $THIS_DIR/$NEW_DB.*; do
        echonotice 'Deleting temporary files created for transfer...'
        rm -f $THIS_DIR/$NEW_DB.*
        break
    done

    echonotice 'Done!'
    echoinstructions "You can close this now :)"
}

error_cleanup () {
    exitcode=$?

    # Just in case script was exited while in a prompt
    echo

    if [ "$exitcode" == "0" ]; then
        echoerror "Script exited prematurely, but exit code was '0'."
    fi

    echoerror "The following command on line ${BASH_LINENO[0]} exited with code $exitcode:"
    echo "             $BASH_COMMAND"

    if [ "$DB_CREATED" = true ]; then
        echo
        echonotice "Dropping database \`$NEW_DB\` if created..."
        echo "DROP DATABASE \`$NEW_DB\`;" | sql_on_new_db || echoerror "Could not drop database \`$NEW_DB\` (see warnings)"
    fi

    general_cleanup

    exit $exitcode
}

trap error_cleanup EXIT

mysql_path () {
    printf "/var/lib/mysql/"
}
old_db_path () {
    printf "%s%s/" "$(mysql_path)" "$OLD_DB"
}
new_db_path () {
    printf "%s%s/" "$(mysql_path)" "$NEW_DB"
}
get_tables () {
    (sudo find /var/lib/mysql/$OLD_DB -name "*.frm" -printf "%f\n") | cut -d'.' -f1 | sort
}

STEP=0


authenticate () {
    printf "\e[0;104m"
    sudo ls &> /dev/null
    printf "\e[0m"
    echonotice 'Authenticated.'
}
echostep $((++STEP))
authenticate

TABLE_COUNT=`get_tables | wc -l`
SPACE_AVAIL=`df -k --output=avail $(mysql_path) | tail -n1`
SPACE_NEEDED=(`sudo du -s $(old_db_path)`)
SPACE_ERR=`echo "$SPACE_AVAIL-$SPACE_NEEDED" | bc`
SPACE_WARN=`echo "$SPACE_AVAIL-$SPACE_NEEDED*3" | bc`
if [ $SPACE_ERR -lt 0 ]; then
    echoerror 'There is not enough space to branch the database.'
    echoerror 'Please free up some space and run this command again.'
    SPACE_AVAIL_FORMATTED=`printf "%'d" $SPACE_AVAIL`
    SPACE_NEEDED_FORMATTED=`printf "%'${#SPACE_AVAIL_FORMATTED}d" $SPACE_NEEDED`
    echonotice "$SPACE_NEEDED_FORMATTED bytes needed to create database branch"
    echonotice "$SPACE_AVAIL_FORMATTED bytes currently free"
    exit 1
elif [ $SPACE_WARN -lt 0 ]; then
    echowarn 'This action will use more than 1/3 of your available space.'
    SPACE_AVAIL_FORMATTED=`printf "%'d" $SPACE_AVAIL`
    SPACE_NEEDED_FORMATTED=`printf "%'${#SPACE_AVAIL_FORMATTED}d" $SPACE_NEEDED`
    echonotice "$SPACE_NEEDED_FORMATTED bytes needed to create database branch"
    echonotice "$SPACE_AVAIL_FORMATTED bytes currently free"
    printf "\e[0;104m"
    read -p " $(now): Do you still want to branch the database? [y/n] " -n 1 -r CONFIRM
    printf "\e[0m"
    echo
    if [[ ! $CONFIRM =~ ^[Yy]$ ]]; then
        echonotice 'Database was NOT branched'
        exit 1
    fi
fi

PASS='badpass'
connect_to_db () {
    printf "\e[0;104m %s: MySQL root password: \e[0m" "$(now)"
    read -s PASS
    PASS=${PASS:-badpass}
    echo
    echonotice "Connecting to MySQL..."
}
create_db () {
    echonotice 'Creating empty database...'
    echo "CREATE DATABASE \`$NEW_DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" | mysql -u root -p$PASS 2>> $(tmp_file 'errors.log')
    DB_CREATED=true
}
build_tables () {
    echonotice 'Retrieving and building database structure...'
    mysqldump $OLD_DB --skip-comments -d -u root -p$PASS 2>> $(tmp_file 'errors.log') | pv --width 80  --name " $(now)" > $(tmp_file 'dump.sql')
    pv --width 80  --name " $(now)" $(tmp_file 'dump.sql') | sql_on_new_db
}
set_debug_1 () {
    echonotice 'Switching into recovery mode for innodb...'
    printf '[mysqld]\ninnodb_file_per_table = 1\ninnodb_force_recovery = 1\n' | sudo tee $MYSQL_CNF_PATH > /dev/null
}
set_debug_0 () {
    echonotice 'Switching out of recovery mode for innodb...'
    sudo rm -f $MYSQL_CNF_PATH
}
discard_tablespace () {
    echonotice 'Unlinking default data...'
    (
        echo "USE \`$NEW_DB\`;"
        echo "SET foreign_key_checks = 0;"
        get_tables | while read -r line;
            do echo "ALTER TABLE \`$line\` DISCARD TABLESPACE; SELECT 'Table \`$line\` imported.';";
        done
        echo "SET foreign_key_checks = 1;"
    ) > $(tmp_file 'discard_tablespace.sql')
    cat $(tmp_file 'discard_tablespace.sql') | sql_on_new_db | pv --width 80 --line-mode --size $TABLE_COUNT --name " $(now)" > /dev/null
}
import_tablespace () {
    echonotice 'Linking imported data...'
    (
        echo "USE \`$NEW_DB\`;"
        echo "SET foreign_key_checks = 0;"
        get_tables | while read -r line;
            do echo "ALTER TABLE \`$line\` IMPORT TABLESPACE; SELECT 'Table \`$line\` imported.';";
        done
        echo "SET foreign_key_checks = 1;"
    ) > $(tmp_file 'import_tablespace.sql')
    cat $(tmp_file 'import_tablespace.sql') | sql_on_new_db | pv --width 80 --line-mode --size $TABLE_COUNT --name " $(now)" > /dev/null
}
stop_mysql () {
    echonotice 'Stopping MySQL...'
    sudo /etc/init.d/mysql stop >> $(tmp_file 'log')
}
start_mysql () {
    echonotice 'Starting MySQL...'
    sudo /etc/init.d/mysql start >> $(tmp_file 'log')
}
restart_mysql () {
    echonotice 'Restarting MySQL...'
    sudo /etc/init.d/mysql restart >> $(tmp_file 'log')
}
copy_data () {
    echonotice 'Copying data...'
    sudo rm -f $(new_db_path)*.ibd
    sudo rsync -ah --info=progress2 $(old_db_path) --include '*.ibd' --exclude '*' $(new_db_path)
}
give_access () {
    echonotice "Giving MySQL user \`$USER\` access to database \`$NEW_DB\`"
    echo "GRANT ALL PRIVILEGES ON \`$NEW_DB\`.* to $USER@localhost" | sql_on_new_db
}

echostep $((++STEP))
connect_to_db

EXISTING_TABLE=`echo "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$NEW_DB'" | mysql --skip-column-names -u root -p$PASS 2>> $(tmp_file 'errors.log')`
if [ "$EXISTING_TABLE" == "$NEW_DB" ]
    then
        echoerror "Database \`$NEW_DB\` already exists"
        exit 1
fi

echoinstructions "The hamsters are working. Check back in 5-10 minutes."
sleep 5

echostep $((++STEP))
create_db
echostep $((++STEP))
build_tables
echostep $((++STEP))
set_debug_1
echostep $((++STEP))
discard_tablespace
echostep $((++STEP))
stop_mysql
echostep $((++STEP))
copy_data
echostep $((++STEP))
start_mysql
echostep $((++STEP))
import_tablespace
echostep $((++STEP))
set_debug_0
echostep $((++STEP))
restart_mysql
echostep $((++STEP))
give_access

echo
echosuccess "Database \`$NEW_DB\` is ready to use."
echo

trap general_cleanup EXIT

Tạo nhánh tính năng với các thay đổi cơ sở dữ liệu, chuyển sang mysqldump db_name | mysql new_db_name3 cho lỗi 5 lần trong khi thực hiện các chỉnh sửa trên nhánh tính năng ở giữa và hợp nhất:

Bạn có thể chuyển cơ sở dữ liệu MySQL không?

Làm cách nào để sao chép toàn bộ cơ sở dữ liệu SQL?

Làm cách nào để chuyển dữ liệu từ cơ sở dữ liệu này sang cơ sở dữ liệu khác?

Làm cách nào để chuyển dữ liệu từ máy chủ MySQL này sang máy chủ khác?