Backup Mysql Tutorial

This tutorial explains the how to backup and restore the MySQL Database. Databases are used to store large amount of precious data and it becomes very important to Backup your data. In case case of some hardware or software failures backup data can be used to restore the Database.

Backing Up MySQL Database

MySQL database backup can be accomplished in two ways:

a) Copying the raw mysql database files &
b) Exporting tables to text files

Copying the MySQL database files

MySQL uses the same table format on different platforms, so it’s possible to copy MySQL table and index files from one platform and use them on another without any difficulties (assuming, of course, that you’re using the same version of MySQL on both platforms).

Exporting tables to text files

The MySQLDump is handy utility that can be used to quickly backup the MySQL Database to the text files. To use the MySQLDump utility it is required to logon to the System running the MySQL Databse. You can use Telnet to remotely logon to the system if you don’t have the physical access to the machine.

The syntax for the command is as follows.

mysqldump -u [Username] -p [password] [databasename] > [backupfile.sql]
[username] – this is your database username
[password]- this is the password for your database
[databasename] – the name of your database
[backupfile.sql] – the filename for your database backup

Let’s discuss the example of backing up MySQL Database named “accounts” into text file accounts.sql. Here are the scenarios of taking the backup assuming that both user name and password of the database is “admin”.

a) Taking the full backup of all the tables including the data.
Use the following command to accomplish this:
mysqldump -u admin -p admin accounts > accounts.sql

b) Taking the backup of table structures only.
Use the following command to accomplish this:
mysqldump -u admin -p admin –no-data accounts > accounts.sql

c) Taking the backup data only.
Use the following command to accomplish this:
mysqldump -u admin -p admin –no-create-info accounts > accounts.sql

Restoring MySQL Database

Restoring the MySQL is very easy job. You can use the following to command to restore the accounts database from accounts.sql backup file.

mysql – u admin -p admin accounts < accounts.sql

In this tutorial you learned how to take the backup of your MySQL Database and restore the same in the event of some database crash or on some other machine.

——— CARA KE II ——–

Backup MySQL Database dengan MySQLDump

<!–
google_ad_client = “pub-1199705375920649″;
google_ad_width = 250;
google_ad_height = 250;
google_ad_format = “250x250_as”;
google_ad_type = “text_image”;
google_ad_channel = “”;
google_color_border = “FFFFFF”;
google_color_bg = “FFFFFF”;
google_color_link = “CC3300″;
google_color_text = “000000”;
google_color_url = “804000”;
//–>

window.google_render_ad();

Mysqldump adalah salah satu fasilitas bawaan server MYSQL, yang berfungsi untuk membackup database mysql. mysqldump adalah perintah command line, tapi apabila menghendaki dijalankan rutin bisa di pasang di cron system.

Contoh penggunaan :

mysqldump –user [user name]–password=[password] [database name] > [dump file]

Keterangan perintah di atas :

  • –user [user name] : user name adalah user account dalam MYSQL server
  • –password=[password] : password dari user name
  • [database name] : nama database yang akan di backup , apabila kita ingin membackup semua database yang ada gunakan options –all-databases
  • [dump file] : adalah nama file hasil backup database, bisa gunakan path , misal /var/backup/mysql.dump

Selain bisa digunakan membackup database ke file, mysqldump bisa juga membackup database ke database mysql di server lain. dengan perintah :

mysqldump –user admin –password=test –opt mydata | mysql –host=192.168.13.13 -C newmydata

Keterangan :
–opt : options ini berfungsi mengoptimalkan fungsing pembacaan database
–host : server tujuan tempat data base tujuan (di server tujuan sudah dibuat newmydata database terlebih dahulu)
-C : options ini berfungsi agar data di kompresi terlebih dahulu sebelum di kirim ke database tujuan

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: