主从数据库部署 - 《云计算工匠工坊》

一、主从数据库概述
二、主从复制的优点
三、主从数据库的工作流程
四、读写分离
五、主从数据库部署

一、主从数据库概述

从字面意思上来了解，主从复制的意思其实就是用来建立一个和主数据库完全统一的数据库环境，这种数据库被称之为从数据库，主数据库一般是为准实时的业务数据库。

二、主从复制的优点

1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

三、主从数据库的工作流程

支撑主从数据库工作的三个线程：
1.binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。
在从库里，当复制开始的时候，从库就会创建两个线程进行处理：
2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。
3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

具体流程：
①主库db的更新事件(update、insert、delete)被写到binlog
②：从库发起连接，连接到主库
③：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库
④：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log.
⑤：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db.
主从同步复制有以下几种方式：
（1）同步复制，master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。
（2）异步复制，master只需要完成自己的数据库操作即可，至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。
（3）半同步复制，master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MYSQL引入的。

四、读写分离

什么是读写分离？
让主数据库（master）处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库（slave）处理SELECT查询操作。在数据库中，数据库的“写”操作（写10000条数据到数据库可能要3分钟）操作是比较耗时的。但是数据库的“读”（从数据库读10000条数据可能只要5秒钟）。所以读写分离，解决的是数据库的写入，影响了查询的效率。

什么是MyCat?
MyCAT 是一款开源软件，也是目前最流行的基于Java语言编写的数据库中间件，实现了MySQL协议的服务器，前端用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为多个小表，存储在后端MySQL服务器里或者其他数据库里。
配和主从数据库使用可实现读写分离功能。

五、主从数据库部署

1.修改三台节点主机名

[root@localhost ~]# hostnamectl  set-hostname mycat 
[root@localhost ~]# hostnamectl  set-hostname db1
[root@localhost ~]# hostnamectl  set-hostname db2

2.配置三台节点主机名映射文件

[root@mycat ~]# vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4
.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6
.localdomain6
192.168.100.11 mycat
192.168.100.22 db1
192.168.100.33 db2

将第一台节点的hosts文件发送到第二台节点

[root@mycat ~]# scp /etc/hosts db1:/etc/hosts
The authenticity of host 'db1 (192.168.100.22)' can't be established.
ECDSA key fingerprint is SHA256:ladIYzwOdP7VIClaeiywLIItopeS/FekUpIk72ZdRNA.
ECDSA key fingerprint is MD5:11:88:c8:68:4d:0f:8a:63:3a:1e:d7:bd:a1:a3:69:b5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'db1,192.168.100.22' (ECDSA) to the list of known hosts.
root@db1's password: 
hosts                          100%  217   184.1KB/s   00:00

将第一台节点的hosts文件发送到第三台节点

[root@mycat ~]# scp /etc/hosts db2:/etc/hosts
The authenticity of host 'db2 (192.168.100.33)' can't be established.
ECDSA key fingerprint is SHA256:ladIYzwOdP7VIClaeiywLIItopeS/FekUpIk72ZdRNA.
ECDSA key fingerprint is MD5:11:88:c8:68:4d:0f:8a:63:3a:1e:d7:bd:a1:a3:69:b5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'db2,192.168.100.33' (ECDSA) to the list of known hosts.
root@db2's password: 
hosts                          100%  217   116.7KB/s   00:00    
[root@mycat ~]#

3.配置yum源

[root@mycat ~]# cat /etc/yum.repos.d/local.repo 
[centos]
name=centos
baseurl=file:///opt/centos
gpgcheck=0
enabled=1
[root@mycat ~]# 
[root@mycat ~]# yum -y install vsftpd                //在第一台节点上安装ftp服务
[root@mycat ~]# vi /etc/vsftpd/vsftpd.conf             //编辑ftp配置文件
anon_root=/opt                                                                    //在文件顶部添加
[root@mycat ~]# systemctl  restart vsftpd                //重启ftp服务
[root@mycat ~]# stemctl  enable vsftpd                //设置服务开机自启
[root@db1 ~]# mv /etc/yum.repos.d/* /media/            //在第二台节点移除网络源
[root@db1 ~]# vi /etc/yum.repos.d/local.repo            //编辑本地local.repo文件
[centos]
name=centos
baseurl=ftp://192.168.100.11/centos
gpgcheck=0
enabled=1
[root@db2 ~]# mv /etc/yum.repos.d/* /media/            //在第三台节点移除网络源
[root@db2 ~]# vi /etc/yum.repos.d/local.repo            //编辑本地local.repo文件
[centos]
name=centos
baseurl=ftp://192.168.100.11/centos
gpgcheck=0
enabled=1

4.关闭三台节点防火墙以及selinux

[root@mycat ~]# systemctl  stop firewalld && systemctl  disable firewalld    
                                              //关闭三台节点防火墙与selinux
[root@mycat ~]# setenforce 0

5.第二台和第三台节点安装数据库服务

[root@db1 ~]# yum -y install mariadb mariadb-server
[root@db2 ~]# yum -y install mariadb mariadb-server

6.检查第一台节点的jdk环境

[root@mycat ~]# java -version     
openjdk version "1.8.0_161"
OpenJDK Runtime Environment (build 1.8.0_161-b14)
OpenJDK 64-Bit Server VM (build 25.161-b14, mixed mode)
[root@mycat ~]#

7.初始化第二台和第三台节点数据库，数据库密码全部设置为123456

[root@db1 ~]# systemctl  restart mariadb     //启动
[root@db1 ~]# mysql_secure_installation 
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
 ... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@db2 ~]# systemctl  restart mariadb              //第二台启动
[root@db2 ~]# mysql_secure_installation 
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
 ... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!

8.配置第二台和第三台

[root@db1 ~]# vi /etc/my.cnf        //编辑第一台数据库配置文件
[mysqld]
log_bin = mysql-bin   
binlog_ignore_db = mysql 
server_id = 12
[root@db1 ~]# systemctl  restart mariadb   //重启数据库
[root@db2 ~]# vi /etc/my.cnf        //编辑第三台
[mysqld]
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = 13
[root@db2 ~]# systemctl  restart mariadb

9.登录第一台数据库配置权限

[root@db1 ~]# mysql -uroot -p123456            //登录
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 "123456"; 
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to 'user'@'db2' identified by '123456'; 
Query OK, 0 rows affected (0.00 sec)

10.配置第二台数据库权限

[root@db2 ~]# mysql -uroot -p123456            //登录    
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='db1',master_user='user',master_password='123456'; 
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: db1
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 527
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 811
        Relay_Master_Log_File: mysql-bin.000001
             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: 527
              Relay_Log_Space: 1107
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12
1 row in set (0.00 sec)

11.验证数据库同步

MariaDB [(none)]> create database test;        //在db1节点创建test库
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use test;            //进入到test库中        
Database changed

MariaDB [test]> create table company(id int not null primary key,name varchar(50),addr varchar(255));        //创建数据表
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]>  insert into company values(1,"facebook","usa");                     //插入数据
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from company;                                //查看数据
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | facebook | usa  |
+----+----------+------+
1 row in set (0.00 sec)

MariaDB [(none)]> show databases;                                //在db2节点查询数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use test;                                    //进入数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [test]> select * from company;                                //查询数据表中的内容
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | facebook | usa  |
+----+----------+------+
1 row in set (0.00 sec)