How do I replicate Mysql DB server to new server?

Question

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.

• Konerak: mysqldump will work, and the users are stored in tables too, so the users will be copied with. You will need to execute FLUSH PRIVILEGES on the new server so it re-reads the user tables. Also, mysqldump has a few options (which are off by default) to dump stuff like triggers... if you forget those, your new database won't have them.

Answer 1

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

[mysqld]
log-bin-mysql-bin
expire-logs-days=7

Step 02) Restart mysql so that no one else can login to Server1

service mysql restart --skip-networking

Step 03) Dump everything and gzip it

DUMPFILE=/root/MySQLData.sql.gz
mysqldump --master-data=2 --single-transaction --flush-privileges -uroot -p --routines --triggers --all-databases | gzip > ${DUMPFILE}

Step 04) Restart mysql on Server1 to allow normal connectivity

service mysql restart

Step 05) Move the ZIP file to the new server (Server2) in /root

Step 06) Unzip the file on Server2

gzip -d /root/MySQLData.sql

Step 07) Login into mysql on Server2

mysql -uroot -p

Step 08) Load the data from the mysql prompt on Server2

mysql> source /root/MySQLData.sql

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

[mysqld]
log-bin-mysql-bin
expire-logs-days=7

Step 02) Restart mysql so that no one else can login to Server1

service mysql restart --skip-networking

Step 03) Create a Replication Username in mysql client:

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repluser@'10.1.2.20' IDENTIFIED BY 'replpass';

Step 04) Dump everything and gzip it

DUMPFILE=/root/MySQLData.sql.gz
mysqldump --master-data=1 --single-transaction --flush-privileges -uroot -p --routines --triggers --all-databases | gzip > ${DUMPFILE}

Step 05) Restart mysql on Server1 to allow normal connectivity

service mysql restart

Step 06) Move the ZIP file to the new server (Server2) in /root

Step 07) Unzip the file on Server2

gzip -d /root/MySQLData.sql

Step 08) Login into mysql on Server2

mysql -uroot -p

Step 09) Setup replication in mysql client:

CHANGE MASTER TO
MASTER_HOST='10.1.2.20',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000000',
MASTER_LOG_POS=2;

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:

head -22 source /root/MySQLData.sql | tail -1

Step 10) Load the data from the mysql prompt on Server2

mysql> source /root/MySQLData.sql

Step 11) Start replication

START SLAVE; SELECT SLEEP(3); SHOW SLAVE STATUS\G

If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, you are done.

• user53864: Great answer!, I'm also waiting for such an answer here-->dba.stackexchange.com/questions/11023/…