大项目都是这么玩mysql主从读写分离

最新推荐文章于 2023-11-28 14:01:52 发布

Pang文

最新推荐文章于 2023-11-28 14:01:52 发布

阅读量540

点赞数

分类专栏： # 数据库—mysql # 数据库—oracle # 架构设计模式——集群负载均衡文章标签：数据库 mysql redis mongodb sql

本文链接：https://blog.csdn.net/pangzhaowen/article/details/105124027

版权

数据库—mysql 同时被 3 个专栏收录

32 篇文章 1 订阅

订阅专栏

数据库—oracle

6 篇文章 0 订阅

订阅专栏

架构设计模式——集群负载均衡

6 篇文章 0 订阅

订阅专栏

为什么MySQL要做主从复制（读写分离）？

通俗来讲，如果对数据库的读和写都在同一个数据库服务器中操作，业务系统性能会降低。
为了提升业务系统性能，优化用户体验，可以通过做主从复制（读写分离）来减轻主数据库的负载。
而且如果主数据库宕机，可快速将业务系统切换到从数据库上，可避免数据丢失。

MySQL主从复制（读写分离）和集群的区别：

我对MySQL也是刚开始研究，不是很专业。我的理解是：

主从复制（读写分离）：

一般需要两台及以上数据库服务器即可（一台用于写入数据，一台用于同步主的数据并用于数据查询操作）。
局限性：

配置好主从复制之后，同一张表，只能对一个服务器写操作。如果在从上执行了写操作，而之后主也操作了这张表，或导致主从不同步；据说可以配置成主主方式，但我还没有研究到。

主数据库服务器宕机，需要手动将业务系统切换到从数据库服务器。无法做到高可用性（除非再通过部署keepalive做成高可用方案）。

集群是由N台数据库服务器组成，数据的写入和查询是随机到任意一台数据库服务器的，其他数据库服务器会自动同步数据库的操作。

任何一台数据库宕机，不会对整个集群造成大的影响。

局限性

我经过测试才知道目前mysql集群版本（MySQL Cluster）只能对NDB存储引擎的数据进行集群同步，如果是INNODB或其他的MySQL存储引擎是不行的。这个也导致了我放弃了在业务系统中应用这种方案。

回归正题

MySQL5.6开始主从复制有两种方式：基于日志（binlog）；基于GTID（全局事务标示符）。
需要注意的是：GTID方式不支持临时表！所以如果你的业务系统要用到临时表的话就不要考虑这种方式了，至少目前最新版本MySQL5.6.12的GTID复制还是不支持临时表的。
所以此篇教程主要是告诉大家如何通过日志（binlog）方式做主从复制！
Master :192.168.15.141
Slave:192.168.15.142
如何安装mysql我就不细说了，请看linux下安装mysql

配置文件

master
vi /etc/my.cnf
[mysqld]
server-id = 1
log-bin innodb_flush_log_at_trx_commit=1
sync_binlog=1
datadir=/var/lib/mysql
character-set-server=utf8
init_connect='SET NAMES utf8'
server-id=2
datadir=/var/lib/mysql
character-set-server=utf8
init_connect='SET NAMES utf8'
[root@localhost ~]# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO '<root>'@'<192.168.15.142>' IDENTIFIED BY '<123456>';
Query OK, 0 rows affected (0.10 sec)
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -P3306< test.sql
Enter password:
You have mail in /var/spool/mail/root
mysql> CHANGE MASTER TO MASTER_HOST='192.168.15.141',MASTER_USER='root',MASTER_PASSWORD='new-password',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=3931
mysql> START slave;
Query OK, 0 rows affected (0.74 sec)
MySQL主从复制与读写分离

MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践

Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。

因此，一般来说都是通过主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力这样的方案来进行部署与实施的。

如下图所示：

下面是我在实际工作过程中所整理的笔记，在此分享出来，以供大家参考。

一、MySQL的安装与配置
具体的安装过程，建议参考我的这一篇文章：http://heylinux.com/archives/993.html
值得一提的是，我的安装过程都是源码包编译安装的，并且所有的配置与数据等都统一规划到了/opt/mysql目录中，因此在一台服务器上安装完成以后，可以将整个mysql目录打包，然后传到其它服务器上解包，便可立即使用。

二、MySQL主从复制
场景描述：
主数据库服务器：192.168.10.130，MySQL已经安装，并且无应用数据。
从数据库服务器：192.168.10.131，MySQL已经安装，并且无应用数据。

2.1 主服务器上进行的操作
启动mysql服务
/opt/mysql/init.d/mysql start

通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p'new-password'

授权给从数据库服务器192.168.10.131
mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.10.131' identified by ‘password’;

记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到。

2.2 配置从服务器
修改从服务器的配置文件/opt/mysql/etc/my.cnf
将 server-id = 1修改为 server-id = 10，并确保这个ID没有被别的MySQL服务所使用。

启动mysql服务
/opt/mysql/init.d/mysql start

通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p'new-password'

执行同步SQL语句
mysql> change master to
master_host=’192.168.10.130’,
master_user=’rep1’,
master_password=’password’,
master_log_file=’mysql-bin.000005’,
master_log_pos=261;

正确执行后启动Slave同步进程
mysql> start slave;

主从同步检查
mysql> show slave status\G
==============================================
**************** 1. row *******************
Slave_IO_State:
Master_Host: 192.168.10.130
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 415
Relay_Log_File: localhost-relay-bin.000008
Relay_Log_Pos: 561
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: YES
Slave_SQL_Running: YES
Replicate_Do_DB:
……………省略若干……………
Master_Server_Id: 1
1 row in set (0.01 sec)
==============================================

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。

如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：
(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> FLUSH TABLES WITH READ LOCK;

(2)查看主数据库状态
mysql> show master status;

(3)记录下 FILE 及 Position 的值。
将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。

(4)取消主数据库锁定
mysql> UNLOCK TABLES;

2.3 验证主从复制效果

主服务器上的操作
在主服务器上创建数据库first_db
mysql> create database first_db;
Query Ok, 1 row affected (0.01 sec)

在主服务器上创建表first_tb
mysql> create table first_tb(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)

在主服务器上的表first_tb中插入记录
mysql> insert into first_tb values (001,’myself’);
Query Ok, 1 row affected (0.00 sec)

mysql> use first_db
Database chaged

mysql> show tables;
=============================
+--------------------+
| Tables_in_first_db |
+--------------------+
| first_tb |
+--------------------+
1 row in set (0.02 sec)
=============================
数据库表first_tb也已经自动创建

mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
1 rows in set (0.00 sec)
=============================
记录也已经存在

由此，整个MySQL主从复制的过程就完成了，接下来，我们进行MySQL读写分离的安装与配置。

三、MySQL读写分离
场景描述：
数据库Master主服务器：192.168.10.130
数据库Slave从服务器：192.168.10.131
MySQL-Proxy调度服务器：192.168.10.132

以下操作，均是在192.168.10.132即MySQL-Proxy调度服务器上进行的。

3.1 MySQL的安装与配置
具体的安装过程与上文相同。

3.2 检查系统所需软件包
通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。
gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*
libevent* glib*

若缺少相关的软件包，可通过yum -y install方式在线安装，或直接从系统安装光盘中找到并通过rpm -ivh方式安装。

3.3 编译安装lua
MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。

lua可通过以下方式获得
从http://www.lua.org/download.html下载源码包

从rpm.pbone.net搜索相关的rpm包
download.fedora.redhat.com/pub/fedora/epel/5/i386/lua-5.1.4-4.el5.i386.rpm
download.fedora.redhat.com/pub/fedora/epel/5/x86_64/lua-5.1.4-4.el5.x86_64.rpm

这里我们建议采用源码包进行安装
cd /opt/install
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar zvfx lua-5.1.4.tar.gz
cd lua-5.1.4

vi src/Makefile
在 CFLAGS= -O2 -Wall (MYCFLAGS)这一行记录里加上−fPIC，更改为CFLAGS=−O2−Wall−fPIC(MYCFLAGS)这一行记录里加上−fPIC，更改为CFLAGS=−O2−Wall−fPIC(MYCFLAGS) 来避免编译过程中出现错误。

make linux
make install

cp etc/lua.pc /usr/lib/pkgconfig/
export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig

3.4 安装配置MySQL-Proxy
MySQL-Proxy可通过以下网址获得：
http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/

推荐采用已经编译好的二进制版本，因为采用源码包进行编译时，最新版的MySQL-Proxy对automake，glib以及libevent的版本都有很高的要求，而这些软件包都是系统的基础套件，不建议强行进行更新。
并且这些已经编译好的二进制版本在解压后都在统一的目录内，因此建议选择以下版本：
32位RHEL5平台：
http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
64位RHEL5平台：
http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-64bit.tar.gz

测试平台为RHEL5 32位，因此选择32位的软件包
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz

tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
mv mysql-proxy-0.8.1-linux-rhel5-x86-32bit /opt/mysql-proxy

创建mysql-proxy服务管理脚本
mkdir /opt/mysql-proxy/init.d/

vim mysql-proxy

#!/bin/sh

03	# mysql-proxy This script starts and stops the mysql-proxy daemon

05	# chkconfig: - 78 30

06	# processname: mysql-proxy

07	# description: mysql-proxy is a proxy daemon to mysql

09	# Source function library.

10	. /etc/rc.d/init.d/functions

12	#PROXY_PATH=/usr/local/bin

13	PROXY_PATH=/opt/mysql-proxy/bin

15	prog="mysql-proxy"

17	# Source networking configuration.

18	. /etc/sysconfig/network

20	# Check that networking is up.

21	[ ${NETWORKING} = "no" ] && exit 0

23	# Set default mysql-proxy configuration.

24	#PROXY_OPTIONS="--daemon"

25	PROXY_OPTIONS="--admin-username=root --admin-password=password --proxy-read-only-backend-addresses=192.168.10.131:3306 --proxy-backend-addresses=192.168.10.130:3306 --admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua"

26	PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid

28	# Source mysql-proxy configuration.

29	if [ -f /etc/sysconfig/mysql-proxy ]; then

30	. /etc/sysconfig/mysql-proxy

33	PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH

35	# By default it's all good

RETVAL=0

38	# See how we were called.

39	case "$1" in

start)

41	# Start daemon.

42	echo -n $"Starting $prog: "

43	NICELEVELNICELEVELPROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql --log-level=warning --log-file=/opt/mysql-proxy/log/mysql-proxy.log

RETVAL=$?

echo

46	if [ $RETVAL = 0 ]; then

47	touch /var/lock/subsys/mysql-proxy

;;

stop)

51	# Stop daemons.

52	echo -n $"Stopping $prog: "

53	killproc $prog

RETVAL=$?

echo

56	if [ $RETVAL = 0 ]; then

57	rm -f /var/lock/subsys/mysql-proxy

58	rm -f $PROXY_PID

;;

restart)

$0 stop

sleep 3

$0 start

;;

66	condrestart)

67	[ -e /var/lock/subsys/mysql-proxy ] && $0 restart

;;

status)

70	status mysql-proxy

RETVAL=$?

;;

74	echo "Usage: $0 {start\|stop\|restart\|status\|condrestart}"

RETVAL=1

;;

esac

79	exit $RETVAL

脚本参数详解：
==============================================
PROXY_PATH=/opt/mysql-proxy/bin //定义mysql-proxy服务二进制文件路径

PROXY_OPTIONS="--admin-username=root \ //定义内部管理服务器账号
--admin-password=password \ //定义内部管理服务器密码
--proxy-read-only-backend-addresses=192.168.10.131:3306 \ //定义后端只读从服务器地址
--proxy-backend-addresses=192.168.10.130:3306 \ //定义后端主服务器地址
--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua \ //定义lua管理脚本路径
--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua" \ //定义lua读写分离脚本路径

PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid //定义mysql-proxy PID文件路径

NICELEVELNICELEVELPROXY_PATH/mysql-proxy PROXYOPTIONS −−daemon //定义以守护进程模式启动−−keepalive //使进程在异常关闭后能够自动恢复−−pid−file=PROXYOPTIONS −−daemon //定义以守护进程模式启动−−keepalive //使进程在异常关闭后能够自动恢复−−pid−file=PROXY_PID \ //定义mysql-proxy PID文件路径
--user=mysql \ //以mysql用户身份启动服务
--log-level=warning \ //定义log日志级别，由高到低分别有(error|warning|info|message|debug)
--log-file=/opt/mysql-proxy/log/mysql-proxy.log //定义log日志文件路径
==============================================

cp mysql-proxy /opt/mysql-proxy/init.d/
chmod +x /opt/mysql-proxy/init.d/mysql-proxy

mkdir /opt/mysql-proxy/run
mkdir /opt/mysql-proxy/log

mkdir /opt/mysql-proxy/scripts

配置并使用rw-splitting.lua读写分离脚本
最新的脚本我们可以从最新的mysql-proxy源码包中获取
cd /opt/install

wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz

tar xzvf mysql-proxy-0.8.1.tar.gz
cd mysql-proxy-0.8.1
cp lib/rw-splitting.lua /opt/mysql-proxy/scripts

修改读写分离脚本rw-splitting.lua
修改默认连接，进行快速测试，不修改的话要达到连接数为4时才启用读写分离
vim /opt/mysql-proxy/scripts/rw-splitting.lua
=============================
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, //默认为4
max_idle_connections = 1, //默认为8
is_debug = false
}
end
=============================

修改完成后，启动mysql-proxy
/opt/mysql-proxy/init.d/mysql-proxy start

3.5 测试读写分离效果
创建用于读写分离的数据库连接用户
登陆主数据库服务器192.168.10.130，通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p'new-password'
mysql> GRANT ALL ON *.* TO 'proxy1'@'192.168.10.132' IDENTIFIED BY 'password';
由于我们配置了主从复制功能，因此从数据库服务器192.168.10.131上已经同步了此操作。

为了清晰的看到读写分离的效果，需要暂时关闭MySQL主从复制功能
登陆从数据库服务器192.168.10.131，通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p'new-password'

关闭Slave同步进程
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

连接MySQL-Proxy
/opt/mysql/bin/mysql -uproxy1 -p'password' -P4040 -h192.168.10.132

登陆成功后，在first_db数据的first_tb表中插入两条记录
mysql> use first_db;
Database changed
mysql> insert into first_tb values (007,’first’);
Query Ok, 1 row affected (0.00 sec)
mysql> insert into first_tb values (110,’second’);
Query Ok, 1 row affected (0.00 sec)

查询记录
mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
1 rows in set (0.00 sec)
=============================
通过读操作并没有看到新记录

mysql> quit
退出MySQL-Proxy

下面，分别登陆到主从数据库服务器，对比记录信息
首先，检查主数据库服务器
mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
| 007 | first |
+------+------+
| 110 | second |
+------+------+
3 rows in set (0.00 sec)
=============================
两条新记录都已经存在

然后，检查从数据库服务器
mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
1 rows in set (0.00 sec)
=============================
没有新记录存在

由此验证，我们已经实现了MySQL读写分离，目前所有的写操作都全部在Master主服务器上，用来避免数据的不同步；
另外，所有的读操作都分摊给了其它各个Slave从服务器上，用来分担数据库压力。

经验分享：
1.当MySQL主从复制在 show slave status\G 时出现Slave_IO_Running或Slave_SQL_Running 的值不为YES时，需要首先通过 stop slave 来停止从服务器，然后再执行一次本文 2.1与2.2 章节中的步骤即可恢复，但如果想尽可能的同步更多的数据，可以在Slave上将master_log_pos节点的值在之前同步失效的值的基础上增大一些，然后反复测试，直到同步OK。因为MySQL主从复制的原理其实就是从服务器读取主服务器的binlog，然后根据binlog的记录来更新数据库。

2.MySQL-Proxy的rw-splitting.lua脚本在网上有很多版本，但是最准确无误的版本仍然是源码包中所附带的lib/rw-splitting.lua脚本，如果有lua脚本编程基础的话，可以在这个脚本的基础上再进行优化；

3.MySQL-Proxy实际上非常不稳定，在高并发或有错误连接的情况下，进程很容易自动关闭，因此打开--keepalive参数让进程自动恢复是个比较好的办法，但还是不能从根本上解决问题，因此通常最稳妥的做法是在每个从服务器上安装一个MySQL-Proxy供自身使用，虽然比较低效但却能保证稳定性；

4.一主多从的架构并不是最好的架构，通常比较优的做法是通过程序代码和中间件等方面，来规划，比如设置对表数据的自增id值差异增长等方式来实现两个或多个主服务器，但一定要注意保证好这些主服务器数据的完整性，否则效果会比多个一主多从的架构还要差；

5.MySQL-Cluster 的稳定性也不是太好；

6.Amoeba for MySQL 是一款优秀的中间件软件，同样可以实现读写分离，负载均衡等功能，并且稳定性要大大超过MySQL-Proxy，建议大家用来替代MySQL-Proxy，甚至MySQL-Cluster。

数据库主从复制与读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于前面我们学习过的rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

如图所示：

一、案例前置知识点

1、 mysq支持的复制类型

1）基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。

2）基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

3）混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

2、复制的工作过程

1）在每个事务更新数据完成之前，master在二进制日志记录这些改变。写入二进制日志完成后，master通知存储引擎提交事务。

2） Slave将master的binary log复制到其中继日志。首先slave开始一个工作线程（I/O），I/O线程在master上打开一个普通的连接，然后开始binlog dump process。binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件，I/O线程将这些事件写入中继日志。

3） Sql slave thread（sql从线程）处理该过程的最后一步，sql线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会位于os缓存中，所以中继日志的开销很小。

如图所示：

3、 mysql读写分离原理

读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。

前较为常见的Mysql读写分离分为以下两种：

1）基于程序代码内部实现

在代码中根据select 、insert进行路由分类，这类方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。

2）基于中间代理层实现

代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到，后端数据库，有以下代表性的程序。

（1）mysql_proxy。mysql_proxy是Mysql的一个开源项目，通过其自带的lua脚本进行sql判断。

（2）Atlas。是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。

（3）Amoeba。由阿里巴巴集团在职员工陈思儒使用序java语言进行开发，阿里巴巴集团将其用户生产环境下，但是他并不支持事物以及存数过程。

经过上述简单的比较，不是所有的应用都能够在基于程序代码中实现读写分离，像一些大型的java应用，如果在程序代码中实现读写分离对代码的改动就较大，所以，像这种应用一般会考虑使用代理层来实现，那么今天就使用Amoeba为例，完成主从复制和读写分离。

MySQLProxy介绍

下面使用MySQL官方提供的数据库代理层产品MySQLProxy搭建读写分离。
MySQLProxy实际上是在客户端请求与MySQLServer之间建立了一个连接池。所有客户端请求都是发向MySQLProxy，然后经由MySQLProxy进行相应的分析，判断出是读操作还是写操作，分发至对应的MySQLServer上。对于多节点Slave集群，也可以起做到负载均衡的效果。

实验图:

懂的越多，不会的也就越多，知识之路是不断进取的

mysql主从复制

mysql主从复制
（超简单）

怎么安装mysql数据库，这里不说了，只说它的主从复制，步骤如下：

1、主从服务器分别作以下操作：
1.1、版本一致
1.2、初始化表，并在后台启动mysql
1.3、修改root的密码

2、修改主服务器master:
   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=222 //[必须]服务器唯一ID，默认是1，一般取IP最后一段

3、修改从服务器slave:
   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[不是必须]启用二进制日志
       server-id=226 //[必须]服务器唯一ID，默认是1，一般取IP最后一段

4、重启两台服务器的mysql
   /etc/init.d/mysql restart

5、在主服务器上建立帐户并授权slave:
   #/usr/local/mysql/bin/mysql -uroot -pmttang
   mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456'; //一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。

6、登录主服务器的mysql，查询master的状态
   mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000004 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)
   注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

7、配置从服务器Slave：
   mysql>change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456',
         master_log_file='mysql-bin.000004',master_log_pos=308;   //注意不要断开，308数字前后无单引号。

   Mysql>start slave;    //启动从服务器复制功能

8、检查从服务器复制功能状态：

   mysql> show slave status\G

   *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.222 //主服务器地址
Master_User: mysync //授权帐户名，尽量避免使用root
Master_Port: 3306 //数据库端口，部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 600 //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
                    ......

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

以上操作过程，主从服务器配置完成。

9、主从服务器测试：

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

mysql> create database hi_db;
Query OK, 1 row affected (0.00 sec)

mysql> use hi_db;
Database changed

mysql> create table hi_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into hi_tb values(001,'bobu');
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | hi_db |
   | mysql |
   | test |
   +--------------------+
   4 rows in set (0.00 sec)

从服务器Mysql查询：

   mysql> show databases;

   +--------------------+
   | Database |
   +--------------------+
   | information_schema |
   | hi_db | //I'M here，大家看到了吧
   | mysql |
   | test |

+--------------------+
   4 rows in set (0.00 sec)

   mysql> use hi_db
   Database changed
   mysql> select * from hi_tb; //查看主服务器上新增的具体数据
   +------+------+
   | id   | name |
   +------+------+
   |    1 | bobu |
   +------+------+
   1 row in set (0.00 sec)

10、完成：
编写一shell脚本，用nagios监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了，发短信警报吧。

windows下的MySQL主从复制

MySQL的主从复制是通过binlog日志来实现的，主从复制中的“主”指的是MySQL主服务器上的数据库，“从”指的是MySQL从服务器上的数据库，且这种复制是基于数据库级别的，为此从服务器中的数据库名称必须和主服务器中的数据库名称保持一致，那么，要想实现主从复制，我们至少要有两个MySQL服务器（最好是两个MySQL服务器分别位于不同的主机上，或者在一个主机上安装两个MySQL，端口不同即可）。

一般来说，MySQL数据库的主库和从库是分布在不同的主机上。假如，现在我们只有一台主机，且是windows系统，如何实现MySQL的主从复制呢？方法如下：

这里我们只介绍一主一从的操作方法。

我的电脑已经安装了 xampp 集成环境（同wamp安装包类似），可以把它里面的MySQL服务作为MySQL的主服务器。那么，我们还需要在这台电脑上再安装一个MySQL，作为数据库的从服务器。

我的电脑xampp中已安装的MySQL版本为 5.6.20，端口为3306。

我们需要再安装一个MySQL（最好安装同版本和相近版本的，以免出现问题），端口改为3307，安装方法见：http://blog.csdn.net/lamp_yang_3533/article/details/52263751

数据库服务器的参数：

主服务器（master）： IP为127.0.0.1，端口为3306
从服务器（slave）： IP为127.0.0.1，端口为3307

主服务器配置：

修改主服务器的数据库配置文件（E:\xampp\mysql\bin\my.ini），在 [mysqld] 标签的最下面，添加如下代码：

#需要备份的数据库

binlog-do-db=test

#不需要备份的数据库

binlog-ignore-db=mysql

#开启二进制日志

log-bin=mysql-bin

#服务器id

server-id=1

保存退出，重启MySQL主服务器。

binlog-do-db用于指定需要同步的数据库，binlog-ignore-db指定不需要同步的数据库，如果这两个参数都不设置，则从服务器会复制主服务器的所有数据库。

一般不用root账号作同步账号，为此，我们需要在主服务器上创建一个新的用户（如 user01，密码为123456）。

这里我们用命令行的方式创建，方法如下：

打开cmd，切换至 E:\xampp\mysql\bin，用 root 账户连接MySQL主服务器：

mysql -uroot -p -P3306

创建新用户：

create user 'user01'@'127.0.0.1' identified by '123456';

（@后面的ip地址为允许连接的客户端的ip地址。）

然后，给新用户配置主从复制的权限：

grant replication slave on *.* to 'user01'@'127.0.0.1' identified by '123456';

（@后面的ip地址为允许连接的客户端的ip地址，如果改为 '%'，就表示客户端没有ip地址的限制）

如果主服务器的数据库（test）中，已经有数据，我们需要先手动把主服务器中的数据复制到从服务器。方法如下：

在本案例中，我们只备份一个数据库（test），test中有一个表basic_user，表中也已经有了数据。为了防止我们复制数据的时候，数据库test中的数据发生更新，我们需要先锁定数据库，命令如下：

flush tables with read lock;

这个命令是全局读锁定，它会给主服务器中的所有数据库都加上读锁，这里顺便说一下读锁和写锁的区别：

read lock（读锁）：也叫共享锁，允许所有的读操作，但阻塞写操作，即所有连接只可以读数据，但不允许写数据。
write lock（写锁）：也叫排它锁、独占锁，只允许当前连接的读和写，不允许其他并发的读操作和写操作。

锁定主服务器的数据库后，我们在从服务器中，也创建一个数据库test，并将所有的表（包括表结构和表数据）都导入。

然后，我们执行下面的命令，解锁：

unlock tables;

查看主服务器的 master 状态：

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

从服务器配置：

修改从服务器的数据库配置文件（E:\mysql\my.ini），在 [mysqld] 标签的最下面，添加如下代码：

#端口

port = 3307

#服务器id

server_id = 2

#开启二进制日志（从服务器不是必须要开启二进制日志）

log-bin=mysql-bin

保存退出，重启MySQL服务。

连接MySQL从服务器：

mysql -uroot -p -P3307

配置复制的参数：

change master to master_host='127.0.0.1',master_user='user01',master_password='123456',master_port=3306,master_log_file='mysql-bin.000008',master_log_pos=498;

参数详解：

master_host：主服务器的IP
master_user：主服务器上新创建的用户名
master_password：用户的密码
master_port：主服务器的端口，如果未曾修改，默认即可。

master_log_file：主服务器二进制日志文件的名称，填写查看主服务器的master状态时显示的File的值

master_log_pos：日志的位置，填写查看主服务器的master状态时显示的Position的值

启动从服务器的slave复制功能：

start slave;

查看从服务器的slave状态：

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: user01

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 120

Relay_Log_File: hp-PC-relay-bin.000004

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果 Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes，则说明主从复制的所有配置已成功，即从服务器已经可以自动与主服务器的数据库数据实现同步了。

此后，只要主服务器的数据有更新（比如：在 test数据库中新建了一张表或者表中的数据发生了变化），从服务器都会自动与主服务器保持一致。但如果有人刻意改变了从服务器的数据，主服务器中的数据并不会同步更新，除非我们把这两个MySQL服务器设置为互为主从。

懂的越多，不会的也就越多，知识之路是不断进取的

Pang文

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
大项目都是这么玩mysql主从读写分离

为什么MySQL要做主从复制（读写分离）？通俗来讲，如果对数据库的读和写都在同一个数据库服务器中操作，业务系统性能会降低。为了提升业务系统性能，优化用户体验，可以通过做主从复制（读写分离）来减轻主数据库的负载。而且如果主数据库宕机，可快速将业务系统切换到从数据库上，可避免数据丢失。MySQL主从复制（读写分离）和集群的区别：我对MySQL也是刚开始研究，不是很专业。我的理...
复制链接

扫一扫