Backup and restore all MySQL databases

[BlackThorn]

Hello. I'd like to reset my.cnf, because I've used "innodb_file_per_table = 1". So I'd like to get backup all databases first. After that, I'll drop them all and reset my.cnf, delete ibdata1 and ib_log* files, and upload/restore all databases again via SSH. Is there any specific method to restore all databases at one time without user name and password? I am asking because there are a lot of sites (I have more than 80 MySQL databases) and I don't want to deal with user names and passwords one by one. Meanwhile, where can I find default my.cnf file? Thanks.

 

[floyd]

https://help.directadmin.com/item.php?id=678

 

[BlackThorn]

@floyd well, I already knew about it, but I just wanted to be sure I had the right answer. Also, what about my.cnf?

 

[floyd]

You create the my.cnf file based on your needs.

 

[BlackThorn]

@floyd thanks but would you tell me how to drop all databases? Is there a SSH-command to do it?

 

[floyd]

I guess I could Google it for you but you can do that I think. I don't have any of this stuff memorized.

 

[Richard G]

Fast method.
Move /var/lib/mysql to somewhere else and reinstall mysql/mariadb again.

 

[Zhenyapan]

also if you want to have independent backups - better to create them via Directadmin's - Admin Backup Transfer just select:
Database Settings
Database Data
----
then you will be able to restore this backups via DA even if mysql (users) table will be broken..