Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Copy Database

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

       Copy Database

      In modern times, companies rely on databases to store crucial information like financial transactions, customer profiles, and employee records. It is very important to maintain regular copies of databases there can always be a chance of data loss from power surges and disk crashes. Therefore, regular backups of databases are crucial for effective data management.

      Copy Database in MySQL

      In MySQL, copying a database involves creating an exact duplicate of an existing database, including its schema and data. This is almost similar to having a backup of a database. It is important to ensure that any changes made to the original database after the copy is made are also reflected in the copied database, if necessary.
      To create a copy of a database SQL Server provides the Copy Database statement. But, this is not available in MySQL. Therefore, to create copy of a database we need to dump the contents of one database to other manually.
      The following are three steps that involve in copying a database
      • First of all, we need to create a new database.
      • Then, we need to export the original database using mysqldump.
      • Finally, importing the exported data into the new database.

      Example

      First of all, let us create a database in the MySQL server using the following query 
      CREATE DATABASE testdb;
      We can verify whether the database testdb is created or not using the SHOW DATABASES statement.
      SHOW DATABASES;
      As we can see the output below, the testdb database has been created successfully.
      Database
      information_schema
      mysql
      performance_schema
      testdb
      Once the database is created successfully, we need to change the current database to 'testdb', using the USE statement so that any operations we perform such as creating a table will be stored in this database.
      USE testdb;
      Now, let us create a table named CUSTOMERS using the CREATE query as follows 
      CREATE TABLE CUSTOMERS (
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      The following query inserts 7 records into the above-created table 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 ),
      (3, 'Kaushik', 23, 'Kota', 2000.00 ),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
      (7, 'Muffy', 24, 'Indore', 10000.00 );
      Using the following query, we can verify whether the table CUSTOMERS is created in 'testdb' database or not 
      SHOW TABLES;
      The table is successfully created in the testdb database.
      Tables_in_testdb
      customers

      Creating Copy database (Manually)

      As discussed earlier in MySQL to create a copy of an existing database we need to create a separate database and dump the contents of it to the newly created database manually.
      Following statement creates a database named testdb_copy (to which we need to copy the contents of the testdb database created above).
      CREATE DATABASE testdb_copy;
      Once both our source (testdb) and destination (testdb_copy) databases are ready, we need to follow the steps given below to manually copy the data from one to another.
      Step1 − Open the command prompt, browse through the bin folder of the MySQL server. For instance, we have installed MySQL in the C\ Program Files directory so, the following command will take us to the bin folder 
      C:\> CD C:\Program Files\MySQL\MySQL Server 8.0\bin
      Step 2 − Using the mysqldump tool, we can copy the database objects and data into a .sql file. Here, we are exporting the contents of the testdb database to a file named "testdb.sql" located at "D:\Database_backup".
      Note − The (>) operator is used for exporting the database from one location to another.
      mysqldump -u root -p testdb > D:\database_backup\testdb.sql
      Step 3 − Import the contents of the "testdb.sql" file into the destination database (in our case "testdb_copy").
      Note − The (<) operator is used for importing the database from one location to another.
      mysql -u root -p testdb_copy < D:\database_backup\testdb.sql

      Verification

      To verify whether the data and database object is imported into the testdb_copy database, first, we need to use the current database using the following query in the MySQL Command Line Client 
      USE testdb_copy;
      If the contents of the testdb are copied in to the testdb_copy successfully, we should be able to find the customers table in the list of tables (which is created earlier).
      Therefore, let us verify whether the data from the "testdb" database have been copied to the "testdb_copy" database or not using the following query 
      SHOW TABLES;
      As we can see in the list below, all the database objects and data have been successfully copied.
      Tables_in_testdb
      customers

      Copy Database Without MySQLdump

      If we want to copy a database without using the mysqldump tool, we must manually create each table in the destination database and copy all the data from the tables present in the current database. This is a repitetive process that should be done for each table that needs to be copied.

      Example

      Let us create a new database in the MySQL server using the following query 
      CREATE DATABASE Tutorials;
      We can verify whether the database Tutorials is created or not using the following query 
      SHOW DATABASES;
      As we can see the output below, the 'Tutorials' database has been created successfully.
      Database
      information_schema
      mysql
      performance_schema
      tutorials
      Now, we are switching the current database to Tutorials, so that any operations we perform such as creating a table will be stored in this database.
      USE Tutorials;
      Once we have switched, create a table named CUSTOMERS using the following query 
      CREATE TABLE CUSTOMERS (
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      Here, we are inserting some records into the table using the INSERT INTO statement below 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 ),
      (3, 'Kaushik', 23, 'Kota', 2000.00 ),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
      (7, 'Muffy', 24, 'Indore', 10000.00 );
      Using the below query, let us create another database Tutorials_copy, where we want to copy all the data objects and data of the Tutorials database.
      CREATE DATABASE Tutorials_copy;
      We can verify whether the database Tutorials_copy is created or not, using the following query 
      SHOW DATABASES;
      The database has been created.
      Database
      information_schema
      mysql
      performance_schema
      tutorials
      tutorials_copy
      Now, switch the current database to 'tutorials_copy' using the following query
      USE Tutorials_copy;
      Here, we are creating an empty table named "CUSTOMERS" in the 'Tutorials_copy' database with the same schema as the original 'CUSTOMERS' table in the 'Tutorials' 
      CREATE TABLE Tutorials_copy.customers LIKE Tutorials.customers;
      This query inserts all the data from the original 'customers' table in the 'Tutorials' database into the new 'customers' table in the 'tutorials_copy' database.
      INSERT Tutorials_copy.customers SELECT * FROM Tutorials.customers;
      We can verify whether the database objects and data from the 'Tutorials' database have been copied to the 'Tutorials_copy' database or not.
      SHOW TABLES;

      Output

      As we can see in the list below, the table has been successfully copied
      Tables_in_tutorials_copy
      customers
      Let us also retrieve the records of CUSTOMERS table in 'Tutorials_copy' database to verify whether the records have been copied or not 
      Select * from CUSTOMERS;
      As we can see the CUSTOMERS table in 'Tutorials_copy' database below, the records have been successfully copied
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00