MySQL主从复制资料汇总_MySQL

程序员文章站 2022-05-05 08:38:57

...

1，复制原理

如下图1.png所示：

MySQL主从复制资料汇总_MySQL

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

下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

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

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

2，mysql主从同步应用场景

（1）：数据分布

（2）：负载均衡

（3）：备份

（4）：高可用和容错

3，搭建mysql主从的环境要求

主从系统要保持一致：包括数据库版本，操作系统版本，磁盘IO磁盘容量，网络带宽等。

[root@data02 ~]# cat /etc/redhat-release

CentOS release 6.2 (Final)

[root@data02 ~]#

	主库master	从库slave
OS系统版本	CentOS release 6.2 (Final)	CentOS release 6.2 (Final)
数据库版本	5.6.12-log	5.6.12-log
磁盘容量	50G	30G
主机ip地址	192.168.52.129	192.168.52.130
端口	3306	3306
内存	1G	1G
服务器类型	虚拟机	虚拟机

4，开始搭建mysql主从复制

4.1建立复制账号

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*TO repl@'192.168.52.130' IDENTIFIED BY 'repl_1234';

建立一个复制账号，只允许从192.168.52.130上来访问登录主库进行二进制日志传输同步。PS:如果mysql版本新旧密码算法不同，可以设置set password for 'backup'@'10.100.0.200'=old_password('1234')）

4.2 手动同步数据

因为当开始搭建的时候，主库上已经有了数据，所以要先把主库已经存在的数据先手动同步迁移到从库上面去。搭建过程中，禁止在主库从库上进行任何对数据库的ddl、dml等数据操作。

这里可以用mysqldump也可以用xtrabackup导出主库上面的数据：

（4.2.1）：xtrabackup方式

在主库上192.168.52.129上面进行数据备份，备份命令，要添加--safe-slave-backup参数：

innobackupex --user=backup--password="123456" --host=192.168.52.129 --socket=/tmp/mysql.sock--defaults-file=/etc/my.cnf /data/backups/mysql/repl/backup_slave --parallel=3--safe-slave-backup --no-timestamp

去备份目录/data/backups/mysql/repl/backup_slave查看备份时候的主库二进制信息，需要根据这个二进制信息来进行数据同步，如下所示：

[root@data01 test]# cd/data/backups/mysql/repl/backup_slave

[root@data01 backup_slave]# more xtrabackup_binlog_info

mysql-bin.000147 120

[root@data01 backup_slave]#

压缩备份文件并且传输到从库192.168.52.130上面：

tar -zcvf backup_slave.tar.gz backup_slave/

scp backup_slave.tar.gz192.168.52.130:/tmp/

（4.2.2）mysqldump方式

在主库上192.168.52.129上做基于主库做数据备份

/usr/local/mysql/bin/mysqldump -ubackup--password=123456 --host=192.168.52.129 --single-transaction --flush-logs --master-data=2 --add-drop-table--create-option --quick --extended-insert=false --set-charset --disable-keys -A> /tmp/alldb.sql

压缩备份文件，传输到从库192.168.52.130上面去。

gzip /tmp/alldb.sql

scp /tmp/alldb.sql.gz 192.168.52.130:/tmp/

4.3 配置master库（192.168.52.129）

Vim /etc/my.cnf

server-id=129 # Server ID

log-bin =/home/data/mysql/binlog/mysql-bin

binlog-ignore-db=mysql # No sync databases

binlog-ignore-db=test # No sync databases

binlog-ignore-db=information_schema # No sync databases

binlog-ignore-db=performance_schema

binlog-do-db=user_db

保存退出，重启mysql主库，可以查看主库的状态，如下：

mysql> show master status;

+------------------+----------+--------------+--------------------------------------------------+-------------------+

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+--------------------------------------------------+-------------------+

| mysql-bin.000151 | 120 | user_db |mysql,test,information_schema,performance_schema | |

+------------------+----------+--------------+--------------------------------------------------+-------------------+

1 row in set (0.00 sec)



mysql>



mysql> show master status\G;

*************************** 1. row***************************

File: mysql-bin.000151

Position: 120

Binlog_Do_DB: user_db

Binlog_Ignore_DB:mysql,test,information_schema,performance_schema

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

4.4 配置slave从库（192.168.52.130）

Slave的配置与master库类似，如下所示：

Vim /etc/my.cnf

#------------------Master-Slaveconfig-----------------

log-slave-updates=1

replicate-same-server-id=0

server-id=230 # Server ID

log-bin=/home/data/mysql/binlog/mysql-bin.log

relay-log=mysql-relay-bin

master-info-repository=TABLE

relay-log-info-repository=TABLE

binlog-ignore-db=mysql # No sync databases

binlog-ignore-db=test # No sync databases

binlog-ignore-db=information_schema # No sync databases

binlog-ignore-db=performance_schema

binlog-do-db=user_db

expire-logs-days=10

max_binlog_size = 10485760

server_id 是必须的，而且唯一。slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置 bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。

relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。

有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

配置玩，重启slave数据库

mysql> show slave status;

Empty set (0.05 sec)

mysql>

没有记录，需要设置一些主从配置。

4.5 设置主从连接复制

生成CHANGE MASTER语句，然后在从上执行，master信息，从备份集合里面获取：

xtrabackup备份的话，从xtrabackup_binlog_info里面获取，如下所示：

[root@data02 tmp]#tar -xvfbackup_slave.tar.gz

[root@data02 tmp]#cd backup_slave

[root@data02 backup_slave]# morextrabackup_binlog_info

mysql-bin.000141 120

[root@data02 backup_slave]#

Mysqldump的啊，从sql文件的头几列获取，如下所示：

[root@data02 tmp]#cd /tmp/

[root@data02 tmp]#gunzip alldb.sql.gz

[root@data02 tmp]# more alldb.sql |grep"CHANGE MASTER TO MASTER_LOG_FILE" |grep "MASTER_LOG_POS"|more

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000141',MASTER_LOG_POS=120;

生成changemaster语句，如下：

CHANGE MASTER TO MASTER_HOST='192.168.52.129',

MASTER_USER='repl',

MASTER_PASSWORD='repl_1234',

MASTER_LOG_FILE='mysql-bin.000141',

MASTER_LOG_POS=120;

有报错信息：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.52.129',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='repl_1234',

-> MASTER_LOG_FILE='mysql-bin.000141',

-> MASTER_LOG_POS=120;

ERROR 1794 (HY000): Slave is not configuredor failed to initialize properly. You must at least set --server-id to enableeither a master or a slave. Additional error messages can be found in the MySQLerror log.

mysql>

具体原因目前不详，网上查找到的资料:数据库打开这几张表的默认引擎为MyISAM，但是这几张表在建表时的引擎为INNODB

但是能确定的，这几张表确实是在mysql5.6中新入的

innodb_index_stats,

innodb_tables_stats,

slave_master_info,

slave_relay_log_info,

slave_worker_info

解决方法：

登录数据库，进入mysql库，执行如下SQL删除5张表

记住，一定要是drop table if exists

drop table if exists innodb_index_stats;

drop table if exists innodb_table_stats;

drop table if exists slave_master_info;

drop table if exists slave_relay_log_info;

drop table if exists slave_worker_info;

执行完后，可以用show tables查看一下，看表的数据是否已经比删除之前减少了，如果减少了，说明你成功了！

[root@data02 test] cd/home/data/mysql/data/mysql

[root@data02 mysql]# ll *.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 innodb_index_stats.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 innodb_table_stats.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 slave_master_info.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 slave_relay_log_info.ibd

-rw-rw----. 1 mysql mysql 98304 1? 9 02:08 slave_worker_info.ibd

[root@data02 mysql]#

强行删除ibd文件：

[root@data02 mysql]# rm -f *.ibd

重启数据库，登录mysql

source/usr/test/mysql/share/mysql_system_tables.sql

show tables;

发现表已经回来了，表数据大概总数量为28个。

之后执行change master to，OK，搞定，如下所示：

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.03sec)

mysql> CHANGE MASTER TOMASTER_HOST='192.168.52.129',MASTER_USER='repl',MASTER_PASSWORD='repl_1234',MASTER_LOG_FILE='mysql-bin.000141',MASTER_LOG_POS=120;

Query OK, 0 rows affected, 2 warnings (0.07sec)

mysql>

启动slave

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql>

4.6 验证主从复制状态

在slave服务器上查看slave状态：

mysql> show slave status\G

*************************** 1. row***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.52.129

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000151

Read_Master_Log_Pos: 346

Relay_Log_File:mysql-relay-bin.000018

Relay_Log_Pos: 509

Relay_Master_Log_File: mysql-bin.000151

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:business_db,user_db,plocc_system

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 346

Relay_Log_Space: 845

Until_Condition: None

这里主要看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

IO和SQL线程都是Yes以及Seconds_Behind_Master是0就表示从库正常运行了。

在master服务器上查看：

mysql> show full processlist;

+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+

+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+

+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+

3 rows in set (0.03 sec)

mysql>

看到有192.168.52.130:45665的线程在同步二进制数据

4.7 master添加数据验证

去master（192.168.52.129）上操作，添加表记录：

mysql> create table master_test select 1as a,'a' as b;

Query OK, 1 row affected (0.72 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql>

去slave（192.168.52.130）上查询下，看表数据是否同步过来，看到数据已经同步过来了，如下所示：

mysql> select * fromuser_db.master_test;

+---+---+

| a | b |

+---+---+

| 1 | a |

+---+---+

1 row in set (0.06 sec)

mysql>

5，如何添加新的slave服务器总结

如果master运行很久了，需要添加新的slave服务器，那么搭建新的slave，此时，有几种方法可以使slave从另一个服务开始，例如，从master拷贝数据，从另一个slave克隆，从最近的备份开始一个slave。Slave与master同步时，需要三样东西：

(1)master的某个时刻的数据快照；

(2)master当前的日志文件、以及生成快照时的字节偏移。这两个值可以叫做日志文件坐标(log file coordinate)，因为它们确定了一个二进制日志的位置，你可以用SHOW MASTER STATUS命令找到日志文件的坐标；

(3)master的二进制日志文件。

也可以通过以下几中方法来克隆一个slave：

(1) 冷拷贝(cold copy)

停止master，将master的文件拷贝到slave；然后重启master。缺点很明显。

(2) 热拷贝(warm copy)

如果你仅使用MyISAM表，你可以使用mysqlhotcopy拷贝，即使服务器正在运行。

如果有myisam和innodb表，可以在业务低峰期用tar包的方式来热拷贝。

(3) 使用mysqldump

使用mysqldump来得到一个数据快照可分为以下几步：

锁表：如果你还没有锁表，你应该对表加锁，防止其它连接修改数据库，否则，你得到的数据可以是不一致的。如下：

mysql> FLUSH TABLES WITH READ LOCK;

在另一个连接用mysqldump创建一个你想进行复制的数据库的转储：

见4.2.1小节

对表释放锁。

mysql> UNLOCK TABLES;

(4) 使用xtrabackup

使用xtrabackup得到数据快照，见4.2.2小节

6，如何实现MSS

当设置log_slave_updates时，你可以让slave扮演其它slave的master。此时，slave把SQL线程执行的事件写进行自己的二进制日志(binary log)，然后，它的slave可以获取这些事件并执行它。如下图6.png所示：

MySQL主从复制资料汇总_MySQL

7，复制过滤

复制过滤可以让你只复制服务器中的一部分数据，有两种复制过滤：在master上过滤二进制日志中的事件；在slave上过滤中继日志中的事件。如下图7.png所示：

MySQL主从复制资料汇总_MySQL

8，常用的mysql主从拓扑结构

复制的体系结构有以下一些基本原则：

(1) 每个slave只能有一个master；

(2) 每个slave只能有一个唯一的服务器ID；

(3) 每个master可以有很多slave；

(4) 如果你设置log_slave_updates，slave可以是其它slave的master，从而扩散master的更新。

MySQL不支持多主服务器复制(MultimasterReplication)——即一个slave可以有多个master。但是，通过一些简单的组合，我们却可以建立灵活而强大的复制体系结构。

9，mysql主从同步之间的管理

介绍一下基本的mysql主从的管理操作命令：

9.1 停止mysql从服务

STOP SLAVE IO_THREAD; #停止IO进程

STOP SLAVE SQL_THREAD; #停止SQL进程

STOP SLAVE; #停止IO和SQL进程

9.2 开启mysql主从同步服务

START SLAVE IO_THREAD; #启动IO进程

START SLAVE SQL_THREAD; #启动SQL进程

START SLAVE; #启动IO进程和SQL进程

9.3 重置mysql主从同步

RESET SLAVE;

#用于让从属服务器忘记其在主服务器的二进制日志中的复制位置, 它会删除master.info和relay-log.info文件，以及所有的中继日志，并启动一个新的中继日志,当你不需要主从的时候可以在从上执行这个操作。不然以后还会同步，可能会覆盖掉你的数据库。

9.4 查看主从同步状态

SHOW SLAVE STATUS;

#这个命令主要查看Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master、Last_IO_Error、Last_SQL_Error这些值来把握复制的状态。

9.5 临时跳过MYSQL同步错误

#经常会朋友mysql主从同步遇到错误的时候，比如一个主键冲突等，那么我就需要在确保那一行数据一致的情况下临时的跳过这个错误，那就需要使用SQL_SLAVE_SKIP_COUNTER = n命令了，n是表示跳过后面的n个事件,比如我跳过一个事件的操作如下：

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

9.6 从指定位置重新同步数据

#有的时候主从同步有问题了以后，需要从log位置的下一个位置进行同步，相当于跳过那个错误，这时候也可以使用CHANGE MASTER命令来处理，只要找到对应的LOG位置就可以,比如：

CHANGE MASTER TOMASTER_HOST='10.1.1.75',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=106;

START SLAVE;

PS：生产环境中这种操作尽量能避免就避免。

10，线上维护mysql主从需要注意的事项

1. 不要乱使用SQL_SLAVE_SKIP_COUNTER命令。

这个命令跳过之后很可能会导致你的主从数据不一致，一定要先将指定的错误记录下来，然后再去检查数据是否一致，尤其是核心的业务数据。

2. 结合percona-toolkit工具pt-table-checksum定期查看数据是否一致。

这个是DBA必须要定期做的事情，呵呵，有合适的工具何乐而不为呢？另外percona-toolkit还提供了对数据库不一致的解决方案，可以采用pt-table-sync，这个工具不会更改主的数据。还可以使用pt-heartbeat来查看从服务器的复制落后情况。

3. 使用replicate-wild-ignore-table选项而不要使用replicate-do-db或者replicate-ignore-db。

原因已经在上面做了说明。

4. 将主服务器的日志模式调整成mixed。

5. 每个表都加上主键，主键对数据库的同步会有影响尤其是居于ROW复制模式。

MySQL主从复制资料汇总_MySQL

1，复制原理

2，mysql主从同步应用场景

3，搭建mysql主从的环境要求

4，开始搭建mysql主从复制

4.1建立复制账号

4.2 手动同步数据

（4.2.1）：xtrabackup方式

（4.2.2）mysqldump方式

4.3 配置master库（192.168.52.129）

4.4 配置slave从库（192.168.52.130）

4.5 设置主从连接复制

4.6 验证主从复制状态

4.7 master添加数据验证

5，如何添加新的slave服务器总结

6，如何实现MSS

7，复制过滤

8，常用的mysql主从拓扑结构

9，mysql主从同步之间的管理

9.1 停止mysql从服务

9.2 开启mysql主从同步服务

9.3 重置mysql主从同步

9.4 查看主从同步状态

9.5 临时跳过MYSQL同步错误

9.6 从指定位置重新同步数据

10，线上维护mysql主从需要注意的事项

Mysql获取id最大值、表的记录总数等相关问题的方法汇总

mysql 5.7 docker 主从复制架构搭建教程

Mysql 错误问题汇总(不断更新中)

安装Mysql5.7.10 winx64出现的几个问题汇总

mysql表的基础操作汇总（三）

mysql 索引的基础操作汇总（四）

Mysql 5.7.14 使用常见问题汇总(推荐)

C++与mysql连接遇到的问题汇总

Mysql获取id最大值、表的记录总数等相关问题的方法汇总

linux系统中使用openssl实现mysql主从复制