Copy mysql database from remote server to local computer

Question

I m under VPN and I don t have SSH access to remote server   I can connect to remote database by console  mysql -u username -p -h remote site com   Now I m trying to clone the remote database to local computer  mysqldump -u username -p -h remote site com mysqldump   mysql -u root -ppassword webstuff   And I ve got error   mysqldump  Got error  1045  Access denied for user  webstaff   10 75 1 2    using password  YES  when trying to connect   How to copy mysql database from remote server to local computer

User · Answer

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

User · Answer

Check syntax and execute one command at a time  then verify output   mysqldump -u remoteusername -p remotepassword -h your site com databasename  gt  dump sql  mysql -u localusername -p localpassword databasename  lt  dump sql   Once you ve matched all passwords  you can use pipe

User · Answer

C  Users  gt mysqldump -u root -p -h ip address --databases database name -r sql file sql Enter password  your password

User · Answer

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

User · Answer

Copy mysql database from remote server to local computer   I ran into the same problem  And I could not get it done with the other answers  So here is how I finally did it  yes  a beginner tutorial    Step 1  Create a new database in your local phpmyadmin    Step 2  Dump the database on the remote server into a sql file  here I used Putty SSH    mysqldump --host  mysql5 domain com  --user  db231231  --password  DBPASSWORD  databasename  gt  dbdump sql   Step 3  Download the dbdump sql file via FTP client  should be located in the root folder   Step 4  Move the sql file to the folder of your localhost installation  where mysql exe is located  I am using uniform-server  this would be at C  uniserver core mysql bin   with XAMPP it would be C  xampp mysql bin  Step 5  Execute the mysql exe as follows    mysql exe -u root -pYOURPASSWORD YOURLOCALDBNAME  lt  dbdump sql   Step 6  Wait    depending on the file size  You can check the progress in phpmyadmin  seeing newly created tables   Step 7  Done  Go to your local phpmyadmin to check if the database has been filled with the entire data   Hope that helps  Good luck     Note 1  When starting the uniformer-server you can specify a password for mysql  This is the one you have to use above for YOURPASSWORD   Note 2  If the login does not work and you run into password problems  check your password if it contains special characters like    If so  then you probably need to escape them      Note 3  In case not all mysql data can be found in the local db after the import  it could be that there is a problem with the mysql directives of your dbdump sql

User · Answer

Better yet use a oneliner   Dump remoteDB to localDB   mysqldump -uroot -pMypsw -h remoteHost remoteDB   mysql -u root -pMypsw localDB   Dump localDB to remoteDB   mysqldump -uroot -pmyPsw localDB   mysql -uroot -pMypsw -h remoteHost remoteDB

User · Answer

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

User · Answer

This answer is not remote server but local server  The logic should be the same  To copy and backup my local machine MAMP database to my local desktop machine folder  go to console then  mysqldump -h YourHostName -u YourUserNameHere -p YourDataBaseNameHere  gt  DestinationPath xxxwhatever sql   In my case YourHostName was localhost  DestinationPath is the path to the download  you can drag and drop your desired destination folder and it will paste the path in   Then password may be asked   Enter password  xxxxxxxx

User · Answer

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  gt  backup sql   Replace DBNAME with the name of the database you d like to download to your computer

[mysql] Copy mysql database from remote server to local computer

Examples related to mysql