数据库主从分离加读写分离操作步骤
数据库主从分离服务
1、基础环境安装
(1) 修改主机名【mysql1、mysql2】
[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/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】
[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】
# yum install -y mariadb mariadb-server # systemctl start mariadb # systemctl enable mariadb
2、初始化数据库并配置主从服务
【mysql1、mysql2】
# 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】
# 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】
# 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 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: 3 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) ERROR: No query specified
3、验证数据库主从服务
（1）主节点mysql1创建数据库
[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）从节点验证数据库复制功能
[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】
[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=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服务
# 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 <?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）修改配置文件权限
# chown root:root /usr/local/mycat/conf/schema.xml
（4）编辑配置文件权限
# 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服务
[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服务查询数据库信息
# 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服务添加表数据
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 | +----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
