Mysql – Creacte Mysql Database copy

MySQL

I need copy full database in MySql but cant to do this.

I try

mysqldump db_name | mysql new_db_name

Its work, but DB so big and this comand need 5Gb+ in max_allowed_packed
Maybe you know best solution?
Task: Full copy Database with data

Thanks!

Best Answer

You can do it in two ways :

1 Backup & Restore :

Take backup using mysqldump & restore backup with new name.

2 Manual Create Table in New Database :

create schema new_schema;

select concat("create table new_schema.`",table_name,"` as select * from `",table_schema,"`.`",table_name,"`;")
from
information_schema.tables 
where table_schema='old_schema'; 

/* Execute the above statement output simultaneously*/

-- i.e
create table new_schema.`XXX` as select * from `njindiainvest`.`XXX`;
create table new_schema.`YYY` as select * from `njindiainvest`.`YYY`;
create table new_schema.`ZZZ` as select * from `njindiainvest`.`ZZZ`;

Related Solutions

Mysql – Can thesql restore a single table from a large thesqldump

I have created both linux and windows scripts to restore the specific tables from the dump file:

linux (bash script):

#!/bin/bash

# Where to restore
db_host='localhost'
db_name='adhoctuts'
db_user='root'
db_pass='Adhoctuts2018#'

dump_file='/root/scripts/dump_ignore.sql'

# Associative table list array as source_table=>destination_table pairs
declare -A tbl_list=( ["tbl1"]="restored_tbl1" ["tbl2"]="restored_tbl2")

for tbl in "${!tbl_list[@]}"
do
    echo "Restore $tbl to ${tbl_list[$tbl]}"
    # extract the content between drop table and Table structure for, also replace the table name
    sed -n -e '/DROP TABLE IF EXISTS `'$tbl'`/,/\/*!40000 ALTER TABLE `'$tbl'` ENABLE KEYS \*\/;/p' $dump_file > tbl.sql
    sed -i 's/`'$tbl'`/`'${tbl_list[$tbl]}'`/g' tbl.sql
    mysql -h $db_host -u $db_user -p"$db_pass" $db_name < tbl.sql
    rm -f tbl.sql
done

windows script (bat):

%= Define the database and root authorization details =% 
@ECHO OFF
SETLOCAL EnableDelayedExpansion

set db_host=192.168.70.138
set db_name=adhoctuts
set db_user=adhoctuts
set db_pass=Adhoctuts2018#

set dump_file=dump_ignore.sql

set tbl_cnt=2
set source_table[1]=tbl1
set destination_table[1]=restored_tbl1
set source_table[2]=tbl2
set destination_table[2]=restored_tbl2

set i=1
:loop   
    set src=!source_table[%i%]!
    set dest=!destination_table[%i%]!
    for /f "tokens=1 delims=[]" %%a in ('find /n "DROP TABLE IF EXISTS `%src%`"^<"%dump_file%"') do set /a start=%%a
    for /f "tokens=1 delims=[]" %%a in ('find /n "ALTER TABLE `%src%` ENABLE KEYS"^<"%dump_file%"') do set /a end=%%a
    (
    for /f "tokens=1* delims=[]" %%a in ('find /n /v ""^<"%dump_file%"') do (

    set "line=%%b "
     IF %%a geq %start% IF %%a leq %end% ECHO( !line:%src%=%dest%!
     )
    )>"tbl.sql"

    mysql -h %db_host% -u %db_user% -p"%db_pass%" %db_name% < "tbl.sql"
    del /f "tbl.sql"
    if %i% equ %tbl_cnt% goto :eof
    set /a i=%i%+1
goto loop

here you define the tables you need to restore and under what name to restore. This the more general solution.

I have also created separate tutorial for MySQL selective/exceptional tasks. You may check if needed:

https://youtu.be/8fWQbtIISdc

https://adhoctuts.com/mysql-selective-exceptional-permissions-and-backup-restore/

MySQL backup InnoDB

As your link points out, ALTER TABLE can break a transaction

In fact, according to Page 418 Paragraph 3 of MySQL 5.0 Certification Study Guide

the following commands can break a transaction

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
START TRANSACTION

Thus, you cannot run any of these and have a stable mysqldump

SUGGESTION

You badly need to create a MySQL Replication setup

That way, the Master and stay up 24/7.

On the Slave, you can do the following:

STOP SLAVE;
mysqldump --single-transaction
START SLAVE;

and never disturb the Master.

CAVEAT

Since setting up Replication requires a stable mysqldump, you may need downtime for the one-time setup. My answer would be a lot different if your MySQL was in Linux.