mysql主从复制实操

主从复制简介

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

实现MySQL主从复制需要进行的配置：

主服务器：
1)开启二进制日志
2)配置唯一的server-id
3)获得master二进制日志文件名及位置
4)创建一个用于slave和master通信的用户账号

从服务器：
1)配置唯一的server-id
2)使用master分配的用户账号读取master二进制日志
3)启用slave服务

具体实现过程如下：

maseter 192.168.1.101
slave 192.168.1.174

主数据库master修改：

1.修改mysql配置

找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id,也可以设置为IP最后一个段位的值

重启mysql

service mysqld restart

2.从服务器的账号和授权

打开mysql会话shell

mysql -h  -u root -p password



mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'123.57.44.85';
mysql>flush privileges;

以上命令分别对应如下：
1.创建用户slave，创建用户并授权：用户slave，密码123456
2.分配拷贝权限给slave
3.刷新权限

3. 查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

File:日志文件的当前文件名
Position：当前数据的记录位置

以上信息是从数据库初始拷贝时位置

从服务器slave修改

1.修改mysql配置

同样找到my.cnf配置文件，添加server-id

 mysql -u root -p root
 vi 
[mysqld]
server-id=2 

设置server-id，必须唯一

关闭slave（如果你以前配置过主从的话，一定要先关闭）

stop slave;

重启mysql

service mysqld restart

2.配置从数据库的同步配置信息

打开mysql会话，执行同步SQL语句

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.1.101',
    ->     MASTER_USER='slave',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_LOG_FILE='mysql-bin.000003',
    ->     MASTER_LOG_POS=73;

参数解释：MASTER_HOST : 设置要连接的主服务器的ip地址

MASTER_USER : 设置要连接的主服务器的用户名

MASTER_PASSWORD : 设置要连接的主服务器的密码

MASTER_LOG_FILE : 设置要连接的主服务器的bin日志的日志名称，即第3步得到的信息

MASTER_LOG_POS : 设置要连接的主服务器的bin日志的记录位置，即第3步得到的信息，（这里注意，最后一项不需要加引号。否则配置失败）

3.启动slave同步进程：

mysql>start slave;

4.查看slave状态：

复制代码

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.101
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 11662
               Relay_Log_File: mysqld-relay-bin.000022
                Relay_Log_Pos: 11765
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
        ...

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

上面两项均为yes，说明配置成功，否则，请重复前面的步骤。

到这里MySQL的主从复制就配置完了。

扩展操作-主机当机，指定数据库同步

接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。

还可以用到的其他相关参数：

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

高级配置:主从只同步部分库或表

同步部分数据有两个思路，
1.master只发送需要的;
2.slave只接收想要的。

** master端 **

binlog-do-db 二进制日志记录的数据库(多数据库用逗号，隔开)

binlog-ignore-db 二进制日志中忽略数据库 (多数据库用逗号，隔开)

举例说明：

1)binlog-do-db=YYY 需要同步的数据库，不在内的不同步。(不添加这行表示同步所有)

# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = game  

这里主库只同步game库

2)binlog-ignore-db = mysql 这是不记录binlog，来达到从库不同步mysql库，以确保各自权限

# 不同步哪些数据库  
binlog-ignore-db = mysql,test,information_schema 

注意：这个配置数据库一开始就直接不生成相关数据库的日志，即便是主库本机上的二进制文件本身也没有相关库的日志信息

** slave端 **

replicate-do-db 设定需要复制的数据库(多数据库使用逗号，隔开)

replicate-ignore-db 设定需要忽略的复制数据库 (多数据库使用逗号，隔开)

replicate-do-table 设定需要复制的表

replicate-ignore-table 设定需要忽略的复制表

replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符

replicate-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符

与上述对比，这里的replicate就很好理解了，下面简单说几点。

举例子：

从库忽略复制数据库test，但是需要说明的是，其实从库的relaylog中是存在关于test的相关日志(前提是主库binlog-ignore-db没有test的日志配置)，只是从库没有使用罢了。

replicate-ignore-db = test

增加通配符的两个配置

replicate-wild-do-table=db_name.% 只复制哪个库的哪个表
replicate-wild-ignore-table=mysql.% 忽略哪个库的哪个表