服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - Mysql主从复制(master-slave)实际操作案例

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

2020-04-06 14:22junjie Mysql

这篇文章主要介绍了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
  • 主从复制
  • Master-Slave

    • 延伸 · 阅读

    精彩推荐
    • MysqlMySQL锁的知识点总结

      MySQL锁的知识点总结

      在本篇文章里小编给大家整理了关于MySQL锁的知识点总结以及实例内容，需要的朋友们学习下。...

      别人放弃我坚持吖4362020-12-14
    • Mysql详解MySQL中的分组查询与连接查询语句

      详解MySQL中的分组查询与连接查询语句

      这篇文章主要介绍了MySQL中的分组查询与连接查询语句,同时还介绍了一些统计函数的用法,需要的朋友可以参考下 ...

      GALAXY_ZMY5442020-06-03
    • Mysql解决MySQl查询不区分大小写的方法讲解

      解决MySQl查询不区分大小写的方法讲解

      今天小编就为大家分享一篇关于解决MySQl查询不区分大小写的方法讲解，小编觉得内容挺不错的，现在分享给大家，具有很好的参考价值，需要的朋友一起...

      Veir_dev5592019-06-25
    • Mysqlmysql 不能插入中文问题

      mysql 不能插入中文问题

      当向mysql5.5插入中文时，会出现类似错误 ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column ...

      MYSQL教程网5722019-11-25
    • MysqlMySQL数据库varchar的限制规则说明

      MySQL数据库varchar的限制规则说明

      本文我们主要介绍了MySQL数据库中varchar的限制规则，并以一个实际的例子对限制规则进行了说明，希望能够对您有所帮助。 ...

      mysql技术网4192019-11-23
    • MysqlMySQL 数据备份与还原的示例代码

      MySQL 数据备份与还原的示例代码

      这篇文章主要介绍了MySQL 数据备份与还原的相关知识,本文通过示例代码给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考下...

      逆心2972019-06-23
    • MysqlERROR: Error in Log_event::read_log_event()

      ERROR: Error in Log_event::read_log_event()

      ERROR: Error in Log_event::read_log_event(): read error, data_len: 438, event_type: 2 ...

      MYSQL教程网6412020-03-13
    • Mysql浅谈mysql 树形结构表设计与优化

      浅谈mysql 树形结构表设计与优化

      在诸多的管理类，办公类等系统中，树形结构展示随处可见，本文主要介绍了mysql 树形结构表设计与优化，具有一定的参考价值，感兴趣的小伙伴们可以参...

      小码农叔叔5242021-11-16
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1449 　 1053 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1862 　 1864 　 1146 　 2003 　 1175 　 1215 　 1615 　 1054 　 2006 　 1130 　 1366 　 10048 　 28000 　 1786 　 1135 　 1577 　 1665 　 1067 　 1093 　
    © 2019-2024 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图