# oz-demo-mysql-1 **Repository Path**: orangezh/oz-demo-mysql-1 ## Basic Information - **Project Name**: oz-demo-mysql-1 - **Description**: mysql集群环境搭架等 - **Primary Language**: Java - **License**: Apache-2.0 - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 2 - **Created**: 2020-12-08 - **Last Updated**: 2021-03-17 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # oz-demo-mysql-1 #### 介绍 mysql集群环境搭架等 # MySql安装 - 上传mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar ```shell rz ``` - 解压mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar ```shell tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar ``` - 检查是否已有Mysql安装，已有就移除 ```shell #检查 rpm -qa | grep mariadb #移除 rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps ``` - 安装mysql-community-common-5.7.28-1.el7.x86_64.rpm ```shell rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm ``` - 安装mysql-community-libs-5.7.28-1.el7.x86_64.rpm和mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm ```shell rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm ``` - 安装客户端 mysql-community-client-5.7.28-1.el7.x86_64.rpm ```shell rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm ``` - 安装服务端mysql-community-server-5.7.28-1.el7.x86_64.rpm ```shell rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm ``` - 安装开发包 mysql-community-devel-5.7.28-1.el7.x86_64.rpm ```shell rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm ``` - 初始化Mysql ```shell mysqld --initialize --user=mysql ``` - 查看root默认密码 ```shell cat /var/log/mysqld.log # 密码：nA;lSPz-6tu( ``` - 启动Mysql ```shell #启动 systemctl start mysqld.service #查看状态 systemctl status mysqld.service ``` - 登录Mysql ```shell mysql -uroot -p # 密码：nA;lSPz-6tu( ``` - 修改数据库密码 ```mysql #修改密码 set password=password('123456'); #退出mysql exit ``` ```shell #重新登录 mysql -uroot -p # 密码：123456 ``` - 关闭服务器防火墙 ```shell systemctl stop iptables systemctl stop firewalld systemctl disable firewalld.service ``` - 设置MySql允许远程访问 ```mysql #设置远程访问 use mysql; update user set host='%' where user='root'; select host,user from user; grant all privileges on *.* to 'root'@'%' identified by 'root'; flush privileges; #授权用户testuser1访问 grant all privileges on *.* to 'root'@'%' identified by 'testuser1' with grant option; flush privileges; #授权用户testuser2从指定ip(9.9.8.171)访问 grant all privileges on *.* to 'root'@'%9.9.8.171' identified by 'testuser2' with grant option; flush privileges; ``` # MySql集群部署 ## 主从同步 - 修改主库my.cnf配置文件 ```shell vim /etc/my.cnf log_bin=mysql-bin #开启binlog,文件名称为mysql-bin server-id=1 #指定server-id sync-binlog=1 #执行几次后进行磁盘同步 1就代表次数 #忽略以下库的同步 binlog-ignore-db=performance_schema binlog-ignore-db=information_schema binlog-ignore-db=sys #指定同步的库 不设置就是同步所有库 binlog-do-db=test ``` ```shell #重启MySql systemctl restart mysqld ``` **在[mysqld]后添加skip-grant-tables（使用 set password for设置密码无效，且此后登录无需键入密码）** - 主库授权 ```shell #登录MySQL mysql -uroot -p # 密码：123456 ``` ```mysql #主库授权设置 grant replication slave on *.* to 'root'@'%' identified by 'root'; grant all privileges on *.* to 'root'@'%' identified by 'root'; #刷新权限，立即生效 flush privileges; #查看主库状态 show master status; ``` - 从库配置修改 ```shell vim /etc/my.cnf server-id=2 relay_log=mysql-relay-bin #relay-log名称 read_only=1 #此库只读 ``` - 从库启动并授权 ```mysql #设置复制的主库 change master to master_host='9.9.9.144',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000004',master_log_pos=154; #查看从库状态 show slave status; #开启从库 start slave; #停止从库 stop slave; ``` ## 半同步复制 - 主库半同步复制设置 ```mysql #是否支持动态加载 select @@have_dynamic_loading; #查看插件列表 show plugins; #安装半同步复制插件 install plugin rpl_semi_sync_master soname 'semisync_master.so'; #查看半同步复制相关参数 show variables like '%semi%'; #开启半同步复制 set global rpl_semi_sync_master_enabled=1; #设置超时时间 set global rpl_semi_sync_master_timeout=1000; ``` - 从库半同步复制设置 ```mysql use mysql #是否支持动态加载 select @@have_dynamic_loading; #查看插件列表 show plugins; #安装半同步复制插件 install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #查看半同步复制相关参数 show variables like '%semi%'; #开启半同步复制 set global rpl_semi_sync_slave_enabled=1; #重新加载从库 stop slave; start slave; ``` - 查看是否开启了半同步复制 ```shell cat /var/log/mysqld.log #查看日志是否有以下信息 Start semi-sync binlog_dump to slave ``` ## 双主复制 - master1设置 ```shell vim /etc/my.cnf relay_log=mysql-relay-bin #relay_log名称 log_slave_updates=1 #双主双写自增主键设置 1，3，5，7... 双主单写不用设置 auto_increment_offset=1 #自动递增 auto_increment_increment=2 #递增量 ``` - master2设置 ```shell vim /etc/my.cnf log_bin=mysql-bin #开启binlog,文件名称为mysql-bin server-id=3 #指定server-id sync-binlog=1 #执行几次后进行磁盘同步 1就代表次数 #忽略以下库的同步 binlog-ignore-db=performance_schema binlog-ignore-db=information_schema binlog-ignore-db=sys relay_log=mysql-relay-bin #relay_log名称 log_slave_updates=1 #双主双写自增主键设置 2，4，6，8... 双主单写不用设置 auto_increment_offset=2 #自动递增 auto_increment_increment=2 #递增量 ``` - master2授权设置 ```mysql #主库授权设置 grant replication slave on *.* to 'root'@'%' identified by 'root'; grant all privileges on *.* to 'root'@'%' identified by 'root'; #刷新权限，立即生效 flush privileges; #查看主库状态 show master status; ``` - master1和master2互复制设置 ```mysql #设置复制的master1主库为master2 change master to master_host='9.9.9.144',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000004',master_log_pos=154; #设置复制的master2主库为master1 change master to master_host='9.9.9.226',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=884; ``` # MHA部署 ## MHA安装 - 机器说明 ```shell 9.9.9.144 master 9.9.9.6 slave1 9.9.10.16 slave2 9.9.9.173 MHA Manager ``` 部署双从遇到的问题：server_uuid相同导致的问题 ```shell 错误描述：A slave with the same server_uuid/server_id as this slave has connected to the master; 即存在两个或两个以上slave的server_uuid/server_id相同 解决办法： show variables like '%server%id%'; 查看server_uuid/server_id是否有相同 1、如果server_id相同，修改my.cnf配置文件参数server_id 2、如果server_uuid相同，请删除auto.cnf文件(auto.cnf文件在/etc/my.cnf中datadir配置的目录下，默认/var/lib/mysql)，然后重启数据库，数据库会重新生成server_uuid和auto.cnf文件 ``` - 安装依赖 ```shell yum install perl-DBI -y yum install perl-DBD-MySQL -y yum install perl-Config-Tiny -y yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm yum install perl-Log-Dispatch -y yum install perl-Parallel-ForkManager -y ``` - 在master、slave1、slave2和mha机器上安装mha4mysql-node-0.58-0.el7.centos.noarch.rpm ```shell rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm ``` - 在MHA机器上安装mha4mysql-manager ```shell rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm ``` ## MHA配置 - ssh免密设置 ```shell #MHA 9.9.9.173 ssh-keygen -t rsa ssh-copy-id -i /root/.ssh/id_rsa root@9.9.9.144 ssh-copy-id -i /root/.ssh/id_rsa root@9.9.9.6 ssh-copy-id -i /root/.ssh/id_rsa root@9.9.10.16 #master 9.9.9.144 ssh-keygen -t rsa ssh-copy-id -i /root/.ssh/id_rsa root@9.9.9.6 ssh-copy-id -i /root/.ssh/id_rsa root@9.9.10.16 #slave1 9.9.9.6 ssh-keygen -t rsa ssh-copy-id -i /root/.ssh/id_rsa root@9.9.9.144 ssh-copy-id -i /root/.ssh/id_rsa root@9.9.10.16 #slave2 9.9.10.16 ssh-keygen -t rsa ssh-copy-id -i /root/.ssh/id_rsa root@9.9.9.144 ssh-copy-id -i /root/.ssh/id_rsa root@9.9.9.6 ``` - MHA配置 ```shell mkdir -p /etc/mha vim /etc/mha/mha.cnf #下载scripts 并放到/etc/mha/scripts路径下，记得给脚本加执行权限 下载连接：https://github.com/yoshinorim/mha4mysql-manager/samples/scripts [server default] manager_workdir=/etc/mha/ #manager工作目录 manager_log=/etc/mha/manager.log #mananger日志 master_binlog_dir=/var/lib/mysql #binlog复制目录 user=root password=root ping_interval=1 remote_workdir=/tmp repl_password=root repl_user=root secondary_check_script= /usr/bin/masterha_secondary_check -s 9.9.9.6 -s 9.9.10.16 --user=root --master_host=9.9.9.144 --master_ip=9.9.9.144 --master_port=3306 master_ip_failover_script=/etc/mha/scripts/master_ip_failover #切换脚本 master_ip_online_change_script=/etc/mha/scripts/master_ip_online_change #手动switchover时候的切换脚本 #shutdown_script="" ssh_user=root [server1] hostname=9.9.9.144 port=3306 candidate_master=1 check_repl_delay=0 [server2] hostname=9.9.9.6 port=3306 candidate_master=1 check_repl_delay=0 [server3] hostname=9.9.10.16 port=3306 ``` - 检查ssh连接和复制状态 ```shell #检查ssh免密连接 masterha_check_ssh --conf=/etc/mha/mha.cnf #检查复制 masterha_check_repl --conf=/etc/mha/mha.cnf ``` 出现的问题： ```shell Mon Dec 7 20:51:45 2020 - [info] /etc/mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=9.9.9.144 --orig_master_ip=9.9.9.144 --orig_master_port=3306 Bareword "FIXME_xxx" not allowed while "strict subs" in use at /etc/mha/scripts/master_ip_failover line 93. Execution of /etc/mha/scripts/master_ip_failover aborted due to compilation errors. Mon Dec 7 20:51:45 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln229] Failed to get master_ip_failover_script status with return code 255:0. Mon Dec 7 20:51:45 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48. Mon Dec 7 20:51:45 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers. Mon Dec 7 20:51:45 2020 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK! 解决方案：/etc/mha/scripts/master_ip_failover line 93.注释掉脚本此行 ``` ```shell Mon Dec 7 02:42:53 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln364] None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf Mon Dec 7 02:42:53 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48. Mon Dec 7 02:42:53 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers. Mon Dec 7 02:42:53 2020 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK! 解决方案：开启从库的log-bin，因为需要主备切换，所以从库也需要开启binlong日志 vim /etc/my.cnf log-bin=mysql-bin ``` ```shell Mon Dec 7 02:51:54 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln359] Slave configurations is not valid. Mon Dec 7 02:51:54 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48. Mon Dec 7 02:51:54 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers. Mon Dec 7 02:51:54 2020 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK! 解决方案： 修改主库配置 relay-log=mysql-relay-bin #开启中继日志 修改从库配置 read_only=1 #启用只读属性 relay_log_purge=0 #是否自动清空不再需要中继日志 log_slave_updates=1 #使得更新的数据写进二进制日志中 ``` ```shell Failed to save binary log: Binlog not found from /data/mysql/binlog/! If you got this error at MHA Manager, please set "master_binlog_dir=/path/to/binlog_directory_of_the_master" correctly in the MHA Manager's configuration file and try again. at /usr/bin/save_binary_logs line 123. eval {...} called at /usr/bin/save_binary_logs line 70 main::main() called at /usr/bin/save_binary_logs line 66 Mon Dec 7 18:57:57 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln161] Binlog setting check failed! Mon Dec 7 18:57:57 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln408] Master configuration failed. Mon Dec 7 18:57:57 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48. Mon Dec 7 18:57:57 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers. Mon Dec 7 18:57:57 2020 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK! 解决方案：mha.cnf配置文件master_binlog_dir参数设置错误 vim /etc/mha/mha.cnf master_binlog_dir=/var/lib/mysql ``` ## MHA启动与停止 - 启动Manager ```shell nohup masterha_manager --conf=/etc/mha/mha.cnf< /dev/null > /etc/mha/manager.log 2>&1 & ``` - 查看状态 ```shell masterha_check_status --conf=/etc/mha/mha.cnf ``` - 停止Manager ```shell masterha_stop --conf=/etc/mha/mha.cnf rm /etc/mha//mha.failover.complete ``` ## MHA测试 1. 查看MHA状态 ```shell masterha_check_status --conf=/etc/mha/mha.cnf mha (pid:38659) is running(0:PING_OK), master:9.9.9.144 ``` 2. 停掉Master数据库 ```shell systemctl stop mysqld ``` 3. 查看MHA状态，此时MHA已经停掉了 ```shell masterha_check_status --conf=/etc/mha/mha.cnf mha is stopped(2:NOT_RUNNING). ``` 4. 进入从库查看从库状态 ```shell show slave status \G Master_Host: 9.9.9.6 #master已切换成了slave1(9.9.9.6) ```