python管理mysql（实现读写分离）及如何用Mycat读写分离

Day24  

编写python代码实现读写分离

1、安装pymysql，它是python管理mysql的驱动，或者称为连接器

[root@pyhton ~]#pip3 config set global.index-url Simple Index

[root@python ~]# pip3 install pymysql   #安装pymysql

[root@pyhton ~]# python3

Python 3.6.8

[root@pyhton ~]# yum -y install pymysql

2、编写rwsplit.py文件

# 引入模块 python链接mysql工具，驱动包，连接器

import pymysql

# python 类 类名 rwsplit,名字可以和文件名不一致

# 三个函数，函数的标识 def 函数名 (self,参数列表):

# __init__ 初始化函数，构造函数，在这个类被实例的时候，执行__init__函数

# master_statment 自定义函数，根据实际需要，开发或者运维自己定义的函数

# slave_statement 自定义函数，根据实际需要，自定

class rwsplit(object):

# 构造函数

# 1.在屏幕上输出了 initialized

# 2.创建了全局变量master_conn

# 3.创建了全局变量slave_conn

# 4.全局变量可以在其他的函数中进行调用和修改

# 5.为什么要在init函数中创建全局变量，因为init在rwsplit类被实例的同时执行

def __init__ (self):

print("initialized")

self.master_conn=pymysql.connect(

host="10.1.1.11",

user="zhangmin",

password="zhangmin",

database="test",

port=3306

)

self.slave_conn=pymysql.connect(

host="10.1.1.12",

user="zhangmin",

password="zhangmin",

database="test",

port=3310

)

# 输出serverid，执行sql语句，增删改查

def master_statment(self,sql):

cursor=self.master_conn.cursor()

cursor.execute("show variables like 'server_id'")

print(cursor.fetchall())

print(cursor.execute(sql))

self.master_conn.commit()

# 执行查询操作

def slave_statment(self,sql):

cursor=self.slave_conn.cursor()

cursor.execute(sql)

print(cursor.fetchall())

# 入口

if __name__ == "__main__":

# 实例rwsplit

demo=rwsplit()

# 要求用户输入一个sql语句

sql=input("sign sql:")

#判断sql

if sql[:6]=="select" or sql[:4]=="show":

demo.slave_statment(sql)

else:

demo.master_statment(sql)

从服务器登录mysql 8.x遇到的问题(每次要获取公钥)之解决方案

[root@salve_mysql ~]# help change master

# Get_master_public_key: 1/0   【自动获取/不自动获取】

[root@salve_mysql ~]#mysql -uroot -p

Enter password:Juan@1888$

mysql> show slave status\G

# 先停用slave服务

[root@salve_mysql ~]#stop slave;

# 重写change_master，解决每次登录从服务器都需要公钥的问题:

mysql>change master to

master_host="192.168.2.206",

master_user='slave0',

master_password='slave0',

master_port=3306,

master_laster_log_file='binlog.000001',

master_log_pos=748;

# 然后启动slave并查看状态

mysql>start slave;

mysql>show slave status\G

mysql -uslave -p123 -h10.0.0.13 -P3306 --get-server-public-key         

//获取服务器的公共密钥

python管理mysql(192.168.2.206)

# mysql5.7的安装和配置，此版本没有公钥

1、下载并压缩软件包

[root@mysql5 ~]# ls

anaconda-ks.cfg

initserver.sh

mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

soft

[root@mysql5 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

2、移动目录

[root@mysql5 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql

3、清空/etc目录下的my.cnf

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

3、创建用户和文件并修改文件的所属组

[root@mysql5 ~]# mkdir /usr/local/mysql/mysql-files

[root@mysql5 ~]# useradd -r -s /sbin/nologin mysql

[root@mysql5 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/

[root@mysql5 ~]# chmod 750 /usr/local/mysql/mysql-files/

4、初始化数据库，找到初始密码 

[root@mysql5 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql

A temporary password is generated for root@localhost: uK%b!EI&I9rf

5、查看是否生成data目录

[root@mysql5 ~]#  ls /usr/local/mysql/

bin   include  man          share

data  lib      mysql-files  support-files

docs  LICENSE  README

6、把mysql.server文件放到/etc/init.d/目录下

[root@mysql5 ~]#  cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57

[root@mysql5 ~]# service mysql57 start

Starting MySQL.Logging to '/usr/local/mysql/data/mysql5.7.err'.

SUCCESS!

7、编写配置文件

[root@mysql5 ~]# 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@mysql5 ~]# service mysql8 start

8、首次登录并设置创建可远程的连接用户huajuan

[root@mysql5 ~]# /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 'huajuan'@'%' identified by 'huajuan';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'huajuan'@'%';

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 myqsl.user;

mysql> update mysql.user set host='%' where user='root';

mysql> flush privileges;

#插入记录，发现此时卡住,取消锁表才可执行成功

mysql> insert into user (username, password) values('bbb', 'bbbb');

# python3管理mysql的具体配置

[root@pyhton ~]# yum -y install pymysql

[root@pyhton ~]# python3

# 编写.test.py文件实现

import pymysql

conn=pymysql.connect(host='192.168.2.220',port=3306,database='test',user='root', password='root' )

cursor=conn.cursor()

# 创建用户slave0，此时root拒绝远程登录

cursor.execute("create user 'slave0'@'%'%' identified by 'slave0'")

cursor.execute('grant replication slave on *.* to "slave"')

# 这次允许root远程，可以添加权限

conn=pymysql.connect(host='', port=0)

cursor.execute("grant replication slave on *.* to 'slave0'@'%'")

# 刷新权限并锁表

cursor.execute("flush privileges")

cursor.execute("flush tables with read lock")

#获得master的状态

cursor.execute("show master status")

print(cursor.fetchall())

(('binlog.000001'))

# 等待slave就绪后解锁，此时才可插入记录

isOk=input("slave server ok? y/n")

if isOK=='y':

cursor.execute("unlock tables")

同步数据，将master的data文件传给slave

[root@mysql5 ~]# scp -r /usr/local/mysql/data root@192.168.2.222:/usr/local/mysql/

[root@slave57 ~]# ls /usr/local/mysql/

bin   docs     lib      man     mysql-files  share

data  include  LICENSE  my.cnf  README       support-files

[root@slave57 ~]# ls /usr/local/mysql/data/

auto.cnf       ca.pem           ib_logfile0         public_key.pem

binlog.000001  client-cert.pem  ib_logfile1         server-cert.pem

binlog.000002  client-key.pem   mysql               server-key.pem

binlog.000003  db01-master.err  mysql5.7.err        sys

binlog.index   ib_buffer_pool   performance_schema  test

ca-key.pem     ibdata1          private_key.pem

[root@mysql5 ~]# service mysql57 start

Starting MySQL. SUCCESS

# 检查是否同步

# 解锁

[root@mysql5 ~]# unlock tables;

# 插入数据进行验证

注意!!!：遇到启动mysql报错时解决方案

[root@mysql5 ~]#  ps -ef | grep mysqld

mysql      3396      1  0 14:40 pts/1    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/db01-master.err --pid-file=/usr/local/mysql/data/mysql5.7.pid --socket=/tmp/mysql.sock --port=3306

root       3624   3147  0 14:43 pts/2    00:00:00 grep --color=auto mysqld

[root@mysql5 ~]# kill -9 3396

Mycat读写分离

环境准备

# mycat(192.168.2.60)  client(192.168.2.215)

配置步骤

1、上传jdk和mycat安装包

[root@mycat ~]# ls

anaconda-ks.cfg

jdk-8u192-linux-x64.tar.gz

Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

Soft

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

[root@mycat ~]# tar -xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

[root@mycat ~]# tar -xf jdk-8u192-linux-x64.tar.gz

3、查看并且配置jdk环境

[root@mycat ~]# ls

anaconda-ks.cfg

jdk1.8.0_192

jdk-8u192-linux-x64.tar.gz

mycat

Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

soft

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

bin man

COPYRIGHT README.html

include release

javafx-src.zip src.zip

jre THIRDPARTYLICENSEREADMEJAVAFX.txt

4、将文件移动到默认启动文件目录下

[root@mycat ~]# cp -r jdk1.8.0_192/ /usr/local/jdk

[root@mycat ~]# cp -r mycat/ /usr/local/

[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 ~]# source /etc/profile

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

export PATH=$PATH:$JAVA_HOME/bin

[root@mycat ~]# $PATH

-bash: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/jdk/bin: 没有那个文件或目录

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

bin  catlet  conf  lib  logs  version.txt

[root@mycat ~]# ls -ld /usr/local/mycat/

drwxr-xr-x. 7 root root 85 8月  15 15:20 /usr/local/mycat/

[root@mycat ~]# ll /usr/local/mycat/

总用量 12

drwxr-xr-x. 2 root root  190 8月  15 15:20 bin

drwxr-xr-x. 2 root root    6 8月  15 15:20 catlet

drwxr-xr-x. 4 root root 4096 8月  15 15:20 conf

drwxr-xr-x. 2 root root 4096 8月  15 15:20 lib

drwxr-xr-x. 2 root root    6 8月  15 15:20 logs

-rwxr-xr-x. 1 root root  219 8月  15 15:20 version.txt

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

dataMigrate.sh   startup_nowrap.sh

init_zk_data.sh  wrapper-linux-ppc-64

mycat            wrapper-linux-x86-32

rehash.sh        wrapper-linux-x86-64

5、测试启动mycat

[root@mycat ~]# /usr/local/mycat/bin/mycat consoleRunning Mycat-server...

jvm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log

6、找到并修改server.xmml 和schema.xml（真实数据库信息）文件

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

server.xml  schema.xml

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

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

7、启动服务并检查是否启动成功

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

Starting Mycat-server...

[root@mycat ~]# ss -lntpu|grep 8066  # 端口号

tcp    LISTEN     0      100      :::8066                 :::*                   users:(("java",pid=2098,fd=81))

8、连接测试

[root@client ~]# ls

anaconda-ks.cfg

mysql-8.0.33-linux-glibc2.12-x86_64

mysql-8.0.33-linux-glibc2.12-x86_64.tar

mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

mysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xz

mysql-test-8.0.33-linux-glibc2.12-x86_64.tar.xz

soft

[root@client ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64/

[root@client mysql-8.0.33-linux-glibc2.12-x86_64]# ls

bin   include  LICENSE  README  support-files

docs  lib      man      share

[root@client mysql-8.0.33-linux-glibc2.12-x86_64]# cd bin/

[root@client bin]# ./mysql -h192.168.2.60 -P8066 -uhuajuan -phuajuan

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

# 实现了读写分离，master能读和插入记录，slave只能读取

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 11 |

+---------------+-------+

1 row in set (0.01 sec)