Mysql database migration using shell command
I had a problem migrating the large mysql databases from one server to another. When ever i tried to do that using some utility I got “Out of Memory” error.
After a bit of research work, I realized that big databases can be migrated using shell command easily
Below you can find instructions to move database from one hosting provider to another:
1. Make database backup
The following shell command will dump whole database into file “dump.sql”: shell> mysqldump -u [uname] -p [dbname] > [dump.sql] It will prompt you for database password. Type in the password ORshell> mysqldump –host=[hostname] –user=[username] –password=[passowrd] [database_name] > dump.sql
2. Back up the database
Pack that backup into archive (for example – tar.gz). The following shell command will pack file “dump.sql” into archive “dump.tar.gz”:
shell> tar -czvf dump.tar.gz ./dump.sql OR (for TAR that doesn’t support ‘z’ option):shell> tar -cvf – ./dump.sql | gzip -c > dump.tar.gz
3. Transfer the archived from one hosting provider to another using FTP or anything else.
4. Unpack that backup archive at new server.
shell> tar -zxvf ./dump.tar.gzOR (for TAR that doesn’t support ‘z’ option): shell> gunzip < ./dump.tar.gz | tar -xvf -
5. Restore database from dump file using native MySQL command-line tool – mysql
shell> mysql –host=[hostname] –user=[username] –password=[password] [database_name] < dump.sql To view how to restore mysql from compressed mysql file Pleasit visit http://blog.technointellects.com/how-to-restore-compressed-mysql-backup-file/compress directory under Linux using shell prompt
How can I compress a whole directory under Linux / UNIX using a shell prompt?
It is useful to backup files, email all files. Technically, it is called as a compressed archive. GNU tar command is best for this work. It can be use on remote Linux or UNIX server. It does two things for you:
1. Create the archive
2. Compress the archive
You need to use tar command as follows (syntax of tar command):
tar -zcvf archive-name.tar.gz directory-name
Where,
- -z: Compress archive using gzip program
- -c: Create archive
- -v: Verbose i.e display progress while creating archive
- -f: Archive File name
For example, you have directory called /home/demo/prog and you would like to compress this directory then you can type tar command as follows:
$ tar -zcvf demo-11-july-2008.tar.gz /home/demo/prog
Above command will create an archive file called demo-1-jan-2005.tar.gz in current directory. If you wish to restore your archive then you need to use following command (it will extract all files in current directory):
$ tar -zxvf demo-11-july-2008.tar.gz
Where,
- -x: Extract files
If you wish to extract files in particular directory, for example in /tmp then you need to use following command:
$ tar -zxvf demo-11-july-2008.tar.gz -C /tmp
$ cd /tmp
$ ls
RSS Feeds
