day29(配置mysql5.7环境+使用python管理数据库+使用中间件mycat配置读写分离)

一、配置mysql5.7的环境

1.基础配置

 # 将mysql5.7的包拖入xshell
[root@mysql_5 ~]# ls
anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
 
# 解压
[root@mysql_5 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz 
 
# 备份文件
[root@mysql_5 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
 
# 删除文件
[root@mysql_5 ~]# rm -rf /etc/my.cnf
 
# 创建mysql目录
[root@mysql_5 ~]# mkdir /usr/local/mysql/mysql-files
 
# 创建用户
[root@mysql_5 ~]# useradd -r -s /sbin/nologin mysql
 
# 修改属主和属组
[root@mysql_5 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
 
# 修改权限
[root@mysql_5 ~]# chown 750 /usr/local/mysql/mysql-files/
 
# 初始化
[root@mysql_5 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --
basedir=/usr/local/mysql
2024-08-15T02:45:14.516552Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. 
Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-15T02:45:14.667185Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-15T02:45:14.702443Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-15T02:45:14.760234Z 0 [Warning] No existing UUID has been found, so we assume that this 
is the first time that this server has been started. Generating a new UUID: 663a18b9-5ab0-11efa23c-000c29962445.
2024-08-15T02:45:14.761289Z
0 [Warning] Gtid table is not ready to be used. Table 
'mysql.gtid_executed' cannot be opened.
2024-08-15T02:45:15.341756Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use 
TLSv1.2 or higher.
2024-08-15T02:45:15.341774Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use 
TLSv1.2 or higher.
2024-08-15T02:45:15.343472Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-15T02:45:15.478916Z 1 [Note] A temporary password is generated for root@localhost: 
Usd!cwgSr6A#
# 其他配置
[root@mysql_5 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57
 
# 启动服务
[root@mysql_5 ~]# service mysql57 start
Starting MySQL.Logging to '/usr/local/mysql/data/mysql_5.7.err'.
 SUCCESS! 
 
 # 修改配置文件
[root@mysql_5 ~]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/usr/local/mysql/data/db01-master.err
log-bin=/usr/local/mysql/data/binlog
server-id=10
character_set_server=utf8mb4
 
# 重新启动服务
[root@mysql_5 ~]# service mysql57 restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.Logging to '/usr/local/mysql/data/db01-master.err'.
 SUCCESS! 

2.登录mysql

# 其他配置
[root@mysql_5 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57
 
# 启动服务
[root@mysql_5 ~]# service mysql57 start
Starting MySQL.Logging to '/usr/local/mysql/data/mysql_5.7.err'.
 SUCCESS! 
 
 # 修改配置文件
[root@mysql_5 ~]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/usr/local/mysql/data/db01-master.err
log-bin=/usr/local/mysql/data/binlog
server-id=10
character_set_server=utf8mb4
 
# 重新启动服务
[root@mysql_5 ~]# service mysql57 restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.Logging to '/usr/local/mysql/data/db01-master.err'.
 SUCCESS! 
# 登录
[root@mysql_5 ~]# /usr/local/mysql/bin/mysql -p
Enter password:Usd!cwgSr6A#
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
# 修改密码
mysql> alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.00 sec)
 
# 创建新账户haha
mysql> create user 'haha'@'%' identified by 'haha';
Query OK, 0 rows affected (0.00 sec)
 
# 给权限

mysql> grant all on *.* to 'haha'@'%';
Query OK, 0 rows affected (0.00 sec)
 
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
# 创建数据库
mysql> create database if not exists test charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
 
# 使用数据库
mysql> use test;
Database changed
 
# 创建表
mysql> create table user(id int primary key auto_increment,username varchar(45) not 
null,password varchar(45) not null);
Query OK, 0 rows affected (0.01 sec)
 
# 给表中插入数据
mysql> insert into user (username,password)values("aaa","aaa");
Query OK, 1 row affected (0.00 sec)
 
# 查看表
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | aaa | aaa |
+----+----------+----------+
1 row in set (0.00 sec)
 
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | haha |
| % | slave0 |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
5 rows in set (0.00 sec)
 

二、使用python管理数据库

1.python

#建立数据库连接

 
>>> 
conn=pymysql.connect(host="192.168.2.57",port=3306,database="test",user="haha",password="haha")
 
# 创建游标对象
>>> cursor=conn.cursor()
 
# 创建用户
>>> cursor.execute("create user 'slave0'@'%' identified by 'slave0'")
0
 
# 给slave用户给权限（不能执行，因为haha账户没有权限给slave权限，所以会报错）
>>> cursor.execute("grant replication slave on *.* to 'slave0'@'%'")
Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
 File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 148, in execute
 result = self._query(query)
 File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 310, in _query
 conn.query(q)
 File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 548, in query
 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
 File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 775, in 
_read_query_result
 result.read()
 File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1156, in read
 first_packet = self.connection._read_packet()
 File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 725, in 
_read_packet
 packet.raise_for_error()
 File "/usr/local/lib/python3.6/site-packages/pymysql/protocol.py", line 221, in 
raise_for_error
 err.raise_mysql_exception(self._data)
 File "/usr/local/lib/python3.6/site-packages/pymysql/err.py", line 143, in 
raise_mysql_exception
 raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'haha'@'%' (using password: YES)")
>>> 
 

2.数据库

3.python

4.在python中进行锁表

5.数据库

6.python

7.使用python自动化管理数据库

三、配置主从数据库

搭建从数据库（mysql5.7版本）

[root@slave_5 ~]# ls
anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
 
# 解压
[root@slave_5 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
 
# 备份文件
[root@slave_5 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
 
# 删除文件
[root@slave_5 ~]# rm -rf /etc/my.cnf
 
# 创建目录
[root@slave_5 ~]# mkdir /usr/local/mysql/mysql-files
 
# 创建用户

[root@slave_5 ~]# useradd -r -s /sbin/nologin mysql
 
# 修改属主和属组
[root@slave_5 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
 
# 修改权限
[root@slave_5 ~]# chown 750 /usr/local/mysql/mysql-files/
 
# 停止服务（主数据库）
[root@mysql_5 ~]# service mysql57 stop
 
# 删除data中的auto.cnf(主数据库)
[root@mysql_5 ~]# rm -rf /usr/local/mysql/data/auto.cnf
 
# 将主数据库的data同步到从数据库中
[root@mysql_5 ~]# rsync -av /usr/local/mysql/data root@192.168.2.58:/usr/local/mysql/
 
# 修改配置文件（从数据库）
[root@slave_5 ~]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3310
log-error=/usr/local/mysql/data/err.log
relay-log=/usr/local/mysql/data/relaylog
character_set_server=utf8mb4
server-id=11
 
# 其他配置
[root@slave_5 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57
 
# 配置环境变量
[root@slave_5 ~]# sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin' /etc/profile
[root@slave_5 ~]# sed -n '$p' /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
[root@slave_5 ~]# source /etc/profile
 
# 设置开机自启
[root@slave_5 ~]# chkconfig --add mysql57
[root@slave_5 ~]# chkconfig mysql57 on
[root@slave_5 ~]# service mysql57 start
Starting MySQL.Logging to '/usr/local/mysql/data/err.log'.
 SUCCESS! 
 
# 登录root账户
[root@slave_5 ~]# mysql -uroot -pHui@2003
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
 
# 登录root账户（主数据库）
[root@mysql_5 ~]# mysql -proot
mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
# 锁表
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
 
# 查看master状态信息
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 154 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
 
# 登录root用户（从数据库）
[root@slave_5 ~]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.44 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
# 配置change master to(从数据库)
mysql> change master to 
master_host="192.168.2.57",master_user="slave0",master_passworrd="slave0",master_log_file="binlo
g.000002",master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
# 启动slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
# 查看主从配置详细信息
mysql> show slave status\G;

*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.2.57
 Master_User: slave0
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: binlog.000002
 Read_Master_Log_Pos: 154
 Relay_Log_File: relaylog.000002
 Relay_Log_Pos: 317
 Relay_Master_Log_File: binlog.000002
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 
# 主数据库
 
# 解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
 
# 插入数据
mysql> insert into test.user (username,password)values('xxxxx','xxxxx');
Query OK, 1 row affected (0.03 sec)
 
# 从数据库
 
# 数据已经同步
mysql> select * from test.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | aaa | aaa |
| 2 | bbbb | bbbbb |
| 3 | xxxxx | xxxxx |
+----+----------+----------+
3 rows in set (0.00 sec)

四、配置读写分离（中间件）

读写分离的配置文件：

1.添加一个新的虚拟主机

设置ip为192.168.2.59，主机名为mycat

2.上传jdk和mycat包

3.解压

4.配置环境变量

5.查看版本

6.测试mycat启动

7.找到server.xml和scheme.xml文件

8.配置server.xml

9.配置schema.xml文件

10.启动服务

五、测试mycat

1.客户端

2.远程工具登录

在Navicat中登录查看表中的数据：