頻道欄目
首頁 > 資訊 > MySQL > 正文

mysql-5.6主從復制及遇到的錯誤

13-09-25        來源:[db:作者]  
收藏   我要投稿
mysql-5.6主從復制及遇到的錯誤
 
mysql的復制過程:每執行一個寫操作,它都會往自己的數據庫中存一份,與此同時這個寫操作也會存儲在二進制日志文件中一份,并且把它們保存為事件,所以在這個數據庫上,前端數據每執行一個寫操作或者有可能引起修改的操作,都會保存一個事件,我們就把這個事件通過mysql服務器3306端口發送給另外一臺服務器,另外一臺服務器把這個事件接收下來,接受下來以后先保存在本地的日志文件中,而后從這個日志文件中一次讀一個事件并且在本地執行一下,然后保存在數據庫里面,這個過程就叫mysql的復制。
 
安裝mysql的過程就不講了,直接開始我們的主從復制的配置過程:
 
1、 開啟master和slave的二進制日志功能,也就是在mysql的主配置文件/usr/local/mysql/etc/my.cnf中,添加log_bin=mysql-bin,將master的server_id 設置為1,slave的server_id=2.
 
下面是master的配置文件
 
[root@localhost ~]# cat /usr/local/mysql/etc/my.cnf | grep -v ^#| grep -v ^$
[mysqld]
 server_id = 1
 log_bin=mysql-bin
 log-bin-index = mysql-bin.index
 log-error = /var/log/mysql/mysql-error.log
 general_log = 1
 general_log_file = /var/log/mysql/mysql.log
 user = mysql
 basedir = /usr/local/mysql
 datadir = /datadir
 port = 3306
 socket = /var/lib/mysql/mysql5.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
 
接下來是slave的配置文件
 
[root@wordpress ~]# cat /usr/local/mysql/etc/my.cnf | grep -
v ^# | grep -v ^$
[mysqld]
 server_id = 2
 log_bin=mysql-bin
 log-bin-index = mysql-bin.index
 general_log = 1
 general_log_file = /var/log/mysql/mysql.log
 log-error=/var/log/mysql/mysql.error
 basedir = /usr/local/mysql
 datadir = /database
 port = 3306
 socket = /var/run/mysqld/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
 
2、在主服務器上,設置一個從數據庫的賬戶,使用REPLICATION SLAVE賦予權限
 
mysql> grant replication slave on *.* to 'tt'@'192.168.254.153' identified by '123456';
Query OK, 0 rows affected (0.06 sec)
 
mysql> show master status \G;                查看當前master的二進制日志狀態和起始點
*************************** 1. row ***************************
             File: mysql-bin.000011
         Position: 330
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)
 
 
 
mysql> flush tables with read lock;             將mysql的數據庫鎖表,僅僅允許讀,以保證數據的一致性
Query OK, 0 rows affected (0.06 sec)
 
 
 
[root@localhost ~]# mysqldump -uroot -p123 blog  > blog.sql        將blog這個庫以腳本的形式導出來,便于導入slave
 
mysql> unlock tables;                                              解鎖
Query OK, 0 rows affected (0.00 sec)
 
3、 slave上的操作
 
[root@wordpress ~]# mysql -u root -p123456 < blog.sql
 
mysql> change master to master_host='192.168.2
54.46',master_user='master',master_password='m
aster',master_log_file='mysql-bin.000011',master_log_pos=330;
Query OK, 0 rows affected, 2 warnings (0.13 sec)
 
 
 
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.254.46
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 330
               Relay_Log_File: wordpress-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 330
              Relay_Log_Space: 120
              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: NULL
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: 0
                  Master_UUID: 
             Master_Info_File: /database/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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)
 
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
 
 
 
4、測試
 
在master上新建表,
 
mysql> use blog;
Database changed
mysql> create table hi_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.14 sec)
 
 
mysql> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| hi_tb          |
+----------------+
1 row in set (0.00 sec)
 
在slave查看
 
mysql> use blog;
Database changed
mysql> show tables;
Empty set (0.00 sec)
 
mysql slave沒有發現新建的表,說明主從復制中間出了問題,所以需要查看mysql錯誤日志
 
tail  -f  /var/log/mysql/mysql.error
 
2013-09-22 15:39:21 2150 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2013-09-22 15:39:21 2150 [Note] Slave I/O thread: connected to master 'master@192.168.254.46:3306',replication started in log 'mysql-bin.000011' at position 330
2013-09-22 15:39:21 2150 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2013-09-22 15:39:21 2150 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000011' at position 330, relay log './wordpress-relay-bin.000001' position: 4
2013-09-22 15:39:21 2150 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'master'@'192.168.254.153' (using password: YES) (Errno: 1045), Error_code: 1597
2013-09-22 15:39:21 2150 [ERROR] Slave I/O thread couldn't register on master
2013-09-22 15:39:21 2150 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-bin.000011' at position 330, Error_code: 1597
2013-09-22 15:39:21 2150 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2013-09-22 15:39:21 2150 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'master'@'192.168.254.153' (using password: YES) (Errno: 1045), Error_code: 1597
2013-09-22 15:39:21 2150 [ERROR] Slave I/O thread couldn't register on master
 
 
 
錯誤原因,沒有在master上給master用戶授權!
 
mysql> select * from hi_tb;  (master)
+------+------+
| id   | name |
+------+------+
|    1 | bobu |
|    2 | dsfa |
+------+------+
2 rows in set (0.00 sec)
 
mysql> select * from hi_tb;  (slave)
+------+------+
| id   | name |
+------+------+
|    1 | bobu |
|    2 | dsfa |
+------+------+
2 rows in set (0.01 sec)
 
實現了同步。
 
最后,再次對比一下主從的二進制日志
 
相關TAG標簽
上一篇:臺積電:絕大多數7nm客戶都會轉向6nm_IT新聞_博客園
下一篇:最后一頁
相關文章
圖文推薦

關于我們 | 聯系我們 | 廣告服務 | 投資合作 | 版權申明 | 在線幫助 | 網站地圖 | 作品發布 | Vip技術培訓 | 舉報中心

版權所有: 紅黑聯盟--致力于做實用的IT技術學習網站

美女MM131爽爽爽毛片