目录
- 1.数据库主从分类:
- 2.mysql主从介绍由来
- 3.主从作用
- 4.主从复制原理
- 5.主从复制配置(数据一致时)
- 5.1主从服务器分别安装mysql5.7
- 5.2主数据库与从数据库数据一致
- 5.3在主数据库里创建一个同步账号授权给从数据库使用
- 5.4在从库上测试连接
- 5.5配置主数据库
- 5.6配置从数据库
- 5.7配置并启动主从复制的功能(mysql02从数据库上)
- 5.8测试:
- 主库:
- 从库:
- 主库创建数据库clq并且加入数据:
- 从库中查看:
- 6.主从配置(数据不一致时)
- 6.1一般全备主库需要另开一个终端,给数据库加上读锁(只读不写)
- 6.2确保主主数据库与从数据库的数据一样
- 6.3在从库上查看主库有哪些库,确保一致
- 6.4确保俩库的配置文件已经配置了相应的文件
1.数据库主从分类:
主从分为俩种:传统主从/gtid主从
2.mysql主从介绍由来
现实生活中,数据极其重要,存储数据库的方式很多,但是数据库存在着一种隐患。
隐患:
用一台数据库存放数据,若数据库服务器宕机了导致数据丢失数据多了,访问量大了,一台服务器无法保证服务质量
因此数据库主从诞生
3.主从作用
故障切换,实现预备读写分离,提供查询服务数据库管理系统备份(dbsm),避免影响业务
4.主从复制原理
bin log:二进制日志,记录写操作(增删改查)
relay log:中继日志
- 主库会将所有的写操作记录到binlog日志下生成一个log dump线程,将binlog日志传给从库的i/o线程。
- 从库有俩个线程:
i/o线程
sql线程 - 从库的i/o线程会请求主库得到binlog日志写到relay log(中继日志)中
- sql线程,会读取relay log日志文件中的日志,并解析具体操作,来实现主从的操作一样,达到数据一致
5.主从复制配置(数据一致时)
步骤:
- 确保主数据库与从数据的数据一样
- 主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库(修改配置文件)
- 配置从数据库(修改配置文件)
环境需求:
俩台mysql服务器,一台主服务器(写功能),一台从服务器(读功能)
主数据库(centos8) ip地址:192.168.136.145 centos8.0/mysql5.7 相同数据
第六节:数据不相同 (可能在公司之前有数据的情况)
从数据库(centos8) ip地址:192.168.136.191 centos7.0/mysql5.7 相同数据
5.1主从服务器分别安装mysql5.7
可看相关教程教程(超详细):
#二进制安装: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
5.2主数据库与从数据库数据一致
[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 | +--------------------+
5.3在主数据库里创建一个同步账号授权给从数据库使用
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)
5.4在从库上测试连接
[root@mysql02 ~]# mysql -uvvv -vvv0917 -h192.168.136.145 mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)
5.5配置主数据库
[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 | | | | +------------------+----------+--------------+------------------+---
5.6配置从数据库
[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
5.7配置并启动主从复制的功能(mysql02从数据库上)
[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,就是配置成功,否则失败
5.8测试:
主库:
mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
从库:
mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
主库创建数据库clq并且加入数据:
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 | +----+------+------+ #主从复制完成!
6.主从配置(数据不一致时)
6.1一般全备主库需要另开一个终端,给数据库加上读锁(只读不写)
避免其他人在写入数据导致不一样
flush tables with read lock: quit:退出即可为解锁(备份完之后才能解锁)
6.2确保主主数据库与从数据库的数据一样
#先对主库进行全备 [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
6.3在从库上查看主库有哪些库,确保一致
[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 | +--------------------+
6.4确保俩库的配置文件已经配置了相应的文件
[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数据库主从复制的资料请关注www.887551.com其它相关文章!