# MySQL backup and restoration [solved]

## Bigun

If I understand correctly, to do  MySQL backup this is what I should do:

```

mysqldump -A -u main --password=******  > mysqlbackup.dat

```

Is that correct?

Second question is how do I restore that backup to another machine?

----------

## steveb

Backup:

```
mysqldump \

  --user=root \

  --password='your_password' \

  --host=localhost \

  --all-databases \

  --opt \

  --allow-keywords \

  --flush-logs \

  --hex-blob \

  --master-data \

  --max_allowed_packet=16M \

  --quote-names \

  --result-file=mysqlbackup.sql
```

Restore:

```
cat mysqlbackup.sql \

  | mysql \

  --user=root \

  --password='your_password' \

  --host=localhost \

  --max_allowed_packet=16M
```

cheers

SteveB

----------

## Bigun

Oh my...

The script above is how I did my backup (mind you I only have access to my username, not root), will that still work?

----------

## steveb

 *bigun89 wrote:*   

> The script above is how I did my backup (mind you I only have access to my username, not root), will that still work?

 It is not important what user name you use. mysqldump will just dump all data the user has access to. With root you are sure to get everything you need. If you use another user, then you may not export all data (depends on the rights you have).

cheers

SteveB

----------

## Bigun

 *steveb wrote:*   

>  *bigun89 wrote:*   The script above is how I did my backup (mind you I only have access to my username, not root), will that still work? It is not important what user name you use. mysqldump will just dump all data the user has access to. With root you are sure to get everything you need. If you use another user, then you may not export all data (depends on the rights you have).
> 
> cheers
> 
> SteveB

 

I just needed what my user had access to, but just as well... tunnelling the output with ">" is just the same as using "--result-file", yes?

----------

## steveb

 *bigun89 wrote:*   

> I just needed what my user had access to, but just as well... tunnelling the output with ">" is just the same as using "--result-file", yes?

 Yes

----------

## Bigun

*breaths sigh of relief*

Thank you

----------

## steveb

No problem.

cheers

SteveB

----------

## geforce

 *steveb wrote:*   

> 
> 
>   --max_allowed_packet=16M \

 

Does it mean than the mysqlbackup.sql cannot be larger than 16M ?

----------

## steveb

 *geforce wrote:*   

>  *steveb wrote:*   
> 
>   --max_allowed_packet=16M \ 
> 
> Does it mean than the mysqlbackup.sql cannot be larger than 16M ?

 No!

----------

## Bigun

I think that merely limits the chunks of data sent to MySQL at a time.

----------

## geforce

Okay, thanks !

----------

## kashani

You should be aware that if you have done your backups with --max_allowed_packet=16M and your server is set to --max_allowed_packet=8M it'll error out. You'll need to change the server to use --max_allowed_packet=16M before you can dump in. Basically you just need to make sure that you're using the same --max_allowed_packet setting everywhere.

kashani

----------

## geforce

Thanks for the tip, 

I looked in Mysql config and luckyly it was 16M too.

Phil

----------

