mysql主从复制

原理：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                 |
+--------------------+