[SOLVED] Command Line Database Table Backup

[matfish]

Hi, I'm having a little trouble.

 

I'm moving a 280Mb database to a new server which consists of about 36 tables. As you can imagine phpMyadmin is having trouble with the export and import. I have found command line ways to backup the whole database but I was wondering if there is a way to backup individual tables and then import the individual tables via SSH?

 

Many thanks

[Mchl]

mysqldump

[tomfmason]

Here is an excerpt from a  bash script that I used to use for backing up my entire server

backup.sh

#!/usr/bin/env bash
function backup_sql() {
    dbs=`mysql -u root --batch -e "SHOW DATABASES;"`
    backup_dir=/backups/mysql/
    for db in $dbs; do
if [ "$db" != "Database" ]; then
	mysqldump --opt -c -e -Q -q --single-transaction -u root $db > $backup_dir/$db.sql
fi
    done
}
if type "$1" | grep -qF "$1 is a function"; then "$@"; fi

 

Usage: backup.sh backup_sql

 

If you only want to backup one db just do something like this

mysqldump --opt -c -e -Q -q --single-transaction -u root YOUR_DB > /path/to/YOUR_DB.sql

 

I now use LVM snapshots for backing up/migrating my mysql server.

[matfish]

Thats great thank you but I'm trying to backup a specific table out of the database?

 

Many thanks

[tomfmason]

Thats great thank you but I'm trying to backup a specific table out of the database?

 

Many thanks

 

Yeah just add the table name after the db.

 

Like this:

 

mysqldump --opt -c -e -Q -q --single-transaction -u root YOUR_DB YOUR_TABLE > /path/to/YOUR_TABLE.sql

[matfish]

Thats great - thanks for your help!