MySQL主从实时备份

1 主A从B电脑ip
A:192.168.1.111
B:192.168.1.112


2 给主A从B电脑创建相同数据库


3 授权（主从都需要）
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH  PRIVILEGES;


grant file on *.* to 'root'@'192.168.1.112' identified by 'root' with grant option;
grant all on backup.* to 'root'@'192.168.1.112' identified by 'root' with grant option;
grant all on backup.* to 'root'@'localhost' identified by 'root' with grant option;
grant replication slave on *.* to 'root'@'192.168.1.112' identified by 'root' with grant option;
flush privileges;


4 my.ini配置
主
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
server-id=200
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
从
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
server-id=210
replicate-do-db=dbname


5 重启两台机器的mysql服务


6 在从数据库执行下命令，注意还原点参数。
mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> change master to master_user='root',master_password='root',master_host='192.168.1.111',master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=120;
mysql> start slave;


7 查看状态
show slave status;
show master status;

show processlist;