Mysql主从复制配置操作指导（主备）

最新推荐文章于 2024-04-29 00:04:44 发布

cyf_2008

最新推荐文章于 2024-04-29 00:04:44 发布

阅读量512

点赞数

分类专栏：数据库大数据文章标签： MySQL 主从主备复制配置

本文链接：https://blog.csdn.net/cyf_2008/article/details/73028367

版权

大数据同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

Mysql主从复制配置操作指导（主备）

为了方便运维人员能够快速完成Mysql数据库主从复制创建操作，特编写此文档，供运维人员和工程人员参考使用。

一、准备工作：

停止应用；

停止zabbix；否则会有告警出现。

#/etc/rc.d/init.d/zabbix_server stop

二、修改主库

1、修改主库配置文件

用root用户执行

#vi /etc/my.cnf

server-id = 1 #server-id，主库和备库不能一样，一般主库为1，备库为2

binlog-do-db = azkaban #要复制的数据库，每行一个。

binlog-do-db = zabbix

binlog-ignore-db = mysql #不要复制的数据库，每行一个。

binlog-ignore-db =information_schema

binlog-ignore-db =performance_schema

binlog-ignore-db = test

2、重启主库

# /etc/init.d/mysql restart

查看主库状态，确认主机已经生效

# mysql -uroot -p'******'

mysql> show master status;

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

1 row in set (0.00 sec)

mysql>

3、修改主库表状态为只读

mysql> FLUSH TABLES WITH READ LOCK;

4、查看主库状态，记录主库的bin-log号和position号

mysql> SHOW MASTER STATUS;

1 row in set (0.00 sec)

mysql>

三、基准数据导入，在备机操作。

1、在备库上创建需要同步的数据库；

[mysql@mysql-slave backup]$./createdatabases.sh

在备机上运行备份脚本，从主库备份需要同步的库到备机。

[mysql@mysql-slave backup]$./backup.sh

2、在备机上运行恢复脚本，把备份导入备库。

[mysql@mysql-slave backup]$./restore.sh

3、在备机上运行备份用户脚本，从主机备份用户的表到备机

[mysql@mysql-slave backup]$/usr/local/mysql/bin/mysqldump-uroot -p'******' -h mysql-master.td.com mysql user > user.dump

4、修改脚本去除备机已经有的用户

[mysql@mysql-slave backup]$vi user.dump

5、在备机运行恢复用户脚本，把备份用户导入备库。

[mysql@mysql-slave backup]$/usr/local/mysql/bin/mysqldump-uroot -p'******' -h mysql-master.td.com mysql user < user.dump

6、导出mysql.db表

[mysql@mysql-slave backup]$/usr/local/mysql/bin/mysqldump-uroot -p'******' -h mysql-master.td.com mysql db > db.dump

7、修改脚本去除备机db表中已经有的行。

[mysql@mysql-slave backup]$vi db.dump

8、在备机运行恢复mysql.db表脚本，把备份mysql.db表导入备库。

[mysql@mysql-slave backup]$/usr/local/mysql/bin/mysqldump-uroot -p'******' -h mysql-master.td.com mysql db < db.dump

9、使生效

Flush privileges;

四、修改备库

1、修改备库配置文件

#vi /etc/my.cnf

server-id = 2

log_slave_updates = 1

relay_log_index =relay-bin.index

read_only = 1

replicate-do-db = azkaban

replicate-do-db = zabbix

replicate-ignore-db =mysql

replicate-ignore-db =information_schema

replicate-ignore-db =performance_schema

replicate-ignore-db =test

2、重启备库

# /etc/init.d/mysql restart

3、查看备库状态，确认备机已经生效

mysql> show slave status;

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

| Waiting for master to send event |mysql-master.td.com | dbslave | 3306 | 60 | mysql-bin.000493 | 365980158 | relay-bin.000003 | 201404794 | mysql-bin.000493 | Yes | No | azkaban,zabbix |mysql,information_schema,performance_schema,test | | | | | 0 | | 0 | 365980158 | 201405161 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 |37f9e478-bb63-11e6-94a6-1402ec83df4c | /mysql/mysql/master.info | 0 | NULL | Slave has read all relaylog; waiting for more updates | 86400 | | | | | | | | 0 | | | |

1 row in set (0.00 sec)

mysql>

4、修改跟随主库(红色需要修改)

Mysql>CHANGEMASTER TO MASTER_HOST='mysqlmaster.td.com',MASTER_USER='dbslave',MASTER_PASSWORD='******',MASTER_LOG_FILE='mysql-bin.000493',MASTER_LOG_POS=164575684;

5、查看备库状态，

Mysql>show slave status;

6、启动备库

Mysql>start slave;

7、查看备库状态

mysql> show slave status;

| Waiting for master to send event |mysql-master.td.com | dbslave | 3306 | 60 | mysql-bin.000493 | 365980158 | relay-bin.000003 | 201404794 | mysql-bin.000493 | Yes | Yes | azkaban,zabbix |mysql,information_schema,performance_schema,test | | | | | 0 | | 0 | 365980158 | 201405161 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 |37f9e478-bb63-11e6-94a6-1402ec83df4c | /mysql/mysql/master.info | 0 | NULL | Slave has read all relaylog; waiting for more updates | 86400 | | | | | | | | 0 | | | |

1 row in set (0.00 sec)

mysql>

注意：红色部分，显示两个YES，表示主从同步正常。

主要看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master是否为0，0就是已经同步了

8、主库表状态修改取消只读。（在主机操作）

mysql>UNLOCK TABLES;

9、查看主库状态

mysql> show master status;

1 row in set (0.00 sec)

mysql>

10、查看备库状态

mysql> show slave status;

| Waiting for master to send event |mysql-master.td.com | dbslave | 3306 | 60 | mysql-bin.000493 | 367215481 | relay-bin.000003 | 202640117 | mysql-bin.000493 | Yes | Yes | azkaban,zabbix |mysql,information_schema,performance_schema,test | | | | | 0 | | 0 | 367215481 | 202640484 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 |37f9e478-bb63-11e6-94a6-1402ec83df4c | /mysql/mysql/master.info | 0 | NULL | Slave has read all relaylog; waiting for more updates | 86400 | | | | | | | | 0 | | | |

1 row in set (0.00 sec)

mysql>

11、修改完毕

五、检查验证

1、表同步测试：

选择一个同步的库，如：Azkaban

mysql>use azkaban

mysql>create table new (name char(20),phone char(20));

mysql>insert into new (’abc‘,’0532555555’);

在备库查询:

mysql>use azkaban

mysql>select * from new;

2、数据同步测试：

在主机上的一个库中修改一个表的内容，在备机查看，是否及时更新。

如果及时更新，表示主从同步正常，否则，根据错误情况进行处理。

进行测试：主库修改表，

mysql>use azkaban

mysql>update new set phone=‘0532888888’ ;

mysql>flush privileges;

在备库查询:

mysql>use azkaban

mysql>select * from new;

六、问题处理

问题1：MySQL主从数据库不同步，主机上有event，备机上没有同步event。

处理：发现从数据库中缺少event，检查导出的数据发现，默认导出语句不导出event，function和routine，需要添加参数 -R –E，其中 R表示导出function和routine，E表示导出event。

问题2：导入脚本执行时报错,ERROR 1418 (HY000):This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in itsdeclaration and binary logging is enabled (you *might* want to use the lesssafe log_bin_trust_function_creators variable

处理：如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数

set global log_bin_trust_function_creators = ON;

问题3：同步报错“在备机删除记录时找不到相应的记录”Could not execute Delete_rows event on table report.plaza; Can'tfind record in 'plaza', Error_code: 1032; handler error HA_ERR_END_OF_FILE;

处理：修改备机/etc/my.cnf配置文件。

slave_skip_errors = all #跳过所有错误，继续同步。

修改备机参数：

set global sql_slave_skip_counter =100; #当碰到错误时，跳过去。

问题4：同步报错因为主键，插入错误

处理：

修改备机参数：

set global sql_slave_skip_counter =1000; #当碰到错误时，跳过去。

启动SQL_THREAD进程

mysql>start sql_thread;

查看备机状态

mysql>show slave status;

重点关注：

Slave_IO_State

Waiting for master to send event #表示备机从主机的binlog读取日志并写入relaylog正常。

Slave_SQL_Running_State

Updating #表示备机正在更新relaylog到备库中，一般是在重新启动SQL_THREAD后会看待该状态

Slave has read all relay log; waiting formore updates #看到这个，表示备库已经和主库同步了。

Seconds_Behind_Master：

924 #表示被库落后主库的Postion数，当SQL_THREAD处于更新状态时，这个字段一般非0，当然，随着SQL_THREAD运行的时间，这个值会不断减小，直到为0；

Read_Master_Log_Pos：100310

读取Master binlog的位置：100310

Exec_Master_Log_Pos：100310

执行Master binlog的位置：100310

如果Read_Master_Log_Pos和Exec_Master_Log_Pos相等表示主备库同步。

七、常用工具：

查看binlog内容的工具：

mysqlbinlog --no-defaults --start-datetime="2017-04-14 18:00:00"--stop-datetime="2017-04-14 18:40:00" --start-position=1024490800--stop-position=1024498949 -d report /mysql/mysql/mysql-bin.000493 -s

参数说明：

--no-defaults：不使用默认参数，解决执行mysqlbinlog命令时报“mysqlbinlog: unknown variable 'default-character-set=utf8'”错误。

start-datetime=datetime：开始时间

stop-datetime=datetime：结束时间

start-position：开始位置

start-position：结束位置

-d report：数据库名

/mysql/mysql/mysql-bin.000493 :binlog文件名

-s：只显示语句

直接在mysql里看binlog内容：

mysql>show binlog events in ‘mysql-bin.000493‘ from 100 limit 0,3

命令说明：

show binlog events：查看binlog日志内容

in ‘mysql-bin.000493‘：查看mysql-bin.000493文件的内容

from 100：从position100开始看起

limit 0,3：查看范围，偏移量0,共3行,结合上面，从position100开始看3行，含position 100

八、附录

show slave status字段含义：

| Slave_IO_State

| Master_Host

| Master_User

| Master_Port

| Connect_Retry

| Master_Log_File

| Read_Master_Log_Pos

| Relay_Log_File

| Relay_Log_Pos

| Relay_Master_Log_File

| Slave_IO_Running

| Slave_SQL_Running

| Replicate_Do_DB

| Replicate_Ignore_DB

| Replicate_Do_Table

START SLAVE语法

START SLAVE [thread_type [, thread_type]... ]

START SLAVE [SQL_THREAD] UNTIL

MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

START SLAVE [SQL_THREAD] UNTIL

RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos

thread_type: IO_THREAD | SQL_THREAD

不含选项的START SLAVE会同时启动两个从属服务器线程。I/O线程从主服务器中读取查询，并把它们存储在中继日志中。SQL线程读取中继日志并执行查询。START SLAVE要求SUPER权限。

如果START SLAVE成功地启动了从属服务器线程，则会返回，不会出现错误。但是，即使在此情况下，也有可能出现这样的现象——服务器线程启动了，然后又停止了（例如，因为它们没有成功地连接到主服务器上，或者没有能读取二进制日志，或者出现了其它问题）。START SLAVE对此不会发出警告。您必须检查从属服务器的错误日志，查看是否有由从属服务器线程产生的错误消息，或者使用SHOW SLAVE STATUS检查它们是否运行正常。

您可以把IO_THREAD和SQL_THREAD选项添加到语句中，指明哪些线程将要启动。

可以添加一个UNTIL子句，指定从属服务器应启动并运行，直到SQL线程达到主服务器二进制日志中的一个给定点为止。当SQL线程达到此点时，它会停止。如果在该语句中指定了SQL_THREAD选项，则它只会启动SQL线程。否则，它会同时启动两个从属服务器线程。如果SQL线程正在运行，则UNTIL子句被忽略，并发布一个警告。

对于一个UNTIL子句，您必须同时指定一个日志文件名和位置。不要把主服务器和中继日志选项混合在一起。

UNTIL条件由一个后续的STOP SLAVE语句，或一个不包括UNTIL子句的START SLAVE语句，或一个服务器重启命令重新设置。

UNTIL子句对于调试复制操作是有用的，或者可用于促使复制操作继续，直到接近一个特定的点时为止，在此点，您想要避免让从属服务器复制一个语句。举例说明，如果在主服务上执行了一个不明智的DROP TABLE语句，您可以使用UNTIL来告知从属服务器，执行到此点就停止，不要再继续了。要查找该事件是什么，需对主服务器日志或从属中继日志使用mysqlbinlog，或通过使用SHOW BINLOG EVENTS语句。

如果您正在使用UNTIL，让从属服务器成段地处理已复制的查询，则建议您使用--skip-slave-start选项来启动从属服务器，以防止当从属服务器启动时，SQL线程运行。最好在一个选项文件中使用此选项，而不是在命令行中使用，这样，如果发生了意料外的服务器重新启动，它也不会被忘记。

SHOW SLAVE STATUS语句包括了输出字段。这些字段显示了UNTIL条件的当前值。

在以前版本的MySQL中，本语句被称为SLAVESTART。在MySQL 5.1中仍然接受这种用法，以便与以前版本兼容。但现在不赞成使用。

13.6.2.9. STOP SLAVE语法

STOP SLAVE [thread_type [, thread_type] ...]

thread_type: IO_THREAD | SQL_THREAD

用于中止从属服务器线程。STOP SLAVE要求SUPER权限。

和START SLAVE相似，本语句在使用时可以加IO_THREAD和SQL_THREAD选项，指明将被中止的线程。

在以前版本的MySQL中，本语句被称为SLAVESTOP。在MySQL 5.1中仍然接受这种用法，以便与以前版本兼容。但是现在不赞成使用。

Mysql配置为Master/Slave的常用维护命令

master端：

show master status;—查看状态：

show processlist; –查看slave下MySQL进程信息

reset master; #慎用，将清空日志及同步position

slave端：

CHANGE MASTER TOMASTER_LOG_FILE=’master.000019′;

show slave status;

show slave logs;

show processlist;

reset slave; #慎用，将清空slave配置信息、日志及同步position

在从服务器上跳过错误事件

mysql>stop slave;

mysql>set global sql_slave_skip_counter= n(跳过主服务器中的接下来的 n 个事件。此命令对于由语句引起的复制终止有效。仅在从服务器线程没运行的时候有效);

mysql>start slave;

mysql 主服务器中同步用户必须具有 SUPER ，RELOAD，REPLICATIONSLAVE 权限

当新加从服务器时，需要先在从库上 load data master; 保证和其他从库数据一致

set global sql_slave_skip_counter=n # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。

reset master #主机端运行,清除所有的日志，这条命令就是原来的flush master

reset slave #从机运行，清除日志同步位置标志，并重新生成master.info

虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下，

load table tblname from master

#从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值

load data from master #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值

change master to master_def_list #在线改变一些主机设置，多个用逗号间隔,比如

change master to

master_host=’master2.mycompany.com’,

master_user=’replication’,

master_password=’******

master_pos_wait() #从机运行

show master status #主机运行，看日志导出信息

show slave hosts #主机运行，看连入的从机的情况。

show slave status (slave)

show master logs (master)

show binlog events [ in 'logname' ] [ frompos ] [ limit [offset,] rows ]

purge [master] logs to ‘logname’ ; purge [master]logs before ‘date’

//显示所有本机上的二进制日志

mysql> SHOW MASTER LOGS;

//删除所有本机上的二进制日志

mysql> RESET MASTER;

//删除所有创建时间在binary-log.xxx之前的二进制日志

mysql> PURGE MASTER LOGS TO‘binary-log.xxx’;

//只保留最近6天的日志，之前的都删掉

find /var/intra -type f -mtime +6 -name“*.log” -exec rm -f {} \;

//用键盘左上角（也就是Esc下面）那个键包围起来，说明是命令。-1d是昨天，以此类推-1m是上个月等等

day=`/bin/date -v -1d +%Y%m%d`;

//给文件改名

mv xxx.log xxx-${day}.log;

//这里还要加上数据库的用户名密码，作用是更新日志（包括二进制日志和查询日志等等）

mysqladmin flush-logs

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 MASTERTOMASTER_HOST='10.1.1.75',MASTER_USER='replication',MASTER_PASSWORD='******',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复制模式。

cyf_2008

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql主从复制配置操作指导（主备）

大数据上azkaban、hive、spark等应用的元数据需要存储在MySQL里，通过MySQL的主备来保证高可用。
复制链接

扫一扫

专栏目录

Mysql主从复制配置操作指导（主备）

Mysql主从复制配置操作指导（主备）

一、准备工作：

二、修改主库

三、基准数据导入，在备机操作。

四、修改备库

五、检查验证

六、问题处理

七、常用工具：

八、附录

“相关推荐”对你有帮助么？