数据库主从分离加读写分离操作步骤
数据库主从分离服务
1、基础环境安装 (1) 修改主机名【mysql1、mysql2】
代码语言：javascript复制[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】
代码语言：javascript复制# setenforce 0
# systemctl stop firewalld（3）配置hosts文件【mysql1、mysql2】
代码语言：javascript复制[root@mysql1 ~]# vi /etc/hosts
[root@mysql1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.200.3 mysql1
192.168.200.17 mysql2
[root@mysql2 ~]# vi /etc/hosts
[root@mysql2 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.200.3 mysql1
192.168.200.17 mysql2（4）配置本地YUM源【mysql1、mysql2】
代码语言：javascript复制[root@mysql1 ~]# ll
total 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=mariadb
baseurl=file:///opt/mariadb-repo
gpgcheck=0
enabled=1
[root@mysql1 yum.repos.d]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: mariadb
Cleaning up everything
Maybe 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 repolist
Loaded plugins: fastestmirror
Determining fastest mirrors
mariadb | 2.9 kB 00:00:00
mariadb/primary_db | 66 kB 00:00:00
repo id repo name status
mariadb mariadb 91
repolist: 91（5）安装数据库服务并启动【mysql1、mysql2】
代码语言：javascript复制# yum install -y mariadb mariadb-server
# systemctl start mariadb
# systemctl enable mariadb2、初始化数据库并配置主从服务 【mysql1、mysql2】
代码语言：javascript复制# 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 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!【mysql1】
代码语言：javascript复制# vi /etc/my.cnf.d/server.cnf
添加如下配置内容
[mysqld]
log_bin=mysql-bin
binlog_ignore_db=mysql
server_id=3
# systemctl restart mariadb
[root@mysql1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 9
Server version: 10.3.23-MariaDB-log MariaDB Server
Copyright (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】
代码语言：javascript复制# vi /etc/my.cnf.d/server.cnf
添加如下内容
[mysqld]
log_bin=mysql-bin
binlog_ignore_db=mysql
server_id=17
# systemctl restart mariadb
[root@mysql2 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 9
Server version: 10.3.23-MariaDB-log MariaDB Server
Copyright (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 statusG;
*************************** 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: 3
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
ERROR: No query specified3、验证数据库主从服务 （1）主节点mysql1创建数据库
代码语言：javascript复制[root@mysql1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 11
Server version: 10.3.23-MariaDB-log MariaDB Server
Copyright (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 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.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）从节点验证数据库复制功能
代码语言：javascript复制[root@mysql2 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 12
Server version: 10.3.23-MariaDB-log MariaDB Server
Copyright (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 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]> 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、基础环境安装 （1）修改主机名【mycat】
代码语言：javascript复制[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环境
代码语言：javascript复制# 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=mariadb
baseurl=file:///opt/mariadb-repo
gpgcheck=0
enabled=1
[root@mycat yum.repos.d]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: mariadb
Cleaning up everything
Maybe 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 repolist
Loaded plugins: fastestmirror
Determining fastest mirrors
mariadb | 2.9 kB 00:00:00
mariadb/primary_db | 66 kB 00:00:00
repo id repo name status
mariadb mariadb 91
repolist: 91
# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
[root@mycat ~]# java -version
openjdk 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读写分离中间件服务 （1）安装Mycat服务
代码语言：javascript复制# 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的逻辑库配置文件
代码语言：javascript复制[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
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="test" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" dbDriver="native" writeType="0" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.200.3:3306" user="root" password="000000">
<readHost host="hostS1" url="192.168.200.17:3306" user="root" password="000000" />
</writeHost>
</dataHost>
</mycat:schema>（3）修改配置文件权限
代码语言：javascript复制# chown root:root /usr/local/mycat/conf/schema.xml（4）编辑配置文件权限
代码语言：javascript复制# vi /usr/local/mycat/conf/server.xml
修改：
<user name="root">
<property name="password">000000</property>
<property name="schemas">USERDB</property>
</user>
删除：
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>（5）启动Mycat服务
代码语言：javascript复制[root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# netstat -ntpl
Active 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、验证数据库集群服务读写分离功能 （1）用mycat服务查询数据库信息
代码语言：javascript复制# yum install -y MariaDB-client
[root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server 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 USERDB
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
MySQL [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服务添加表数据
代码语言：javascript复制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服务对数据库读写操作分离
代码语言：javascript复制[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 |
---------- -------- ------- ---------------- ------ ------ -------- ------ ------ --------- ----------- ------------ 
