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

[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

# setenforce 0
# systemctl stop firewalld

[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

[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

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

# 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!

# 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

# 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

[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

[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

[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

# 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)

# 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

[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>

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

# 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>

[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   

# 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]> 

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)

[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 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+