Mysql5.6主从复制 - 快车库

主服务IP：192.168.1.117 从服务IP：192.168.1.118

修改主服务my.cnf，重启mysql服务

   [mysqld]
   innodb_buffer_pool_size = 512M
   log_bin = mysqlmaster-bin.log
   server_id = 117

修改从服务my.cnf，重启mysql服务

  [mysqld]
  innodb_buffer_pool_size = 512M
  log_bin = mysqlslave-bin.log
  server_id = 118  #大于主服务server_id

192.168.1.117创建用于主从复制的账户并复制数据
(1)创建账户(192.168.1.118上一样)

    mysql>GRANT REPLICATION SLAVE ON *.* TO  'repl'@'192.168.1.118' IDENTIFIED BY      'repl';

(2)数据库锁表(192.168.1.118上一样)

    mysql>FLUSH TABLES WITH READ LOCK;

(3)查看master状态


    mysql> show master status;
+------------------------+----------+--------------+------------------+-------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------+----------+--------------+------------------+-------------------+
| mysqlmaster-bin.000015 |      120 |              |                  |                   |
+------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
   二进制日志文件是mysqlmaster-bin.000015，位置是120

(4)复制数据并解锁

    #mysqldump -uroot -Proot --all-databases  --triggers --routines --events >all.sql
    mysql>UNLOCK TABLES;

192.168.1.118上设置
(1)导入all.sql

    #mysql -uroot -P3306 < all.sql

(2)从数据库设置复制的主数据库信息(192.168.1.117上一样)

    mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.117',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysqlmaster-bin.000015',MASTER_LOG_POS=120;
    mysql> START slave;
    mysql>  SHOW slave STATUS \G

显示Slave_IO_Running: Yes Slave_SQL_Running: Yes则表示成功，如果Slave_IO_Running:connecting，则可能是防火墙的原因

列表项目