mysql双主日志_mysql双主操作记录

最新推荐文章于 2021-06-22 23:01:11 发布

原创最新推荐文章于 2021-06-22 23:01:11 发布 · 226 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql双主日志

博客围绕MySQL双主日志展开，包含修改root用户密码、创建用户、设置远程访问权限等操作。还涉及主从服务器的配置，如设置server - id、log - bin等，以及创建备份账号、授予权限、查看主从状态等步骤，同时提及keepalive日志更改位置。

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.youkuaiyun.com/zt15732625878/article/details/86493096