1、python脚本实现数据库主从分离
# 引入模块 python链接mysql工具，驱动包，连接器
import pymysql
# python 类 类名 rwsplit,名字可以和文件名不一致
# 三个函数，函数的标识 def 函数名 (self,参数列表):
# __init__ 初始化函数，构造函数，在这个类被实例的时候，执行__init__函数
# master_statement 自定义函数，根据实际需要，开发或者运维自己定义的函数
# 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.0.0.57",
user="li",
password="li",
database="test",
port=3306
)
self.slave_conn=pymysql.connect(
host="10.0.0.58",
user="li",
password="li",
database="test",
port=3310
)
# 输出serverid，执行sql语句，增删改查
def master_statment(self,sql):
cursor=self.master_conn.cursor()
cursor.execute("show variables like 'server_id'") #在 MySQL中用于查询服务器标识（server_id）变量值的命令
print(cursor.fetchall())
cursor.execute(sql)
print(cursor.fetchall())
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)
2、mysql主从自动开机同步
# 主数据库：
mysql> flush tables with read lock;
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 154 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> select host,user from mysql.user;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 6
Current database: test
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | li |
| % | root |
| % | slave0 |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
5 rows in set (0.02 sec)
# 从数据库：
mysql> help change master to
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] ... [ channel_option ]
option: {
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_RETRY_COUNT = count
| MASTER_DELAY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'source_log_name'
| MASTER_LOG_POS = source_log_pos
| MASTER_AUTO_POSITION = {0|1}
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_CRL = 'crl_file_name'
| MASTER_SSL_CRLPATH = 'crl_directory_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| MASTER_TLS_VERSION = 'protocol_list'
| IGNORE_SERVER_IDS = (server_id_list)
}
mysql> change master to master_host="10.0.0.57",master_user="slave0",master_password="123",master_log_file="binlog.000002",master_log_pos=154,get_master_public_key=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.57
Master_User: slave0
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 154
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 从数据库重启：
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.57
Master_User: slave0
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 154
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3、配置mysql57
mysql-5.7.44-linux-glibc2.12-x86_64.tar
[root@mysql57 ~]# yum -y install lrzsz
[root@mysql57 ~]# tar -zxf 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 ~]# chmod 750 /usr/local/mysql/mysql-files/
[root@mysql57 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql
2024-08-15T02:41:36.903061Z 1 [Note] A temporary password is generated for root@localhost: zc0fLfoM%SDP
[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 ~]# 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
[root@mysql57 ~]# sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile
[root@mysql57 ~]# source /etc/profile
[root@mysql57 ~]# mysql -p'zc0fLfoM%SDP'
mysql> alter user 'root'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'li'@'%' identified by 'li';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'li'@'%';
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.01 sec)
mysql> use test;
Database changed
mysql> create table user(id int primary key auto_increment,username varchar(45) notnull,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.01 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | aaa | aaa |
+----+----------+----------+
1 row in set (0.00 sec)
4、使用python操纵mysql数据库
（1）使用普通用户连接数据库并创建新用户
# python：
>>> import pymysql
>>> conn=pymysql.connect(host="10.0.0.57",port=3306,database="test",user="li",password="li")
>>> cursor=conn.cursor()
>>> cursor.execute("create user 'slave0'@'%' identified by '123'")
0
# mysql：
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | li |
| % | slave0 |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
5 rows in set (0.00 sec)
（2）使用root用户连接数据库并为用户授予权限
# mysql:
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)
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | li |
| % | root |
| % | slave0 |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
5 rows in set (0.00 sec)
# python:
>>> conn=pymysql.connect(host="10.0.0.57",port=3306,database="test",user="root",password="123");
>>> cursor=conn.cursor()
>>> cursor.execute("grant replication slave on *.* to 'slave0'@'%'")
0
# mysql:
mysql> show grants for 'li'@'%';
+-----------------------------------------+
| Grants for li@% |
+-----------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'li'@'%' |
+-----------------------------------------+
1 row in set (0.01 sec)
（3）使用python将数据库锁定
# python
>>> cursor.execute("flush tables with read lock")
0
mysql执行插入语句会宕机不动
（4）使用python将数据库解锁
# python
>>> cursor.execute("unlock tables")
0
mysql执行语句立马执行成功
（5）使用普通用户连接数据库查看master状态
# python
>>> conn=pymysql.connect(host="10.0.0.57",port=3306,database="test",user="li",password="li");
>>> cursor=conn.cursor()
>>> cursor.execute("show master status")
1
>>> print(cursor.fetchall())
(('binlog.000001', 154, '', '', ''),)
5、编辑python脚本自动化操纵mysql数据库
[root@python ~]# vim test0.py
import pymysql
conn=pymysql.connect(host="10.1.1.15",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")
1、创建mysql57从数据库并进行数据同步
主数据库：
[root@mysql57 ~]# chkconfig --add mysql57 //设置开机自启
[root@mysql57 ~]# chkconfig mysql57 on
[root@mysql57 ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@mysql57 ~]# service mysql57 stop
Shutting down MySQL.. SUCCESS!
[root@mysql57 ~]# yum -y install rsync
[root@mysql57 ~]# rsync -av /usr/local/mysql/data root@10.0.0.58:/usr/local/mysql/
[root@mysql57 ~]# service mysql57 start
Starting MySQL. SUCCESS!
[root@mysql57 ~]# mysql57 -p'123'
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 154 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从数据库：
[root@slavemysql ~]# systemctl stop firewalld
[root@slavemysql ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@slavemysql ~]# tar -zxf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@slavemysql ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
[root@slavemysql ~]# mkdir /usr/local/mysql/mysql-files
[root@slavemysql ~]# useradd -r -s /sbin/nologin mysql
[root@slavemysql ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@slavemysql ~]# chmod 750 /usr/local/mysql/mysql-files/
[root@slavemysql ~]# yum -y install rsync
[root@slavemysql ~]# ls /usr/local/mysql/
bin/ include/ man/ share/
data/ lib/ mysql-files/ support-files/
docs/ LICENSE README
[root@slavemysql ~]# rm -rf /etc/my.cnf
[root@slavemysql ~]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
activate_all_roles_on_login=on
port=3310
log-error=/usr/local/mysql/data/db01-slave.err
relay-log=/usr/local/mysql/data/relaylog
server-id=11
character_set_server=utf8mb4
[root@slavemysql ~]# service mysql57 start
[root@slavemysql ~]# chkconfig --add mysql57
[root@slavemysql ~]# chkconfig mysql57 on
[root@slavemysql ~]# mysql -p'123'
mysql> change master to master_host="10.0.0.57",master_user="slave0",master_password=="123",master_log_file="binlog.000002",master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.57
Master_User: slave0
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 154
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2、同步后测试同步是否成功
从数据库同步完成后主数据库向数据库表中插入数据测试：
mysql> unlock tables;
Query OK, 0 rows affected (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 -A
Database changed
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | aaa | aaa |
| 2 | bbbb | bbbb |
+----+----------+----------+
2 rows in set (0.00 sec)
mysql> insert into user values(3,'ccccc','ccccc');
Query OK, 1 row affected (0.01 sec)
从同步完成后从数据库检查是否同步成功：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.01 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 -A
Database changed
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | aaa | aaa |
| 2 | bbbb | bbbb |
| 3 | ccccc | ccccc |
+----+----------+----------+
3 rows in set (0.01 sec)
3、学习MyCat插件
（1）了解MyCat
MyCat 是一个开源的分布式数据库中间件。
它的主要作用是实现数据库的分布式扩展和数据分片。通过 MyCat，可以将一个大型数据库系统拆分成多个小型的逻辑数据库，从而提高数据库的性能、可用性和可扩展性。
例如，如果一个数据库中的表数据量非常大，查询性能下降，通过 MyCat 可以将该表的数据按照一定的规则（如按照主键范围、哈希值等）分布到多个数据库节点上，从而减轻单个节点的负载，提高查询效率。
MyCat 还支持读写分离，将读操作分发到从库，写操作发送到主库，以平衡数据库的读写压力。
此外，它提供了一些高级功能，如数据的聚合、排序、分页等，使得在分布式环境下进行复杂的数据库操作变得更加容易。
总之，MyCat 为解决数据库在大规模数据处理和高并发访问场景下的性能和扩展问题提供了有效的解决方案。
（2）配置MyCat
1、添加一个新的虚拟主机，设置ip为10.0.0.60，主机名为mycat，关闭防火墙
2、上传jdk和mycat安装包
3、解压并且添加到指定的位置
1）安装配置jdk，制造mycat启动环境
[root@mycat ~]# systemctl stop firewalld
[root@mycat ~]# systemctl disable firewalld
[root@mycat ~]# ls
anaconda-ks.cfg Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
jdk-8u192-linux-x64.tar.gz v
[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/
bin lib src.zip
COPYRIGHT LICENSE THIRDPARTYLICENSEREADME-JAVAFX.txt
include man THIRDPARTYLICENSEREADME.txt
javafx-src.zip README.html
jre release
[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:/usr/local/jdk/bin: 没有那个文件或目录
[root@mycat ~]# javac -version
javac 1.8.0_192
2）测试启动mycat到控制台
[root@mycat ~]# ls /usr/local/mycat/
bin catlet conf lib logs version.txt
[root@mycat ~]# ll /usr/local/mycat/
总用量 12
drwxr-xr-x. 2 root root 190 8月 15 15:19 bin
drwxr-xr-x. 2 root root 6 8月 15 15:19 catlet
drwxr-xr-x. 4 root root 4096 8月 15 15:19 conf
drwxr-xr-x. 2 root root 4096 8月 15 15:19 lib
drwxr-xr-x. 2 root root 6 8月 15 15:19 logs
-rwxr-xr-x. 1 root root 219 8月 15 15:19 version.txt
[root@mycat ~]# ls /usr/local/mycat/bin/
mycat
[root@mycat ~]# /usr/local/mycat/bin/mycat console
Running Mycat-server...
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
|
3）查看修改mycat配置文件
[root@mycat ~]# ls /usr/local/mycat/conf/
schema.xml server.xml
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml //配置真实数据库信息
4）启动mycat服务，实现数据库读写分离
[root@mycat ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# netstat -lnput | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 11528/java
5）mycat服务测试，使用mycat连接数据库并添加一条表记录
[root@mysql57 ~]# mysql -h10.0.0.60 -P8066 -uli -p'li'
mysql> show databases;
+----------+
| DATABASE |
+----------+
| test |
+----------+
1 row in set (0.01 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 -A
Database changed
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | aaa | aaa |
| 2 | bbbb | bbbb |
| 3 | ccccc | ccccc |
+----+----------+----------+
3 rows in set (0.01 sec)
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 10 |
+---------------+-------+
1 row in set (0.02 sec)
mysql> insert into user values(4,'ddd','ddd');
Query OK, 1 row affected (0.06 sec)
6）mycat服务测试，使用从服务器查看是否同步数据
从数据库
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | aaa | aaa |
| 2 | bbbb | bbbb |
| 3 | ccccc | ccccc |
| 4 | ddd | ddd |
+----+----------+----------+
4 rows in set (0.00 sec)
7）mycat服务测试，使用Navicat工具连接mycat查看数据库信息
