首页 > 数据库 >2024.8.15(python管理mysql、Mycat实现读写分离)

2024.8.15(python管理mysql、Mycat实现读写分离)

时间:2024-08-16 17:27:01浏览次数:10  
标签:15 2024.8 local Mycat usr mysql mysql57 mycat root

一、python管理mysql

1、搭建主mysql

[root@mysql57 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz 
[root@mysql57 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
[root@mysql57 ~]# rm -rf /etc/my.cnf
[root@mysql57 ~]# mkdir /usr/local/mysql/mysql-files
[root@mysql57 ~]# useradd -r -s /sbin/nologin mysql
[root@mysql57 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@mysql57 ~]# chown 750 /usr/local/mysql/mysql-files/
[root@mysql57 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql
[root@mysql57 ~]# ls /usr/local/mysql/
[root@mysql57 ~]# cp /usr/local/mysql/support-files/mysql.server
 /etc/init.d/mysql57
[root@mysql57 ~]# service mysql57 start
[root@mysql57 ~]# /usr/local/mysql/bin/mysql -p
Enter password: 

mysql> alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'blt'@'%' identified by 'blt';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'blt'@'%';
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.02 sec)

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | aaa      | aaa      |
+----+----------+----------+
1 row in set (0.00 sec)
2、pymysql

[root@client ~]# python3 

>>> import pymysql
>>>conn=pymysql.connect(host="192.168.8.150",port=3306,database="test",user="blt",password="blt");
>>> cursor=conn.cursor()

3、修改root权限
mysql> update mysql.user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
>>>conn=pymysql.connect(host="192.168.8.150",port=3306,database="test",user="root",password="root");
>>> curson=conn.cursor()
>>> cursor.execute("create user 'slave0'@'%' identified by 'slave0'")
0
>>> cursor.execute("grant replication slave on *.* to 'slave0'@'%'")
>>> cursor.execute("flush privileges")
>>> cursor.execute("flush tables with read lock")
0
>>> cursor.execute("unlock tables")
0
>>> cursor.execute("flush tables with read lock")
0
>>> cursor.execute("show master status")
0
>>> print(cursor.fetchall())
()
>>> cursor.execute("show master status")
4、my.cnf

[root@mysql57 ~]# rm -rf /etc/my.cnf

[root@mysql57 ~]# 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@mysql57 ~]# service mysql57 restart

>>> conn=pymysql.connect(host="192.168.8.150",port=3306,database="test",user="blt",password="blt");
>>> cursor=conn.cursor()
>>> cursor.execute("show master status")
1
>>> print(cursor.fetchall())
(('binlog.000001', 154, '', '', ''),)
>>> cursor.execute("unlock tables")
0
5、slave数据库

[root@slave57 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz 
[root@slave57 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
[root@slave57 ~]# ls /usr/local/mysql/
[root@slave57 ~]# mkdir /usr/local/mysql/mysql-files
[root@slave57 ~]# useradd -r -s /sbin/nologin mysql
[root@slave57 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@slave57 ~]# chmod 750 /usr/local/mysql/mysql-files/

6、主从同步

[root@mysql57 ~]# rm -rf /usr/local/mysql/data/auto.cnf 
[root@mysql57 ~]# yum -y install rsync

[root@mysql57 ~]# rsync -av /usr/local/mysql/data [email protected]:/usr/local/mysql/

[root@slave57 ~]# yum -y install rsync

[root@slave57 ~]# vim /usr/local/mysql/my.cnf
[root@slave57 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57
[root@slave57 ~]# sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin' /etc/profile
[root@slave57 ~]# sed -n '$p' /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
[root@slave57 ~]# source /etc/profile
[root@slave57 ~]# chkconfig --add mysql57
[root@slave57 ~]# chkconfig mysql57
[root@slave57 ~]# chkconfig mysql57 on

7、错误

[root@slave57 ~]# service mysql57 start
Starting MySQL.2024-08-15T06:29:37.027747Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/usr/local/mysql/data/slave57.pid).

[root@slave57 ~]# mkdir /var/log/mariadb
[root@slave57 ~]# touch /var/log/mariadb/mariadb.log
[root@slave57 ~]# chown -R mysql:mysql /var/log/mariadb/
[root@slave57 ~]# service mysql57 start
Starting MySQL. SUCCESS! 

二、MyCat实现读写分离

1、添加新的虚拟主机,关闭防火墙

[root@mycat ~]# systemctl stop firewalld
[root@mycat ~]# setenforce 0

2、上传jdk和Mycat

[root@mycat ~]# ls

3、解压并上传到指定位置

[root@mycat ~]# tar -xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz 
[root@mycat ~]# tar -xf jdk-8u192-linux-x64.tar.gz 
[root@mycat ~]# cp -r jdk1.8.0_192/ /usr/local/jdk
[root@mycat ~]# cp -r mycat/ /usr/local/

4、查看并且配置jdk文件

[root@mycat ~]# ls /usr/local/jdk/

[root@mycat ~]# sed -i '$aexport JAVA_HOME=/usr/local/jdk' /etc/profile
[root@mycat ~]# source /etc/profile
[root@mycat ~]# $JAVA_HOME
-bash: /usr/local/jdk: 是一个目录
[root@mycat ~]# sed -i '$aexport PATH=$PATH:$JAVA_HOME/bin' /etc/profile
[root@mycat ~]# source /etc/profile

[root@mycat ~]# $PATH
-bash: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/jdk:/usr/local/jdk/bin: 没有那个文件或目录

[root@mycat ~]# javac -version
javac 1.8.0_192

5、测试启动Mycat

[root@mycat ~]# /usr/local/mycat/bin/mycat console

6、配置读写分离

[root@mycat logs]# vim /usr/local/mycat/conf/server.xml 

93         <user name="blt" defaultAccount="true">
94                 <property name="password">blt</property>
95                 <property name="schemas">test</property>
107 <!--
108         <user name="user">
109                 <property name="password">user</property>
110                 <property name="schemas">TESTDB</property>
111                 <property name="readOnly">true</property>
112         </user>
113 -->

[root@mycat logs]# vim /usr/local/mycat/conf/schema.xml  

4         <!-- 1.名称为真实数据库名称,添加一个dataNode-->
5         <schema name="test" dataNode="dn1" checkSQLschema="false" sqlMaxLimit="100">
6         </schema>
7         <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
8                 /> -->
9         <dataNode name="dn1" dataHost="localhost1" database="test" />
20   <writeHost host="hostM1" url="192.168.8.150:3306" user="blt" password="blt">
21            <!-- can have multi read hosts -->
22 <readHost host="hostS2" url="192.168.8. 151:3310" user="blt" password="blt" />
23                 </writeHost>


7、测试

[root@mycat ~]# /usr/local/mycat/bin/mycat start

[root@mycat ~]# netstat -lnput | grep 8066
[root@client ~]# cd mysql-5.7.44-linux-glibc2.12-x86_64/
[root@client mysql-5.7.44-linux-glibc2.12-x86_64]# cd bin/
[root@client bin]# ./mysql -h192.168.8.152 -P8066 -ublt -pblt

mysql> show databases;
+--------------------+
| Database           |
+--------------------+              |
| test               |
+--------------------+
1 rows in set (0.00 sec)

mysql> 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
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | aaa      | aaa      |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
1 row in set (0.02 sec)

标签:15,2024.8,local,Mycat,usr,mysql,mysql57,mycat,root
From: https://blog.csdn.net/weixin_70751278/article/details/141216825

相关文章

  • python管理MySQL数据库 mysql5.7读写分离 配置mycat(twenty-nine day)
    一、pymysql管理数据库1、搭建主mysql5.7[root@mysql57~]#lsanaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz[root@mysql57~]#tar-xfmysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@mysql57~]#lsanaconda-ks.cfgmysql-5.7.44-linux-glibc2......
  • 实训day29(8.15)
    一、python管理mysql1、搭建主mysql[root@mysql57~]#tar-xfmysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@mysql57~]#cp-rmysql-5.7.44-linux-glibc2.12-x86_64/usr/local/mysql[root@mysql57~]#rm-rf/etc/my.cnf[root@mysql57~]#mkdir/usr/local......
  • P1578 奶牛浴场
    题面链接最大子子矩形问题首先一些概念1.有效子矩阵:内部不包含任何障碍点,且边界与坐标轴平行的子矩阵2.极大子矩阵:一个有效子矩阵,如果不包含它,且比它大的有效子矩阵,则为极大有效子矩阵3.最大有效子矩阵:所有有效矩阵中最大面积的子矩阵极大化思想定理1.有一个障碍点的矩形......
  • CF1503E 2-Coloring
    CF1503E2-Coloring题目大意略过。做法解析不会组合,使用了DP,但其实本质相同。我们假设所有的格子都是蓝色的,然后考虑将一些格子换成黄色的。我们考虑从每一行的两头开始将格子换成黄色,只要不把整一行都换成黄色的我们就可以保证每一行恰好有一段蓝色的格子。为了保证每一......
  • 15. 三数之和【 力扣(LeetCode) 】
    一、题目描述给你一个整数数组nums,判断是否存在三元组[nums[i],nums[j],nums[k]]满足i!=j、i!=k且j!=k,同时还满足nums[i]+nums[j]+nums[k]==0。请你返回所有和为0且不重复的三元组。注意:答案中不可以包含重复的三元组。二、测试用例示例1:输......
  • 回顾加密风险投资15年演变:步履维艰,但总体向上
    1.引言:加密风险投资的历程过去15年,加密领域经历了从默默无闻到引爆全球金融市场的巨大变迁。风投机构(VC)在这一过程中扮演了重要角色,推动了区块链和加密货币行业的快速发展。本文将探讨加密风险投资的动态演变,重点介绍这些年里投资策略的变化,特别是从股权投资向代币投资的转变......
  • 世微AP5125 外置MOS管5-150V 8A平均电流型LED降压恒流驱动器 SOT23-6 手电筒与车灯方
    产品描述AP5125是一款外围电路简单的Buck型平均电流检测模式的LED恒流驱动器,适用于8-100V电压范围的非隔离式大功率恒流LED驱动领域。芯片采用固定频率140kHz的PWM工作模式,利用平均电流检测模式,因此具有优异的负载调整率特性,高精度的输出电流特性。AP5125......
  • 【2024-08-15】中青年了
    20:00在生气时,自己照照镜子,这会给你很大的帮助,它将成为敲醒你的正念之钟。                                                 ——一行禅师在上周处理外婆的后事时,跟香......
  • MyCat2,你会了吗?
    1.概述1.1定义MyCAT是目前最流行的分布式数据库中间件之一,是一个开源的分布式数据库系统,是一个实现了MySQL协议的服务器。可把它看作一个数据库代理,可以使用MySQL客户端访问,后端可以用MySQL原生协议与多个MySQL服务器通信,也可以用JDBC协议与大多数主流数据库服务器通信,......
  • CodeForces 1575F Finding Expected Value
    洛谷传送门CF传送门考虑单个序列如何求答案。考虑鞅与停时定理。定义一个局面的势能为\(\sum\limits_{i=0}^{K-1}f(b_i)\),其中\(f(x)\)是一个关于\(x\)的函数,\(b_i\)为\(i\)的出现次数。那么我们要构造\(f(x)\),使得每次操作,局面势能期望减少\(1\),那么期望步数......