mysql复制主从集群搭建

最近搭了个主从复制，中间出了点小问题，排查搞定，记录下来


1
环境：
虚拟机：
OS:
centos6.5
Linux host2 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux


服务器IP
192.168.18.66
192.168.18.67


DB：
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.20    |
+-----------+


2
主机：192.168.18.66
从机：192.168.18.67


3
修改主服务器配置,添加如下内容：
  server-id=10
  log-bin=mysql-bin
  binlog-ignore-db=mysql
  binlog-ignore-db=information_schema
  binlog-ignore-db=performance_schema
  replicate-do-db=reptest


此时主服务器这个配置文件/etc/my.cnf内容如下：
[client]
#password = system
#port = 3306
default-character-set=utf8


[mysqld]


server-id=10
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=reptest


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 


port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500


[mysql]
default-character-set=utf8


4
修改从服务器配置
   server-id=20
   relay_log=mysql-relay-bin
   read_only


此时从服务器配置文件内容如下：
[client]
#password=system
#port=3306
default-character-set=utf8


[mysqld]


server-id=20
relay_log=mysql-relay-bin
#read_only
#log_slave_updates=1


#master-host=192.168.18.66
#master-user=repl
#master-password=123
#master-port=3306
#master-connect-retry=60
#replicate_do_db=reptest
#replicate_ignore_db=mysql,information_schema,performance_schema


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500


[mysql]
default-character-set=utf8


mysql复制的相关参数可参考下面的网页：
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html




5
在主上增加复制用户
mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
flush privileges;


192.168.18.67是从服务器，就通过repl用户密码为空来同步复制


mysql> select host,user,Repl_slave_priv from mysql.user where user='repl';
+---------------+------+-----------------+
| host          | user | Repl_slave_priv |
+---------------+------+-----------------+
| 192.168.18.67 | repl | Y               |
+---------------+------+-----------------+
1 row in set (0.00 sec)


6
重启主从服务器：
停主，停从
mysqladmin -uroot shutdown -psystem
起从，起主
/etc/init.d/mysql start


[root@host3 ~]# /etc/init.d/mysql start
Starting MySQL.. SUCCESS! 


7
导出主数据库数据，取快照
1）锁主库
 flush tables with read lock;


2)
这一步比较重要，要记住File和Position值，在起从服务器上的slave线程时备用
mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 401
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set: 
1 row in set (0.00 sec)


3)
[root@host3 ~]# mysqldump -uroot -p  reptest --triggers --routines --events > /home/zxw/master_reptest.sql


顺便看一下，mysqldump的内容如下：
[root@host3 ~]# ll /home/zxw/
total 4
-rw-r--r--. 1 root root 1910 Aug 25 13:50 master_reptest.sql
[root@host3 ~]# nl /home/zxw/master_reptest.sql 
     1  -- MySQL dump 10.13  Distrib 5.6.20, for Linux (x86_64)
     2  --
     3  -- Host: localhost    Database: reptest
     4  -- ------------------------------------------------------
     5  -- Server version       5.6.20-log
       
     6  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
     7  /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
     8  /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;