（二）mysql主從同步搭建步驟

標籤：

Master_Server:192.168.159.10

 Slave_Server:192.168.159.11 1.測試遠程賬戶登入  Master_Server:  #給backup使用者授權  mysql> grant replication slave on *.* to ‘backup‘@‘192.168.159.11‘  identified by ‘123‘;  #檢查  authentication_string 相當於mysql5.5之前的password  mysql> select host,user,authentication_string from mysql.user; | host | user | authentication_string | +----------------+------------------+------------------------------------------- + | localhost | root | *3DB01AD4E54E8FE9429FA016DB24E4E725247513 | | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | debian-sys-maint | *C9A3938D4184835A2096D4F76879FFCEA1383C22 || 192.168.159.11 | backup | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------------+------------------+------------------------------------------- +  Slave_Server: mysql -ubackup -p -h‘192.168.159.10‘  #輸入密碼測試是否能登陸 ========================================= 不能登陸時，問題排查： 1）檢查Master_Server是否只監聽本機3306連接埠    netstat -an|grep 3306    [email protected]:~# netstat -an|grep 3306    tcp6       0      0 :::3306                 :::*                    LISTEN    tcp6       0      0 192.168.159.10:3306     192.168.159.11:53918    ESTABLISHED       #如果只出現127.0.0.1：3306 則修改my.cnf 解除ip綁定，    vim /etc/mysql/mysql.conf.d/mysqld.cnf     
    注釋掉# bind-address          = 127.0.0.1    測試能否成功 2）關閉防火牆/telnet 3306 檢測連接埠能否訪問 ==========================================2.開始配置主從  先確定mysql版本,mysql5.17後不支援將主從配置資訊寫在設定檔裡面了，  1）Master_Server 設定檔     開啟binary log: log_bin  =  /var/log/mysql/mysql-bin.log     進入mysql，show master status，記下file，positin     mysql> show master status;     +------------------+----------+--------------+------------------+-------------------+     | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |     +------------------+----------+--------------+------------------+-------------------+     | mysql-bin.000001 |      611 |              |                  |                   |     +------------------+----------+--------------+------------------+-------------------+  2）Slave_Server 設定檔     修改server-id:server-id  =  2     進入mysql     mysql>change master to master_host=‘192.168.159.10‘,          >master_user=‘backup‘,          >master_password=‘123‘,          >master_log_file=‘mysql-bin.000001‘,          >master_log_pos=611;     mysql>start slave; 3）檢測是否成功    mysql> show slave status\G        Slave_IO_Running: Yes     Slave_SQL_Running: Yes ==========================================問題：配置完成Slave_IO_Running:No,查看error.log，The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work解決：因為我們虛擬機器做了clone，導致主從啟動時讀取的uuid完全相同，查看      show variables like ‘server%id%‘;      修改UUID:vim /var/lib/mysql/auto.cnf  #改成一樣的就可以了              

 

（二）mysql主從同步搭建步驟