MYSQL 的 AB 複製（主從，雙機熱備）

標籤：京華時報   伺服器   ip地址   資料庫   master   

MYSQL 的 AB 複製（主從，雙機熱備）    在實際的應用中，我們經常需要製作一個備份資料庫。當我們的主要資料庫發生問題導致無法正常相應的時候，我們可以把備用的從資料庫街上來頂替主庫，以達到不間斷服務，熱備的作用。在實際應用中從庫和主庫的資料必須同步，保持一致，對主庫的任何操作通要引發從庫上的同樣操作。實現原理：從庫以一定的頻率去讀取主庫的二進位記錄檔，按照日誌中記錄對從庫進行同樣的操作，以達到同步效果。注意版本影響：實現雙機的熱備首先要瞭解主從資料庫伺服器的版本的需求。首先，MYSQL的版本都要高於3.2，還有一個基本的原則就是作為從資料庫的資料庫版本可以高於主伺服器資料庫的版本，但是不可以低於主伺服器的資料庫版本實驗環境：  IP地址主機名稱主庫：192.168.200.101  master從庫：192.168.200.10slave兩台機器都要安裝軟體包：mysql mysql-srver# yum -y install mysql mysql-server主庫操作：# vim /etc/my.cnf 在mysqld中填加如下配置server-id=1log-bin=mysql-binlogexpire_logs_day=10max_binlog_size=500M# service mysqld restart主庫增加使用者，用於讀取主庫日誌# mysql -u root -pmysql> grant replication slave,reload,super on *.* to [email protected] identified by ‘123456‘;replication slave：用於複製型從屬伺服器（從主伺服器中讀取二進位日誌事件）reload：允許使用FLUSHsuper：允許使用 CHANG MASTER,KILL,PURGE,MASTER LOGS,SET GLOBAL 等語句獲得 MasterDB 的相關資訊mysql> show master status;+------------------+----------+--------------+------------------+| File   | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 |106 |||+------------------+----------+--------------+------------------+1 row in set (0.00 sec)記錄下File和Position的值。然後登入從伺服器，進行如下操作：mysql> FLUSH TABLES WITH READ LOCK;# mysqldump --all-databases > db.backuo.sql# scp db.backuo.sql [email protected]從庫操作：從庫串連主庫進行測試，如果串連成功說明主庫配置成功# mysql -u slave -p123456 -h 192.168.200.101# service mysqld restart# mysql -u root -p < db.backup.sql停從庫，修改從庫/etc/my.cnf，增加選項：vim /etc/my.cnf[mysqld]server-id=2master-host=192.168.200.101master-user=slavemaster-password=1234565、啟動從庫，進行主從庫資料同步# service mysqld restart# mysql -u root -p mysql> stop slave;mysql> reset slave;mysql> CHANGE MASTER TO-> MASTER_HOST=‘192.168.200.101‘,-> MASTER_USER=‘slave‘,-> MASTER_PASSWORD=‘123456‘,-> MASTER_LOG_FILE=‘mysql-bin.000003‘,-> MASTER_LOG_POS=106;Query OK, 0 rowsaffected (0.04 sec)mysql> start slave;mysql> CHANGE MASTER TO-> MASTER_HOST=‘master_host_name‘, //主伺服器的IP地址-> MASTER_USER=‘replication_user_name‘, //同步資料庫的使用者-> MASTER_PASSWORD=‘replication_password‘, //同步資料庫的密碼-> MASTER_LOG_FILE=‘recorded_log_file_name‘, //主伺服器二進位日誌的檔案名稱(前面要求記錄的參數)-> MASTER_LOG_POS=recorded_log_position; //記錄檔的開始位置(前面要求記錄的參數)報錯解決方案：ERROR 1201(HY000):Could now initialize master info structure; more error messages can be found in the MySQL error logstop slave;reset slave;CHANGE MASTER TO MASTER_HOST=‘192.168.3.254‘, MASTER_USER=‘slave‘, MASTER_PASSWORD=‘123456‘, MASTER_LOG_FILE=‘mysql-binlog.000003‘, MASTER_LOG_POS=246;start slave;資料不同步解決辦法：mysql> stop slave;Query OK, 0 rows affected (0.00 sec)mysql> set global sql_slave_skip_counter=1;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)進行測試：主庫查看當前存在的庫mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | test               | +--------------------+3 rows in set (0.00 sec)從庫查看當前存在庫mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | test               | +--------------------+3 rows in set (0.00 sec)說明兩者中的資料保持了一致性主庫建立庫，表：mysql> create database sampdb;mysql> use sampdbmysql> create table new(name char(20),phone char(20));開啟從庫，查看：mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | sampdb             | | test               | +--------------------+4 rows in set (0.00 sec)mysql> use sampdbmysql> show tables;說明主從資料庫建立成功。主從資料庫相關命令：slave stop; slave start; 開始停止從資料庫。show slave status\G; 顯示從庫狀態資訊show master status\G;顯示主庫狀態資訊purge master logs to ’binlog.000004’; 此命令非常小心，刪除主要資料庫沒用的二進位記錄檔。如果誤刪除，那麼從庫就沒有辦法自動更新了。change master；從伺服器上修改參數使用另外，如果你當前操作的從庫以前曾經與其他伺服器建立過主從關係，你可能會發現即使你在my.cnf檔案中即便更改了主伺服器的位置，但是MSQL仍然在試圖串連就舊的主伺服器的現象。發生這種問題的時候，我們可以通過清除master.info這個快取檔案或者在mysql中通過命令來進行設定。方式如下：a、刪除master.info方法這個檔案位於資料檔案存放目錄裡。預設是在/var/lib/mysql中的。你可以直接將其刪除，然後重新啟動伺服器。b、mysql命令方法如果你不方便重新啟動伺服器的話，那麼就只能使用mysql命令來協助你做到。首先登入到主伺服器上，查看當前伺服器狀態：mysql> show master status\G;+---------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+---------------+----------+--------------+------------------+| mysql-bin.003 | 73 | test | manual,mysql |+---------------+----------+--------------+------------------+1.FLUSH TABLES WITH READ LOCK 這個命令是全域讀鎖定，執行了命令之後所有庫所有表都被鎖定唯讀。一般都是用在資料庫聯機備份，這個時候資料庫的寫操作將被阻塞，讀操作順利進行。 解鎖的語句也是unlock tables。 2.LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} 這個命令是表層級的鎖定，可以定製鎖定某一個表。例如： lock  tables test read; 不影響其他表的寫操作。 解鎖語句也是unlock tables。 這兩個語句在執行的時候都需要注意個特點，就是 隱式提交的語句。在退出mysql終端的時候都會隱式的執行unlock tables。也就是如果要讓表鎖定生效就必須一直保持對話。 P.S.  MYSQL的read lock和wirte lock read-lock:  允許其他並發的讀請求，但阻塞寫請求，即可以同時讀，但不允許任何寫。也叫共用鎖定 write-lock: 不允許其他並發的讀和寫請求，是排他的(exclusive)。也叫獨佔鎖 3. flush table tablename

本文出自 “江湖笑笑生” 部落格，謝絕轉載！

MYSQL 的 AB 複製（主從，雙機熱備）