2024年8月15日（python管理MySQL数据库 mysql5.7读写分离配置mycat）

东边的那根草~

已于 2024-08-15 18:57:57 修改

阅读量1.3k

点赞数 45

文章标签： adb linux 运维 centos python 服务器

于 2024-08-15 17:41:29 首次发布

本文链接：https://blog.csdn.net/weixin_70751333/article/details/141216822

版权

一、pymysql管理数据库

1、搭建主mysql5.7

[root@mysql57 ~]# ls
anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@mysql57 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@mysql57 ~]# ls
anaconda-ks.cfg
mysql-5.7.44-linux-glibc2.12-x86_64
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 ~]# ls /usr/local/mysql/
bin docs lib man README support-files
data include LICENSE mysql-files share
[root@mysql57 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57
[root@mysql57 ~]# service mysql57 start
Starting MySQL.Logging to '/usr/local/mysql/data/mysql57.err'.
SUCCESS!

[root@mysql57 ~]# /usr/local/mysql/bin/mysql -p

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

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

mysql> grant all on *.* to 'lxe'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

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.00 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)

[root@localhost ~]# python3

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

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.176",port=3306,database="test",user="root",password="root");
>>> cursor.execute("create user 'slave0'@'%' identified by 'slave0'")  #创建用户
>>> cursor=conn.cursor()  

>>> 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("show master status")
0
>>> print(cursor.fetchall())
()

[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.176",port=3306,database="test",user="lxe",password="lxe");
>>> cursor=conn.cursor()
>>> cursor.execute("show master status")
1
>>> print(cursor.fetchall())
(('binlog.000001', 154, '', '', ''),)
>>> cursor.execute("unlock tables")
0

2、从服务器

[root@salve57 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@salve57 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
[root@salve57 ~]# ls /usr/local/mysql/
bin include LICENSE README support-files
docs lib man share
[root@salve57 ~]# mkdir /usr/local/mysql/mysql-files
[root@salve57 ~]# useradd -r -s /sbin/nologin mysql
[root@salve57 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@salve57 ~]# chmod 750 /usr/local/mysql/mysql-files/

[root@slave57 ~]# rm -rf /etc/my.cnf
[root@slave57 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

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

[root@salve57 ~]# vim /usr/local/mysql/data/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/data/err.log
log-relay=/usr/local/mysql/data/relaylog
character_set_server=utf8mb4
server-id=11
[root@salve57 ~]# sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin' /etc/profile

[root@salve57 ~]# sed -n '$p' /etc/profile

export PATH=$PATH:/usr/local/mysql/bin
[root@salve57 ~]# source /etc/profile
[root@salve57 ~]# chkconfig --add mysql57
[root@salve57 ~]# chkconfig mysql57 on

[root@salve57 ~]# service mysql57 start

3、主服务器

[root@mysql57 ~]# chmod 750 /usr/local/mysql/mysql-files/
[root@mysql57 ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@mysql57 ~]# yum -y install rsync
[root@mysql57 ~]# rsync -av /usr/local/mysql/data root@192.168.8.177:/usr/local/mysql/

二、企业架构MySQL读写分离

三、mycat读写分离

Mycat数据库中间件

国内最活跃的、性能最好的开源對据库中间件!

官方网址:http://www.mycat.io/https://github.com/MyCATApache/MyCAT-Server/

因为mycat是由java语言开发,必须使用java的运行环境才能进行启动和操作

步骤

1、上传jdk和mycat安装包

[root@mycat ~]# ls
jdk-8u192-linux-x64.tar.gz
Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

2、解压并添加到指定的位置

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

3、配置jdk文件

[root@mycat ~]# sed -i '$aexport JAVA_HOME=/usr/local/jdk' /etc/profile
[root@mycat ~]# sed -n '$p' /etc/profile
export JAVA_HOME=/usr/local/jdk
[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 ~]# sed -n '$p' /etc/profile
export PATH=$PATH:$JAVA_HOME/bin
[root@mycat ~]# source /etc/profile
[root@mycat ~]# $PATH
-bash: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/jdk/bin: 没有那个文件或目录
[root@mycat ~]# javac -version
javac 1.8.0_192

4、测试启动mycat

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

5、找到server.xml schema.xml文件并更改

[root@mycat ~]# vim /usr/local/mycat/conf/server.xml
 93         <user name="lxe" defaultAccount="true">
 94                 <property name="password">lxe</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 -->

 5         <schema name="test" dataNode="dn1" checkSQLschem    a="false" sqlMaxLimit="100">
  6         </schema>
  7         <dataNode name="dn1" dataHost="localhost1" datab    ase="test" />
  8 <!--    <dataNode name="dn2" dataHost="localhost1" datab    ase="db2" />
  9         <dataNode name="dn3" dataHost="localhost1" datab    ase="db3" /> -->

 14         <dataHost name="localhost1" maxCon="1000" minCon    ="10" balance="0"
 15                           writeType="0" dbType="mysql" d    bDriver="native" switchType="1"  slaveThreshold="100">
 16                 <heartbeat>select user()</heartbeat>
 17                 <!-- can have multi write hosts -->
 18                 <writeHost host="hostM1" url="192.168.8.    176:3306" user="lxe"
 19                                    password="lxe">
 20                         <!-- can have multi read hosts -    ->
 21                         <readHost host="hostS2" url="192    .168.8.177:3306" user="lxe" password="lxe" />
 22                 </writeHost>
 23 <!--            <writeHost host="hostS1" url="localhost:    3316" user="root"
 24                                    password="123456" />-
    ->

6、启动mycat并测试

[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.178 -P8066 -ulxe -plxe

mysql> show databases;
+--------------------+
| Database           |
+--------------------+              |
| test               |
+--------------------+


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)