mysql主从数据库（5.7版本）与python的交互及mycat

mysql数据库基本操作：

[root@m ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz         解压压缩包
[root@m ~]# 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@m ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
[root@m ~]# rm -rf  /etc/my.cnf
[root@m ~]# mkdir /usr/local/mysql/mysql-files
[root@m ~]# useradd -r -s /sbin/nologin mysql
[root@m ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@m ~]# chmod 750 /usr/local/mysql/mysql-files/        授予权限
[root@m ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/        进行初始化
2024-08-15T02:37:51.714490Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-15T02:37:51.883374Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-15T02:37:51.927026Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-15T02:37:51.985501Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5e50091b-5aaf-11ef-903e-000c2962cb99.
2024-08-15T02:37:51.986327Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-15T02:37:53.342567Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-15T02:37:53.342586Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-15T02:37:53.344783Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-15T02:37:53.842790Z 1 [Note] A temporary password is generated for root@localhost: UW2j.oFipqq=        获得原始密码
[root@m ~]# 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@m ~]# ls /usr/local/mysql/
bin   docs     lib      man          README  support-files
data  include  LICENSE  mysql-files  share
[root@m ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57
[root@m ~]# service mysql57 start         启动mysql
Starting MySQL.Logging to '/usr/local/mysql/data/m.sql.err'.
 SUCCESS! 

修改配置文件

[root@m ~]# 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@m ~]# /usr/local/mysql/bin/mysql -p        开启mysql
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified by 'root';        修改数据库账号
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'chz'@'%' identified by 'chz';        创建可以连接外部的账号
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'chz'@'%';        授予权限
Query OK, 0 rows affected (0.00 sec)

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

mysql> 

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

mysql> insert into user (username,password)values("aaa","aaa");
Query OK, 1 row affected (0.01 sec)

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

mysql> 
mysql> select host ,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | chz           |
| %         | slave0        |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
5 rows in set (0.00 sec)
mysql> update  mysql.user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;

交互

在python（写成脚本执行）

import pymysql
conn=pymysql.connect(host="192.168.2.50",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")

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

cursor.execute("unlock tables")        取消锁表

mycat

[root@mycat ~]# ls
anaconda-ks.cfg             jre-8u191-linux-x64.tar.gz
jdk-8u192-linux-x64.tar.gz  Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
[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 ~]# ls
anaconda-ks.cfg             jre-8u191-linux-x64.tar.gz
jdk1.8.0_192                mycat
jdk-8u192-linux-x64.tar.gz  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/

查看并且配置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/BIN:/usr/local/jdk/bin: 没有那个文件或目录
[root@mycat ~]# java -version
java version "1.8.0_192"
Java(TM) SE Runtime Environment (build 1.8.0_192-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.192-b12, mixed mode)
[root@mycat ~]# javac -version
javac 1.8.0_192
[root@mycat ~]# /usr/local/mycat/bin/mycat console        启动mycat

Running Mycat-server...
wrapper  | --> Wrapper Started as Console
wrapper  | Launching a JVM...
jvm 1    | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
jvm 1    | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1    |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
jvm 1    | 
jvm 1    | log4j:WARN No appenders could be found for logger (io.mycat.memory.MyCatMemory).
jvm 1    | log4j:WARN Please initialize the log4j system properly.
jvm 1    | log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
jvm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log        启动成功

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

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

数据库的账号，数据库的密码，数据库

注释掉

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

删除前面的，然后修改

[root@mycat ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# netstat -lnput | grep 8066

测试连接

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

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

[root@client bin]# ./mysql -h10.1.1.60 - P8066 -uchz -pchz