Mysql主从复制与读写分离实战

环境：
主节点：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)

此时可以读取到新插入的数据，读写分离得以实现。