mysql主从

mysql主从

主从简介

主从作用

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

主从形式

• 一主一从
• 主主复制
• 一主多从---扩展系统读取的性能，因为读是在从库读取的
• 多主一从---5.7开始支持
• 联级复制

主从复制原理

主从复制步骤：

• 主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
• 从库生成两个线程，一个I/O线程，一个SQL线程
  • I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中
  • SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

主从复制配置

主从复制配置步骤：

1. 确保从数据库与主数据库里的数据一样
2. 在主数据库里创建一个同步账号授权给从数据库使用
3. 配置主数据库（修改配置文件）
4. 配置从数据库（修改配置文件）

需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	系统与应用版本	有无数据
主数据库	192.168.102.135	centos8 mariadb	有数据
从数据库	192.168.102.134	redhat8 mariadb	无数据

mysql主从配置

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

为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中，如果主数据库中无数据则不需要做这一步

//查看主库有哪些库
[root@135 ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+
4 rows in set (0.001 sec)

//查看从库有哪些库
[root@134 ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.029 sec)
//此锁表的终端必须在备份完成以后才能退出
[root@135 ~]# cd /opt/mydata/
[root@135 mydata]# mysqldump -uroot --all-databases > all.sql
[root@135 mydata]# ls
all.sql

//将备份文件传送到从库
[root@135 mydata]# scp all.sql 192.168.102.134:/opt
root@192.168.102.134's password: 

all.sql                               100%  468KB  30.5MB/s   00:00  

[root@134 opt]# ls
all.sql

//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@134 opt]# mysql < all.sql 
[root@134 opt]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+

//解除主库的锁表状态，直接退出交互式界面即可
MariaDB [(none)]> quit
Bye

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

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.102.134' identified by 'repl233';
Query OK, 0 rows affected (0.037 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

配置主数据库

//在[mysqld]这段的后面加上如下内容
log-bin=mysql_bin   //启用binlog日志
server-id=10     //数据库服务器唯一标识符，主库的server-id值必须比从库的小

//重启mysql服务
[root@135 ~]# systemctl restart mariadb.service 

//查看主库的状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000004 |      342 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

配置从数据库

[root@134 ~]# vim /etc/my.cnf

[mysqld]
log-bin=mysql_bin
server-id=20

//重启从库的mysql服务
[root@134 ~]# systemctl restart mariadb.service 

//配置并启动主从复制
MariaDB [(none)]> change master to
    -> master_host='192.168.102.135',
    -> master_user='repl',
    -> master_password='repl233',
    -> master_log_file='mysql_bin.000004',
    -> master_log_pos=342;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

//查看从服务器状态
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.102.135
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql_bin.000004
           Read_Master_Log_Pos: 342
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql_bin.000004
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

测试验证

在主服务器的school库的student表中插入数据

[root@135 ~]# mysql

MariaDB [(none)]> use school;
Database changed

MariaDB [school]> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | muimi |   16 |
|  2 | rima  |   18 |
|  3 | jeed  |   19 |
+----+-------+------+
3 rows in set (0.000 sec)


MariaDB [school]> insert into student(name,age) values('lion',24),('asuka',23);
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [school]> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | muimi |   16 |
|  2 | rima  |   18 |
|  3 | jeed  |   19 |
|  4 | lion  |   24 |
|  5 | asuka |   23 |
+----+-------+------+
5 rows in set (0.000 sec)

在从数据库中查看数据是否同步

MariaDB [(none)]> select * from school.student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | muimi |   16 |
|  2 | rima  |   18 |
|  3 | jeed  |   19 |
|  4 | lion  |   24 |
|  5 | asuka |   23 |
+----+-------+------+
5 rows in set (0.000 sec)