mysql高可用配置(一)
一、简介
MySQL使用双向半同步复制模式，通过开源的keepalived实现自动切换，应用通过vip连接数据库。配合自定义脚本，实现故障安全切换，切换过程对应用透明。
二、部署主从
2.1、在主备节点部署：
[root@hzyg-ip-bd32 mnt]# tar xf percona-xtrabackup-2.4.20-Linux-x86_64.el7.tar.gz ##主节点上传包并解压
2.2、备份主库数据：
安装的xtrabackup,bin路径下执行该命令，其中defaults-file参数填写主库的my.cnf文件路径，user参数填写登录数据库用户名，password是登录数据库用户的密码，/mnt/data/backup表示将备份的数据存放在什么路径下。
[root@hzyg-ip-bd32 bin]# ./innobackupex --defaults-file=/etc/my.cnf --user=root --password=h7fXy0%z#Ci2 --no-timestamp /mnt/data/backup/
查看位点及所在binlog文件
[root@hzyg-ip-bd32 backup]# cat xtrabackup_binlog_info binlog.000002 6975146 [root@hzyg-ip-bd32 backup]#
2.3、应用备份期间产生的日志
此步骤在目标点和源端执行均可,还是在bin路径下执行，执行完毕后，将/mnt/data/backup/拷贝到从库
./innobackupex --apply-log /mnt/data/backup/
2.4、停止从数据库
[root@hzyg-ip-bd33 ~]# systemctl stop mysqld
确认全备已应用日志（主要看backup_type）
[root@hzyg-ip-bd33 backup]# cat xtrabackup_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 2235041313 last_lsn = 2235041322 compact = 0 recover_binlog_info = 0 flushed_lsn = 2235041322 [root@hzyg-ip-bd33 backup]#
查看位点
[root@hzyg-ip-bd33 backup]# cat xtrabackup_info uuid = b82a5192-9ea3-11ec-bb9a-6805cad60b78 name = tool_name = innobackupex tool_command = --defaults-file=/etc/my.cnf --user=root --password=... --no-timestamp /mnt/data/backup/ tool_version = 2.4.20 ibbackup_version = 2.4.20 server_version = 5.7.35-log start_time = 2022-03-08 13:50:52 end_time = 2022-03-08 13:50:54 lock_time = 0 binlog_pos = filename 'binlog.000002', position '6975146' innodb_from_lsn = 0 innodb_to_lsn = 2235041313 partial = N incremental = N format = file compact = N compressed = N encrypted = N [root@hzyg-ip-bd33 backup]#
2.5、全量数据恢复到从库
[root@hzyg-ip-bd33 backup]# ./innobackupex --defaults-file=/etc/my.cnf --copy-back /mnt/data/backup/
备注：原来的data目录可以移走，数据恢复以后检查下/data目录下文件的属主属组权限都为mysql，不然mysql启动报错
2.6、启动备库
[root@hzyg-ip-bd33 backup]# systemctl start mysqld
2.7、配置双向半同步复制
由于我们未开启GTID,根据上文中查看的binlog文件、位点进行同步 ，其中MASTER_LOG_FILE参数填写binlog文件名，MASTER_LOG_POS参数填写具体位点。
创建复制账号
grant replication slave,replication client on *.* to repuser@'%' identified by 'repuser';
备库执行，建立复制关系：
change master to master_host='hzyg-ip-bd32',master_port=3306,master_user='repuser',master_password='repuser',master_auto_position=1;
CHANGE MASTER TO MASTER_HOST='hzyg-ip-bd32', MASTER_PORT=3306, MASTER_USER='repuser', MASTER_PASSWORD='repuser', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=6795146;
主库也要执行，建立复制关系，开启双向复制：
change master to master_host='hzyg-ip-bd33',master_port=3306,master_user='repuser',master_password='repuser',master_auto_position=0;
开启主从；
start slave
2.8、检查主从同步
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: hzyg-ip-bd32 Master_User: repuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000005 Read_Master_Log_Pos: 1743713 Relay_Log_File: hzyg-ip-bd33-relay-bin.000015 Relay_Log_Pos: 1740851 Relay_Master_Log_File: binlog.000005 Slave_IO_Running: Yes 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: 1743713 Relay_Log_Space: 1741065 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: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 323306 Master_UUID: d68b624b-6eae-11ec-8671-6805cad60b78 Master_Info_File: /data1/mysql/data/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: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified mysql>
备注：
slave_io_running和slave_sql_running显示yes,seconds_behind_master为0时，表示主从已经恢复
