【Linux】重生之从零开始学习运维之Mysql

一主一从

主12主机

准备工作

mkdir -p /data/mysql/logbin
chown -R mysql:mysql /data/mysql

主节点mysql配置

vim /etc/my.cnf.d/mysql-server.cnf
server-id=177
log_bin=/data/mysql/logbin/mysql-bin
default_authentication_plugin=mysql_native_password

查看效果

systemctl restart mysqld
ll /data/mysql/logbin/

show master logs;

同步账号授权

create user repluser@'10.0.0.%' identified by '123456';
grant replication slave on *.* to repluser@'10.0.0.%';
flush privileges;

从15主机

准备工作

\rm -rf /var/lib/mysql/*
systemctl start mysqld
mkdir -p /data/mysql/logbin
chown -R mysql:mysql /data/mysql

从节点mysql配置

vim /etc/my.cnf.d/mysql-server.cnf
server-id=183
read-only
log-bin=/data/mysql/logbin/mysql-bin
default_authentication_plugin=mysql_native_password

查看效果

systemctl start mysqld
ll /data/mysql/logbin

从角色配置数据同步角色

主12主机

确认数据信息

show master logs;
show master status;

从15主机

配置主从同步

CHANGE MASTER TO MASTER_HOST='10.0.0.12', MASTER_USER='repluser',
MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=849;

启动

start slave;

测试

主12主机创建数据库db1

create database db1;

从15主机查看是否同步

show databases;

节点重置

主12主机重置

reset master;

show slave status\G

重新连接

CHANGE MASTER TO MASTER_HOST='10.0.0.12', MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=157;

start slave;

一主多从

从18主机

准备工作

mkdir -p /data/mysql/logbin
chown -R mysql:mysql /data/mysql

vim /etc/my.cnf.d/mysql-server.cnf
server-id=186
read-only
log-bin=/data/mysql/logbin/mysql-bin
default_authentication_plugin=mysql_native_password

主12主机基础数据同步

mysqldump -A --source-data=1 --single-transaction > all.sql
scp all.sql 10.0.0.18:/root/

从18主机修改配置

set sql_log_bin=0;临时关闭
source /root/all.sql;
set sql_log_bin=1;重新开启

从18主机开启同步

start slave;
show slave status\G

级联复制

中间节点配置

select @@log_slave_updates;

开启中继能力

vim /etc/my.cnf.d/mysql-server.cnf
log_slave_updates

查看中继主机

show slave status\G
select user,host from mysql.user;

传递给从18主机

mysqldump -A -F --single-transaction --source-data=1 > middle-all.sql
scp middle-all.sql root@10.0.0.18:/root/

从18主机配置

重置环境

systemctl stop mysqld
\rm -rf /var/lib/mysql/*
\rm -rf /data/mysql/logbin/*
systemctl start mysqld

vim /etc/my.cnf.d/mysql-server.cnf
server-id=186
read-only
log-bin=/data/mysql/logbin/mysql-bin
default_authentication_plugin=mysql_native_password

修改同步配置文件

CHANGE MASTER TO 
 MASTER_HOST='10.0.0.15',
 MASTER_USER='repluser',
 MASTER_PASSWORD='123456',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000003', 
 MASTER_LOG_POS=157;

导入数据

set sql_log_bin=0;临时关闭
select @@sql_log_bin;
source /root/middle-all.sql
set sql_log_bin=1;

启动从服务

start slave;

查看中继主机

show processlist\G

主主复制

重置从18主机

stop slave;
reset slave all;

中继15主机确认

show slave hosts;

准备工作

主1-12主机

vim /etc/my.cnf.d/mysql-server.cnf
server-id=177
log_bin=/data/mysql/logbin/mysql-bin
default_authentication_plugin=mysql_native_password

vim /etc/my.cnf.d/mysql-server.cnf
server-id=183
log-bin=/data/mysql/logbin/mysql-bin
default_authentication_plugin=mysql_native_password

查看主从效果

systemctl restart mysqld
show slave status\G

配置12主机为从角色

 CHANGE MASTER TO
    ->     MASTER_HOST='10.0.0.15',
    ->     MASTER_USER='repluser',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_PORT=3306,
    ->     MASTER_LOG_FILE='mysql-bin.000004',
    ->     MASTER_LOG_POS=157;
start slave;

查看从12主机状态

show slave status\G