第六周作业-N67044-张铭扬
1. 简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序
DDL：Data Defination Language 数据定义语言，主要是建表、删除表、修改表字段等操作
命令：CREATE，DROP，ALTER，
DML：Data Manipulation Language 数据操纵语言，用于对于数据库的数据的操作
命令：INSERT，DELETE，UPDATE
DCL：Data Control Language 数据控制语言，是用来设置或更改数据库用户或角色权限的语句
命令：GRANT,REVOKE
DQL：Data Query Language 数据查询语言，用于查询数据
命令：SELECT
mysql各个关键字查询时候的先后顺序
select--from--where--group by--having--order by，select和from是必选的
2. 自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个
1）以classid为分组，统计出每个班级的人数，只显示前三个
mysql> select classid, count(*) 数量 from students group by classid limit 3; +---------+--------+ | classid | 数量 | +---------+--------+ | 2 | 3 | | 1 | 4 | | 4 | 4 | +---------+--------+ 3 rows in set (0.01 sec)
2）统计每个班的男生和女生人数，并按照班级号排序
mysql> select classid,gender, count(*) 数量 from students group by classid,gender order by classid; +---------+--------+--------+ | classid | gender | 数量 | +---------+--------+--------+ | NULL | M | 2 | | 1 | F | 2 | | 1 | M | 2 | | 2 | M | 3 | | 3 | F | 3 | | 3 | M | 1 | | 4 | M | 4 | | 5 | M | 1 | | 6 | F | 3 | | 6 | M | 1 | | 7 | F | 2 | | 7 | M | 1 | +---------+--------+--------+ 12 rows in set (0.00 sec)
3）统计每个班学生的平均年龄，筛选出平均年龄最大的前三个
mysql> select classid,avg(age) from students group by classid order by avg(age) desc limit 3; +---------+----------+ | classid | avg(age) | +---------+----------+ | NULL | 63.5000 | | 5 | 46.0000 | | 2 | 36.0000 | +---------+----------+ 3 rows in set (0.00 sec)
4）筛选出平均年龄大于22的班级
mysql> select classid,avg(age) from students group by classid having avg(age)>22; +---------+----------+ | classid | avg(age) | +---------+----------+ | 2 | 36.0000 | | 4 | 24.7500 | | 5 | 46.0000 | | NULL | 63.5000 | +---------+----------+ 4 rows in set (0.00 sec)
5）按姓名排序，选出前三名学生
mysql> select * from students order by name limit 3; +-------+-----------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+-----------+-----+--------+---------+-----------+ | 20 | Diao Chan | 19 | F | 7 | NULL | | 4 | Ding Dian | 32 | M | 4 | 4 | | 15 | Duan Yu | 19 | M | 4 | NULL | +-------+-----------+-----+--------+---------+-----------+ 3 rows in set (0.00 sec)
5）按照年纪从大到小排序，筛选出前五名学生
mysql> select * from students order by age desc limit 5 ; +-------+--------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 4 | Ding Dian | 32 | M | 4 | 4 | +-------+--------------+-----+--------+---------+-----------+ 5 rows in set (0.00 sec)
6）筛选出成绩前五名学生
mysql> select s.name,sc.score from students as s left outer join scores as sc on s.stuid=sc.stuid order by score desc limit 5; +-------------+-------+ | name | score | +-------------+-------+ | Shi Potian | 97 | | Shi Qing | 96 | | Shi Zhongyu | 93 | | Lin Daiyu | 93 | | Ding Dian | 89 | +-------------+-------+ 5 rows in set (0.00 sec)
7）筛选出平均分前三名的班级
mysql> select s.classid,avg(sc.score) from students as s left outer join scores as sc on s.stuid=sc.stuid group by s.classid order by avg(sc.score) desc limit 3; +---------+---------------+ | classid | avg(sc.score) | +---------+---------------+ | 5 | 96.0000 | | 2 | 83.2500 | | 4 | 80.0000 | +---------+---------------+ 3 rows in set (0.00 sec)
8）筛选出低于平均分的学生
mysql> select s.name,sc.score from students as s left outer join scores as sc on s.stuid=sc.stuid having score < (select avg(score) from scores) order by score; +------------+-------+ | name | score | +------------+-------+ | Yu Yutong | 39 | | Shi Potian | 47 | | Lin Daiyu | 57 | | Yu Yutong | 63 | | Ding Dian | 71 | | Xie Yanke | 75 | +------------+-------+ 6 rows in set (0.00 sec)
9）筛选出男性的平均年龄
mysql> select gender,avg(age) from students group by gender having gender='M'; +--------+----------+ | gender | avg(age) | +--------+----------+ | M | 33.0000 | +--------+----------+ 1 row in set (0.00 sec)
10）选出每班年龄大于平均年龄的人数
mysql> select classid,count(*) from students where age > (select avg(age) from students) group by classid order by count(*); +---------+----------+ | classid | count(*) | +---------+----------+ | 4 | 1 | | 5 | 1 | | NULL | 1 | | 2 | 2 | +---------+----------+ 4 rows in set (0.01 sec)
3. xtrabackup备份和还原数据库练习
[root@centos8 ~]# ls anaconda-ks.cfg hellodb_innodb.sql percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm [root@centos8 ~]# yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm [root@centos8 ~]# mkdir /backup [root@centos8 ~]# xtrabackup -uroot --backup --target-dir=/backup/base xtrabackup: Transaction log of lsn (18481319) to (18481329) was copied. 220813 19:42:34 completed OK! [root@centos8 backup]# ll /backup/base/ total 72756 -rw-r----- 1 root root 475 Aug 13 19:42 backup-my.cnf -rw-r----- 1 root root 156 Aug 13 19:42 binlog.000003 -rw-r----- 1 root root 16 Aug 13 19:42 binlog.index drwxr-x--- 2 root root 132 Aug 13 19:42 hellodb -rw-r----- 1 root root 6094 Aug 13 19:42 ib_buffer_pool -rw-r----- 1 root root 12582912 Aug 13 19:42 ibdata1 drwxr-x--- 2 root root 143 Aug 13 19:42 mysql -rw-r----- 1 root root 28311552 Aug 13 19:42 mysql.ibd drwxr-x--- 2 root root 8192 Aug 13 19:42 performance_schema drwxr-x--- 2 root root 28 Aug 13 19:42 sys -rw-r----- 1 root root 16777216 Aug 13 19:42 undo_001 -rw-r----- 1 root root 16777216 Aug 13 19:42 undo_002 -rw-r----- 1 root root 18 Aug 13 19:42 xtrabackup_binlog_info -rw-r----- 1 root root 102 Aug 13 19:42 xtrabackup_checkpoints -rw-r----- 1 root root 484 Aug 13 19:42 xtrabackup_info -rw-r----- 1 root root 2560 Aug 13 19:42 xtrabackup_logfile -rw-r----- 1 root root 39 Aug 13 19:42 xtrabackup_tablespaces
远程主机：10.0.0.153
[root@centos8 ~]# ll /backup/base/ total 72756 -rw-r----- 1 root root 475 Aug 13 19:49 backup-my.cnf -rw-r----- 1 root root 156 Aug 13 19:49 binlog.000003 -rw-r----- 1 root root 16 Aug 13 19:49 binlog.index drwxr-x--- 2 root root 132 Aug 13 19:49 hellodb -rw-r----- 1 root root 6094 Aug 13 19:49 ib_buffer_pool -rw-r----- 1 root root 12582912 Aug 13 19:49 ibdata1 drwxr-x--- 2 root root 143 Aug 13 19:49 mysql -rw-r----- 1 root root 28311552 Aug 13 19:49 mysql.ibd drwxr-x--- 2 root root 8192 Aug 13 19:49 performance_schema drwxr-x--- 2 root root 28 Aug 13 19:49 sys -rw-r----- 1 root root 16777216 Aug 13 19:49 undo_001 -rw-r----- 1 root root 16777216 Aug 13 19:49 undo_002 -rw-r----- 1 root root 18 Aug 13 19:49 xtrabackup_binlog_info -rw-r----- 1 root root 102 Aug 13 19:49 xtrabackup_checkpoints -rw-r----- 1 root root 484 Aug 13 19:49 xtrabackup_info -rw-r----- 1 root root 2560 Aug 13 19:49 xtrabackup_logfile -rw-r----- 1 root root 39 Aug 13 19:49 xtrabackup_tablespaces [root@centos8 ~]# yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm [root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base [root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base [root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql [root@centos8 ~]#service mysqld start
4. 实现mysql主从复制，主主复制和半同步复制
1）主从复制
主节点
#修改配置文件
[root@centos8 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server_id=152 log-bin=/data/mysql/login/mysql-bin [root@centos8 ~]# mkdir -p /data/mysql/login [root@centos8 ~]# chown -R mysql.mysql /data/mysql/ [root@centos8 ~]# systemctl restart mysqld [root@centos8 ~]# ll /data/mysql/login/ total 8 -rw-r----- 1 mysql mysql 156 Aug 13 22:48 mysql-bin.000001 -rw-r----- 1 mysql mysql 35 Aug 13 22:48 mysql-bin.index
#查看server_id和二进制文件是否修改成功
mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 152 | +-------------+ 1 row in set (0.00 sec) mysql> show master logs; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000001 | 156 | No | +------------------+-----------+-----------+ 1 row in set (0.00 sec)
#创建账号 mysql> create user repluser@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.02 sec) mysql> grant replication slave on *.* to repluser@'10.0.0.%'; Query OK, 0 rows affected (0.01 sec)
从节点
#修改配置文件
[root@centos8 ~]# vim /etc/my.cnf [root@centos8 ~]# systemctl restart mysqld [root@centos8 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server_id=153 read-only mysql> CHANGE MASTER TO -> MASTER_HOST='10.0.0.152', -> MASTER_USER='repluser', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=156; mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.0.0.152 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 156 Relay_Log_File: centos8-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No 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: 156 Relay_Log_Space: 156 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: NULL #开启线程
mysql> start slave;
修改数据
#主节点 10.0.0.152 mysql> create database db1; Query OK, 1 row affected (0.01 sec) #从节点 10.0.0.153 mysql> show databases; +--------------------+ | Database | +--------------------+ | db1 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec)
2）主主复制
主节点1：10.0.0.152
[root@centos8 ~]# vim /etc/my.cnf [root@centos8 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server_id=152 log-bin=/data/mysql/login-bin [root@centos8 ~]# mkdir -p /data/mysql [root@centos8 ~]# chown -R mysql.mysql /data/mysql/ [root@centos8 ~]# systemctl start mysqld mysql> create user repluser@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to repluser@'10.0.0.%'; Query OK, 0 rows affected (0.00 sec)
主节点2：10.0.0.153
[root@centos8 ~]# vim /etc/my.cnf [root@centos8 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server_id=153 log-bin=/data/mysql/login-bin [root@centos8 ~]# mkdir -p /data/mysql [root@centos8 ~]# chown -R mysql.mysql /data/mysql/ mysql> CHANGE MASTER TO -> MASTER_HOST='10.0.0.152', -> MASTER_USER='repluser', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='login-bin.000002', -> MASTER_LOG_POS=156;
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.152
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: login-bin.000002
Read_Master_Log_Pos: 681
Relay_Log_File: centos8-relay-bin.000002
Relay_Log_Pos: 849
Relay_Master_Log_File: login-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
在主节点1中配置从节点
mysql> CHANGE MASTER TO -> MASTER_HOST='10.0.0.153', -> MASTER_USER='repluser', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='binlog.000001', -> MASTER_LOG_POS=695; Query OK, 0 rows affected, 9 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 10.0.0.153 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000001 Read_Master_Log_Pos: 695 Relay_Log_File: centos8-relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: binlog.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes
修改数据看实验是否成功
3）半同步复制
主节点10.0.0.152
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show PLUGINS; +---------------------------------+----------+--------------------+--------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+--------------------+---------+ | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | +---------------------------------+----------+--------------------+--------------------+---------+
[root@centos8 ~]# vim /etc/my.cnf
[root@centos8 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=152
log-bin
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
[root@centos8 ~]# systemctl restart mysqld
mysql> show GLOBAL VARIABLES LIKE '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (3.01 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
从节点10.0.0.153
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> exit Bye [root@centos8 ~]# vim /etc/my.cnf [root@centos8 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=153 rpl_semi_sync_slave_enabled=O
[root@centos8 ~]# systemctl restart mysqld
mysql> show GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.152',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='centos8-bin.000001',
-> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
从节点10.0.0.154
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> exit Bye [root@centos8 ~]# vim /etc/my.cnf [root@centos8 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] server-id=154 rpl_semi_sync_slave_enabled=O
[root@centos8 ~]# systemctl restart mysqld
mysql> show GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.152',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='centos8-bin.000001',
-> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
从节点配置完后查看主节点状态是否配置成功
mysql> show GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 2 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 1 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 2 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
修改数据看是否复制成功
停掉其中一个从节点10.0.0.154 服务 ，观察半同步复制是否成功
5. 用mycat实现mysql的读写分离
MYCAT 10.0.0.152
[root@centos8 ~]#yum -y install java [root@centos8 ~]# mkdir /apps [root@centos8 ~]# ls anaconda-ks.cfg Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz [root@centos8 ~]# tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/ [root@centos8 ~]# echo 'PATH=/apps/mycat/bin:$PSTH' > /etc/profile.d/mycat.sh [root@centos8 ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh [root@centos8 ~]# . /etc/profile.d/mycat.sh [root@centos8 ~]# mycat Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump } [root@centos8 ~]# mycat start Starting Mycat-server... [root@centos8 ~]# tail -f /apps/mycat/logs/wrapper.log STATUS | wrapper | 2022/08/14 16:04:33 | --> Wrapper Started as Daemon STATUS | wrapper | 2022/08/14 16:04:34 | Launching a JVM... INFO | jvm 1 | 2022/08/14 16:04:35 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org INFO | jvm 1 | 2022/08/14 16:04:35 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved. INFO | jvm 1 | 2022/08/14 16:04:35 | INFO | jvm 1 | 2022/08/14 16:04:36 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. INFO | jvm 1 | 2022/08/14 16:04:36 | MyCAT Server startup successfully. see logs in logs/mycat.log [root@centos8 ~]# vim /apps/mycat/conf/server.xml [root@centos8 ~]# cat /apps/mycat/conf/server.xml <property name="serverPort">3306</property> <property name="managerPort">9066</property> <property name="idleTimeout">300000</property> <property name="authTimeout">15000</property> <property name="bindIp">0.0.0.0</property> <property name="dataNodeIdleCheckPeriod">300000</property> [root@centos8 ~]# vim /apps/mycat/conf/schema.xml [root@centos8 ~]# cat /apps/mycat/conf/schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema> <dataNode name="dn1" dataHost="localhost1" database="hellodb" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="host1" url="10.0.0.153:3306" user="root" password="123456"> <readHost host="host2" url="10.0.0.154:3306" user="root" password="123456" /> </writeHost> </dataHost> </mycat:schema> [root@centos8 ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 100 127.0.0.1:25 0.0.0.0:* LISTEN 0 1 127.0.0.1:32000 0.0.0.0:* LISTEN 0 128 *:3306 *:* LISTEN 0 128 *:9066 *:* LISTEN 0 50 *:40525 *:* LISTEN 0 50 *:41589 *:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 100 [::1]:25 [::]:* LISTEN 0 50 *:1984 *:*
主节点10.0.0.153
mysql> create user root@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to root@'10.0.0.%'; Query OK, 0 rows affected (0.00 sec)
从节点10.0.0.154
mysql> show databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | repluser | 10.0.0.% | | root | 10.0.0.% | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 6 rows in set (0.01 sec)
客户端10.0.0.155
[root@localhost ~]# mysql -uroot -p123456 -h 10.0.0.152 mysql> show databases; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.00 sec) mysql> use TESTDB; 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> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ 7 rows in set (0.00 sec)
客户端读操作
mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 154 | +-------------+ 1 row in set (0.00 sec)
客户端写操作
