[实践OK]教你在MySQL 5.0以上版本中配置主从库bin-log相关清理问题，主辅同步，unknown variable 'master-host=版本问题，iptables防火墙 只允许某IP访问某端口、访问特定网站。
实践如下：
1.配置主库my.cnf
[mysqld]
log-bin=mysql-testsyn-bin --主库二进制日志文件名的前缀
binlog-do-db=wb --要同步的数据库
server-id = 1 --serverid和辅库不同即可
修改为：
主库加帐号，10.51.178.1** 内网IP可以来访问，创建用户slave.拥有replication slave权限：
grant replication slave on *.* to "slave"@"10.51.178.1**" identified by "123FFFCCC6" with grant option;
用PHPMyadmin看也就是这两项权限： GRANT REPLICATION SLAVE ，不需要插入修改删除啥的，最小化。
查看主库状态：
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 1606516 | | | |
+------------------+----------+--------------+------------------+-------------------+
配置辅库：
server-id = 2
master-host = 10.51.180.**
master-user = slave
master-password = 123FFFCCC6
master-port = 3306
master-connect-retry = 5 --重试间隔时间
replicate-do-db = wb --同步db
启动mysql ,查看辅库状态：
show slave status;
说明:
Slave_IO_Running:连接到主库,并读取主库的日志到本地，生成本地日志文件
Slave_SQL_Running：读取本地日志文件，并执行日志里的sql
把主库上的数据dump到辅库上
主库：10.51.180.**:
mysqldump --opt --master-data -uroot -p123FFFCCC6 > /home/mahaibo/dump-testsyn &
注意参数：--master-data
辅库:10.51.178.1**
先建立库:create database wb;
导入前停止同步：
stop slave
mysql -uroot -p123FFFCCC6 wb < /home/mahaibo/dump-testsyn
start slave;
必须先停止stop slave，然后才能导入
操作指南如下：
【=====================================
首先，关闭辅助库同步：
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
其次，从主机上导出sql并在辅机上执行和主机一样。
第三，清空主机上所有位置： reset master;
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 120
Binlog_Do_DB: wb
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
辅机：
mysql> show slave status\G;
Read_Master_Log_Pos: 4
第四，清空辅库的bin-log位置：
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
第五,打开辅库的slave同步线程：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
第六，看到辅库和主库的Read_Master_Log_Pos= 120 ，如下：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.51.180.220
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
==============================================】
4.在主库10.51.180.**上在wb.user表里插入一条数据：
insert into users (username,password) values ('test1','test1');
辅库上的users表里也相应的有了数据。
5.查看主库二进制日志文件内容：
show binlog events;
注意：
1.主辅库同步主要是通过二进制日志来实现同步的。
2.在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info文件。因为master.info记录了上次要连接主库的信息，如果不删除，即使my.cnf里进行了修改，也不起作用。因为读取的还是master.info文件里的信息。
MySQL运行时间长了之后，二进制日志会占用大量硬盘空间，清楚这些日志的命令如下：
show binary logs;
purge master logs before now();
mysql> purge master logs before now();
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000004 | 46653 |
+------------------+-----------+
1 row in set (0.00 sec)
reset master 将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件 起始值从000001 开始，然而purge binary log 命令并不会修改记录binlog的顺序的数值
上面还有一个没有清掉，得用这个就彻底清了，120：
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> RESET SLAVE
-> ;
Query OK, 0 rows affected (0.00 sec)
http://www.shangxueba.com/jingyan/1638441.html
————————————参考实践自如下链接————————————————————
http://blog.csdn.net/ljx0305/article/details/4371655
http://wenku.baidu.com/link?url=rK2b1ofBZnxgoQF5rW0JcjwU7Cn-WT6AuH_E29sOkgKDkEYeJQ6E5XGgt-NzAaYxuPzx25I_qJCB9Xe3F6QKTtILymCkKzFrEUi9rY_xtyO
新版本不支持master-host参数，[ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'master-host=10.51.180.2**'：
------解决方案--------------------
Mysql版本从5.1.7以后开始就不支持“master-host”类似的参数
在从库上执行如下命令；
change master to master_host='masterIP', master_user='slave', master_password='slvaePASS';
slave start;
切记：
此处是masterIP地址，不是slave地址，而登陆账号为slave账号密码
------解决方案--------------------
master-host=192.168.8.111
master-user=backup
master-password=backup
master-port=3306
master-connect-retry=60
这些参数 mysql5.5已经废弃了
必须在slave上用change master to 来设置slave
server-id = 2
#master-host = 10.51.180.** 新版本废弃
#master-user = slave
#master-password = 123FFFCCC6
#master-port = 3306
#master-connect-retry = 5
replicate-do-db = wb
mysql> change master to master_host='10.51.180.**', master_user='slave', master_password='123FFFCCC6';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.51.180.**
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: wb
.......
说明成功了，因为参数已经是Connecting和yes了:
Slave_IO_Running:连接到主库,并读取主库的日志到本地，生成本地日志文件
Slave_SQL_Running：读取本地日志文件，并执行日志里的sql
但此时还没有库，只是连接成功，于是，得从主库拉出数据，并停止辅库：
mysql> show databases; //slave没有wb表
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
照猫画虎建一个wb库:
mysql> show create database mysql;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE DATABASE `wb` /*!40100 DEFAULT CHARACTER SET utf8 */ ;
Query OK, 1 row affected (0.00 sec)
从master下的mysql里导出表及数据sql：
10.51.180.**
mysqldump -uroot -p wb > wb2slave.09.24.sql
scp上步导出的sql到slave目录里source导入：
scp root@10.51.180.**:/root/wb2slave.09.24.sql ./
导入到刚才在slave建立的wb 库里：
mysql -uroot -p
mysql> use wb;
mysql> stop slave
Database changed
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> source /root/wb2slave.09.24.sql
Query OK, 0 rows affected (0.00 sec)
导入完成后，启动slave：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
清掉刚才导入之前的，主库里的bin-log：
mysql> PURGE MASTER LOGS BEFORE '2015-09-24 17:05:00';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000008 | 1222 |
+------------------+-----------+
mysql-bin.000007没有了，mysql-bin.000008一个了。
其它清理方法如下：
清除3天前的 binlog
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
清指定文件的bin-log：
PURGE MASTER LOGS TO 'mysql-bin.000008';
清理MySQL的bin-log二进制日志
Posted by zuzhihui in mysql on 2011/11/27 with No Comments
MySQL运行时间长了之后，二进制日志会占用大量硬盘空间，清楚这些日志的命令如下：
show binary logs;
purge master logs before now();
重置mysql主从同步（MySQL Reset Master-Slave Replication）:
reset master;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
http://www.shangxueba.com/jingyan/1638441.html
发现内外网不一致，加个外网：
Last_IO_Error: error connecting to master 'slave@10.51.180.220:3306' - retry-time: 60 retries: 1
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
grant replication slave on *.* to "slave"@"10.51.180.*" identified by "123FFFCCC6" with grant option
grant replication slave on *.* to "slave"@"101.200.1.*" identified by "123FFFCCC6" with grant option
flush privileges;
一台服务器有内网ip和外网ip，如何通过iptables禁止访问本机的外网ip的某个端口，比如3306端口?
iptables防火墙 只允许某IP访问某端口、访问特定网站:
+++++++++++++++++主库：eth0 : 10.51.180.220++++++++++++++++++++++++++
让辅库IP10.51.178.1** 能访问到主的3306端口:
iptables -I INPUT -s 10.51.178.1** -p tcp --dport 3306 -j ACCEPT
写到开机启动里头 vi /etc/rc.local：
#开机就放开内部Mysql端口：让辅库内网IP能访问到该机器的3306备份端口
/usr/sbin/iptables -I INPUT -s 10.51.178.1** -p tcp --dport 3306 -j ACCEPT
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
http://www.sunzhenghua.com/msyql-grant-user-master-slave-replication-copy
参考：http://www.myexception.cn/mysql/387629.html
========================================================================
防火墙问题引起，Telnet 时出现，No route to host：
telnet ：No route to host
telnet 10.51.180.** 3306
Trying 10.51.180.**...
telnet: connect to address 10.51.180.**: No route to host
[root@iZ258cfosv4Z ~]# telnet 10.51.180.** 3306
http://blog.sina.com.cn/s/blog_5022501501010pin.html
10.51.180.** //在主库上关闭掉iptables即可。
/bin/systemctl stop iptables.service
10.51.178.128
telnet 10.51.180.** 3306
Trying 10.51.180.**...
Connected to 10.51.180.**.
Escape character is '^]'.
测试连接：
mysql -uroot -h10.51.180.** -p
再测试这个，OK也就行了：
mysql -uslave -h10.51.180.** -p
show slave status\G;
Master_UUID: fef2f186-1fc5-11e5-9cb7-00163e0003e7
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
change master to master_host='10.51.180.**', master_user='slave', master_password='123FFFCCC6';
Slave_IO_State: Waiting for master to send event
Master_Host: 10.51.180.**
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Read_Master_Log_Pos: 9687
Relay_Log_Pos: 820
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: wb
_________________________________________________________________________________
Could not find first log file name in binary log index file'的解决办法
数据库主从出错：
Slave_IO_Running: No 一方面原因是因为网络通信的问题也有可能是日志读取错误的问题。以下是日志出错问题的解决方案：
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
解决办法：
从机器停止slave
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
到master机器登陆mysql：
记录master的bin的位置，mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | wb | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
刷新日志：mysql> flush logs;
因为刷新日志file的位置会+1，即File变成为:mysqld-bin.000011
马上到slave执行
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=120;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.51.180.**
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: wb
Master_Server_Id: 1
Master_UUID: fef2f186-1fc5-11e5-9cb7-00163e0003e7
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
正常了。
1.配置主库my.cnf
[mysqld]
log-bin=mysql-testsyn-bin --主库二进制日志文件名的前缀
binlog-do-db=wb --要同步的数据库
server-id = 1 --serverid和辅库不同即可
修改为：
主库加帐号，10.51.178.1** 内网IP可以来访问，创建用户slave.拥有replication slave权限：
grant replication slave on *.* to "slave"@"10.51.178.1**" identified by "123FFFCCC6" with grant option;
用PHPMyadmin看也就是这两项权限： GRANT REPLICATION SLAVE ，不需要插入修改删除啥的，最小化。
查看主库状态：
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 1606516 | | | |
+------------------+----------+--------------+------------------+-------------------+
配置辅库：
server-id = 2
master-host = 10.51.180.**
master-user = slave
master-password = 123FFFCCC6
master-port = 3306
master-connect-retry = 5 --重试间隔时间
replicate-do-db = wb --同步db
启动mysql ,查看辅库状态：
show slave status;
说明:
Slave_IO_Running:连接到主库,并读取主库的日志到本地，生成本地日志文件
Slave_SQL_Running：读取本地日志文件，并执行日志里的sql
把主库上的数据dump到辅库上
主库：10.51.180.**:
mysqldump --opt --master-data -uroot -p123FFFCCC6 > /home/mahaibo/dump-testsyn &
注意参数：--master-data
辅库:10.51.178.1**
先建立库:create database wb;
导入前停止同步：
stop slave
mysql -uroot -p123FFFCCC6 wb < /home/mahaibo/dump-testsyn
start slave;
必须先停止stop slave，然后才能导入
操作指南如下：
【=====================================
首先，关闭辅助库同步：
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
其次，从主机上导出sql并在辅机上执行和主机一样。
第三，清空主机上所有位置： reset master;
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 120
Binlog_Do_DB: wb
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
辅机：
mysql> show slave status\G;
Read_Master_Log_Pos: 4
第四，清空辅库的bin-log位置：
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
第五,打开辅库的slave同步线程：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
第六，看到辅库和主库的Read_Master_Log_Pos= 120 ，如下：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.51.180.220
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
==============================================】
4.在主库10.51.180.**上在wb.user表里插入一条数据：
insert into users (username,password) values ('test1','test1');
辅库上的users表里也相应的有了数据。
5.查看主库二进制日志文件内容：
show binlog events;
注意：
1.主辅库同步主要是通过二进制日志来实现同步的。
2.在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info文件。因为master.info记录了上次要连接主库的信息，如果不删除，即使my.cnf里进行了修改，也不起作用。因为读取的还是master.info文件里的信息。
MySQL运行时间长了之后，二进制日志会占用大量硬盘空间，清楚这些日志的命令如下：
show binary logs;
purge master logs before now();
mysql> purge master logs before now();
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000004 | 46653 |
+------------------+-----------+
1 row in set (0.00 sec)
reset master 将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件 起始值从000001 开始，然而purge binary log 命令并不会修改记录binlog的顺序的数值
上面还有一个没有清掉，得用这个就彻底清了，120：
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> RESET SLAVE
-> ;
Query OK, 0 rows affected (0.00 sec)
http://www.shangxueba.com/jingyan/1638441.html
————————————参考实践自如下链接————————————————————
http://blog.csdn.net/ljx0305/article/details/4371655
http://wenku.baidu.com/link?url=rK2b1ofBZnxgoQF5rW0JcjwU7Cn-WT6AuH_E29sOkgKDkEYeJQ6E5XGgt-NzAaYxuPzx25I_qJCB9Xe3F6QKTtILymCkKzFrEUi9rY_xtyO
新版本不支持master-host参数，[ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'master-host=10.51.180.2**'：
------解决方案--------------------
Mysql版本从5.1.7以后开始就不支持“master-host”类似的参数
在从库上执行如下命令；
change master to master_host='masterIP', master_user='slave', master_password='slvaePASS';
slave start;
切记：
此处是masterIP地址，不是slave地址，而登陆账号为slave账号密码
------解决方案--------------------
master-host=192.168.8.111
master-user=backup
master-password=backup
master-port=3306
master-connect-retry=60
这些参数 mysql5.5已经废弃了
必须在slave上用change master to 来设置slave
server-id = 2
#master-host = 10.51.180.** 新版本废弃
#master-user = slave
#master-password = 123FFFCCC6
#master-port = 3306
#master-connect-retry = 5
replicate-do-db = wb
mysql> change master to master_host='10.51.180.**', master_user='slave', master_password='123FFFCCC6';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.51.180.**
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: wb
.......
说明成功了，因为参数已经是Connecting和yes了:
Slave_IO_Running:连接到主库,并读取主库的日志到本地，生成本地日志文件
Slave_SQL_Running：读取本地日志文件，并执行日志里的sql
但此时还没有库，只是连接成功，于是，得从主库拉出数据，并停止辅库：
mysql> show databases; //slave没有wb表
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
照猫画虎建一个wb库:
mysql> show create database mysql;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE DATABASE `wb` /*!40100 DEFAULT CHARACTER SET utf8 */ ;
Query OK, 1 row affected (0.00 sec)
从master下的mysql里导出表及数据sql：
10.51.180.**
mysqldump -uroot -p wb > wb2slave.09.24.sql
scp上步导出的sql到slave目录里source导入：
scp root@10.51.180.**:/root/wb2slave.09.24.sql ./
导入到刚才在slave建立的wb 库里：
mysql -uroot -p
mysql> use wb;
mysql> stop slave
Database changed
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> source /root/wb2slave.09.24.sql
Query OK, 0 rows affected (0.00 sec)
导入完成后，启动slave：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
清掉刚才导入之前的，主库里的bin-log：
mysql> PURGE MASTER LOGS BEFORE '2015-09-24 17:05:00';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000008 | 1222 |
+------------------+-----------+
mysql-bin.000007没有了，mysql-bin.000008一个了。
其它清理方法如下：
清除3天前的 binlog
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
清指定文件的bin-log：
PURGE MASTER LOGS TO 'mysql-bin.000008';
清理MySQL的bin-log二进制日志
Posted by zuzhihui in mysql on 2011/11/27 with No Comments
MySQL运行时间长了之后，二进制日志会占用大量硬盘空间，清楚这些日志的命令如下：
show binary logs;
purge master logs before now();
重置mysql主从同步（MySQL Reset Master-Slave Replication）:
reset master;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
http://www.shangxueba.com/jingyan/1638441.html
发现内外网不一致，加个外网：
Last_IO_Error: error connecting to master 'slave@10.51.180.220:3306' - retry-time: 60 retries: 1
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
grant replication slave on *.* to "slave"@"10.51.180.*" identified by "123FFFCCC6" with grant option
grant replication slave on *.* to "slave"@"101.200.1.*" identified by "123FFFCCC6" with grant option
flush privileges;
一台服务器有内网ip和外网ip，如何通过iptables禁止访问本机的外网ip的某个端口，比如3306端口?
iptables防火墙 只允许某IP访问某端口、访问特定网站:
+++++++++++++++++主库：eth0 : 10.51.180.220++++++++++++++++++++++++++
让辅库IP10.51.178.1** 能访问到主的3306端口:
iptables -I INPUT -s 10.51.178.1** -p tcp --dport 3306 -j ACCEPT
写到开机启动里头 vi /etc/rc.local：
#开机就放开内部Mysql端口：让辅库内网IP能访问到该机器的3306备份端口
/usr/sbin/iptables -I INPUT -s 10.51.178.1** -p tcp --dport 3306 -j ACCEPT
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
http://www.sunzhenghua.com/msyql-grant-user-master-slave-replication-copy
参考：http://www.myexception.cn/mysql/387629.html
========================================================================
防火墙问题引起，Telnet 时出现，No route to host：
telnet ：No route to host
telnet 10.51.180.** 3306
Trying 10.51.180.**...
telnet: connect to address 10.51.180.**: No route to host
[root@iZ258cfosv4Z ~]# telnet 10.51.180.** 3306
http://blog.sina.com.cn/s/blog_5022501501010pin.html
10.51.180.** //在主库上关闭掉iptables即可。
/bin/systemctl stop iptables.service
10.51.178.128
telnet 10.51.180.** 3306
Trying 10.51.180.**...
Connected to 10.51.180.**.
Escape character is '^]'.
测试连接：
mysql -uroot -h10.51.180.** -p
再测试这个，OK也就行了：
mysql -uslave -h10.51.180.** -p
show slave status\G;
Master_UUID: fef2f186-1fc5-11e5-9cb7-00163e0003e7
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
change master to master_host='10.51.180.**', master_user='slave', master_password='123FFFCCC6';
Slave_IO_State: Waiting for master to send event
Master_Host: 10.51.180.**
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Read_Master_Log_Pos: 9687
Relay_Log_Pos: 820
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: wb
_________________________________________________________________________________
Could not find first log file name in binary log index file'的解决办法
数据库主从出错：
Slave_IO_Running: No 一方面原因是因为网络通信的问题也有可能是日志读取错误的问题。以下是日志出错问题的解决方案：
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
解决办法：
从机器停止slave
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
到master机器登陆mysql：
记录master的bin的位置，mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | wb | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
刷新日志：mysql> flush logs;
因为刷新日志file的位置会+1，即File变成为:mysqld-bin.000011
马上到slave执行
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=120;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.51.180.**
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: wb
Master_Server_Id: 1
Master_UUID: fef2f186-1fc5-11e5-9cb7-00163e0003e7
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
正常了。
作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：https://jackxiang.com/post/1371/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！
最后编辑： jackxiang 编辑于2015-10-10 23:51
diff 比较两个文件的问题。。。
在discuz的home center里看到的用法评论列表
