When migrating from one server to another, migrating the sql database is usually advantageous. There are two ways to do this, dumping the old database to a file(s) and re-importing that file to the new server, or, copy the database files themselves over to the new server (/var/lib/mysql) making sure they are owned by the new mysql user!
If you perform the first method for migration/backup, it may be advantageous to also back up the mysql users you’ve created and granted access to certain db’s for. This is because many people have their application just use a specific account that only has the access its needs to the given database.
To do this, the easiest way is to make a .sql file which you can easily import into the new mysql installation:
mysql -uUSERNAME -pMYPASSWORD -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | while read uh; do mysql -uROOT -pMYPASSWORD -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql
The above does just that, creates a grants.sql file you can use to import into the new mysql install using the mysql command (hint: mysql -uroot -pderp Ensure that you run a “FLUSH PRIVILEGES;” after these grant statements to apply the new users!!!
Thanks stackoverflow peoples:)
