本文共 7622 字，大约阅读时间需要 25 分钟。
一、 实验环境部署
主服务器（MySQL-01） IP： 192.168.8.241 端口3306 ,操作系统：Centos6.5 64位 从服务器（MySQL-02） IP： 192.168.8.242 端口3306，操作系统：Centos6.5 64位 虚拟机配置：内存2G，硬盘28G，2块网卡（1块网卡也可以），注意复制虚拟机 时候选择生成不同的MAC地址，虚拟机生成之后，网卡的的名称会变为eth2、eth3，修改/etc/udev/rules.d/70-persistent-net.rules文件，将无效的MAC记录屏蔽掉，将真实的MAC对应的记录改名为eth0、eth1。 两台虚拟机的网络配置好，配置网关、配置DNS，将虚拟机网卡设置为物理桥接模式，使之能够访问外网，通过 ping www.baidu.com 测试是否能访问外网。 通过yum安装mysql数据库server/client/develop： yum -y install mysql-server mysql mysql-devel二、mysql单向主从同步配置I. 主服务器的操作 1. 关于主服务器的相关配置 1.1 设置server-id值并开启binlog参数 根据mysql的同步原理：关键因素就是binlog日志。 编辑/etc/my.cnf配置文件，修改和添加相关参数。 [root@localhost ~]# vi /etc/my.cnf [mysqld] server-id = 1 log-bin = mysql-bin 备注: #. 上面两参数放在my.cnf中的[mysqld]模块下,否则会出错; #. 要先在my.cnf文件中查找相关参数,并按具体要求修改,不存在时添加相关参数,切记,参数不能重复; #binlog-do-db = test ：需要备份数据，多个写多行，不写全部都备份 #binlog-ignore-db = mysql ：不需要备份的数据库，多个写多行 修改完配置文件，检查配置后的结果： [root@localhost ~]# grep -E "server-id|log-bin" /etc/my.cnf 重启mysql数据库 service mysqld restart 设置数据库根用户root的密码： mysqladmin -u root password 'mysql' 设置的密码是“mysql" 1.2 建立用于主、从数据同步的帐号 "rep" [root@localhost ~]#mysql -u root -pmysql mysql>select user(); mysql>grant replication slave on *.* to 'rep'@'192.168.8.%' identified by '123456';
mysql>flush privileges;
备注：
#replication slave：为mysql同步的必须权限，此处不要授权all #*.*：表示所有库所有表，库也是可以指定具体的库和表进行复制,如test.test1（test库的test1表）； # rep@192.168.8.%： rep为同步账号，192.168.1.%为授权主机，使用了%表示允许整个192.168.8.0网段以rep用户访问； #identified by "123456" ： 123456为密码，实际环境用复杂密码 1.3 对主数据库锁表只读: 注：实际环境中，操作主从复制，需要申请停机时间，锁表会影响业务。 mysql>flush tables with read lock; 注：这个锁表命令的时间，在不同引擎的情况，会受下面参数的控制，锁表超过设置时间不操作会自动解锁； 默认情况下的时长为： mysql>show variables like "%timeout%"; 可以查看到默认锁表时间最大值。 mysql> show variables like "%timeout%"; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ .......... | interactive_timeout | 28800 | ........ | wait_timeout | 28800 | +----------------------------+-------+ 10 rows in set (0.00 sec) 完成后测试是否锁表成功：打开另一窗口创建一test1表，是不会执行的，证明锁表不能更新，但可读，不可写，因为是read读锁，锁表主要是为了导出数据库文件，从而取得正确的偏移量的值，保证导入从数据库，数据一致。 1.4 查看主库状态 查看主库状态，即当前日志文件名和二进制日志偏移量 mysql>show master status; 命令显示的信息要记录在案，后面的从库复制时是从这个位置开始的。 mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 619 | | mysql | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 1.5 导出主数据库数据 [root@localhost ~]#mkdir /backup [root@localhost ~]#mysqldump -uroot -pmysql -A -B |gzip >/backup/mysql_bak.$(date +%F)sql.gz 注：-A表示备份所有库， -B表示增加user DB和drop等参数（导库时会直接覆盖所有的）。 [root@mysql-master backup]# ll total 144 -rw-r--r-- 1 root root 144884 Feb 23 08:58 mysql_bak.2016-02-23sql.gz 为了确保导库期间，数据库没有数据插入，可以再检查下主库状态信息 [root@mysql-master backup]# mysql -uroot -pmysql -e "show master status" +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 619 | | mysql | +------------------+----------+--------------+------------------+ 注：无特殊情况,binlog文件及位置点是保持不变的。 导库后，解锁主库，恢复可写； mysql>unlock tables; Query OK, 0 rows affected (0.00 sec) 特别提示，有读者这里犯迷糊，实际上做从库的，无论主库更新多少数据了，最后从库都会从上面show master status 的位置很快赶上主库的位置进度的。 1.6 把主库备份的mysql数据迁移到从库 [root@mysql-master backup]# scp /backup/mysql_bak.2016-02-23sql.gz 192.168.8.242:/backup/ II. 从服务器的操作 1. 关于从服务器的相关配置 1.1 设置server-id值并关闭binlog设置 注：数据库的server-id在LAN内是唯一的，这里的server-id要和主库及其他从库不同，并注释掉从库的binlog参数配置； 编辑/etc/my.cnf配置文件，修改相关的参数设置 master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差 [root@localhost ~]# vi /etc/my.cnf [mysqld] server-id = 2 #log-bin = mysql-bin 检查配置后的结果 [root@localhost ~]#grep -E "server-id|log-bin" /etc/my.cnf 设置数据库根用户root的密码： mysqladmin -u root password 'mysql' 设置的密码是“mysql" 重启从数据库 [root@localhost ~]#service mysqld restart 1.2 还原主库导出的数据到从库 gunzip mysql_bak.2016-02-23sql.gz mysql -uroot -pmysql <mysql_bak.2016-02-23sql 1.3 登录从库配置同步参数 mysql>change master to #连接主数据库 mysql>master_host="192.168.8.241", #主库的IP地址 mysql>master_port=3306, #主库的端口，从库的端口可以和主库不同 mysql>master_user="rep", #主库上建立的用于数据同步的用户《rep》 mysql>master_password="123456", #用户《rep》的密码 mysql>master_log_file="mysql-bin.000003", #是mysql>show master status时看到的二进制日志文件名称，不能多空格。 mysql>master_log_pos=619; #是mysql>show master status时查看到的二进制日志偏移量，不能多空格。 (change master to master_host="192.168.8.241",master_port=3306,master_user="rep",master_password="123456",master_log_file="mysql-bin.000003",master_log_pos=619; ) 1.4 启动从库同步开关 启动从库同步开关，并查看同步状态 [root@localhost backup]#mysql -uroot -pmysql -e "start slave" [root@localhost backup]#mysql -uroot -pmysql -e "show slave status\G" 也可以登录从库，在数据库下面执行相关命令： mysql>start slave; mysql>show slave status\G; 判断搭建是否成功就看如下IO和SQL两个线程是否显示为“yes”状态 Slave_to_Running：YES #负责从库去主库读取binlog日志，并写入从库中继日志中 Slave_SQL_Running：YES #负责读取并执行中继日志中的binlog转换sql语句后应用到数据库汇总。 也可以执行命令过滤查看如下： [root@mysql-slave tmp]# mysql -uroot -pmysql -e "show slave status\G" | egrep "IO_Running|SQL_Running" Slave_IO_Running: Yes Slave_SQL_Running: Yes 1.5 测试主从同步 在主库创建 —>数据库以及查看 在主库中创建库“mytable”用于主从同步： [root@localhost ]#mysql -uroot -pmysql -e "show databases;" [root@localhost ]#mysql -uroot -pmysql -e "create database mytable;" 在从库查看是否主从同步： [root@mysql-master ~]# mysql -uroot -pmysql -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mytable | | test | +--------------------+ [root@mysql-master ~]# [root@mysql-slave tmp]# mysql -uroot -pmysql -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mytable | | test | +--------------------+ [root@mysql-slave tmp]# 到此！单向主从数据库同步成功完成；从数据库可以实现数据同步。三、mysql双向主从同步的配置 3.1在MySQl-01上操作，用户授权，略过，是因为在做单向主从的时候已经对192.168.8.0/24整个网段都进行了授权。如果单向主从关系创建复制用户“rep”的时候只制定了单独地址192.168.8.242，那么就需要增加对地址192.168.8.241的授权。执行下面的命令： mysql -u root -pmysql mysql> grant replication slave on *.* to 'rep'@'192.168.8.241' identified by '123456'; //建立权限 mysql> flush privileges; 注：因为做的是所有库的主从，所以在MySQL-01上的操作会同步到MySQL-02上 3.2在MySQL-02上的操作 修改MySQL-02的数据库文件,在[mysql]下修改如下内容 server_id = 2 log_bin = mysql-bin 重启 MySQL-02的mysql数据库： service mysqld restart 查看MySQL-02上的日志文件： mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 3.3在MySQL-01上操作 mysql> change master to master_host='192.168.8.242',master_user='rep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106; mysql> start slave; 检查是否成功 mysql>show slave status\G； Slave_IO_Running: Yes Slave_SQL_Running: Yes 表示成功。 至此，mysql的双向主从同步已完成附：mysql主从同步常见异常及恢复方法1. 一般的异常只需要跳过一步即可恢复 >slave stop; >SET GLOBAL sql_slave_skip_counter = 1; >slave start; 2.断电导致主从不能同步时，通主库的最后一个bin-log日志进行恢复 在主库服务器上，mysqlbinlog mysql-bin.xxxx > binxxxx.txt tail -n 100000 binxxxx.txt > tail-binxxxx.txt vim tail-binxxxx.txt 打开tail-binxxxx.txt文件找到最后一个postion值 然后在从库上，change host to 相应正确的值 >slave stop; >change master to master_host='ip', master_user='username', master_password='password', master_log_file='mysql-bin.xxxx', master_log_pos=xxxx; >slave start; >show slave status\G; 3.主键冲突、表已存在等错误代码如1062,1032,1060等，可以在mysql主配置文件指定 略过此类异常并继续下条sql同步，这样也可以避免很多主从同步的异常中断 [mysqld] slave-skip-errors = 1062,1032,1060
本文转自yuweibing51CTO博客，原文链接：http://blog.51cto.com/yuweibing/1744390 ，如需转载请自行联系原作者