文档章节

mysql主从设置

mecisery
 mecisery
发布于 2017/09/04 16:58
字数 1596
阅读 11
收藏 1

参考:http://369369.blog.51cto.com/319630/790921/

  1、确保主数据库与从数据库一模一样。

    例如:主数据库里的a的数据库里有b,c,d表,那从数据库里的就应该有一个模子刻出来的a的数据库和b,c,d表

  2、在主数据库上创建同步账号。

    GRANT REPLICATION SLAVE ON *.* to 'djy'@'%' identified by '12345678';

    djy:是新创建的用户名

    12345678:是新创建的用户名的密码

    以上命令的详细解释,最好百度一下,写太多反到更加更不清思路。

  3、配置主数据库的my.ini(因为是在window下,所以是my.ini不是my.cnf)。

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

其他扩展配置项(无下面配置默认全库主从备份): 

binlog-do-db=djydjy #需要备份的数据库名,如果备份多个数据库,重复设置这个选项 即可
binlog-ignore-db=mysql #不需要备份的数据库名,如果备份多个数据库,重复设置这 个选项即可
log-slave-updates=1 #这个参数一定要加上,否则不会给更新的记录些到二进制文件 里
slave-skip-errors=all #是跳过错误,继续执行复制操作(可选)
expire_logs_days=2 #设置log保留天数

  配置从数据库的my.cnf。
     #vi /etc/my.cnf
       [mysqld]
       #log-bin=mysql-bin   //[不是必须]启用二进制日志
       server-id=226     //[必须]服务器唯一ID,默认是1,一般取IP最后一段
log-slave-updates=1 #这个参数一定要加上,否则不会给更新的记录些到二进制文件 里
slave-skip-errors=all #是跳过错误,继续执行复制操作(可选)
expire_logs_days=2 #设置log保留天数

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

5、在主服务器上建立帐户并授权slave:
   #/usr/local/mysql/bin/mysql -uroot -pmttang  
   mysql>GRANT REPLICATION SLAVE ON *.* to 'djy'@'%' identified by '12345678'; //一般不用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='10.20.4.10',master_user='djy',master_password='12345678',master_log_file='mysql-bin.000001',master_log_pos=154;    //注意不要断开,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,就表明主从有问题了,发短信警报吧。

 

11.精简命令及排错


GRANT REPLICATION SLAVE ON *.* to 'djy'@'%' identified by '12345678';
show master status;


change master to master_host='10.20.4.33',master_user='djy',master_password='12345678',master_log_file='mysql-bin.000003',master_log_pos=154;
start slave;
show slave status\G

 


查看MYSQL数据库中所有用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;


start slave;报错:ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
策略:使用reset slave all;清空所有的复制信息

MySQL报错:master and slave have equal MySQL server UUIDs
策略:
#  vi /main/mysql/data/auto.cnf
[auto]
server-uuid=f4863bcf-1d28-11e4-8657-000c29eec7bf
#随便改动一两个值即可以,或者按照上面的格式重新写一个

12.跳过错误,查错

mysql主从复制,经常会遇到错误而导致slave端复制中断,这个时候一般就需要人工干预,跳过错误才能继续
跳过错误有两种方式:
a.跳过指定数量的事务:
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳过一个事务
mysql>slave start

b.修改mysql的配置文件,通过slave_skip_errors参数来跳所有错误或指定类型的错误
vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误

c.还原被删除的数据

根据错误提示信息,用mysqlbinlog找到该条数据event SQL并逆向手动执行。如delete 改成insert。

本例中,此事件在主服务器Master binlog中的位置是 mysql-bin.000003, end_log_pos 440267874。

1)利用mysqlbinlog工具找出440267874的事件

/usr/local/mysql-5.6.30/bin/mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 |grep -A 20 '440267874'

或者/usr/local/mysql-5.6.30/bin/mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 --stop-position=440267874 | tail -20

或者usr/local/mysql-5.6.30/bin/mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 > decode.log 

( 或者加上参数-d, --database=name 来进一步过滤)

复制代码

#160923 20:01:27 server id 1223307  end_log_pos 440267874 CRC32 0x134b2cbc      Delete_rows: table id 319 flags: STMT_END_F
### DELETE FROM `db_99ducj`.`tbuservcbgolog`
### WHERE
###   @1=10561502 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1683955 /* INT meta=0 nullable=0 is_null=0 */
###   @3=90003 /* INT meta=0 nullable=0 is_null=0 */
###   @4=0 /* INT meta=0 nullable=0 is_null=0 */
###   @5='2016-09-23 17:02:24' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @6=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
# at 440267874

复制代码

 

以上为检索出来的结果,事务语句为:delete from db_99ducj.tbuservcbgolog where @1=10561502 and @2=1683955 ...

其中@1 @2 @3...分别对应表tbuservcbgolog的列名,填补上即可。

我们可以逆向此SQL 将deleter 变成Insert,手动在从库上执行此Insert SQL,之后restart slave就好了。

 

 

 

 

 

 

 


 

© 著作权归作者所有

共有 人打赏支持
mecisery
粉丝 1
博文 45
码字总数 24379
作品 0
海淀
linux系统如何为mysql设置主从服务器?

如何在linux环境下设置mysql主从服务器呢?设置mysql主从服务器,听上去很高大上的样子,其它实现起来也是不难的。一般设置主从服务器,主要是为了数据安全,即使主服务器出现问题,还可以从...

hero2019
07/06
0
0
MySQL主从延时复制

MySQL的主从复制是实现MySQL大规模集群的基础,其在日常生产环境中被广泛的被应用,而在MySQL5.6开始对MySQL的底层代码不断的重构完善后在MySQL的主从复制取得极大的进步,且在5.7版本引入主...

往事_Jim_遗
2017/11/25
0
0
mysql binlog日志自动清理及手动删除

说明: 当开启mysql数据库主从时,会产生大量如mysql-bin.00000* log的文件,这会大量耗费您的硬盘空间。 mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin....

jiangwu
2016/01/17
60
0
MySQL的主从复制介绍及配置

1.MySQL主从复制介绍 MySQL数据库支持单向、双向、链式级联、环状等不同业务场景的复制。在复制过程中,一台服务器充当主服务器(Master),接收来自用户的内容更新,而一个或多个其他的服务...

10pcm
06/26
0
0
mysql binlog日志自动清理及手动删除

说明: 当开启mysql数据库主从时,会产生大量如mysql-bin.00000* log的文件,这会大量耗费您的硬盘空间。 mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin....

langtu329
2016/04/08
22
0

没有更多内容

加载失败,请刷新页面

加载更多

kubeadm部署kubernetes集群

一、环境要求 这里使用RHEL7.5 master、etcd:192.168.10.101,主机名:master node1:192.168.10.103,主机名:node1 node2:192.168.10.104,主机名:node2 所有机子能基于主机名通信,编辑...

人在艹木中
40分钟前
2
0
Shell特殊符号总结以及cut,sort,wc,uniq,tee,tr,split命令

特殊符号总结一 * 任意个任意字符 ? 任意一个字符 # 注释字符 \ 脱义字符 | 管道符 # #号后的备注被忽略[root@centos01 ~]# ls a.txt # 备注 a.txt[root@centos01 ~]# a=1[root@centos01...

野雪球
今天
2
0
OSChina 周二乱弹 —— 程序员圣衣

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @达尔文:分享Skeeter Davis的单曲《The End of the World》 《The End of the World》- Skeeter Davis 手机党少年们想听歌,请使劲儿戳(这里...

小小编辑
今天
14
0
[ python import module ] 导入模块

import moudle_name ----> import module_name.py ---> import module_name.py文件路径 -----> sys.path (这里进行查找文件) # from app.web import Personimport app.web.Person as Pe......

_______-
昨天
5
0
Redis性能问题排查解决手册

一、性能相关的数据指标 通过Redis-cli命令行界面访问到Redis服务器,然后使用info命令获取所有与Redis服务相关的信息。通过这些信息来分析文章后面提到的一些性能指标。 nfo命令输出的数据可...

IT--小哥
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部