mysql主从设置
博客专区 > mecisery 的博客 > 博客详情
mysql主从设置
mecisery 发表于2个月前
mysql主从设置
  • 发表于 2个月前
  • 阅读 9
  • 收藏 1
  • 点赞 0
  • 评论 0

腾讯云 十分钟定制你的第一个小程序>>>   

参考: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就好了。

 

 

 

 

 

 

 


 

标签: MySQL
共有 人打赏支持
粉丝 2
博文 24
码字总数 21526
×
mecisery
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: