mysql双主操作记录

grep 'temporary password' /var/log/mysqld.log
ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'cotton';

set global validate_password_policy=0;
set global validate_password_length=4;

CREATE USER 'USER'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'cotton';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'cotton' WITH GRANT OPTION;

'%' 表示所有ip都可以远程访问

GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip' IDENTIFIED BY '远程登录密码' WITH GRANT OPTION;

'ip'表示某个ip可以远程访问

server-id=1
log-bin=myssql_master
log-slave-updates=on

replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%

server-id=2
log-bin=myssql_slave
log-slave-updates=on

replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%

# 创建备份的账号 使用MYSQL_NATIVE_PASSWORD的方式加密
mysql> CREATE USER 'repl_master'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'cotton';
# 对repl_master授予备份的权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_master'@'%';
# 刷新权限
mysql> FLUSH PRIVILEGES;

# 查看MySQL主节点的状态
mysql> SHOW MASTER STATUS;

myssql_master.000001 | 761
mysql> CHANGE MASTER TO
# MySQL主的IP
-> MASTER_HOST='192.168.73.141',
# MySQL主的端口
-> MASTER_PORT=3306
# MySQL主的备份账号
-> MASTER_USER='repl_master',
# MySQL主的备份账号密码
-> MASTER_PASSWORD='cotton',
# 日志文件 通过show master status得到的
-> MASTER_LOG_FILE='mysql_master.000001',
# 日志文件位置 通过show master status得到的
-> MASTER_LOG_POS=516;

change master to master_host='192.168.118.140',master_port=3306,MASTER_USER='repl_master',MASTER_PASSWORD='cotton',MASTER_LOG_FILE='mysql_master.000001',MASTER_LOG_POS=716;

# 开启从库
mysql> START SLAVE;
# 查看从库的状态
mysql> SHOW SLAVE STATUS;

# 创建备份的账号 使用MYSQL_NATIVE_PASSWORD的方式加密
mysql> CREATE USER 'repl_slave'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'cotton';
# 对repl_slave授予备份的权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_slave'@'%';
# 刷新权限
mysql> FLUSH PRIVILEGES;

# 查看MySQL主节点的状态
mysql> SHOW MASTER STATUS;

myssql_slave.000001 | 759

change master to master_host='192.168.118.141',master_port=3306,MASTER_USER='repl_slave',MASTER_PASSWORD='cotton',MASTER_LOG_FILE='mysql_master.000001',MASTER_LOG_POS=759;

change master to Master_Log_File='mysql-bin.000002',Master_Log_Pos=154;
change master to Master_Log_File='myssql_slave.000003',Master_Log_Pos=777;

./configure --prefix=/ect/keepalived

# 检查mysql服务是否存活的脚本
vrrp_script chk_mysql {
script "/usr/bin/killall -0 mysqld"
}

keepalive日志更改位置
https://blog.csdn.net/zt15732625878/article/details/86493096