The following command will use mysqldump to create a dump of all available databases in the OLD_HOST that are available for the user OLD_USER.
The results will be imported to another server via the mysql pipe.
OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW"; mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" --all-databases | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";
The user must have the LOCK TABLES privilege for the above command to work or else you will get the following error.
mysqldump: Got error: 1044: "Access denied for user 'OLD_USER'@'OLD_HOST' to database 'DBNAME'" when using LOCK TABLES
In case you cannot give the privilege to the user, then use the parameter --single-transaction to mitigate the problem. The command changes as follows.
OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW"; mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" --single-transaction --all-databases | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";
In case you want to copy only specific databases, use the following
OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; OLD_DBS=("DB1" "DB2");
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" "${OLD_DBS[@]}" | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS";
In case you want to copy only specific tables from a database, use the following
OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; OLD_DB="DB1"; OLD_TABLES=("TBL1" "TBL2");
NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW"; NEW_DB="NewDB";
mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" "$OLD_DB" "${OLD_TABLES[@]}" | mysql -h "$NEW_HOST" -u "$NEW_USER" -p"$NEW_PASS" "$NEW_DB";
This post is also available in: Greek
