Mysql主从同步.docx

Mysql主从同步（Linux环境）配置介绍1．Linux(CentOS)2．Mysql5.0 以上3．A服务器： 主服务器master4．B服务器： 从服务器slaveMaster配置#mysql –u root –p mysqlgrant 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-binserver-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 varSlave配置设定/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.1、查看从机状态发现 Slave_SQL_Running :nomysql show slave status\GSlave_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