MySQL 主从配置

环境

centos6.7 虚拟机两台

主：192.168.23.160

从：192.168.23.163

准备

在两台机器上分别安装mysql5.6.23，安装完成后利用临时密码登录mysql数据修改root的密码；将my.cnf配置文件放至/etc/my.cnf，重启mysql服务进程。

my.cnf 初始内容：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

lower_case_table_names=1
skip_name_resolve
character-set-server=utf8
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

innodb_buffer_pool_size = 2G
long_query_time=2
slow_query_log=0
log_queries_not_using_index=1
thread_concurrency=8
thread_cache_size=32
max_connections=500
max_connect_errors=800

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

配置主库

在主库的my.cnf配置文件的[mysqld]节点下增加

server_id=160

log_bin=mysql-bin

log_bin_trust_function_creators=1

重启mysql服务后登录mysql数据库，执行 how master status; 查看主库的状态

创建同步账号：grant replication slave on . to ‘sync’@’%’ identified by ‘123456’;

配置从库

在从库的my.cnf配置文件的[mysqld]节点下增加

server_id=163

replicate-ignore-table=dbname.table1,dbname.table2 //需要忽略同步的表

重启mysql服务后登录mysql数据库，执行以下命令：

change master to master_host='192.168.23.160', master_port=3306, master_user='sync', master_password='123456', master_log_file='mysql-bin.000004', master_log_pos=425;

master_log_file、master_log_pos为主库通过“how master status;”命令查询出来的结果。

通过show slave status \G;查看从库的连接状态

Slave_IO_Running、Slave_SQL_Running状态都为“Yes”表示成功。

注意：服务器直接要开放3306 端口，或者关闭防火墙！

初次配置可能会有Slave_IO_Running、Slave_SQL_Running状态都为“No”的情况，重启从库可以解决（保证主、从库配置正确、网络连接正常）。

日志配置

随着数据增多，日志文件会越来越多，需要定时清理日志，不然日志文件会占满磁盘。

在my.cnf 添加：expire_logs_days=[days] – 天数，表示在主数据库日志切换的时候会删除指定天数以前的日志；max_binlog_size 可以设置单个二进制文件的大小。

日志文件目录修改（主库）

1、关闭mysql服务

2、将原目录(/var/lib/mysql)下的mysql-bin.*文件移动到新的目录下（/home/mysql-logs），目录要有可读写权限；修改mysql-bin.index文件中的文件目录为绝对路径，如：/home/mysql-logs/mysql-bin.000001。

3、修改配置文件，log_bin=home/mysql-logs/mysql-bin

4、启动mysql服务

读写分离（mysql-proxy）

windows 下配置

[mysql-proxy]
admin-lua-script=D:/tools/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy-backend-addresses=192.168.23.160:3306  -- 主库
proxy-read-only-backend-addresses=192.168.23.163:3306  -- 从库
proxy-lua-script=D:/tools/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=D:/tools/mysql-proxy/log/mysql-proxy.log
log-level=debug
daemon=true
keepalive=true

Linux 下配置

安装pkg-config

1、编译mysql-proxy

**注：**0.8.5 不需要编译，也不需要有安装pkg-config

用mysql-proxy -V查看mysql-proxy的相关信息，这里留意下版本信息，将会在第三步配置lua路径的时候用到；

2、使用vim /etc/mysql-proxy.cnf命令打开mysqk-proxy的配置文件进行编辑：

在mysql-proxy.cnf中输入如下内容：

[mysql-proxy] 
admin-username=user   #admin用户名
admin-password=password    admin密码
admin-lua-script=/usr/lib64/mysql-proxy/lua/admin.lua    #lua位置，参见上面的版本信息
daemon=true   # mysql-proxy以守护进程方式运行
keepalive=true    #保持连接启动进程会有2个， 一号进程用来监视二号进程
proxy-backend-addresses=10.17.6.210   #目标地址，Indb内网地址，默认端口3306，10.17.6.210是目标inDB的ip
log-file=/var/log/mysql-proxy.log    #日志文件存储路径
log-level=debug

编辑完之后通过Esc退出编辑，然后用“:wq”保存mysql-proxy.cnf的编辑。

3、用chmod 0660 /etc/mysql-proxy.cnf命令来改变配置文件的权限，然后用 mysql-proxy --defaults-file=/etc/mysql-proxy.cnf启动mysql-proxy服务：

chmod 0660 /etc/mysql-proxy.cnf
mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

4、用mysql -h$uhost_ip -P4040 -u$User -p$Password测试：

mysql -h106.75.94.37 -P4040 -uroot -p123456

$uhost_ip:UHost的外网IP $User:用户名 $Password：数据库密码

注意：打开防火墙的4040端口，MySQL-Proxy默认端口为4040，通过访问4040端口就可以访问3306端口。