Mysql主从安装配置方法

59次阅读

共计 6650 个字符，预计需要花费 17 分钟才能阅读完成。

本篇内容介绍了“Mysql 主从安装配置方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

环境：
主从服务器上的 MySQL 版本同为 5.1.34
主机 IP：192.168.0.1
从机 IP：192.168.0.2

一. MySQL 主服务器配置
1. 编辑配置文件 /etc/my.cnf
# 确保有如下行 www.2cto.com
server-id = 1
log-bin=-bin
binlog-do-db=mysql # 需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
binlog-ignore-db=mysql # 不需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
log-slave-updates #这个参数一定要加上，否则不会给更新的记录些到二进制文件里
slave-skip-errors #是跳过错误，继续执行复制操作

2. 建立用户
mysql grant replication slave on *.* to slave@192.168.0.2 identified by lsquo;111111 prime;;
# grant replication slave on *.* to lsquo; 用户名 rsquo;@ 主机 rsquo; identified by lsquo; 密码 rsquo;;
# 可在 Slave 上做连接测试: mysql -h 192.168.0.1 -u test -p

3. 锁主库表
mysql FLUSH TABLES WITH READ LOCK;

4. 显示主库信息
记录 File 和 Position，从库设置将会用到
=====================
mysql SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+——————+———-+————–+——————+
| mysql-bin.000001 | 106 | | |
+——————+———-+————–+——————+

5. 另开一个终端，打包主库
cd /usr/local/mysql #mysql 库目录
tar zcvf var.tar.gz var www.2cto.com
============================

二.MySQL 从服务器配置
1、传输拿到主库数据包、解包
# cd /usr/local/mysql
# scp 192.168.0.1:/usr/local/mysql/var.tar.gz .
# tar zxvf var.tar.gz

2、查看修改 var 文件夹权限
# chown -R mysql:mysql var
3. 编辑 /etc/my.cnf
server-id=2
log-bin=mysql-bin
master-host=192.168.0.1
master-user=slave
master-password=111111
master-port=3306
replicate-do-db=test # 需要备份的数据库名
replicate-ignore-db=mysql #忽略的数据库
master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差(秒)
log-slave-updates #这个参数一定要加上，否则不会给更新的记录些到二进制文件里
slave-skip-errors #是跳过错误，继续执行复制操作

4、验证连接 MASTER
# mysql -h292.168.0.1 -uslave -ppassword
mysql show grants for slave@192.168.0.2;
5、在 SLAVE 上设置同步
设置连接 MASTER MASTER_LOG_FILE 为主库的 File，MASTER_LOG_POS 为主库的 Position
============================
mysql slave stop;
mysql CHANGE MASTER TO MASTER_HOST= 192.168.0.1 ,MASTER_USER= slave ,MASTER_PASSWORD= 111111 ,MASTER_LOG_FILE=
mysql-bin.000001 ,MASTER_LOG_POS=106; www.2cto.com
6、启动 SLAVE 服务
mysql slave start;

7、查看 SLAVE 状态
mysql SHOW SLAVE STATUS\G;
其中 Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 Yes，表明 Slave 的 I/O 和 SQL 线程
都在正常运行。
8、解锁主库表
mysql UNLOCK TABLES;
到此主从库搭建成功。可以在主库上插入数据测试同步是否正常。

常见错误及解决方法：
常见问题的处理：
www.2cto.com
1：在从库上面 show slave status\G; 出现下列情况，
Slave_IO_Running: Yes
Slave_SQL_Running: No
Seconds_Behind_Master: NULL

原因：
a. 程序可能在 slave 上进行了写操作
b. 也可能是 slave 机器重起后，事务回滚造成的.

解决方法：

进入 master

mysql show master status;
+———————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+———————-+———-+————–+——————+
| mysql-bin.000040 | 324 | | |
+———————-+———-+————–+——————+
然后到 slave 服务器上执行手动同步

slave stop;
change master to
master_host= 10.14.0.140 ,
master_user= repl ,
master_password= 111111 ,
master_port=3306,
master_log_file= mysql-bin.000040 ,
master_log_pos=324;
slave start;
show slave status\G;

2、现象：从数据库无法同步，show slave status 显示 Slave_IO_Running 为 No,Seconds_Behind_Master
为 null

解决：重启主数据库

service mysql restart

mysql show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 98 | | |
+——————+———-+————–+——————+
slave stop;
change master to Master_Log_File= mysql-bin.000001 ,Master_Log_Pos=98
slave start;
或是这样：
stop slave;
set global sql_slave_skip_counter =1;
start slave;

这个现象主要是 master 数据库存在问题，我在实际的操作中先重启 master 后重启 slave 即可解决这问题，
出现此问题，必须要要重启 master 数据库。

1. 主辅库同步主要是通过二进制日志来实现同步的。
2. 在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info 文件。因为 master.info 记录
了上次要连接主库的信息，如果不删除，即使 my.cnf 里进行了修改，也不起作用。因为读取的还是
master.info 文件里的信息。

在 mysql 复制环境中, 有 8 个参数可以让我们控制, 需要复制或需要忽略不进行复制的 DB 或 table 分别为:
下面二项需要在 Master 上设置：
Binlog_Do_DB: 设定哪些数据库需要记录 Binlog
Binlog_Ignore_DB: 设定哪里数据库不需要记录 Binlog

优点是 Master 端的 Binlog 记录所带来的 Io 量减少，网络 IO 减少，还会让 slave 端的 IO 线程,SQL 线程减少，
从而大幅提高复制性能,
缺点是 mysql 判断是否需要复制某个事件不是根据产生该事件的查询所在的 DB, 而是根据执行查询时刻所在
的默认数据库（也就是登录时指定的库名或运行 use database 中指定的 DB）, 只有当前默认 DB 和配置中
所设定的 DB 完全吻合时 IO 线程才会将该事件读取给 slave 的 IO 线程. 所以, 如果在默认 DB 和设定须要复制的
DB 不一样的情况下改变了须要复制的 DB 中某个 Table 中的数据, 该事件是不会被复制到 Slave 中去的, 这样就
会造成 Slave 端的数据和 Master 的数据不一致. 同样, 在默认的数据库下更改了不须要复制的数据库中的数据,
则会被复制到 slave 端, 当 slave 端并没有该数据库时, 则会造成复制出错而停止.

下面六项需要在 slave 上设置：
Replicate_Do_DB: 设定需要复制的数据库, 多个 DB 用逗号分隔
Replicate_Ignore_DB: 设定可以忽略的数据库.
Replicate_Do_Table: 设定需要复制的 Table
Replicate_Ignore_Table: 设定可以忽略的 Table
Replicate_Wild_Do_Table: 功能同 Replicate_Do_Table, 但可以带通配符来进行设置。
Replicate_Wild_Ignore_Table: 功能同 Replicate_Do_Table, 功能同 Replicate_Ignore_Table, 可以带通配符。

优点是在 slave 端设置复制过滤机制, 可以保证不会出现因为默认的数据库问题而造成 Slave 和 Master 数据
不一致或复制出错的问题.
缺点是性能方面比在 Master 端差一些. 原因在于: 不管是否须要复制, 事件都会被 IO 线程读取到 Slave 端,
这样不仅增加了网络 IO 量, 也给 Slave 端的 IO 线程增加了 Relay Log 的写入量.
同步原理说明
MySQL 的 Replication 基于主服务器在二进制日志中跟踪所有对数据库的更改 (更新、删除等)。
MySQL 使用 3 个线程来完成 Replication 工作，具体分布是主上 1 个相关线程、从上 2 个相关线程；
主的相关线程可以理解为主服务器上 SHOW PROCESSLIST 的输出中的 Binlog Dump 线程、从服务器分别为 IO 和
SQL 线程;
主服务器创建将 binlog 中的内容发送到从服务器。从服务器 I / O 线程读取主服务器 Binlog Dump 线程发送的
内容并将该数据拷贝到从服务器数据目录中的中继日志文件（relay-log）里，SQL 线程用于读取中继日志
并执行日志中包含的更新。
MySQL 的 Replication 是单向，异步同步
MySQL 同步机制基于 master 把所有对数据库的更新、删除等)都记录在二进制日志里。因此，想要启用同步
机制，在 master 就必须启用二进制日志。每个 slave 接受来自 master 上在二进制日志中记录的更新操作，
因此在 slave 上执行了这个操作的一个拷贝。应该非常重要地意识到，二进制日志只是从启用二进制日志开
始的时刻才记录更新操作的。所有的 slave 必须在启用二进制日志时把 master 上已经存在的数据拷贝过来。
如果运行同步时 slave 上的数据和 master 上启用二进制日志时的数据不一致的话，那么 slave 同步就会失败。
把 master 上的数据拷贝过来的方法之一实在 slave 上执行 LOAD DATA FROM MASTER 语句。不过要注意，LOAD DATA FROM MASTER 是从 MySQL 4.0.0 之后才开始可以用的，而且只支持 master 上的 MyISAM 类型表。同样地，
这个操作需要一个全局的读锁，这样的话传送日志到 slave 的时候在 master 上就不会有更新操作了。当实现了
自由锁表热备份时 (在 MySQL 5.0 中)，全局读锁就没必要了。由于有这些限制，因此我们建议只在 master 上
相关数据比较小的时候才执行 LOAD DATA FROM MASTER 语句，或者在 master 上允许一个长时间的读锁。
由于每个系统之间 LOAD DATA FROM MASTER 的速度各不一样，一个比较好的衡量规则是每秒能拷贝 1MB 数据。
这只是的粗略的估计，不过 master 和 slave 都是奔腾 700MHz 的机器且用 100MBit/ s 网络连接时就能达到这个
速度了。slave 上已经完整拷贝 master 数据后，就可以连接到 master 上然后等待处理更新了。如果 master
当机或者 slave 连接断开，slave 会定期尝试连接到 master 上直到能重连并且等待更新。重试的时间间隔由 ndash;master-connect-retry 选项来控制，它的默认值是 60 秒。每个 slave 都记录了它关闭时的日志位置。
master 是不知道有多少个 slave 连接上来或者哪个 slave 从什么时候开始更新。

MySQL 同步功能由 3 个线程 (master 上 1 个，slave 上 2 个) 来实现。执行 START SLAVE 语句后，slave 就创建
一个 I / O 线程。I/ O 线程连接到 master 上，并请求 master 发送二进制日志中的语句。master 创建一个线程来把
日志的内容发送到 slave 上。这个线程在 master 上执行 SHOW PROCESSLIST 语句后的结果中的 Binlog Dump
线程便是。slave 上的 I / O 线程读取 master 的 Binlog Dump 线程发送的语句，并且把它们拷贝到其数据目录
下的中继日志 (relay logs) 中。第三个是 SQL 线程，salve 用它来读取中继日志，然后执行它们来更新数据。
如上所述，每个 mster/slave 上都有 3 个线程。每个 master 上有多个线程，它为每个 slave 连接都创建一个线程，每个 slave 只有 I / O 和 SQL 线程。在 MySQL 4.0.2 以前，同步只需 2 个线程 (master 和 slave 各一个)。slave 上的 I /O
和 SQL 线程合并成一个了，它不使用中继日志。slave 上使用 2 个线程的优点是，把读日志和执行分开成 2 个
独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果 slave 停止了一段时间，那么
I/ O 线程可以在 slave 启动后很快地从 master 上读取全部日志，尽管 SQL 线程可能落后 I / O 线程好几的小时。
如果 slave 在 SQL 线程没全部执行完就停止了，但 I / O 线程却已经把所有的更新日志都读取并且保存在本地的中
继日志（relay-log）中了，因此在 slave 再次启动后就会继续执行它们了。这就允许在 master 上清除二进制
日志，因为 slave 已经无需去 master 读取更新日志了。执行 SHOW PROCESSLIST 语句就会告诉我们所关心的 master 和 slave 上发生的情况。