Regarding Backup

Hello Guys

Please , Can you advice me the best approach for 2 things ?

1 ) mysqldump and mysqlbackup : What is the best option to schedule it using cron without password exposition ?

  1. I have a database handling stream solution. Basically I have a table with huge transactions with a lot of Inserts and deletes . Can you support me how can I perform a analyze , optimize in this tables without long locks ? I think in mysqlcheck, however I am not sure about it

Version OS : Red Hat 7 and mysql 5.7 ( I am aware that it is out of date )


Hi Andre, instead of mysqldump I suggest you use mydumper, which can work in parallel so is much faster: GitHub - mydumper/mydumper: Official MyDumper project

You can create a defaults file (e.g. .mylogin.cnf) and then have mydumper use it, that way you avoid putting the password inside the cron file.

For your second question, you can do analyze with only a quick lock, but if you need to optimize and rebuild the table, you’ll have to do it with pt-online-schema-change to avoid problems. Another alternative is to perform the procedure on a read replica, then promote it as primary.

Hope that helps

Thanks Ivan! I wll try this one.