目录1.数据库主从分类:2.Mysql主从介绍由来3.主从作用4.主从复制原理5.主从复制配置(数据一致时)5.1主从服务器分别安装mysql5.75.2主数据库与从数据库数据一致5
主从分为俩种:传统主从/GTID主从
现实生活中,数据极其重要,存储数据库的方式很多,但是数据库存在着一种隐患。
隐患:
用一台数据库存放数据,若数据库服务器宕机了导致数据丢失数据多了,访问量大了,一台服务器无法保证服务质量
因此数据库主从诞生
故障切换,实现预备读写分离,提供查询服务数据库管理系统备份(DBSM),避免影响业务
bin log:二进制日志,记录写操作(增删改查)
Relay log:中继日志
步骤:
环境需求:
俩台mysql服务器,一台主服务器(写功能),一台从服务器(读功能)
主数据库(Centos8) ip地址:192.168.136.145 centos8.0/mysql5.7 相同数据
第六节:数据不相同 (可能在公司之前有数据的情况)
从数据库(centos8) ip地址:192.168.136.191 centos7.0/mysql5.7 相同数据
可看相关教程教程(超详细):https://www.jb51.net/article/221946.htm
#二进制安装:Https://blog.csdn.net/qq_47945825/article/details/116848970?spm=1001.2014.3001.5501
#或者网络仓库安装:(一般二进制安装)
https://blog.csdn.net/qq_47945825/article/details/116245442?spm=1001.2014.3001.5501
[root@mysql01 ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| infORMation_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@mysql02 ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
replication:复制 slave:从 192.168.136.191:从数据库ip地址
mysql> create user 'vvv'@'192.168.136.191' identified by 'vvv0917';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.*to 'vvv'@'192.168.136.191';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@mysql02 ~]# mysql -uvvv -vvv0917 -h192.168.136.145
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
[root@mysql01 ~]# cat /etc/my.cnf
[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
log-bin=mysql_bin #启动binlog日志
server-id=10 #数据库服务器唯一标识,id必须比从数据库小
#重启服务 (此重启方式,前提已配置mysqld.service文件)
[root@mysql01 ~]# systemctl restart mysqld
观察主数据库状态:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000004 | 962 | | | |
+------------------+----------+--------------+------------------+---
[root@mysql02 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3307
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#skip-grant-tables
server-id=20 #服务器id,大于主数据库id
relay-log=mysql_relay_log #启动中继日志
#log-bin=mysql-bin
#重启服务:
[root@mysql02 ~]# systemctl restart mysqld
[root@slave02 ~]# mysql -uroot -p
mysql> change master to
-> master_host='192.168.136.145',
-> master_user='vvv',
-> master_passWord='vvv0917',
-> master_log_file='mysql_bin.000004',
-> master_log_pos=962;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #stop slave为关闭
Query OK, 0 rows affected (0.01 sec)
#查看配置状态:
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.136.145
Master_User: vvv
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000004
Read_Master_Log_Pos: 962
Relay_Log_File: mysql_relay_log.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#此处必须俩个都是yes,就是配置成功,否则失败
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> create database clq;
Query OK, 1 row affected (0.00 sec)
mysql> create table clq01(id int(11)not null primary key auto_increment,name varchar(100)not null,age tinyint(4));
mysql> insert clq01(name,age) values('A',20),('B',21),('C',22);
Query OK, 3 rows affected (0.00 sec)
mysql> select * from clq01;
+----+------+------+s
| id | name | age |
+----+------+------s+
| 1 | A | 20 |
| 2 | B | 21 |
| 3 | C | 22 |
+----+------+------+
#主从复制完成!
避免其他人在写入数据导致不一样
flush tables with read lock:
quit:退出即可为解锁(备份完之后才能解锁)
#先对主库进行全备
[root@mysql01 ~]# mysqldump -uroot -A > all-databases.sql
#拷贝数据到从数据库上
[root@mysql01 ~]# ls /clq
all-databases.sql
[root@mysql01 ~]# scp /clq/all-databases.sql root@192.168.136.193:/clq/
The authenticity of host '192.168.136.193 (192.168.136.193)' can't be established.
ECDSA key fingerprint is SHA256:XIAQEoJ+M0vOHmCwQvhUdw12u5s2nvkN0A4TMKLaFiY.
Are you sure you want to continue connecting (yes/no/[fingerprint])yes
root@192.168.136.193's password:
all-databases.sql 100% 853KB 115.4MB/s 00:00
[root@mysql02 clq]# ll
总用量 896 #从库上查看
-rw-r--r--. 1 root root 873266 5月 17 19:36 all-databases.sql
[root@mysql02 clq]# mysql -uroot -pHuawei0917@ < all-databases.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql02 clq]# mysql -uroot -pHuawei0917@ -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| clq |
| mysql |
| performance_schema |
| sys |
+--------------------+
主库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| clq |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@mysql01 ~]# cat /etc/my.cnf
[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
log-bin=mysql_bin #日志文件
server-id=10 #唯一标识服务id
[root@mysql02 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3307
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#skip-grant-tables
server-id=20 #唯一标识服务id(大于主库)
relay-log=mysql_relay_log #中继日志
#log-bin=mysql-bin
此后步骤和5.5之后一模一样!
小结:
主库修改数据,从库的数据随之改变!
反之,从库修改数据,主库的数据不会发生改变
查看数据库运行的命令进程
mysql> show processlist;
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 5 | repl | 192.168.136.219:39788 | NULL | Binlog Dump | 1575 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 7 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
以上就是MySQL数据库主从复制原理及作用分析的详细内容,更多关于MySQL数据库主从复制的资料请关注编程网其它相关文章!
--结束END--
本文标题: MySQL数据库主从复制原理及作用分析
本文链接: https://www.lsjlt.com/news/134721.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-20
2024-04-20
2024-04-20
2024-04-20
2024-04-20
2024-04-20
2024-04-20
2024-04-20
2024-04-20
2024-04-20
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0