mysql主从配置干什么_Mysql主从配置(主从介绍、准备工作、配置主、配置从、测试主从同步)...

最新推荐文章于 2023-09-07 18:45:52 发布

何之源

最新推荐文章于 2023-09-07 18:45:52 发布

阅读量169

点赞数

文章标签： mysql主从配置干什么

本文链接：https://blog.csdn.net/weixin_29223603/article/details/114342869

版权

主从介绍

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：

在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。

Mysql主从复制的实现原理图大致如下(来源网络)：

MySQL之间数据复制的基础是二进制日志文件(binary log file)。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

MySQL主从是基于binlog的，主上须开启binlog才能进行主从。实现步骤：

主将更改操作记录到binlog里

从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里

从根据relaylog里面的sql语句按顺序执行

准备工作

1.主从数据库版本最好一致

2.主从数据库内数据保持一致

主数据库(yolks2)：192.168.248.129

从数据库(yolks1)：192.168.248.128

配置主

1.修改yolks2机器上的mysql配置文件，添加以下代码：

server-id=129 #此处暂定ip末端数字

log_bin=yolks2 #bin_log前缀名称，暂定和机器hostname一致

如下图所示：

2.重启mysql服务

/etc/init.d/mysqld restart

3.查看生成的文件:实现主从的根本

[root@yolks2 ~]# ls -lt /data/mysql/ |grep yolks2*

-rw-rw---- 1 mysql mysql 48988 9月 2 16:38 yolks2.err

-rw-rw---- 1 mysql mysql 5 9月 2 16:38 yolks2.pid

-rw-rw---- 1 mysql mysql 120 9月 2 16:38 yolks2.000001

-rw-rw---- 1 mysql mysql 16 9月 2 16:38 yolks2.index

4.备份zrlog数据库 : zrlog之前搭建测试博客测试数据库

mysqldump -uroot -p'mysql123' zrlog > /tmp/zrlog.sql

5.创建新数据库:test_copy_AB

mysql -uroot -p'mysql123' -e "create database test_copy_AB"

6.将步骤4备份的zrlog数据恢复到步骤5新创建的test_copy_AB数据库中

mysql -uroot -p'mysql123' test_copy_AB < /tmp/zrlog.sql

7.通过再次重复步骤3进行比较导入新库后，bin_log的文件大小有变化

导入后

[root@yolks2 ~]# !ls

ls -lt /data/mysql/ |grep yolks2*

-rw-rw---- 1 mysql mysql 11417 9月 2 16:50 yolks2.000001

导入前

[root@yolks2 ~]# ls -lt /data/mysql/ |grep yolks2*

-rw-rw---- 1 mysql mysql 120 9月 2 16:38 yolks2.000001

说明数据库已经将信息记录到yolks2.000001文件中去

8.创建用作同步数据的用户

grant replication slave on *.* to 'repl'@192.168.248.128 identified by 'repl123456';#此处ip指的是从机器地址

9.然后刷新所有的表，同时给数据库加上一把锁，阻止对数据库进行任何的写操作

flush tables with read lock;

10.显示File和Position信息，从机器设置会需要

mysql> show master status;

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

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

| yolks2.000001 | 11629 | | | |

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

1 row in set (0.00 sec)

11.将本地需要同步的数据库先做备份

mysqldump -uroot -p'mysql123' test > /tmp/test.sql #备份数据库到文件命令

配置从

1.修改数据库配置文件，然后添加从专属的server-id

server-id=128

如下图所示：

2.将主机器上yolks2的/tmp/目录下备份的sql文件同步过来

scp 192.168.248.129:/tmp/*.sql /tmp/

3.从机器上做别名

alias 'mysql=/usr/local/mysql/bin/mysql' #做mysql别名

alias 'mysqldump=/usr/local/mysql/bin/mysqldump' #做mysqldump别名

4.创建和主机器上yolks2对应的几个数据库

create database dbtest;

create database zrlog;

create database mysql2;

create database test_copy_AB;

5.然后将从主机器yolks2上同步过来的备份sql文件导入

mysql -uroot mysql2 < /tmp/mysql2.sql

mysql -uroot dbtest < /tmp/dbtest.sql

mysql -uroot zrlog < /tmp/zrlog.sql

mysql -uroot test_copy_AB < /tmp/test_copy_AB.sql

6.从机器进入mysql关闭同步

stop slave;

7.更改配置同步主机器的设置

选项：

master_log_file ：主机器执行show master status命令结果的File即可

master_log_pos ：主机器执行show master status命令结果的Position即可

效果如下：

mysql> change master to master_host='192.168.248.129',master_user='repl', master_password='repl123456', master_log_file='yolks2.000001', master_log_pos=11629；

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

8.开启同步

start slave;

9.查看是否可以同步的输出显示

mysql> show slave status\G

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

Slave_IO_State: Connecting to master

Master_Host: 192.168.248.129

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: yolks2.000001

Read_Master_Log_Pos: 11629

Relay_Log_File: yolks-001-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: yolks2.000001

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Replicate_Do_DB:

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: 11629

Relay_Log_Space: 120

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'repl@192.168.248.129:3306' - retry-time: 60 retries: 2

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 180902 18:25:48

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

Slave_IO_Running: Connecting

找到原因：需要主机器手动停止防火墙

systemctl disable firewalld

service firewalld stop

从机器重新尝试显示状态

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.248.129

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: aminglinux1.000002

Read_Master_Log_Pos: 120

Relay_Log_File: yolks-001-relay-bin.000003

Relay_Log_Pos: 285

Relay_Master_Log_File: aminglinux1.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

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: 120

Relay_Log_Space: 627

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 129

Master_UUID: deba07ec-aebd-11e8-90fc-000c2973b522

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

判断成功还是失败的关键参数：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

及一些error是否有数值不为0的情况

10.主机器yolks2上执行读写解除

unlock tables

测试主从同步

my.cnf其他几个忽略掉的配置参数

主机器：

binlog-do-db= //仅同步指定的库

binlog-ignore-db= //忽略指定库

从机器：

replicate_do_db=

replicate_ignore_db=

replicate_do_table=

replicate_ignore_table=

replicate_wild_do_table= //如aming.%, 支持通配符%

replicate_wild_ignore_table=

1.主机器登录mysql使用test_copy_AB数据库

mysql> show tables;

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

| Tables_in_test_copy_AB |

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

| comment |

| link |

| log |

| lognav |

| plugin |

| tag |

| type |

| user |

| website |

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

9 rows in set (0.00 sec)

2.将tag表清空

truncate table tag;

3.将数据增删改查查看从机变化进行测试

何之源

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql主从配置干什么_Mysql主从配置(主从介绍、准备工作、配置主、配置从、测试主从同步)...

主从介绍MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在...
复制链接

扫一扫