Mysql双主同步
双主同步部署架构图
双主同步实操
-
在cdh1增加配置
[root@cdh1 ~]# vi /etc/my.cnf [mysqld] server-id = 1 log-bin=mysql-bin relay-log = mysql-relay-bin ## 忽略mysql、information_schema库下对表的操作 replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=information_schema.% ## 默认的情况下mysql级联复制是关闭的; log-slave-updates=on ## 复制过程中,有任何错误,直接跳过 slave-skip-errors=all auto-increment-offset=1 auto-increment-increment=2 ## binlog的格式:STATEMENT,ROW,MIXED binlog_format=mixed ## 自动过期清理binlog,默认0天,即不自动清理 expire_logs_days=10
注意,cdh1自增为奇数位:
auto-increment-offset=1 主键自增基数, 从1开始。
auto-increment-increment=2 主键自增偏移量,每次为2。
-
服务器cdh2安装好mysql,增加配置如下:
[root@cdh2 ~]# vi /etc/my.cnf [mysqld] server-id = 2 log-bin=mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=information_schema.% log-slave-updates=on slave-skip-errors=all auto-increment-offset=2 auto-increment-increment=2 binlog_format=mixed expire_logs_days=10
cdh2自增为偶数位:
auto-increment-offset=2 主键自增基数, 从2开始。
auto-increment-increment=2 主键自增偏移量,每次为2。
修改完上述配置后,重启数据库
[root@cdh1 ~]# systemctl restart mysqld.service [root@cdh2 ~]# systemctl restart mysqld.service
-
同步授权配置
在cdh1创建replica用于主从同步的用户
[root@cdh1 ~]# mysql -uroot -p123456 mysql> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica'; ## mysql安装了validate_password密码校验插件,导致要修改的密码不符合密码策略的要求 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements ## 查看当前的密码策略 mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.00 sec) ## 修改密码强度检查等级,0/LOW、1/MEDIUM、2/STRONG。(生产不要这么搞) mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec) ## 修改密码最小长度(生产不要这么搞) mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) ## 查看日志文件与偏移量,开启同步时需要使用 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 614 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
在cdh2创建replica用于主从同步的用户
[root@cdh2 ~]# mysql -uroot -p123456 mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) ## 查询日志文件与偏移量 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 614 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
-
配置主从同步信息
在cdh1(192.168.4.11)中执行:
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 614 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> change master to master_host='192.168.4.12',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=614, master_connect_retry=30; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql>
在cdh2(192.168.4.12)中执行:
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 614 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> change master to master_host='192.168.4.11',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=614, master_connect_retry=30; Query OK, 0 rows affected, 2 warnings (0.01 sec)
-
开启主从同步
cdh1和cdh2分别执行:
mysql> start slave; Query OK, 0 rows affected (0.00 sec)
在cdh1查询同步信息:
show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.4.12 Master_User: replica Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 614 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.%,information_schema.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 614 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 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: 230220 02:58:54 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
发现有错误:
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
这是因为我为了方便,在cdh1中搭建好了mysql,直接克隆到cdh2,所以出现UUID相同的情况
解决:
[root@cdh2 ~]# find / -name auto.cnf /var/lib/mysql/auto.cnf [root@cdh2 ~]# vi /var/lib/mysql/auto.cnf [auto] ## 修改一台机器的UUID即可 server-uuid=133077d3-b0e8-11ed-98f7-000c2919c290 ## 重启mysql [root@cdh2 ~]# systemctl restart mysqld.service
解决上述问题后,cdh1和cdh2重新同步
mysql> start slave; Query OK, 0 rows affected, 1 warning (0.00 sec)
chd1和chd2查看同步状态:
show slave status\G; *************************** 1. row *************************** ...... ## 两个参数都是YES表示成功了 Slave_IO_Running: Yes Slave_SQL_Running: Yes
keepAlived安装和高可用配置
-
在cdh1和cdh2分别安装keepAlived
[root@cdh1 ~]# yum -y install keepalived [root@cdh2 ~]# yum -y install keepalived
-
关闭防火墙
[root@cdh1 ~]# systemctl stop firewalld [root@cdh1 ~]# systemctl disable firewalld
-
设置主机名称
cdh1节点:
[root@cdh1 ~]# hostnamectl set-hostname cdh1
cdh2节点:
[root@cdh2 ~]# hostnamectl set-hostname cdh2
-
cdh1节点配置
[root@cdh1 ~]# cat /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { router_id cdh1 # 机器标识,和主机名保持一致,运行keepalived服务器的一个标识 } vrrp_instance VI_1 { #vrrp实例定义 state BACKUP #lvs的状态模式,MASTER代表主, BACKUP代表备份节点 nopreempt #非抢占模式 interface ens33 #绑定对外访问的网卡,vrrp实例绑定的网卡 virtual_router_id 111 #虚拟路由标示,同一个vrrp实例采用唯一标示 priority 100 #优先级,100代表最大优先级, 数字越大优先级越高 advert_int 1 #master与backup节点同步检查的时间间隔,单位是秒 authentication { #设置验证信息 auth_type PASS #有PASS和AH两种 auth_pass 6666 #验证密码,BACKUP密码须相同 } virtual_ipaddress { #KeepAlived虚拟的IP地址 192.168.4.100 } } virtual_server 192.168.4.100 3306 { #配置虚拟服务器IP与访问端口 delay_loop 6 #健康检查时间 lb_algo rr #负载均衡调度算法, rr代表轮询 lb_kind DR #负载均衡转发规则 DR/NAT/ persistence_timeout 0 #会话保持时间,这里要做测试, 所以设为0, 实际可根据session有效时间配置 protocol TCP #转发协议类型,支持TCP和UDP real_server 192.168.4.11 3306 { #配置服务器节点VIP1 notify_down /opt/mysql/mysql.sh #当服务挂掉时, 会执行此脚本,结束keepalived进程 weight 1 #设置权重,越大权重越高 TCP_CHECK { #状态监测设置 connect_timeout 10 #超时配置, 单位秒 retry 3 #重试次数 delay_before_retry 3 #重试间隔 connect_port 3306 #连接端口, 和上面保持一致 } } }
创建关闭脚本mysql.sh
[root@cdh1 mysql]# vi /opt/mysql/mysql.sh ## 添加脚本内容 pkill keepalived
设置执行权限:
[root@cdh1 mysql]# chmod a+x mysql.sh
-
chd2节点配置
[root@cdh2 ~]# vi /etc/keepalived/keepalived.conf global_defs { router_id cdh2 # 机器标识,和主机名保持一致,运行keepalived服务器的一个标识 } vrrp_instance VI_1 { #vrrp实例定义 state BACKUP #lvs的状态模式,MASTER代表主, BACKUP代表备份节点 nopreempt #非抢占模式 interface ens33 #绑定对外访问的网卡 virtual_router_id 111 #虚拟路由标示,同一个vrrp实例采用唯一标示 priority 98 #优先级,100代表最大优先级, 数字越大优先级越高 advert_int 1 #master与backup节点同步检查的时间间隔,单位是秒 authentication { #设置验证信息 auth_type PASS #有PASS和AH两种 auth_pass 666 #验证密码,BACKUP密码须相同 } virtual_ipaddress { #KeepAlived虚拟的IP地址 192.168.4.100 } } virtual_server 192.168.4.100 3306 { #配置虚拟服务器IP与访问端口 delay_loop 6 #健康检查时间 lb_algo rr #负载均衡调度算法, rr代表轮询, 可以关闭 lb_kind DR #负载均衡转发规则, 可以关闭 persistence_timeout 0 #会话保持时间,这里要做测试, 所以设为0, 实际可根据session有效时间配置 protocol TCP #转发协议类型,支持TCP和UDP real_server 192.168.4.12 3306 { #配置服务器节点linux31 notify_down /opt/mariaDB/mariadb.sh #当服务挂掉时, 会执行此脚本,结束keepalived进程 weight 1 #设置权重,越大权重越高 TCP_CHECK { #r状态监测设置 connect_timeout 10 #超时配置, 单位秒 retry 3 #重试次数 delay_before_retry 3 #重试间隔 connect_port 3306 #连接端口, 和上面保持一致 } } }
-
验证高可用
使用keepAlived虚拟IP连接数据库
停止调主节点Mysql服务,验证是否自动切换
[root@cdh1 mysql]# systemctl stop mysqld [root@cdh1 mysql]# systemctl status keepalived ● keepalived.service - LVS and VRRP High Availability Monitor Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled) Active: inactive (dead) Feb 20 04:10:49 cdh1 Keepalived_healthcheckers[1923]: TCP connection to [192.168.4.11]:3306 failed. Feb 20 04:10:52 cdh1 Keepalived_healthcheckers[1923]: TCP connection to [192.168.4.11]:3306 failed. Feb 20 04:10:55 cdh1 Keepalived_healthcheckers[1923]: TCP connection to [192.168.4.11]:3306 failed. Feb 20 04:10:55 cdh1 Keepalived_healthcheckers[1923]: Check on service [192.168.4.11]:3306 failed after 3 retry. Feb 20 04:10:55 cdh1 Keepalived_healthcheckers[1923]: Removing service [192.168.4.11]:3306 from VS [192.168.4.100]:3306 Feb 20 04:10:55 cdh1 Keepalived_healthcheckers[1923]: Executing [/opt/mysql/mysql.sh] for service [192.168.4.11]:3306 in...:3306 Feb 20 04:10:55 cdh1 Keepalived_healthcheckers[1923]: Lost quorum 1-0=1 > 0 for VS [192.168.4.100]:3306 Feb 20 04:10:55 cdh1 Keepalived[1922]: Stopping Feb 20 04:10:55 cdh1 Keepalived_vrrp[1924]: VRRP_Instance(VI_1) sent 0 priority Feb 20 04:10:55 cdh1 Keepalived_vrrp[1924]: VRRP_Instance(VI_1) removing protocol VIPs. Hint: Some lines were ellipsized, use -l to show in full.
Mysql平滑2N扩容
平滑2N扩容部署架构图
平滑2N扩容实操
-
在cdh2节点上,增加VIP
修改/etc/keepalived/keepalived.conf
[root@cdh2 ~]# vi /etc/keepalived/keepalived.conf [root@cdh2 ~]# cat /etc/keepalived/keepalived.conf global_defs { router_id cdh2 # 机器标识,和主机名保持一致,运行keepalived服务器的一个标识 } vrrp_instance VI_1 { #vrrp实例定义 state BACKUP #lvs的状态模式,MASTER代表主, BACKUP代表备份节点 nopreemp interface ens33 #绑定对外访问的网卡 virtual_router_id 112 #虚拟路由标示,同一个vrrp实例采用唯一标示 priority 100 #优先级,100代表最大优先级, 数字越大优先级越高 advert_int 1 #master与backup节点同步检查的时间间隔,单位是秒 authentication { #设置验证信息 auth_type PASS #有PASS和AH两种 auth_pass 666 #验证密码,BACKUP密码须相同 } virtual_ipaddress { #KeepAlived虚拟的IP地址 192.168.4.101 } } virtual_server 192.168.4.101 3306 { #配置虚拟服务器IP与访问端口 delay_loop 6 #健康检查时间 lb_algo rr #负载均衡调度算法, rr代表轮询, 可以关闭 lb_kind DR #负载均衡转发规则, 可以关闭 persistence_timeout 0 #会话保持时间,这里要做测试, 所以设为0, 实际可根据session有效时间配置 protocol TCP #转发协议类型,支持TCP和UDP real_server 192.168.4.12 3306 { #配置服务器节点linux31 notify_down /opt/mariaDB/mariadb.sh #当服务挂掉时, 会执行此脚本,结束keepalived进程 weight 1 #设置权重,越大权重越高 TCP_CHECK { #r状态监测设置 connect_timeout 10 #超时配置, 单位秒 retry 3 #重试次数 delay_before_retry 3 #重试间隔 connect_port 3306 #连接端口, 和上面保持一致 } } }
-
解除原双主同步
chd1节点数据库
[root@cdh1 ~]# mysql -uroot -p123456 mysql> stop slave; Query OK, 0 rows affected (0.00 sec)
cdh2节点数据库
[root@cdh2 ~]# mysql -uroot -p123456 mysql> stop slave; Query OK, 0 rows affected (0.00 sec)
-
安装mysql扩容服务器
-
新建两台虚拟机,分别为chd3和cdh4
-
在cdh3和cdh4两个节点上安装mysql
-
配置cdh3和cdh1,实现新的双主同步
-
cdh3节点上,修改/etc/my.cnf
[root@cdh3 ~]# vi /etc/my.cnf [root@cdh3 ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server-id = 3 log-bin=mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=information_schema.% log-slave-updates=on slave-skip-errors=all auto-increment-offset=2 auto-increment-increment=2 binlog_format=mixed expire_logs_days=10
-
重启chd3节点的数据库
[root@cdh3 ~]# systemctl restart mysqld
-
创建replica用于主从同步的用户
[root@cdh3 ~]# mysql -uroot -p123456 mysql> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
-
在cdh1节点上,进行数据全量备份
[root@cdh1 mysql]# mysqldump -uroot -p123456 --routines --single_transaction --master-data=2 --databases dwyys > cdh1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@cdh1 mysql]# ll total 96 -rw-r--r--. 1 root root 90397 Feb 21 02:20 cdh1.sql -rwxr-xr-x. 1 root root 17 Feb 20 03:31 mysql.sh [root@cdh1 mysql]#
-
查看并记录master status信息
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 590 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
-
将备份的cdh1.sql通过scp拷贝到cdh3
[root@cdh1 mysql]# scp cdh1.sql root@192.168.4.13:/opt/mysql
-
将数据还原至cdh3节点数据库上
[root@cdh3 mysql]# mysql -uroot -p123456 < /opt/mysql/cdh1.sql mysql: [Warning] Using a password on the command line interface can be insecure.
-
配置主从同步信息
mysql> change master to master_host='192.168.4.11',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000004', master_log_pos=590, master_connect_retry=30; Query OK, 0 rows affected, 2 warnings (0.01 sec)
-
cdh1和cdh3开启主从同步
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.4.12 Master_User: replica Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 590 Relay_Log_File: mysql-relay-bin.000011 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 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: mysql.%,information_schema.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 590 Relay_Log_Space: 740 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: 2 Master_UUID: 133077d3-b0e8-11ed-98f7-000c2919c290 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: 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
-
配置chd1和cdh3节点的同步
查看cdh3的日志信息
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 90212 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
在cdh1节点上,配置主从同步信息
mysql> STOP SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host='192.168.4.13',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=90212, master_connect_retry=30; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
-
-
配置chd2和cdh4的双主同步
-
在cdh4节点上修改/etc/my.cnf
[root@cdh4 ~]# vi /etc/my.cnf [root@cdh4 ~]# cat /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server-id = 4 log-bin=mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=information_schema.% log-slave-updates=on slave-skip-errors=all auto-increment-offset=2 auto-increment-increment=2 binlog_format=mixed expire_logs_days=10 [root@cdh4 ~]#
-
重启cdh4数据库
[root@cdh4 ~]# systemctl restart mysqld
-
创建replica用于主从同步的用户
[root@cdh4 ~]# mysql -uroot -p123456 mysql> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
-
在cdh2节点上,进行数据全量备份
[root@cdh2 mysql]# mysqldump -uroot -p123456 --routines --single_transaction --master-data=2 --databases dwyys > cdh2.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@cdh2 mysql]# ll total 8 -rw-r--r--. 1 root root 2861 Feb 21 02:40 cdh2.sql -rwxr-xr-x. 1 root root 17 Feb 20 03:35 mysql.sh [root@cdh2 mysql]#
-
查看并记录master status信息
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 590 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
-
将备份的cdh1.sql通过scp命令拷贝到cdh4节点上
[root@cdh2 mysql]# scp cdh2.sql root@192.168.4.14:/opt/mysql
-
将数据还原至cdh4节点上
[root@cdh4 opt]# mysql -uroot -p123456 < /opt/mysql/cdh2.sql
-
配置主从同步信息
根据上述的master status信息,在cdh4中执行
mysql> change master to master_host='192.168.4.12',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=590, master_connect_retry=30; Query OK, 0 rows affected, 2 warnings (0.00 sec)
-
在cdh2和cdh4节点上开启主从同步
mysql> start slave; Query OK, 0 rows affected (0.00 sec)
-
检查同步状态信息
mysql> show slave status \G;
-
配置cdh2和cdh4节点的同步
查看cdh4的日志信息
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 2676 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
在cdh2节点上,配置同步信息
mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host='192.168.4.14',master_user='replica', master_password='replica', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=2676, master_connect_retry=30; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
-
-
-
通过KeepAlived,实现高可用
-
在新增的cdh3和cdh4上安装KeepAlived服务
-
修改cdh3节点配置
[root@cdh3 ~]# vi /etc/keepalived/keepalived.conf [root@cdh3 ~]# cat /etc/keepalived/keepalived.conf global_defs { router_id cdh3 # 机器标识,一般设为hostname,故障发生时,邮件通知会使用到。 } vrrp_instance VI_1 { #vrrp实例定义 state BACKUP #lvs的状态模式,MASTER代表主, BACKUP代表备份节点 interface ens33 #绑定对外访问的网卡 virtual_router_id 111 #虚拟路由标示,同一个vrrp实例采用唯一标示 priority 98 #优先级,100代表最大优先级, 数字越大优先级越高 advert_int 1 #master与backup节点同步检查的时间间隔,单位是秒 authentication { #设置验证信息 auth_type PASS #有PASS和AH两种 auth_pass 6666 #验证密码,BACKUP密码须相同 } virtual_ipaddress { #KeepAlived虚拟的IP地址 192.168.4.100 } } virtual_server 192.168.4.100 3306 { #配置虚拟服务器IP与访问端口 delay_loop 6 #健康检查时间 persistence_timeout 0 #会话保持时间,这里要做测试, 所以设为0, 实际可根据session有效时间配置 protocol TCP #转发协议类型,支持TCP和UDP real_server 192.168.4.13 3306 { #配置服务器节点linux32 notify_down /opt/mysql/mysql.sh weight 1 #设置权重,越大权重越高 TCP_CHECK { #r状态监测设置 connect_timeout 10 #超时配置, 单位秒 retry 3 #重试次数 delay_before_retry 3 #重试间隔 connect_port 3306 #连接端口, 和上面保持一致 } } }
重启服务
[root@cdh3 ~]# systemctl restart keepalived
创建关闭脚本
[root@cdh3 ~]# vi /opt/mysql/mysql.sh [root@cdh3 ~]# cat /opt/mysql/mysql.sh pkill keepalived
加入执行权限
chmod a+x mysql.sh
-
修改cdh4节点配置
重复cdh3的keepAlived节点配置
-
-
清理数据,并验证