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 '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.