基于二进制日志复制

主从复制

Master

1：启用二进制日志和设置唯一ID
#vi /etc/my.cnf    
[mysqld]
bind-address=0.0.0.0
server-id=1
lob-bin=mysql-bin


2：创建复制账户
mysql> create user 'repl'@'%' identified by 'www.c1688.CC';
mysql> grant replication slave,REPLICATION CLIENT on *.* to 'repl'@'%';

可以用一个语句创建和赋予权限
mysql> grant replication slave,REPLICATION CLIENT on *.* to 'repl' identified by 'www.c1688.CC';

3:获取master二进制日志文件和位置
mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000023 |     3185 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+


Slave
1：启用server-id
#vi /usr/local/mysql/my.cnf
[mysqld]
server-id=2
lob-bin=mysql-bin     #slave可以不启用，启用是为了恢复数据


2：配置复制连接
mysql> change master to master_host='192.168.31.3', master_user='repl',master_password='www.c1688.CC',master_log_file='mysql-bin.000023',master_log_pos=3185;


3：启动线程
mysql> start slave;

4：查看状态
mysql> show slave status\G;




可以在master查看连接
mysql> show processlist;




清空原有复制环境
slave从停止线程
mysql> stop slave;

清空配置文件
mysql> reset slave;