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