本文共 16795 字，大约阅读时间需要 55 分钟。

数据库主从分离加读写分离操作步骤

数据库主从分离服务

1、基础环境安装

[root@mysql1 ~]# hostnamectl set-hostname mysql1[root@mysql1 ~]# su[root@mysql1 ~]# hostnamectl    Static hostname: mysql1         Icon name: computer-vm           Chassis: vm        Machine ID: 622ba110a69e24eda2dca57e4d306baa           Boot ID: 3a1e8d246bae4e60af7cffc079a603ac    Virtualization: kvm  Operating System: CentOS Linux 7 (Core)       CPE OS Name: cpe:/o:centos:centos:7            Kernel: Linux 3.10.0-862.2.3.el7.x86_64      Architecture: x86-64[root@mysql2 ~]# hostnamectl set-hostname mysql2[root@mysql2 ~]# su[root@mysql2 ~]# hostnamectl    Static hostname: mysql2         Icon name: computer-vm           Chassis: vm        Machine ID: 622ba110a69e24eda2dca57e4d306baa           Boot ID: 02f85b02c080436e92f00b10270364a9    Virtualization: kvm  Operating System: CentOS Linux 7 (Core)       CPE OS Name: cpe:/o:centos:centos:7            Kernel: Linux 3.10.0-862.2.3.el7.x86_64      Architecture: x86-64

（2）关闭防火墙和SELINUX服务【mysql1、mysql2】

# setenforce 0# systemctl stop firewalld

（3）配置hosts文件【mysql1、mysql2】

[root@mysql1 ~]# vi /etc/hosts[root@mysql1 ~]# cat /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.200.3   mysql1192.168.200.17  mysql2[root@mysql2 ~]# vi /etc/hosts[root@mysql2 ~]# cat /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.200.3   mysql1192.168.200.17  mysql2

（4）配置本地YUM源【mysql1、mysql2】

[root@mysql1 ~]# lltotal 109436-rw-r--r-- 1 root root 112060697 Oct 20 01:04 mariadb-repo.tar.gz[root@mysql1 ~]# tar -zxvf mariadb-repo.tar.gz -C /opt/......[root@mysql1 ~]# cd /etc/yum.repos.d/[root@mysql1 yum.repos.d]# mv * /media/[root@mysql1 yum.repos.d]# vi local.repo[root@mysql1 yum.repos.d]# cat local.repo [mariadb]name=mariadbbaseurl=file:///opt/mariadb-repogpgcheck=0enabled=1[root@mysql1 yum.repos.d]# yum clean allLoaded plugins: fastestmirrorCleaning repos: mariadbCleaning up everythingMaybe you want: rm -rf /var/cache/yum, to also free up space taken by orphaned data from disabled or removed repos[root@mysql1 yum.repos.d]# yum repolistLoaded plugins: fastestmirrorDetermining fastest mirrorsmariadb                                                                                         | 2.9 kB  00:00:00     mariadb/primary_db                                                                              |  66 kB  00:00:00     repo id                                                 repo name                                                statusmariadb                                                 mariadb                                                  91repolist: 91

（5）安装数据库服务并启动【mysql1、mysql2】

# yum install -y mariadb mariadb-server# systemctl start mariadb# systemctl enable mariadb

2、初始化数据库并配置主从服务

# mysql_secure_installation 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 currentpassword for the root user.  If you've just installed MariaDB, andyou 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 MariaDBroot 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 anyoneto log into MariaDB without having to have a user account created forthem.  This is intended only for testing, and to make the installationgo a bit smoother.  You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y ... Success!Normally, root should only be allowed to connect from 'localhost'.  Thisensures 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 canaccess.  This is also intended only for testing, and should be removedbefore 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 farwill 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 MariaDBinstallation should now be secure.Thanks for using MariaDB!

【mysql1】

# vi /etc/my.cnf.d/server.cnf添加如下配置内容[mysqld]log_bin=mysql-binbinlog_ignore_db=mysqlserver_id=3# systemctl restart mariadb[root@mysql1 ~]# mysql -uroot -p000000Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 9Server version: 10.3.23-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, 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 '0000000';Query OK, 0 rows affected (0.001 sec)MariaDB [(none)]> grant replication slave on *.* to 'user'@'mysql2' identified by '000000';Query OK, 0 rows affected (0.000 sec)MariaDB [(none)]> flush privileges;Query OK, 0 rows affected (0.001 sec)MariaDB [(none)]> Ctrl-C -- exit!Aborted

【mysql2】

# vi /etc/my.cnf.d/server.cnf添加如下内容[mysqld]log_bin=mysql-binbinlog_ignore_db=mysqlserver_id=17# systemctl restart mariadb[root@mysql2 ~]# mysql -uroot -p000000Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 9Server version: 10.3.23-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, 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.055 sec)MariaDB [(none)]> flush privileges;Query OK, 0 rows affected (0.000 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.002 sec)MariaDB [(none)]> show slave status\G;*************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: mysql1                   Master_User: user                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 826                Relay_Log_File: mysql2-relay-bin.000002                 Relay_Log_Pos: 1125         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: 826               Relay_Log_Space: 1435               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: 3                Master_SSL_Crl:             Master_SSL_Crlpath:                     Using_Gtid: No                   Gtid_IO_Pos:        Replicate_Do_Domain_Ids:    Replicate_Ignore_Domain_Ids:                  Parallel_Mode: conservative                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it              Slave_DDL_Groups: 3Slave_Non_Transactional_Groups: 0    Slave_Transactional_Groups: 01 row in set (0.000 sec)ERROR: No query specified

3、验证数据库主从服务

[root@mysql1 ~]# mysql -uroot -p000000Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 11Server version: 10.3.23-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> create database test;Query OK, 1 row affected (0.001 sec)MariaDB [(none)]> use testDatabase changedMariaDB [test]> create table company(id int not null primary key,name varchar(50),addr varchar(255));Query OK, 0 rows affected (0.048 sec)MariaDB [test]> insert into company values(1,"alibaba","china");Query OK, 1 row affected (0.003 sec)MariaDB [test]> select * from company;+----+---------+-------+| id | name    | addr  |+----+---------+-------+|  1 | alibaba | china |+----+---------+-------+1 row in set (0.000 sec)MariaDB [test]> Ctrl-C -- exit!Aborted

（2）从节点验证数据库复制功能

[root@mysql2 ~]# mysql -uroot -p000000Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 12Server version: 10.3.23-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.001 sec)MariaDB [(none)]> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [test]> show tables;+----------------+| Tables_in_test |+----------------+| company        |+----------------+1 row in set (0.000 sec)MariaDB [test]> select * from company ;+----+---------+-------+| id | name    | addr  |+----+---------+-------+|  1 | alibaba | china |+----+---------+-------+1 row in set (0.000 sec)MariaDB [test]> Ctrl-C -- exit!Aborted

数据库读写分离服务

1、基础环境安装

[root@mycat ~]# hostnamectl set-hostname mycat[root@mycat ~]# su[root@mycat ~]# hostnamectl    Static hostname: mycat         Icon name: computer-vm           Chassis: vm        Machine ID: 622ba110a69e24eda2dca57e4d306baa           Boot ID: 89411efcfdf84e9a9ba8891403e562b8    Virtualization: kvm  Operating System: CentOS Linux 7 (Core)       CPE OS Name: cpe:/o:centos:centos:7            Kernel: Linux 3.10.0-862.2.3.el7.x86_64      Architecture: x86-64

（2）配置本地YUM源并安装JDK环境

# tar -zxvf mariadb-repo.tar.gz -C /opt/[root@mycat ~]# cd /etc/yum.repos.d/[root@mycat yum.repos.d]# mv * /media/[root@mycat yum.repos.d]# vi local.repo[root@mycat yum.repos.d]# cat local.repo[mariadb]name=mariadbbaseurl=file:///opt/mariadb-repogpgcheck=0enabled=1[root@mycat yum.repos.d]# yum clean allLoaded plugins: fastestmirrorCleaning repos: mariadbCleaning up everythingMaybe you want: rm -rf /var/cache/yum, to also free up space taken by orphaned data from disabled or removed repos[root@mycat yum.repos.d]# yum repolistLoaded plugins: fastestmirrorDetermining fastest mirrorsmariadb                                                                                         | 2.9 kB  00:00:00     mariadb/primary_db                                                                              |  66 kB  00:00:00     repo id                                                 repo name                                                statusmariadb                                                 mariadb                                                  91repolist: 91# yum install -y  java-1.8.0-openjdk java-1.8.0-openjdk-devel[root@mycat ~]# java -versionopenjdk version "1.8.0_262"OpenJDK Runtime Environment (build 1.8.0_262-b10)OpenJDK 64-Bit Server VM (build 25.262-b10, mixed mode)

2、部署Mycat读写分离中间件服务

# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/# chmod -R 777 /usr/local/mycat/[root@mycat ~]# echo export MYCAT_HOME=/usr/local/mycat >> /etc/profile[root@mycat ~]# source /etc/profile

（2）编辑Mycat的逻辑库配置文件

[root@mycat ~]# > /usr/local/mycat/conf/schema.xml [root@mycat ~]# vi /usr/local/mycat/conf/schema.xml [root@mycat ~]# cat /usr/local/mycat/conf/schema.xml 
   
   
     
      
          
     
      select user()
         
             
          
     
    
   

（3）修改配置文件权限

# chown root:root /usr/local/mycat/conf/schema.xml

（4）编辑配置文件权限

# vi /usr/local/mycat/conf/server.xml 修改：
   		
    
     000000
    		
    
     USERDB
    
   删除：
   		
    
     user
    		
    
     TESTDB
    		
    
     true
    
   

（5）启动Mycat服务

[root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat startStarting Mycat-server...[root@mycat ~]# netstat -ntplActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      1515/java           tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      490/rpcbind         tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1111/sshd           tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      945/master          tcp6       0      0 :::1984                 :::*                    LISTEN      1515/java           tcp6       0      0 :::8066                 :::*                    LISTEN      1515/java           tcp6       0      0 :::46338                :::*                    LISTEN      1515/java           tcp6       0      0 :::9066                 :::*                    LISTEN      1515/java           tcp6       0      0 :::111                  :::*                    LISTEN      490/rpcbind         tcp6       0      0 :::22                   :::*                    LISTEN      1111/sshd           tcp6       0      0 ::1:25                  :::*                    LISTEN      945/master          tcp6       0      0 :::43930                :::*                    LISTEN      1515/java

3、验证数据库集群服务读写分离功能

# yum install -y MariaDB-client[root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p000000Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> show databases;+----------+| DATABASE |+----------+| USERDB   |+----------+1 row in set (0.001 sec)MySQL [(none)]> use USERDBReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMySQL [USERDB]> show tables;+----------------+| Tables_in_test |+----------------+| company        |+----------------+1 row in set (0.002 sec)MySQL [USERDB]> select * from company;+----+---------+-------+| id | name    | addr  |+----+---------+-------+|  1 | alibaba | china |+----+---------+-------+1 row in set (0.081 sec)MySQL [USERDB]>

（2）用Mycat服务添加表数据

MySQL [USERDB]> insert into company values(2,"bastetball","usa");Query OK, 1 row affected (0.024 sec)MySQL [USERDB]> select * from company;+----+------------+-------+| id | name       | addr  |+----+------------+-------+|  1 | alibaba    | china ||  2 | bastetball | usa   |+----+------------+-------+2 rows in set (0.003 sec)

（3）验证Mycat服务对数据库读写操作分离

[root@mycat ~]#  mysql -h127.0.0.1 -P9066 -uroot -p000000 -e 'show  @@datasource;'+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+| DATANODE | NAME   | TYPE  | HOST           | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+| dn1      | hostM1 | mysql | 192.168.200.3  | 3306 | W    |      0 |    7 | 1000 |     104 |         0 |          8 || dn1      | hostS1 | mysql | 192.168.200.17 | 3306 | R    |      0 |    7 | 1000 |      95 |         9 |          0 |+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+

转载地址：https://blog.csdn.net/qq_45392321/article/details/109837637 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！