（第二十九天）-优快云博客

本文链接：https://blog.youkuaiyun.com/2301_78422961/article/details/141230164

一、配置 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 #

# 其他配置

[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!

2. 登录 mysql

# 登录

[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)

# 给权限

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)

二、使用 python 管理数据库

1.python

# 建立数据库连接

>>>

conn = pymysql . connect ( host = "192.168.2.57" , port = 3306 , database = "test" , user = "haha" , password = "haha" )

# 创建游标对象

>>> cursor = conn . cursor ()

# 创建用户

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

# 给 slave 用户给权限（不能执行，因为 haha 账户没有权限给 slave 权限，所以会报错）

>>> cursor . execute ( "grant replication slave on *.* to 'slave0'@'%'" )

Traceback ( most recent call last ):

File "<stdin>" , line 1 , in < module >

File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py" , line 148 , in execute

result = self . _query ( query )

File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py" , line 310 , in _query

conn . query ( q )

File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py" , line 548 , in query

self . _affected_rows = self . _read_query_result ( unbuffered = unbuffered )

File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py" , line 775 , in

_read_query_result

result . read ()

File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py" , line 1156 , in read

first_packet = self . connection . _read_packet ()

File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py" , line 725 , in

_read_packet

packet . raise_for_error ()

File "/usr/local/lib/python3.6/site-packages/pymysql/protocol.py" , line 221 , in

raise_for_error

err . raise_mysql_exception ( self . _data )

File "/usr/local/lib/python3.6/site-packages/pymysql/err.py" , line 143 , in

raise_mysql_exception

raise errorclass ( errno , errval )

pymysql . err . OperationalError : ( 1045 , "Access denied for user 'haha'@'%' (using password: YES)" )

>>>

2. 数据库

# 修改 root 权限，允许 root 远程登录

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 ;

Query OK, 0 rows affected ( 0.00 sec)

3.python

# 在 python 中使用 root 账户登录

# 建立连接

>>>

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

# 创建游标对象

>>> cursor = conn . cursor ()

# 给 slave0 账户给权限

>>> cursor . execute ( "grant replication slave on *.* to 'slave0'@'%'" )

4. 在 python 中进行锁表

# 锁表

>>> cursor . execute ( "flush table with read lock" )

# 锁表后，在 MySQL 中插入数据，不能创建，一直卡在插入数据的那里，插不进去，一旦解锁， MySQL 中插入数据的这条

语句就会立马执行成功

# 解锁

>>> cursor . execute ( "unlock tables" )

5. 数据库

# 在数据库中插入数据

mysql> insert into user (username, password ) values ( 'bbbb' , 'bbbbb' );

Query OK, 1 row affected ( 23.77 sec)

6.python

# 使用 python 查询 mysql 的状态信息

# 建立连接

>>>

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

# 创建游标对象

>>> cursor = conn . cursor ()

# 执行查询数据库的语句

>>> cursor . execute ( "show master status" )

# 将查到的语句进行打印

>>> print ( cursor . fetchall ())

(( 'binlog.000001' , 154 , '' , '' , '' ),)

# 补充

# 查看状态信息，查到是空的话是因为没有写 /usr/local/mysql/my.cnf 配置文件

>>> cursor . execute ( "show master status" )

>>> print ( cursor . fetchall ())

()

7. 使用 python 自动化管理数据库

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" )

三、配置主从数据库

搭建从数据库（ mysql5.7 版本）

[root @slave_5 ~] # ls

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

# 解压

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

# 备份文件

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

# 删除文件

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

# 创建目录

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

# 创建用户

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

# 修改属主和属组

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

# 修改权限

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

# 停止服务（主数据库）

[root @mysql_5 ~] # service mysql57 stop

# 删除 data 中的 auto.cnf( 主数据库 )

[root @mysql_5 ~] # rm -rf /usr/local/mysql/data/auto.cnf

# 将主数据库的 data 同步到从数据库中

[root @mysql_5 ~] # rsync -av /usr/local/mysql/data root@192.168.2.58:/usr/local/mysql/

# 修改配置文件（从数据库）

[root @slave_5 ~] # vim /usr/local/mysql/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql .sock

port= 3310

log-error=/usr/local/mysql/data/err .log

relay-log=/usr/local/mysql/data/relaylog

character_set_server=utf8mb4

server -id= 11

# 其他配置

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

# 配置环境变量

[root @slave_5 ~] # sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin' /etc/profile

[root @slave_5 ~] # sed -n '$p' /etc/profile

export PATH=$PATH:/usr/local/mysql/bin

[root @slave_5 ~] # source /etc/profile

# 设置开机自启

[root @slave_5 ~] # chkconfig --add mysql57

[root @slave_5 ~] # chkconfig mysql57 on

[root @slave_5 ~] # service mysql57 start

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

SUCCESS!

# 登录 root 账户

[root @slave_5 ~] # mysql -uroot -pHui@2003

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 ( 28000 ): Access denied for user 'root' @'localhost' ( using password : YES)

# 登录 root 账户（主数据库）

[root @mysql_5 ~] # mysql -proot

mysql: [Warning] Using a password on the command line interface can be insecure.

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

Your MySQL connection id is 2

Server version: 5.7.44 -log MySQL Community Server (GPL)

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> flush tables with read lock ;

Query OK, 0 rows affected ( 0.00 sec)

# 查看 master 状态信息

mysql> show master status ;

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

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

| binlog .000002 | 154 | | | |

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

1 row in set ( 0.00 sec)

# 登录 root 用户（从数据库）

[root @slave_5 ~] # mysql -uroot -proot

mysql: [Warning] Using a password on the command line interface can be insecure.

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

Your MySQL connection id is 6

Server version: 5.7.44 MySQL Community Server (GPL)

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.

# 配置 change master to( 从数据库 )

mysql> change master to

master_host= "192.168.2.57" ,master_user= "slave0" ,master_passworrd= "slave0" ,master_log_file= "binlo

g.000002" ,master_log_pos= 154 ;

Query OK, 0 rows affected, 2 warnings ( 0.01 sec)

# 启动 slave

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: 192.168.2.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> unlock tables ;

Query OK, 0 rows affected ( 0.00 sec)

# 插入数据

mysql> insert into test .user (username, password ) values ( 'xxxxx' , 'xxxxx' );

Query OK, 1 row affected ( 0.03 sec)

# 从数据库

# 数据已经同步

mysql> select * from test .user ;

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

| id | username | password |

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

| 1 | aaa | aaa |

| 2 | bbbb | bbbbb |

| 3 | xxxxx | xxxxx |

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

3 rows in set ( 0.00 sec)

四、配置读写分离（中间件）

读写分离的配置文件：

1. 添加一个新的虚拟主机

设置 ip 为 192.168.2.59 ，主机名为 mycat

2. 上传 jdk 和 mycat 包

[root@mycat ~] # ls

anaconda-ks.cfg Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

jdk-8u192-linux-x64.tar.gz

3. 解压

# 解压

[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 mycat

jdk1.8.0_192 Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

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. 配置环境变量

[root @mycat ~ ] # ls /usr/local/jdk/

bin lib src.zip

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 :/ root / bin :/ usr / local / jdk / bin : 没有那个文

件或目录

5. 查看版本

[root@mycat ~] # javac -version

javac 1 .8.0_192

6. 测试 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 :18 bin

drwxr-xr-x. 2 root root 6 8 月 15 15 :18 catlet

drwxr-xr-x. 4 root root 4096 8 月 15 15 :18 conf

drwxr-xr-x. 2 root root 4096 8 月 15 15 :18 lib

drwxr-xr-x. 2 root root 6 8 月 15 15 :18 logs

-rwxr-xr-x . 1 root root 219 8 月 15 15 :18 version.txt

[root@mycat ~] # ls /usr/local/mycat/bin/

dataMigrate.sh mycat startup_nowrap.sh wrapper-linux-x86-32

init_zk_data.sh rehash.sh wrapper-linux-ppc-64 wrapper-linux-x86-64

# 成功启动

[root@mycat ~] # /usr/local/mycat/bin/mycat console

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 |

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

7. 找到 server.xml 和 scheme.xml 文件

[root@mycat ~] # ls /usr/local/mycat/conf/

server.xml

wrapper.conf

8. 配置 server.xml

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

93 <user name = "zzz" defaultAccount = "true" >

94 <property name = "password" >zzz</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 -- >

9. 配置 schema.xml 文件

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

1 <?xml version = "1.0" ?>

2 <!DOCTYPE mycat:schema SYSTEM "schema.dtd" >

3 <mycat:schema xmlns :mycat = "http://io.mycat/" >

4

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" />

10 <!-- <dataNode name = "dn2" dataHost = "localhost1" database = "db2" />

11 <dataNode name = "dn3" dataHost = "localhost1" database = "db3" /> -- >

12 <!--<dataNode name = "dn4" dataHost = "sequoiadb1" database = "SAMPLE" />

13 <dataNode name = "jdbc_dn1" dataHost = "jdbchost" database = "db1" />

14 <dataNode name = "jdbc_dn2" dataHost = "jdbchost" database = "db2" />

15 <dataNode name = "jdbc_dn3" dataHost = "jdbchost" database = "db3" /> -- >

16 <dataHost name = "localhost1" maxCon = "1000" minCon = "10" balance = "0"

17 writeType = "0" dbType = "mysql" dbDriver = "native" switchType = "1"

slaveThreshold = "100" >

18 <heartbeat>select user()</heartbeat>

19 <!-- can have multi write hosts -- >

20 <writeHost host = "hostM1" url = "192.168.2.57:3306" user = "zzz"

21 password = "zzz" >

22 <!-- can have multi read hosts -- >

23 <readHost host = "hostS2" url = "192.168.2.58:3310" user = "zzz"

password = "zzz" />

24 </writeHost>

25 </dataHost>

10. 启动服务

# 启动服务

[root@mycat ~] # /usr/local/mycat/bin/mycat start

Starting Mycat-server...

# 查看端口（有端口就是启动成功了）

[root@mycat ~] # netstat -lntup | grep 8066

tcp6 0 0 :::8066 :::* LISTEN

# 补充（报错的话查看错误日志）

[root@mycat ~] # vim /usr/local/mycat/logs/wrapper.log .

还有 2 个文件等待编辑

五、测试 mycat

1. 客户端

[root @client bin] # ./mysql -h192.168.2.59 -P8066 -uzzz -pzzz

mysql: [Warning] Using a password on the command line interface can be insecure.

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

Your MySQL connection id is 2

Server version: 5.6.29 -mycat- 1.6.5 - release - 20180122220033 MyCat Server (OpenCloundDB)

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> 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 | bbbbb |

| 3 | xxxxx | xxxxx |

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

3 rows in set ( 0.02 sec)

mysql> show variables like "server_id" ;

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

| Variable_name | Value |

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

| server_id | 10 |

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

1 row in set ( 0.01 sec)

mysql>

2. 远程工具登录

在 Navicat 中登录查看表中的数据