首页 > 代码库 > mysql主从同步的配置

mysql主从同步的配置


1. mysql主从复制的介绍

1.1 为什么使用主从复制

MySQL支持单双向,链式级联,异步复制。mysql 在插入数据过程中,会锁表,此时,就无法读数据。设置好主从同步之后,主数据库用来对数据进行增,删,改,从数据库只用来查数据。生产环境下,一般会忽略授权表的同步,对从服务器的用户授权select的权限,同时在my.cnf中加入read-only的参数,确保从库只读。

1.2 MySQL主从复制的优点:

有利于数据库的健壮性,访问速度和系统维护管理。

主从服务器架构的设置,可以大大的强加数据库架构的健壮性,当主服务器出现问题时,我们可以切换到从服务器继续提供服务

主从服务器架构可以对用户的请求实现读写分离,即通过在从服务器上处理用户select查询请求,降低查询相应时间,及读写给主服务器带来的压力,对于更新的数据(update,insert,delete)仍然交给主服务器处理,确保主服务器和从服务器保持时间同步

如果网站是以非更新为主的业务,如bog,www首页展示等业务,读请求比较多,这时从服务器的负载均衡策略就很有效了,这就是传说中的读写分离架构

可以把几个不同的从服务器,根据公司的业务进行拆分,有为外部用户提供查询服务的从服务器,有用来备份的从服务器,还有提供公司内部后台、脚本、日志分析及开发人员服务的从服务器,这样除了减轻主服务器的压力外,使得对外用户浏览,对内处理公司内部用户业务,及DBA备份业务互不影响

 

1.3MySQL主从复制的原理

1) MySQL的主从复制是一个异步同步的过程,数据库从一个Mysql数据库(主数据库master)复制到另一个mysql数据库(从数据库slave)。在master和slave之间实现整个主从复制的过程有三个线程参与完成。其中两个在slave端(IO线程和SQL线程),另一个在master端(IO线程)

2) 要实现主从复制,必须打开master端的binlog功能,主从复制实际上就是slave从master获取binlog日志,然后再在slave自身上用相同顺序执行binlog中记录的各种操作

 

1.4MySQL主从复制的过程

1)slave服务器上执行start slave ,开启主从复制开关

2)此时,slave服务器的IO线程会通过在master上授权的用户请求连接master服务器,并请求从指定binlog日志文件的指定位置后的binlog日志内容

3)master服务器接收到slave的IO线程的请求后,master服务器上负责复制的IO线程会根据slave服务器IO线程的请求信息读取指定binlog日志文件指定位置之后的binlog日志信息,然后返回给slave端的IO线程,同时还返回在master端新的binlog文件名及binlog的指定位置

4)当slave服务器IO线程收到master服务器IO线程发来的日志文件和新的位置点后,会将binlog日志内容写入到自身的relaylog(中继日志)文件的最末端,并将新的binlog文件名和位置点记录到maste-info中,以便下次同步使用

5)slave服务器的SQL线程会实时检测本地relaylog中增加了的日志内容,然后及时把log文件中的内容解析成曾经在master端执行过的SQL语句内容,并在自身slave服务器上按语句顺序依次执行,那么主从同步就已经实现

2.主从同步的实现

2.1环境:

mysql版本:mysql-5.1.56

主(master):192.168.254.253

从(slave):192.168.254.251

2.2主库上的操作

1)删除空用户,空密码和test数据库

[root@Mysql-master ~]# mysql-uroot -p‘jeck123‘
mysql> drop user‘‘@‘linux-jie‘;
mysql> drop database test;

技术分享

2)在主配置文件my.cnf修改server-id,并开启binlog功能

[root@Mysql-master ~]#vim  /etc/my.cnf 
[mysqld]
server-id       = 10
log-bin=mysql-bin
[root@Mysql-master ~]# egrep"server-id|log-bin" /etc/my.cnf 
log-bin=mysql-bin
server-id       = 10
[root@Mysql-master ~]# service mysqld reload
 SUCCESS! Reloadingservice MySQL
 
 
注意:server-id是任意设置的,但是在局域网内唯一。改完之后,需要重启mysql.

 

3)建立用于同步的账户rep

[root@Mysql-master ~]# mysql-uroot -p‘jeck123‘
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.56-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. Allrights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is freesoftware,
and you are welcome to modify and redistribute it under the GPLv2 license
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the currentinput statement.
mysql> grant replicationslave on *.* to rep@‘192.168.254.%‘ identified by ‘rep123‘;   #建立rep用户,授权replication slave权限,并设置只能在192.168.254.0/24网段访问
Query OK, 0 rows affected(0.00 sec)
mysql> show grants for rep@‘192.168.254.%‘;        #查看刚才创建的用户及权限
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.254.%                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO ‘rep‘@‘192.168.254.%‘IDENTIFIED BY PASSWORD ‘*08F5BB4670F148DB0376A1EE646F0C67EAE138CE‘ |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
注意:用于rep的权限为replication slave ,不能授权all。
‘192.168.254.%‘表示192.168.254.0网段

4)创建用于测试的数据库,并锁表

mysql> create databasereptest;   #创建测试数据库
Query OK, 1 row affected(0.04 sec)
mysql> show databases ;      #查看创建结果
+--------------------+
|Database           |
+--------------------+
|information_schema |
|jeck               |
|mysql              |
|reptest            |
|test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> flush tables withread lock;         #锁表,防止在备份数据库时产生新的数据
Query OK, 0 rows affected(0.00 sec)
提示:锁表之后,数据库将无法写入数据,所以生产环境中,配置主从同步需要申请停机时间

附:不同的引擎锁表时间不同,一般受下面两个参数影响,超过锁表时间,将自动解锁

interactive_timeout = 60

wait_timeout = 60

技术分享

5)查看主库的状态,并把主库所有数据库导出

[root@Mysql-master ~]# mysql-uroot -p‘jeck123‘ -e "show master status ;"
+------------------+----------+--------------+------------------+
| File             |Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 |     438 |              |                  |
+------------------+----------+--------------+------------------+
[root@Mysql-master ~]# mkdir/repback
[root@Mysql-master ~]#mysqldump -uroot -pjeck123 -A -B | gzip >/repback/rep.sql.gz
[root@Mysql-master ~]# ls/repback/
rep.sql
注意:若数据库比较大,超过了100G,导出需要的时间会很长,若允许停机,可以使用tar打包备份。总之备份之间,不能有数据写入

6)解锁主库,恢复可写

[root@Mysql-master ~]# mysql-uroot -p‘jeck123‘ -e "unlock tables ;"

2.3从库上的操作

1)设置server-id,关闭binlog功能(若从库需要增量备份,则不需要关闭)

[root@Mysql-slave ~]#vim  /etc/my.cnf 
[mysqld]
server-id       = 20
#log-bin=mysql-bin
[root@Mysql-slave ~]# egrep"server-id|log-bin" /etc/my.cnf 
#log-bin=mysql-bin
server-id       = 20
[root@Mysql-slave ~]# service mysqld reload
 SUCCESS! Reloadingservice MySQL

 

2)还原主库导出的数据库到从库

[root@Mysql-slave ~]# scp 192.168.254.10:/repback/rep.sql.gz  /root/
[root@Mysql-slave ~]# ls rep.sql.gz 
rep.sql.gz
[root@Mysql-slave ~]# gzip -d rep.sql.gz 
[root@Mysql-slave ~]# mysql -uroot -pjeck321 < rep.sql 
[root@Mysql-slave ~]# service mysqld reload
 SUCCESS! Reloadingservice MySQL

 

3)登录从库配置同步参数

mysql> change master to
    ->master_host=‘192.168.254.10‘,
    -> master_port=3306,
    -> master_user=‘rep‘,
    ->master_password=‘rep123‘,
    -> master_log_file=‘mysql-bin.000015‘,
-> master_log_pos=438;
参数解释:
   master_host=’192.168.254.10’,   指定master主服务器
    master_port=3306,           指定同步的端口
    master_user=rep,                指定用户
    master_password=‘repj123‘,         用户的密码
    master_log_file=‘mysql-bin.000015‘,    指定开始同步的biblog文件
    master_log_pos=438;             指定日志的偏移量(该数据是在主库上执行show master status的结果)
注意:master_log_file和master_log_pos必须是主库上之前查看的位置点,不然同步过程中数据会丢失

 

快捷操作:可以直接改对应的参数,然后复制全部,直接执

cat | mysql  -uroot  -pjeck123 <<EOF
                   changemaster to
        master_host=‘192.168.254.10‘,
        master_port=3306,
        master_user=‘rep‘,
        master_password=‘rep123‘,
        master_log_file=‘mysql-bin.000015‘,
        master_log_pos=438;
EOF

4)开启从库同步开关。检查同步状态

mysql>start slave ;       #开启slave同步
QueryOK, 0 rows affected (0.00 sec)
mysql>show slave status\G         #查看同步状态
***************************1. row ***************************
               Slave_IO_State: Waiting formaster to send event
                  Master_Host: 192.168.254.10
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 438
               Relay_Log_File:Mysql-slave-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes        #yes表示IO线程启动成功
            Slave_SQL_Running: Yes          #yes表示SQL线程启动成功
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 438
              Relay_Log_Space: 412
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0         #同步延时为0
Master_SSL_Verify_Server_Cert:No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1row in set (0.00 sec)
提示:当出现IO线程运行状态为yes,SQL线程运行状态为yes,同步延时为0,表示同步没问题。

5)验证:

在reptest库中创建一个user表,并插入两行数据

mysql>use reptest;
Databasechanged
mysql>create table user (name char(10), password varchar(48) ) ;
QueryOK, 0 rows affected (0.02 sec)
mysql>insert into user (name , password) values ( ‘zhangsan‘ ,‘zs123‘);
QueryOK, 1 row affected (0.00 sec)
mysql>insert into user (name , password) values ( ‘lisi‘ ,‘ls123‘);          
QueryOK, 1 row affected (0.00 sec)
mysql>select * from user;
+----------+----------+
|name     | password |
+----------+----------+
|zhangsan | zs123    |
|lisi     | ls123    |
+----------+----------+
2rows in set (0.00 sec)
在从库中查看是否有相应的数据
[root@Mysql-slave~]# mysql -uroot -pjeck123 -e "select * from reptest.user"
+----------+----------+
|name     | password |
+----------+----------+
|zhangsan | zs123    |
|lisi     | ls123    |
+----------+----------+
发现在主库上创建的表已经同步到从库上

3. 生产环境下,配置主从同步注意事项

1)第一次做从库时候,如果主库已经上线,而且在跑业务,此时需要申请停机维护时间,一般是凌晨。

2)不加班。在工作时间从容配置从库

从库的配置核心就是主库上导出的数据库的完整备份和当时的binlog,我们可以在每天数据库备份的时候,顺便记录一下备份时候的binlog点,那么我们就可以在后期直接配置从库了

3)忽略权限库同步的参数

binlog-ignore-db =information_schema

binlog-ignore-db =mysql

4)主从复制故障解决一例

show slave status 报错 error xxx doesn’t exist

且Slave_IO_Running: Yes

Slave_SQL_Running: NO

Seconds_Behind_Master: NULL

            解决方法:

                stop slave

                 set globalsql_slave_skip_counter =1 ;

                start slave ;

set globalsql_slave_skip_counter =N  取值>0表示忽略N个更新

 


本文出自 “Study-Everyday” 博客,请务必保留此出处http://studys.blog.51cto.com/9736817/1604034

mysql主从同步的配置