mysql5.6 主从_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，则可能是防火墙的原因

列表项目