I have a set of MYSQL tables on the development server that I need to place on the production server. How can I "recreate" all of them?
There are triggers present as well (I think). I need to recreate everything.
+2
A:
Use mysqldump to create a dump file that you can feed to mysql on the target server.
To make sure triggers get exported too, use the --triggers option. (Although I think those are included by default.)
To make sure stored procedures get exported too, use the --routines option. Note that (emphasis mine):
This option was added in MySQL 5.1.2. Before that, stored routines are not dumped. Routine DEFINER values are not dumped until MySQL 5.1.8. This means that before 5.1.8, when routines are reloaded, they will be created with the definer set to the reloading user. If you require routines to be re-created with their original definer, dump and load the contents of the mysql.proc table directly as described earlier.
Pekka
2010-09-09 13:32:04
Is there any way to get this data via myphpadmin?
tzmatt7447
2010-09-09 15:30:03
@tzmatt hmm, I have no idea whether phpMyAdmin already dumps triggers and stored procs. My guess would be it doesn't - you'd have to check or try out
Pekka
2010-09-09 15:32:45
@tzmatt it seems to do triggers according to this: http://wiki.phpmyadmin.net/pma/phpMyAdmin_2.11.4
Pekka
2010-09-09 15:33:33
I ultimately used the export feature which pMA had and it _seems_ to have worked fine...
tzmatt7447
2010-09-10 09:40:17
A:
Use mysqldump (documentation located here). If you do not specify tables it assumes all tables. You can also explicitly choose tables to copy or to ignore. You can tell it to create drop statements before your create statements. It takes cares of triggers but I forget if it takes care of routines, you'll have to take a look.
stocherilac
2010-09-16 12:42:34