【Linux服务器】-mysql数据库主备实现

2.mysql数据库主备实现

2.1.主备配置

2.1.1.前置环境准备

服务名称	服务器IP	角色	数据库版本
mysql-01	192.168.6.136	master	mysql-8.0.4
mysql-02	192.168.6.135	slave	mysql-8.0.4

1、确保2台数据库之间的网络是互通的
2、配置期间不能有数据写入

2.1.2.master数据库服务器配置

编辑数据库配置文件

vi /etc/my.cnf

cat  >> /etc/my.cnf <<EOF 
#服务器 id，随意，但要唯一
server-id = 1  
#二进制文件的名
log-bin = mysql-bin 
#设置日志最大为1G
max_binlog_size=1024M
#设置binlog文件最大保留天数30天
expire_logs_days=30
#数据库宕机恢复后，自动同步缺少的数据
relay_log=mysql-relay-bin
relay_log_recovery=1
#设置自增id的初始值为1，每次增量为2
auto_increment_offset=1
auto_increment_increment=2
#开启gtid
gtid_mode=ON
enforce_gtid_consistency=1
#需要同步的数据库，如test1，test2，根据实际情况配置
binlog-do-db=test1
binlog-do-db=test2
#不要同步的数据库
binlog-ignore-db=mysql,information_schema,performance_schema,sys
#不要同步的表
#replicate-ignore-table=库名.表名
EOF

创建同步用户

use mysql;
#创建用户
create user 'user_sync'@'%' identified by 'mypassword';
#授权账号复制权限
grant replication slave on *.* to 'user_sync'@'%';
#刷新配置
FLUSH PRIVILEGES;
reset master;
#重置master信息
#说明：同步开始前，不要有数据写入，否则影响bin-log日志文件
flush tables with read lock;
#锁表命令，在同步配置完成后再使用解锁表命令：unlock tables;

输出File 和 Position 值

show master status;
#记下输出中的 File 和 Position 值，后续在从库上使用
| mysql-bin.000001 |      157 | 

2.1.3.slave数据库服务器配置

编辑数据库配置文件

cat >>/etc/my.cnf <<EOF

server-id = 2
#二进制日志文件名
log-bin=master-b-bin
#二进制日志格式
binlog-format=ROW
#设置日志最大为1G
max_binlog_size=1024M
#设置binlog文件最大保留天数30天
expire_logs_days=30
#数据库宕机恢复后，自动同步缺少的数据
relay_log=mysql-relay-bin
relay_log_recovery=1
#设置自增id的初始值为1，每次增量为2
auto_increment_offset=2
auto_increment_increment=2
#开启gtid
gtid_mode=ON
enforce_gtid_consistency=1
#需要同步的数据库，如test1，test2，根据实际情况配置
binlog-do-db=test1
binlog-do-db=test2
#不要同步的数据库
binlog-ignore-db=mysql,information_schema,performance_schema,sys
#不要同步的表 
#replicate-ignore-table=库名.表名
EOF

编辑同步数据库指向

stop slave;
change master to
 master_host = '192.168.6.136',
 master_user = 'user_sync',
 master_password = 'mypassword',
  #主服务器数据库上的file值(不能有空格)
 master_log_file = 'mysql-bin.000001',
  #主服务器数据库的Position值
 master_log_pos = 157；

start slave;

显示同步情况

show slave status\G
当Slave_IO_Running和Slave_SQL_Running均为Yes时，代表正在保持同步

2.2.主备故障切换

3.mysql数据库主主

登录备数据库

use mysql;
#创建用户
create user 'user_sync_b'@'%' identified by 'mypassword';
#授权账号复制权限
grant replication slave on *.* to 'user_sync_b'@'%';
#刷新配置
FLUSH PRIVILEGES;
reset master;
#重置master信息
#说明：同步开始前，不要有数据写入，否则影响bin-log日志文件
flush tables with read lock;
#锁表命令，在同步配置完成后再使用解锁表命令：unlock tables;

输出File 和 Position 值

show master status;
#记下输出中的 File 和 Position 值，后续在从库上使用
| mysql-bin.000001 |      157 | 

导出数据库

mysqldump -u 用户名 -p --all-databases > all_databases_backup.sql
mysql -u 用户名 -p < all_databases_backup.sql

编辑同步数据库指向

stop slave;
change master to
 master_host = '192.168.6.135',
 master_user = 'user_sync_b',
 master_password = 'mypassword',
  #主服务器数据库上的file值(不能有空格)
 master_log_file = 'mysql-bin.000001',
  #主服务器数据库的Position值
 master_log_pos = 157；

start slave;

显示同步情况

show slave status\G
当Slave_IO_Running和Slave_SQL_Running均为Yes时，代表正在保持同步