Mysql dual-machine Hot Standby Master/Slave configuration, mysql dual-machine Hot Standby Master/Slave Configuration
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 '@ '192. 57. 39. * 'identified BY 'replace @ 100 ';
Grant replication slave on *. * TO 'replica '@ '2017. 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 = fadfafad -- single-transaction -- all-databases> replica. SQL
5. copy the replica. SQL to slave server, then import data in slave.
Mysql -- user = root -- password = fadfafad <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
MASTER_HOST = '2017. 57. 38 .*',
MASTER_USER = 'replace ICA ',
MASTER_PASSWORD = 'replace @ 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.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.