1.主从数据库的优点
① 方便做数据的热备份。
② 架构的扩展更容易。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。
③ 读写分离,使数据库能支撑更大的并发。在报表中尤其重要。
2. 常见的主从形式
- 一从一主
一主一从是最常见的主从架构,实施起来简单并且有效,不仅可以实现HA,而且还能读写分离,进而提升集群的并发能力。 - 一从多主
提高系统的读性能。 - 多主一从
多主一从可以将多个MySQL数据库备份到一台存储性能比较好的服务器上。 - 双主复制
双主复制,也就是互做主从复制,每个Master既是Master,又是另外一台服务器的slave。这样任何一方所做的变更,都会通过复制应用到另外一方的数据库中。 - 级联复制
级联复制模式下,部分slave的数据同步不连接主节点,而是连接从节点。因为如果主节点有太多的从节点,就会损耗一部分性能用于replication,那么可以让3~5个从节点连接主节点,其它从节点作为二级或者三级与从节点连接,这样不仅可以缓解主节点的压力,并且对数据一致性没有负面影响。
3.部署主从数据库
192.168.200.39节点
[root@localhost ~]# hostnamectl set-hostname mysql1
[root@localhost ~]# bash
192.168.200.99节点
[root@localhost ~]# hostnamectl set-hostname mysql2
[root@localhost ~]# bash
两个节点关闭防火墙
[root@mysql1 ~]# setenforce 0
[root@mysql1 ~]# systemctl stop firewalld
[root@mysql1 ~]# systemctl disable firewalld
修改配置文件
[root@mysql1 ~]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.200.39 mysql1
192.168.200.99 mysql2
~
配置yum 源
[root@mysql2 ~]# cat /etc/yum.repos.d/local.repo
[centos]
name=centos
baseurl=file:///opt/centos
gpgcheck=0
enabled=1
安装数据库
[root@mysql1 ~]# yum install -y mariadb mariadb-server
开启并设置为开机自启
[root@mysql1 ~]# systemctl start mariadb
[root@mysql1 ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
数据库初始化
[root@mysql2 ~]# mysql_secure_installation
编辑配置文件
在39节点编辑配置文件
[root@mysql1 ~]# vi /etc/my.cnf
[mysqld]
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = 39
[root@mysql1 ~]# systemctl restart mariadb
进入数据库授权
[root@mysql1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB-log 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)]> grant all privileges on *.* to root@'%' identified by "000000";
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to 'user'@'mysql2' identified by '000000';
Query OK, 0 rows affected (0.00 sec)
在99节点编辑配置文件
[root@mysql2 ~]# vi /etc/my.cnf
[mysqld]
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = 99
[root@mysql2 ~]# systemctl restart mariadb
[root@mysql2 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB-log 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.00 sec)
配置完毕主从数据库之间的连接信息之后,开启从节点服务。使用show slave status\G命令,并查看从节点服务状态,如果Slave_IO_Running和Slave_SQL_Running的状态都为YES,则从节点服务开启成功。
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: mysql1
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master 'user@mysql1:3306' - retry-time: 60 retries: 86400 message: Access denied for user 'user'@'192.168.200.99' (using password: YES)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)