MySQL主從複製

標籤：記錄檔   生效   UI   code   nec   replicat   cat   nbsp   slave   

  原理：利用mysql二進位記錄檔，實現資料同步  步驟：  一、配置主庫（Master）  1. 修改主庫my.cnf設定檔，加入如下配置[[email protected] ~]# nano -w /etc/my.cnf [mysqld] log-bin=mysql-bin server-id=1  2. 重啟mysql，使配置生效[[email protected] ~]# service mysqld restart  3. 以root登入mysql，建立主從複製使用者並授權主從複製[[email protected] ~]# mysql -uroot -p mysql> CREATE USER ‘ repl‘@‘%‘ IDENTIFIED BY ‘ repl _password‘;mysql> GRANT REPLICATION SLAVE ON *.* TO ‘ repl‘@‘%‘;mysql> FLUSH PRIVILEGES;  4. 查看主庫二進位日誌座標（記住File和Position列對應的值）mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 747      |              |                  |+------------------+----------+--------------+------------------+

 

   二、配置從庫（Slave）  1. 修改從庫my.cnf設定檔，加入如下配置[[email protected] ~]# nano -w /etc/my.cnf [mysqld] server-id=2  2. 修改MySQL server UUID（可選，如果是直接複製虛擬機器，則這步必須做）mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak  3. 重啟mysql，使配置生效並產生新的MySQL server UUID[[email protected] ~]# service mysqld restart  4. 以root登入mysql，並設定從庫訪問主庫二進位記錄檔[[email protected] ~]# mysql -uroot -p

mysql> CHANGE MASTER TO    ->    MASTER_HOST=‘master_host_name‘,    ->    MASTER_USER=‘repl‘,    ->    MASTER_PASSWORD=‘repl_password‘,    ->    MASTER_LOG_FILE=‘mysql-bin.000001‘,    ->    MASTER_LOG_POS=747;

  5. 啟動Slavemysql> start slave;  6. 查看Slave串連狀態（Slave_IO_Running、Slave_SQL_Running都為Yes則表示串連成功）mysql> show slave status \G; *************************** 1. row ***************************  Slave_IO_State: Waiting for master to send event  Master_Host: 192.168.168.110  Master_User: repl  Master_Port: 3306  Connect_Retry: 60  Master_Log_File: mysql-bin.000001  Read_Master_Log_Pos: 747  Relay_Log_File: localhost-relay-bin.000002  Relay_Log_Pos: 320  Relay_Master_Log_File: mysql-bin.000001  Slave_IO_Running: Yes  Slave_SQL_Running: Yes  

MySQL主從複製