mysql主从复制GTID binglog 多源复制 多线程复制

MySQL主从复制是异步的 所以会有延迟  可以通过程序逻辑造成错觉同步  比如 某人发布一片文章 此人可以直接主库 而游客不知道发布了没 读的是从库

MySQL5.7以后增加很好主从复制相关的试图

     use performance_schema 可以查看

MySQL主从复制

一,基于binlog日志

    对于主库master 

              1.开启binlog     设置server-id

              2.创建授权用户

                       (1) 5.7之前 grant replication slave on *.* to '用户名'@'从服务ip' identified by '密码'

                       (2)5.7之后推荐使用

                                   create user '用户名'@'从服务器ip' identified by '密码';

                                   grant replication slave on *.* to '用户名'@'服务器ip';

               3.master控制复制只能停留在库级别  要想控制复制 建议在slave端控制  //建写在配置文件里

                             master端                                                                         slave端

                       binglog_do_db =                                                  replication_do_db   replication_ignore_db    //复制或忽略的数据库

                      binglog_ignore_db=                                             replication_do_table  replication_ignore_table  //复制或者忽略的数据库表

               4.dump主库的数据

                   使用 mysqldump --single-transaction --master-data=2 --trigger --routines --all-databases -uroot -p > dump.sql

                   锁定主机查看master状态

                  flush tables with read lock

                  show master status \G    ps:注意 File: mysql-bin-log.000001     Positin:   1234

                  再次执行 FLUSH TABLES WITH READ LOCK

                  使用 unlock tables 解锁表

       对于是slave

                导入主库数据

                mysql -uroot -p < dump.sql

                停止slave

                         stop slave

               设置主从   ps:这他妈的才是重点

                  change master to master_host='主库ip',

                  master_user='',

                  master_port=3306

                  master_password=''

                  master_log_file=''                    就是主库show master status 查到的 File项的只

                  master_log_pso=''                    同上

                  开启主从复制

                   start slave

                 show slave status \G   //下面两项必须是yes 否则查看防火墙

                            Slave_IO_Running: Yes

                            Slave_SQL_Running: Yes

 二,基于事物的GTID 

   从基于日志转换成基于事物的前提

    1.MySQL高于5.7.6以上

    2.所有MySQL服务器的gtid_mode =off

三,多源复制

四,多线程复制

[mysqld_multi]
 mysqld = /usr/local/mysql/bin/mysqld_safe
 mysqladmin = /usr/local/mysql/bin/mysqladmin
[mysqld3306]
 socket     = /tmp/mysql3306.sock
 port       = 3306
 pid-file   = /usr/local/mysql/data/3306/3306.pid
 datadir    = /usr/local/mysql/data/3306/data
 basedir    = /usr/local/mysql
 server-id  =3306
 log-bin          = /usr/local/mysql/data/3306/mysql-bin
  #binlog-format有三种形式：Statement、Mixed、Row，默认设置为mixed
  binglog-format = mixed
  #需要同步的库，不指定默认同步全部库
  binlog-do-db=test
  #不需要同步的库
  binlog-ignore-db=mysql
  binlog-ignore-db=information_schema
  binlog-ignore-db=performance_schema
  binlog-ignore-db=sys
[mysqld3307]
 socket     = /tmp/mysql3307.sock
 port       = 3307
 pid-file   = /usr/local/mysql/data/3307/3307.pid
 datadir    = /usr/local/mysql/data/3307/data
 basedir    = /usr/local/mysql
 server-id  =3307
[mysqld3308]
 socket     = /tmp/mysql3308.sock
 port       = 3308
 pid-file   = /usr/local/mysql/data/3308/3308.pid
 datadir    = /usr/local/mysql/data/3308/data
 basedir    = /usr/local/mysql
 server-id  =3308