mysql主从复制配置教程

mysql主从的优势:
1.主从复制->读写分离;可以缓解主数据库的压力
2.备份,避免了主服务器因为故障数据丢失问题

mysql 数据库复制操作大致可以分成3个步骤:

1. 主服务器将数据的改变记录到二进制日志(binary log)中,
2. 从服务器将主服务器的binary log events 复制到它的中继日志(relay log)中
3. 从服务器重做中继日志中的事件,将数据的改变与从服务器保持同步

• 详细说明:
• 从服务器先通过IO线程(I/O thread)发送读取请求到主服务器
• 主服务器通过 log dump thread 线程读取到binary log event信息发给从服务器,写入中继日志中
• 从服务器通过sql线程( SQL thread )读取中继日志写入数据库
• 所以整个过程涉及到了3个线程的操作
  

mysql主从复制的搭建过程

1. 确保从数据库与主数据库里的数据一致
2. 在主数据库里创建一个同步账户授权给从数据库使用
3. 配置主数据库（修改配置文件）与开启binlog日志
4. 配置从数据库（修改配置文件）
5. 配置从节点
   下面开始配置:
   1.Mater 节点配置

角色	IP	操作系统	mysql版本	端口	复制账号	密码
主Master	192.168.203.144	CentOS7.6.1810	mysql8.0.19	3306	slave	slave
从Slave	192.168.203.133	CentOS7.6.1810	mysql8.0.19	3306	-	-

1.1 创建账号

#创建账号
CREATE USER 'slave'@'192.168.203.%' IDENTIFIED WITH mysql_native_password BY 'slave';
#授权
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.203.%';

2.开启Master的binary log配置

#在my.cnf中添加配置
vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id = 1

在mysql中查看二进制相关配置

mysql> show global variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+
#查看当前使用的binlog 记住当前的 mysql-bin.000004  770
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      770 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#查看日志
mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------+
| mysql-bin.000004 |   4 | Format_desc    |         4 |         125 | Server ver: 8.0.20, Binlog ver: 4               |
| mysql-bin.000004 | 125 | Previous_gtids |         4 |         156 |                                                 |
| mysql-bin.000004 | 156 | Anonymous_Gtid |         1 |         242 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'            |
| mysql-bin.000004 | 242 | Query          |         1 |         322 | BEGIN                                           |
| mysql-bin.000004 | 322 | Query          |         1 |         433 | use `bin`; INSERT INTO t VALUES(2, 'shineyork') |
| mysql-bin.000004 | 433 | Xid            |         1 |         464 | COMMIT /* xid=9 */                              |
| mysql-bin.000004 | 464 | Anonymous_Gtid |         1 |         550 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'            |
| mysql-bin.000004 | 550 | Query          |         1 |         630 | BEGIN                                           |
| mysql-bin.000004 | 630 | Query          |         1 |         739 | use `bin`; INSERT INTO t VALUES(3, 'xiaohua')   |
| mysql-bin.000004 | 739 | Xid            |         1 |         770 | COMMIT /* xid=16 */                             |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------+
10 rows in set (0.02 sec)

其他相关命令

-- 查看所有二进制文件信息
show binary logs;
-- 查看最新二进制文件
show master status;
-- 刷新日志
flush logs;
-- 清空所有的日志文件
reset master

2.Slave节点配置
2.1 修改从节点mysql配置

#/etc/my.cnf添加一下配置
server-id = 2
relay_log = /usr/local/mysql/data/mysql-relay-bin
relay_log-index = /usr/local/mysql/data/mysql-relay-bin.index
log_slave_updates = 1
read_only = 1
# 修改完配置之后重启mysql
[root@localhost ~] systemctl restart mysqld

注意 server-id 不能与Master的 server-id 一致
2.2 指定主节点的IP,端口和用户

mysql> change master to master_host='192.168.203.144',master_port=3306,master_user='slave',master_password='slave',master_log_file='mysql-bin.000004',master_log_pos=0;

2.3 启动从节点

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
#查看从节点状态
mysql> show slave status \G;

关注以下信息

• Slave_IO_Running: Connecting
• Slave_SQL_Running: Yes
  其他相关命令

#清除slave信息 重新配置
reset slave all

接下来你就可以在主数据库上新增数据进行测试了

数据库恢复

有时候不小心误删数据 可以通过binglog进行恢复

#查看当前binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 |      708 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

添加测试数据

CREATE DATABASE bin DEFAULT CHARACTER SET utf8;
use bin;
CREATE TABLE `t` (
  `id` int(10) ,
  `name` varchar(20)
) ;
INSERT INTO t VALUES(1, 'xiaoming');
INSERT INTO t VALUES(2, 'huahua');
INSERT INTO t VALUES(3, 'xxx');

查看binlog日志信息

#查看日志详情 找到你要恢复的数据节点位置
mysql> show binlog events in 'mysql-bin.000009';
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                  |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000001 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.27-log, Binlog ver: 4                                 |
| mysql-bin.000001 |  123 | Previous_gtids |         1 |         154 |                                                                       |
| mysql-bin.000001 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 |  219 | Query          |         1 |         310 | create database bin                                                   |
| mysql-bin.000001 |  310 | Anonymous_Gtid |         1 |         375 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 |  375 | Query          |         1 |         505 | use `bin`; CREATE TABLE `t` (
  `id` int(10) ,
  `name` varchar(20)
) |
| mysql-bin.000001 |  505 | Anonymous_Gtid |         1 |         570 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 |  570 | Query          |         1 |         647 | BEGIN                                                                 |
| mysql-bin.000001 |  647 | Query          |         1 |         753 | use `bin`; INSERT INTO t VALUES(1, 'xiaoming')                         |
| mysql-bin.000001 |  753 | Xid            |         1 |         784 | COMMIT /* xid=2369 */                                                 |
| mysql-bin.000001 |  784 | Anonymous_Gtid |         1 |         849 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 |  849 | Query          |         1 |         926 | BEGIN                                                                 |
| mysql-bin.000001 |  926 | Query          |         1 |        1034 | use `bin`; INSERT INTO t VALUES(2, 'huahua')                       |
| mysql-bin.000001 | 1034 | Xid            |         1 |        1065 | COMMIT /* xid=2370 */                                                 |
| mysql-bin.000001 | 1065 | Anonymous_Gtid |         1 |        1130 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 | 1130 | Query          |         1 |        1207 | BEGIN                                                                 |
| mysql-bin.000001 | 1207 | Query          |         1 |        1309 | use `bin`; INSERT INTO t VALUES(3, 'xxx')                             |
| mysql-bin.000001 | 1309 | Xid            |         1 |        1340 | COMMIT /* xid=2371 */                                                 |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
18 rows in set (0.00 sec)

接下来做一个操作就是对于t表中的数据进行删除所有的数据(这是模拟某些不小心的同学做的操作)；

mysql> delete from `t` where id > 1;
Query OK, 2 rows affected (0.02 sec)

mysql> select * from t;
+------+---------+
| id   | name    |
+------+---------+
|    1 | xiaoming|
+------+---------+
1 row in set (0.00 sec)

现在来做恢复；根据数据情况找到数据的节点位置， 发现是从849开始到1340结束

[root@localhost data]# mysqlbinlog mysql-bin.000009 --start-position 849 --stop-position 1340  | mysql -u root -p
Enter password: