文章目录
1.主从简介
在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。
用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？
业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？
1.1主从作用
1.实时灾备，用于故障切换
2.读写分离，提供查询服务
3.备份，避免影响业务
1.2主从形式
1.一主一从
2.主主复制
3.一主多从—扩展系统读取的性能，因为读是在从库读取的
4.多主一从—5.7开始支持
5.联级复制
2.主从复制原理
主从复制步骤：
①：主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
②：从库生成两个线程，一个I/O线程，一个SQL线程。
2.1 I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中
2.2 SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的
3.主从复制配置
主从复制配置步骤：
1.确保从数据库与主数据库里的数据一样
2.在主数据库里创建一个同步账号授权给从数据库使用
3.配置主数据库（修改配置文件）
4.配置从数据库（修改配置文件）
环境说明：
| 数据库角色 | ip | 有无数据 | 系统版本 |
|---|---|---|---|
| 主数据库 | 192.168.56.128 | 有 | redhat7 |
| 从数据库 | 192.168.56.128 | 无 | centos7 |
3.1安装mysql备份数据
[root@localhost ~]# mysql -uroot -ppengsuran123!
mysql> create database runtime;
Query OK, 1 row affected (0.32 sec)
mysql> use runtime;
Database changed
mysql> create table student(name varchar(30) not null,age tinyint);
Query OK, 0 rows affected (0.12 sec)
mysql> insert into student values ('tom',20),('jerry',30);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+-------+------+
| name | age |
+-------+------+
| tom | 20 |
| jerry | 30 |
+-------+------+
2 rows in set (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK; //全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# scp all-201902271230.sql 192.168.56.129:/root/
The authenticity of host '192.168.56.129 (192.168.56.129)' can't be established.
ECDSA key fingerprint is SHA256:DIJvphKzuijt6gvnvzNl3Gfb2MGVtHFdTB02n+Dx7lQ.
ECDSA key fingerprint is MD5:bf:e9:52:ee:af:ce:8f:c2:6f:7c:8f:d1:b2:fa:14:02.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.129' (ECDSA) to the list of known hosts.
root@192.168.56.129's password:
all-201902271213.sql 100% 785KB 9.8MB/s 00:00
[root@localhost ~]# ls
all-201902271213.sql anaconda-ks.cfg
//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@localhost ~]# mysql -uroot -ppengsuran123! < all-201902271230.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppengsuran123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| pengsuran |
| performance_schema |
| runtime |
| sys |
+--------------------+
3.2在主数据库里创建一个同步账号授权给从数据库使用
mysql> CREATE USER 'repl'@'192.168.56.129' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.56.129';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
3.3配置主数据库
[root@localhost ~]# vim /etc/my.cnf
[mysql]
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=1
symbolic-links=0
log-error=/var/log/mysqld.log
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:80 *:*
LISTEN 0 128 *:20048 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 64 *:43704 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 *:40666 *:*
LISTEN 0 64 *:2049 *:*
LISTEN 0 128 127.0.0.1:9000 *:*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::20048 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 128 :::39610 :::*
LISTEN 0 64 :::40125 :::*
LISTEN 0 64 :::2049 :::*
LISTEN 0 80 :::3306 :
//查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.4配置从数据库
[root@localhost ~]# vim /etc/my.cnf
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=5
relay-log=mysql_relay_log
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:10050 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 80 :::3306 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :
//配置并启动主从复制
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.56.128',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl123',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
//查看从服务器状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql_relay_log.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此处必须为yes
Slave_SQL_Running: Yes //此处必须为yes
3.6测试验证
在主库上插入数据
mysql> select *from student;
+-------+------+
| name | age |
+-------+------+
| tom | 20 |
| jerry | 30 |
+-------+------+
2 rows in set (0.00 sec)
mysql> insert student values('xiaohong',30),('xiaoming',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select *from student;
+----------+------+
| name | age |
+----------+------+
| tom | 20 |
| jerry | 30 |
| xiaohong | 30 |
| xiaoming | 20 |
+----------+------+
4 rows in set (0.00 sec)
在从数据库中查看数据是否同步：
mysql> use runtime;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from student;
+----------+------+
| name | age |
+----------+------+
| tom | 20 |
| jerry | 30 |
| xiaohong | 30 |
| xiaoming | 20 |
+----------+------+
4 rows in set (0.01 sec)
4在zabbix上添加监控和触发器
添加监控项、触发器
监控I/O和SQL线程是否正常
客户端：
[root@localhost ~]# vi .my.cnf
[client]
user=root
password=pengsuran123！
[root@localhost ~]# vim SQL.sh
a=$(mysql -e 'show slave status\G'|grep ' Slave_IO_Running'|awk -F: '{print $2}')
b=$(mysql -e 'show slave status\G'|grep 'Slave_SQL_Running'|awk -F: 'NR==1 {print $2}')
if [ $a == Yes -a $b == Yes ]
then
echo '0'
else
echo '1'
exit 1
fi
[root@localhost ~]# chmod +x SQL.sh
[root@localhost ~]# chown zabbix.zabbix SQL.sh
[root@localhost ~]# ./SQL.sh
5.在zabbix监控主从延迟
[root@localhost ~]# vim pos.sh
#!/bin/bash
c=$(mysql -e 'show slave status \G'|grep 'Read_Master_Log_Pos'|awk -F: '{print $2}')
d=$(mysql -e 'show slave status \G'|grep 'Exec_Master_Log_Pos'|awk -F: '{print $2}')
e=$[c-d]
if [ $e -eq 0 ]
then
echo "0"
else [ $e -ne 0 ]
echo "$e"
fi
[root@localhost~]# chmod +x pos.sh
[root@localhost ~]# chown zabbix.zabbix pos.sh
[root@localhost ~]# vim /usr/local/etc/zabbix_agentd.conf
UserParameter=mysql-slave111,/root/pos.sh
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
