MySQL主从复制部署

前言

MySQL的主从复制是基于二进制日志机制的，需开启二进制日志功能。在具体的配置过程中，需注意主服务器与从服务器均配置唯一ID编号，且从服务器必须设置主服务器的主机名、日志文件名、文件位置等参数。整个过程可分为如下几个步骤：

（1）    主服务器：开启二进制日志功能，设置服务器ID，并重启MySQL服务；

（2）    在所有从服务器上设置服务器ID（唯一）；

（3）    在主服务器上为不同的从服务器创建可读取主服务器日志文件的账户，或使用统一账户；

一、系统规划

1、主服务器

操作系统版本: RHEL 6.6 x64

数据库版本: MySQL 5.6

主机IP：192.168.152.129

主机名：master

数据库名称：

ID编号：1

2、从服务器

操作系统版本：RHEL 6.6 x64

数据库版本：MySQL 5.6（必须保证MySQL版本一致）

主机IP：192.168.152.128

主机名：slave

数据库名称：

ID编号：2

注：本文为个人学习之用，使用虚拟机模拟，可作为实施参考。

二、安装准备

1、存储

需要根据实际情况，为源数和目标端数据库分配足够的可用空间

2、网络

需要开通源端和目标端服务器的any-any的网络端口互通性

3、NTP

需要同步源和目标主机时钟同步机制，若为内网环境，推荐配置内网时钟服务器

4、其他

配置静态IP、主机名、IP与主机名绑定、防火墙（开启端口或关闭防火墙）

5、安装MySQL

      为主服务器和从服务器安装MySQL，须保证两者版本一致，或者slave端版本高于master版本。

三、主服务器配置

1、准备测试数据

准备测试数据，模拟要同步对象。

mysql> create database hr;

mysql> use hr;

mysql> create table emp(id int primary key,name char(20));

mysql> insert into emp(id,name) values(1,'tom');

2、设置my.cnf参数

（1）拷贝配置文件

# cp /usr/local/mysql/my.cnf /etc/my.cnf

如有必要，应注意备份配置文件，并且在生产活动懂养成备份的意识。

（2）参数设置

以下仅列出几个必要参数：

log-bin=mysql-bin             启用二进制日志
server-id=1                        服务器ID，可1-之间的任意整数
binlog-ignore-db=information_schema           避免同步的数据库
binlog-ignore-db=performance_schema 
binlog-ignore-db=mysql
binlog-do-db=hr                       二进制需要同步的数据库名

注意：在配置文件中不可使用skip-networking参数，否则从服务器将无法连接主服务器并进行数据同步

（3）重启服务器

# service mysqld restart

3、为slave用户添加同步专用权限

（1）创建复制账户并赋权

mysql> GRANT FILE ON *.* TO 'slave_cp'@'192.168.1.2' IDENTIFIED BY 'root';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_cp'@'192.168.1.2' IDENTIFIED BY 'root';

mysql> FLUSH PRIVILEGES;

（2）添加管理用户

grant all privileges on *.* to admin@'%' identified by 'root';

（3）刷新权限

flush privileges; 

四、从服务器设置

1、设置my.cnf参数

（1）备份配置文件

# cp /usr/local/mysql/my.cnf /etc/my.cnf

（2）修改参数信息

必要参数如下：

log-bin=mysql-bin

server-id=2                       #从服务器ID

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=mysql

replicate-do-db=hr                 #同步的数据库名

replicate-ignore-db=mysql        #屏蔽不同步的数据库

log-slave-updates

slave-skip-errors=all

slave-net-timeout=60

注：MySQL 5.6之后没有master-user等参数，否则重启报错

（3）重启服务器

# service mysqld restart

五、数据库备份、同步

1、对数据库进行备份

[root@master mysql]# /usr/local/mysql/bin/mysqldump -h localhost -uroot -proot --all-databases --lock-all-tables >/tmp/dbdump.sql
[root@master mysql]# scp /tmp/dbdump.sql root@slave:/tmp/
[root@slave ~]# mysql -u root -p < /tmp/dbdump.sql

注：从服务器不需要创建要同步的数据库，备份脚本会自动创建。

2、配置从服务器连接主服务器同步

（1）、获取主服务器二进制日志信息

[root@master mysql]# mysql -uroot -p

Enter password:
mysql> flush tables with read lock;        --只读锁定
mysql> show master status;
mysql> unlock tables;                       --解除锁定

（2）设置从服务器同步

[root@slave ~]# mysql -u root –p

mysql> stop slaves;

mysql> change master to
    -> master_host='192.168.152.129',
    -> master_user='slave_cp',
    -> master_password='root',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=195;                 --这里的参数取值参考查询到的master日志信息

mysql> start slave;
mysql> show slave status \G

3、同步验证

在master上添加数据，查看slave端的同步情况。

master节点：

[root@master ~]# mysql -uroot -p
mysql> create database test;
mysql> use test;
mysql> create table users(id int primary key,name char(20));
mysql> insert into users(id,name)values(1,'aa');
mysql> use hr;
mysql> show tables;
mysql> create table bb(id int,name char(20));
mysql> insert into emp(id,name)values(2,'bb');

 slave节点：

[root@slave ~]# mysql -uroot -p
mysql> select * from test.users;

错误

1、启动从服务器失败

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解决：