领购物优惠券9块9包邮
首页 > 数据库 > MySQL教程

Mysql主从复制(master-slave)实际操作案例

   2015-09-21 0
核心提示：这篇文章主要介绍了Mysql主从复制(master-slave)实际操作案例,同时介绍了Mysql grant 用户授权的相关内容,需要的朋友可以参考下

在这一章节里， 我们来了解下如何在 Mysql 中进行用户授权及主从复制
 
这里先来了解下 Mysql 主从复制的优点:
 
1、 如果主服务器出现问题， 可以快速切换到从服务器提供的服务
2、 可以在从服务器上执行查询操作， 降低主服务器的访问压力
3、 可以在从服务器上执行备份， 以避免备份期间影响主服务器的服务
注意一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询， 实时性要求高的数据仍然需要从主数据库获得
 
在这里我们首先得完成用户授权， 目的是为了给从服务器有足够的权限来远程登入到主服务器的 Mysql
 
在这里我假设
主服务器的 IP 为: 192.168.10.1
从服务器的 IP 为: 192.168.10.2
 
Mysql grant 用户授权
 
查看 Mysql 的用户表

复制代码 代码如下:

msyql> mysql -uroot -p123123;
msyql> select user, host, password from mysql.user;

结果如下:
复制代码 代码如下:
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root             | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+------------------+-----------+-------------------------------------------+

从如上表中看以看出 root 用户只能从本机登入 Mysql， 也就是来自 localhost 或者 127.0.0.1
 
现在来通过 grant 命令来添加授权用户
复制代码 代码如下:

msyql> ？ grant   //查看 grant 的详细用法
 
msyql> grant all on *.* to user1@192.168.10.2 identified by "123456"; // *.* = 所有的数据库.所有的表
//或者
msyql> grant replication slave on *.* to 'user2'@'192.168.10.%' identified by "123456"; // %代表通配符

通过了 grant 命令给予了来自 192.168.10.2 的用户 user1 权限， 允许其远程登录, 如下:
复制代码 代码如下:

+------------------+--------------+-------------------------------------------+
| user             | host         | password                                  |
+------------------+--------------+-------------------------------------------+
| root             | localhost    | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root             | 127.0.0.1    | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| user1            | 192.168.10.2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| user2            | 192.168.10.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------------+--------------+-------------------------------------------+

此时就可以在 192.168.10.2 的机器上访问 10.1 的 Mysql 了， 如下:
复制代码 代码如下:

msyql> mysql -uuser1 -p123456 -h192.168.10.1;


Mysql bin-log 日志
 
开启 bin-log 二进制日志， 它保存了所有增删改的操作， 以便于数据恢复或同步

修改主服务器 mysql 配置文件:

复制代码 代码如下:
shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
 
/********** my.cnf **********/
[mysqld]
 
#开启慢查询日志， 记录查询过长的 sql 语句，以便于优化
log_slow_queries   = /var/log/mysql/mysql-slow.log
 
#开启 bin-log 日志
log-bin            = /var/log/msyql/mysql-bin.log

添加完成后重启 Mysql 服务
复制代码 代码如下:

shawn@Shawn:~$ sudo /etc/init.d/mysql restart

现在你可以通过如下命令来查看 bin-log 日志是否成功开启
复制代码 代码如下:

mysql> show variables like "%log_%";
 
| log_bin                 | ON        |
| log_slow_queries        | ON        |

如果显示为 ON， 那么就可以在 /var/log/mysql/ 文件夹看到 mysql-bin.000001 二进制文件
 
关于 bin-log 日志的相关操作:
复制代码 代码如下:

mysql> flush logs；

此时就会多一个最新的 bin-log 日志
复制代码 代码如下:

mysql> show master status;

查看最后一个 bin-log 日志， 如下:
复制代码 代码如下:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+
 
mysql> show master logs;

查看所有 bin-log 日志， 如下:
复制代码 代码如下:

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      4340 |
| mysql-bin.000002 |       107 |
+------------------+-----------+
 
mysql> reset master;

清空所有 bin-log 日志
复制代码 代码如下:

shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000001 | more

查看 bin-log 日志内容
复制代码 代码如下:

#如果有字符集问题的话可以执行:
shawn@Shawn:~$ mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000001

shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000002 | mysql -uroot -p123123 test;
恢复 mysql-bin.000002 中所有的操作到 test 数据库中

shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000002 --start-position="193" --stop-position="398" | mysql -uroot -p123123 test;
恢复 mysql-bin.000002 中指定的操作(position)到 test 数据库中


 
Mysql 主从复制 - 数据同步
 
到这一步的时候首先确保 Mysql 用户授权已经完成以及 Mysql bin-log 日志已经成功开启
并确保每台服务器的 server-id 是唯一的
 
再次修改主服务器(192.168.10.1)的 mysql 配置文件:
复制代码 代码如下:

shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
 
/********** my.cnf **********/
#取消 server-id 注释符号
server-id   = 1
/****************************/
 
#重启 Mysql 服务
shawn@Shawn:~$ sudo /etc/init.d/mysql restart

到这里， 主服务器的配置已经完成， 很简单
 
这次我们主要做的是让从服务器同步主服务器的数据， 同步的是将来所有对主服务做的增删改操作， 但是现有主服务器中的大量数据得先手动同步到从服务器， 操作如下:
复制代码 代码如下:

#清空一下主服务器的 bin-log 日志， （可选: 保险操作， 防止主从 bin-log 日志混乱）
mysql> reset master;
 
#然后备份导出主服务器中现有的 test 数据库
shawn@Shawn:~$ mysqldump -uroot -p123123 test -l -F > /tmp/test.sql;
 
-F = flush logs, 生成新的日志文件， 包括 bin-log 日志
-l = lock 数据库， 防止在导出的时候被写入数据， 完成后自动解锁
 
#完成后把文件传输给从服务器
shawn@Shawn:~$ scp /tmp/test.sql 192.168.10.2:/tmp/
 
#然后再查询确保一下从服务器已经成功授过权
mysql> show grants for user1@192.168.10.2\G
 
*************************** 1. row ***************************
Grants for user1@192.168.10.2:
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.10.2'
IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

完成后， 现在我们到从服务器 (192.168.10.2) 导入现有的数据:
复制代码 代码如下:

#清空一下从服务器的 bin-log 日志， （可选: 保险操作）
mysql> reset master;
 
#然后导入主服务器中现有的数据
shawn@Shawn:~$ mysqldump -uroot -p123123 test -v -f < /tmp/test.sql;

 
-v = 查看导入的详细信息
-f = 是当中间遇到错误时， 可以 skip 过去， 继续执行下面的语句
当然你也可以用 source 命令导入
好了， 目前为止主服务器(192.168.10.1)和从服务器(192.168.10.2)现有的数据已经成功手动同步
 
接下来修改从服务器(192.168.10.2)的 mysql 配置文件:
复制代码 代码如下:

shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
 
/********** my.cnf **********/
#取消 server-id 注释符号, 并修改值
server-id       = 2
 
#取消 master-host 注释符号, 并修改值
master-host     = 192.168.10.1
 
#取消 master-user 注释符号, 并修改值
master-user     = user1
 
#取消 master-password 注释符号, 并修改值
master-password = 123456
 
#取消 master-port 注释符号, 并修改值, 主服务器默认端口号为: 3306
master-port     = 3306
/****************************/
 
#重启 Mysql 服务
shawn@Shawn:~$ sudo /etc/init.d/mysql restart

配置文件修改完成， 此时在从服务器中登入自己的 Mysql, 而不是远程登入主服务器(192.168.10.1)
复制代码 代码如下:

#在从服务器中登入自身的 Mysql
msyql> mysql -uroot -p123123;
 
#查看是否已经取得同步
msyql> show slave status\G
 
*************************** 1. row ***************************
      Connect_Retry: 60
    Master_Log_FIle: mysql-bin.000002
Read_Master_Log_Pos: 106
   Slave_IO_Running: Yes 
  Slave_SQL_Running: Yes

Slave_IO_Running 如果是 Yes 的话代表成功从主服务器中同步到 bin-log 日志
Slave_SQL_Running 如果是 Yes 的话代表成功执行 bin-log 日志中的 SQL 语句
此时的 Master_Log_FIle 和 Read_Master_Log_Pos 的值应该对应主服务器中的 show master status 命令的值
Connect_Retry 中的 60 代表每 60 秒就去主服务器同步 bin-log 日志
 
OK, 如果你看到的是那两个关键的 Yes， 那你就可以去测试了， 在主服务器插入新的数据， 再去从服务器查看， 不出意外的话， 你会兴奋一下， 数据已经同步了
 
这里再说一下其他经常用到的命令:
复制代码 代码如下:

#启动复制线程
msyql> start slave
 
#停止复制线程
msyql> stop slave
 
#动态改变到主服务器的配置
msyql> change master to
 
#查看从数据库运行进程
msyql> show processlist

这里也同时说一下操作中的常见错误:
 
问题: 从数据库无法同步
Slave_SQL_Running 值为 NO， 或 Seconds_Bebind_Master 值为 Null

原因:
一、 程序有可能在 slave 上进行了写操作
二、 也有可能是 slave 机器重启后， 事务回滚造成的

解决方法一:

复制代码 代码如下:

msyql> stop slave;
 
msyql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
 
msyql> start slave;

解决方法二:
复制代码 代码如下:

msyql> stop slave;
 
#查看主服务器上当前的 bin-log 日志名和偏移量
msyql> show master status;
 
#获取到如下内容:
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      286 |              |                  |
+------------------+----------+--------------+------------------+
 
#然后到从服务器上执行手动同步
msyql> change master to
    -> master_host="192.168.10.1"
    -> master_user="user1"
    -> master_password="123456"
    -> master_post=3306
    -> master_log_file="mysql-bin.000005"
    -> master_log_pos=286;
    
msyql> start slave;

再次通过 show slave status 查看:
如果 Slave_SQL_Running 的值变为 Yes， Seconds_Bebind_Master 的值为 0 时， 即正常
 
好了， 如上是我自己在操作中所总结的一些内容， 如有更好的建议， 欢迎留言一起探讨
顺便说一下， 我使用的是 Ubuntu 12.04

 
标签： Mysql 主从复制
反对 0举报 0 评论 0
 

免责声明：本文仅代表作者个人观点，与乐学笔记（本网）无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
    本网站有部分内容均转载自其它媒体，转载目的在于传递更多信息，并不代表本网赞同其观点和对其真实性负责，若因作品内容、知识产权、版权和其他问题，请及时提供相关证明等材料并与我们留言联系，本网站将在规定时间内给予删除等相关处理.

  • sql：mysql：函数：TIMESTAMPDIFF函数实现TimeStamp字段相减，求得时间差
    sql：mysql：函数：TIMESTAMPDIFF函数实现TimeS
     函数内指定是minute，则最终结果value值的单位是分钟，如果函数内指定为hours，则最终结果value值单位为小时。//UPLOAD_TIME 减去 CREATE_DTTM 求得时间差，以分钟数计时select avg(TIMESTAMPDIFF(MINUTE,CREATE_DTTM,UPLOAD_TIME)) value,LEFT(CREATE_DTTM
    03-08
  • mysql下如何执行sql脚本 执行SQL脚本
    1.编写sql脚本,假设内容如下:  create database dearabao;  use dearabao;  create table niuzi (name varchar(20));  保存脚本文件,假设我把它保存在F盘的hello world目录下,于是该文件的路径为:F:\hello world\niuzi.sql2.执行sql脚本,可以有2种方法: 
    02-10
  • MySQL 5.7版本sql_mode=only_full_group_by问题
    用到GROUP BY 语句查询时com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not functionally dependent on colu
    02-10
  • Oracle迁移到MySQL性能下降的注意点 oracle数据
    背景：最近有较多的客户系统由原来由Oracle改造到MySQL后出现了性能问题CPU 100%，或是后台的CRM系统复杂SQL在业务高峰的时候出现堆积导致业务故障。在我的记忆里面淘宝最初从Oracle迁移到MySQL期间也遇到了很多SQL的性能问题，记忆最为深刻的子查询，当初的
    02-10
  • MySQL与Oracle 差异比较之六触发器
    触发器编号类别ORACLEMYSQL注释1创建触发器语句不同create or replace trigger TG_ES_FAC_UNIT  before insert or update or delete on ES_FAC_UNIT  for each rowcreate trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_u
    02-10
  • mysql where条件：某时间字段为今天的sql语句
    1.查询：注册时间为今天的所有用户数：select count(*) from customer where TO_DAYS(createtime) = TO_DAYS(NOW())2.获取当前时间到凌晨24点还有多长时间：（Java中可用于判断某时间是否为今天）final Calendar cal = Calendar.getInstance();    ca
    02-10
  • mysql中的sql
    变量用户变量: 在用户变量前加@系统变量: 在系统变量前加@@运算符算术运算符有: +(加), -(减), * (乘), / (除) 和％ (求模) 五中运算位运算符有:(位于), | (位或), ^ (位异或), ~ (位取反),(位右移),(位左移)比较运算符有: = (等于),(大于),(小于), = (大
    02-10
  • mysql5.7配置文件修改sql_mode 重启无效解决方法。this is incompatible with sql_mode=only_full_group_by
    mysql5.7配置文件修改sql_mode 重启无效解决方
    whereis my.cnf找到配置路径：/etc/my.cnf找到[mysqld]，在下面添加sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION重要：如果没有[mysqld]，一定要先添加[mysqld]再在下
    02-10
  • mysql 8 查询报错(sql_mode=only_full_group_by)
    mysql 8 查询报错(sql_mode=only_full_group_by
    Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_
    02-10
  • Oracle、MySql、Sql Server比对
    MySql:廉价(部分免费)：当前，MySQL採用双重授权(DualLicensed)，他们是GPL和MySQLAB制定的商业许可协议。假设你在一个遵循GPL的***（开源）项目中使用MySQL，那么你能够遵循GPL协议免费使用MySQL。否则，你须要购买MySQLAB制定的那个商业许可协议。Windows $
    02-10
更多>MySQL教程
MySQL中表子查询与关联子查询的基础学习教程 CentOS系统下编译安装MySQL以及设置相关yum源的教程
更多>Oracle教程
Oracle数据库中表空间的基本管理操作小结 在Linux系统上同时监控多个Oracle数据库表空间的方法
更多>SQL Server
SQL SERVER修改函数名容易引发的问题分析 SQLSERVER分页查询关于使用Top方式和row_number()解析函数的不同
更多>MongoDB
教大家8天学通MongoDB——第一天 基础入门篇 MongoDB运行状态监控、性能分析工具mongostat详解
更多>PostgreSQL
PostgreSQL角色与用户管理介绍 Windows下PostgreSQL安装图解
点击排行
网站首页  |  关于我们  |  联系方式  |  使用协议  |  版权隐私  |  网站地图  |  网站留言  |  违规举报