MySQL安装
[root@mysql ~]# tar zxf mysql-boost-5.7.44.tar.gz
[root@mysql ~]# du -sh mysql-5.7.44/
475M mysql-5.7.44/
[root@mysql ~]# cd mysql-5.7.44/
[root@mysql mysql-5.7.44]# yum install cmake -y
[root@mysql mysql-5.7.44]# yum install gcc gcc-c++ -y
[root@mysql mysql-5.7.44]# yum install openssl-devel pakage -y
[root@mysql mysql-5.7.44]# yum install ncurses-devel -y
#清除缓存，当你安装好依赖性后依旧报错
[root@mysql mysql-5.7.44]# rm -rf CMakeCache.txt
[root@mysql ~]# yum install libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm -y
#编译
[root@mysql mysql-5.7.44]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/
#双核下载
[root@mysql mysql-5.7.44]# make -j2
#安装
[root@mysql mysql-5.7.44]# make install部署MySQL
拷贝数据库到另一台主机
[root@mysql3 ~]# useradd -s /sbin/nologin -M mysql [root@mysql3 ~]# mkdir -p /data/mysql [root@mysql3 ~]# chown mysql.mysql -R /data/mysql/ [root@mysql3 ~]# cd /usr/local/ [root@mysql3 local]# cd mysql/support-files/ [root@mysql3 support-files]# cp mysql.server /etc/init.d/mysqld
有很多的链接，所以该怎么传？
[root@mysql2 local]# rsync -al -r mysql root@172.25.254.30:/usr/local/
被传 检查一下
[root@mysql3 ~]# cd /usr/local/ [root@mysql3 local]# du -sh mysql/ 1.9G mysql/
修改配置文件
[root@mysql3 local]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 server-id=30 [root@mysql3 local]# vim ~/.bash_profile PATH=$PATH:$HOME/bin:/usr/local/mysql/bin [root@mysql3 local]# source ~/.bash_profile
初始化有问题
#删除里面的内容 [root@mysql3 support-files]# cd /data/mysql/ [root@mysql3 mysql]# ll [root@mysql3 mysql]# rm -rf *
开始初始化
[root@mysql3 mysql]# mysqld --user mysql --initialize 2024-08-22T08:10:28.783404Z 1 [Note] A temporary password is generated for root@localhost: =WPU9lJ>YDb5 #密码>文件 [root@mysql3 ~]# echo "=WPU9lJ>YDb5" > passwd
启动MySQL
方式一： [root@mysql3 ~]# /etc/init.d/mysqld start Starting MySQL.Logging to '/data/mysql/mysql3.example.org.err'. SUCCESS! 方式二： [root@mysql3 support-files]# chkconfig mysqld on [root@mysql3 support-files]# chkconfig --list
安全初始化
[root@mysql3 ~]# mysql_secure_installation
实验遇到的问题
在MySQL中主从同步的时候产生的：原因是我删除了主库中从库没有的数据库，我也没想到还会导致集群宕机，解决方法就是让指针往下移动
mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
组从复制
配置master
设置server-id
[root@mysql2 local]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 server-id=20 log-bin=mysql-bin #重启生效 [root@mysql3 ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
查看server-id
[root@mysql3 ~]# mysql -uroot -plee -e "select @@server_id" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 30 |
二进制
[root@mysql3 ~]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 server-id=20 log-bin=mysql-bin [root@mysql3 ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
检查是否生成二进制文件
[root@mysql2 mysql]# cd /data/mysql/ [root@mysql2 mysql]# ls auto.cnf ib_logfile1 mysql.sock.lock ca-key.pem ibtmp1 performance_schema ca.pem mysql private_key.pem client-cert.pem mysql2.example.org.err public_key.pem client-key.pem mysql2.example.org.pid server-cert.pem ib_buffer_pool mysql-bin.000001 server-key.pem ibdata1 mysql-bin.index sys ib_logfile0 mysql.sock
作用：记录动作
登录MySQL
建立同步使用的用户
mysql> CREATE USER repl@'%' IDENTIFIED BY 'lee'; mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 878 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
实验
建立库
mysql> create database example; Query OK, 1 row affected (0.00 sec)
建立表
mysql> create table example.userlist( username varchar(10) not null, -> password varchar(50) not null -> ); Query OK, 0 rows affected (0.00 sec)
插入值
mysql> insert into example.userlist values ('lee','123');
Query OK, 1 row affected (0.01 sec)
slave端
同步日志
mysql> change master to master_host='172.25.254.20',master_user='repl',master_password='lee',master_log_file='mysql-bin.000004',master_log_pos=1145; 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: 172.25.254.20 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 1145 Relay_Log_File: mysql3-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes
10从查看
拉平数据
生产环境中备份时需要锁表，保证数据备份前后的数据一致
主端备份
[root@mysql2 ~]# mysqldump -uroot -p example > example.sql [root@mysql2 ~]# cat example.sql
拷贝
[root@mysql2 ~]# scp example.sql root@172.25.254.30:/mnt/ root@172.25.254.30's password: example.sql 100% 1979 2.4MB/s 00:00
slave端
[root@mysql3 ~]# cd /mnt/ [root@mysql3 mnt]# ls example.sql [root@mysql3 mnt]# mysql -uroot -plee -e "create database example;" [root@mysql3 mnt]# mysql -uroot -plee example < example.sql mysql> change master to master_host='172.25.254.20',master_user='repl',master_password='lee',master_log_file='mysql-bin.000004',master_log_pos=1145; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec)
在主端添加数据
mysql> insert example.userlist values ('user2','123');
Query OK, 1 row affected (0.00 sec)
slave端查看
写入＞读取 主比较多 购物平台
写入＜ 读取 从比较多 微博
延迟复制
一般一台就够了，用来防止误删，在这个时间内进行恢复
slave
mysql> STOP SLAVE SQL_THREAD; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_delay=60; Query OK, 0 rows affected (0.00 sec) mysql> start slave sql_thread; Query OK, 0 rows affected (0.00 sec)
主端
mysql> delete from example.userlist where username='user1'; Query OK, 0 rows affected (0.00 sec)
延迟端数据备份
[root@mysql3 mnt]# mysql -uroot -plee example > 2.sql
慢查询日志
一条语句执行超过10s
原因：库表设计不合理，数据庞大
重启生效
[root@mysql2 ~]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 server-id=20 slow_query_log=on log-bin=mysql-bin [root@mysql2 ~]# /etc/init.d/mysqld restart Shutting down MySQL............ SUCCESS! Starting MySQL. SUCCESS!
立即生效
mysql> SET GLOBAL slow_query_log=ON; Query OK, 0 rows affected (0.00 sec) mysql> show variables like "slow%"; +---------------------+-----------------------------+ | Variable_name | Value | +---------------------+-----------------------------+ | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /data/mysql/mysql2-slow.log | +---------------------+-----------------------------+ 3 rows in set (0.01 sec)
MySQL的并行复制
如果数据复制的比较慢，数据库的差异就比较大，所以某一些企业会要求强一致性！
默认情况下slave使用的是sql单线程回放，会导致主从延迟
就让slave启用多线程！
一台延迟，其他主机得保持高度一致
从端 10
[root@mysql ~]# vim /etc/my.cnf [root@mysql ~]# [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 server-id=10 gtid_mode=ON enforce-gtid-consistency=ON slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 master_info_repository=TABLE relay_log_recovery=ON #日志回放恢复功能开启 [root@mysql ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!
组从原理
三个线程
实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作，
一个主库线程，两个从库线程。
二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以
将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之
后，再将锁释放掉。
从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库
的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。
从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同
步。
gtid模式
主20 从30
[root@mysql2 ~]# vim /etc/my.cnf gtid_mode=ON enforce-gtid-consistency=ON [root@mysql2 ~]# /etc/init.d/mysqld restart Shutting down MySQL............ SUCCESS! Starting MySQL. SUCCESS!
主端查看
[root@mysql2 ~]# mysqlbinlog -vv /data/mysql/mysql-bin.000006 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ;
slave重新设定
mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host='172.25.254.20',master_user='repl',master_password='lee',MASTER_AUTO_POSITION=1; mysql> start slave; Query OK, 0 rows affected (0.03 sec)
半同步
半同步：等----到达后---才做----
主端
[root@mysql-node1 ~]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock server-id=1 log-bin=mysql-bin gtid_mode=ON enforce-gtid-consistency=ON rpl_semi_sync_master_enabled=1 #开启半同步功能 symbolic-links=0 [root@mysql-node1 ~]# mysql -p lee #安装半同步插件 mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; mysql> select * from information_schema.PLUGINS where PLUGIN_NAME LIKE'%semi%'\G *************************** 1. row *************************** PLUGIN_NAME: rpl_semi_sync_master PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: REPLICATION PLUGIN_TYPE_VERSION: 4.0 PLUGIN_LIBRARY: semisync_master.so PLUGIN_LIBRARY_VERSION: 1.7 PLUGIN_AUTHOR: He Zhenxing PLUGIN_DESCRIPTION: Semi-synchronous replication master PLUGIN_LICENSE: GPL LOAD_OPTION: ON 1 row in set (0.00 sec) mysql> show variables like 'rpl_semi_sync%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | +-------------------------------------------+------------+ 6 rows in set (0.02 sec) # AFTER_SYNC 半同步
从端
[root@mysql-node1 ~]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock server-id=1 log-bin=mysql-bin gtid_mode=ON enforce-gtid-consistency=ON rpl_semi_sync_master_enabled=1 #开启半同步功能 symbolic-links=0 [root@mysql-node1 ~]# mysql -p lee #安装半同步插件 mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
模拟故障
mysql> stop slave IO_THREAD; Query OK, 0 rows affected (0.01 sec) mysql> start slave IO_THREAD; Query OK, 0 rows affected (0.00 sec)
半同步模式中，slave全部down后会不能写入数据，但是会自动开启异步模式，默认10s ，就可以写入数据。
当slave恢复后，会自动恢复半同步模式。
MySQL高可用
组复制MGR
配置文件
[root@mysql2 ~]# vim /etc/my.cnf log_bin=binlog gtid_mode=ON enforce-gtid-consistency=ON disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON binlog_format=ROW plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address="172.25.254.20:33061" group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061" group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8" group_replication_bootstrap_group=off group_replication_single_primary_mode=OFF group_replication_enforce_update_everywhere_checks=ON group_replication_allow_local_disjoint_gtids_join=1 log_bin=binlog disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON binlog_format=ROW plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address="172.25.254.20:33061" group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061" #组里面总的数据库 group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8" #允许链接的网段 group_replication_bootstrap_group=off #只能手动开启，初始化建组的时候！ group_replication_single_primary_mode=OFF group_replication_enforce_update_everywhere_checks=ON group_replication_allow_local_disjoint_gtids_join=1
数据初始化
主端 [root@mysql-node10 ~]# /etc/init.d/mysqld stop [root@mysql-node10 ~]# rm -rf /data/mysql/* [root@mysql-node10 ~]# mysqld --user=mysql --initialize [root@mysql-node10 ~]# /etc/init.d/mysqld start [root@mysql-node10 ~]# mysql -uroot -p初始化后生成的密码 -e "alter user root@localhost identified by 'lee';"
关闭日志-创建-打开日志
#关闭日志记录功能，一下操作不记录 mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'%' IDENTIFIED BY 'lee'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lee' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected, 1 warning (2.17 sec) mysql> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec) mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+--------------------+-------------+--------------+ | group_replication_applier | ed197966-61db-11ef-9c50-000c2938b8ba | mysql2.example.org | 3306 | ONLINE | +---------------------------+--------------------------------------+--------------------+-------------+--------------+ 1 row in set (0.00 sec)
域名解析
三台主机都做
[root@mysql2 ~]# vim /etc/hosts 172.25.254.20 mysql2.example.org 172.25.254.10 mysql.example.org 172.25.254.30 mysql3.example.org
配置组数据库
复制配置文件到10 30
[root@mysql2 ~]# scp /etc/my.cnf root@172.25.254.10:/etc/my.cnf [root@mysql2 ~]# scp /etc/my.cnf root@172.25.254.30:/etc/my.cnf
根据ip更改配置文件
[mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 server-id=10 slow_query_log=on log_bin=binlog gtid_mode=ON enforce-gtid-consistency=ON disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON binlog_format=ROW plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address="172.25.254.10:33061" group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061" group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8" group_replication_bootstrap_group=off group_replication_single_primary_mode=OFF group_replication_enforce_update_everywhere_checks=ON group_replication_allow_local_disjoint_gtids_join=1
删数据-初始化-改密码-关日志-用户-授权-刷新-打开日志-加组-start
mysql> alter user root@localhost identified by 'lee'; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repl@'%' IDENTIFIED BY 'lee'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lee' FOR CHANNEL -> 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected, 1 warning (5.89 sec) mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+--------------------+-------------+--------------+ | group_replication_applier | 400d45e2-61dd-11ef-bb2e-000c29420ba6 | mysql.example.org | 3306 | ONLINE | | group_replication_applier | ed197966-61db-11ef-9c50-000c2938b8ba | mysql2.example.org | 3306 | ONLINE | +---------------------------+--------------------------------------+--------------------+-------------+--------------+ 2 rows in set (0.00 sec)
30端也是同样的操作，就不重复赘述了
建立表
20端
mysql> create table example.userlist(
-> username varchar(20) primary key not null,
-> password varchar(40) not null
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> DESC example.userlist;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO | PRI | NULL | |
| password | varchar(40) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.08 sec)
mysql> insert into example.userlist values('user1','123');
Query OK, 1 row affected (0.07 sec)
30开启组复制功能
#启动组复制（Group Replication）功能 mysql> START GROUP_REPLICATION; Query OK, 0 rows affected, 1 warning (3.22 sec)
MySQL路由
用于负载均衡
也可以用nginx代替，但是mysql路由更专业
打开MySQL，其他两台
安装mysql路由
[root@mysql2 ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm
编辑配置文件
[root@mysql2 ~]# vim /etc/mysqlrouter/mysqlrouter.conf [routing:ro] bind_address = 0.0.0.0 bind_port = 7001 destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306 routing_strategy = round-robin [root@mysql2 ~]# systemctl start mysqlrouter.service [root@mysql2 ~]# netstat -antlupe | grep 7001 tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 988 54378 4412/mysqlroute
修改远程访问用户其他两台10 30
mysql> create user root@'%' identified by 'lee'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to root@'%'; Query OK, 0 rows affected (0.00 sec)
测试
[root@mysql2 ~]# mysql -uroot -plee -h 172.25.254.20 -P 7001 mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 20 | +-------------+ 1 row in set (0.00 sec) 第二次登录 [root@mysql2 ~]# mysql -uroot -plee -h 172.25.254.20 -P 7001 mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 10 | +-------------+ 1 row in set (0.01 sec)
MHA
在用的不是很多了
解决的的是单点故障问题
版本：RHEL7
172.25.254.50
安装
[root@mha ~]# ls anaconda-ks.cfg initial-setup-ks.cfg Music Videos Desktop master_ip_failover Pictures Documents master_ip_online_change Public Downloads MHA-7.zip Templates [root@mha ~]# unzip MHA-7.zip
免密认证
[root@mha MHA-7]# ssh-keygen Generating public/private rsa key pair. Enter file in which to save the key (/root/.ssh/id_rsa): Created directory '/root/.ssh'. Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /root/.ssh/id_rsa. Your public key has been saved in /root/.ssh/id_rsa.pub. The key fingerprint is: SHA256:gthck7Fs1z5D5s3IjPuCcuPfgl6d93XlvNwvBlJ0TgM root@mha.exmaple.org The key's randomart image is: +---[RSA 2048]----+ | . E. | | . + . . + | | B . +. + . | | + + o O +. . | | . + . S O.o .| | . o.+. o.| | oo o... =| | . =.oo . .+.+| | =oo.oo ..++| +----[SHA256]-----+ [root@mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.10 /usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub" The authenticity of host '172.25.254.10 (172.25.254.10)' can't be established. ECDSA key fingerprint is SHA256:pC51ks+z966hoeJX/rHp+k7sYWGJczd9HAqbq5ezjbQ. ECDSA key fingerprint is MD5:64:35:ae:bb:e4:fd:64:b7:46:f6:cd:9f:a8:52:3e:c6. Are you sure you want to continue connecting (yes/no)? yes /usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed /usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys root@172.25.254.10's password: Number of key(s) added: 1 Now try logging into the machine, with: "ssh 'root@172.25.254.10'" and check to make sure that only the key(s) you wanted were added.
域名解析
[root@mha MHA-7]# vim /etc/hosts 172.25.254.50 mha.exmaple.org 172.25.254.10 mysql1.exmaple.org 172.25.254.20 mysql2.example.org 172.25.254.30 mysql3.example.org
免密认证
50对10-20-30
[root@mha ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.10 [root@mha ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.20 [root@mha ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.30
还原配置文件
10-20-30
[mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 server-id=30 log-bin=mysql-bin gtid_mode=ON enforce-gtid-consistency=ON
停数据库 清理数据 初始化 启动数据库 修改密码 同步用户 授权
master端
[root@mysql2 ~]# /etc/init.d/mysqld stop [root@mysql2 ~]# rm -fr /data/mysql/* [root@mysql2 ~]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock server-id=1 log-bin=mysql-bin gtid_mode=ON log_slave_updates=ON enforce-gtid-consistency=ON symbolic-links=0 [root@mysql2 ~]# mysqld --user mysql --initialize [root@mysql2 ~]# /etc/init.d/mysqld start [root@mysql2 ~]# mysql_secure_installation [root@mysql2 ~]## mysql -p mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'lee'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to root@localhost; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to root@'%'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Query OK, 0 rows affected (0.02 sec) mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; Query OK, 0 rows affected (0.00 sec)
slave
20 30
停数据库 清理数据 初始化 启动数据库 修改密码 同步用户 授权
#在slave1和slave2中 [root@mysql-node10 & 30 ~]# /etc/init.d/mysqld stop [root@mysql-node10 & 30 ~]# rm -fr /data/mysql/* [root@mysql-node10 & 30 ~]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock server-id=1 log-bin=mysql-bin gtid_mode=ON log_slave_updates=ON enforce-gtid-consistency=ON symbolic-links=0 [root@mysql-node20 & 30 ~]# mysqld --user mysql --initialize [root@mysql-node20 & 300 ~]# /etc/init.d/mysqld start [root@mysql-node20 & 30 ~]# mysql_secure_installation [root@mysql-node20 & 30 ~]# mysql -p mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl', MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected (0.01 sec) mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1; Query OK, 0 rows affected (0.00 sec) mysql> STOP SLAVE IO_THREAD; Query OK, 0 rows affected (0.00 sec) mysql> START SLAVE IO_THREAD; Query OK, 0 rows affected (0.00 sec) mysql> SHOW STATUS LIKE 'Rpl_semi_sync%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.01 sec)
50mha
解压包+拷贝节点
#在MHA中 [root@mysql-mha ~]# unzip MHA-7.zip [root@mysql-mha MHA-7]# ls mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-Mail-Sender-0.8.23- 1.el7.noarch.rpm mha4mysql-manager-0.58.tar.gz perl-Mail-Sendmail-0.79- 21.el7.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm perl-MIME-Lite-3.030- 1.el7.noarch.rpm perl-Config-Tiny-2.14-7.el7.noarch.rpm perl-MIME-Types-1.38- 2.el7.noarch.rpm perl-Email-Date-Format-1.002-15.el7.noarch.rpm perl-Net-Telnet-3.03- 19.el7.noarch.rpm perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm perl-Parallel-ForkManager-1.18- 2.el7.noarch.rpm [root@mysql-mha MHA-7]# yum install *.rpm -y [root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.10:/mnt [root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.20:/mnt [root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.30:/mnt
node 节点
[root@mysql1 ~]# yum install /mnt/mha4mysql-node-0.58- 0.el7.centos.noarch.rpm -y [root@mysql2 ~]# yum install /mnt/mha4mysql-node-0.58- 0.el7.centos.noarch.rpm -y [root@mysql3 ~]# yum install /mnt/mha4mysql-node-0.58- 0.el7.centos.noarch.rpm -y
MHA配置环境
#生成配置文件 [root@mysql-mha ~]# mkdir /etc/masterha [root@mysql-mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz [root@mysql-mha MHA-7]# cd mha4mysql-manager-0.58/samples/conf/ [root@mysql-mha conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf
配置文件
[root@mha ~]# vim /etc/masterha/app1.cnf [server default] user=root password=lee ssh_user=root repl_user=repl repl_password=lee master_binlog_dir= /data/mysql remote_workdir=/tmp secondary_check_script= masterha_secondary_check -s172.25.254.20 -s 172.25.254.11 ping_interval=3 # master_ip_failover_script= /script/masterha/master_ip_failover # shutdown_script= /script/masterha/power_manager # report_script= /script/masterha/send_report # master_ip_online_change_script= /script/masterha/master_ip_online_change [server default] manager_workdir=/etc/masterha manager_log=/etc/masterha/manager.log [server1] hostname=172.25.254.20 candidate_master=1 check_repl_delay=0 [server2] hostname=172.25.254.30 candidate_master=1 check_repl_delay=0 [server3] hostname=172.25.254.10 no_master=1
权限，允许远程连接,只用主机做就可以了
mysql> create user root@'%' identified by 'lee'; mysql> grant all on *.* to root@localhost; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to root@'%'; Query OK, 0 rows affected (0.01 sec)
20增加ip11
[root@mysql2 ~]# ip a a 172.25.254.11/24 dev eth0
测试结果
[root@mha masterha]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
故障切换
mha
[root@mysql-mha ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.30 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
故障手动切换
master挂了
在MHA-master中做故障切换 [root@mysql-mha masterha]# masterha_master_switch --master_state=dead -- conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.20 --dead_master_port=3306 --new_master_host=172.25.254.30 --new_master_port=3306 --ignore_last_failover
再去20端口切换主机
锁文件：在一次切换之后把状态锁住，下一次转换时要删除锁文件
自动切换的时候要删掉
.complete
手动切换
关闭master服务
[root@mysql2 ~]# /etc/init.d/mysqld stop Shutting down MySQL........... SUCCESS!
MHA
删除锁文件
锁文件：在一次切换之后把状态锁住，下一次转换时要删除锁文件
自动切换的时候要删掉.complete
[root@mha ~]# cd /etc/masterha [root@mha masterha]# ls app1.cnf app1.failover.complete mha4mysql-manager-0.58 [root@mha masterha]# rm -rf *.complete [root@mha masterha]# ls app1.cnf mha4mysql-manager-0.58
执行命令
[root@mha ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.20 --dead_master_port=3306 --new_master_host=172.25.254.30 --new_master_port=3306 --ignore_last_failover ----- Failover Report ----- app1: MySQL Master failover 172.25.254.20(172.25.254.20:3306) to 172.25.254.30(172.25.254.30:3306) succeeded Master 172.25.254.20(172.25.254.20:3306) is down! Check MHA Manager logs at mha.exmaple.org for details. Started manual(interactive) failover. Selected 172.25.254.30(172.25.254.30:3306) as a new master. 172.25.254.30(172.25.254.30:3306): OK: Applying all logs succeeded. 172.25.254.10(172.25.254.10:3306): OK: Slave started, replicating from 172.25.254.30(172.25.254.30:3306) 172.25.254.30(172.25.254.30:3306): Resetting slave info succeeded. Master failover to 172.25.254.30(172.25.254.30:3306) completed successfully.
测试结果
故障恢复
[root@mysql2 ~]# /etc/init.d/mysqld start Starting MySQL.. SUCCESS! [root@mysql2 ~]# mysql -uroot -plee mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.30', MASTER_USER='repl', MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1; Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec)
测试结果
自动切换
MHA
删除锁文件
锁文件：在一次切换之后把状态锁住，下一次转换时要删除锁文件
自动切换的时候要删掉.complete
[root@mha ~]# cd /etc/masterha [root@mha masterha]# ls app1.cnf app1.failover.complete mha4mysql-manager-0.58 [root@mha masterha]# rm -rf *.complete [root@mha masterha]# ls app1.cnf mha4mysql-manager-0.58
执行命令
[root@mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf
master端
检查防火墙
[root@mysql3 ~]# iptables -nL Chain INPUT (policy ACCEPT) target prot opt source destination Chain FORWARD (policy ACCEPT) target prot opt source destination Chain OUTPUT (policy ACCEPT) target prot opt source destination #有的话停止服务 [root@mysql3 ~]# iptables -F
停止mysql服务
[root@mysql2 ~]# /etc/init.d/mysqld stop Shutting down MySQL............ SUCCESS!
预备master 端
[server1] hostname=172.25.254.20 candidate_master=1 check_repl_delay=0 [server2] hostname=172.25.254.30 candidate_master=1 check_repl_delay=0 [server3] hostname=172.25.254.10 no_master=1
测试结果
故障前
故障后
记得做故障恢复。
VIP
修改配置文件
#上传在群中发给大家的脚本 [root@mysql-mha ~]# ls master_ip_failover master_ip_online_change MHA-7 MHA-7.zip [root@mysql-mha ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/ [root@mysql-mha ~]# chmod +x /usr/local/bin/master_ip_* #修改脚本在脚本中只需要修改下vip即可 [root@mysql-mha ~]# vim /usr/local/bin/master_ip_failover my $vip = '172.25.254.100/24'; my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0"; my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0"; [root@mysql-mha ~]# vim /usr/local/bin/master_ip_online_change my $vip = '172.25.254.100/24'; my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0"; my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0"; my $exit_code = 0; [root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf & 启动监控程序
删除锁文件
[root@mha masterha]# ls app1.cnf app1.failover.complete manager.log mha4mysql-manager-0.58 [root@mha masterha]# rm -rf app1.failover.complete [root@mha masterha]# rm -rf manager.log
修改主配置文件
[root@mha masterha]# vim /etc/masterha/app1.cnf master_ip_failover_script= /usr/local/bin/master_ip_failover # shutdown_script= /script/masterha/power_manager # report_script= /script/masterha/send_report master_ip_online_change_script= /usr/local/bin/master_ip_online_change
把vip添加到主端 20
[root@mysql2 ~]# ip a a 172.25.254.100/24 dev eth0
检测一主两从的架构+自动切换
[root@mha masterha]#masterha_check_repl --conf=/etc/masterha/app1.cnf [root@mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf
vip会随着master的迁移一起迁移
故障恢复
[root@mysql2 ~]# /etc/init.d/mysqld start Starting MySQL. SUCCESS! mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.30', MASTER_USER='repl', -> MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
扫一扫
42万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
