原理:master开启binlog功能,用于记录数据库的操作。slave通过slave的I/O线程请求master的binlog日志的position之后的内容。master收到salve I/O线程的请求,通过master的I/O线程将bin-log文件的内容和position返回给slave线程
slave收到后写入relay-log中继日志,并且开始sql线程,实时监控中继日志有无更新,并解析中继日志中的sql语句,到slave服务器中执行
实现:
1.同步主从数据库
#备份master数据库
[root@localhost backup]# cd /var/lib/mysql
[root@localhost mysql]# tar -czf /backup/mysql.tar.gz *
[root@localhost backup]# scp mysql.tar.gz 192.168.95.134:/root
#在salve复原
[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# tar -czf /back/slave.tar/gz * #备份slave数据
[root@localhost mysql]# tar -xf /root/mysql.tar.gz #在slave还原
#2.修改master和slave配置文件
[root@localhost ~]# vim /etc/my.cnf
#master:
log_bin=mysql-bin
server_id=1
#slave:
server-id=2
#3.查看master binlog文件名和位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000061 | 154 | | | a2eb11e0-1b11-11ee-b7e9-000c290c6dd2:1-11 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
#4.在master授权用户
mysql> create user salve1@192.168.95.134 identified by 'wudaohao';
mysql> grant replication slave on *.* to slave1@192.168.95.134;
#5.设置主从同步
mysql> change master to
-> master_host='192.168.95.132',
-> master_user='slave1',
-> master_password="wudaohao",
-> master_log_file='mysql-bin.000061',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G #显示连接成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#6.测试
master增加一个数据库:
mysql> create database uu;
slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| booksDB |
| db |
| db2 |
| db3 |
| db5 |
| market |
| mysql |
| performance_schema |
| sys |
| team |
| uu |
+--------------------+