database-backups - w3toppers.com

Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)

Assuming by full dump you also mean the VIEWs and the EVENTs, you would need: GRANT USAGE ON *.* TO ‘dump’@’%’ IDENTIFIED BY …; GRANT SELECT, LOCK TABLES ON `mysql`.* TO ‘dump’@’%’; GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `myschema`.* TO ‘dump’@’%’; and if you have VIEWs that execute a function, then unfortunately … Read more

Export and Import all MySQL databases at one time

Export: mysqldump -u root -p –all-databases > alldb.sql Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments: mysqldump -u root -p –opt –all-databases > alldb.sql mysqldump -u root -p –all-databases –skip-lock-tables > alldb.sql Import: mysql -u root -p < alldb.sql