MySQL database - backup problem

tags:

backup
mysql

views:

answers:

+2 Q:

MySQL database - backup problem

Hi I need to backup MySQL database and then deploy it on another MySQL server.

The problem is, I need it backup without data , just script which creates database, tables, procedures, users, resets autoincrements etc. ...

I tried MySQL administrator tool (Windows) and UNchecked "complete inserts check box", but it still created it ... Thanks in advance

+2 A:

use mysqldump with option -d or --no-data
don't forget option -R to get the procedures
this page could help you: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

PierrOz 2010-03-25 21:38:42

I triedmysqldump -u root -p -d -R mydb > myback.sql but it left stored procedures in comments

Mike 2010-03-25 22:17:07

I have not looked at it, but mysqldump uses comments in the form /* xxxxx .... */ where xxxx is a MySQL version. If the server you load this dump into is the same or newer than the one you took the backup from, this comment will be executed as regular statements. This is used to preserve backward compatibility.

Daniel Schneller 2010-03-25 22:48:25

@Mike: actually they are "half" in comments: /*! */ will be interpreted by MySQL (see http://dev.mysql.com/doc/refman/5.1/en/comments.html) but will be ignored by other SQL servers. If you want to export your schema and procedures into another server, I recommend you to version them in sql files as soon as you build them and maintain them with every change you do on your database

PierrOz 2010-03-26 08:17:57

@PierrOz thanx for explanation :-)

Mike 2010-03-26 08:29:42

@Mike: you're welcome :)

PierrOz 2010-03-26 09:22:19

According to the page, there isn't a good way to dump the routines and have them easily able to be recreated.

What they suggest is to dump the mysql.proc table directly. Including all the data.

Then use your myback.sql to restore the structure. Then restore the mysql.proc table with all of its data.

"... If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database. ..."

Harold 2010-03-25 22:44:04

If I understand, firstly I should execute SQL file with creating database and secondly create procedures from separate file to already created database.Correct?

Mike 2010-03-25 22:52:39

+1 A:

From within phpMyAdmin you can export the structure, with or without the data. ~~The only thing I'm not sure of, is wether it exports users as well. If you like, I can test that tomorrow morning.~~ It exports users too. You can check all sorts of options.

alt text

Marga Keuvelaar 2010-03-25 22:51:56

It would be very kind of youI am going to deploy it today evenening, so if you will be faster that'll be sweetthanx for now

Mike 2010-03-26 08:29:06

I tested it yesterday evening (was too curious), it does export users, permissions, etc. Basically anything you need to replicate your database, with or without data - your choice. The output is a script, with all the statements in it, in the right order.

Marga Keuvelaar 2010-03-26 08:34:52

that is just sweet :)

Mike 2010-03-26 08:55:39

ansaurus

tags:

views:

answers:

MySQL database - backup problem

related questions