第六周作业-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)
客户端写操作
最新文章
- 使用subclass UIActivity的方案来分享图片
- android中Post方式发送HTTP请求
- Nginx配置文件详细说明
- ES TIPS
- C语言初学者代码中的常见错误与瑕疵(14)
- POJ 1159 Palindrome（LCS）
- JavaSE_ IO流 总目录(19~22)
- Java时间比較
- Objective-C NSObject 的实现分析(2014-10-23更新)
- SpringMVC转发和重定向区别！
- mongodb备份恢复,数据导入导出
- Eclipse连接Hadoop集群及WordCount实践
- 第二篇--上传git 代码
- 2017-12-19python全栈9期第四天第三节之iterable可迭代对象join之字符串和列表转换成字符串和range
- Session, Token, OAuth 鉴权那些事儿
- springAOP实现操作日志记录，并记录请求参数与编辑前后字段的具体改变
- PHP 获取给定时间的周日时间或月末时间或每天
- php函数总结2
- Java第九次作业--输入输出流和文件操作
- HDU 5203 Rikka with wood sticks 分类讨论