Mysql主从同步
0、配置介绍
1．Linux(CentOS)
2．Mysql5.0 以上
3．A服务器： 192.168.1.2 主服务器master
4．B服务器： 192.168.1.3 从服务器slave
1、Master配置
#mysql –u root –p
mysql> GRANT REPLICATION SLAVE ON *.* TO slave@192.168.1.3 IDENTIFIED BY ‘slave’;
mysql>FLUSH PRIVILEGES;
mysql>exit;
上面是Master开放一个账号slave密码slave给IP:192.168.1.3有档案处理的权限
# mysqladmin –u root –p shutdown
在A机器上修改/etc/my.cnf
在[mysqld]区段内加入参数
log-bin
server-id=1
#sql-bin-update-same
binlog-do-db=vbb
binlog-ignore-db=mysql
binlog-ignore-db=test
<注意>注释掉原文件中的log-bin
重启A服务器mysql
此时因为有加入log-bin参数,因此开始有index产生了,在/var/lib/mysql目录下有.index档案纪录数据库的异动log.
查看主机状态
mysql>show master status \G;
2、Slave配置
设定/etc/my.cnf
在[mysqld]区段加入
master-host=192.168.1.2
master-user=slave
master-password=slave
master-port=3306
server-id=2 注意确保唯一性
master-connect-retry=60 预设重试间隔60秒
replicate-do-db=vbb 告诉slave只做vbb数据库的更新
启动同步：
mysql>start slave;
查看从机状态
mysql>show slave status \G；
3、常见问题处理
3.1、查看从机状态发现 Slave_SQL_Running :no
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
....
Seconds_Behind_Master:NULL
原因：
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的.
解决办法I：
1.首先停掉Slave服务：slave stop
2.到主服务器上查看主机状态：
记录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
解决办法II：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
自己的使用体会:方法一是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删除记录同步也会有一些错误信息,不会影响使用.方法二不一定会有效果.
3.2、查看从机状态发现 Slave_IO_Running :no
Master slave 复制错误
Description：
Slave_IO_Running:NO
Slave_SQL_Running:Yes
Seconds_Behind_Master: NULL
本人遇到的Slave_IO_Running:NO的情况有下面两种：
1. 在配置slave同步时因为slave访问master没有权限导致；
2. master上的mysql-bin.xxxxxx文件全被我误删除了；
对于第一种情况，仔细检查数据库访问权限即可解决；
对于第二种情况，下面稍微详细介绍一下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.3.21
Master_User: slave
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000016
Read_Master_Log_Pos: 173
Relay_Log_File: mysqld-relay-bin.000008
Relay_Log_Pos: 98
Relay_Master_Log_File: mysql-bin.000016
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: br> Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 173
Relay_Log_Space: 98
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
1 row in set (0.00 sec)
[root@slave mysql]# tail /var/log/mysqld.log
081223 15:51:50 InnoDB: Started; log sequence number 0 43655
081223 15:51:51 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when
this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-
log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
081223 15:51:51 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log' socket: '/var/lib/mysql/mysql.sock' port: 3307 Source distribution
081223 15:51:51 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000016' at
position 173, relay log '/var/run/mysqld/mysqld-relay-bin.000007' position: 98
081223 15:51:51 [Note] Slave I/O thread: connected to master 'slave@192.168.3.21:3307', replication started
in log 'mysql-bin.000016' at position 173
081223 15:51:51 [ERROR] Error reading packet from server: Could not find first log file name in binary log
index file ( server_errno=1236)
081223 15:51:51 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file'
from master when reading data from binary log
081223 15:51:51 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000016', position 173
081223 15:51:58 [Note] Error reading relay log event: slave SQL thread was killed
解决步骤：
重启master库：service mysqld restart
mysql> show master status;
+------------------+----------+--------------+------------------+
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |
98 | | |
+------------------+----------+--------------+------------------+
mysql> slave stop;
mysql> change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;
mysql> slave start;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.21
Master_User: slave
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
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: 235
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
1 row in set (0.00 sec)
转载于:https://blog.51cto.com/yang12313/567045
295
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
