MySQL5.6如何實現數據庫之間的相互遷移

下面講講關于MySQL5.6如何實現數據庫之間的相互遷移，文字的奧妙在于貼近主題相關。所以，閑話就不談了，我們直接看下文吧，相信看完MySQL5.6如何實現數據庫之間的相互遷移這篇文章你一定會有所受益。

###業務描述

1. 公司內部一個MySQL的master-slave架構上面跑有公司倆個項目的庫
2. 倆個項目的有存在共用的table的情況
3. 倆個項目都可以對共用的table進行讀寫操作
4. 項目A的一個子業務在每天固定的時間段內都會有高并發的寫操作,造成的后果就是在這個子業務工作的時間段內,倆個項目的讀寫操作會變得非常的慢,并且對外表現就是應用會很卡
5. 項目A的子業務使用的庫a是獨立的,和另外一個項目已經項目A下面的子業務并不存在關聯關系,是這個子業務獨立使用的

###需求:

1. 將庫a獨立剝離出來
2. 將業務A的關聯的數據庫剝離出來
3. 倆個共用的業務的table暫時還沒有方案

###方案:

1. 新建一套master-slave的主從環境使用5.6的最新版本
2. 搭建級聯環境
   • old master -> old slave -> new master -> old master
3. xtrabackup物理備份
4. pt-table-checksum 數據一致性校驗
5. 項目A的子業務可以停掉一段時間,因為是階段性的應用
6. 應用停掉的一段時間內,等待獨立的database沒有寫入.針對庫級別上面的表不做鎖操作
7. 應用停掉一段時間之后直接修改子業務的MySQL的配置信息,將新的數據寫入new master
8. 級聯組織的架構不做任何的修改,只是在業務層次做寫入的改動,這樣子造成的后果就是老的master-slave和新的master-slave針對這個獨立的database的數據是不一致的。
9. 在這里需要考慮的問題是舊的master-slave上面會不會因為部分業務忘記修改或者其他的原因,未完全將業務的讀寫遷移到新的master-slave架構上面,這樣會造成數據的不一致,并且在databases中萬一存在自增的建的話,可能會有建值沖突的情況。
10. 在完成業務MySQL的配置更改之后,四層的級聯架構保持原樣的目的是為了后續還需要繼續做業務剝離。

###環境:

1. os system CentOS 6.9
2. MySQL 5.6.38 最新版本

###步驟：

1. 備份：

   • 使用xtrabackup 2.4.4版本在old slave 上面進行物理備份,不在old master上面備份的主要原因在于為了避免備份的時候消耗系統資源和數據庫資源影響線上業務的正常使用,因為我們線上的業務所有的讀寫都是在主庫上面,并沒有做讀寫分離.所以針對于old slave的操作對于業務的影響幾乎可以忽略.
   • 在備份的時候需要考慮一個因素就是你的備份所放置云服務器的磁盤空間是否足夠,我第一次備份的時候并沒有使用流備份,就直接備份到本地,在備份傳輸的時候網絡IO和磁盤IO這塊需要花費更多的時間。

   • 沒有使用流備份:備份77G,花費9分鐘
     innobackupex --host=127.0.0.1 --user=root --password=xxxxx --port=3306  /path/BACKUP_DIR/

   • 使用流備份進行壓縮備份:備份12G,花費40分鐘
     innobackupex --host=127.0.0.1 --port=3306 --user=root --password=xxxxx --stream=tar /tmpdir  | gzip > /path/BACKUP_DIR/back_file.tar.gz

   • 因為是做4層的級聯復制,所以只需要備份old slave上面的binlog的file_name和file_position這倆個信息,根據這倆個信息change master創建主從復制.不過其中需要注意的文件有:
     • xtrabackup_binlog_info：這個文件里面記錄是在xtrabackup備份完非innodb數據之后,執行show master status獲取得到的信息,做主傳統主從復制的信息來自與這里.
     • xtrabackup_info：這個文件里面記錄了備份完全,釋放MySQL資源之后記錄一些具體的詳細信息,在這里也存在備份的binlog的信息,這個信息的主要來源是從redo log里面獲取得到的,當這個信息和上面那個文件信息不一致的時候,主要是以這個文件為主,主要原因在與redo log里面記錄的binlog信息打上commit標簽之后就表明事務是已經完成了的。
   • 需要注意的參數有:
     • --slave-info：在從庫上面執行備份,獲取主庫上面的二進制日志信息,并且會生成xtrabackup_slave_info文件記錄這些信息
     • --safe-slave-backup：和slave-info結合使用,主要是在發起備份的時候會暫停slave的sql_thread進程確保備份的時候沒有臨時表打開,保證數據的一致性
     • --safe-slave-backup-timeout=SECONDS：指定safe-slave-backup應該等待多長時間

2. 恢復：在新的master-slave上面備份數據達到一致的狀態,其目的是為了應用redo log:

   innobackupex --defaults-file=/etc/my.cnf --apply-log /path/BACKUP_DIR

   將數據恢復到datadir目錄下面:

   innobackupex --defaults-file=/etc/my.cnf --copy-back /path/BACKUP_DIR

   修改datadir的數據權限

   chown mysql.mysql /datadir -R

3. 搭建new master-slave：在搭建old slave -> new master架構之前先搭建new master-slave 架構
   • 需要注意的是,在搭建之前需要注意事項有:
     • GTID是否開啟,因為 old master-slave 是基于傳統復制,所以new的master-slave不能開啟GTID復制
     • server-id不能一致
     • binlog的日志格式,四個必須保持一致
     • 在配置文件my.cnf 添加參數 “replicate-ingore-db=mysql.* ” 的目的是為了不復制系統庫mysql的信息,目的是因為后續的用戶權限管理;并且5.6并不支持在線修改這些復制過濾,只能在配置文件里面修改之后重啟數據庫。
   • 在new master-slave備份恢復之后,直接可以在new master上面show master status獲取binlog信息,根據這些信息做主從同步.
4. old slave -> new master 搭建主從：從第一步備份獲取得到slave的info信息搭建主從
5. 監測主從是否搭建成功,在old slave -> new master -> new slave 執行show slave status信息觀察

6. pt-table-checksum檢測數據的一致性

   • 命令：
     pt-table-checksum --replicate=percona.checksumss --nocheck-replication-filters --no-check-binlog-format h=x.x.x.x,u=rpl,p='xxxxx',P=3306 --databases-regex=database.* --recursion-method dsn=h=x.x.x.x,u=root,p='xxxxx',P=3306,D=zst_teach,t=dsns

   • 注意：
     • 命令第一個鏈接的主機是需要監測的master-slave中master實例信息
     • 命令中的dsn后續的鏈接信息是dsn存在信息的MySQL實例,這個dsn信息可以存放在master,也可以存放到第三方實例,這個時候pt-table-checksum執行所在的云服務器需要有root用戶訪問zst_teach.dsns表信息的權限,即select的權限
     • 命令在執行的時候會在master上面產生percona.checksumss信息,其中percona庫是不會手動生成的,需要自己手動生成,但是checksums會自動生成,但是其中需要注意的是,由于是在master上面生成percona.checksumss信息表,所以rpl@command_host用戶需要有percona.checksums的select,insert,update,delete,super,process,lock tables,craete的權限,而且還必須有針對所有表的select,lock tables,super,process權限。
     • 并且為了在slave進行檢測,檢測的用戶也必須要有所以表的select,super,lock tables,process的權限
     • master:
       grant update,delete,insert,super,process,lock tables,create on *.* 'user'@'command_host'
   • slave:
     grant select,process,lock tabes,super on *.* to 'user'@'master_host';

7.pt-table-sync數據同步(在master和slave都可以執行)

• pt-table-sync --print  --sync-to-master h=slave_host,P=3306,u=repl,p='xxxxx' --database=DB_name --tables=table_name1,table_name2

  --print 打印出在slave執行的SQL
  --sync-to-master 指定slave的IP地址,從show slave status獲取master的信息

• 對于以上MySQL5.6如何實現數據庫之間的相互遷移相關內容，大家還有什么不明白的地方嗎？或者想要了解更多相關，可以繼續關注我們的行業資訊板塊。