配置文件
[client]
port=3336
socket=/home/mysql/data/mysql.sock
default-character-set=utf8mb4
[mysqld]
port=3336
socket=/home/mysql/data/mysql.sock
#skip-slave-start 复制线程不会随着mysql进程启动而开启，就是需要手动启动start slave
#设备ID
server_id = 1
#确定AUTO_INCREMENT列值的起点，也就是初始值
auto_increment_offset = 1
#自增ID 步长
auto_increment_increment = 2
skip-external-locking
key_buffer_size=256M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
query_cache_size=32M
max_allowed_packet=16M
myisam_sort_buffer_size=128M
tmp_table_size=32M
table_open_cache=512
thread_cache_size=20
wait_timeout=300
interactive_timeout=300
max_connections=600
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency=32
#isolation level and default engine
default-storage-engine=INNODB
transaction-isolation=READ-COMMITTED
basedir=/home/mysql
datadir=/home/mysql/data
pid-file=/home/mysql/data/hostname.pid
#open performance schema
log-warnings
sysdate-is-now
binlog_format=ROW
log_bin_trust_function_creators=1
log-error=/home/mysql/data/hostname.err
log-bin=/home/mysql/arch/mysql-bin
expire_logs_days=7
max_binlog_size=1024M
innodb_write_io_threads=16
relay-log=/home/mysql/relay_log/relay-log
relay-log-index=/home/mysql/relay_log/relay-log.index
relay_log_info_file=/home/mysql/relay_log/relay-log.info
log_slave_updates=1
gtid_mode=OFF
enforce_gtid_consistency=OFF
# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
#other logs
#general_log=1
#general_log_file=/home/mysql/data/general_log.err
#slow_query_log=1
#slow_query_log_file=/home/mysql/data/slow_log.err
#for replication slave
sync_binlog=500
#for innodb options
innodb_data_home_dir=/home/mysql/data/
innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
innodb_log_group_home_dir=/home/mysql/arch
innodb_log_files_in_group=4
innodb_log_file_size=1G
innodb_log_buffer_size=200M
#根据生产需要，调整pool size
innodb_buffer_pool_size=2G
#innodb_additional_mem_pool_size=50M #deprecated in 5.6
tmpdir=/home/mysql/tmp
innodb_lock_wait_timeout=1000
#innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_locks_unsafe_for_binlog=1
#innodb io features: add for mysql5.5.8
performance_schema
innodb_read_io_threads=4
innodb-write-io-threads=4
innodb-io-capacity=200
#purge threads change default(0) to 1 for purge
innodb_purge_threads=1
innodb_use_native_aio=on
#case-sensitive file names and separate tablespace
innodb_file_per_table=1
lower_case_table_names=1
[mysqldump]
quick
max_allowed_packet=128M
[mysql]
no-auto-rehash
default-character-set=utf8mb4
[mysqlhotcopy]
interactive-timeout
[myisamchk]
key_buffer_size=256M
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M
[client]
port=3336
socket=/home/mysql/data/mysql.sock
default-character-set=utf8mb4
[mysqld]
port=3336
socket=/home/mysql/data/mysql.sock
#skip-slave-start 复制线程不会随着mysql进程启动而开启，就是需要手动启动start slave
server_id = 2
auto_increment_offset = 2
auto_increment_increment = 2
skip-external-locking
key_buffer_size=256M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
query_cache_size=32M
max_allowed_packet=16M #这个设置多大合适
myisam_sort_buffer_size=128M
tmp_table_size=32M
table_open_cache=512
thread_cache_size=20
wait_timeout=300
interactive_timeout=300
max_connections=600
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency=32
#isolation level and default engine
default-storage-engine=INNODB
transaction-isolation=READ-COMMITTED
basedir=/home/mysql
datadir=/home/mysql/data
pid-file=/home/mysql/data/hostname.pid
#open performance schema
log-warnings
sysdate-is-now
binlog_format=ROW
log_bin_trust_function_creators=1
log-error=/home/mysql/data/hostname.err
log-bin=/home/mysql/arch/mysql-bin
expire_logs_days=7
max_binlog_size=1024M
innodb_write_io_threads=16
relay-log=/home/mysql/relay_log/relay-log
relay-log-index=/home/mysql/relay_log/relay-log.index
relay_log_info_file=/home/mysql/relay_log/relay-log.info
log_slave_updates=1
gtid_mode=OFF
enforce_gtid_consistency=OFF
# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
#other logs
#general_log=1
#general_log_file=/home/mysql/data/general_log.err
#slow_query_log=1
#slow_query_log_file=/home/mysql/data/slow_log.err
#for replication slave
sync_binlog=500
#for innodb options
innodb_data_home_dir=/home/mysql/data/
innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
innodb_log_group_home_dir=/home/mysql/arch
innodb_log_files_in_group=4
innodb_log_file_size=1G
innodb_log_buffer_size=200M
#根据生产需要，调整pool size
innodb_buffer_pool_size=2G
#innodb_additional_mem_pool_size=50M #deprecated in 5.6
tmpdir=/home/mysql/tmp
innodb_lock_wait_timeout=1000
#innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_locks_unsafe_for_binlog=1
#innodb io features: add for mysql5.5.8
performance_schema
innodb_read_io_threads=4
innodb-write-io-threads=4
innodb-io-capacity=200
#purge threads change default(0) to 1 for purge
innodb_purge_threads=1
innodb_use_native_aio=on
#case-sensitive file names and separate tablespace
innodb_file_per_table=1
lower_case_table_names=1
[mysqldump]
quick
max_allowed_packet=128M
[mysql]
no-auto-rehash
default-character-set=utf8mb4
[mysqlhotcopy]
interactive-timeout
[myisamchk]
key_buffer_size=256M
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M
相互创建用户、统一文件状态
- 节点1给节点2主机创建授权用户，并查看本地数据库master的状态
#给节点2主机root用户授权
mysql> grant replication slave on *.* to 'root'@'10.110.90.154' identified by '密码';
#刷新一下授权
mysql> flush privileges;
#查看本地数据库master的状态，重点记录File和Position的值
mysql> show master status;
2. 节点2给节点1主机创建授权用户，并查看本地数据库master的状态
```shell
mysql> grant replication slave on *.* to 'root'@'10.110.90.153' identified by '密码';
mysql> flush privileges;
mysql> show master status;
- 可以刷新一下日志，让File保持一致
;#每执行一次file的编号名称就会+1
mysql>flush logs;
相互设置master
mysql>change master to master_host='节点2ip',master_port=3336,master_user='root',master_password='密码',master_log_file='mysql-bin.000010',master_log_pos=154; #设置自己的master信息#
mysql>start slave; #开启slave
mysql>show slave status; #查看本机slave状态，主要查看以下内容。
如果change master报：
ERROR 29 (HY000): File '/home/mysql/relay_log/relay-log.index' not found (Errcode: 2 - No such file or directory)
创建对应文件即可：vim relay_log/relay-log.index，保存退出
基于mysql用户执行权限：chown mysql:mysql relay_log -R
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.110.90.154
Master_User: root
Master_Port: 3336
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 154
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000010
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: 154
Relay_Log_Space: 521
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: e5960e25-5bde-11ed-8974-005056847d1b
Master_Info_File: mysql.slave_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
出现：
Slave_IO_Running: Yes IO线程，为Yes
Slave_SQL_Running: Yes SQL线程，为Yes
就说明开启成功。
出现Slave_IO_Running: NO：
可能是vm直接克隆了linux服务器，主从库的uuid一致了。然后配置主从数据库就会这样。
查找出文件所在路径：find / -iname “auto.cnf”
修改auto.cnf中的uuid就可以了
出现：Slave_SQL_Running:NO就执行：
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
keepalived实现高可用
#解压
[root@节点1 ~]# tar -zxvf keepalived-2.2.7.tar.gz
#进入解压后的目录
[root@节点1 ~]# cd keepalived-2.2.7
#/usr/local/keepalived 为安装目录
[root@节点1 ~]# ./configure --prefix=/usr/local/keepalived
#进行安装
[root@节点1 ~]# make && make install
#编译安装成功后使用如下命令查看
[root@节点1 ~]# cd /usr/local/keepalived/sbin
[root@节点1 sbin]# ls
keepalived #说明安装成功
[root@节点1 sbin]#
#拷贝文件
[root@节点1 sbin] cp keepalived /usr/sbin/
#拷贝文件生成服务
[root@节点1 sbin] cp keepalived /etc/init.d/keepalived
#成功后使用下方命令查看
[root@节点1 sbin] ll /usr/sbin/keepalived
-rwxr-xr-x. 1 root root 2948464 11月 4 10:13 /usr/sbin/keepalived
#设置开机自启动 如果disabled 则使用systemctl enable keepalived命令设置开机自启
[root@节点1 sbin] systemctl list-unit-files|grep keepalived
keepalived.service disabled
[root@节点1 sbin] systemctl enable keepalived
[root@节点1 sbin] mkdir /etc/keepalived
[root@节点1 sbin] cp /usr/local/keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/
#输入配置信息
[root@节点1 sbin] vim /etc/keepalived/keepalived.conf
[root@节点1 sbin] ll /etc/keepalived/
总用量 16
-rw-r--r-- 1 root root 678 11月 4 10:34 keepalived.conf
-rw-r--r-- 1 root root 3550 11月 4 10:18 keepalived.conf.sample
[root@节点1 sbin]#
节点1配置：
! Configuration File for keepalived
global_defs {
router_id 节点1 #通常为主机名
}
vrrp_instance VI_1 {
state BACKUP # 角色 主：master 从：BACKUP
interface ens192 #你的网卡 可以通过ip addr查看
virtual_router_id 51
priority 100 #优先级，同一个vrrp_instance 的MASTER优先级必须比BACKUP 高
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.110.90.155
}
}
virtual_server 10.110.90.155 3336 {
delay_loop 6
persistence_timeout 50
protocol TCP
real_server 10.110.90.153 3336 {
notify_down /etc/keepalived/kill_keepalived.sh #在检测到server down后执行脚本
TCP_CHECK {
connect_timeout 5
nb_get_retry 5
connect_port 3336
delay_before_retry 3
}
}
}
节点2配置：
! Configuration File for keepalived
global_defs {
router_id 节点2
}
vrrp_instance VI_1 {
state BACKUP
interface ens192
virtual_router_id 51
priority 90
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.110.90.155
}
}
virtual_server 10.110.90.155 3336 {
delay_loop 6
persistence_timeout 50
protocol TCP
real_server 10.110.90.154 3336 {
notify_down /etc/keepalived/kill_keepalived.sh
TCP_CHECK {
connect_timeout 5
nb_get_retry 5
connect_port 3336
delay_before_retry 3
}
}
}
编写检测mysql server down脚本文件
[root@节点1 ~]# vim /etc/keepalived/kill_keepalived.sh
#写如下内容
#!/bin/bash
kill -9 $(cat /var/run/keepalived.pid)
#脚本需要赋予执行权限：
[root@节点1 ~] chmod 777 /etc/keepalived/kill_keepalived.sh
分别启动keepalived
# 重启服务
[root@节点1 ~] sudo reboot now
#查看启动状态
[root@节点1 ~] systemctl status keepalived
[root@节点1 ~]# systemctl start keepalived 如果没有启动的话可以手动启动
#查看启动状态
[root@节点1 ~] systemctl status keepalived #或者 ps -ef|grep keepalived
[root@节点1 keepalived] systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since 五 2022-11-04 10:46:14 CST; 3h 29min ago
Docs: man:keepalived(8)
man:keepalived.conf(5)
man:genhash(1)
https://keepalived.org
Process: 20281 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 20283 (keepalived)
Tasks: 3
Memory: 792.0K
CGroup: /system.slice/keepalived.service
├─20283 /usr/local/keepalived/sbin/keepalived -D
├─20284 /usr/local/keepalived/sbin/keepalived -D
└─20285 /usr/local/keepalived/sbin/keepalived -D
11月 04 11:13:13 节点2 Keepalived_vrrp[20285]: Sending gratuitous ARP on ens192 for 10.110.90.155
11月 04 11:13:13 节点2 Keepalived_vrrp[20285]: Sending gratuitous ARP on ens192 for 10.110.90.155
11月 04 11:13:13 节点2 Keepalived_vrrp[20285]: Sending gratuitous ARP on ens192 for 10.110.90.155
11月 04 11:13:13 节点2 Keepalived_vrrp[20285]: Sending gratuitous ARP on ens192 for 10.110.90.155
11月 04 11:13:18 节点2 Keepalived_vrrp[20285]: (VI_1) Sending/queueing gratuitous ARPs on ens192 for 10.110.90.155
11月 04 11:13:18 节点2 Keepalived_vrrp[20285]: Sending gratuitous ARP on ens192 for 10.110.90.155
11月 04 11:13:18 节点2 Keepalived_vrrp[20285]: Sending gratuitous ARP on ens192 for 10.110.90.155
11月 04 11:13:18 节点2 Keepalived_vrrp[20285]: Sending gratuitous ARP on ens192 for 10.110.90.155
11月 04 11:13:18 节点2 Keepalived_vrrp[20285]: Sending gratuitous ARP on ens192 for 10.110.90.155
11月 04 11:13:18 节点2 Keepalived_vrrp[20285]: Sending gratuitous ARP on ens192 for 10.110.90.155
keepalive通过组播，单播等方式（自定义），实现keepalive主备推选。工作模式分为抢占和非抢占（通过参数nopreempt来控制）。
1）抢占模式：
主服务正常工作时，虚拟IP会在主上，备不提供服务，当主服务优先级低于备的时候，备会自动抢占虚拟IP，这时，主不提供服务，备提供服务。也就是说，工作在抢占模式下，不分主备，只管优先级。
如上配置，不管keepalived.conf里的state配置成master还是backup，只看谁的priority优先级高（一般而言，state为MASTER的优先级要高于BACKUP）。
priority优先级高的那一个在故障恢复后，会自动将VIP资源再次抢占回来！！
2）非抢占模式：
这种方式通过参数nopreempt（一般设置在advert_int的那一行下面）来控制。不管priority优先级，只要MASTER机器发生故障，VIP资源就会被切换到BACKUP上。
并且当MASTER机器恢复后，也不会去将VIP资源抢占回来，直至BACKUP机器发生故障时，才能自动切换回来。
千万注意：nopreempt这个参数只能用于state为backup的情况，所以在配置的时候要把master和backup的state都设置成backup，这样才会实现keepalived的非抢占模式！
也就是说：
a）当state状态一个为master，一个为backup的时候，加不加nopreempt这个参数都是一样的效果。即都是根据priority优先级来决定谁抢占vip资源的，是抢占模式！
b）当state状态都设置成backup，如果不配置nopreempt参数，那么也是看priority优先级决定谁抢占vip资源，即也是抢占模式。
c）当state状态都设置成backup，如果配置nopreempt参数，那么就不会去考虑priority优先级了，是非抢占模式！即只有vip当前所在机器发生故障，另一台机器才能接管vip。即使优先级高的那一台机器恢复 后也不会主动抢回vip，只能等到对方发生故障，才会将vip切回来。
1841
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
