MySql-主从复制

时间 2020-02-02 标签 mysql 主从复制

1、概述
一、原理
复制(Replication)是从一台MySQL数据库服务器（主服务器master）复制数据到另外一个服务器（从服务器slave）的一个进程。
主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志能够记录发送到从服务器的更新。当一个从服务器链接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，而后封锁并等待主服务器通知新的更新。
当进行复制时，全部对复制中的表的更新必须在主服务器上进行。不然，你必需要当心，以免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。css

二、mysql支持的复制类型
（１）基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行一样的语句。MySQL默认采用基于语句的复制，效率比较高。
一旦发现无法精确复制时，会自动选着基于行的复制。
（２）基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
（３）混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的没法精确的复制时，就会采用基于行的复制。mysql

三、MySQL复制技术的一些特色：
（1）数据分布 (Data distribution )
（2）负载平衡(load balancing)
（3）备份(Backups)
（4）高可用性和容错行 High availability and failover nginx

四、工做方式
主要分三个步骤
(1)master将改变记录到二进制日志(binary log)中（这些记录叫作二进制日志事件，binary log events）；
(2)slave将master的binary log events拷贝到它的中继日志(relay log)；
(3)slave重作中继日志中的事件，将改变反映它本身的数据。
c++

该过程的第一部分就是master记录二进制日志。在每一个事务更新数据完成以前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即便事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。web

下一步就是slave将master的binary log拷贝到它本身的中继日志。首先，slave开始一个工做线程——I/O线程。I/O线程在master上打开一个普通的链接，而后开始binlog dump process（可使用 show processlist 查看 Binlog Dump线程）。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容，Binlog dump process从master的二进制日志中读取事件，若是已经跟上master，它会睡眠并等待master产生新的事件，I/O线程将这些事件写入中继日志。sql

SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志一般会位于OS的缓存中，因此中继日志的开销很小。数据库

此外，在master中也有一个工做线程：和其它MySQL的链接同样，slave在master中打开一个链接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操做不能在slave上并行操做。vim

2、实践
如下基于 CentOS x86_64 使用 mariadb-10.1.11.tar.gz 进行配置，首先在主从节点安装好mariadb缓存

主节点：192.168.1.106  CentOS6.6  x86_64
从节点：192.168.1.113  CentOS6.6  x86_64

[root@localhost ~]# wget https://downloads.mariadb.org/interstitial/mariadb-10.1.11/source/mariadb-10.1.11.tar.gz
[root@localhost ~]# yum -y install gcc gcc-c++ make cmake ncurses-devel ncurses libxml2 libxml2-devel openssl-devel bison bison-devel #安装编译环境
[root@localhost ~]# tar xf mariadb-10.1.11.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -s mariadb-10.1.11/ mysql
[root@localhost local]# cd mysql
[root@localhost mysql]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mydata -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STPRAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWIYH_READLINE=1 -DWIYH_SSL=system -DVITH_ZLIB=system -DWITH_LOBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
[root@localhost mysql]# make && make install

剩余步骤与mysql编译安装方法相同，此处再也不详写安全

配置过程：
版本：
1、双方的MySQL版本要一致；
2、若是不一致：主的要低于从的；

主服务器：
1、改server-id
2、启用二进制日志
3、建立有复制权限的账号

从服务器：
1、改server-id
2、启用中继日志
3、链接主服务器
4、启动复制线程

复制开始的位置：
1、都从0开始：
2、若主服务器已经运行一段时间，而且存在不小的数据集，此时须要先将主服务器数据库进行备份，而后在从服务恢复，从主服务器上复制时从主服务器所处的位置开始复制；

（1）从0开始复制
配置主服务器：

安装好后配置文件中默认已经启用二进制日志，主节点server-id能够先不用修改，可是两个节点的server-id不能相同，二进制日志文件不要和数据文件放在一块，而后开始建立有复制权限的账号：

MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.%.%' identified by 'replpass';      
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

配置从服务器：

[root@localhost ~]# vim /etc/my.cnf     #只需修改如下几项

#log-bin=mysql-bin
#关闭二进制日志
server-id       = 11
#修改server-id
relay-log       = /mydata/relaylogs/relay-bin
#启用中继日志

[root@localhost ~]# mkdir /mydata/relaylogs   #建立目录

[root@localhost ~]# chown -R mysql.mysql /mydata/


MariaDB [(none)]> show global variables like '%relay%';
+-----------------------+-----------------------------------+
| Variable_name | Value | +-----------------------+-----------------------------------+
| max_relay_log_size | 1073741824 | | relay_log             | /mydata/relaylogs/relay-bin       |
| relay_log_basename    | /mydata/relaylogs/relay-bin       |
| relay_log_index       | /mydata/relaylogs/relay-bin.index |
| relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0                                 |
| sync_relay_log        | 10000                             |
| sync_relay_log_info | 10000 | +-----------------------+-----------------------------------+
10 rows in set (0.05 sec)

而后启动复制功能：

MariaDB [(none)]> change master to master_host='192.168.1.106',master_user='repluser',master_password='replpass';  
Query OK, 0 rows affected (0.05 sec)

MariaDB [(none)]> start slave;  #启动slave
Query OK, 0 rows affected (0.03 sec)

[root@localhost data]# tail -f localhost.localdomain.err #查看日志文件
2016-01-31  2:01:13 140230526023648 [Note] InnoDB: Waiting for purge to start
2016-01-31  2:01:13 140230526023648 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.26-76.0 started; log sequence number 1616829
2016-01-31  2:01:13 140230526023648 [Note] Plugin 'FEEDBACK' is disabled.
2016-01-31  2:01:13 140229866223360 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-01-31  2:01:13 140230526023648 [Note] Server socket created on IP: '::'.
2016-01-31  2:01:13 140230526023648 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '10.1.11-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
2016-01-31  2:10:05 140230215559936 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.1.106', master_port='3306', master_log_file='', master_log_pos='4'.
2016-01-31  2:14:53 140230214953728 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/mydata/relaylogs/relay-bin.000001' position: 4
2016-01-31  2:14:54 140230215256832 [Note] Slave I/O thread: connected to master 'repluser@192.168.1.106:3306',replication started in log 'FIRST' at position 4

在这里主要是看:
Slave_IO_Running=Yes
Slave_SQL_Running=Yes

在主服务器插入数据：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+
| mysql-bin.000001 | 641 | | | +------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show  databases;
+--------------------+
| Database | +--------------------+
| information_schema | | mydb | | mysql | | performance_schema |
| test | +--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+
| mysql-bin.000001 | 762 | | | +------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

查看从服务器，已经收到数据了：

MariaDB [(none)]> show databases;
+--------------------+
| Database | +--------------------+
| information_schema | | mydb | | mysql | | performance_schema |
| test | +--------------------+
5 rows in set (0.01 sec)

MariaDB [(none)]> stop slave;    #中止从服务器服务
Query OK, 0 rows affected (0.01 sec) 

[root@localhost ~]# service mysqld stop  #中止服务器从新启动后，slave会自动启动
Shutting down MySQL.. SUCCESS! 
[root@localhost ~]# service mysqld start
Starting MySQL. SUCCESS! 
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.11-MariaDB Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> show warnings;
+-------+------+--------------------------+
| Level | Code | Message | +-------+------+--------------------------+
| Note | 1254 | Slave is already running | +-------+------+--------------------------+
1 row in set (0.00 sec)

（2）从半道复制
主服务器：

MariaDB [(none)]> drop database mydb;       #首先在主从节点先删除数据库
Query OK, 0 rows affected (0.01 sec)

[root@cacti mydata]# mysql < hellodb.sql     #导入自定义的数据库
[root@cacti mydata]# mysqldump --all-databases --flush-logs --master-data=2 --lock-all-tables > all.sql  #先备份
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

[root@cacti mydata]# scp all.sql  root@192.168.1.113:~  #将数据复制到从服务器

MariaDB [(none)]> show  master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+
| master-bin.000002 | 366 | | | +-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

从服务器：

[root@localhost ~]# mysql < all.sql 

 MariaDB [(none)]> stop slave;   #确保slave功能关闭
 MariaDB [(none)]> change master to master_host='192.168.1.106',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000002',master_log_pos=366;  
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.03 sec)

在主服务器端插入数据进行MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show  databases;
+--------------------+
| Database | +--------------------+
| hellodb            |
| information_schema | | mydb | | mysql | | performance_schema |
| test | +--------------------+
6 rows in set (0.00 sec)

能够看到两边数据一致，说明复制已经没有问题了。

从半道复制和从0复制其实差异不大，只是在从服务器中链接主服务器时须要指定二进制日志文件及其位置便可。

3、更多知识

1、主从服务器时间要同步（ntp）：
  */5 * * * * /usr/sbin/ntpdate 192.168.1.1

2、如何限制从服务器只读？
  read-only=ON （在/etc/my.cnf 中定义）
  注意：仅能限制那不具备SUPER权限用户没法执行写操做；
  想限制全部用户：
  mysql> FLUSH TABLES WITH READ LOCK;

3、如何主从复制时的事务安全？
  在主服务器上配置：
  sync_binlog=1 #每次提交当即将缓冲去内容同步到日志,须要关闭自动提交功能

4、复制过滤器：为了节约资源并提升服务器的性能，能够设置过滤器只复制但愿备份的数据库，在配置的文件 my.cnf 中的 [mysqld] 块中使用如下配置进行过滤：
master上把事件从二进制日志中过滤:
    binlog_do_db= #复制哪些数据库，白名单
    binlog_ignore_db= #相反的，黑名单

slave上事件从中继日志中过滤:
    replicate_do_db= #数据库的白名单
    replicate_ignore_db= #数据库的黑名单
    replicate_do_table= db_name.table_name #表的白名单
    replicate_ignore_table= #表的黑名单
    replicate_wild_do_table= #支持通配符的白名单
    replicate_wild_ignore_table= #支持通配符的黑名单
MySQL 5.5以上，下面的这些表都建议过滤掉，只复制生产环境数据。
    replicate-wild-ignore-table =mysql.%
    replicate-wild-ignore-table =test.%
    replicate-wild-ignore-table =log.%
    replicate-wild-ignore-table =information_schema.%
    replicate-wild-ignore-table =performance_schema.%

4、配置过程的问题
1）设定 Replication 完成后, start slave后出现 “Could not initialize master info structure”

1.MariaDB [(none)]> reset slave; # 重点就是这行
2.MariaDB [(none)]> CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107; # 请依照自行环境设定
3.MariaDB [(none)]> start slave; # 就正常了.

从新设置 slave, MASTER_LOG_FILE 和 MASTER_LOG_POS 会被清空, 因此须要从新设置.

2）mysql 主从同步失败 Last_IO_Error: Got fatal error 1236 from master

先进入slave中执行:”slave stop;”来中止从库同步；
再去master中执行:”flush logs;”来清空日志；
而后在master中执行:”show master status;”查看下主库的状态，主要是日志的文件和position；
而后回到slave中，执行:”CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.000004′,MASTER_LOG_POS=106;”，文件和位置对应master中的;
最后在slave中执行:”slave start;”来启动同步。