Mysql主从同步.docx

Mysql主从同步Mysql主从同步（Linux环境）0、 配置介绍1Linux(CentOS)2Mysql5.0 以上3A服务器： 主服务器master4B服务器： 从服务器slave1、 Master配置#mysql u root p mysql grant replication slave on *.* to slave identified by slave;mysqlflush privileges;mysqlexit;上面是Master开放一个账号slave密码slave给IP:有档案处理的权限# mysqladmin u root p shutdown 在A机器上修改/etc/f 在mysqld区段内加入参数 log-bin=mysql-bin server-id=1 #sql-bin-update-same binlog-do-db=vbbbinlog-ignore-db=mysql重启A服务器mysql此时因为有加入log-bin参数,因此开始有index产生了,在/var/lib/mysql目录下有.index档案纪录数据库的异动log.查看主机状态mysqlshow master status ;5.另开一个终端，打包主库cd /usr/local/mysql #mysql库目录tar zcvf var.tar.gz var=二.MySQL从服务器配置1、传输拿到主库数据包、解包# cd /usr/local/mysql# scp :/usr/local/mysql/var.tar.gz .# tar zxvf var.tar.gz2、查看修改var文件夹权限# chown -R mysql:mysql var2、 Slave配置设定/etc/f 在mysqld区段加入 master-host= master-user=slave master-password=slave master-port=3306 server-id=2 注意确保唯一性#master-connect-retry=60 预设重试间隔60秒 #replicate-do-db=vbb 告诉slave只做vbb数据库的更新 启动同步：mysqlstart slave;查看从机状态mysqlshow slave status ；3、 常见问题处理3.1、查看从机状态发现 Slave_SQL_Running :nomysql show slave statusGSlave_IO_Running: YesSlave_SQL_Running: NoLast_Errno: 1062.Seconds_Behind_Master:NULL原因：1.程序可能在slave上进行了写操作 2.也可能是slave机器重起后，事务回滚造成的.解决办法I：1.首先停掉Slave服务：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: YesSlave_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 :noMaster slave 复制错误Description：Slave_IO_Running:NOSlave_SQL_Running:YesSeconds_Behind_Master: NULL本人遇到的Slave_IO_Running:NO的情况有下面两种：1. 在配置slave同步时因为slave访问master没有权限导致；2. master上的mysql-bin.xxxxxx文件全被我误删除了；对于第一种情况，仔细检查数据库访问权限即可解决；对于第二种情况，下面稍微详细介绍一下：mysql show slave statusG* 1. row * Slave_IO_State: Master_Host: 1 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: NULL1 row in set (0.00 sec)rootslave mysql# tail /var/log/mysqld.log081223 15:51:50 InnoDB: Started; log sequence number 0 43655081223 15:51:51 Warning Neither -relay-log nor -relay-log-index were used; so replication may break whenthis 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 distribution081223 15:51:51 Note Slave SQL thread initialized, starting replication in log mysql-bin.000016 atposition 173, relay log /var/run/mysqld/mysqld-relay-bin.000007 position: 98081223 15:51:51 Note Slave I/O thread: connected to master slave1:3307, replication startedin log mysql-bin.000016 at position 173081223 15:51:51 ERROR Error reading packet from server: Could not find first log file name in binary logindex file ( server_errno=1236)081223 15:51:51 ERROR Got fatal error 1236: Could not find first log file name in binary log index filefrom master when reading data from binary log081223 15:51:51 Note Slave I/O thread exiting, read up to log mysql-bin.000016, position 173081223 15:51:58 Note Error reading relay log event: slave SQL thread was killed解决步骤：重启master库：service mysqld restartmysql 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 statusG* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 1 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