centos6.6中mysql5.6主从复制

• 环境
  操作系统：centos6.6
  master：192.168.0.230
  slave：192.168.0.231
  mysql版本：mysql5.6.20
• 在master和slave中安装mysql，参考这里写链接内容
• 修改主从配置
  [master]

  [root@master local]# vim /usr/local/mysql/my.cnf

# For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

    [mysqld]
    character-set-server=utf8mb4
    interactive_timeout=86400
    wait_timeout=86400
    symbolic-links=0
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M

    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    log-bin=master-log
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
    general_log=ON
    general_log_file=/usr/local/mysql/log/mysql.log
    # These are commonly set, remove the # and set as required.
     basedir = /usr/local/mysql
     datadir = /usr/local/mysql/data
     port = 3306
     server_id = 1

    # socket = .....
    log_bin_trust_function_creators=1
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M 

    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
    [mysql]
    default-character-set=utf8mb4

[slave]
[root@slave local]# vim /usr/local/mysql/my.cnf

# For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

    [mysqld]
    character-set-server=utf8mb4
    interactive_timeout=86400
    wait_timeout=86400
    symbolic-links=0
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M

    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    binlog-ignore-db=mysql
    binlog-ignore-db=performance_schema
    binlog-ignore-db=information_schema
    read-only
    general_log=ON
    general_log_file=/usr/local/mysql/log/mysql.log
    # These are commonly set, remove the # and set as required.
     basedir = /usr/local/mysql
     datadir = /usr/local/mysql/data
     port = 3306
     server_id = 2

    # socket = .....
    # 能导入存储过程
    log_bin_trust_function_creators=1
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M 

    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
    [mysql]
    default-character-set=utf8mb4

• 重启master和slave

 [root@slave local] service mysqld restart

• 在master中创建一个用于复制的用户并授权
  [master]

[root@master local] mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 384459
Server version: 5.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' identified by '123456';
mysql> flush privileges;
mysql> \q
Bye

在此复制用户已创建，如果数据库中没有实际的数据，不用备份，如果存在实际的数据则需要，则在master中备份数据：

[root@master local]  /usr/local/mysql/bin/mysqldump -u root -p123456 --opt  --flush-logs  --db > /root/allbak.sql

• 查看master的偏移量

[root@master local] mysql -u root -p123456 -e "show master status"

-------------------+----------+--------------+---------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+---------------------------------------------+-------------------+
| master-log.000018 |      120 |             |sql,information_schema,performance_schema |                   |
+-------------------+----------+--------------+---------------------------------------------+-------------------+

记下File及Position

• 登录slave，做复制配置
  [slave]

mysql> stop slave;
mysql>  change master to master_host='192.168.0.230',master_user='slave',master_password='123456',master_log_file="mysqlmaster-bin.000018",master_log_pos= 120 ;
mysql>start slave;
mysql>show slave status\G;

找到这五行，如下则主从配置成功：

    ------------------------

        Slave_IO_State: Waiting for master to send event

        Slave_IO_Running: Yes

        Slave_SQL_Running: Yes

        Read_Master_Log_Pos: 120

        Relay_Master_Log_File: bin.000018

• 错误集锦：

问题1：
如果在slave中show slave status\G 时提示Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
解决办法：
在master上：
flush logs;
show master status;
记下File, Position。
在slave上重新CHANGE MASTER
CHANGE MASTER TO MASTER_LOG_FILE=’master-log.000014’,MASTER_LOG_POS=120;
slave start;

问题2：
如果在slave中show slave status\G 时提示
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决方法：
因为我的master和slave是安装在虚拟机中的，在装slave时直接拷贝的master的vmx文件，导致master和slave中的数据库uuid是一样的。因此修改/usr/local/mysql/data/auto.cnf中的server-uuid中的任意一个值：
例如：将server-uuid=ba04c6bc-77dc-11e5-9b20-0050532548d4中的最后一个字符4改为3，重启就ok。