一、配置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 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) # 创建新账户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'") 0 # 给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 executeresult = self._query(query)File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 310, in _queryconn.query(q)File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 548, in queryself._affected_rows = self._read_query_result(unbuffered=unbuffered)File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 775, in _read_query_resultresult.read()File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1156, in readfirst_packet = self.connection._read_packet()File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 725, in _read_packetpacket.raise_for_error()File "/usr/local/lib/python3.6/site-packages/pymysql/protocol.py", line 221, in raise_for_errorerr.raise_mysql_exception(self._data)File "/usr/local/lib/python3.6/site-packages/pymysql/err.py", line 143, in raise_mysql_exceptionraise 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'@'%'") 0
4.在python中进行锁表
# 锁表 >>> cursor.execute("flush table with read lock") 0 # 锁表后,在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") 1 # 将查到的语句进行打印 >>> print(cursor.fetchall()) (('binlog.000001', 154, '', '', ''),) # 补充 # 查看状态信息,查到是空的话是因为没有写/usr/local/mysql/my.cnf配置文件 >>> cursor.execute("show master status") 0 >>> 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) 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> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) # 查看master状态信息 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用户(从数据库) [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) 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. # 配置change master to(从数据库) mysql> change master to master_host="192.168.2.57",master_user="slave0",master_passworrd="slave0",master_log_file="binlog.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 eventMaster_Host: 192.168.2.57Master_User: slave0Master_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 154Relay_Log_File: relaylog.000002Relay_Log_Pos: 317Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_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)
四、配置读写分离(中间件)
读写分离的配置文件:
文件名称 | 作用 |
---|---|
server.xml | 配置mycat的对外的用户、密码、映射数据库名称等信息 |
schema.xml | 配置后端真实有效的用户、密码、真实数据库名称等信息 |
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 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:/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 | 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
7.找到server.xml和scheme.xml文件
[root@mycat ~]# ls /usr/local/mycat/conf/ autopartition-long.txt rule.xml auto-sharding-long.txt schema.xml auto-sharding-rang-mod.txt sequence_conf.properties cacheservice.properties sequence_db_conf.properties dbseq.sql sequence_distributed_conf.properties ehcache.xml sequence_time_conf.properties index_to_charset.properties server.xml log4j2.xml sharding-by-enum.txt migrateTables.properties wrapper.conf myid.properties zkconf partition-hash-int.txt zkdownload partition-range-mod.txt
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 <!--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" />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) 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> 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中登录查看表中的数据: