# 單機主從復制 MySQL可以采用主從機制進行備份,如果一對一進行備份對于生成環境而言比較浪費資源,主服務器把數據變化記錄到主日志,然后從服務器通過I/O線程讀取主服務器的日志,并將它寫入到從服務器的中繼日志中,接著SQL線程線程讀取中繼日志,并且在從服務器上重放,從而實現MySQL復制. MySQL具有可以運行多個實例的功能,這個功能是通過MySQLd_multi實現的.當一臺機器上需要運行多個MySQL服務器時,MySQLd_multi是管理多個MySQLd的服務進程,這些服務進程程序用不同的unix socket或是監聽不同的端口,通過命令,可以啟動,關閉和報告所管理的服務器的狀態. # 單機主從復制準備 首先停止MySQL服務器 ```shell service mysql stop ``` 把常用的工具添加到/usr/bin目錄,命令如下 ```shell ln -s /usr/local/mysql/bin/mysqld_multi /usr/bin/mysqld_multi ln -s /usr/local/mysql/scripts/mysql_install_db /usr/bin/mysql_install_db ``` 初始化三個數據數據目錄并安裝3個MySQL服務,命令如下 ```shell cd /usr/local/mysql/ mkdir -p /usr/local/var/mysql1 mkdir -p /usr/local/var/mysql2 mkdir -p /usr/local/var/mysql3 ./scripts/mysql_install_db --datadir=/usr/local/var/mysql1 --user=mysql ./scripts/mysql_install_db --datadir=/usr/local/var/mysql2 --user=mysql ./scripts/mysql_install_db --datadir=/usr/local/var/mysql3 --user=mysql ``` 從MySQL的源碼中把MySQLd_multi.server復制到/etc/init.d/目錄下,命令執行如下 ```shell cd /usr/local/mysql/support-files/ cp ./mysqld_mysqld.server/etc/init.d/mysql_multi.server ``` 配置數據庫文件.直接配置/etc/my.conf,修改相應的屬性 ```ini [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = root [mysqld1] port = 3306 [mysqld2] port = 3307 socket = /temp/mysql2.sock datadir = /usr/local/var/mysql2 [mysqld3] port = 3308 socket = /temp/mysql3.sock datadir = /usr/local/var/mysql3 [mysqld] ``` 使用mysqld_multi重啟服務器 ```shell mysqld_multi --defults-extra-file=/etc/my.cnf stop mysqld_multi --defults-extra-file=/etc/my.cnf start ``` 查看數據狀態 ```shell mysqld_multi --defults-extra-file=/etc/my.conf report ``` 出現以下信息表示成功 ```shell Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is running MySQL server from group: mysqld3 is running ``` 查看端口狀態 ```shell netstat -an|grep 330 ``` # 實現單機主從復制 開啟三個MySQL服務 ```shell mysqld_multi --defaults-extra-file=/etc/my.cnf start 1-3 netstat -an|grep 330 ``` 登錄Master服務器,設置一個復制使用的賬號,并賦予replication slave權限 ```sql grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123' grant replication slave on *.* to 'repl'@'%' identified by '123'; ``` 修改Master 主數據庫服務器的配置文件my.cnf,開啟binlog,并設置server-id的值,需要重啟服務器之后生效 ```ini [mysqld1] port = 3306 log-bin = /usr/local/var/mysql1/mysql-bin server-id = 1 ``` 在Master主服務器上,設置鎖定有效,這個操作是為了確保沒有數據庫操作,以便獲得一致性的快照 ```shell mysql -u root -P 3306 -S /tmp/mysql.sock flush tables with read lock; ``` 使用show master status命令查看日志情況,查詢得到主服務器上當前的二進制名和偏移量值.這個操作是為了從數據庫啟動以后,從這個點開始進行數據的恢復 ```sql show master status \G; ``` 主數據服務此時可以做一個備份,可以在服務器停止的情況下直接使用系統復制命令 ```shell tar -cvf data.tar data; ``` 備份完成后,主數據恢復寫操作 ```sql unlock tables; ``` > 主數據庫的配置已經成功,如果my.cnf的MySQLd選項設置server-id參數,從服務器沒有設置server-id,就會報錯 ```sql start slave; ``` 接下來編輯/etc/my.cnf文件,配置如下 ```ini [mysqld_multi] mysqld = /usr/local/mysql/binmysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = root [mysqld1] port = 3306 log-bin = /usr/local/var/mysql1/mysql-bin server-id = 1 [mysqld2] port = 3307 socket = /temp/mysql2.sock datadir = /usr/local/var/mysql2 log-bin = /usr/local/var/mysql2/mysql-bin server-id = 2 [mysqld3] port = 3308 socket = /temp/mysql3.sock datadir = /usr/local/var/mysql3 log-bin = /usr/local/var/mysql3/mysql-bin server-id = 3 [mysqld] ``` 重啟Master主服務器 ```shell mysqld_multi --defaults-extra-file=/etc/my.cnf stop 1-3 mysqld_multi --defaults-extra-file=/etc/my.cnf start 1-3 mysqld_multi --defaults-extra-file=/etc/my.cnf report 1-3 ``` 對數據庫服務器做相應的設置,此時需要制定復制使用的用戶,主數據的ip地址,端口以及開始復制的日志文件和位置等,具體設置如下. ```sql mysql -uroot -p -P 3307 -S /tmp/mysql2.sock show variables like '%log_bin%'; stop slave; change master to ->master_host='127.0.0.1' ->master_user='repl' ->master_password='123' ->master_log_file='mysql-bin.000001' ->master_log_pos=120; ``` ## 以下操作請結合上一節參考 在從服務器上執行show slave status \G命令查詢服務器的狀態 也可以執行show processlist \G查詢從服務器的進程狀態 此時,可以測試復制服務器的正確性