0

I need to periodically copy the outcome of a view on our local database to another remote database.

I've tried using mysqldump which works fine when copying data from tables to tables, but i need to copy the data from either a view or a stored procedure.

As an example I've created a sourcedb and a destinationdb. I need to copy data from the view in sourcedb and append the data in a table at the destinationdb.

Is that possible?

Mysqldump:
mysqldump --skip-add-drop-table -uroot -p*** sourcedb sourceview | sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' | mysql -P3306 -uroot -p*** destinationdb

Error:
ERROR 1146 (42S02) at line 41: Table 'destinationdb.table1' doesn't exist

5
  • 4
    select ... into outfile ...
    – danblack
    Oct 26 '21 at 11:23
  • The process needs to be automated. I need to pull the data from my database and insert it into the remote database in one go. I'm writing a small C# program to handle the remote connection and datatransmission , but i need to find a good way to get the content of a view or a stored procedure. Oct 27 '21 at 5:51
  • 1
    which database versions?
    – danblack
    Oct 27 '21 at 6:55
  • MariaDB 10.3.14 Oct 28 '21 at 7:20
  • Consider connect storage engine on the source and treat the created connect engine table as the destination. Also "or a stored procedure" in your question, how do you consider a stored procedure a source?
    – danblack
    Oct 28 '21 at 8:00
0

For much more flexibility, you may consider to use mysqldump --tab option.

According to your error, please make sure that the table is exists in the destination database at remote machine. If it's not, create it first.

Here is some simple steps :

  1. Prepare new fresh directory for local files and make sure it accessable by mysqldump

rm -rf /local-backup; mkdir -p /local-backup/today; chmod 1777 /local-backup/today /local-backup; cd /local-backup

  1. Actual mysqldump and get tab separated file

mysqldump adb atable --tab="today"

  1. upload the file generated to remote server. you may use scp or rsync

  2. at remote server, execute:

LOAD DATA CONCURRENT INFILE '/fullpath/of_the_file' IGNORE INTO TABLE adb.atable;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.