mysql主从同步配置与不同步问题整理

本文章详细的介绍了mysql主从同步配置，包括了linux和windows中的主从配置及可能出现问题的解决办法。

linux中主从同步
主服务器IP: 250.110.120.119
从服务器IP: 110.120.119.250

1.在主服务器新建一个用户，给从服务器同步使用。

 

代码如下	复制代码
GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'110.120.119.250.' IDENTIFIED BY 'tongbu';

意思是新建一个用户名为tongbu 密码为tongbu 具有所有操作权限的帐号。

2.修改主服务器my.cnf文件
其实这一步可以省略，因为保持默认的配置文件就可以了。
/*
添加
server-id=1
并开启log-bin二进制日志文件
log-bin=mysql-bin
注:需要把默认的server-id=1去掉
*/

3.重启主服务器mysql数据库

4.进入mysql界面执行下面命令 获取日志文件名和偏移量

 

代码如下	复制代码
show master status; 显示如: mysql> show master status; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000010 | 106 | | | +——————+———-+————–+——————+ 1 row in set (0.00 sec)

得到的日志文件名为 mysql-bin.000010 偏移量为106

5.修改从服务器的my.cnf
添加一个
server-id=2
注:需要把默认的server-id=1去掉

6.重启从服务器mysql数据库

7.对从数据库进行设置，@@@这是最最重要的地方。

 

代码如下	复制代码
change master to master_host='250.110.120.119', master_user='tongbu', master_password='tongbu', master_log_file='mysql-bin.000010', master_log_pos=106;

8.启动从服务器

start slave;
9.执行下面命令 查看进程

show processlistG
显示如下

代码如下

复制代码

mysql> show processlistG *************************** 1. row *************************** Id: 4 User: root Host: localhost db: test Command: Query Time: 0 State: NULL Info: show processlist *************************** 2. row *************************** Id: 5 User: system user Host: db: NULL Command: Connect Time: 1773 State: Waiting for master to send event Info: NULL *************************** 3. row *************************** Id: 6 User: system user Host: db: NULL Command: Connect Time: 1630 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL 3 rows in set (0.00 sec)

10.查看从服务器状态

 

代码如下

复制代码

show slave statusG 显示如下 mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 250.110.120.119 Master_User: tongbu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 281 Relay_Log_File: up2-relay-bin.000002 Relay_Log_Pos: 426 Relay_Master_Log_File: mysql-bin.000010 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: 281 Relay_Log_Space: 579 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: 1 row in set (0.00 sec)

测试:
1、在主服务器test数据库中创建user表

代码如下	复制代码
mysql>use test; mysql>create table user(id int);

2、在从服务器中查看user表

代码如下	复制代码
mysql>use test; mysql> show tables like ‘user’; +———————-+ | Tables_in_test(user) | +———————-+ | user | +———————-+ 1 row in set (0.00 sec)

说明主从数据同步成功。

windows主从同步操作方案

负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制 日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。
       
环境：
A、B的MySQL数据库版本同为5.0.18
A：
操作系统：Windows 2003
IP地址：10.100.0.100
B：
操作系统：Windows 2000 server
的IP地址：10.100.0.200

配置过程：
1、在A的数据库中建立一个备份帐户，命令如下：

代码如下	复制代码
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@’10.100.0.200’ IDENTIFIED BY ‘1234’;

建立一个帐 户backup，并且只能允许从10.100.0.200这个地址上来登陆，密码是1234。

2、因为 mysql版本新密码算法不同，所以进入mysql下，输入：

代码如下	复制代码
set password for'backup'@'10.100.0.200'=old_password('1234');

3、关停A服务器，将A中的数据拷贝到B服务器中，使得A和B中的数据同步，并且确保在全部设置操作结束前，禁止在A和B服务器中进行写操作，使得两数据 库中的数据一定要相同！

4、对A服 务器的配置进行修改，打开mysql/my.ini文件，在[mysqld]下面添加如下内容：

代码如下	复制代码
server-id=1 log-bin=c:log-bin.log

server- id：为主服务器A的ID值
log-bin：二进制变更日值

4、重启A 服务器，从现在起，它将把客户堆有关数据库的修改记载到二进制变更日志里去。

5、关停B 服务器，对B服务器锦熙配置，以便让它知道自己的镜像ID、到哪里去找主服务器以及如何去连接服务器。最简单的情况是主、从服务器分别运行在不同的主机上 并都使用着默认的TCP/IP端口，只要在从服务器启动时去读取的mysql/my.ini文件里添加以下几行指令就行了。

代码如下	复制代码
[mysqld] server-id=2 master-host=10.100.0.100 master-user=backup master-password=1234 //以下内容为可选 replicate-do-db=backup

server- id：从服务器B的ID值。注意不能和主服务器的ID值相同。
master-host：主服务器的IP地址。
master-user：从服务器连接主服务器的帐号。
master-password：从服务器连接主服务器的帐号密码。
replicate-do-db：告诉主服务器只对指定的数据库进行同步镜像。

6、重启从 服务器B。至此所有设置全部完成。更新A中的数据，B中也会立刻进行同步更新。如果从服务器没有进行同步更新，你可以通过查看从服务器中的 mysql_error.log日志文件进行排错。

8、查看日志一些命令
1, show master statusG;
           在这里主要是看log-bin的文件是否相同。
    show slave statusG;
    在这里主要是看:
                   Slave_IO_Running=Yes
                   Slave_SQL_Running=Yes
   如果都是Yes,则说明配置成功.

2,在 master上输入show processlistG;
 

代码如下	复制代码
mysql> SHOW PROCESSLISTG      *************************** 1. row ***************************        Id: 2        User: root        Host: localhost:32931        db: NULL        Command: Binlog Dump        Time: 94        State: Has sent all binlog to slave; waiting for binlog to          be updated        Info: NULL

   如果出现Command: Binlog Dump,则说明配置成功.

MySQL主从不同步问题解决

1.首先停掉Slave服务：

mysql> slave stop2.到主服务器上查看主机状态：
记录File和Position对应的值。

代码如下

复制代码

mysql> show master status; +------------------+-----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+-----------+--------------+------------------+ | mysql-bin.000020 | 135617781 | | | +------------------+-----------+--------------+------------------+ 1 row in set (0.00 sec)3.到slave服务器上执行手动同步： mysql> change master to > master_host='master_ip', > master_user='user', > master_password='pwd', > master_port=3307, > master_log_file='mysql-bin.000020', > master_log_pos=135617781; 1 row in set (0.00 sec) mysql> slave start; 1 row in set (0.00 sec)再次查看slave状态发现： Slave_IO_Running: Yes Slave_SQL_Running: Yes

...
Seconds_Behind_Master: 0我们知道，因为DATA REPLICATION机制完全是基于在主上执行的增量SQL要被传播到辅服务器上，并且被成功运行。这就势必要求：在运行此机制前，主辅数据库中数据是一致的；以及在运行此机制中，辅数据库禁止来自其他的SQL（非主上传播过来SQL）的写操作。但是在运行中仍然可能遇到不一致的产生，这会导致通信无法正常继续下去。因此一旦主从出现问题，首先应该解决同步位置的问题，修复丢失的数据

MySQL主从不同步问题解决

问题 描述：主从复制在某个时刻，从服务器同步失败，并给出报错消息。

消息内容：基本意思是缺少某个库的某个表

原因：主从服务长时间不同步，主库已经生成很多新的表或者库，而从库却没有。

操作过程：
（1）登陆主服务器，查看主服务器的状态

代码如下	复制代码
mysql>show master status；

（2）登陆从服务器，执行同步操作。[code]

代码如下	复制代码
mysql>stop slave;mysql > change master to ...(此处省略)；mysql > start slave;

（3）从服务器上查看状态[code]

代码如下	复制代码
mysql > show slave statusG

看报错信息少什么表或库，少什么就直接从主服务器通过scp复制，然后重复过程（1）~（3）直到不报错为止