MySql数据库之主从复制_网站建设

MySql数据库之主从复制

2025-07-22 09:59 点击:0

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

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

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

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

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

下一步就是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 slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

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

二、实践
以下基于 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-id2、启用二进制日志3、创建有复制权限的帐号从服务器：1、改server-id2、启用中继日志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-idrelay-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;  #启动slaveQuery 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 start2016-01-31  2:01:13 140230526023648 [Note] InnoDB:  Percona XtraDB (percona) 5.6.26-76.0 started; log sequence number 16168292016-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 started2016-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 distribution2016-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: 42016-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 startStarting MySQL. SUCCESS! [root@localhost ~]# mysqlWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 5Server version: 10.1.11-MariaDB Source distributionCopyright (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复制其实差别不大，只是在从服务器中连接主服务器时需要指定二进制日志文件及其位置即可。

三、更多知识

1、主从服务器时间要同步（ntp）：  */5 * * * * /usr/sbin/ntpdate 192.168.1.12、如何限制从服务器只读？  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.%

四、配置过程的问题 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;”来启动同步。