环境:
主节点:192.168.182.202
从节点:192.168.182.203
版本:Mysql5.7
1.下载、编译mysql
root@mysqlslave1 ~# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
#解压文件
root@mysqlslave1 ~# mkdir -p /usr/softwares/
root@mysqlslave1 ~# mv mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz /usr/softwares/
root@mysqlslave1 ~# cd /usr/softwares/
root@mysqlslave1 /u/softwares# tar -xzvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
#移动到/usr/local并改名为mysql
root@mysqlslave1 /u/softwares# mv mysql-5.7.23-linux-glibc2.12-x86_64 /usr/local/mysql
#修改配置文件
root@mysqlslave1 /u/l/mysql# vim /etc/my.cnf
[mysql]
default-character-set=utf8
[mysqld]
default-storage-engine=INNODB
character_set_server=utf8
#复制mysql.server到/etc/init.d/目录下(目的想实现开机自动执行效果)
root@mysqlslave1 /u/l/mysql# cd /usr/local/mysql/support-files/
root@mysqlslave1 /u/l/m/support-files# cp mysql.server /etc/init.d/mysql
#修改/etc/init.d/mysql参数
root@mysqlslave1 /u/l/m/support-files# vim /etc/init.d/mysql
#修改以下内容:
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
创建mysql用户
#建立一个mysql的组
root@mysqlslave1 /u/l/m/support-files# groupadd mysql
#建立mysql用户,并且把用户放到mysql组
root@mysqlslave1 /u/l/m/support-files# useradd -r -g mysql mysql
#为mysql用户设置密码
root@mysqlslave1 /u/l/m/support-files# passwd mysql
#给目录/usr/local/mysql 更改拥有者
root@mysqlslave1 /u/l/m/support-files# chown -R mysql:mysql /usr/local/mysql/
安装初始化mysql
root@mysqlslave1 /u/l/m/support-files# cd /usr/local/mysql/bin/
root@mysqlslave1 /u/l/m/bin# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data
#初始化后会生成一个临时密码 root@localhost::*(最好先记录这个临时密码)
启动mysql
root@mysqlslave1 /u/l/m/bin# ./mysqld_safe --user=mysql &
#检查mysql是否启动
[root@localhost bin]# ps -ef|grep mysql
修改密码
#进入mysql
root@mysqlslave1 /u/l/m/bin# ./mysql -uroot -p
mysql> set password=password('root');
mysql> grant all privileges on *.* to root@'%' identified by 'root';
mysql> flush privileges;
设置开机自启动
#添加mysql服务
root@mysqlslave1 /u/l/m/bin# chkconfig --add mysql
#设置mysql服务为自启动
root@mysqlslave1 /u/l/m/bin# chkconfig mysql on
配置环境变量
#必须在bash下,fish下会报错
root@mysqlslave1 # vim /etc/profile
#在最后一行加入以下内容
export PATH=/usr/local/mysql/bin:$PATH
#使修改生效
root@mysqlslave1 # source /etc/profile
重启Mysql服务
[root@mysqlslave1 ~]# service mysql restart
2.配置主服务器
修改配置文件
root@mysqlslave1 /u/l/m/bin# vim /etc/my.cnf
#在[mysqld]中添加:
server-id=1
log_bin=master-bin
log_bin_index=master-bin.index
binlog_do_db=test
#备注:
#server-id 服务器唯一标识。
#log_bin 启动MySQL二进制日志,即数据同步语句,从数据库会一条一条的执行这些语句。
#binlog_do_db 指定记录二进制日志的数据库,即需要复制的数据库名,如果复制多个数据库,重复设置这个选项即可。
#binlog_ignore_db 指定不记录二进制日志的数据库,即不需要复制的数据库名,如果有多个数据库,重复设置这个选项即可。
#其中需要注意的是,binlog_do_db和binlog_ignore_db为互斥选项,一般只需要一个即可。
创建从服务器的用户和权限
root@mysqlslave1 /u/l/m/bin# mysql -uroot -proot
mysql> grant replication slave on *.* to masterbackup@'192.168.182.%' identified by 'root';
mysql> flush privileges;
重启Mysql服务
root@mysqlslave1 /u/l/m/bin# service mysql restart
查看主服务器状态
root@mysqlslave1 /u/l/m/bin# mysql -uroot -proot
mysql> show master status;
3.slave从服务器的配置
修改配置文件my.cnf
root@mysqlslave2 /u/l/m/bin# vim /etc/my.cnf
#在[mysqld]中添加:
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
#replicate-do-db=test
#备注:
#server-id 服务器唯一标识,如果有多个从服务器,每个服务器的server-id不能重复,跟IP一样是唯一标识,如果你没设置server-id或者设置为0,则从服务器不会连接到主服务器。
#relay-log 启动MySQL二进制日志,可以用来做数据备份和崩溃恢复,或主服务器挂掉了,将此从服务器作为其他从服务器的主服务器。
#replicate-do-db 指定同步的数据库,如果复制多个数据库,重复设置这个选项即可。若在master端不指定binlog-do-db,则在slave端可用replication-do-db来过滤。
#replicate-ignore-db 不需要同步的数据库,如果有多个数据库,重复设置这个选项即可。
#其中需要注意的是,replicate-do-db和replicate-ignore-db为互斥选项,一般只需要一个即可。
重启mysql服务
root@mysqlslave2 /u/l/m/bin# service mysql restart
连接master主服务器
root@mysqlslave2 /u/l/m/bin# mysql -uroot -proot
mysql> change master to master_host='192.168.182.202',master_port=3306,master_user='masterbackup',master_password='root',master_log_file='master-bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.182.202
Master_User: masterbackup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#Slave_IO_Running和Slave_SQL_Running都为yes,则表示同步成功。
4.测试主从复制
在主库创建test库和test表
mysql> create database test;
mysql> use test
mysql> create table test(id varchar(10));
mysql> insert into test.test values('1');
进入从库查看
mysql> select * from test.test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
读写分离
新安装一台RHEL7作为mysql-proxy服务器,IP地址为192.168.182.204,配置好了yum源并关闭了selinux和防火墙
安装mysql-proxy
root@mysql-proxy ~# wget http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
root@mysql-proxy ~# tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
root@mysql-proxy ~# mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy
root@mysql-proxy ~# cd /usr/local/mysql-proxy/
root@mysql-proxy /u/l/mysql-proxy# useradd -r mysql-proxy
useradd:用户“mysql-proxy”已存在
root@mysql-proxy /u/l/mysql-proxy# id mysql-proxy
uid=997(mysql-proxy) gid=995(mysql-proxy) 组=995(mysql-proxy)
root@mysql-proxy /u/l/mysql-proxy# mkdir etc
root@mysql-proxy /u/l/mysql-proxy# vim ~/.bash_profile
修改环境变量
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
使修改生效
root@mysql-proxy /u/l/mysql-proxy# source ~/.bash_profile
Missing end to balance this if statement
~/.bash_profile (line 4): if [ -f ~/.bashrc ]; then
^
from sourcing file ~/.bash_profile
called on standard input
source:读取文件 “/root/.bash_profile” 时发生错误
#这里要切换回bash下进行
root@mysql-proxy /u/l/mysql-proxy# bash
[root@mysql-proxy mysql-proxy]# source ~/.bash_profile
[root@mysql-proxy mysql-proxy]# fish
Welcome to fish, the friendly interactive shell
Type help for instructions on how to use fish
root@mysql-proxy /u/l/mysql-proxy#
修改mysql-proxy配置文件
root@mysql-proxy /u/l/mysql-proxy# vim etc/mysql-proxy.cnf
[mysql-proxy]
user=root
#运行mysql-proxy用户
admin-username=tom
admin-password=tom
#用户的密码
proxy-address=0.0.0.0:3306
proxy-read-only-backend-addresses=192.168.182.203:3306
#指定后端从slave读取数据
proxy-backend-addresses=192.168.182.202:3306#指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
#读写分离配置脚本
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
#管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
#日志位置
log-level=debug
#定义log日志级别
daemon=true
#以守护进程方式运行
keepalive=true
#崩溃时,尝试重启
admin-username=admin
admin-password=admin
#admin用户的密码
plugins=proxy,admin
#添加admin模块
修改 /usr/local/mysql-proxy/etc/mysql-proxy.cnf 权限
root@mysql-proxy /u/l/mysql-proxy# chmod 660 etc/mysql-proxy.cnf
修改读写分离配置脚本
root@mysql-proxy /u/l/mysql-proxy# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, #默认超过4个连接数时,才开始读写分离,改为1
max_idle_connections = 2, #默认8,改为2
is_debug = false
}
end
启动mysql-proxy
root@mysql-proxy /u/l/m/etc# mysql-proxy --defaults-file=/usr/local/mysql-proxy/etc/mysql-proxy.cnf
#没有日志需要建立日志
root@mysql-proxy /u/l/m/etc# touch /usr/local/mysql-proxy/logs/mysql-proxy.log
#查看端口是否开启
root@mysql-proxy /u/l/m/etc# netstat -anptu | grep mysql
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 20649/mysql-proxy
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 20649/mysql-proxy
验证读写分离
在master(192.168.182.201)上对远程用户授权
mysql> grant insert,update,select on *.* to admin@'%' identified by 'admin';
mysql> flush privileges;
使用除master,slave,proxy的服务器连接mysql-proxy,这里使用192.168.182.201作为客户端连接数据库进行读写操作,数据库版本为Mariadb5.5
root@rhel7 ~# mysql -uadmin -padmin -h 192.168.182.204
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
MySQL [(none)]> 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 [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
MySQL [test]> select * from test;
+------+
| id |
+------+
| 1 |
+------+
MySQL [test]> insert into test.test values('2');
Query OK, 1 row affected (0.01 sec)
MySQL [test]> insert into test.test values('3');
Query OK, 1 row affected (0.02 sec)
MySQL [test]> select * from test.test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.01 sec)
在master端(192.168.182.202)查看
mysql> select * from test.test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
停止slave的同步(192.168.182.203),由于从库用于读取数据,假如读写分离生效,停止同步后,新插入的数据是不能从主库读取到的
mysql> stop slave;
在客户端(192.168.182.201)下进行数据的插入和查询
MySQL [test]> insert into test.test values('4');
Query OK, 1 row affected (0.01 sec)
MySQL [test]> insert into test.test values('5');
Query OK, 1 row affected (0.00 sec)
MySQL [test]> select * from test.test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
可见关闭从库同步后无法读取新插入的数据
开启从库同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
再次在客户端(192.168.182.201)下进行数据查询
MySQL [test]> select * from test.test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
此时可以读取到新插入的数据,读写分离得以实现。