文章目录
1.mysql主从简介
1.mysql主从复制主要⽤用途
a.⽤用于备份，避免影响业务
b.实时灾备，⽤用于故障切换
c.读写分离，提供查询服务
2.mysql主从复制存在的问题
a.主库宕机后, 数据可能丢失
b.主库写压⼒力力⼤大, 复制可能会延时
3.mysql主从复制解决⽅方法
a.半同步复制、或者全同步复制. 要求: Mysql5.7版本
b.并⾏行行复制, 解决从库复制延迟的问题. 建议: 5.7版
4.mysql主从复制原理理
1.在主库上把将更更改 DDL DML DCL 记录到⼆二进制⽇日志 Binary Log 中。
2.备库 I/O 线程将主库上的⼆二进制⽇日志复制到⾃自⼰己的中继⽇日志 Relay Log 中。
3.备库 SQL 线程读取中继⽇日志中的事件，将其重放到备库数据库之上
2.SQL语句
SQL语句有三种类型：
DDL：数据定义语言，数据定义语言
DML：Data Manipulation Language，数据操纵语言
DCL：数据控制语言，数据控制语言
| SQL语句类型 | 对应操作 |
|---|---|
| DDL DROP 、 ALTER | 创建 删除、 修改 |
| DML INSERT 、 DELETE | 向表中插入数据 、 删除表中数据 |
| SELECT DCL GRANT 、 REVOKE | 查询表中数据 授权 、 移除授权 |
3.主从形式
1.一主一从
2.主主复制
3.一主多从—扩展系统读取的性能，因为读是在从库读取的
4.多主一从—5.7开始支持
联级复制
4.myslq安装和配置
环境说明
| 数据角色名称 | ip | 系统版本 |
|---|---|---|
| 主-mysql-master | 192.168.69.134 | CentOS7 |
| 从-mysql-slave | 192.168.69.132 | redhat7 |
安装参照上篇文章搭建MySQL服务
4.1安装mysql备份数据
[root@mysql-master ~]# mysql -uroot -pqinyong -e 'show databases;' \\查看主库
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zabbix |
[root@mysql-slave ~]# mysql -uroot -pqinyong -e 'show databases;' \\查看从库
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> FLUSH TABLES WITH READ LOCK; \\再开一个主数据库终端输入以下内容锁库
Query OK, 0 rows affected (0.00 sec)
[root@mysql-master ~]# mysqldump -uroot -pqinyong --all-databases >/root/all-qy-20190227.sql
[root@mysql-master ~]# ls |grep all
all-qy-20190227.sql
[root@mysql-master ~]# scp all-qy-20190227.sql root@192.168.69.132:/root/ //拷贝到从数据库目录下
[root@mysql-slave ~]# ls
all-qy-20190227.sql anaconda-ks.cfg a.txt
解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye
在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@mysql-slave ~]# mysql -uroot -pqinyong <all-qy-20190227.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-slave ~]# mysql -uroot -pqinyong -e 'show databases;' //验证从库和主库一致
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zabbix |
4.2在主数据库里创建一个同步账号授权给从数据库使用
mysql> CREATE USER 'qinyong'@'192.168.69.132' IDENTIFIED BY 'qy123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'qinyong'@'192.168.69.132';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4.3配置主数据库
[root@localhost ~]# vim /etc/my.cnf
//在[mysqld]这段的后面加上如下内容
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin //启用binlog日志
server-id=1 //数据库服务器唯一标识符，主库的server-id值必须比从库的大
[root@mysql-master ~]# systemctl restart mysqld \\重启服务
[root@mysql-master ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 127.0.0.1:9000 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 80 :::3306 :::*
LISTEN 0 128 :::80 :::*
LISTEN 0 128 :::22 :::*
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)
4.4配置从数据库
[root@mysql-slave ~]# vim /etc/my.cnf
//添加如下内容
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=5 //设置从库的唯一标识符，从库的server-id值必须小于主库的该值
relay-log=mysql-relay-bin //启用中继日志relay-log
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@mysql-slave ~]# systemctl restart mysqld
[root@mysql-slave ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*
mysql> change master to master_host='192.168.69.134',master_user='qinyong',master_password='qy123',master_log_file='mysql-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.69.134
Master_User: qinyong
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此处必须为Yes
Slave_SQL_Running: Yes //此处必须为Yes
4.5测试验证
在主库
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student values (1,'sean',20),(2,'tom',23),(3,'jerry',30);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | sean | 20 |
| 2 | tom | 23 |
| 3 | jerry | 30 |
+----+-------+------+
3 rows in set (0.00 sec)
在从库验证查看是否同步
mysql> use zabbix;
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; \\查看同步成功
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | sean | 20 |
| 2 | tom | 23 |
| 3 | jerry | 30 |
+----+-------+------+
3 rows in set (0.00 sec)
5设置zabbix监控主从同步
5.1监控Slave_IO_Running和Slave_SQL_Running是否正常
[root@mysql-slave ~]# vi .my.cnf \\设置免密码查看
[client]
user=root
password=qinyong
[root@mysql-slave ~]# 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@mysql-slave ~]# chmod +x SQL.sh
[root@mysql-slave ~]# chown zabbix.zabbix SQL.sh
[root@mysql-slave ~]# ./SQL.sh
1
[root@mysql-slave ~]# mysql -e 'start slave'
[root@mysql-slave ~]# ./SQL.sh
0
[root@mysql-slave ~]# vim /usr/local/etc/zabbix_agentd.conf
# UnsafeUserParameters=0去掉注释并且改成=1
UnsafeParameter=check_mysql-slave,/bin/bash /root/SQL.sh mysql-slave
[root@mysql-slave ~]# zabbix_get -s 192.168.69.132 -k mysql-slave01
0
5.1.2在zabbix的web管理界面
添加主机、添加监控项、添加触发器
详情参照添加脚本监控
[root@mysql-slave ~]# mysql -e 'start slave'
5.2在zabbix监控主从延迟’Exec_Master_Log_PosRead_Master_Log_Pos
5.2.1在客户端写脚本
[root@mysql-slave ~]# 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@mysql-slave ~]# chmod +x pos.sh
[root@mysql-slave ~]# chown zabbix.zabbix pos.sh
[root@mysql-slave ~]# vim /usr/local/etc/zabbix_agentd.conf
UserParameter=mysql-slave02,/root/pos.sh
5.2.2在zabbixweb设置监控项
1.跟之前添加监控项唯一不同在于引用变量{ITEM.VALUE}最后添加触发器
2.进行验证
6.MySQL数据库Gtid复制
什么是GTID，也就是全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。
如果实验过传统主从复制, 请重置Slave数据库,⽂文章后续会介绍
优点
A：很方便的进行故障转移，因为GTID是全局唯一的标识符，所以就很简单知道哪些事务在从服务器没有执行，在多个从服务器也没必要进行多个日志偏移量配置了.
B：从库和主库的数据一致性。
缺点
A:故障处理比日志处理复杂。
B:执行语句的一些限制。
1.传统一主一从
2.Gtid以双主库为主
1.配置主库
1.1配置⽂文件/etc/my.cnf
[root@mysql-master ~]# vim /etc/my.cnf
[root@mysql-master ~]# 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
server-id = 5
log-bin = mysql_log
gtid_mode = ON \\git开启
enforce-gtid-consistency = true \\git开启.
[root@mysql-master ~]# systemctl restart mysqld \\重启mysql服务⽣生效
[root@mysql-master ~]# mysql -uroot -pqinyong
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_log.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.2配置从数据库
[root@mysql-slave ~]# vim /etc/my.cnf
[root@mysql-slave ~]# 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
server-id = 6
relay-log = mysql-relay-log
gtid_mode = ON
enforce-gtid-consistency = true
relay-log = mysql-relay-log1
[root@mysql-slave ~]# systemctl restart mysqld
[root@mysql-slave ~]# mysql -uroot -pqinyong
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.69.130',master_user='qinyong',master_password='qy123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.69.130
Master_User: qinyong
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-log1.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'qinyong@192.168.69.130:3306' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190302 21:00:33
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | lili | 30 |
| 7 | haha | 22 |
+----+-----------+------+
7 rows in set (0.00 sec)
1.3 在从库查看设置
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | lili | 30 |
| 7 | haha | 22 |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.69.130
Master_User: qinyong
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-log1.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'qinyong@192.168.69.130:3306' - retry-time: 60 retries: 28
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190302 21:27:35
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: qwe9fgd8-34e8-11e9-8ee2-000c295aa226:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
1.4在主数据库更改信息
mysql> use qinyong;
Database changed
mysql> insert into student value(12,'yy',66);
Query OK, 1 row affected (0.00 sec)
mysql> select * from qinyong.student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | lili | 30 |
| 7 | haha | 22 |
| 12 | yy | 66 |
+----+-------------+------+
1.5.在从库中查看是否变化
mysql> select * from qinyong.student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | lili | 30 |
| 7 | haha | 22 |
| 12 | yy | 66 |
+----+-------------+------+
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.69.130
Master_User: qinyong
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-log1.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'qinyong@192.168.69.130:3306' - retry-time: 60 retries: 28
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190302 21:27:35
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d589b398-34e8-11e9-8ee5-000c295aa226:5
Executed_Gtid_Set: d589b398-34e8-11e9-8ee5-000c295aa226:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
以上验证了主从的一致性
扫一扫
1624
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
