python非交互连接mysql+mycat读写分离实现

python非交互连接mysql

>>>import  pymysql

>>>conn=pymysql.connect(host="192.168.118.57",port=3306,database="test",user="root",password="root")
>>> cursor=conn.cursor()

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

0
>>> cursor.execute("grant replication slave on *.* to 'slave0'@'%'")
0
>>> cursor.execute("flush privileges")
0
>>> 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")
1
>>> print(cursor.fetchall())
(('binlog.000001', 1024, '', '', ''),)
>>> 

可以将上述的python连接mysql编写成脚本：

vim  test.py

import pymysql
conn=pymysql.connect(host="192.168.118.57",port=3306,database="test",user="root",password="root");
cursor=conn.cursor()
cursor.execute("create user 'slave2'@'%' identified by 'slave2'")
cursor.execute("grant replication slave on *.*  to 'slave2'@'%'")
cursor.execute("flush privileges")
cursor.execute("flush tables with read lock")
cursor.execute("show master status")
print(cursor.fetchall())

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

if isOK=='y':
        cursor.execute("unlock tables")

mycat读写分离实现

mycat:独属于mysql主从的负载均衡策略，配置写主读从

mycat是用java开发的

步骤：

1.添加一个新主机（192.168.118.70），关闭防火墙，selinux,NetworkManager

2.上传jdk和mycat安装包

3.解压并添加到指定的位置

  224  tar -xf jdk-8u192-linux-x64.tar.gz 
  225  tar -xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz 
  226  cp -r jdk1.8.0_192/ /usr/local/jdk
  227  cp -r mycat/ /usr/local/

4.查看并且配置jdk环境

  229  ls /usr/local/jdk
  230  sed -i '$aexport JAVA_HOME=/usr/local/jdk' /etc/profile

  233  source /etc/profile
  234  $JAVA_HOME
  235  sed -i '$aexport PATH=$PATH:$JAVA_HOME/bin' /etc/profile
  236  source /etc/profile
  237  $PATH

  239  javac -version

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

5.测试启动mycat

  240  ls /usr/local/mycat/
  241  ls /usr/local/mycat/bin
  242  /usr/local/mycat/bin/mycat console

6.找到server.xml 和 schema.xml

ls /usr/local/mycat/conf

7.配置server.xml

vim /usr/local/mycat/conf/schema.xml 

注释掉107-113行

8.配置schema.xml

vim /usr/local/mycat/conf/schema.xm

代码解释：

9.启动服务

/ usr / local / mycat / bin / mycat start

10.检查启动是否成功

netstat -lnput | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      3242/java  

11.测试连接   

[root@master-mysql ~]# mysql -h192.168.118.70 -P8066 -uwangjia -pwangjia
 

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | aaa      | aaa      |
|  2 | bbb      | bbb      |
+----+----------+----------+
2 rows in set (0.05 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 15    |
+---------------+-------+
1 row in set (0.01 sec)