≡
  • 网络编程
  • 数据库
  • CMS技巧
  • 软件编程
  • PHP笔记
  • JavaScript
  • MySQL
位置：首页 > 数据库 > MySQL

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

人气：340 时间：2020-07-09

这篇文章主要为大家详细介绍了MySQL主从复制(master-slave)实际操作案例，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随四海网的小编小韵来看看吧！

在这一章节里， 我们来了解下如何在 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

 

本文来自：http://www.q1010.com/177/9365-0.html

注：关于MySQL主从复制(master-slave)实际操作案例的内容就先介绍到这里，更多相关文章的可以留意四海网的其他信息。

关键词：MYSQL

您可能感兴趣的文章

  • MySQL 一次更新(update)多条记录的思路
  • MySQL错误Cannot find or open table x/x from the internal问题解决方法
  • MySQL 5.7 create VIEW or FUNCTION or PROCEDURE
  • Mysql错误使用SQL语句Groupby被兼容的简单示例
  • Mysql数据库性能优化三（分表、增量备份、还原）
  • Mysql用户忘记密码及密码过期问题的处理示例
  • java连接mysql数据库,java连接SQL Server 数据库的简单示例
  • MySQL中文乱码解决示例
  • MySQL查询两个日期之间记录的简单示例
  • mysql优化插入记录速度的简单示例
上一篇：MySQL查询今天、昨天、近7天、近30天、本月、上一月的SQL语句
下一篇：MySQL错误Cannot find or open table x/x from the internal问题解决方法
热门文章
  • mysql 修改character_set_server为utf-8的简单示例
  • 解决MySQL丢失文件localhost.localdomain.pid、mysql.sock的示例
  • MySQL 数据类型binary和varbinary的简单示例
  • MySQL:reading initial communication packet问题解决方法
  • MySql 表类型MYISAM、InnoDB区别
  • bash: mysql: command not found 的解决方法
  • MYSQL默认用户名ROOT修改方法
  • MySQL 常用命令菜鸟教程
  • MySQL 使用命令行新建用户并授予权限
  • MySql 数据库物理文件存放位置查看示例
  • 最新文章
    • MySQL查看死锁与解除死锁的简单示例
    • MySQL 慢查询的功能实例
    • MySQL查看死锁与去除死锁的简单示例
    • MySQL找出未提交事务的SQL的简单示例
    • MySQL锁阻塞的的简单示例
    • MySQL中的binary类型使用操作的示例
    • SQL优化教程之in与range查询的简单示例
    • MySQL 的 21 个规范、优化最佳实践！
    • MySQL 字符类型大小写敏感的简单示例
    • 解决mybatis-plus分页传入参数后sql where条件没有limit分页信息的问题

四海网收集整理一些常用的php代码,JS代码,数据库mysql等技术文章。