mysql主从同步的配置

34r2312

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数据库

1 2 3

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

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

1 2 3 4 5 6 7 8 9 10 11 12

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

[iyunv@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）创建用于测试的数据库，并锁表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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）查看主库的状态，并把主库所有数据库导出

1 2 3 4 5 6 7 8 9 10 11

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

6）解锁主库，恢复可写

1	[iyunv@Mysql-master ~]# mysql-uroot -p'jeck123' -e "unlock tables ;"

2.3从库上的操作

1）设置server-id，关闭binlog功能（若从库需要增量备份，则不需要关闭）

1 2 3 4 5 6 7 8 9

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

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

1 2 3 4 5 6 7

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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必须是主库上之前查看的位置点，不然同步过程中数据会丢失

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

1 2 3 4 5 6 7 8 9

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）开启从库同步开关。检查同步状态

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

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表，并插入两行数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

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) 在从库中查看是否有相应的数据 [iyunv@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个更新