mysql主从同步配置超详细

雨点打透心脏的1/2处 2023-09-25 09:56 38阅读 0赞

## 如何配置两个MySQL数据库之间的主从同步功能 ##

MySQL从3.23.15版本以后提供数据库复制(replication)功能，利用该功能可以实现两个数据库同步、主从模式、互相备份模式的功能。本文档主要阐述了如何在linux系统中利用mysql的replication进行双机热备的配置。

操作系统：Linux2.6.23.1-42.fc8\#SMP(不安装XEN)

Mysql版本：5.0.45-4.fc8

设备环境：PC(或者虚拟机)两台

数据库同步复制功能的设置都在MySQL的配置文件中体现，MySQL的配置文件(一般是my.cnf)：在本环境下为/etc/my.cnf。

3.1设置环境：

IP的设置：

A主机IP：10.10.0.119

Mask：255.255.0.0

B主机IP：10.10.8.112

Mask：255.255.0.0

在IP设置完成以后，需要确定两主机的防火墙确实已经关闭。可以使用命令serviceiptablesstatus查看防火墙状态。如果防火墙状态

为仍在运行。使用serviceiptablesstop来停用防火墙。如果想启动关闭防火墙，可以使用setup命令来禁用或定制。

最终以两台主机可以相互ping通为佳。

3.2配置A主(master)B从(slave)模式

3.2.1配置A为master

、增加一个用户同步使用的帐号：

GRANTFILEON\*.\*TO‘backup’@'10.10.8.112'IDENTIFIEDBY‘1234’;

GRANTREPLICATIONSLAVEON\*.\*TO‘backup’@'10.10.8.112'IDENTIFIEDBY‘1234’;

赋予10.10.8.112也就是Slave机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATIONSLAVE的权限才可以。

、增加一个数据库作为同步数据库：

createdatabasetest;

、创建一个表结构：

createtablemytest(usernamevarchar(20),passwordvarchar(20));

、修改配置文件：

修改A的/etc/my.cnf文件，在my.cnf配置项中加入下面配置：

server-id=1\#Server标识

log-bin

binlog-do-db=test\#指定需要日志的数据库

、重起数据库服务：

servicemysqldrestart

查看server-id：

showvariablelike‘server\_id’；

mysql>showvariableslike'server\_id';

\+---------------+-------+

|Variable\_name|Value|

\+---------------+-------+

|server\_id|1|

\+---------------+-------+

1rowinset(0.00sec)

、用showmasterstatus/G命令看日志情况。

mysql>showmasterstatus/G

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

File:mysqld-bin.000002

Position:198

Binlog\_Do\_DB:test,test

Binlog\_Ignore\_DB:

1rowinset(0.08sec)

3.2.2配置B为slave

、增加一个数据库作为同步数据库：

createdatabasetest;

、创建一个表结构：

createtablemytest(usernamevarchar(20),passwordvarchar(20));

、修改配置文件：

修改B的/etc/my.cnf文件，在my.cnf配置项中加入下面配置：

server-id=2

master-host=10.10.0.119

master-user=backup\#同步用户帐号

master-password=1234

master-port=3306

master-connect-retry=60\#预设重试间隔秒

replicate-do-db=test\#告诉slave只做backup数据库的更新

、重起数据库服务：

servicemysqldrestart

查看server-id：

showvariableslike‘server\_id’；

mysql>showvariableslike'server\_id';

\+---------------+-------+

|Variable\_name|Value|

\+---------------+-------+

|server\_id|2|

\+---------------+-------+

1rowinset(0.00sec)

、用showslavestatus/G命令看日志情况。

mysql>showslavestatus/G

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

Slave\_IO\_State:Waitingformastertosendevent

Master\_Host:10.10.0.119

Master\_User:backup

Master\_Port:3306

Connect\_Retry:60

Master\_Log\_File:mysqld-bin.000001

Read\_Master\_Log\_Pos:98

Relay\_Log\_File:mysqld-relay-bin.000003

Relay\_Log\_Pos:236

Relay\_Master\_Log\_File:mysqld-bin.000001

Slave\_IO\_Running:Yes

Slave\_SQL\_Running:Yes

Replicate\_Do\_DB:test,test

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

Relay\_Log\_Space:236

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

1rowinset(0.01sec)

3.2.3验证配置

分别使用insert,delete,update在A主机进行增删改查数据库；查看B主机的数据库是否与A主机一致；若一致，则配置成功。

3.3双机互备模式

如果在A主机加入slave设置，在B主机加入master设置，则可以做B->A的同步。

、在A主机的配置文件中mysqld配置项加入以下设置：

master-host=10.10.8.112

master-user=backup

master-password=1234

replicate-do-db=test

master-connect-retry=10

、在B的配置文件中mysqld配置项加入以下设置：

log-bin

binlog-do-db=test

注意：当有错误产生时，\*.err日志文件同步的线程退出，当纠正错误后，要让同步机制进行工作，运行slavestart。

重起A、B机器，则可以实现双向的热备份。

四、常见问题及解决

、Slave机器的权限问题，不但要给slave机器File权限，还要给它REPLICATIONSLAVE的权限。

、在修改完Slave机器/etc/my.cnf之后，slave机器的mysql服务启动之前，记得要删除掉master.info

、在showmasterstatus或着showslavestatus不正常时，看看.err是怎样说的。

、Slave上Mysql的Replication工作有两个线程,I/Othread和SQLthread。I/O的作用是从

master3306端口上把它的binlog取过来(master在被修改了任何内容之后,就会把修改了什么写到自己的binlog等待slave更

新),然后写到本地的relay-log,而SQLthread则是去读本地的relay-log,再把它转换成本Mysql所能理解的语句，于是同步

就这样一步一步的完成.决定I/Othread的是/var/lib/mysql/master.info,而决定SQLthread的是/var

/lib/mysql/relay-log.info.

、启动slave，命令用startslave；重新启动用restartslave

![mysql主从同步配置 超详细\_mysql主从同步配置][mysql_ _mysql]

## mysql 主从配置 限制同步数据库 ##

一、概述MySQL从3.23.15版本以后提供数据库复制(replication)功能，利用该功能可以实现两个数据库同步、主从模式、互相备份模式的功能。本文档主要阐述了如何在linux系统中利用mysql的replication进行双机热备的配置。二、环境操作系统：Linux 2.6.23.1-42.fc8 \# SMP(不安装XEN)Mysql版本：5.0.45-4.fc8设备环境：PC(或者虚拟机)两台三、配置数据库同步复制功能的设置都在MySQL的配置文件中体现，MySQL的配置文件(一般是my.cnf)：在本环境下为/etc/my.cnf。3.1 设置环境：A主机 IP：10.10.0.119Mask：255.255.0.0B主机 IP：10.10.8.112Mask：255.255.0.0在IP设置完成以后，需要确定两主机的防火墙确实已经关闭。可以使用命令service iptables status查看防火墙状态。如果防火墙状态为仍在运行。使用service iptables stop来停用防火墙。如果想启动关闭防火墙，可以使用setup命令来禁用或定制。最终以两台主机可以相互ping通为佳。3.2 配置A主(master) B从(slave)模式3.2.1 配置A 为master、增加一个用户同步使用的帐号：GRANT FILE ON \*.\* TO ‘backup’@'10.10.8.112' IDENTIFIED BY ‘1234’;GRANTREPLICATION SLAVE ON \*.\* TO ‘backup’@'10.10.8.112' IDENTIFIED BY ‘1234’;赋予10.10.8.112也就是Slave机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权限才可以。、增加一个数据库作为同步数据库：create database test;、创建一个表结构：create table mytest (username varchar(20),password varchar(20));、修改配置文件：修改A的/etc/my.cnf文件，在my.cnf配置项中加入下面配置：server-id = 1\#Server标识binlog-do-db=test \#指定需要日志的数据库、重起数据库服务：service mysqld restart查看server-id：show variable like ‘server\_id’；

## rsync文件同步和mysql主从同步已经设置好？rsync文件 ##

分享个SVN安装配置的地址：

【~亲~如果我的回答对你有帮助，麻烦“有用”--“打赏”~~谢谢了呀~~】

![mysql主从同步配置 超详细\_mysql主从同步配置\_02][mysql_ _mysql_02]

## LAMP配置MYsql问题如何处理？ ##

0的beta,出现刚装5。1时的初始错误error 1045最终错误解决：

1、mysql -u root

2、use mysql

3、update user set password=old\_password('scf') where user='root' and host='localhost' ;

4、FLUSH PRIVILEGES;

这样Error 1045(28000) Access Denied for user 'root'@'localhost' (Using password:NO)错误1045就可以迎刃而解了！

考试大温馨提示：本内容来源于网络，仅代表作者个人观点，与本站立场无关，仅供您学习交流使用。

## 在配置mysql 的 主从数据库同步的时候，主数据库名称与从数据库名称一定要一样吗？ ##

用replication-rewrite-db= dbmaster > dbslave

5.0的版本这么设置，高点的版本不知是否支持1.主服务器：

\#master start

log-bin="d:/log/mysql/mysql\_log\_bin"

server-id=1

\#master end

2.从服务器：

\#slave start

log-bin="d:/log/mysql2/log-bin.log"

relay\_log="d:/log/mysql2/relay-log-bin"

\#从机id，区别于主机id

server-id=2

\#主机ip，供从机连接主机用

\#master-host=localhost

\#主机端口

\#master-port=3300

\#刚才为从机复制主机数据新建的账号

\#master-user=slave

\#刚才为从机复制主机数据新建的密码

\#master-password=654321

\#重试间隔时间10秒

\#master-connect-retry=10

\#需要同步的数据库

\#replicate-do-db=test

\#启用从库日志，这样可以进行链式复制

log-slave-updates

\#从库是否只读，0表示可读写，1表示只读

read-only=1

\#只复制某个表

\#replicate-do-table=tablename

\#只复制某些表(可用匹配符)

\#replicate-wild-do-table=tablename%

\#只复制某个库

\#replicate-do-db=dbname

\#不复制某个表

\#replicate-ignore-table=tablename

\#不复制某些表

\#replicate-wild-ignore-table=tablename%

\#不复制某个库

\#replicate-ignore-db=dbname

\#slave end

3.对从服务器制定主服务器使用change master 语句

注意：1.一定要在主服务器上创建一个可以执行replication的用户

2.该用户名在从服务器上可远程登录到主服务器。

3.开启mysql的log-bin日志功能

![mysql主从同步配置 超详细\_mysql主从同步配置\_03][mysql_ _mysql_03]

## mysql主从同步 如何同步已有的数据， ##

\--主机开两个窗口，一个进入mysql，一个是shell

\--主机阻断写操作

mysql> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> SHOW MASTER STATUS;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

\--另外一个窗口导出主机数据库

mysqldump -u root -p123456 --opt -R openser > openser20121203.sql

\--刚才的窗口主机解锁

mysql> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

mysql>

\--打包数据文件到从机

drop database openser;

create database openser;

mysql -u root -p123456 openser < openser20121127.sql

\--从机操作

SLAVE STOP;

reset slave;

CHANGE MASTER TO MASTER\_HOST='192.168.21.26',

MASTER\_USER='repl\_user',

MASTER\_PASSWORD='123456',

MASTER\_LOG\_FILE='mysql-bin.000002',

MASTER\_LOG\_POS=1529881;

start slave;

show slave status\\G方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况

stop slave;

\#表示跳过一步错误，后面的数字可变

set global sql\_slave\_skip\_counter =1;

start slave;

之后再用mysql> show slave status\\g 查看：

slave\_io\_running: yes

slave\_sql\_running: yes

ok，现在主从同步状态正常了。。。

方式二：重新做主从，完全同步

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况

解决步骤如下：

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_ _mysql]: https://img0.baidu.com/it/u=2103945634,1120259169&fm=26&fmt=auto
[mysql_ _mysql_02]: https://img2.baidu.com/it/u=1997573673,3169676873&fm=26&fmt=auto
[mysql_ _mysql_03]: https://img1.baidu.com/it/u=3444328156,2357816058&fm=26&fmt=auto