2014年8月 其他数据库开发大版内专家分月排行榜第三
2010年 总版技术专家分年内排行榜第二
2009年 总版技术专家分年内排行榜第三
本帖子已过去太久远了，不再提供回复功能。用户名：aaron428
文章数：108
访问量：64603
注册日期：
阅读量：1297
阅读量：3317
阅读量：428144
阅读量：1116148
51CTO推荐博文
Mysql主从半同步及双机热备Mysql主从同步配置（mysql版本5.5以上、keepalived软件实现双机热备）：基础：两台机子& 主服务器：192.168.1.130 从服务器：192.168.1.1311、修改mysql配置文件my.cnf（以下配置项，文件中有则根据下边要求修改，没有则自行添加）：主：server-id = 1(主数据库一般都是id为1)log-bin=mysql-bin (必须的)binlog_format=mixed （必须的，推荐类型为mixd）expire_logs_days=5 （为避免日志文件过大，设置过期时间为5天）binlog-ignore-db = mysql （忽略同步的数据库，也不记入二进制日志，可列多行)replicate-do-db = test （需要同步的数据库，记入二进制日志，可列多行）从：server-id = 2log-bin=mysql-binbinlog_format=mixedexpire_logs_days=5注1：MySQL进行主从复制是通过二进制的日志文件来进行，所以我们必须开启MySQL的日志功能，即我们上面的log-bin，同时每一台数据库服务器都需要指定一个唯一的server-id，通常主数据库服务器我们指定为1。注2:Mysql5.5版本以上不再支持master的用法，比如5.1中可用的（master-user = repl；master-password = repl；master-port = 3307）这样的语法已经失效了，如果你使用了，还会报错。（我在这里载了跟头，检查mysql日志/usr/local/db/mysql/ 127.0.0.1.err――您的路径也许会稍有不同――才发现错误）如果你不小心配置了这些参数，MySQL服务器将无法正常启动错误提示为：[ERROR] /usr/local/mysql/bin/mysqld: unknownvariable ‘master-host=192.168.1.130’，正确的办法如上文所写。配置完成之后，将主从两机的mysql服务重新启动一下。2、Mysql主(1.130)，执行：GRANT all privileges on *.* to 'test' @'192.168.1.131' IDENTIFIED BY '123456'(授予slave账号对所有数据库享有slave权限，密码为slave777)；3、查看Mysql主 的状态：执行：show master status；这时会看到master数据库所处的位置，记录下来：| File | Position || mysql-bin.000011 | 383 |4、Mysql从：执行：slave stop；执行：change master tomaster_host='192.168.1.130', master_port=3306, master_user=’test’,master_password=’123456’,master_log_file=’mysql-bin.000011′, master_log_pos=383;执行：slave start；5、查询slave的状态：mysql& show slavestatus \G；看下slave的状态:Master_Log_File: mysql-bin.000011 (和主mysql一致)Read_Master_Log_Pos: 383 （和主mysql一致）Slave_IO_Running: Yes （读写）Slave_SQL_Running: Yes （数据库状态）还要注意状态中是否有error，如果没有的话，就差不多了。6、测试数据库是否能够同步：略；7、如果Slave复制失败，你可以根据错误信息进行修正，然后执行mysql&mysql&就可以把原来应该复制过来的数据都复制过来8、几个常用的命令：S --启动复制线程S --停止复制线程R --重置复制线程S --显示复制线程的状态Show slave status\G; --显示复制线程的状态(分行显示)Show master status\G; --显示主数据库的状态(分行显示)Show master logs --显示主数据库日志，需在主数据库上运行C --动态改变到主数据库的配置Show processlist --显示有哪些线程在运行&由于Mysql的复制都是基于异步进行的，在特殊情况下不能保证数据的成功复制，因此在mysql 5.5之后使用了来自google补丁，可以将Mysql的复制实现半同步模式。所以需要为主服务器加载对应的插件。在Mysql的安装目录下的lib/plugin/目录中具有对应的插件semisync_master.so，semisync_slave.so&在Master和Slave的mysql命令行运行如下命令：&Master：mysql& install pluginrpl_semi_sync_master soname 'semisync_master.so';&mysql& set globalrpl_semi_sync_master_enabled = 1;&mysql& set globalrpl_semi_sync_master_timeout = 1000;&mysql& show variables like '%semi%';+------------------------------------+-------+| Variable_name&&&&&&&&&&&&&&&&&&&&& | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled&&&&&& | ON&&&|| rpl_semi_sync_master_timeout&&&&&& | 1000&|| rpl_semi_sync_master_trace_level&& | 32&&&|| rpl_semi_sync_master_wait_no_slave |ON&&& |+------------------------------------+-------+&Slave：mysql& install pluginrpl_semi_sync_slave soname 'semisync_slave.so';&mysql& set globalrpl_semi_sync_slave_enabled = 1;&mysql&mysql&mysql& show variables like '%semi%';+---------------------------------+-------+| Variable_name&&&&&&&&&&&&&&&&&& | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled&&&& | ON&&&|| rpl_semi_sync_slave_trace_level | 32&&& |+---------------------------------+-------+&检查半同步是否生效：Master：mysql& show global status like'rpl_semi%';+--------------------------------------------+-------+| Variable_name&&&&&&&&&&&&&&&&&&&&&&&&&&&&& | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients&&&&&&& &&&&&&&| 1&&&&||Rpl_semi_sync_master_net_avg_wait_time&&&&| 0&&&& || Rpl_semi_sync_master_net_wait_time&&&&&&&& | 0&&&&|| Rpl_semi_sync_master_net_waits&&&&&&&&&&&& | 0&&&& || Rpl_semi_sync_master_no_times&&&&&&&&&&&&& | 0&&&& || Rpl_semi_sync_master_no_tx&&&&&&&&&&&&&&&& | 0&&&& || Rpl_semi_sync_master_status&&&&&&&&&&&&&&& | ON&&& ||Rpl_semi_sync_master_timefunc_failures&&&&| 0&&&& ||Rpl_semi_sync_master_tx_avg_wait_time&&&&&| 0&&&& || Rpl_semi_sync_master_tx_wait_time&&&&&&&&& | 0&&&& || Rpl_semi_sync_master_tx_waits&&&&&&&&&&&&& | 0&&&& ||Rpl_semi_sync_master_wait_pos_backtraverse | 0&&&& || Rpl_semi_sync_master_wait_sessions&&&&&&&& | 0&&&&|| Rpl_semi_sync_master_yes_tx&&&&&&&&&&&&&&& | 0&&&& |+--------------------------------------------+-------+说明半同步成功。&让半同步功能在MySQL每次启动都自动生效，在Master和Slave的</f中编辑：Master：[mysqld]&rpl_semi_sync_master_enabled=1&rpl_semi_sync_master_timeout=1000&&&& #1秒&Slave：[mysqld]&rpl_semi_sync_slave_enabled=1&&也可通过设置全局变量的方式来设置是否启动半同步插件：Master：mysql& set globalrpl_semi_sync_master_enabled=1取消加载插件mysql& uninstall pluginrpl_semi_sync_&Slave：mysql& set globalrpl_semi_sync_slave_enabled = 1;mysql& uninstall pluginrpl_semi_sync_&安装keepalived软件实现双机热备Keepalived是Linux下面实现VRRP备份路由的高可靠性运行件。基于Keepalived设计的服务模式能够真正做到主服务器和备份服务器故障时IP瞬间无缝交接。&主服务器#tar zxvf keepalived-1.2.2.tar.gz#cd keepalived-1.2.2#mkdir /usr/local/keepalived#./configure --prefix=/usr/local/keepalived#make#make install#cp /usr/local/keepalived/sbin/keepalived /usr/sbin/#cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/#cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/修改keepalived配置文件vim /usr/local/keepalived/etc/keepalived/keepalived.conf（删除多余内容，仅留以下部分）! Configuration File for keepalivedglobal_defs {&notification_email {&&}&notification_email_from &smtp_server 127.0.0.1&smtp_connect_timeout 30&router_id LVS_DEVEL}vrrp_instance VI_1 {&state MASTER&interface eth0&virtual_router_id 51&priority 100&advert_int 1&authentication {&auth_type PASS&auth_pass chtopnet&}&virtual_ipaddress {&192.168.93.132&}}修改/etc/sysconfig/keepalived的内容：KEEPALIVED_OPTIONS="-D -f/usr/local/keepalived/etc/keepalived/keepalived.conf"#service keepalived start从服务器#tar zxvf keepalived-1.2.2.tar.gz#mkdir /usr/local/keepalived#cd keepalived-1.2.2#./configure --prefix=/usr/local/keepalived#make#make install#cp /usr/local/keepalived/sbin/keepalived /usr/sbin/#cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/#cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/#vim /usr/local/keepalived/etc/keepalived/keepalived.conf（删除多余内容，仅留以下部分）! Configuration File for keepalivedglobal_defs {&notification_email {&&}&notification_email_from &smtp_server 127.0.0.1&smtp_connect_timeout 30&router_id LVS_DEVEL}vrrp_instance VI_1 {&state BACKUP&interface eth0&virtual_router_id 51&priority 99&advert_int 1&authentication {&auth_type PASS&auth_pass chtopnet&}&virtual_ipaddress {&192.168.93.132&}}修改/etc/sysconfig/keepalived的内容：KEEPALIVED_OPTIONS="-D -f/usr/local/keepalived/etc/keepalived/keepalived.conf"#service keepalived start&设置定时任务脚本，定时检测mysql状态：Vicheck_mysql_helth.sh#!/bin/bashMYSQL=/usr/local/mysql/bin/mysqlMYSQL_HOST=127.0.0.1MYSQL_USER=rootMYSQL_PASSWORD=aibei1010CHECK_TIME=3#mysql& is working MYSQL_OK is 1 , mysql downMYSQL_OK is 0MYSQL_OK=1functioncheck_mysql_helth (){$MYSQL-u$MYSQL_USER -p$MYSQL_PASSWORD -e "" &/dev/null2&&1if [$? = 0 ] ;then&&&& MYSQL_OK=1else&&&& MYSQL_OK=0fi&&&& return $MYSQL_OK}while[ $CHECK_TIME -ne 0 ]do&&&& let "CHECK_TIME -= 1"&&&& check_mysql_helthif [$MYSQL_OK = 1 ] ; then&&&& CHECK_TIME=0&&&& exit 0fi&if [$MYSQL_OK -eq 0 ] &&& [$CHECK_TIME -eq 0 ]then&&&& killall keepalivedexit1fidone&本文出自 “” 博客，请务必保留此出处
了这篇文章
类别：┆阅读(0)┆评论(0)2种方法解决mysql主从不同步 - blueel - 博客园
今天发现Mysql的主从数据库没有同步
先上Master库：
mysql& & 查看下进程是否Sleep太多。发现很正常。
+-------------------+----------+--------------+-------------------------------+
| File & & & & & & &| Position | Binlog_Do_DB | Binlog_Ignore_DB & & & & & & &|
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | & & 3260 | & & & & & & &| mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
再到Slave上查看
mysql& show slave status\G & & & & & & & & & & & & & & & & & & & & & & & &
Slave_IO_Running: Yes
Slave_SQL_Running: No
可见是Slave不同步
下面介绍两种解决方法：
方法一：忽略错误后，继续同步
该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况
#表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
之后再用mysql& show slave status\G &查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，现在主从同步状态正常了。。。
方式二：重新做主从，完全同步
该方法适用于主从库数据相差较大，或者要求数据完全统一的情况
解决步骤如下：
1.先进入主库，进行锁表，防止数据写入
使用命令：
mysql& flush t
注意：该处是锁定为只读状态，语句不区分大小写
2.进行数据备份&
#把数据备份到mysql.bak.sql文件
[root@server01 mysql]#mysqldump -uroot -p -hlocalhost & mysql.bak.sql
这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者脚本，都比较方便，确保数据万无一失
3.查看master 状态
+-------------------+----------+--------------+-------------------------------+
| File & & & & & & &| Position | Binlog_Do_DB | Binlog_Ignore_DB & & & & & & &|
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | & & 3260 | & & & & & & &| mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
4.把mysql备份文件传到从库机器，进行数据恢复
#使用scp命令
[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/
5.停止从库的状态
6.然后到从库执行mysql命令，导入数据备份
mysql& source /tmp/mysql.bak.sql
7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项
change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;
8.重新开启从同步
9.查看同步状态
mysql& show slave status\G &查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
好了，同步完成啦。用户名：lvnian2009
文章数：248
评论数：136
访问量：53679
注册日期：
阅读量：1297
阅读量：3317
阅读量：428144
阅读量：1116148
51CTO推荐博文
数据库主从同步部署约定：主数据库master &ip：10.0.0.4:3306备数据库slave & ip：10.0.0.3:3306部署流程：主数据库操作如下1、开启主数据的binlog参数，和设置server-id2、创建用于同步数据的账号rep3、锁表并查看当前日志名称和位置4、备份当前主数据库的全部数据（全备）5、给主数据解锁。让主库继续提供服务6、继续往主数据写数据备数据库操作1、把主数据备份的全备数据备份到备数据库2、在备数据库上设置主从同步的相关信息，如主数据库的IP、端口、同步账号、密码、binlog文件名、binlog位置点3、开始主从同步start slave；4、查看是否备份成功，show slave status\G;############################################################################主数据库上面操作开启主数据库的binlog参数，和设置server-idegrep "server-id|log-bin" /f&########################################[root@M_MYSQL ~]# egrep "server-id|log-bin" /f&server-id & & & = 1log-bin=mysql-bin[root@M_MYSQL ~]#重启3306实例数据库/etc/init.d/mysqld restartnetstat -lntup|grep 3306########################################[root@M_MYSQL ~]# /etc/init.d/mysqld restartShutting down MySQL. SUCCESS!&Starting MySQL.. SUCCESS!&[root@M_MYSQL ~]# netstat -lntup|grep 3306tcp & & & &0 & & &0 0.0.0.0:3306 & & & & & & & &0.0.0.0:* & & & & & & & & & LISTEN & & &2412/mysqld & & & &&[root@M_MYSQL ~]#########################################查看是否记录bin_log日志[root@M_MYSQL ~]# ll /application/mysql/data/total 28700-rw-rw----. 1 mysql mysql
Sep 30 21:58 ibdata1-rw-rw----. 1 mysql mysql &5242880 Sep 30 21:58 ib_logfile0-rw-rw----. 1 mysql mysql &5242880 Sep 30 21:33 ib_logfile1-rw-r-----. 1 mysql root & & &3458 Sep 30 21:58 M_MYSQL.err-rw-rw----. 1 mysql mysql & & & &5 Sep 30 21:58 M_MYSQL.piddrwx------. 2 mysql root & & &4096 Sep 30 21:33 mysql-rw-rw----. 1 mysql mysql & & &107 Sep 30 21:58 mysql-bin.000001 & & & & &##有这个，证明在记录主从同步-rw-rw----. 1 mysql mysql & & & 19 Sep 30 21:58 mysql-bin.indexdrwx------. 2 mysql mysql & & 4096 Sep 30 21:33 performance_schemadrwxr-xr-x. 2 mysql mysql & & 4096 Sep 30 21:33 test[root@M_MYSQL ~]#&##########################################在主数据库中创建测试用的数据库和表。crcreate table test(id int(4) not null auto_increment,name char(20) not null,primary key(id));mysql -uroot -plvnian -e " select *"for n in `seq 100`;do mysql -uroot -plvnian -e "insert test values($n,'lvnian$n'); " ;mysql -uroot -plvnian -e " select *"##########################################################查看是否开启binlog[root@M_MYSQL ~]# &mysql -uroot -plvnian -e "show variables like 'log_bin'" & & & & & & & & & &&+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin & & & | ON & &|+---------------+-------+[root@M_MYSQL ~]#&############################################创建用于同步数据的账号repmysql& grant replication slave on *.* to 'rep'@'10.0.0.%' identified by 'lvnian123456';Query OK, 0 rows affected (0.00 sec)mysql&Query OK, 0 rows affected (0.00 sec)mysql -uroot -plvnian -e "select user,host from mysql."&########################################[root@M_MYSQL ~]# &mysql -uroot -plvnian -e "select user,host from mysql."&+------+-----------+| user | host & & &|+------+-----------+| rep &| 10.0.0.% &|| root | 127.0.0.1 |############################################锁表mysql& flush &Query OK, 0 rows affected (0.00 sec)################################################################################查看当前日志名称和位置mysql -uroot -plvnian -e "" &mysql -uroot -plvnian -e ""&########################################[root@M_MYSQL ~]# mysql -uroot -plvnian -e "" &+------------------+----------+--------------+------------------+| File & & & & & & | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 | & & &302 | & & & & & & &| & & & & & & & & &|+------------------+----------+--------------+------------------+[root@M_MYSQL ~]# mysql -uroot -plvnian -e "" & &&+------------------+-----------+| Log_name & & & & | File_size |+------------------+-----------+| mysql-bin.000001 | & & 20802 || mysql-bin.000002 | & & & 150 || mysql-bin.000003 | & & & 302 |+------------------+-----------+[root@M_MYSQL ~]#&######################################备份当前主数据库数据另开一个窗口&mysqldump -uroot &-plvnian -A -B -F --master-data=2 | gzip & /tmp/mysql_bak.$(date +%F).sql.gz & & & &&##################&[root@M_MYSQL ~]# &mysqldump -uroot &-plvnian -A -B -F --master-data=2 --events | gzip & /tmp/mysql_bak.$(date +%F).sql.gz&[root@M_MYSQL ~]# ll /tmp/total 148-rw-r--r--. 1 root &root &145225 Sep 30 22:19 mysql_bak..sql.gz[root@M_MYSQL ~]#给主数据库解锁mysql&Query OK, 0 rows affected (0.00 sec)###############################################继续往主数据库写数据&for n in `seq 100 150`;do mysql -uroot -plvnian -e "insert test values($n,'lvnian$n'); " ;&mysql -uroot -plvnian -e " select *"################################################################################################################################################################################################################################################################################################################启动备数据库&/etc/init.d/mysqld restart[root@S_MYSQL ~]# /etc/init.d/mysqld restartShutting down MySQL. SUCCESS!&Starting MySQL.. SUCCESS!&[root@S_MYSQL ~]# netstat -lntup|grep 3306tcp & & & &0 & & &0 0.0.0.0:3306 & & & & & & & &0.0.0.0:* & & & & & & & & & LISTEN & & &2475/mysqld & & & &&[root@S_MYSQL ~]######################################################把备份数据备份到备数据库scp root@10.0.0.4:/tmp/mysql_bak..sql.gz .#####################################[root@S_MYSQL ~]# scp root@10.0.0.4:/tmp/mysql_bak..sql.gz .root@10.0.0.4's password:&mysql_bak..sql.gz & & & & & & & & & & & & & & & & & 100% &142KB 141.8KB/s & 00:00 & &[root@S_MYSQL ~]# lsanaconda-ks.cfg &aq &deng.txxt &install.log &install.log.syslog &mysql_bak..sql.gz[root@S_MYSQL ~]#&##########[root@S_MYSQL ~]# gzip -d mysql_bak..sql.gz&[root@S_MYSQL ~]# mysql -uroot -plvnian & mysql_bak..sql&[root@S_MYSQL ~]# mysql -uroot -plvnian -e "" & & &&+--------------------+| Database & & & & & |+--------------------+| information_schema || lvnian & & & & & & || mysql & & & & & & &|| performance_schema || test & & & & & & & |+--------------------+[root@S_MYSQL ~]# mysql -uroot -plvnian -e "select *"+-----+-----------+| id &| name & & &|+-----+-----------+| & 1 | lvnian1 & || & 2 | lvnian2 & || & 3 | lvnian3 & || & 4 | lvnian4 & || & 5 | lvnian5 & || & 6 | lvnian6 & || & 7 | lvnian7 & || & 8 | lvnian8 & || & 9 | lvnian9 & || &10 | lvnian10 &|| &11 | lvnian11 &|| &12 | lvnian12 &|| &13 | lvnian13 &|............全部恢复到备数据库成功###############################################开始设置主从同步###################################更加主数据库刚才的文件名称和位置点，等到下面的内容CHANGE MASTER TOMASTER_HOST='10.0.0.4',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='lvnian123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=302;######################show &slave status\G;###################################mysql& CHANGE MASTER TO& & -& MASTER_HOST='10.0.0.4',& & -& MASTER_PORT=3306,& & -& MASTER_USER='rep',& & -& MASTER_PASSWORD='lvnian123456',& & -& MASTER_LOG_FILE='mysql-bin.000003',& & -& MASTER_LOG_POS=302;Query OK, 0 rows affected (0.08 sec)mysql&&mysql&Query OK, 0 rows affected (0.03 sec)mysql& show &slave status\G;*************************** 1. row ***************************& & & & & & & &Slave_IO_State: Waiting for master to send event& & & & & & & & & Master_Host: 10.0.0.4& & & & & & & & & Master_User: rep& & & & & & & & & Master_Port: 3306& & & & & & & & Connect_Retry: 60& & & & & & & Master_Log_File: mysql-bin.000004& & & & & Read_Master_Log_Pos: 9759& & & & & & & &Relay_Log_File: S_MYSQL-relay-bin.000005& & & & & & & & Relay_Log_Pos: 9905& & & & Relay_Master_Log_File: mysql-bin.000004& & & & & & &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: 9759& & & & & & & Relay_Log_Space: 10106& & & & & & & 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: 0Master_SSL_Verify_Server_Cert: No& & & & & & & & Last_IO_Errno: 0& & & & & & & & Last_IO_Error:&& & & & & & & &Last_SQL_Errno: 0& & & & & & & &Last_SQL_Error:&& Replicate_Ignore_Server_Ids:&& & & & & & &Master_Server_Id: 11 row in set (0.00 sec)ERROR:&No query specifiedmysql&&成功的表现：[root@S_MYSQL ~]# mysql -uroot -plvnian -e "show &slave status\G;"|egrep "Slave_IO_Runnin|Slave_SQL_Running"& & & & & & &Slave_IO_Running: Yes& & & & & & Slave_SQL_Running: Yes[root@S_MYSQL ~]#出现上面两个yes证明数据库主从同步成功了################################################################################小结注意防火墙问题注意主从数据库的server-id问题，主从数据库的server-id必须是不同的。注意主数据库的配置文件my.cnf 必须开启记录binlog日志################################################################################同步故障解决下面主从不同步的原因就是上面从库的server-id没有修改，导致主从数据库的server-id是一样的，就会出现下面错误:注意错误提示：&&&&&&&&&Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does n please check the manual before using it).mysql& show &slave status\G;*************************** 1. row ***************************& & & & & & & &Slave_IO_State:&& & & & & & & & & Master_Host: 10.0.0.4& & & & & & & & & Master_User: rep& & & & & & & & & Master_Port: 3306& & & & & & & & Connect_Retry: 60& & & & & & & Master_Log_File: mysql-bin.000003& & & & & Read_Master_Log_Pos: 302& & & & & & & &Relay_Log_File: S_MYSQL-relay-bin.000001& & & & & & & & Relay_Log_Pos: 4& & & & Relay_Master_Log_File: mysql-bin.000003& & & & & & &Slave_IO_Running: No& & & & & & 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: 302& & & & & & & Relay_Log_Space: 107& & & & & & & 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: NULLMaster_SSL_Verify_Server_Cert: No& & & & & & & & Last_IO_Errno: 1593& & & & & & & & Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does n please check the manual before using it).& & & & & & & &Last_SQL_Errno: 0& & & & & & & &Last_SQL_Error:&& Replicate_Ignore_Server_Ids:&& & & & & & &Master_Server_Id: 11 row in set (0.00 sec)上面同步不成功的原因是。没有修改备数据库的server-id .导致备数据库和主数据库的server-id相同。把主备数据库的server-id改为不一致就可以了本文出自 “” 博客，请务必保留此出处
了这篇文章
类别：┆阅读(0)┆评论(0)
09:57:14 09:57:32 12:10:46 12:13:29 18:35:41 21:54:22 20:10:03