mysql主从复制作用 mysql主从复制过程

转载

技术领航探索者 2023-08-12 12:27:37

文章标签 mysql主从复制作用 mysql 数据库 dba 服务器 文章分类 MySQL 数据库

1、mysql主从复制的流程

mysql主从复制作用 mysql主从复制过程_mysql主从复制作用

如上图所示，总的来说，有3个步骤：

(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。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上并行操作。

MySQL 使用3个线程来执行复制功能,其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。
1）主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。
2）从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。
3）第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。
有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

2、定位具体的sql和io进程的id

system user对应着从库的sql和io进程，因为我们的是主主复制，所以既有主服务器Binlog Dump线程，还有从库的sql和io进程，具体如下：
1）我们看到Waiting for master to send event的字样，说明这个进程是从库的io进程，因为从服务器I/O线程就是读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件
2）我们看到Slave has read all relay log; waiting for the slave I/O thread to update it，说明这个进程是从库的sql进程，因为从库SQL线程，就是读取中继日志并执行日志中包含的更新。
3）我们看到Master has sent all binlog to slave; waiting for binlog to be updated，说明这个进程是主库的Binlog Dump，因为主库的Binlog Dump就是将二进制日志中的内容（binlog)发送到从服务器.并且主库的这个进程一般是由主库新建的特定的用户来完成的，我们这里是info_syncer 用户。

mysql> show processlist;
 | 13910311 | system user | | NULL | Connect | -1650 | Slave has read all relay log; waiting for the slave I/O thread to update it
 | 13418520 | system user | | NULL | Connect | 3638625 | Waiting for master to send event
 | 13409506 | info_syncer | 192.168.0.243:36191 | NULL | Binlog Dump | 3690795 | Master has sent all binlog to slave; waiting for binlog to be updated

3、mysql死锁的查询和处理

1）查询相关的锁：
 在5.5中，information_schema 库中增加了三个关于锁的表（MEMORY引擎）：
 innodb_trx ## 当前运行的所有事务
 innodb_locks ## 当前出现的锁
 innodb_lock_waits ## 锁等待的对应关系
 看一下表结构：
 root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;
 +————-+———————+——+—–+———+——-+
 | Field | Type | Null | Key | Default | Extra |
 +————-+———————+——+—–+———+——-+
 | lock_id | varchar(81) | NO | | | |#锁ID
 | lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
 | lock_mode | varchar(32) | NO | | | |#锁模式
 | lock_type | varchar(32) | NO | | | |#锁类型
 | lock_table | varchar(1024) | NO | | | |#被锁的表
 | lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
 | lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
 | lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
 | lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
 | lock_data | varchar(8192) | YES | | NULL | |#被锁的数据
 +————-+———————+——+—–+———+——-+
 10 rows in set (0.00 sec)root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;
 +——————-+————-+——+—–+———+——-+
 | Field | Type | Null | Key | Default | Extra |
 +——————-+————-+——+—–+———+——-+
 | requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID（也就是等待锁的id）
 | requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID
 | blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID
 | blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID
 +——————-+————-+——+—–+———+——-+
 4 rows in set (0.00 sec)root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;
 +—————————-+———————+——+—–+———————+——-+
 | Field | Type | Null | Key | Default | Extra |
 +—————————-+———————+——+—–+———————+——-+
 | trx_id | varchar(18) | NO | | | |#事务ID
 | trx_state | varchar(13) | NO | | | |#事务状态：
 | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间；
 | trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
 | trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
 | trx_weight | bigint(21) unsigned | NO | | 0 | |#
 | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID
 | trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
 | trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态
 | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用
 | trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁
 | trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
 | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小（B）
 | trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数
 | trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数
 | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数
 | trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
 | trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
 | trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
 | trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误
 | trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
 | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
 +—————————-+———————+——+—–+———————+——-+
 22 rows in set (0.01 sec)mysql> show processlist; ##可以看出来，
 或者：
 mysql> select concat(‘KILL ‘,id,’;’) from information_schema.processlist where user=‘root’;
 ±-----------------------+
 | concat(‘KILL ‘,id,’;’) |
 ±-----------------------+
 | KILL 3101; |
 | KILL 2946; |
 ±-----------------------+
 2 rows in set (0.00 sec)
 批量kill多个进程。
 mysql>select concat(‘KILL ‘,id,’;’) from information_schema.processlist where user=‘root’ into outfile ‘/tmp/a.txt’;
 Query OK, 2 rows affected (0.00 sec)

小结： MySQL主从复制结构中，mysql主库需要创建一个用户，专门用于传输binlog到从库，而从库接受和应用主库传过来的日志的用户为 system user,该用户是系统自带的，无须创建。

4、数据库主从和从只读配置

对于MySQL单实例数据库和master库，如果需要设置为只读状态，需要进行如下操作和设置：

将MySQL设置为只读状态的命令：
#mysql -uroot -p
mysql> show global variables like “%read_only%”;
mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like “%read_only%”;

将MySQL从只读设置为读写状态的命令：
mysql> unlock tables;
mysql> set global read_only=0;

对于需要保证master-slave主从同步的salve库，如果要设置为只读状态，需要执行的命令为：
mysql> set global read_only=1;

将salve库从只读状态变为读写状态，需要执行的命令是：

mysql> set global read_only=0;
对于数据库读写状态，主要靠 “read_only”全局参数来设定；默认情况下，数据库是用于读写操作的，所以read_only参数也是0或faluse状态，这时候不论是本地用户还是远程访问数据库的用户，都可以进行读写操作；如需设置为只读状态，将该read_only参数设置为1或TRUE状态，但设置 read_only=1 状态有两个需要注意的地方：
1.read_only=1只读模式，不会影响slave同步复制的功能，所以在MySQL slave库中设定了read_only=1后，通过 show slave status\G 命令查看salve状态，可以看到salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致；
2.read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作；在MySQL中设置read_only=1后，普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时，都会报出数据库处于只读模式不能发生数据变化的错误，但具有super权限的用户，例如在本地或远程通过root用户登录到数据库，还是可以进行数据变化的DML操作；

为了确保所有用户，包括具有super权限的用户也不能进行读写操作，就需要执行给所有的表加读锁的命令 “flush tables with read lock;”，这样使用具有super权限的用户登录数据库，想要发生数据变化的操作时，也会提示表被锁定不能修改的报错。

这样通过设置“read_only=1”和“flush tables with read lock;”两条命令，就可以确保数据库处于只读模式，不会发生任何数据改变，在MySQL进行数据库迁移时，限定master主库不能有任何数据变化，就可以通过这种方式来设定。

但同时由于加表锁的命令对数据库表限定非常严格，如果再slave从库上执行这个命令后，slave库可以从master读取binlog日志，但不能够应用日志，slave库不能发生数据改变，当然也不能够实现主从同步了，这时如果使用 “unlock tables;”解除全局的表读锁，slave就会应用从master读取到的binlog日志，继续保证主从库数据库一致同步。

为了保证主从同步可以一直进行，在slave库上要保证具有super权限的root等用户只能在本地登录，不会发生数据变化，其他远程连接的应用用户只按需分配为select,insert,update,delete等权限，保证没有super权限，则只需要将salve设定“read_only=1”模式，即可保证主从同步，又可以实现从库只读。

相对的，设定“read_only=1”只读模式开启的解锁命令为设定“read_only=0”;设定全局锁“flush tables with read lock;”，对应的解锁模式命令为：“unlock tables;”.

当然设定了read_only=1后，所有的select查询操作都是可以正常进行的。

5、主从复制配置案例

1，准备工作
主数据库：192.168.34.119 /linux
从数据库：192.168.34.129 /linux
2，(主库)修改mysql配置文件
找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/my.cnf,在[mysqld]部分插入如下两行：[mysqld]
 log-bin=mysql-bin #开启二进制日志
 server-id=1 #设置server-id
 2.(主库)重启mysql，创建用于同步的用户账号打开mysql会话
mysql>mysql -uroot -p
创建用户并授权：用户：rel1密码：slavepass
mysql> CREATE USER ‘repl’@‘123.57.44.85’ IDENTIFIED BY ‘slavepass’;#创建用户
 mysql> GRANT REPLICATION SLAVE ON . TO ‘repl’@‘123.57.44.85’;#分配权限
 mysql>flush privileges; #刷新权限
 3.查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：mysql > SHOW MASTER STATUS;
 ±-----------------±---------±-------------±-----------------+
 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 ±-----------------±---------±-------------±-----------------+
 | mysql-bin.000003 | 73 | test | manual,mysql |
 ±-----------------±---------±-------------±-----------------+
 二、从服务器slave修改：1.修改mysql配置
同样找到my.cnf配置文件，添加server-id[mysqld]
 server-id=2 #设置server-id，必须唯一
 2.重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：mysql> CHANGE MASTER TO
 -> MASTER_HOST=‘182.92.172.80’,
 -> MASTER_USER=‘rep1’,
 -> MASTER_PASSWORD=‘slavepass’,
 -> MASTER_LOG_FILE=‘mysql-bin.000003’,
 -> MASTER_LOG_POS=73;
 3.启动slave同步进程：mysql>start slave;
 4.查看slave状态：mysql> show slave status\G;
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 182.92.172.80
 Master_User: rep1
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000013
 Read_Master_Log_Pos: 11662
 Relay_Log_File: mysqld-relay-bin.000022
 Relay_Log_Pos: 11765
 Relay_Master_Log_File: mysql-bin.000013
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB:
 Replicate_Ignore_DB:
 …
 当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。还可以用到的其他相关参数：
master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：
#不同步哪些数据库
 binlog-ignore-db = mysql
 binlog-ignore-db = test
 binlog-ignore-db = information_schema#只同步哪些数据库，除此之外，其他不同步
 binlog-do-db = game

6、Mysql主从FAQ：

6.1 主从数据不同步

1）确认当前主动同步情况：

mysql>show processlist; 查看Master库上进程是否Sleep太多。发现很正常。
 show master status; 也正常。
 mysql> show master status;mysql> show slave status\G //Slave上查看
 Slave_IO_Running: Yes
 Slave_SQL_Running: No //当前是Slave不同步

2）手动同步：

**A方案：**忽略错误后，继续同步。该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况。

mysql> stop slave; 
mysql> set global sql_slave_skip_counter =1;  //#表示跳过一步错误，后面的数字可变 
mysql> start slave; 
mysql> show slave status\G //查看如下，表主从同步状态恢复正常
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

B方案： 重新做主从，完全同步。该方法适用于主从库数据相差较大，或者要求数据完全统一的情况。

1.先进入主库，进行锁表，防止数据写入  
mysql> flush tables with read lock;  //注意：该处是锁定为只读状态，语句不区分大小写 

2.进行数据备份 
#把数据备份到mysql.bak.sql文件 
[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql 
这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失 

3.查看master 状态 
mysql> show master status; 

4.把mysql备份文件传到从库机器，进行数据恢复 
#scp mysql.bak.sql root@192.168.128.101:/tmp/

5.停止从库的状态 
mysql> stop slave; 
6.然后到从库执行mysql命令，导入数据备份 
mysql> source /tmp/mysql.bak.sql 
7.设置从库同步
change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;   //注意该处的同步点，就是主库show master status信息里的| File| Position两项.
8.重新开启从同步 
mysql> stop slave; 
9.查看同步状态 
mysql> show slave status\G //查看
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

3）slave同步状态报错1062处理：

从库日志状态：SHOW SLAVE STATUS \G; //输出显示如下类似报错

Last_Errno: 1062

Last_Error: Error ‘Duplicate entry ‘xxxn-66-77’ for key1’ on query. Default database: ‘guild’. Query: ‘insert into pynpcrecord setMapCode = ‘xxxn’, UpdateTime = ‘2015-08-07 00:00:32’’

mysql主从复制作用 mysql主从复制过程_数据库_02

可能原因：主键重复；在slave已经有该记录，又在master上插入了同一条记录。

修复：

方案1：在从库上将重复的主键记录删除，再次重启主从；

 mysql>delete from xxxx where 主键=yyyy;

 mysql>stops lave;
 mysql>start slave;

方案2：停掉主从同步，忽略一次错误，再开启同步：

mysql>stop slave;
mysql>set global sql_slave_skip_counter=1;
mysql>startslave;

若是新配主从，忽略3次还报此错，还可以在my.cnf里加一行： 
slave-skip-errors=1062；然后重启实例，再重启主从同步；

mysql>stop slave;
mysql>start slave;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。