mysql数据库实时同步配置与常见问题解决方法.doc

Mysql同步配置前言：测试成功为redhat AS5 64位系统平台，Mysql sever为mysql-5.1.42-linux-x86_64-glibc23 版本一、主数据库服务器配置修改mysql的配置文件（对于windows就是MySQL安装目录下的my.ini文件，对于linux通常就是 /etc/f文件）,在配置文件中加入（或修改）rootdrbd1 mysql# vi /etc/fskip-name-resolve /选项可以禁用dns解析，但是，这样不能在mysql的授权表中使用主机名了，只能使用IPskip-grant-tables /如果你在f中的有添加“skip-grant-tables”，那么任何的帐号用任何的密码（当然也包括空）都可以登录到mysql数据库了max_connections=1000 / mysql的最大连接数默认是100, 这个数值对于并发连接很多的数据库应用是远远不够的, 当连接请求大于默认连接数后，就会出现无法连接数据库的错误，因此我们需要把它适当调大一些max_connect_errors=200 /出现某台host连接错误次数等于max_connect_errors（默认10） ，主机host_name再次尝试时被屏蔽。 server-id = 1 log-bin=mysql-bin binlog-do-db=dbname -要同步的数据库名称 如果同步所有数据库就不用设置binlog-ignore-db=dbname -不要同步的数据库名称如下：binlog-do-db = BJ_Erpbinlog-ignore-db = information_schemabinlog-ignore-db = CQ_Erp binlog-ignore-db = HK_Erpbinlog-ignore-db = mysqlbinlog-ignore-db = testbinlog-ignore-db = vpopmail注：MySQL进行主从复制是通过二进制的日志文件来进行，所以我们必须开启MySQL的日志功能，即我们上面的log-bin，同时每一台数据库服务器都需要指定一个唯一的server-id，通常主数据库服务器我们指定为1。注创建数据库并整理：create database Lottery CHARACTER SET utf8 COLLATE utf8_general_ci /创建数据库并设置为uft8格式给主数据库授予一个可以进行复制的用户rootdrbd1 mysql# mysql -u root pmysql grant replication slave on *.* to fyx5 identified by fyxadmin ;Query OK, 0 rows affected (0.00 sec)replication slave MySQL数据库中表示复制的权限名称 repuser 从数据库服务器登陆到主数据库服务器时用到的用户名称 host 5 从数据库IP地址 fyxadmin 登陆密码 如果上述权限设置后，无法同步，可将其改为mysql grant select,insert,update,delete,replication slave on *.* to fyx5 identified by fyxadmin; 配置完上述步骤后，就可以启动主数据库了。二、从数据库配置修改配置文件rootdrbd1 mysql# vi /etc/fskip-name-resolve /选项可以禁用dns解析，但是，这样不能在mysql的授权表中使用主机名了，只能使用IPskip-grant-tables /如果你在f中的有添加“skip-grant-tables”，那么任何的帐号用任何的密码（当然也包括空）都可以登录到mysql数据库了max_connections=1000 / mysql的最大连接数默认是100, 这个数值对于并发连接很多的数据库应用是远远不够的, 当连接请求大于默认连接数后，就会出现无法连接数据库的错误，因此我们需要把它适当调大一些max_connect_errors=200 /出现某台host连接错误次数等于max_connect_errors（默认10） ，主机host_name再次尝试时被屏蔽。 server-id=2 log-bin=mysql-bin -在从服务器上启动日志记录，不是必须，但是官方建议 master-host=主机 -主数据库服务器的IP地址 master-user=用户名 -执行复制的用户名称，就是grant的用户，即fyx master-password=密码 -复制用户的密码，就是grant的用户密码，即fyxadmin master-port=端口 -主数据库服务器的端口，默认是3306 master-connect-retry = 60 -与主服务器失去连接时，从服务器重新尝试连接的时间间隔。 replicate-do-db=dbname -需要同步的数据库 如果同步所有数据库就不用设置 replicate-ignore-db=dbname -不需要同步的数据库如下：replicate-do-db = BJ_Erpreplicate-ignore-db = information_schemareplicate-ignore-db = mysqlreplicate-ignore-db = test现在可以重启从数据库，启动后，还需要启动复制线程先在主查看：rootdrbd1 mysql# mysql -u root p 输入密码进入mysqlmysql show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000004 | 106 | | | +-+-+-+-+1 row in set (0.00 sec)然后在从设置mysql slave stop;mysqlreset slave;mysqlchange master tomaster_host=4,master_port=3306,master_user=fyx,master_password=fyxadmin,master_log_file=mysql-bin.000004,master_log_pos=106;Query OK, 0 rows affected (0.03 sec)mysql slave start;Query OK, 0 rows affected (0.01 sec)change master to master_log_file=mysql-bin.000004,master_log_pos=106;概述 在做web应用系统中，如果数据库出现了性能瓶颈，而你又是使用的MySQL数据库，那么就可以考虑采用数据库集群的方式来实现查询负载了。因为一般来讲一个系统中数据库的查询操作比更新操作要多的多，因此通过多台查询服务器将数据库的查询分担到不同的查询数据库从而提高数据库的查询效率。 MySQL数据库支持数据库的主从复制功能，使用主数据库进行数据的插入、删除与更新操作，而从数据库则专门用来进行数据库查询，从数据库还可以建立多台，通过负载均衡来实现查询，这样就可以将更新操作与查询操作分离到不同的数据库上，从而提高查询的效率，降低系统的负载。三、启动与监控1、监控主数据库服务器运行于主数据库mysql show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000004 | 106 | | | +-+-+-+-+1 row in set (0.00 sec) 其中File是表示日志记录的文件，而Position则是表示当前日志在文件中的位置，这个也是从数据库服务器上执行复制操作必须的标识，后面的两个字段分别表示要记录的数据库名称和不需要记录的数据库名称，我们也可以在配置文件中进行配置。2、监控从数据库服务器运行于从数据库mysql show slave statusG;mysql show slave statusG;* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 5 Master_User: synbj Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 204261590 Relay_Log_File: mail-relay-bin.000221 Relay_Log_Pos: 15149444 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: BJ_Erp Replicate_Ignore_DB: information_schema,mysql,test 红色的部分，分别表示的是Master_Log_File和Read_Master_Log_Pos，即主数据库服务器上的日志文件和要读取的主数据库服务器上的日志的位置，通常这个Read_Master_Log_Pos是和主数据库服务器上的Position是一致的（同步以后），如果从数据库服务器还没有同步完毕，那么这个值通常比主数据库服务器上的要小。 注：如果从数据库服务器在同步的过程中出现了问题，那么我们可以通过reset slave来重置从数据库服务器的复制线程。从数据库常用命令：Slave start; -启动复制线程Slave stop; -停止复制线程Reset slave; -重置复制线程Show slave status; -显示复制线程的状态Show slave statusG; -显示复制线程的状态(分行显示)Show master statusG; -显示主数据库的状态(分行显示)Showmaster logs -显示主数据库日志，需在主数据库上运行Change master to; -动态改变到主数据库的配置Show processlist -显示有哪些线程在运行四、常见问题与解答1、现象：修改完配置后，重启mysql，提示MySQL manager or server PID file could not be found 解决：查找进程中的mysql，kill掉rootdrbd1 mysql# service mysql restart rootdrbd1 mysql# ps -aux|grep myslqrootdrbd1 mysql# kill 进程号rootdrbd1 mysql# service mysql start 注：mysql.sock文件在mysql启动后才会生成，位置为/tmp下或/var/lib/mysql/下 2、现象：从数据库无法同步，show slave status显示Slave_SQL_Running为No,Seconds_Behind_Master为null 原因：A.程序可能在slave上进行了写操作 B也可能是slave机器重起后，事务回滚造成的. 解决：方法一： mysql slave stop; mysql set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql slave start; 方法二：查看Slave状态：mysql 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)#我执行的