Cara menggunakan mysql migrate table

Setelah pada tutorial sebelumnya kita belajar install MySQL menggunakan MySQLInstaller, pada tutorial kali ini akan dibahas bagaimana cara membuat database dan table menggunakan MySQL Workbench.

Table of Contents Show

10.8.10 Data Transfer and Migration Setup
Additional Resources
MySQL Schema Transfer Wizard
How do I migrate a database in MySQL Workbench?
How move MySQL database to another server?
What is the easiest way to move a database from one server to another?
What is migration in MySQL Workbench?

Table of Contents

10.8.10 Data Transfer and Migration Setup
Additional Resources
MySQL Schema Transfer Wizard
How do I migrate a database in MySQL Workbench?
How move MySQL database to another server?
What is the easiest way to move a database from one server to another?
What is migration in MySQL Workbench?

Database yang dibuat pada tutorial kali ini hanya menggunakan cara yang sederhana, belum mengatur relasi antar tabel, Foreign Key, dan lain lain dengan harapan memudahkan para pemula yang ingin belajar mysql.

Database dan tabel yang dibuat pada tutorial ini akan digunakan kembali pada kesempatan lain untuk mempelajari perintah Select,Insert,Update,Delete, Trigger stored procedure dan lain-lain

Struktur table yang akan dibuat adalah seperti gambar dibawah ini :

Langkah Pembuatan :

langkah pertama jalankan MySQL Workbench (saat ini saya menggunakan versi 6.1)
Pilih menu Database – Connect to Database , akan keluar tampilan seperti dibawah ini :

isi host name dengan ip adress komputer tempat MySQL berada
isi username dengan nama user yang sudah terdaftar di MySQL
tekan OK
masukan password jika user yang digunakan memakai password atau langsung tekan OK jika user tidak menggunakan password
akan terlihat tampilan seperti dibawah ini :
langkah selanjutnya buat database baru dengan cara click tab schemes click toolbar create a new schema, atau click kanan pada jendela navigator

ketikan nama database yang akan dibuat pada isian name kemudian tekan tombol Apply
setelah database berhasil dibuat langkah selanjutnya adalah membuat table
aktifkan database dengan cara double click pada nama database sampai nama database terlihat cetak tebal atau ketikan use nama database dari Query editor

buat table baru dengan cara click kanan pada nama database atau click create new table dari toolbar
kemudian lengkapi isiannya

Keterangan :

table nama = nama table yang akan dibuat
column name = nama kolom / nama field
data type = type data dan panjang
PK=Primary key jika dicentang artinya isian untuk field ini tidak boleh ganda /harus unik
NN=Not null jika dicentang artinya harus diisi/tidak boleh dikosongkan
Default=nilai default yang akan dimasukan jika tida diisi oleh user

setelah semua isian diisi tekan apply untuk menyimpan kemudian akan keluar seperti dibawah ini

pada gambar diatas terlihat SQL Script untuk pembuatan table yang kita buat
selanjutknya tekan apply sekali lagi untuk mengakhiri pembuatan table
lakukan cara yang sama untuk membuat table lain

untuk lebih jelas silahan lihat video dibawah ini

The world's most popular open source database

Table of Contents

10.8.10 Data Transfer and Migration Setup
Additional Resources
MySQL Schema Transfer Wizard
How do I migrate a database in MySQL Workbench?
How move MySQL database to another server?
What is the easiest way to move a database from one server to another?
What is migration in MySQL Workbench?

10.8.10 Data Transfer and Migration Setup

Transfers data from the source RDBMS to the target MySQL database (see the figure that follows). The setup screen includes the following options:

Data Copy:

Online copy of table data to target RDBMS: This (default) will copy the data to the target RDBMS.
Create a batch file to copy the data at another time: The data may also be dumped to a file that can be executed at a later time, or be used as a backup. This script uses a MySQL connection to transfer the data.
Create a shell script to use native server dump and load abilities for fast migration: Unlike the simple batch file that performs a live online copy, this generates a script to be executed on the source host to then generate a Zip file containing all of the data and information needed to migrate the data locally on the target host. Copy and extract the generated Zip file on the target host and then execute the import script (on the target host) to import the data into MySQL using a LOAD DATA call.

This faster method avoids the need to traffic all data through MySQL Workbench, or to have a permanent network connection between the MySQL servers.

Note
This option was added in MySQL Workbench 6.3.0.

Options:

Truncate target tables before copying data: In case the target database already exists, this will delete said data.
Worker tasks: The default value is 2. This is the number of tasks (database connections) used while copying the data.
Enable debug output for table copy: Shows debugging information.

Figure 10.63 MySQL Workbench Migration: Data Transfer Setup

The MySQL Workbench Migration Wizard is designed to save DBA and developer time by providing visual, point and click ease of use around all phases of configuring and managing a complex migration process:

Improved! Database migrations - enables migrations from Microsoft SQL Server, Microsoft Access, PostgreSQL, Sybase ASE, Sybase SQL Anywhere, SQLite, and more.
Manage Migration Projects - allows migrations to be configured, copied, edited, executed and scheduled.
Source and Target selection - allows users to define specific data sources and to analyze source data in advance of the migration.
Object migration - allows users to select objects to migrate, assign source to target mappings where needed, edit migration scripts and create the target schema.
Version Upgrades - using migration users can easily move databases off older MySQL versions to the latest.

The Migration Wizard allows you to easily and quickly migrate databases from various RDBMS products to MySQL. Click on image to enlarge.

Additional Resources

Video Tutorial: MySQL Workbench
Features & Benefits
Download MySQL Workbench Enterprise Edition

Perform MySQL server version upgrades to move off older MySQL versions to the latest version. The standard migration wizard can migrate MySQL to MySQL, and a simpler Schema Transfer Wizard can also be used.

MySQL Schema Transfer Wizard

The MySQL Schema Transfer wizard helps you move your data from an older MySQL server version to a different (typically later) MySQL version. This migration tool is meant for developer hosts as it is simpler than the standard migration wizard, because it only migrates MySQL to MySQL. The data is transferred and not based on a consistent snapshot, so it works best on local MySQL instances.

Note

You should not use this wizard on production MySQL instances.

To open the wizard, select and then from the main menu. The next figure shows the initial screen.

Figure 10.45 MySQL Schema Transfer Wizard: Overview

Read the overview text and click Start the Wizard to begin. An example transfer appears in the figure that follows.

Figure 10.46 MySQL Schema Transfer Wizard: Connection Selection

Choose your target and source MySQL connections (see the figure that follows). After choosing and testing your MySQL connections, click Next to continue.

Figure 10.47 MySQL Schema Transfer Wizard: Schema Selection

Choose the schemas to migrate, and click Start Copy to begin copying the selected schemas from the source to target MySQL server. The next figure shows the copy status.

Figure 10.48 MySQL Schema Transfer Wizard: Copy Databases

Review the Message Log to confirm that the migration finished with success. Click Next to view a summary of the results. The following figure shows an example of the copy results.

Figure 10.49 MySQL Schema Transfer Wizard: Results

Click Finish to close the wizard.

How do I migrate a database in MySQL Workbench?

From MySQL Workbench, choose Database and then Migrate to open the migration wizard and display the migration wizard overview (see the figure that follows)..

Keep schemas as they are: Catalog. ... .

Only one schema: Catalog..

How move MySQL database to another server?

Stop the database (or lock it).

Go to the directory where the mysql data files are..

Transfer over the folder (and its contents) over to the new server's mysql data directory..

Start back up the database..

On the new server, issue a 'create database' command. '.

Re-create the users & grant permissions..

What is the easiest way to move a database from one server to another?

Right-click on the database and select Tasks and then Copy Database. Once you click on Copy Database then the following screen will appear. Click on "Next". Enter the Source server name (for example, Server A) and enter the Server Authentication mode and click on "Next".

What is migration in MySQL Workbench?

With the MySQL Workbench Migration Wizard, users can convert an existing database to MySQL in minutes rather than hours or days that the same migration would require using traditional, manual methods. The Migration Wizard allows you to easily and quickly migrate databases from various RDBMS products to MySQL.