MySQL5.6主从复制

五个步骤完成MySQL主从复制

   1.主从服务器的 /etc/my.cnf 的配置，设置唯一ID 启用二进制日志。

   2.创建主从复制的账号，并授权REPLICATION SLAVE权限。

   3.查询master的状态，获取主服务器二进制日志信息。

   4.配置从服务器去连接主服务器进行数据复制。

   5.检查从服务器复制功能状态，测试主从复制。

参考文章 http://manual.blog.51cto.com/3300438/1372378

 1.修改主服务器和从服务器的 /etc/my.cnf 的配置文件

   修改主服务器master:

   vi /etc/my.cnf

       [mysqld]

       log-bin=mysql-bin   #启用二进制日志，并设置二进制日志文件前缀

       server-id=222         #[必须]服务器唯一ID，必须是1至232-1之间的整数

       注意：在配置文件中不可以使用skip-networking参数选项，否则从服务器将无法与主服务器进行连                  接并复制数据。

  修改从服务器slave:

   vi /etc/my.cnf

       [mysqld]

       log-bin=mysql-bin

       server-id=223

  注意：如果有多台从服务器，则所有的服务器ID编号都必须是唯一的。

              MySQL从服务器上二进制日志功能是不需要开启的。

但是，你也可以通过启用从服务器的二进制日志功能，实现数据备份与恢复，此外在一些更复杂的          拓扑环境中，MySQL从服务器也可以扮演其他从服务器的主服务器。

 修改完成后，重启两台服务器的mysql

   service mysql restart

2.在主服务器上建立帐户并授权slave:

  mysql -uroot -p123  

   mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by '123456'; 

    这个账户必须拥有REPLICATION SLAVE权限，可以为不同的从服务器创建不同的账户与密码，也可以使用统一的账户与密码。

3.登录主服务器的mysql，查询master的状态 

   mysql> show master status;

     +-------------------+----------+--------------+------------------+-------------------+

     | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

     +-------------------+----------+--------------+------------------+-------------------+

     | mysql-bin.000008  |   520    |              |                  |                   |

     +-------------------+----------+--------------+------------------+-------------------+

     1 row in set (0.00 sec)

   File列显示的是二进制日志文件名，Position为当前日志记录位置，从服务器的设置中需要用到。

   注意：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

 防止其他主机操作主数据库，可以用只读锁表的方式来防止数据库被修改。

   mysql> flush tables with read lock;

   mysql> show master status;

   +------------------+----------+--------------+------------------+-------------------+

     | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

     +------------------+----------+--------------+------------------+-------------------+

     | mysql-bin.000008 |      520 |              |                  |                   |

     +------------------+----------+--------------+------------------+-------------------+

     1 row in set (0.00 sec)

   mysql> unlock tables;

   flush tables with read lock; 命令的作用是对所有数据库的所有表执行只读锁定，

   只读锁定后所有数据库的写操作将被拒绝，但读操作可以继续。

   执行锁定可以防止在查看二进制日志信息的同时有人对数据进行修改操作，

   最后使用unlock tables; 语句对全局锁执行结束操作。

   提示：

        如果MySQL数据库系统中已经存在大量数据，可以使用使用mysqldump工具在主服务器进行备             份，然后导入从服务器。

        (主)导出

          mysqldump -u root -p'123' --all-databases --lock-all-tables > bak_mysql.sql

        (从)导入

          mysql -u root -p'123456'  < bak_mysql.sql

 4.配置从服务器Slave：

     数据复制的关键操作是配置从服务器去连接主服务器进行数据复制，我们需要告知从服务器建立网络连接所有必要的信息。

使用CHANGE MASTER TO 语句即可完成该项工作，

     MASTER_HOST          指定主服务器主机名或IP地址，

     MASTER_USER          为主服务器上创建的拥有复制权限的账户名称，

     MASTER_PASSWORD      为该账户的密码，

     MASTER_LOG_FILE      指定主服务器二进制日志文件名称，

     MASTER_LOG_POS       为主服务器二进制日志当前记录的位置。

mysql -u root -p'123456'   #进入MySQL

mysql>change master to master_host='192.168.1.100',master_user='mysync',master_password='123456',master_log_file='mysql-bin.000008',master_log_pos=520; 

mysql>start slave;    //启动从服务器复制功能

 5.检查从服务器复制功能状态：

   mysql> show slave status\G

   *************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event

              Master_Host: 192.168.2.100  //主服务器地址

              Master_User: mysync   //授权帐户名，尽量避免使用root

              Master_Port: 3306    //数据库端口，部分版本没有此行

              Connect_Retry: 60

              Master_Log_File: mysql-bin.000008

              Read_Master_Log_Pos: 520     //同步读取二进制日志的位置，大于等于主服务器

              Relay_Log_File: ddte-relay-bin.000003

              Relay_Log_Pos: 251

              Relay_Master_Log_File: mysql-bin.000008

              Slave_IO_Running: Yes    //此状态必须YES

              Slave_SQL_Running: Yes     //此状态必须YES

                    ......

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

以上操作过程，主从服务器配置完成。

6.主从服务器测试：

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

  mysql> create database test_db;

  Query OK, 1 row affected (0.00 sec)

  mysql> use test_db;

  Database changed

  mysql>  create table test_db(id int(3),name char(10));

  Query OK, 0 rows affected (0.00 sec)

  mysql> insert into test_db values(001,'bobu');

  Query OK, 1 row affected (0.00 sec)

  mysql> show databases;

   +----------------------+

   | Database             |

   +----------------------+

   | information_schema   |

   | test_db              |

   | mysql                |

   +----------------------+

   4 rows in set (0.00 sec)

从服务器Mysql查询：

   mysql> show databases;

   +----------------------+

   | Database             |

   +----------------------+

   | information_schema   |

   | test_db              |

   | mysql                |

   +----------------------+

   4 rows in set (0.00 sec)

   mysql> use test_db

   Database changed

   mysql> select * from test_db;           //查看主服务器上新增的具体数据

   +------+------+

   | id   | name |

   +------+------+

   |    1 | bobu |

   +------+------+

   1 row in set (0.00 sec)

   MySQL主从复制完成。