对MySQL数据库读写分离的两种方法

最新推荐文章于 2025-07-07 00:22:25 发布

原创

最新推荐文章于 2025-07-07 00:22:25 发布 · 757 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #adb

一、配置 mysql5.7 的环境

1. 基础配置

# 将 mysql5.7 的包拖入 xshell

[root@mysql_5 ~] # ls

anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

# 解压

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

# 备份文件

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

# 删除文件

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

# 创建 mysql 目录

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

# 创建用户

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

# 修改属主和属组

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

# 修改权限

[root@mysql_5 ~] # chown 750 /usr/local/mysql/mysql-files/

# 初始化

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

basedir=/usr/local/mysql

2024 -08-15T02 :45:14.516552Z 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 :45:14.667185Z 0 [Warning] InnoDB: New log files created, LSN = 45790

2024 -08-15T02 :45:14.702443Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2024 -08-15T02 :45:14.760234Z 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: 663a18b9-5ab0-11ef

a23c-000c29962445.

2024 -08-15T02 :45:14.761289Z 0 [Warning] Gtid table is not ready to be used. Table

'mysql.gtid_executed' cannot be opened.

2024 -08-15T02 :45:15.341756Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use

TLSv1.2 or higher.

2024 -08-15T02 :45:15.341774Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use

TLSv1.2 or higher.

2024 -08-15T02 :45:15.343472Z 0 [Warning] CA certificate ca.pem is self signed.

2024 -08-15T02 :45:15.478916Z 1 [Note] A temporary password is generated for root@localhost:

Usd!cwgSr6A # 2. 登录 mysql

# 其他配置

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

# 启动服务

[root@mysql_5 ~] # service mysql57 start

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

SUCCESS!

# 修改配置文件

[root@mysql_5 ~] # 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@mysql_5 ~] # service mysql57 restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.Logging to '/usr/local/mysql/data/db01-master.err' .

SUCCESS!

# 登录

[root @mysql_5 ~] # /usr/local/mysql/bin/mysql -p

Enter password :Usd!cwgSr6A #

Welcome to the MySQL monitor. Commands end with ; or \g .

Your MySQL connection id is 2

Server version: 5.7.44

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)

# 创建新账户 haha

mysql> create user 'haha' @'%' identified by 'haha' ;

Query OK, 0 rows affected ( 0.00 sec)

# 给权限 二、使用 python 管理数据库

1.python

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

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

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

| host | user |

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

| % | haha |

| % | slave0 |

| localhost | mysql .session |

| localhost | mysql .sys |

| localhost | root |

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

5 rows in set ( 0.00 sec)

# 建立数据库连接 2. 数据库

3.pytho

最低0.47元/天解锁文章

200万优质内容无限畅学