http://www.jackxiang.com/index.php zh-cn http://www.jackxiang.com/post// jack <xdy108@126.com> Fri, 07 Nov 2008 09:28:09 +0000 http://www.jackxiang.com/post// 1.配置主库my.cnf
[mysqld]  
log-bin=mysql-testsyn-bin --主库二进制日志文件名的前缀  
binlog-do-db=wb      --要同步的数据库  
server-id = 1             --serverid和辅库不同即可  
修改为：
log_bin = mysql-bin binlog_format = mixed expire_logs_days = 7 binlog-do-db=wb server-id = 1



主库加帐号，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:

正常了。 ]]> http://www.jackxiang.com/post//#blogcomment <user@domain.com> Thu, 01 Jan 1970 00:00:00 +0000 http://www.jackxiang.com/post//#blogcomment