Mysql主从同步（Mysql A B复制）配置

Mysql主从同步（Mysql AB复制）功能是自动备份数据

vim/var/lib/mysql/auto.cnf  数值不能一样

master主               slave从

192.168.1.1        192.168.1.2

1、主从环境配置：

   mysql_5.6版本

servicemysql start

        ping通

        service iptablesstop

        setenforce 0  

从数据库服务器上一定要有主数据库服务器上的库和表 且 表结构要相同

     2、master主数据库服务器配置

1）master服务器授权用户可以从slave服务器上连接自己，且有拷贝数据的权限

mysql>grantreplication slave on *.* to

slaveuser@"从数据库服务器的地址"  identified by  "密码";                                           

        （注：在从数据库服务器上测试授权用户slaveusser能否登陆主数据库服务器。）

        2）编辑配置文件：开启binlog日志，指定id(1-255,同一个网段内不允许重复)

        [root@data ~]# vim /etc/my.cnf                                         

        [mysqld]                                                              

        log-bin          //主数据库服务器必须开启binlog日志                                                      

        server_id=1  //通常用自己IP地址中的主机位                             

        3）重启服务

        servicemysql restart                                                 

show slave hosts;   //查看主数据库服务器上有哪些从服务器

reset master;  // 删除所有binlog日志，且自动生成第一个binlog日志 

注：在主数据库服务器上配置哪些库可以被slave从服务器同步，哪些库不可以被slave从服务器同步。

在Master主服务器上设置哪些库参加主从同步

注：一般都在从服务器上设置，主、从服务器在其中一台上设置就可以

Vim/etc/my.cnf

[mysqld]

binlog-do-db=test    //slave从服务器可以同步test库

binlog-ignore-db=mysql   //slave从服务器不可以同步mysql库

可以同步与不可以同步属于互斥选项，不要同时设置

3、slave从数据服务器配置

1.开启从数据服务器功能

Vim  /etc/my.cnf                                                                

[mysqld]                                                                       

server_id=2     //指定服务器ID号，一般用本机的IP地址主机位                  

2.servicemysql restart           //重启服务                                 

3.登录Master主服务器，查看主数据库服务器状态里当前使用的binlog和偏移量

 mysql> show masterstatus;    //显示主数据库服务器的状态                    

File  //正在使用的binlog日志

Position   //正在使用binlog日志的偏移量，如图含义是从偏移量位置120开始记录

Binlog_DO_DB  //显示准许同步复制的库

Binlog_Ignore_DB   //显示不准许同步复制的库

Executed_Gtid_Set   //跟binlog日志相似，在这里都是用来主从同步的，默认关闭,两个用一个就可以.

开启Gtid：   //没特殊要求不开启

vim  /etc/my.cnf

gtid_mode=on

4.登录slave从数据库服务器（以本地管理员登录）

Mysql  -uroot –p123456

mysql>changemaster to master_host="主服务器IP地址"，                     

     >master_user="slaveuser主服务器上的授权用户",                        

     >master_password="授权用户的密码"，                                   

     >master_log_file="data-bin.000001",//写图-1file字段里的binlog日志

     >master_log_pos=120; //写图-1的positionz字段里的偏移量数

从配完之后，/var/lib/mysql下会多这几个文件

mysql>change master to master_host="192.168.3.18",

    -> master_user="slaveuser",

    -> master_password="111",

    ->master_log_file="gss.000001",

    -> master_log_pos=120;

QueryOK, 0 rows affected, 2 warnings (0.10 sec)

master.info

relay-log.info 

tarena-relay-bin.000001；

tarena-relay-bin.index

5.最后开起slave数据库里的server_IO与server_SQL进程

mysql> show slave status\G;     //查看从服务当前状态                          

Slave_IO_Running:NO                                                           

Slave_SQL_Running:NO                                                          

mysql>startslave;         //开启IO和SQL                                    

Slave_IO_Running:  YES     //必须开启状态                                   

Slave_SQL_Running:YES      //必须开启状态                                    

注：主数据服务器与从数据服务器的UUID不能相同，相同IO启动不了，

vim/var/lib/mysql/auto.cnf查看UUID，相同就随便改一下，只要不一样就行，

改完重启服务

在从服务器上更改 change master to 下面语句内容时，首先要做 stop slave 即停止主从服务

注：在主数据库服务器上配置哪些库可以被slave从服务器同步，哪些库不可以被slave

从服务器同步。

在Slave从服务器上设置哪些库参加主从同步

Vim  /etc/my.cnf

[mysqld]

replicate_do_db=mysql    //同步mysql库,可以设置多条

replicate-ignore-db=mysql   //不可以同步mysql，不可以和replicate_do_db=一起使用

report-host= localhost //向主服务器报告自己主机名，主服务器查询时会知道是哪个从服务器

report-host=192.168.1.2 //向主服务器报告自己IP，主服务器查询时会知道是哪个从服务器

报告主机名或IP地址，两条写一条就可以

Slave-net-timeout=60   //主从网络中断时，等待60秒，自动连接

read_only=1   //只读模式，同步与super权限用户例外

log-slave-updates 记录从库更新，允许链式复制（A-B-C）（这个选项在做主-从-从（A-B-C）时，如果A上的数据有变化时，C也同步更新，需要在A、B、C三台数据服务器的配置文件/etc/my.cnf的[mysqld]下全部写上）

注：开启从功能后，数据库目录/var/lib/mysql/会多出很多文件

[root@khj~]ls /var/lib/mysql/

master.info    // 连接主数据库服务器的配置信息

relay-log.info  // 中继日志信息

localhost-relay-bin.000001  // 中继日志文件，是主复制过来的日志文件叫中继

localhost-relay-bin.index  //  中继日志文件列表

6.查看从数据库服务器的状态

mysql>show  slave status\G;  //查看从数据服务器的状态

Slave_IO_Running:  YES     //IO线程必须为YES  重要

Slave_SQL_Running: YES    //SQL线程必须为YES  重要

Slave_IO_Running:  YES

作用：负责连接主数据库服务器；把master上binlog日志里的SQL语句拷贝到本机的relaybinlog日志里。

• 连接不上主数据库服务器时会出错：

物理线路

安全限制

授权信息不对

指定使用的binlog日志文件与主数据库服务器上正在使用的不一致

Slave_SQL_Running: YES

作用：执行本机relaybinlog日志里的SQL语句。

如果 Slave_sql_running 启动不了，原因可能是：从数据库服务器上的库和表 表结构与主数据库服务器上不一致时会出错。                        

二、Mysql主从同步工作原理

      1、mysql>show master status;       //查看主数据库服务器的状态              

默认不允许对单个库做replicationslave授权

binlog-do-db=库名 设置master对指定库记日志，从而对指定的库做数据同步

binlog-ignore-db=库名 不同步指定的库 

在my.cnf文件中添加以上选项

     2、mysql> show slave status;   //查看从数据库服务器的状态                 

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

IO：连接master服务器，并把master服务器binlog文件里的sql语句拷贝到本机的relaybinlog文件里

    连接不上主数据库服务器时会处于No状态

SQL：执行本机relaybinlog文件里的sql语句

     从数据库服务器上没有主数据库服务器上的表和库，或表结构与master不一致的时候会处于No状态

只有IO和SQL状态同时为Yes,从数据库服务器才能正常与主数据库服务器同步

Master_Log_File:主数据库服务器当前的binlog日志

Relay_Log_File  当前从数据库服务器使用的中继日志

Relay_Master_Log_File:中继文件中的数据是从主数据库服务器的哪个文件中读来的

发现参数不一样时修改从数据库服务器的状态

mysql>stop  slave;   //修改时要先停止从服务器状态                                                   

mysql>changemaster to 选项=值，选项=值；                              

mysql>start  slave;    //开机从服务器状态                                                 

选项：master_host=’master主机IP’

master_user=’授权用户’

master_password=’密码’

master_log_file=’binlog日志名’

read_master_log_pos=节点数

3、从数据库服务器上文件的作用

master.info    //主数据库服务器的信息,文件存在时，slave连接master时使用该文件,主配置文件中的信息将被忽略

mysqld-relay-bin.index    //记录从数据库服务器上已有的binlog文件列表

relay-log.info     //binlog文件信息

mysqld-relay-bin.000001      //从数据库服务器的binlog中继日志

     4、主数据库服务器执行的改变数据的SQL语句将写到master的binlog日志中，从数据库服务器的IO进程读取master的binlog日志，并将其写到自己的binlog中继日志中，然后SQL进程执行binlog中继日志中的SQL语句，实现主从同步。

三、mysql主从同步结构

生产环境一主多从和主从从用的比较多

1、一主一从，建议将从服务器设置为只读

  192.168.10.1(master)  192.168.10.2(slave)

2、一主多从

  192.168.10.1(master)  192.168.10.2/192.168.10.3/192.168.10.4(slave)

3、主从从   log-slave-updates在双主从服务器上的配置文件里必须加

  192.168.10.1(10.2master)主  

192.168.10.2(10.1slave)(10.3的master)从主 

192.168.10.3(10.2的slave)从

10.1是10.2的主，10.2是10.3的主 10.2分别是10.1的从和10.3的主

思路：

1、给10.1配置主   10.2配置从 实现10.2拷贝10.3binlog日志同步

2、在从的基础上给10.2在配置成主必须开binlog日志    10.3配置从 实现拷贝10.2的binlog日志同步

3、10.2属于主从双数据库服务器，所以在10.2的主配置文件里加一个选项是

   Vim /etc/my.cnf

   [mysqld]

   log-slave-updates  //设置主从从的时候，主从双服务器上必须加这条选项

4.启动从数据服务器10.2与10.3状态

 mysql>slavestart;    //启动从数据库服务器状态

    mysql>show slave status；   //查看从服务器状态

               slave_IO_Running:  YES    //IO线程必须为YES 重要

slave_SQL_Running: YES     //SQL必须YES

注：主数据库服务器必须启动binlog日志所以10.1和10.2必须启动binlog日志，作用是：从服务器会拷贝主服务器的binlog日志，然后在本机生成中继日志，然后在执行中继日志里的SQL语句达到主从同步

4、主主结构（互为主从）

  192.168.10.1(master主)   192.168.10.2(slave从)

               (slave从)                 (master主)

四.删除主从结构

1 把从数据库服务器上的slave 进程停止

数据库管理员身份登录数据库

Mysql>  stop slave

2删除从服务器上的四个文件

[]#rm  -rf

master.info

relay-log.info 

tarena-relay-bin.000001；

tarena-relay-bin.index

主从从

192.168.3.18 主

192.168.3.200从

[mysqld]

log-bin=shuai

server_id=200

datadir=/var/lib/mysql

log-slave-updates=1

192.168.3.100从从

[root@localhost 桌面]# mysqldump -h192.168.3.200 -uroot -p111--all-databases > /sql.sql

Warning: Using a password on the command line interface can beinsecure.

 [root@localhost 桌面]# mysql-hlocalhost  -uroot -ptarena </sql.sql

Warning: Using a password on the command line interface can beinsecure.

四、Mysql读写分离（Mysql代理服务器）

Mysql代理服务器

思路：

1 让mysql-proxy服务监听3306端口

2 在后端的数据库服务器1.1与1.2上做用户授权

3 为保证用户能够看到写入的数据，把负责读写操作的数据库服务器做成主从同步的结构