MySQL主从数据库配置

为什么要做主从复制

1. 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运行。
2. 做数据的热备，主库宕机后能够及时替换主库，保证业务可用性。
3. 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

下图就描述了一个多个数据库间主从复制与读写分离的模型。web服务器(tomcat,apache,iis,weblogic等)从多个slave数据库进行读操作，从master数据库进行写操作，如图：

 在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。

MySQL 主从复制主要用途

l 读写分离

在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

l 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换

l 高可用HA

l 架构扩展

随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

基础准备

IP	主机名	节点
192.168.200.74	mysql1	主数据库节点
192.168.200.58	mysql2	从数据库节点

1.关闭防火墙及SElinux服务

setenforce 0
systemctl stop firewalld

2两个节点.配置hosts文件

vim /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.200.58 mysql1
192.168.200.74 mysql2

3.安装数据库服务

 yum install -y mariadb mariadb-server

 启动数据库服务并 设置开机自启

 systemctl start mariadb
 systemctl enable mariadb

可能会启动错误，排查报错日志

vi my.cnf #进入配置文件查看错误日志的路径

 进入错误日志

 

 日志显示端口被占用

查看mariadb的端口

默认 端口3306（我改成了3303）

现在修改此端口（改为没被占用的端口）

vi /etc/my.cnf

 添加port并设置一个未被占用的端口

 重启后生效

设置为开机自启

systemctl enable mariadb

初始化数据库

 mysql_secure_installation

默认没有密码

 初始化后出现以下，设置一个root密码

 

 配置mysql1主节点

vi /etc/my.cnf

[mysqld]
log_bin = mysql-bin #记录操作日志
binlog_ignore_db = mysql #不同步 mysql 系统数据库
server_id = 30 #数据库集群中的每个节点 id 都要不
同，一般使用 IP 地址的最后段的数字，例如 192.168.200.30，server_id 就写 30
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
[mysqld_safe]
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 -uroot -p000000

在 mysql1 节点，授权在任何客户端机器上可以以 root 用户登录到数据库，然后在主节 点上创建一个 user 用户连接节点 mysql2，并赋予从节点同步主节点数据库的权限。

grant all privileges on *.* to root@'%' identified by "000000";

grant replication slave on *.* to 'user'@'mysql2' identified by '000000';

 配置mysql2从节点

vi /etc/my.cnf

[mysqld]
log_bin = mysql-bin #记录操作日志
binlog_ignore_db = mysql #不同步 mysql 系统数据库
server_id = 40 #数据库集群中的每个节点 id 都要不
同，一般使用 IP 地址的最后段的数字，例如 192.168.200.40，server_id 就写 40
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
[mysqld_safe]
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

在从节点 mysql2 上登录 MariaDB 数据库，配置从节点连接主节点的连接信息。 master_host 为主节点主机名 mysql1，master_user 为上一步中创建的用户 user，

mysql -uroot -p000000 #进入数据库
change master to 
master_host='mysql1',master_user='user',master_password='000000';

 

 配置完毕主从数据库之间的连接信息之后，开启从节点服务。使用 show slave status\G 命令，并查看从节点服务状态，如果 Slave_IO_Running 和 Slave_SQL_Running 的状态都为 YES，则从节点服务开启成功。

systemctl restart mariadb #配置完文件 重启一下

[root@mysql2 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> change master to 
master_host='mysql1',master_user='user',master_password='000000';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

 

 主从连接完成

3.验证数据库主从服务（mysql1创建，再看mysql2）

先在主节点 mysql1 中创建库 test，并在库 test 中创建表 company，插入表数据，创建完 成后，查看表 company 数据，

 create database test;
 use test;
 create table company(id int not null primary key,name varchar(50),addr 
varchar(255));
 insert into company values(1,"alibaba","china");

 

 4.从节点验证复制功能

show databases;
 use test;
 show tables;
 select * from company;

主数据库创建的库，表，数据复制到从数据库。测试成功