I'm looking for recommendations on the best way to copy data between locate and live deployments. I've just installed MYSQL work bench but can't see how to copy data from local to remote. Also in the MSSQL world there are useful tools like Data Compare that help with synchronisation. Is there a good quality equivalent in the mysql world.
Along with MySQL dump there are tools like sqlyog that allow you to sync (not just import) both the schema and the data. I use this at when some of our OTE databases get out of sync with production.
mysqldump is a truly fantastic tool. It simply generates a script of all the create table statements along with insert statements for all the data. From memory it also generates the table create scripts in the same order (unlike SQL Server) - so you can use a simple side-by-side text comparison program such as Araxis Merge to compare the differences.
I would recommend using MYSQLDUMP. It's incredibly powerful and customizable. Here's a good link on how to perform a dump & move (otherwise known as a copy): LINK