Follow Me
Subscribes Hell Buddy Solution.

Google+
^
Back To Top

Feel Glad finally done the Meta Tags generator and video :) project
- type: seo html - 30 September 2014 Tue @Hell Buddy

close
Home Message Share Translate

MySQL - How to transfer data in safe?

10August 2014
Sunday



How to convert engine and transfer data in safe?

Please do not drop the table that already consist data(especially the massive data) unless you don't want it. Before you decide to drop any table, you must save the data as export the .sql file and insert the data into the other new tables; otherwise, you will lost all the important data that would be very difficult to get recovery.


Read the content or code / instruction -

InnoDB vs. MyISAM

Give an example: table Message_1 is a table in InnoDB engine type, now it's going move to the other MyISAM base table for getting FULLTEXT function ...

InnoDB is a Transaction-safe (ACID compliant) storage engine, it can be commit, rollback and crash-recovery capabilities to protect user data. MyISAM is non-transaction-safe, but use low byte for storing data and fast process.

You can have multiple types engines in one database such as you can have many cars park in your same garage. But for management system, we'd better to concern the database organization.

For this example to create a new database(newDB) and table(Message_2) for making a set of container to contains the data...

  1. CREATE new database


  2. CREATE DATABASE newDB;


  3. CREATE new table in new database


  4. CREATE TABLE Message_2( ... )ENGINE=MyISAM


  5. INSERT into new table from old table and existing database

  6. INSERT INTO TABLE Message_2( ... )
    SELECT oldDB.Message_1.columeName FROM oldDB.Message_1


    the subsequence is: database.table.column

Before drop the table...

Make sure you have taken the steps of file protection before taking any table drop actions; otherwise, it will drive you up the wall when you loss any data that you don't want to!

  1. Make sure the data has been transferred into the new table from old table

  2. SELECT * FROM newDB.Message_2;
    SELECT * FROM oldDB.Message_1;

  3. Export table .sql file

  4. go to phpMyAdmin -> choose the database -> click "export" tab on top bar -> Choose export method -> specific SQL format -> click GO


    save the file to your computer directory.

  5. DROP KEY INDEX before DROP TABLE

  6. DROP TABLE table_name;


    You will get the "confirm message" to drop table. Beware, it would be affected the relation between with the other table of the index key constraint. it would be generated error and data inaccurate. So, the secure way is: drop the index key before drop the table;

    ALTER TABLE table_name DROP FOREIGN KEY key_name;


    ALTER TABLE table_name DROP INDEX index_name;

open the content >>

see the code OR slideshow instruction



//<--STEP 1 - Create MySQL New Database-->

CREATE DATABASE new_Database;

//<--STEP 2 - Create MySQL Table example new table name Message_2-->

CREATE TABLE Message_2
(
id INT(100) NOT NULL AUTO_INCREMENT,
title VARCHAR(100),
message TEXT,
PRIMARY KEY(id),
FULLTEXT INDEX(title, message)
)ENGINE=MyISAM;

//<--STEP 3 - INSERT into new table From the other database table-->

INSERT INTO Message_2(title, message)
SELECT old_Database.Message_1.title, old_Database.Message_1.message FROM old_Database.Message_1;


//<--STEP 4 - Check all the SELECT tables-->

SELECT * FROM old_Database.Message_1;
SELECT * FROM new_Database.Message_2;

Watch next videos and relative source page(s)

tag MySQL - mysql phpmyadmin
Programming Web Development Networking FunG project

Topic Discussion:

No Topic Discuss Now...
SEO