mysql数据库的主从同步过程详述

1复制准备主库(mysql master):  ip为192.168.1.5   port为3306从库(mysql slave):   ip为192.168.1.4  port为3306数据库环境准备,具备两台服务器每个机器一个数据库的环境.2.主库上执行操作2.1设置server-id值并开启binlog参数根据mysql的同步原理,我们知道复制的关键因素就是binlog日志.执行vi   /etc/my.cnf编辑my.cnf配置文件,按如下两个参数内容修改:[mysqld]server-id = 1log-bin = mysql-bi提示:1.上面两参数放在my.cnf中的[mysqld]模块下,否则会出错;2.要先在my.cnf文件中查找相关参数,并按要求修改,不存在时在添加参数,切记,参数不能重复;3.修改my.cnf配置后需要重启数据库命令为:/etc/init.d/mysql  restart，注意确认真正重启了(此处数据库为源码包安装,在后面我将贴出mysql的启动脚本)。检查配置后的结果(这是一个好的习惯):grep  -E  "server-id|log-bin" /etc/my.cnf   和egrep一样的作用 /etc/init.d/mysql  restart2.2建立用于同步的账号rep登陆mysql 3306实例主数据库mysql  -uroot  -p'123123'  -S  /tmp/mysql.sockmysql>select   user();    查看用户mysql>grant  replication  slave  on  *.*  to  'rep'@'192.168.1.%'  identified  by  "123456";  建立用于库复制的账号rep#replication  slave为mysql同步的必须权限，此处不要授权all#*.*表示所有库所有表，库也是可以指定具体的库和表进行复制,如test.test1（test库的test1表）；#'rep'@'192.168.1.%' rep为同步账号，192.168.1.%为授权主机，使用了%表示允许整个192.168.1.0网段以rep用户访问；#identified  by  "123456" , 123456为密码，实际环境时复杂一点为好。再次检查创建的rep账号select  user.host  from  mysql.user;也可以查看用户权限   mysql>show  grants  for  rep@'192.168.1.%';2.3对数据库锁表只读（当前窗口不要关闭）生产环境时，操作主从复制，需要申请停机时间，锁表会影响业务。mysql>flush  tables  with  read  lock;提示，这个锁表命令的时间，在不同引擎的情况，会受下面参数的控制，锁表时，如果超过设置时间不操作会自动解锁；interactive_timeout = 60wait_timeout = 60默认情况下的时长为：mysql>show  variables  like  "%timeout%";  可以查看到默认值很大完成后测试下是否锁表，打开另一窗口创建一test1表，是不会执行的，证明锁表不能更新，但可读，不可写，因为是read读锁，锁表主要是为了导出数据库文件，从而取得正确的偏移量的值，保证导入从数据库，数据一致。2.4查看主库状态查看主库状态，即当前日志文件名和二进制日志偏移量show  master  status;命令显示的信息要记录在案，后面的从库复制时是从这个位置开始的。2.5导出数据库数据单开新窗口，导出数据库数据，如果数据库量很大（100G+）,并且允许停机可以，可以直接停库打包数据文件迁移。mkdir  /server/backup/  -pmysqldump  -uroot  -p"123123"  -S  /tmp/mysql.sock  -A  -B  | gzip  > /server/backup/mysql_bak.$(date +%F).sql.gz#注意，-A表示备份所有库， -B表示增加user  DB和drop等参数（导库时会直接覆盖所有的）。ls  -l  /server/backup/mysql_bak.$(date  +%F).sql.gz为了确保导库期间，数据库没有数据插入，可以再检查下主库状态信息mysql  -uroot  -p"123123"  -S  /tmp/mysql.sock  -e  "show  master  status"提示，无特殊情况,binlog文件及位置点是保持不变的。导库后，解锁主库，恢复可写；mysql>unlock  tables;特别提示，有读者这里犯迷糊，实际上做从库的，无论主库更新多少数据了，最后从库都会从上面show master  status  的位置很快赶上主库的位置进度的。2.6把主库备份的mysql数据迁移到从库这步常用命令有scp,rsync等。ls  -l  /server/backup/mysql_bak.$(date  +%F).sql.gz

3从库上执行操作

3.1 设置server-id值并关闭binlog设置

数据库的server-id一般在LAN内是唯一的，这里的server-id要和主库及其他从库不同，并注释掉从库的binlog参数配置；

执行vi  /etc/my.cnf  编辑my.cnf配置文件，按如下两个参数内容修改；

[mysqld]

server-id = 2

#log-bin = mysql-bin

检查配置后的结果

grep  -E  "server-id|log-bin"  /etc/my.cnf

#log-bin = mysql-bin   log-bin后面也可以不带等号内容，mysql会使用默认日志。

server-id  = 2

重启从数据库

/etc/init.d/mysql  restart

3.2还原主库导出的数据到从库

cd  /server/backup/ &&  ls -l

然后解压刚才备份的

gzip  -d  mysql_bak.2012-05-22.sql

恢复命令

mysql  -uroot  -p"123123"  <  mysql_bak.2012-05-22.sql

3.2登陆从库配置同步参数

mysql  -uroot  -p"123123"

CHANGE  MASTER  TO   连接主数据库

MASTER_HOST="192.168.1.5",    这里是主库的ip

MASTER_PORT=3306,   这里是主库的端口，从库的端口可以和主库不同

MASTER_USER="rep",   这里是主库上建立的用于复制的用户rep

MASTER_PASSWORD="123456",   这里是rep的密码

MASTER_LOG_FILE="mysql-bin.000004",  这里是show  master  status时查看到的二进制日志文件名称，注意不能多空格

MASTER_LOG_POS=1273;   这里是show  master  status时查看到的二进制日志偏移量，注意不能多空格。

不登陆数据库，在命令行快速执行change  master的语句（适合在脚本中批量建slave库用）

本文即用此法来操作

cat  | mysql  -uroot -p"oldboy"  -S  /data/3307/mysql.sock<

CHANGE  MASTER  TO

MASTER_HOST="192.168.1.5",

MASTER_PORT=3306,

MASTER_USER="rep",

MASTER_PASSWORD="123456",

MASTER_LOG_FILE="mysql-bin.000004",

MASTER_LOG_POS=1273;

EOF

3.2启动从库同步开关

启动从库同步开关，并查看同步状态

mysql  -uroot  -p"123123"  -S  /tmp/mysql.sock  -e  "start  slave;"

mysql  -uroot  -p"123123"  -e  "show  slave  status\G;"

也可登陆数据库里面执行下面两个命令:

start   slave

show  slave  status\G;

判断搭建是否成功就看如下IO和SQL两个线程是否显示为yes状态

Slave_to_Running: Yes  #负责从库去主库读取binlog日志,并写入从库中继日志中

Slave_SQL_Running: Yes   #负责读取并执行中继日志中的binlog,转换sql语句后应用到数据库汇总

也可以执行命令过滤查看如下

mysql  -uroot  -p"123123"  -S  /tmp/mysql.sock  -e "show  slave  status\G;" | egrep "IO_Running|SQL_Running"

3.3测试复制结果

主库创建一数据库,看从库是否有.

mysql  -uroot -p"123123"  -S /tmp/mysql.sock  -e "create  database  diablo4;"

mysql  -uroot -p"123123"  -e  "show  databases  like  'diablo4';"

如下图主库创建库

从库如下图示:

请关注mysql数据库主从同步过程详述(三) 

在此说明下:在最后试验过程中,当查看从库状态的时候,IO_Running显示为no,从error_log中看到如下报错提示:120523  0:55:31 [Note] Slave I/O thread: connected to master 'rep@192.168.1.5:3306', replication started in log ' mysql-bin.000004' at position 1273120523  0:55:31 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)120523  0:55:31 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log错误代码为1236我的解决方法为:重新检查授权,确认无误,重新按前面步骤记录偏移量,二进制文件,停止从数据库,然后重新在从数据库中CHANGE  MASTER TO 开始,指向正确的二进制文件及偏移量.如下图:下面我将贴出mysql的启动脚本(仅作参考)

#!/bin/bash

mysql_user="root"

mysql_pwd="123123"

CmdPath="/usr/local/mysql/bin"

function_start()

{

        printf "Starting MySQL...\n"

        /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/etc/my.cnf >/dev/null 2>&1 &

}

function_stop()

{

        printf "Stoping MySQL...\n"

        ${CmdPath}/mysqladmin -u${mysql_user} -p${mysql_pwd} -S /tmp/mysql.sock shutdown >/dev/null

}

function_restart()

{

        printf "Restarting MySQL...\n"

        function_stop

        sleep 2

        function_start

}

case $1 in

        start)

                function_start

        ;;

        stop)

                function_stop

        ;;

        restart)

                function_restart

        ;;

        *)

                printf "Usage: $0 {start|stop|restart}\n"

esac

生产环境在工作时间轻松配置从库在定时任务备份时,每天的夜里服务里压力小时侯的定时备份时做一些措施即可,如1.锁表备份全备一份;2.锁表前后取得show  master  status值记录日志里.这样可以在白天从容的实现主从同步了,如下面脚本:#!/bin/bashMYUSER=rootMYPASS="123123"MYSOCK=/tmp/mysql.sock MAIN_PATH=/server/backupDATA_PATH=/server/backupLOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.logDATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz MYSQL_PATH=/usr/local/mysql/binMYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -A -B --flush-logs --single-transaction -e" $MYSQL_CMD -e "flush tables with read lock;"echo "---------show master status result---------" >> $LOG_FILE$MYSQL_CMD -e "show master status;" >> $LOG_FILE${MYSQL_DUMP} | gzip > $DATA_FILE$MYSQL_CMD -e "unlock tables;"mail -s "mysql slave log" [e-mail] < $LOG_FILE5.相关mysql技术技巧概览5.1配置忽略权限库同步参数binlog-ignore"db"information_schemabinlog_ignore"db"mysql5.2主从复制故障解决show  slave  status报错:Error  xxx  don't  exist且show  slave  status\G;Slave_IO_Running: YesSlave_SQL_Running : NoSeconds_Behind_Master: NULL解决方法:stop  slave;set  global  sql_slave_skip_counter=1;start  slave;这样slave就会和master去同步,主要看点:secon是否为0   #  0表示已经同步状态提示:  set  global  sql_slave_skip_counter=n;  # n取值 >0忽略执行N个更新5.3让mysql  slave记录binlog方法在从库的my.cnf中加入如下参数log-slave=updateslog-bin=mysql3306-binexpize_logs_days = 7应用场景：级联复制或从库做数据备份5.4严格设置从库只读read-only的妙用5.5生产环境如何确保从库只读?1)mysql从服务器中加入read-only参数或者在从服务器启动时加该参数;2)忽略mysql库及information_schema库同步;3)授权从库用户时仅授权select权限.

由于各种原因，mysql主从架构经常会出现数据不一致的情况出现，大致归结为如下几类

1:备库写数据  

2:执行non-deterministic query   

3:回滚掺杂事务表和非事务表的事务

4:binlog或者relay log数据损坏

 

数据不同步给应用带来的危害是致命的，当出现主从数据不一致的情况，常见的应对方法是先把从库下线，然后找个半夜三更的时间把应用停掉，重新执行同步，如果数据库的体积十分庞大，那工作量可想而知，会让人崩溃。本文介绍使用percona-toolkit工具对mysql主从数据库的同步状态进行检查和重新同步。

 

一：安装percona-toolkit

1 2 3 4 5 6 7 8

# yum -y  install perl-Time-HiRes # wget  # tar -zxvpf percona-toolkit-2.2.13.tar.gz  # cd percona-toolkit-2.2.13 # perl Makefile.PL  # make  # make install

二：修改mysql 的binlog格式binlog_format参数为row格式 

mysql binlog日志有三种格式，分别为Statement, Mixed,以及ROW！

1.Statement：

每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).

 

2.Row

不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

 

3.Mixed

是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

 

主从数据库分别修改my.cnf文件相关配置项如下：

1	binlog_format=ROW

三：使用pt-table-checksum工具检查数据一致性情况

用法参考：

假设192.168.1.205是主库，192.168.1.207是它的从库，端口在3306。

1. 先校验

1 2 3 4 5

#  pt-table-checksum --user=root --password=123456 \   --host=192.168.1.205 --port=3306 \   --databases=test  --tables=t2  --recursion-method=processlist \   --no-check-binlog-format  --nocheck-replication-filters \   --replicate=test.checksums

2. 根据校验结果，只修复192.168.1.207从库与主库不一致的地方：

1 2	# pt-table-sync  --execute  --replicate \ test.checksums  --sync-to-master h=192.168.1.207,P=3306,u=root,p=123456

3. 修复后，再重新校验一次。执行第一步的语句即可。 

4. 检查修复结果： 登陆到192.168.1.207，执行如下sql语句返回若为空，则说明修复成功：

1 2 3 4 5 6 7 8

SELECT * FROM test.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)

各参数含义

--nocheck-replication-filters:不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。

--no-check-binlog-format:不检查复制的binlog模式，要是binlog模式是ROW，则会报错。

--replicate-check-only:只显示不同步的信息。

--replicate=:把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。 

--databases=:指定需要被检查的数据库，多个则用逗号隔开。

--tables=：指定需要被检查的表，多个用逗号隔开

h=127.0.0.1:Master的地址

u=root:用户名

p=123456:密码

P=3306:端口

下面我们来模拟下主从数据库不同步情况下的pt-table-checksum，为了方面，这里我们采用test schema

 

1: 主库上建表，插入测试数据

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

mysql> create table t2 (id int primary key,name varchar(100) not null,salary int); mysql> CREATE PROCEDURE test_insert ()        BEGIN        DECLARE i INT DEFAULT 0;        WHILE i<10000        DO        INSERT INTO t2       VALUES       (i,CONCAT('员工',i), i);       SET i=i+1;       END WHILE ;       END;; mysql> CALL test_insert();

从库上校验当前数据的同步情况为正常。

从库上删除一半的数据

1 2 3 4 5 6 7 8 9 10

mysql> delete from t2 where id > 5000; Query OK, 4999 rows affected (0.14 sec)    mysql> select count(*) from t2; +----------+ | count(*) | +----------+ |     5001 | +----------+ 1 row in set (0.01 sec)

2：使用pt-table-checksum工具进行校验：

1 2 3 4 5

#  pt-table-checksum --user=root --password=123456 \   --host=192.168.1.205 --port=3306 \   --databases=test  --tables=t2  --recursion-method=processlist \   --no-check-binlog-format  --nocheck-replication-filters \   --replicate=test.checksums

3：登陆从库进行查询checksum表

1 2 3 4 5 6 7 8

mysql> SELECT * FROM test.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)

4：使用pt-table-sync工具进行数据重新同步

1 2	# pt-table-sync  --execute  --replicate \  test.checksums  --sync-to-master h=192.168.1.207,P=3306,u=root,p=123456

5：从库上验证数据，中文“员工”变成了“??”

检查主库，发现出现一样的情况，中文“员工”变成了“??”，猜想和字符集设置相关。

于是检查数据库字符集设置，发现test库字符集非utf8

主从库my.cnf文件添加如下配置项后重启数据库实例

1 2	character_set_client=utf8 character_set_server=utf8

重新执行以上1-4步，发现一切正常！关键第4步要加--charset=utf8 参数

1 2 3

# pt-table-sync  --execute  --replicate \  test.checksums   --charset=utf8 \  --sync-to-master h=192.168.1.207,P=3306,u=root,p=123456