linux環境下創建MySQL數據庫的雙擊熱備

2024-07-24 12:34:43

字體：大中小

來源：轉載

供稿：網友

linux環境下搭建MySQL數據庫的雙擊熱備：

　　準備服務器
　　由于Mysql不同版本之間的（二進制日志）binlog格式可能會不太一樣，因此最好的搭配組合是主（Master）服務器的Mysql版本和從（Slave）服務器版本相同或者更低，主服務器的版本肯定不能高于從服務器版本。
　　本次我用于測試的兩臺服務器版本都是Mysql-5.5.17。

　　Mysql 建立主－從服務器雙機熱備配置步驟
　　2.1環境描述
　　A服務器（主服務器Master）：59.151.15.36
　　B服務器（從服務器Slave）：218.206.70.146
　　主從服務器的Mysql版本皆為5.5.17
　　Linux環境下
　　將主服務器需要同步的數據庫內容進行備份一份，上傳到從服務器上，保證始初時兩服務器中數據庫內容一致。
　　不過這里說明下，由于我是利用Mysql在安裝后就有的數據庫test進行測試的，所以兩臺服務器里面是沒有建立表的，只不分別在test里面建立了同樣的一張空表tb_mobile;
　　Sql語句如下：
　　mysql> create table tb_mobile( mobile VARCHAR(20) comment'手機號碼', time timestamp DEFAULT now() comment'時間' );
　　2.2 主服務器Master配置
　　2.2.1 創建同步用戶
　　進入mysql操作界面，在主服務器上為從服務器建立一個連接帳戶，該帳戶必須授予REPLICATION SLAVE權限。因為從mysql版本3.2以后就可以通過REPLICATION對其進行雙機熱備的功能操作。
　　操作指令如下：
　　mysql> grant replication slave on . to 'replicate'@'218.206.70.146' identified by '123456';
　　mysql> flush privileges;
　　創建好同步連接帳戶后，我們可以通過在從服務器（Slave）上用replicat帳戶對主服務器（Master）數據庫進行訪問下，看下是否能連接成功。
　　在從服務器（Slave）上輸入如下指令：
　　[root@YD146 ~]# mysql -h69.151.15.36 -ureplicate -p123456
　　如果出現下面的結果，則表示能登錄成功，說明可以對這兩臺服務器進行雙機熱備進行操作。
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　2.2.2 修改mysql配置文件
　　如果上面的準備工作做好，那邊我們就可以進行對mysql配置文件進行修改了，首先找到mysql配置所有在目錄，一般在安裝好mysql服務后，都會將配置文件復制一一份出來放到/ect目錄下面，并且配置文件命名為：my.cnf。即配置文件準確目錄為/etc/my.cnf
　　(Linux下用rpm包安裝的MySQL是不會安裝/etc/my.cnf文件的，
　　至于為什么沒有這個文件而MySQL卻也能正常啟動和作用，在點有兩個說法，
　　第一種說法，my.cnf只是MySQL啟動時的一個參數文件，可以沒有它，這時MySQL會用內置的默認參數啟動，
　　第二種說法，MySQL在啟動時自動使用/usr/share/mysql目錄下的my-medium.cnf文件，這種說法僅限于rpm包安裝的MySQL，
　　解決方法，只需要復制一個/usr/share/mysql目錄下的my-medium.cnf文件到/etc目錄，并改名為my.cnf即可。)
　　找到配置文件my.cnf打開后，在[mysqld]下修改即可：
　　[mysqld]
　　server-id = 1　　　　　　　　//唯一id
　　log-bin=mysql-bin //其中這兩行是本來就有的，可以不用動，添加下面兩行即可.指定日志文件
　　binlog-do-db = test　　　　 //記錄日志的數據庫
　　binlog-ignore-db = mysql //不記錄日志的數據庫
　　2.2.3 重啟mysql服務
　　修改完配置文件后，保存后，重啟一下mysql服務，如果成功則沒問題。
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　2.2.4 查看主服務器狀態
　　進入mysql服務后，可通過指令查看Master狀態，輸入如下指令：
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　注意看里面的參數，特別前面兩個File和Position，在從服務器（Slave）配置主從關系會有用到的。
　　注：這里使用了鎖表，目的是為了產生環境中不讓進新的數據，好讓從服務器定位同步位置，初次同步完成后，記得解鎖。
　　linux環境下搭建MySQL數據庫的雙擊熱備

　　2.3 從服務器Slave配置
　　2.3.1修改配置文件
　　因為這里面是以主－從方式實現mysql雙機熱備的，所以在從服務器就不用在建立同步帳戶了，直接打開配置文件my.cnf進行修改即可，道理還是同修改主服務器上的一樣，只不過需要修改的參數不一樣而已。如下：
　　[mysqld]
　　server-id = 2
　　log-bin=mysql-bin
　　replicate-do-db = test
　　replicate-ignore-db = mysql,information_schema,performance_schema
　　2.3.2重啟mysql服務
　　修改完配置文件后，保存后，重啟一下mysql服務，如果成功則沒問題。
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　2.3.3用change mster 語句指定同步位置
　　這步是最關鍵的一步了，在進入mysql操作界面后，輸入如下指令：
　　mysql>stop slave; //先停步slave服務線程，這個是很重要的，如果不這樣做會造成以下操作不成功。
　　mysql>change master to

　　master_host='59.151.15.36',master_user='replicate',master_password='123456',
　　master_log_file=' mysql-bin.000016 ',master_log_pos=107;
　　注：master_log_file, master_log_pos由主服務器（Master）查出的狀態值中確定。也就是剛剛叫注意的。master_log_file對應File, master_log_pos對應Position。Mysql 5.x以上版本已經不支持在配置文件中指定主服務器相關選項。
　　遇到的問題，如果按上面步驟之后還出現如下情況：
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　則要重新設置slave。指令如下
　　mysql>stop slave;
　　mysql>reset slave;
　　之后停止slave線程重新開始。成功后，則可以開啟slave線程了。
　　mysql>start slave;
　　2.3.4查看從服務器（Slave）狀態
　　用如下指令進行查看
　　mysql> show slave status/G;
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　查看下面兩項值均為Yes，即表示設置從服務器成功。
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　2.4 測試同步
　　之前開始已經說過了在數據庫test只有一個表tb_mobile沒有數據，我們可以先查看下兩服務器的數據庫是否有數據：
　　Master:59.151.15.36
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　Slave:218.206.70.146
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　好了，現在可以在Master服務器中插入數據看下是否能同步。
　　Master:59.151.15.36
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　Slave:218.206.70.146
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　可以從上面兩個截圖上看出，在Master服務器上進行插入的數據在Slave服務器可以查到，這就表示雙機熱備配置成功了。

　　Mysql 建立主－主服務器雙機熱備配置步驟
　　服務器還是用回現在這兩臺服務器
　　3.1創建同步用戶
　　同時在主從服務器建立一個連接帳戶，該帳戶必須授予REPLIATION SLAVE權限。這里因為服務器A和服務器B互為主從，所以都要分別建立一個同步用戶。
　　服務器A：
　　mysql> grant replication slave on . to 'replicate'@'218.206.70.146' identified by '123456';
　　mysql> flush privileges;
　　服務器B：
　　mysql> grant replication slave on . to 'replicate'@'59.151.15.36' identified by '123456';
　　mysql> flush privileges;
　　3.2修改配置文件my.cnf
　　服務器A
　　[mysqld]
　　server-id = 1
　　log-bin=mysql-bin
　　binlog-do-db = test
　　binlog-ignore-db = mysql
　　#主－主形式需要多添加的部分
　　log-slave-updates
　　sync_binlog = 1
　　auto_increment_offset = 1
　　auto_increment_increment = 2
　　replicate-do-db = test
　　replicate-ignore-db = mysql,information_schema
　　服務器B：
　　[mysqld]
　　server-id = 2
　　log-bin=mysql-bin
　　replicate-do-db = test
　　replicate-ignore-db = mysql,information_schema,performance_schema
　　#主－主形式需要多添加的部分
　　binlog-do-db = test
　　binlog-ignore-db = mysql
　　log-slave-updates
　　sync_binlog = 1
　　auto_increment_offset = 2
　　auto_increment_increment = 2
　　3.3分別重啟A服務器和B服務器上的mysql服務
　　重啟服務器方式和上面的一樣，這里就不做講解了。
　　3.4分別查A服務器和B服務器作為主服務器的狀態
　　服務器A：
　　linux環境下搭建MySQL數據庫的雙擊熱備

　　服務器B：
　　linux環境下搭建MySQL數據庫的雙擊熱備
　　3.5分別在A服務器和B服務器上用change master to 指定同步位置
　　服務器A：
　　mysql>change master to

　　master_host='218.206.70.146',master_user='replicate',master_password='123456',
　　master_log_file=' mysql-bin.000011 ',master_log_pos=497;
　　服務器B：
　　mysql>change master to
　　master_host='59.151.15.36',master_user='replicate',master_password='123456',
　　master_log_file=' mysql-bin.000016 ',master_log_pos=107;
　　3.6 分別在A和B服務器上重啟從服務線程
　　mysql>start slave;
　　3.7 分別在A和B服務器上查看從服務器狀態
　　mysql>show slave status/G;
　　查看下面兩項值均為Yes，即表示設置從服務器成功。
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　3.8 測試主－主同步例子

　　然后在從服務器A上查看是否有同步數據

　　最后從結果可以看出主－主形式的雙機熱備是能成功實現的。

　　配置參數說明
　　Server-id
　　ID值唯一的標識了復制群集中的主從服務器，因此它們必須各不相同。Master_id必須為1到232－1之間的一個正整數值，slave_id值必須為2到232－1之間的一個正整數值。
　　Log-bin
　　表示打開binlog，打開該選項才可以通過I/O寫到Slave的relay-log，也是可以進行replication的前提。
　　Binlog-do-db
　　表示需要記錄二進制日志的數據庫。如果有多個數據可以用逗號分隔，或者使用多個binlog-do-dg選項。
　　Binglog-ingore-db
　　表示不需要記錄二進制日志的數據庫，如果有多個數據庫可用逗號分隔，或者使用多binglog-ignore-db選項。
　　Replicate-do-db
　　表示需要同步的數據庫，如果有多個數據可用逗號分隔，或者使用多個replicate-do-db選項。
　　Replicate-ignore-db
　　表示不需要同步的數據庫，如果有多個數據庫可用逗號分隔，或者使用多個replicate-ignore-db選項。
　　Master-connect-retry
　　master-connect-retry=n表示從服務器與主服務器的連接沒有成功，則等待n秒（s）后再進行管理方式（默認設置是60s）。如果從服務器存在mater.info文件，它將忽略些選項。
　　Log-slave-updates
　　配置從庫上的更新操作是否寫入二進制文件，如果這臺從庫，還要做其他從庫的主庫，那么就需要打這個參數，以便從庫的從庫能夠進行日志同步。
　　Slave-skip-errors
　　在復制過程，由于各種原因導致binglo中的sql出錯，默認情況下，從庫會停止復制，要用戶介入。可以設置slave-skip-errors來定義錯誤號，如果復制過程中遇到的錯誤是定義的錯誤號，便可以路過。如果從庫是用來做備份，設置這個參數會存在數據不一致，不要使用。如果是分擔主庫的查詢壓力，可以考慮。
　　--slave-skip-errors=[err_code1,err_code2,...|all|ddl_exist_errors]
　　Command-Line Format --slave-skip-errors=name
　　Option-File Format slave-skip-errors
　　System Variable Name slave_skip_errors
　　Variable Scope Global
　　Dynamic Variable No
　　Permitted Values
　　Type string
　　Default OFF
　　Valid Values OFF
　　[list of error codes]
　　all
　　ddl_exist_errors
　　MySQL 5.6 as well as MySQL Cluster NDB 7.3 support an additional shorthand value
　　ddl_exist_errors, which is equivalent to the error code list 1007,1008,1050,1051,
　　1054,1060,1061,1068,1094,1146.
　　Examples:

　　--slave-skip-errors=1062,1053
　　--slave-skip-errors=all
　　--slave-skip-errors=ddl_exist_errors
　　Sync_binlog=1 Or N
　　Sync_binlog的默認值是0，這種模式下，MySQL不會同步到磁盤中去。這樣的話，Mysql依賴操作系統來刷新二進制日志binary log，就像操作系統刷新其他文件的機制一樣。因此如果操作系統或機器（不僅僅是Mysql服務器）崩潰，有可能binlog中最后的語句丟失了。要想防止這種情況，可以使用sync_binlog全局變量，使binlog在每Ｎ次binlog寫入后與硬盤同步。當sync_binlog變量設置為１是最安全的，因為在crash崩潰的情況下，你的二進制日志binary log只有可能丟失最多一個語句或者一個事務。但是，這也是最慢的一種方式（除非磁盤有使用帶蓄電池后備電源的緩存cache,使得同步到磁盤的操作非常快）。
　　即使sync_binlog設置為１，出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。如果使用InnoDB表，Mysql服務器處理COMMIT語句，它將整個事務寫入binlog并將事務提交到InnoDB中。如果在兩次操作之間出現崩潰，重啟時，事務被InnoDB回滾，但仍然存在binlog中。可以用-innodb-safe-binlog選項來增加InnoDB表內容和binlog之間的一致性。（注釋：在Mysql 5.1版本中不需要-innodb-safe-binlog；由于引入了XA事務支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務的binlog(sync_binlog=1)和（默認情況為真）InnoDB日志與硬盤同步，該選項的效果是崩潰后重啟時，在滾回事務后，Mysql服務器從binlog剪切回滾的InnoDB事務。這樣可以確保binlog反饋InnoDB表的確切數據等，并使從服務器保持與主服務器保持同步（不接收回滾的語句）。
　　Auto_increment_offset和Auto_increment_increment
　　Auto_increment_increment和auto_increment_offset用于主－主服務器（master-to-master）復制，并可以用來控制AUTO_INCREMENT列的操作。兩個變量均可以設置為全局或局部變量，并且假定每個值都可以為1到65,535之間的整數值。將其中一個變量設置為0會使該變量為1。
　　這兩個變量影響AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值，auto_increment_offset確定AUTO_INCREMENT列值的起點。
　　如果auto_increment_offset的值大于auto_increment_increment的值，則auto_increment_offset的值被忽略。例如：表內已有一些數據，就會用現在已有的最大自增值做為初始值。

　　如何解決MySQL主從同步錯誤的SQL
　　解決：
　　stop slave;
　　#表示跳過一步錯誤，后面的數字可變
　　set global sql_slave_skip_counter =1;
　　start slave;
　　之后再用mysql> show slave status/G 查看：
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　ok，現在主從同步狀態正常了。

（編輯：武林網）

上一篇：運用sysbench對MySQL進行測試

下一篇：AWS RDS MySQL和MariaDB Galera Cluster的優缺點對比總結