2024.8.15（python管理mysql、Mycat实现读写分离）
一、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: 0mysql> 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 root@192.168.8.151:/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 -pbltmysql> 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 -ADatabase 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)