首页 / LINUX / linux服务-mysql主从
linux服务-mysql主从
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了linux服务-mysql主从，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5906字，纯文字阅读大概需要9分钟。
内容图文
1.主从简介
1.1诞生原因
数据库隐患：访问人多，服务器宕机
服务器时间长质量问题数据丢失
1.2主从作用
- 实时灾备，用于故障切换
- 读写分离，提供查询服务
- 备份，避免影响业务
1.3形式
- 1主1从
- 主主复制（高可用）
- 1主多从
- 多主1从（5.7版本支持）
- 联级复制
2.主从原理
- 用户在主机写入（增删改），数据库改变，二进制日志记录生成日志备份（logdump）线程。从机的I/O线程请求读取，主机收到请求后传输给从机。从机写入中继日志（relaylog），从机的SQL线程读取中继日志，解析后重现主机的操作
3.主从复制配置
环境
| 数据库角色 | ip | 系统与数据库版本 | 有无数据 |
|---|---|---|---|
| 主 | 192.168.94.141 | rhel8.2 mysql5.7 |
有 |
| 从 | 192.168.94.140 | rhel8.2 mysql5.7 |
无 |
3.1创建同步用户
- 看主表有的库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
- 看从表有的库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
- 主从关掉防火墙selinux
[root@matster ~]# systemctl stop firewalld.service
[root@matster ~]# setenforce 0
- 全备主库
备份前上读锁
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.001 sec)
//全备
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases>all-20210104.sql
[root@localhost ~]# ll
total 476
-rw-r--r-- 1 root root 482035 Jan 4 10:38 all-20210104.sql
-rw-------. 1 root root 1179 Jan 4 00:36 anaconda-ks.cfg
//将数据传给从
[root@localhost ~]# scp /root/all-20210104.sql 'root'@'192.168.94.140':/root/
The authenticity of host '192.168.94.140 (192.168.94.140)' can't be established.
ECDSA key fingerprint is SHA256:3N9nRI4b+PknMMJ8yhtDWv/UjyjRNYizaOHP28glw0E.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.94.140' (ECDSA) to the list of known hosts.
root@192.168.94.140's password:
all-20210104.sql 100% 471KB 68.1MB/s 00:00
- 同步主从数据
//从端恢复数据
[root@localhost ~]# mysql -uroot -p <all-20210104.sql
Enter password:
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| company |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
- 创建主从同步用户并授权（主上操作）
//创建用户(主)
mysql> create user 'repl'@'192.168.94.140' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.00 sec)
//授权并刷新
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.94.140';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 连接测试(从连主)
[root@localhost ~]# mysql -urepl -prepl123 -h192.168.94.141
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3.2配置数据库
- 配置主
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=10
[root@localhost ~]# systemctl restart mariadb.service
//查看
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
- 配置从
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=20
relay-log=mysql-relay-bin
//设置从
MariaDB [(none)]> change master to \
-> master_host='192.168.94.141',\
-> master_user='repl',\
-> master_password='123456',\
-> master_log_file='mysql-bin.000001',\
-> master_log_pos=328;
Query OK, 0 rows affected (0.006 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
//查看状态
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.94.141
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- 测试
//主增加记录mia
MariaDB [school]> select * from school.student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 24 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | zhangshan | 26 |
| 5 | zhangshan | 20 |
| 6 | lisi | 27 |
| 7 | cchenshuo | 10 |
| 8 | wangwu | 3 |
| 9 | qiuyi | 15 |
| 10 | qiuxiaotian | 20 |
| 11 | eason | 28 |
+----+-------------+------+
11 rows in set (0.000 sec)
MariaDB [school]> insert student(name,age) value('mia',24);
Query OK, 1 row affected (0.001 sec)
//从查看
MariaDB [school]> select * from school.student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 24 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | zhangshan | 26 |
| 5 | zhangshan | 20 |
| 6 | lisi | 27 |
| 7 | cchenshuo | 10 |
| 8 | wangwu | 3 |
| 9 | qiuyi | 15 |
| 10 | qiuxiaotian | 20 |
| 11 | eason | 28 |
| 12 | mia | 24 |
+----+-------------+------+
12 rows in set (0.000 sec)
内容总结
以上是互联网集市为您收集整理的linux服务-mysql主从全部内容，希望文章能够帮你解决linux服务-mysql主从所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】