≡ Menu

Export and Import Great MySQL Database on Linux

We are often difficult to backup a database that may have been walking for years, finally have a big size.

Examples of cases we want to move hosting to another hosting site, or any other VPS to VPS, etc.

because most hosting/VPS have linux operating system, I demonstrated here using Debian Linux distributions.

Let’s just say we’ll Database Size backup has a size of about 1 Gb, certainly there will be trouble, if we make the process of export & import with:

  • phpmyadmin (hang & error)
  • sql GUI (error)
  • or any copy of the / var / lib / mysql (often errors)
  • or else with a SQL command without compression (will be difficult for the backup process, because the *. sql files are large)

We will apply is the combined use of compression Linux & SQL command

  • E.g. for the two servers I consider to have the same criteria:
  • e.g. export server IP address: 1.1.1.1
  • e.g. import server IP address: 2.2.2.2
  • database name is: “school” (without quotation marks “”)
  • username root mysql: root
  • mysql root password: admin
  • Our database will backup the folder /var/www/
  • backup file name: data

Login as root on linux database that will be contained in the backup (export)

Run the following command:

#mysqldump --opt -u root -p sekolah | gzip -9 > /var/www/data.sql.gz

Then confirm the password will appear, type your MySQL root password

Enter password: admin

Wait a few moments to complete, the backup process is running, after the process is complete, will return to the console again

#

Okay now moved to import destination, login as root in linux destination

Then create a database with the name of the school is as follows:

#mysql -u root -p

Prompt appears for the root user password mysql

Enter password: admin

Mysql console will appear

mysql> CREATE DATABASE school;

Will come back to the console prompt & mysql, type exit

Query OK, 1 row affected (0.00 sec)

  mysql> exit

Then to simplify lawyer sql backup copies of files from old server to new server, do the following command:

# wget http://1.1.1.1/data.sql.gz

The download process will start, if completed would be to return to the console:

#

And data.sql.gz files will be in the /root/

Now start import process is as follows:

# gunzip < /root/data.sql.gz | mysql -u root -p school

Database import process will run, and when completed will be returned to the console:

#

Has successfully completed a database on export from the old server, and the import to a new server with success.

Credit: KASKUS

{ 0 comments… add one }

Leave a Comment

CAPTCHA
*