mysql主从原理

简介

解决宕机带来的数据不一致，因为MySQL复制可以实时备份数据。
减轻数据库服务器的压力，多台服务器的性能一般比单台要好。

1.1主从作用

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

1.2主从形式

1.一主一从
2.主主复制
3.一主多从
4.多主一从
5.联级复制

2.主从复制原理

主库db的更新事件(update、insert、delete)被写到binlog
主库创建一个binlog dump thread，把binlog的内容发送到从库
从库启动并发起连接，连接到主库
从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

3. 主从复制配置

主从复制配置步骤：
　　⒈确保从数据库与主数据库里的数据一样
　　⒉在主数据库里创建一个同步账号授权给从数据库使用
　　⒊配置主数据库（修改配置文件）
　　４配置从数据库（修改配置文件）
需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.232.134	centos7/redhat7 mysql-5.7	有数据
从数据库	192.168.232.220	centos7/redhat7 mysql-5.7	无数据

步骤：

3.1 安装mysql5.7版本，此处可参考mysql进阶

3.2 确保从数据库与主数据库里的数据一样

//查看主数据库
[root@wangjiayue ~]# mysql -uroot -p'wjy123'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

//查看从数据库
[root@wangjiayue2 ~]# mysql -uroot -p'wjy123'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

//全量备份主数据库，备份至对需要备份的表开启所读模式
以免备份时有客户写入新的数据

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)
//备份完成后才可退出锁表终端

//备份主库并将备份文件传送到从库
[root@wangjiayue ~]# mysqldump -uroot -p'wjy123' --all-databases > all-2010062301.sql 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wangjiayue ~]# scp all-2020062301.sql root@192.168.232.134:/root/
root@192.168.232.134's password: 
all-2010062301.sql                         100%  831KB  26.5MB/s   00:00 

//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye

//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@wangjiayue2 ~]# mysql -uroot -p'wjy123' < all-2020062301.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wangjiayue2 ~]# mysql -uroot -p'wjy123' 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

3.3 在主数据库里创建一个同步账号授权给从数据库使用

mysql> grant replication slave on *.* to 'repl'@'192.168.232.134' identified by 'repl123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.4 配置主数据库

//编辑配置文件
[root@wangjiayue ~]# vim /etc/my.cnf
server-id = 10     //数据库服务器标识符，主库的id值必须比从库的大
log-bin = mysql_bin    //启用binlog日志

//重启mysql服务
[root@wangjiayue ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//查看主库的状态
[root@wangjiayue ~]# mysql -uroot -p'wjy123' -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3.5 配置从数据库

//编辑配置文件
[root@wangjiayue2 ~]# vim /etc/my.cnf
server-id = 20  //设置从库的标识符，从库的id值必须小于主库的该值
relay-log = myrelay_bin //启用中继日志relay-log

//重启mysql服务
[root@wangjiayue2 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//配置并启动主从复制
mysql> change master to \
    -> master_host='192.168.232.220',
    -> master_user='repl',
    -> master_password='wjy_123',
    -> master_log_file='mysql_bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

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

查看从服务器状态
mysql> show slave status\G
## ## ## ## ## ## ## ## ## ## ## ## ## * 1. row ## ## ## ## ## ## ## ## ## ## ## ## ## *
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.232.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: myrelay_bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes  
            Slave_SQL_Running: Yes 
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
.....

3.6 验证

//在主数据库中添加数据和删除数据
mysql> create database abc; 
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   22 |
|  3 | sean      |   28 |
|  4 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.00 sec)

mysql> delete from student where id = 4; //删除数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   22 |
|  3 | sean  |   28 |
+----+-------+------+
3 rows in set (0.00 sec)


//在从数据库中查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   22 |
|  3 | sean  |   28 |
+----+-------+------+
3 rows in set (0.01 sec)