Copy mysql database from remote server to local computer

Solution 1:

Assuming the following command works successfully:

mysql -u username -p -h remote.site.com

The syntax for mysqldump is identical, and outputs the database dump to stdout. Redirect the output to a local file on the computer:

mysqldump -u username -p -h remote.site.com DBNAME > backup.sql

Replace DBNAME with the name of the database you'd like to download to your computer.

Solution 2:

Check syntax and execute one command at a time, then verify output.

mysqldump -u remoteusername -p remotepassword -h your.site.com databasename > dump.sql

mysql -u localusername -p localpassword databasename < dump.sql

Once you've matched all passwords, you can use pipe.

Solution 3:

Often our databases are really big and the take time to take dump directly from remote machine to other machine as our friends other have suggested above.

In such cases what you can do is to take the dump on remote machine using MYSQLDUMP Command

MYSQLDUMP -uuser -p --all-databases > file_name.sql

and than transfer that file from remote server to your machine using Linux SCP Command

scp user@remote_ip:~/mysql_dump_file_name.sql ./

Solution 4:

This can have different reasons like:

• You are using an incorrect password
• The MySQL server got an error when trying to resolve the IP address of the client host to a name
• No privileges are granted to the user

You can try one of the following steps:

To reset the password for the remote user by:

SET PASSWORD FOR some_user@ip_addr_of_remote_client=PASSWORD('some_password');

To grant access to the user by:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON YourDB.* TO user@Host IDENTIFIED by 'password';

Hope this helps you, if not then you will have to go through the documentation

Solution 5:

Please check this gist.

https://gist.github.com/ecdundar/789660d830d6d40b6c90

#!/bin/bash

# copymysql.sh

# GENERATED WITH USING ARTUR BODERA S SCRIPT
# Source script at: https://gist.github.com/2215200

MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

REMOTESERVERIP=""
REMOTESERVERUSER=""
REMOTESERVERPASSWORD=""
REMOTECONNECTIONSTR="-h ${REMOTESERVERIP} -u ${REMOTESERVERUSER} --password=${REMOTESERVERPASSWORD} "

LOCALSERVERIP=""
LOCALSERVERUSER=""
LOCALSERVERPASSWORD=""
LOCALCONNECTION="-h ${LOCALSERVERIP} -u ${LOCALSERVERUSER} --password=${LOCALSERVERPASSWORD} "

IGNOREVIEWS=""
MYVIEWS=""
IGNOREDATABASES="select schema_name from information_schema.SCHEMATA where schema_name != 'information_schema' and schema_name != 'mysql' and schema_name != 'performance_schema'  ;"

# GET A LIST OF DATABASES
databases=`$MYSQL $REMOTECONNECTIONSTR -e "${IGNOREDATABASES}" | tr -d "| " | grep -v schema_name`

# COPY ALL TABLES
for db in $databases; do
    # GET LIST OF ITEMS
    views=`$MYSQL $REMOTECONNECTIONSTR --batch -N -e "select table_name from information_schema.tables where table_type='VIEW' and table_schema='$db';"
    IGNOREVIEWS=""
    for view in $views; do
        IGNOREVIEWS=${IGNOREVIEWS}" --ignore-table=$db.$view " 
    done
    echo "TABLES "$db
    $MYSQL $LOCALCONNECTION --batch -N -e "create database $db; "
    $MYSQLDUMP $REMOTECONNECTIONSTR $IGNOREVIEWS --compress --quick --extended-insert  --skip-add-locks --skip-comments --skip-disable-keys --default-character-set=latin1 --skip-triggers --single-transaction  $db | mysql $LOCALCONNECTION  $db 
done

# COPY ALL PROCEDURES
for db in $databases; do
    echo "PROCEDURES "$db
    #PROCEDURES
    $MYSQLDUMP $REMOTECONNECTIONSTR --compress --quick --routines --no-create-info --no-data --no-create-db --skip-opt --skip-triggers $db | \
    sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | mysql $LOCALCONNECTION  $db 
done

# COPY ALL TRIGGERS
for db in $databases; do
    echo "TRIGGERS "$db
    #TRIGGERS
    $MYSQLDUMP $REMOTECONNECTIONSTR  --compress --quick --no-create-info --no-data --no-create-db --skip-opt --triggers $db | \
    sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | mysql $LOCALCONNECTION  $db 
done

# COPY ALL VIEWS
for db in $databases; do
    # GET LIST OF ITEMS
    views=`$MYSQL $REMOTECONNECTIONSTR --batch -N -e "select table_name from information_schema.tables where table_type='VIEW' and table_schema='$db';"`
    MYVIEWS=""
    for view in $views; do
        MYVIEWS=${MYVIEWS}" "$view" " 
    done
    echo "VIEWS "$db    
    if [ -n "$MYVIEWS" ]; then
      #VIEWS
      $MYSQLDUMP $REMOTECONNECTIONSTR --compress --quick -Q -f --no-data --skip-comments --skip-triggers --skip-opt --no-create-db --complete-insert --add-drop-table $db $MYVIEWS | \
      sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g'  | mysql $LOCALCONNECTION  $db  
    fi    
done

echo   "OK!"