mysql主从

诚诚k

已于 2024-08-07 09:00:22 修改

阅读量346

点赞数 3

文章标签： mysql 数据库

于 2024-08-06 10:48:07 首次发布

本文链接：https://blog.csdn.net/czzwa/article/details/140944394

版权

1. 主从简介

在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。

用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？
业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？

1 主从作用

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

主从形式

一主一从
主主复制
一主多从---扩展系统读取的性能，因为读是在从库读取的
多主一从---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服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中

//先看主数据库有哪一些库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xs                 |
+--------------------+
5 rows in set (0.00 sec)

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

全备主库

此时，全备主库需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致

mysql> flush tables with read lock;    // 此锁表的终端必须在备份完成以后才能退出
Query OK, 0 rows affected (0.01 sec)


此时在另一个终端进行全备主库
[root@node1 ~]# mysqldump -uroot -p --all-databases > /opt/all-8060910.sql
Enter password: 
[root@node1 ~]# cd /opt/
[root@node1 opt]# ls
all-8060910.sql  data  software
// 已经做好备份

//将备份文件传送到从库

[root@node1 opt]# scp /opt/all-8060910.sql root@192.168.100.20:/opt/
The authenticity of host '192.168.100.20 (192.168.100.20)' can't be established.
ED25519 key fingerprint is SHA256:6X4azVsTaqSFt4Du+6bzFCpH5+wW+fQpcaJeO7DkHxo.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.100.20' (ED25519) to the list of known hosts.
root@192.168.100.20's password: 
all-8060910.sql                                                                 100%  856KB  34.8MB/s   00:00    

//解锁主库锁表状态，直接退出交互式界面
 mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
[root@node1 ~]#

在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致

[root@node2 ~]# mysql -uroot -p < /opt/all-8060910.sql 
Enter password: 
[root@node2 ~]# mysql -uroot -plinux -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xs                 |
+--------------------+

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

mysql> create user 'repl'@'192.168.100.20' identified by 'linux'  //创建同步账号
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.100.20';  //设置从库权限
Query OK, 0 rows affected (0.00 sec)

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

配置主数据库

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

server-id = 1          //数据库服务器唯一标识符，主库的server-id值必须比从库的小
log-bin = mysql_bin
log-error=/var/log/mysqld.log
symbolic-links=0`

重启mysql服务

[root@node1 ~]# systemctl restart mysqld
[root@node1 ~]# ss -antl
State        Recv-Q       Send-Q             Local Address:Port             Peer Address:Port       Process       
LISTEN       0            128                      0.0.0.0:22                    0.0.0.0:*                        
LISTEN       0            80                             *:3306                        *:*                        
LISTEN       0            128                         [::]:22                       [::]:*                        
// 3306端口已起

查看主库状态

[root@node1 ~]# mysql -uroot -plinux -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.000006 |     1388 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

配置从数据库

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

server-id = 2              //设置从库的唯一标识符，从库的server-id值必须大于主库的该值
relay-log=mysql-relay-bin      //启用中继日志relay-log  
symbolic-links=0
log-error=/var/log/mysqld.log


启动mysql
[root@node2 ~]# systemctl restart mysqld
[root@node2 ~]# ss -antl                    // 查看3306端口
State        Recv-Q       Send-Q             Local Address:Port             Peer Address:Port       Process       
LISTEN       0            128                      0.0.0.0:22                    0.0.0.0:*                        
LISTEN       0            128                         [::]:22                       [::]:*                        
LISTEN       0            80                             *:3306                        *:*

配置并启动主从复制，在从库上做

mysql> change master to master_host='192.168.100.10',  // 指定主库
    -> master_user='repl',                             // 用户
    -> master_password='linux',                        // 密码
    -> master_log_file='mysql_bin.000006',             // 日志，这两个是查看主库的状态下的   
    -> master_log_pos=1388;                            // 日志
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql>

查看从服务器状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000006
          Read_Master_Log_Pos: 1388
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000006
             Slave_IO_Running: Yes               // 此处必须是yes
            Slave_SQL_Running: Yes               // 此处必须是yes

测试验证

在主库xs库的class表中插入数据

mysql> insert into class values(3,'zhangsan'),(4,'lisi');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class;
+----+----------+
| id | name     |
+----+----------+
|  1 | cxk      |
|  2 | mjq      |
|  3 | zhangsan |
|  4 | lisi     |
+----+----------+
4 rows in set (0.00 sec)


// 查看从数据库是否同步
mysql> select * from class;
+----+----------+
| id | name     |
+----+----------+
|  1 | cxk      |
|  2 | mjq      |
|  3 | zhangsan |
|  4 | lisi     |
+----+----------+
4 rows in set (0.00 sec)

同步成功

binlog日志

可以通过binlog日志查看做了什么样的操作

从BEGIN位置来看，下面做了什么操作，他是与主数据库做的一样的操作

mysql> show relaylog events in 'mysql-relay-bin.000002';
+------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name               | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-relay-bin.000002 |   4 | Format_desc    |         2 |         123 | Server ver: 5.7.37, Binlog ver: 4     |
| mysql-relay-bin.000002 | 123 | Previous_gtids |         2 |         154 |                                       |
| mysql-relay-bin.000002 | 154 | Rotate         |         1 |           0 | mysql_bin.000006;pos=1388             |
| mysql-relay-bin.000002 | 201 | Format_desc    |         1 |           0 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql-relay-bin.000002 | 320 | Anonymous_Gtid |         1 |        1453 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-relay-bin.000002 | 385 | Query          |         1 |        1523 | BEGIN                                 |
| mysql-relay-bin.000002 | 455 | Table_map      |         1 |        1572 | table_id: 141 (xs.class)              |
| mysql-relay-bin.000002 | 504 | Write_rows     |         1 |        1631 | table_id: 141 flags: STMT_END_F       |
| mysql-relay-bin.000002 | 563 | Xid            |         1 |        1662 | COMMIT /* xid=465 */                  |
+------------------------+-----+----------------+-----------+-------------+---------------------------------------+
9 rows in set (0.00 sec)