Friday, 18 February 2011

backing up your mamp mysql databases with mysqldump

BACKUP via:
mysqldump -u root -p venngo_db --routines > venngo_db.sql
mysqldump -u root -p venngo_analytics —-routines > venngo_analytics.sql
mysqldump -u root -p venngo_newsletter —-routines > venngo_newsletter.sql

RESTORE via:
mysql -u root -p venngo_db < venngo_db.sql
mysql -u root -p venngo_analytics < venngo_analytics.sql
mysql -u root -p venngo_newsletter < venngo_newsletter.sql

during import of new .sql files you may need to increase your mysql memory via:

mysql -u root -p

set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;

UPDATE: sometimes MAMP version of mysql wont have enough memory to import large databases. here's how to increase its max packet size in MAMP (not pro)
The MySQL server of MAMP (not PRO) will be started without any my.cnf file. But you can create your own my.cnf file.
Stop servers
Create a "my.cnf" file in "/Applications/MAMP/conf/".
Add your content in to "my.cnf"
Save "my.cnf"
Start servers
You do not have to put a complete configuration in the my.cnf file. You can just add parts of a configuration ... for example:
[mysqld]
max_allowed_packet = 64M

No comments:

Post a Comment