Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration).
I want to replicate every bit of data on production server to new server keeping permissions, users and privileges intact. I just learned about the mysqldump -u root -p databasename > file.sql
command but I am not sure how it is going to affect the permissions and users.
Please suggest what is the best way to replicate database in Mysql by keeping the integrity of the data. Help is greatly appreciated.
Best Answer
If you are comfortable with a maintenance window, here is what you can do:
Step 01) You need to add this line to my.cnf on Server1
Step 02) Restart mysql so that no one else can login to Server1
Step 03) Dump everything and gzip it
Step 04) Restart mysql on Server1 to allow normal connectivity
Step 05) Move the ZIP file to the new server (Server2) in /root
Step 06) Unzip the file on Server2
Step 07) Login into mysql on Server2
Step 08) Load the data from the mysql prompt on Server2
That's it. That makes all the data as of that dump available on Server2
If you wanted to establish MySQL Replication so that everything written on the Master immediately gets done on the Slave, the steps are a little different. For this example, you will need the Private IP address of Server1. You can get that with
ip addr show
. Let's suppose the Private IP address of Server1 is 10.1.2.20.Step 01) You need to add this line to my.cnf on Server1
Step 02) Restart mysql so that no one else can login to Server1
Step 03) Create a Replication Username in mysql client:
Step 04) Dump everything and gzip it
Step 05) Restart mysql on Server1 to allow normal connectivity
Step 06) Move the ZIP file to the new server (Server2) in /root
Step 07) Unzip the file on Server2
Step 08) Login into mysql on Server2
Step 09) Setup replication in mysql client:
Don't worry about log file and position. They are bogus. The mysqldump has the correct log file and position at line 22. In order to see run this:
Step 10) Load the data from the mysql prompt on Server2
Step 11) Start replication
If
Slave_IO_Running=Yes
andSlave_SQL_Running=Yes
, you are done.