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.gz3从库上执行操作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 restart3.2还原主库导出的数据到从库cd /server/backup/ && ls -l然后解压刚才备份的gzip -d mysql_bak.2012-05-22.sql恢复命令mysql -uroot -p"123123" mysql_bak.2012-05-22.sql3.2登陆从库配置同步参数mysql -uroot -p"123123"CHANGE MASTER TO 连接主数据库MASTER_HOST="192.168.1.5", 这里是主库的ipMASTER_PORT=3306, 这里是主库的端口,从库的端口可以和主库不同MASTER_USER="rep", 这里是主库上建立的用于复制的用户repMASTER_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.sockCHANGE MASTER TOMASTER_HOST="192.168.1.5",MASTER_PORT=3306,MASTER_USER="rep",MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000004",MASTER_LOG_POS=1273;EOF3.2启动从库同步开关启动从库同步开关,并查看同步状态mysql -uroot -p"123123" -S /tmp/mysql.sock -e "start slave;"mysql -uroot -p"123123" -e "show slave status\G;"也可登陆数据库里面执行下面两个命令:start slaveshow 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/bashmysql_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" 123456@163.com 5.相关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-toolkit12345678# yum -y install perl-Time-HiRes# wget http://www.percona.com/downloads/percona-toolkit/2.2.13/tarball/percona-toolkit-2.2.13.tar.gz# 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文件相关配置项如下:1binlog_format=ROW三:使用pt-table-checksum工具检查数据一致性情况用法参考:假设192.168.1.205是主库,192.168.1.207是它的从库,端口在3306。1. 先校验12345# 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.checksums2. 根据校验结果,只修复192.168.1.207从库与主库不一致的地方:12# pt-table-sync --execute --replicate \test.checksums --sync-to-master h=192.168.1.207,P=3306,u=root,p=1234563. 修复后,再重新校验一次。执行第一步的语句即可。 4. 检查修复结果: 登陆到192.168.1.207,执行如下sql语句返回若为空,则说明修复成功:12345678SELECT*FROMtest.checksumsWHEREmaster_cnt this_cntOR master_crc this_crcOR 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: 主库上建表,插入测试数据12345678910111213mysql> 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 DO INSERT INTO t2 VALUES (i,CONCAT('员工',i), i); SET i=i+1; END WHILE ; END;;mysql> CALL test_insert();从库上校验当前数据的同步情况为正常。从库上删除一半的数据12345678910mysql> 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工具进行校验:12345# 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.checksums3:登陆从库进行查询checksum表12345678mysql> SELECT*FROMtest.checksumsWHEREmaster_cnt this_cntOR master_crc this_crcOR ISNULL(master_crc) ISNULL(this_crc)4:使用pt-table-sync工具进行数据重新同步12# pt-table-sync --execute --replicate \ test.checksums --sync-to-master h=192.168.1.207,P=3306,u=root,p=1234565:从库上验证数据,中文“员工”变成了“??”检查主库,发现出现一样的情况,中文“员工”变成了“??”,猜想和字符集设置相关。于是检查数据库字符集设置,发现test库字符集非utf8主从库my.cnf文件添加如下配置项后重启数据库实例12character_set_client=utf8character_set_server=utf8重新执行以上1-4步,发现一切正常!关键第4步要加--charset=utf8 参数123 # pt-table-sync --execute --replicate \ test.checksums --charset=utf8 \ --sync-to-master h=192.168.1.207,P=3306,u=root,p=123456