Restore Mysql Database by Physical files

Restore Mysql Database by Physical files

When you want to move whole database schema and data, then you

Follows the following steps

Step 1 : Stop the database on server you want to move database.

Step 2 : Go to the directory where mysql data are ie data dir

Step 3 : Transfer over the folder (and its contents) over to the new server's mysql data directory

Step 4 : Start back up the database. And then start the mysql server

Step 5 : On the new server, issue a 'create database' command.'

Step 6 : Re-create the users & grant permissions.

Or

For existing user from old server

grant all privileges on *.* to root@'%' identified by 'admin123';

flush privileges;

or

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION; 

If you want to move database with replication then you have to move physical files

From master to new server 1 and replica to new replication server 2

Check all is working fine, then you have to configure

Replication for master to slave on new servers

The second way to restore the database to new server is pipe a mysqldump command to a sql command, You can do all databases or a specific one. So, for instance,

mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserver

You can do all databases with

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver 

If you just want to move a specific table try:

mysqldump -u username -ppassword databasename tablename > databasename.tablename.sql

You can specify more table names above, in the same command. Once the command completes, move the databasename.tablename.sql file to the other server and then restore using:

mysql -u username -ppassword databasename < databasename.tablename.sql