Mysql 主从同步

原理参考资料

MySQL数据库主从复制原理：

Mysql主从同步其实是一个异步复制的过程，要实现复制首先需要在master上开启bin-log日志功能，整个过程需要开启三个线程：

分别是master的IO线程，slave开启IO线程和SQL线程。

1).在从服务器上执行slave start，从服务器上的IO线程会通过授权用户连接上master，并请求master从指定的文件和位置之后发送bin-log日志内容。

2).master服务器收到来自slave服务器的IO线程的请求后，master服务器上的IO线程根据slave服务器发送的指定的bin-log日志之后的内容，然后返回给slave端的IO线程。

(返回的信息中除了bin-log日志内容外,还有本次返回日志内容后在master服务端的新的binlog文件名以及在binlog中的下一个指定更新位置)

3).slave的Io线程接收到信息后,将接收到的日志内容依次添加到slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中

以便在下一次读取的时候能够清楚的告诉Master“需要从哪个位置之后的日志内容”

4).slave的SQL线程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为Master端真实执行时候的那些可执行内容，并在自身执行。

5).SQL线程执行完毕，完成数据同步。

1、配置主mysql

创建数据库

mysql> use MS;
mysql> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| age   | int(11)     | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql 启动二进制日志，并设置同步数据库

[[e-mail] ~]# vim /etc/my.cnf
。。。。。。
# 启动二进制日志
log-bin=mysql-bin-master
# 本地数据库ID
server-id=1
# 同步数据库名
binlog-do-db=MS
# 去除mysql数据库
binlog-ignore-db=mysql

重启mysqld

[[e-mail] ~]# systemctl restart mysqld

授权到指定slave mysql IP ,也可以指定网段

mysql> grant replication slave on *.* to [e-mail]‘192.168.0.%’ identified by "123456";

查看主mysql 状态信息：

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000001 |      154 | MS           | mysql            |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

导出数据库MS，再导入到从服务器中

[[e-mail] ~]# mysqldump -uroot -p MS >MS.sql
Enter password:
[[e-mail] ~]# ls -l
total 36
-rw-r--r--. 1 root root  1850 Mar 16 19:43 MS.sql

重置mster 命令

mysql>  reset master;
Query OK, 0 rows affected (0.01 sec)

2、配置从服务器

安装好相同版本的mysql后，导入数据库；

mysql> create database MS;
Query OK, 1 row affected (0.00 sec)

mysql> use MS
Database changed
mysql> source /root/MS.sql;

配置my.cnf,重启mysqld

[[e-mail] ~]# vim /etc/my.cnf
# 从服务器id
server-id=2

[[e-mail] ~]# systemctl restart mysqld

关闭主服务器和从服务器防火墙

[[e-mail] ~]# systemctl stop firewalld.service

mysql 配置slave

指定主服务器ip 地址

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  change master to master_host='192.168.0.140',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看是否正常连接主服务器

mysql> show slave status\G;
。。。。。。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主服务器查看状态

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 10
   User: slave
   Host: 192.168.0.141:41644
     db: NULL
Command: Binlog Dump
   Time: 50
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 11
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
2 rows in set (0.00 sec)

3、测试

主服务器插入数据

mysql> insert into person (name,age)values('max',13);
Query OK, 1 row affected (0.07 sec)
mysql> insert into person values('linda',13);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into person (name,age)values('test2',14);
Query OK, 1 row affected (0.00 sec)
mysql> select * from person;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | bob   |  12 |
|  2 | max   |  13 |
|  3 | test2 |  14 |
+----+-------+-----+
3 rows in set (0.00 sec)

从服务器查看同步数据

mysql> select * from person;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | bob   |  12 |
|  2 | max   |  13 |
|  3 | test2 |  14 |
+----+-------+-----+
3 rows in set (0.00 sec)