Mysql一主多从复制数据
应用场景：外部仅仅访问主数据库（Mysql_Master），多个从数据库（Mysql_Slave）做数据库热备份。一旦主数据库宕机，从服务器负责处理数据实验拓补图：
实验环境：
操作系统版本：CentOS7
Mysql_Master：192.168.199.17
Mysql_Slave01：192.168.199.18
Mysql_Slave02：192.168.199.19
实现MySQL主从复制需要进行的配置：
主服务器：
开启二进制日志
配置唯一的server-id
获得master二进制日志文件名及位置
创建一个用于slave和master通信的用户账号
从服务器：
配置唯一的server-id
使用master分配的用户账号读取master二进制日志
启用slave服务
实验步骤：
01.依据以上环境，分别登录到Mysql_Master、Mysql_Slave01、Mysql_Slave02三台服务器，关闭firewalld和selinux防火墙，并配置IP、DNS、hostname等
Mysql_Master：
Mysql_Slave01：
Mysql_Slave02：
02.分别登录到Mysql_Master、Mysql_Slave01、Mysql_Slave02三台服务器，执行以下操作。
#安装Mariadb
yum install mariadb-server mariadb -y
#设置Mariadb开机自动启动服务
systemctl enable mariadb
#启动Mariadb服务
systemctl start mariadb
#查看Mariadb服务是否正常运行
systemctl status mariadb
03.分别配置Mysql_Master、Mysql_Slave01、Mysql_Slave02三台服务器的/etc/my.cnf文件，并重启Mariadb服务。
Mysql_Master：
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server-id=1
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
Mysql_Slave01：
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
Mysql_Slave02：
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server-id=3
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
04.登录到Mysql_Master服务器，并进入到Mariadb数据库，修改数据库相关配置。
# mysql -u root -p
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.199.18';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.199.19';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> SHOW MASTER STATUS;
05.分别登录到Mysql_Slave01、Mysql_Slave02服务器，并进入到Mariadb数据库，修改数据库相关配置。
# mysql -u root -p
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.199.17',
-> MASTER_USER='root',
-> MASTER_PASSWORD='',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=584;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
验证试验结果：
01.使用show databases;数据库命令查看三台服务器目前所拥有的数据库列表
Mysql_Master：
Mysql_Slave01：
Mysql_Slave02：
02.登录到主数据库(Mysql_Master)当中，创建数据库名为HBGSLZ的数据库
MariaDB [(none)]> create database HBGSLZ;
MariaDB [(none)]> show databases;
03.分别登录到 Mysql_Slave01、 Mysql_Slave02从服务器中，查看数据库列表，检查是否也存在新建的数据库HBGSLZ
Mysql_Slave01：
Mysql_Slave02：
页:
[1]