MYSQL主从复制搭建全过程实战---探索不能实时同步的关键性参数

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/45873859

一、主库操作 
 1、全库导出
 mysqldump  -h192.168.1.6 --all-databases  -uroot -proot --opt --add-drop-database --add-drop-table --events --triggers --routines --default-character-set=utf8 --master-data=2  --single-transaction --complete-insert --quote-names --log-error=/gyj/mysqldump20150520.log > /gyj/backup_20150520.mysql


2、获得导出时候的mysql的binlog的位置
（head -50 backup_20150520.mysql或者tail -50  backup_20150520.mysql）


----CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=529372;


3、拷贝主库备份到从备
[root@uubee6 gyj]# scp backup_20150520.mysql root@192.168.1.8:/gyj/
 
 
 二、备库操作
 1、清空备库数据库（保留information_schema、performance_schema、mysql三个库），然后导入主库的数据
  确认慢日志有没有关闭：SET GLOBAL slow_query_log=off;
  如果没关闭导入时会报错：ERROR 1580 (HY000) at line 3405: You cannot 'DROP' a log table if logging is enabled


 mysql -h192.168.1.8 -uroot -proot < /gyj/backup_20150520.mysql


 
 三、主库操作
 1、建复制用户
 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'slavepass';
 

 

四、备库操作

 1、在从库配置连接主库，执行以下命令
CHANGE MASTER TO
MASTER_HOST='192.168.1.6',
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=529372,
MASTER_CONNECT_RETRY=10;


2、启动从库
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)




3、查询slave状态：
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.6
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 657284
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 128192
        Relay_Master_Log_File: binlog.000001
             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: 657284
              Relay_Log_Space: 128358
              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: 0
Master_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: 101
                  Master_UUID: c811e75a-d875-11e4-8bb1-b083fede6c94
             Master_Info_File: /u01/my3306/data/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: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)


ERROR: 
No query specified



五、测试
  1、主库操作
  mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)


2、备库操作
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
 
3、备库上没有同步过去，但我在备库操作flush logs;命令就能同步过来！




4、查主库上的几个参数：
 mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
 mysql> show variables like '%innodb_flush_metho%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+
1 row in set (0.00 sec)


mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 100    |
+---------------+-------+
1 row in set (0.00 sec)



5、找到原因：sync_binlog=100，在主库把参数：sync_binlog设为1即可！！！
set global sync_binlog=1;   ---最好永久设置改my.cnf配置文件


 
6、记下常用几个命令
reset master;
show master status\G;
show binlog events in 'binlog.000001';
flush logs;