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:
|
1 |
#mysqldump --opt -u root -p sekolah | gzip -9 > /var/www/data.sql.gz |
Then confirm the password will appear, type your MySQL root password
|
1 |
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
|
1 |
# |
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:
|
1 |
#mysql -u root -p |
Prompt appears for the root user password mysql
|
1 |
Enter password: admin |
Mysql console will appear
|
1 |
mysql> CREATE DATABASE school; |
Will come back to the console prompt & mysql, type exit
|
1 2 3 |
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:
|
1 |
# wget http://1.1.1.1/data.sql.gz |
The download process will start, if completed would be to return to the console:
|
1 |
# |
And data.sql.gz files will be in the /root/
Now start import process is as follows:
|
1 |
# gunzip < /root/data.sql.gz | mysql -u root -p school |
Database import process will run, and when completed will be returned to the console:
|
1 |
# |
Has successfully completed a database on export from the old server, and the import to a new server with success.
Credit: KASKUS