【MySQL】主从复制过程（实践）

news/2024/5/8 11:01:23/文章来源:https://blog.csdn.net/Stephen_Daa/article/details/129921186

在这里插入图片描述
1.安装好2台数据库服务器的系统，然后安装好MySQL软件

[root@jd-mysql ~]# mysql --version
mysql  Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using  EditLine wrapper

[root@jd-mysql-2 ~]# mysql --version                                                             
mysql  Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using  EditLine wrapper

2.在master上开启二进制日志
在这里插入图片描述

3.统一2台服务器的基础数据

[root@jd-mysql ~]# mysqldump -uroot -p'Jiangda123#'  --all-databases  >all_db.SQL
mysqldump: [Warning] Using a password on the command line interface can be insecure.[root@jd-mysql ~]# scp all_db.SQL root@192.168.1.151:/root
all_db.SQL                                                            100%  898KB  88.2MB/s   00:00[root@jd-mysql-2 ~]# mysql -uroot -p'Jiangda123#' <all_db.SQL 
mysql: [Warning] Using a password on the command line interface can be insecure.

4.清除所有的二进制日志，因为有全备，不需要二进制日志了

root@(none) 14:54  mysql>reset master;
Query OK, 0 rows affected (0.00 sec)root@(none) 14:54  mysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| jd-mysql-bin.000001 |      154 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.在master上新建一个授权用户，给slave来复制二进制日志

root@(none) 14:55  mysql>grant replication slave on *.* to 'jiangda'@'192.168.1.%' identified by 'a123#';
Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 14:55  mysql>flush privileges;
Query OK, 0 rows affected (0.00 sec)root@(none) 14:57  mysql>show grants for 'jiangda'@'192.168.1.%';
+-----------------------------------------------------------+
| Grants for jiangda@192.168.1.%                            |
+-----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'jiangda'@'192.168.1.%' |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

6.在slave上配置master info的信息

CHANGE MASTER TO MASTER_HOST=‘192.168.1.150’ ,
MASTER_USER=‘jiangda’,
MASTER_PASSWORD=‘Jiangda123#’,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘jd-mysql-bin.000001’,
MASTER_LOG_POS=154;

root@(none) 14:48  mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.150' ,->  MASTER_USER='jiangda',->  MASTER_PASSWORD='Jiangda123#',->  MASTER_PORT=3306,->  MASTER_LOG_FILE='jd-mysql-bin.000001',->  MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

7.查看slave是否配置成功(在slave操作)

 root@(none) 11:29  mysql>show slave status\G;

在这里插入图片描述

8.启动slave

root@(none) 14:59  mysql>start slave;
Query OK, 0 rows affected (0.00 sec)

root@(none) 11:31  mysql>show slave status\G;

在这里插入图片描述

2个都是yes说明IO线程和SQL线程都启动了。

9.测试主从复制的效果

主上面的操作

root@(none) 11:23  mysql>create database jd;
Query OK, 1 row affected (0.02 sec)root@(none) 11:36  mysql>use jd;
Database changed
root@jd 11:36  mysql>create table  t1(id int);
Query OK, 0 rows affected (0.04 sec)root@jd 11:36  mysql>insert into t1(id) values(1),(2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0root@jd 11:37  mysql>

从服务器上的操作

root@(none) 11:36  mysql>use jd;
Database changed
root@jd 11:36  mysql>show tables;
Empty set (0.00 sec)root@jd 11:36  mysql>show tables;
+------------------+
| Tables_in_yuanrd |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)root@jd 11:36  mysql>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.03 sec)

slave是一直连接到master的，传输层使用tcp，因为建立了连接。

使用的账号和密码都是jiangda ，进行复制二进制日志使用

[root@jd-mysql ~]# netstat -anplut|grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      1333/mysqld         
tcp6       0      0 192.168.1.150:3306      192.168.1.151:44682     ESTABLISHED 1333/mysqld   root@(none) 15:03  mysql>show processlist;
+----+-------------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User        | Host                | db   | Command     | Time | State                                                         | Info             |
+----+-------------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 13 | system user |                     | NULL | Connect     |  578 | Slave has read all relay log; waiting for more updates        | NULL             |
| 18 | jiangda     | 192.168.1.151:44682 | NULL | Binlog Dump |  220 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 19 | root        | localhost           | NULL | Query       |    0 | starting                                                      | show processlist |
+----+-------------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

10.master.info 和 relay-log.info在哪里？

[root@jd-mysql-2 mysql]# pwd
/data/mysql
[root@jd-mysql-2 mysql]]# ls *.info
master.info  relay-log.info[root@jd-mysql-2 mysql]# cat master.info 
25
jd-mysql-bin.000001
1196
192.168.1.150
jiangda
Jiangda123#
3306
60
00
30.0000
de35d2ef-cd37-11ed-93d8-000c2954bc91
864000[root@jd-mysql-2 mysql]# cat relay-log.info 
7
./jd-mysql-2-relay-bin.000002
1365
jd-mysql-bin.000001
1196
0
0
1