欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Mysql 主从备份完整版

程序员文章站 2022-11-22 15:23:28
MYSQL主从配置有很多好处,一个是起着实时备份的作用,另外一个也可以读写分离,减轻网站的负载,baidu出来的很多配置其实很有问题,在这里重新梳理一下,首先了解一下原理。该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前,master在二日志记录这些改变。MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,mast......

MYSQL 主从配置有很多好处,一个是起着实时备份的作用,另外一个也可以读写分离,减轻网站的负载,baidu出来的很多配置其实很有问题,在这里重新梳理一下,首先了解一下原理。

 

该过程的第一部分就是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线程从中继日志读取事件,更新slave的数据,使其与master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。

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

 

一、 前提条件:

 

1. 数据库服务器最起码两台,假设是数据库MYSQL-A, 数据库MYSQL-B,MYSQL-A的IP 10.0.0.2,MYSQL-B的ip是10.0.0.3;

用户: root /root

数据库 virt

 

2. 建议主从数据库的版本一致,如果不一致很容易出错,或者在mysql的配置文档 my.cnf(window为my.ini) 加上binlog_checksum =none 参数

 

3. 删除所有的外键,表与表的依赖关系最容易导致主从复制失败,一般而言,外键在测试环境上测试校验数据的完整性,而在生产环境最好删除外键,删除外键,可以通过mysql数据字典生成删除脚本,如下

 

SELECT CONCAT("alter table ",a.`TABLE_SCHEMA`,".",TABLE_NAME," drop foreign key ",CONSTRAINT_NAME,";")

FROM KEY_COLUMN_USAGE a

WHERE a.`table_SCHEMA`="virt" AND  REFERENCED_TABLE_NAME  IS NOT NULL

 

4. 设置主键的不长,通常情况下,数据库的主键是无意义的ID,而且是自增长的,如果数据库进行主主备份,那么互相复制的过程容易产生主键冲突,所有需要设置自增长的步长和奇偶数。

 

二、 同步用户

你用root做数据库同步账号当然也没有问题,但更多是建议建立复制账号,如下

CREATE USER 'backup'@'%' IDENTIFIED BY 'backup';

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'10.0.0.%' IDENTIFIED BY 'backup';

 

 

三、 在同步前先让两台数据库的数据一致

 

1. MYSQL-A  下执行SQL命令:flush tables with read lock;目的是锁表

2. MYSQL-B下面执行命令:mysqldump -h10.0.0.2 -uroot -proot virt > var/backup/virtback.sql;备份数据;

3. MYSQL-A下执行SQL命令:unlock tables;解除锁定。

4. MYSQL-B 执行命令:mysql -uroot -proot virt < var/backup/virtback.sql;还原数据

 

 

四、 配置数据库主从备份参数

MYSQL-A, vi /etc/my.cnf,下面的参数需要处理一下

server-id = 1

log-bin=/var/lib/mysql/mysql-bin.log

binlog_format=mixed

binlog-do-db=virt

binlog-ignore-db=mysql  

binlog-ignore-db=information_schema  

binlog-ignore-db=performance_schema  

innodb_flush_log_at_trx_commit=1

sync_binlog=1  

expire_logs_days = 5

auto_increment_increment=2

auto_increment_offset=1

 

MYSQL-B, vi /etc/my.cnf,下面的参数需要处理一下

server-id = 2

log-bin=/var/lib/mysql/mysql-bin.log

binlog_format=mixed

binlog-do-db=virt

binlog-ignore-db=mysql  

binlog-ignore-db=information_schema  

binlog-ignore-db=performance_schema  

innodb_flush_log_at_trx_commit=1

sync_binlog=1  

expire_logs_days = 5

auto_increment_increment=2

auto_increment_offset=2

 

 

五、 重启MYSQL服务

MYSQL-A,MYSQL-B数据库服务器重启

命令:service mysql restart

你也可以/etc/init.d/mysqld restart

 

如果以前有主从备份或者日志,也可以执行SQL命令清除日志

PURGE MASTER LOGS BEFORE '2017-02-21 00:00:00';

RESET MASTER;

 

 

六、 从MYSQL-A到MYSQL-B进行复制过程

1. 检查MYSQL-A 的日志情况,在MYSQL-A执行SQL命令

show master status

 

File 和 Position在复制过程中要用到。

MASTER_LOG_FILE , MASTER_LOG_POS

 

2. 在 MYSQL-B执行SQL命令

#SHOW SLAVE STATUS

CHANGE MASTER TO MASTER_HOST='10.0.0.2',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='backup',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

START SLAVE;

#STOP SLAVE

 

3. 查看状态

MYSQL-B 上面SQL 命令 SHOW SLAVE STATUS

 

  你会看到slave_io_running,slave_sql_runing 的状态都是yes 那么表示从MYSQL-A到MYSQL-B进行复制过好了

 

 

七、 从MYSQL_B到MYSQL-A进行复制过程

4. 检查MYSQL-B 的日志情况,在MYSQL-B执行SQL命令

show master status

 

File 和 Position在复制过程中要用到。

MASTER_LOG_FILE , MASTER_LOG_POS

 

5. 在 MYSQL-A执行SQL命令

#SHOW SLAVE STATUS

CHANGE MASTER TO MASTER_HOST='10.0.0.2',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='backup',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

START SLAVE;

#STOP SLAVE

 

6. 查看状态

MYSQL-A 上面SQL 命令 SHOW SLAVE STATUS

 

  你会看到slave_io_running,slave_sql_runing 的状态都是yes 那么表示从MYSQL-B到MYSQL-A进行复制过好了

 

 

八、 测试

1. 建立表

 

CREATE TABLE `masterslavetest` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `item` varchar(30) DEFAULT NULL,
  `value` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB;

 

 

2. 在mysql-a 插入数据

INSERT INTO masterslavetest(`item`,`value`) VALUES ("mysql-a","mysql-a");

3. 在mysql-b 查看

  执行SQL 命令 SELECT * FROM masterslavetest

 

 

4. 在mysql-a 插入数据

INSERT INTO masterslavetest(`item`,`value`) VALUES ("mysql-b","mysql-b");

 

5. 在mysql-A 查看

 

通过以上测试,可以说明主从备份,主主互相备份成功了

本文地址:https://blog.csdn.net/wcuuchina/article/details/85991063

相关标签: mysql