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 'replica'@'123.57.39.*' IDENTIFIED BY 'replica@123';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'123.57.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 < replica.sql
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='mysql-bin.000001', 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 = '123.57.38.*',
MASTER_USER = 'replica',
MASTER_PASSWORD = 'replica@123',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 107;
7.finally start slave;
Be don't forget to issue "show slave status\G' to check whether slave runs normally.
