一、pymysql管理数据库
1、搭建主mysql5.7
[root@mysql57 ~]# ls
anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@mysql57 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@mysql57 ~]# 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@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 ~]# 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 ~]# /usr/local/mysql/bin/mysql -p
mysql> alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'lxe'@'%' identified by 'lxe';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'lxe'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
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.00 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | aaa | aaa |
+----+----------+----------+
1 row in set (0.00 sec)[root@localhost ~]# python3
>>> import pymysql
>>>conn=pymysql.connect(host="192.168.8.176",port=3306,database="test",user="lxe",password="lxe");
>>> cursor=conn.cursor()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)>>> conn=pymysql.connect(host="192.168.8.176",port=3306,database="test",user="root",password="root");
>>> cursor.execute("create user 'slave0'@'%' identified by 'slave0'") #创建用户
>>> cursor=conn.cursor()
>>> cursor.execute("grant replication slave on *.* to 'slave0'@'%'") #给权限
>>> cursor.execute("flush privileges") #刷新
>>> cursor.execute("flush tables with read lock")
0
>>> cursor.execute("unlock tables") #锁表
0
>>> cursor.execute("show master status")
0
>>> print(cursor.fetchall())
()[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
>>> conn=pymysql.connect(host="192.168.8.176",port=3306,database="test",user="lxe",password="lxe");
>>> cursor=conn.cursor()
>>> cursor.execute("show master status")
1
>>> print(cursor.fetchall())
(('binlog.000001', 154, '', '', ''),)
>>> cursor.execute("unlock tables")
02、从服务器
[root@salve57 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@salve57 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
[root@salve57 ~]# ls /usr/local/mysql/
bin include LICENSE README support-files
docs lib man share
[root@salve57 ~]# mkdir /usr/local/mysql/mysql-files
[root@salve57 ~]# useradd -r -s /sbin/nologin mysql
[root@salve57 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@salve57 ~]# chmod 750 /usr/local/mysql/mysql-files/[root@slave57 ~]# rm -rf /etc/my.cnf
[root@slave57 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8[root@salve57 ~]# yum -y install rsync
[root@salve57 ~]# vim /usr/local/mysql/data/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/data/err.log
log-relay=/usr/local/mysql/data/relaylog
character_set_server=utf8mb4
server-id=11[root@salve57 ~]# sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin' /etc/profile
[root@salve57 ~]# sed -n '$p' /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
[root@salve57 ~]# source /etc/profile
[root@salve57 ~]# chkconfig --add mysql57
[root@salve57 ~]# chkconfig mysql57 on[root@salve57 ~]# service mysql57 start
3、主服务器
[root@mysql57 ~]# chmod 750 /usr/local/mysql/mysql-files/
[root@mysql57 ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@mysql57 ~]# yum -y install rsync
[root@mysql57 ~]# rsync -av /usr/local/mysql/data [email protected]:/usr/local/mysql/
二、企业架构MySQL读写分离
三、mycat读写分离
Mycat数据库中间件
国内最活跃的、性能最好的开源對据库中间件!
官方网址:http://www.mycat.io/https://github.com/MyCATApache/MyCAT-Server/
因为mycat是由java语言开发,必须使用java的运行环境才能进行启动和操作
步骤
1、上传jdk和mycat安装包
[root@mycat ~]# ls
jdk-8u192-linux-x64.tar.gz
Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
2、解压并添加到指定的位置
[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/
3、配置jdk文件
[root@mycat ~]# sed -i '$aexport JAVA_HOME=/usr/local/jdk' /etc/profile
[root@mycat ~]# sed -n '$p' /etc/profile
export JAVA_HOME=/usr/local/jdk
[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 ~]# sed -n '$p' /etc/profile
export PATH=$PATH:$JAVA_HOME/bin
[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: 没有那个文件或目录
[root@mycat ~]# javac -version
javac 1.8.0_192
4、测试启动mycat
[root@mycat ~]# /usr/local/mycat/bin/mycat console
5、找到server.xml schema.xml文件并更改
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml
93 <user name="lxe" defaultAccount="true">
94 <property name="password">lxe</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 --> 5 <schema name="test" dataNode="dn1" checkSQLschem a="false" sqlMaxLimit="100">
6 </schema>
7 <dataNode name="dn1" dataHost="localhost1" datab ase="test" />
8 <!-- <dataNode name="dn2" dataHost="localhost1" datab ase="db2" />
9 <dataNode name="dn3" dataHost="localhost1" datab ase="db3" /> -->
14 <dataHost name="localhost1" maxCon="1000" minCon ="10" balance="0"
15 writeType="0" dbType="mysql" d bDriver="native" switchType="1" slaveThreshold="100">
16 <heartbeat>select user()</heartbeat>
17 <!-- can have multi write hosts -->
18 <writeHost host="hostM1" url="192.168.8. 176:3306" user="lxe"
19 password="lxe">
20 <!-- can have multi read hosts - ->
21 <readHost host="hostS2" url="192 .168.8.177:3306" user="lxe" password="lxe" />
22 </writeHost>
23 <!-- <writeHost host="hostS1" url="localhost: 3316" user="root"
24 password="123456" />-
->6、启动mycat并测试
[root@mycat ~]# /usr/local/mycat/bin/mycat start
[root@mycat ~]# netstat -lnput | grep 8066[root@client ~]# cd mysql-5.7.44-linux-glibc2.12-x86_64/
[root@client mysql-5.7.44-linux-glibc2.12-x86_64]# cd bin/
[root@client bin]# ./mysql -h192.168.8.178 -P8066 -ulxe -plxe
mysql> show databases;
+--------------------+
| Database |
+--------------------+ |
| test |
+--------------------+
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 |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 10 |
+---------------+-------+
1 row in set (0.02 sec)