mysql5安装及主从同步及用mysql-proxy实现读写分离配置

一、mysql-5.5.28编译安装

1.安装依赖

yum install gcc gcc-c++ make cmake ncurses-devel bison libaio-devel openssl-devel -y

2.源码编译安装

解压tar包：tar zxvf mysql-5.5.28.tar.gz

进入目录：cd mysql-5.5.28

编译安装：

cmake \

-DCMAKE_INSTALL_PREFIX=/data/mysql \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all \

-DSYSCONFDIR=/etc \

-DMYSQL_DATADIR=/data/mysqldata/ \

-DMYSQL_UNIX_ADDR=/data/mysqldata/mysql.sock \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_SSL=system \

-DMYSQL_TCP_PORT=30862 \

-DENABLE_DOWNLOADS=1 \

-DWITH_SSL=system \

-DDOWNLOAD_BOOST=1 \

-DWITH_BOOST=/data/mysql

make

make install

useradd -s /sbin/nologin mysql

chown -R mysql:mysql /data/mysql /data/mysqldata

3.安装后的其他调整

(1)建立配置文件

在mysql的源码目录中的support-files文件夹下，提供了适合不同负载数据库的样本配置文件。一般选择my-medium.conf文件即可。

cp support-files/my-medium.cnf /etc/my.cnf

(2) 初始化数据库

为了正常使用MySQL数据库系统，应以运行用户mysql的身份执行初始化脚本mysql_install_db,指定数据存放目录等。

/usr/local/mysql/scripts/mysql_install_db \

--user=mysql \

--basedir=/data/mysql \

--datadir=/data/mysqldata

(3) 设置环境变量

为了方便在任何目录下使用mysql命令，需要在/etc/profile设置环境变量。

echo "PATH=$PATH:/data/mysql/bin/" >> /etc/profile

. /etc/profile    //立即生效

4.添加系统服务

如果希望添加mysqld系统服务，以便通过chkconfig进行管理，可以直接使用源码包中提供的服务脚本。

(1)找到support-files文件夹下的mysql.server脚本文件，将其复制到/etc/init.d目录下，改名为mysqld。

cp support-files/mysql.server /etc/init.d/mysqld

(2)设置执行权限，通过执行chkconfig命令将其添加为mysqld系统服务。

chmod +x /etc/init.d/mysqld    //设置执行权限

chkconfig --add mysqld          //添加mysqld服务

chkconfig --level 35 mysqld on

(3)使用service工具启动mysqld服务，并查看其运行状态。

[root@RedHat6-1 ~]# service mysqld start

Starting MySQL..                                          [确定]

[root@RedHat6-1 ~]# netstat -antp | grep mysqld

tcp    0  0 0.0.0.0:3306        0.0.0.0:*         LISTEN   2586/mysqld

5访问MySQL数据库

1. 登录到MySQL服务器

经过安装后的初始化过程，MySQL数据库的默认管理员用户名为“root”，密码为空。若要以未设置密码的root用户登录本机的MySQL数据库，可以执行以下命令设置root密码：

[root@RedHat6-1 ~]#/data/mysql/bin/mysqladmin -u root password '123456'

[root@RedHat6-1 ~]# mysql -u root -p

Enter password:    //根据提示输入正确的密码

show databases;

use database;

create table test1 values(id int);

show tables;

insert into test1 value(1);

select id from test1;

二、mysql主从配置

主库master ip:192.168.232.3     从库slave ip: 192.168.232.4

1、主库master在执行SQL语句之后，会把相关的SQL语句记录到binlog文件中。

2、从库slave连接主库master，并从主库master获取该binlog文件，存于本地relay-log文件中，然后从master.info文件读取上次同步时的pos位置节点起执行SQL语句。

由以上信息可知，mysql主从同步复制实验，需要以下几个步骤：

1、 配置主库master同步复制时的选项

2、 在主库master上创建同步复制时的用户并授权

3、 用不锁表方法导出ljk数据库

4、 配置slave端同步复制时所需要的选项

5、 在从库slave上创建数据库ljk并导入备份

6、 设置从库slave与主库master同步

7、 在从库slave上开启同步

8、 查看从库slave的relay-log以及master.info

9、 测试主从同步

1配置主库master同步复制时的选项

mysql的主从配置，只需要修改my.cnf文件即可。如下：

cat /etc/my.cnf |grep -v ^#|grep -v ^$

log-bin=mysql-bin

server-id=1

binlog-do-db=ljk

binlog-ignore-db=mysql

修改完成后重启mysql：service mysqld restart

2在主库master上创建同步复制时的用户并授权

登录master端，创建数据库用户ljk，并授权为replication slave权限。如下：

grant replication slave on *.* to ‘ljk’@’%’identified by ‘123456’;

select user,repl_slave_priv from user where user=’ljk’;

目前数据库用户ljk被授予replication slave权限，在user表中的表现为repl_slave_priv字段为Y。

注意：replication slave权限：只有拥有此权限的用户才可以查看从服务器slave以及从主服务器master读取二进制日志的权限。

授权完毕后，需要在slave测试ljk用户是否可以连接master。如下：

ifconfig ens33|grep “inet”|awk ‘{print $2}’|cut -d: -f2

mysql -h 192.168.232.3 -uljk -p123456

目前在从服务器slave已经可以正常连接master服务器。

3导出ljk数据库

在从库slave与主库master第一次同步数据时，有三种方法。

第一种方法就是在主库锁表后，使用tar把master库直接打包，然后使用scp或者rsync把该打包文件弄到从库slave上。这种情况一般适用于网站或者业务在初始化，或在数据库大于100G时建议使用。

第二种方法就是直接使用mysqldump命令导出数据库，然后在从库上进行恢复。

第三种方法，只需开启同步复制即可。但是这个有一个前提就是，mysql的binlog必须齐全，这个齐全就是要包括该数据创建时的binlog也要存在。并且同步时，还必须要从最初始的binlog开始。所以，这种方法，一般不使用。

通过第二种方法来导出ljk数据库，如下：

mysqldump -uroot --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 ljk>ljk.sql

--skip-lock-tables 不锁表

--single-transaction 通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照

--master-data=2 在导出的时候同时生成binlog文件名和位置在导出的文件开头。这个很重要。binlog的文件和位置可以从这里拿到。

把第6步的binlog文件和位置设置成导出的文件开头找到的那个带有注释的file和position就可以了(cat ljk.sql : CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;)

把ljk数据库的备份文件ljk.sql，通过scp命令复制到从库slave上，如下：

scp ljk.sql root@192.168.232.4:/root/

4配置从库slave同步复制时所需要的选项

从库slave上my.cnf文件中，修改server-id值为唯一即可。如下：

server-id   = 2

replicate-do-db=数据库名
replicate-ignore-db=数据库名

修改完成后重启mysql：service mysqld restart

5在从库slave上创建数据库ljk并导入备份

在从库slave上，然后把备份的数据导入进去。如下：

create database ljk;

mysql –uroot --default-character-set=utf8 ljk</root/ljk.sql

目前从库已经完全恢复ljk数据库。

6设置从库slave与主库master同步

设置从库slave与主库master同步，使用如下命令：

change master to master_host=’192.168.232.3’,master_user=’ljk’,master_password=’123456’,master_log_file=’mysql-bin.000007’,master_log_pos=107, master_connect_retry=60;

其中：master_host表示是主库的IP

master_user表示主库master上允许同步的用户

maser_password表示同步用户的密码

master_log_file表示从哪个binlog文件开始同步

master_log_pos表示从该binlog文件的那个pos节点位置开始同步

master_connect_retry表示从库slave与主库master同步周期，mysql5.5默认是3600s

master_connect_retry 重连时间间隔

change master to  master_connect_retry=60;

master_retry_count 最大重试次数

change master to  master_retry_count=1000;

master_heartbeat_period

change master to  master_heartbeat_period=30;

slave_net_timeout 从机重连超时时间

set global slave_net_timeout=60;

注意：master_log_file和master_log_pos，就是第3步导出ljk库时记录在ljk.sql中的binlog文件名和pos位置节点。

7在从库slave上开启同步

在从库上开启从同步，如下：

start slave;

以上就开启了mysql主从同步复制的开关。使用如下命令检查，同步是否正常。如下：

show slave status;

查看同步是否主要是查看Slave_IO_Running与Slave_SQL_Running选项。如果正常同步，这两选必须同时为YES。

如果Slave_IO_Running为NO，可能是从库与主库的网络不通或3306端口未开。

如果Slave_SQL_Running为NO，可能是从库与主库的数据不一致，可设置模式为冥等模式。

几条主从常用命令：

SET GLOBAL slave_exec_mode = 'IDEMPOTENT';#冥等模式

SET GLOBAL slave_exec_mode = 'STRICT';#严格模式

SHOW SLAVE STATUS;#显示从状态

SHOW MASTER STATUS;#显示主状态

SHOW VARIABLES LIKE 'slave_exec_mode';#显示同步模式

SHOW VARIABLES LIKE 'binlog_format';#显示binlog模式

8查看从库slave的relay-log以及master.info

首先看relay-log信息，如下：

mysqlbinlog k8snode1-relay-bin.000005|more

可以在relay-log日志中看到，从库slave开始同步主库的binlog文件名以及同步复制时的pos位置节点。

查看master.info，如下：

cat master.info|more

可以看到这个文件保存了从库slave同步主库master时的相关信息：IP、用户、密码、binlog文件名、pos位置节点、同步周期。

9测试主从同步

现在我们来测试下，mysql的主从同步。先在主库master上给ljk数据库插入和删除一条数据。如下：

insert into test1 value(5);

delete from test1 where id=2;

select id from test1;

此时主库master的test1数据库中只有1、3、4、5，这个4条数据，

登录从库slave，查看下ljk数据库的情况。

可以看到从库slave中的ljk库中数据和master中的数据一致。

查看从库master.info文件的信息，如下：（信息一致）

cat master.info|more

主库：mysqlbinlog /data/mysql/mysql-bin.000008

三、mysql-proxy

1安装mysql-proxy–实现读写分离是由lua脚本实现的，现在mysql-proxy里面已经集成，无需再安装

tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

  mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

2配置mysql-proxy，创建主配置文件

cd /usr/local/mysql-proxy

mkdir lua #创建脚本存放目录

mkdir logs #创建日志目录

cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件

cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本

vim /etc/mysql-proxy.cnf   #创建配置文件

[mysql-proxy]

user=root #运行mysql-proxy用户

admin-username=proxy #主从mysql共有的用户

admin-password=selleck@123 #用户的密码

proxy-address=192.168.232.5:40862 #mysql-proxy运行ip和端口，不加端口，默认4040

proxy-read-only-backend-addresses=192.168.232.4:3307 #指定后端从slave读取数据

proxy-backend-addresses=192.168.232.3:3307 #指定后端主master写入数据

proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置

admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本

log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置

log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)

daemon=true    #以守护进程方式运行

keepalive=true #mysql-proxy崩溃时，尝试重启

保存退出！

chmod 660 /etc/mysql-proxy.cnf

主从机上添加proxy用户：

GRANT select,create,alter,drop,insert,update,delete ON ljk.test1 TO ‘proxy’@‘localhost’ IDENTIFIED BY ‘QEVWk$p8FGX7Ae*OT%qh@c$glX’;

flush privileges;

3修改读写分离配置文件

vim /usr/local/mysql-proxy/lua/rw-splitting.lua

if not proxy.global.config.rwsplit then

 proxy.global.config.rwsplit = {

  min_idle_connections = 1, #默认超过4个连接数时，才开始读写分离，改为1

  max_idle_connections = 1, #默认8，改为1

  is_debug = false

 }

end

4启动mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

netstat -tupln | grep 40862 #已经启动

tcp 0 0 192.168.0.204:40862 0.0.0.0:* LISTEN 1264/mysql-proxy

关闭mysql-proxy使用：killall -9 mysql-proxy

killall命令未找到：yum install psmisc -y

5测试读写分离

在master192.168.232.3通过代理登陆mysql：

mysql -uproxy -h192.168.232.5 -P40862 -p123456

报错：ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.232.5' (111)

需开放40862端口：

systemctl start firewalld

firewall-cmd --zone=public --add-port=40862/tcp --permanent

firewall-cmd --reload

systemctl stop firewalld

在slave192.168.232.4上root登录mysql停止slave：

mysql -uroot -p123456

stop slave

在master上通过代理登陆mysql：

mysql -uproxy -h192.168.232.5 -P40862 -p123456

use ljk

delete from test1 where id=1;

select * from test1; 可以看到仍然可以查询到1（因为是从slave上读取的数据）

exit

在master上用root用户登陆mysql：

mysql -uroot -p123456

use ljk

select * from test1;查询不到1，说明1已经成功删除，读写分离成功。

在slave192.168.232.4上root登录mysql开启slave：

mysql -uroot -p123456

start slave; #开启主从同步

show slave status; #查看状态信息

exit

在master192.168.232.3上通过代理登陆mysql：
    mysql -uproxy -h192.168.232.5 -P40862 -p123456

在slave192.168.232.4上通过代理登陆mysql：
    mysql -uproxy -h192.168.232.5 -P40862 -p123456

新增一条数据测试是否同步成功：

insert into test1 value(1);

select * from test1; 可以看到都查询到了1

exit

    主从同步成功