Mysql双机热备主从配置_MySQL

replication on mysql:
Master/Slave.
Master server is the production MySQL instance.
Slave server is the java host (123.57.39.*).
First we need to install MySQL server and client on the java host(123.57.39.*).


1.in master execute:
CREATE USER &＃39;replica&＃39;@&＃39;123.57.39.*&＃39; IDENTIFIED BY &＃39;replica@123&＃39;;
GRANT REPLICATION SLAVE ON *.* TO &＃39;replica&＃39;@&＃39;123.57.39.*&＃39;;
flush privileges;


2.
add the following to [mysqld] segment in /etc/my.cnf of master DB. And make sure mysql user is the owner of log-bin direcotry. Otherwise you
will get error when you startup MySQL.
server-id=1
log-bin=/var/lib/mysql/binlog/mysql-bin
expire_logs_days=7


3.
On Slave,add the following to [mysqld] segment in /etc/my.cnf
server-id=2
innodb_flush_log_at_trx_commit=2 //this line is not required,just improve innodb performance
expire_logs_days=7


4.in master,then copy data to slave
mysqldump --master-data=2 --user=root --password=fadfafafaD --single-transaction --all-databases > replica.sql


5.copy the replica.sql to slave server,then import data in slave.
mysql --user=root --password=fadfafafaD

6.change master settting on slave;view the privious replica.sql,using "more replica.sql",you will see some text like the follwing,
-- CHANGE MASTER TO MASTER_LOG_FILE=&＃39;mysql-bin.000001&＃39;, MASTER_LOG_POS=107;
record the red font part,then fill them into the following statement,then execute the following statement in slave mysql server.


CHANGE MASTER TO
MASTER_HOST = &＃39;123.57.38.*&＃39;,
MASTER_USER = &＃39;replica&＃39;,
MASTER_PASSWORD = &＃39;replica@123&＃39;,
MASTER_LOG_FILE = &＃39;mysql-bin.000001&＃39;,
MASTER_LOG_POS = 107;




7.finally start slave;
Be don&＃39;t forget to issue "show slave status\G&＃39; to check whether slave runs normally.