mysql读写分离
jerry2 CTO
  2020-11-11 15:27:45 2020-11-11 15:27    1.4k 字  6 分钟  

安装环境

1
2台及以上服务器：CantOS 7

安装MySql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 卸载mysql
rpm -qa|grep mysql #若有可用rpm -e卸载
# 查找mysql残留包，有则删除 rm -rf ，没有则忽略
find / -name mysql
# 安装相关依赖
yum -y install make gcc-c++ cmake bison-devel ncurses-devel numactl libaio
# 创建对应mysql用户
groupadd mysql useradd -s /sbin/nologin -g mysql -M mysql
# 下载二进制包并解压
cd /usr/local/ 
# wget下载或者本地下载后上传-下载速度慢的话可以用国内的镜像地址下载 
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
# 解压安装包 
tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
# 解压后为了方便后面操作可把解压后文件名修改为mysql 
mv mysql-5.7.23-linux-glibc2.12-x86_64 mysql 
# 更改文件夹所属 
chown -R mysql.mysql /usr/local/mysql
# 创建mysql存储文件
mkdir -p /data/mysql/{data,logs,tmp}
# 更改文件夹所属
chown -R mysql.mysql /data/mysql/
# 创建配置文件
vi /etc/my.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 简单模板如下：
[client]
port            = 3306
socket          = /data/mysql/tmp/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql        
datadir = /data/mysql/data  
port = 3306               

socket = /data/mysql/tmp/mysql.sock
pid-file  = /data/mysql/tmp/mysqld.pid
tmpdir = /data/mysql/tmp    
skip_name_resolve = 1
symbolic-links=0
max_connections = 2000
group_concat_max_len = 1024000
sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names = 1
log_timestamps=SYSTEM
character-set-server = utf8
interactive_timeout = 1800  
wait_timeout = 1800
max_allowed_packet = 32M
binlog_cache_size = 4M
sort_buffer_size = 2M
read_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 96M
max_heap_table_size = 96M
max_length_for_sort_data = 8096

#logs
server-id = 1003306
log-error = /data/mysql/logs/error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 3
log-bin = /data/mysql/logs/binlog
binlog_format = row
expire_logs_days = 15
log_bin_trust_function_creators = 1
relay-log = /data/mysql/logs/relay-bin
relay-log-recovery = 1  
relay_log_purge = 1  

#innodb  
innodb_file_per_table = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 0
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode = 2
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_buffer_pool_size = 2G
1
2
3
4
# 配置mysql.server
cd /usr/local/mysql/support-files
cp mysql.server /etc/init.d/mysql
vi /etc/init.d/mysql
1
2
3
# 修改目录位置
basedir=/usr/local/mysql
datadir=/data/mysql/data
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 注册开机启动服务(可忽略)
chkconfig --add mysql
chkconfig --list
# 添加环境变量
echo "PATH=$PATH:/usr/local/mysql/bin  " >> /etc/profile  
source /etc/profile
# 初始化mysql服务
/usr/local/mysql/bin/mysqld  --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
# 临时密码保存在errlog中 
# 获取临时密码
more /data/mysql/logs/error.log |grep password
# 启动mysql服务
service mysql start
# 使用初始密码登录mysql服务 并修改密码
mysql -uroot -p
1
2
3
alter user '<db-user>'@'localhost' identified by '<db-password>';
flush privileges;
quit；#退出
1
2
#重新登录
mysql -uroot -p
1
2
3
4
5
6
7
8
9
10
#查看数据库
show databases;
#进入mysql数据库修改配置
use mysql;
#查看用户表
select host,user from user;
#更新用户访问权限为远程访问
update user set host='%' where host='localhost’;
#刷新配置
flush privileges;

配置主从复制

完成上述安装,保证mysql数据库可访问

配置主库

1
2
# 修改主库配置
vi /etc/my.cnf
1
2
3
4
5
6
7
#增加server-id 服务号保证不重复
server-id=132
log_bin
#需要同步的数据库
binlog-do-db=zgys
#忽略数据库（不需要同步的数据库）
binlog-ignore-db=mysql
1
2
3
#重启mysql服务，登陆查看主机状态
service mysql restart
mysql -uroot -p 
1
2
3
4
#增加从库权限
grant replication slave on *.* to '<replica-user>'@'<replica-host>' identified by '<replica-password>';
show master status;
#记主file 和 Position 列的值

配置从库

1
2
3
4
#删除auto.cnf
rm -f  /data/mysql/auto.cnf
# 修改主库配置
vi /etc/my.cnf
1
2
#增加server-id
server-id=133
1
2
3
#重启mysql服务，登陆查看主机状态
service mysql restart
mysql -uroot -p 
1
2
3
4
5
6
7
8
9
10
11
#关闭从机状态
stop slave;
# reset slave;
#设置和主机建立连接
change master to master_host='主库ip',master_user='主机用户名',master_password='主机密码',master_log_file='主库file',master_log_pos=主库Position;
#启动从机状态
start slave
#查看从机状态
show slave status \G;
# Slave_IO_Running及Slave_SQL_Running进程必须正常运行，即Yes状态，否则说明同步失败,
# 若失败查看mysql错误日志中具体报错详情来进行问题定位

**test:**在主节点创建一个zgys的数据库，然后在该数据库加一张表，添加一条测试数据

读写分离

安装mycat

1
2
3
4
5
6
7
wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
#解压
tar -zxvf 1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
#移动mycat到 /usr/local/MyCat/下
mv mycat /usr/local/MyCat/
#修改mycat配置文件schema.xml文件和server.xml
vim /usr/local/MyCat/mycat/conf/server.xml

server.xml

1
2
3
4
5
<!-- 配置客户端连接Mycat的用户名、密码、逻辑数据库 -->
<user name="<mycat-user>" defaultAccount="true">
    <property name="password"><mycat-password></property>
    <property name="schemas”>zgys</property>
</user>

schema.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/“>

    <!-- 逻辑数据库配置 -->
    <schema name="zgys" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn"></schema>
    <!-- 配置存储节点 -->
    <dataNode name="dn" dataHost="node" database="zgys" />
    <!-- 配置数据库主机 -->
    <dataHost name="node" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <!--心跳检测 -->
        <heartbeat>select user()</heartbeat>
        <!-- 配置读、写数据库 -->
        <writeHost host="<write-host>" url="<write-host>:3306" user="<db-user>"
                   password="<db-password>">
            <readHost host="<read-host>" url="<read-host>:3306" user="<db-user>" password="<db-password>" />
        </writeHost>
        <!-- 写数据库宕机后的备份数据库 -->
        <writeHost host="<backup-host>" url="<backup-host>:3306" user="<db-user>"
                   password="<db-password>" />
    </dataHost>

</mycat:schema>
1
2
#启动mycat
mycat start
 评论