MySQL主从配置

最新推荐文章于 2023-07-11 22:37:49 发布

Op_Wa

最新推荐文章于 2023-07-11 22:37:49 发布

阅读量123

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/Op_Wa/article/details/120004027

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL主从复制的由来、作用及不同形式，旨在解决数据丢失和性能问题。通过实时灾备、读写分离和备份，确保系统稳定。文中详细展示了新部署和已有数据的主从配置步骤，包括创建同步账号、配置主从数据库、启动复制等，最后通过测试验证配置成功。

摘要由CSDN通过智能技术生成

主从由来

主从的由来是为了应对以下问题：
- 用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？
- 业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？

主从的作用

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

主从形式

一主一从
一主多从（常用方式）：扩展系统读取的性能，因为读是在从库读取的
多主一从
主主复制
联级复制

主从复制原理

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

配置主从

主从配置步骤：
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库（修改配置文件）
- 配置从数据库（修改配置文件）
部署主从的两种情况：
- 主数据库原先就有数据
- 新部署的主从

mysql主从配置（新部署的主从，主从库都没有数据）

准备工作：
- 搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
- 主数据库：IP：192.168.218.131 无数据，二进制安装mysql
- 从数据库：IP：192.168.218.133 无数据，二进制安装mysql
查看主从数据库内容是否一直

//主数据库内容
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

//从数据库内容
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

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

//配置前先关闭防火墙，主从数据库都需要关闭
[root@localhost ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@localhost ~]# setenforce 0 
[root@localhost ~]# vi /etc/selinux/config 
SELINUX=disabled

//创建同步账号
mysql> grant replication slave on *.* to 'repl'@'192.168.218.133' identified by 'repl123';

Query OK, 0 rows affected, 1 warning (0.00 sec)

//刷新权限
mysql> flush privileges;    
Query OK, 0 rows affected (0.00 sec)

//使用从数据库登录测试一下
[root@localhost ~]# mysql -urepl -p'repl123' -h192.168.218.131
mysql>   //登录成功

配置主数据库

//添加内容
[root@localhost ~]# vi /etc/my.cnf 

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

# replication slave config 
log-bin = mysql_bin  //启用binlog日志
server-id = 10     //数据库服务器唯一标识符，主库的server-id值必须比从库的小

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

//查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从数据库

//添加内容
[root@localhost ~]# vim /etc/my.cnf 

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

# replication slave config
server-id = 20   //要比主库大
relay-log = mysql_relay  //启用中继日志relay-log

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

//配置主从复制
mysql> change master to 
    -> master_host='192.168.218.131',
    -> master_user='repl',
    -> master_password='repl123',
    -> master_log_file='mysql_bin.000002',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

//启动主从复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

//查看从服务器状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.218.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql_relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes  //此处必须为yes
            Slave_SQL_Running: Yes  //此处必须为yes

测试是否设置成功

//在主库上创建一个库school 
mysql> use school;
Database changed
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//在从库上查看是否有库school
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
//以上显示测试成功，说明主从配置设置成功

mysql主从配置（主数据库原先有数据）

准备工作：
- 搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
- 主数据库：IP：192.168.218.131 有数据，二进制安装mysql
- 从数据库：IP：192.168.218.133 无数据，二进制安装mysql
查看主是数据库内容

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

mysql> use school;

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  4 | tom   |   10 |
|  5 | jerry |   20 |
|  6 | lisi  |   30 |
+----+-------+------+
3 rows in set (0.00 sec)

全备主库并在从库主机恢复

// 全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

//备份主库数据
[root@localhost ~]# mysqldump -uroot -p --all-databases > all-database-20210830.sql
Enter password: 

//复制主库备份资源到从库主机
[root@localhost ~]# scp all-database-20210830.sql 192.168.218.133:/root/
root@192.168.218.133's password: 
all-database-20210830.sql                                 100%  853KB  47.7MB/s   00:00 

//在从库主机恢复备份
[root@localhost ~]# mysql -uroot -p < all-database-20210830.sql 
Enter password: 

//关闭读锁

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

//配置前先关闭防火墙，主从数据库都需要关闭
[root@localhost ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@localhost ~]# setenforce 0 
[root@localhost ~]# vi /etc/selinux/config 
SELINUX=disabled

//创建用户
mysql> grant replication slave on *.* to 'repl'@'192.168.218.133' identified by 'repl123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

//在从库主机测试是否可以登录
[root@localhost ~]# mysql -u'repl' -p'repl123' -h192.168.218.131
mysql>

配置主数据库

//在/etc/my.cnf添加内容
[root@localhost ~]# vi /etc/my.cnf 

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

# replication slave config 
server-id = 10  //数据库服务器唯一标识符，主库的server-id值必须比从库的小
log-bin = mysql_bin  //启用binlog日志

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

//查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从数据库

//在/etc/my.cnf添加内容
[root@localhost ~]# vim /etc/my.cnf 

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

# replication slave config
server-id = 20
relay-log = mysql-relay-bin

//重启服务
[root@localhost ~]# vim /etc/my.cnf 
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//配置主从复制
mysql> change master to
    -> master_host='192.168.218.131',
    -> master_user='repl',
    -> master_password='repl123',
    -> master_log_file='mysql_bin.000003',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

//开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

//查看主从服务状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.218.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes    //此处必须为Yes
            Slave_SQL_Running: Yes    //此处必须为Yes

测试是否设置成功

//之前主库表student中的内容
mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  4 | tom   |   10 |
|  5 | jerry |   20 |
|  6 | lisi  |   30 |
+----+-------+------+
3 rows in set (0.00 sec)

//插入一条数据
mysql> insert student(name,age) value('zhangsan' ,40);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  4 | tom      |   10 |
|  5 | jerry    |   20 |
|  6 | lisi     |   30 |
|  7 | zhangsan |   40 |
+----+----------+------+
4 rows in set (0.00 sec)

//在从库主机查看是否同步
mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  4 | tom      |   10 |
|  5 | jerry    |   20 |
|  6 | lisi     |   30 |
|  7 | zhangsan |   40 |
+----+----------+------+
4 rows in set (0.00 sec)
//有zhangsan ，以上主从配置成功

额外小知识点：

//查看进程命令show processlist
mysql> show processlist;
+----+------+-----------------------+--------+-------------+------+---------------------------------------------------------------+----------------------+
| Id | User | Host                  | db     | Command     | Time | State                                                         | Info                 |
+----+------+-----------------------+--------+-------------+------+---------------------------------------------------------------+----------------------+
|  2 | root | localhost             | NULL   | Query       |    0 | starting                                                      | show processlist     |
|  3 | repl | 192.168.218.133:54164 | NULL   | Binlog Dump |  644 | Master has sent all binlog to slave; waiting for more updates | NULL                 |
|  4 | root | localhost             | school | Query       |    9 | Waiting for global read lock                                  | create table teacher |
+----+------+-----------------------+--------+-------------+------+---------------------------------------------------------------+----------------------+
3 rows in set (0.00 sec)

//如果运行mysql命令的时候感觉很慢，或者一直卡在那个地方，可以使用kill +id杀掉进程
mysql> kill 4;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host                  | db   | Command     | Time | State                                                         | Info             |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  2 | root | localhost             | NULL | Query       |    0 | starting                                                      | show processlist |
|  3 | repl | 192.168.218.133:54164 | NULL | Binlog Dump |  725 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

Op_Wa

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL主从配置

主从由来主从的由来是为了应对以下问题：用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？主从的作用实时灾备，用于故障切换读写分离，提供查询服务备份，避免影响业务主从形式一主一从一主多从（常用方式）：扩展系统读取的性能，因为读是在从库读取的多主一从主主复制联级复制主从复制原理主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的
复制链接

扫一扫

专栏目录