While you can easily copy databases using the mysqldump command, and restore them with no problems, often times the user permissions are a problem. You may have created a bunch of users, maybe from different addresses, with different permissions, and moving them over one at a time can be a chore.
So, you can bang these commands into a file, and run it. This *ASSUMES* you have root access (no password) set up, and you're on the MySQL local host where your database is. If you're not, add the appropriate host flag, and specify whatever root-level user you have set up.
Code:
mysql -B -N -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user where user!='';" mysql > mysqlusers.txt
while read line; do mysql -B -N -e "SHOW GRANTS FOR $line"; done < mysqlusers.txt > mysql-user-permissions.sql
sed -i 's/$/;/' mysql-user-permissions.sql
This will give you a .sql file, with all your grants/passwords intact. Running this on the new server (again, modify user/host as needed)
Code:
mysql -u root -p < mysql-user-permissions.sql
...imports the file you just created. Done.
