Mysql數據遷徙方法工具的示例分析

這篇文章給大家分享的是有關(guān)數據遷徙方法工具的示例分析的內容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。

數據遷徙是每個(gè)后端都會(huì )遇到的工作之一，本文介紹了一些常見(jiàn)的數據遷徙方法與工具

mysqldump：數據結構不變的數據遷徙

導出數據

mysqldump -u root -p DATABASE_NAME table_name > dump.sql

恢復數據

mysql -u root -p DATABESE_NAME < dump.sql

或者連接mysql客戶(hù)端

mysql> source dump.sql

使用pymysql連接數據庫

可以直接用用戶(hù)名密碼連接的數據庫

class GeneralConnector:
  def __init__(self, config, return_dic=False):
    self.return_dic = return_dic
    self.config = config

  def __enter__(self):
    self.conn = pymysql.connect(**self.config, port=3306)
    if self.return_dic:
      # 一行數據會(huì )變成一個(gè)字典
      self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
    else:
      self.cursor = self.conn.cursor()
    return self.cursor

  def __exit__(self, *args):
    self.cursor.close()
    self.conn.commit()
    self.conn.close()

使用：

# local_db = {
#   'user': 'root',
#   'passwd': '',
#   'host': '127.0.0.1',
#   'db': 'local_db'
#   }
with GeneralConnector(const.local_db, return_dic=True) as cursor:
  cursor.execute('SELECT `col1`, `col2` FROM test;')
  return cursor.fetchall()

連接處于需要SSH連接的的數據庫

class SSHConnector:
  def __init__(self, server, config, return_dic=False):
    self.return_dic=return_dic
    self.server = server
    self.config = config

  def __enter__(self):
    self.conn = pymysql.connect(**self.config, port=self.server.local_bind_port)
    if self.return_dic:
      # 一行數據會(huì )變成一個(gè)字典
      self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
    else:
      self.cursor = self.conn.cursor()
    return self.cursor

  def __exit__(self, *args):
    self.cursor.close()
    self.conn.commit()
    self.conn.close()

使用：

# SERVER = SSHTunnelForwarder(
#     (remote_host, ssh_port),
#     ssh_username=USERNAME,
#     ssh_pkey=SSH_KEY,
#     ssh_private_key_password=SSH_KEY_PASSWD,
#     remote_bind_address=('127.0.0.1', 3306) # mysql服務(wù)位置
#   )
# server_db = {
#   'user': 'root',
#   'passwd': '',
#   'host': '127.0.0.1',
#   'db': 'server_db'
#   }
# 創(chuàng  )建一個(gè)隧道將服務(wù)端的mysql綁定到本地3306端口
with const.SERVER as server:
  with SSHConnector(server, const.server_db) as cursor:
    cursor.execute('show tables;')
    data = cursor.fetchall()
    print(data)

cursor的各種操作

1.cursor.execute(sql_statement)

執行一條sql語(yǔ)句

2.cursor.fetchall()

獲取cursor的所有結果，常跟在select語(yǔ)句后使用

3.cursor.fetchone()

獲取cursor的第一條結果

4.cursor.lastrowid

最后一條數據的id

5.cursor.executemany(insert_statement, data_list)

批量插入一批數據，如

with const.SERVER as server:
  with connector.Connector(server, const.db_1) as cursor:
    cursor.execute('select * from preference')
    preferences = cursor.fetchall()

  with connector.Connector(server, const.db_2) as cursor:
    cursor.executemany('insert into preference (`id`,`theme`,`user_id`) values (%s,%s,%s)',preferences)

從cursor獲取list類(lèi)型的結果

cursor.execute('SELECT `name` FROM user;')

直接使用fetchall()，只能得到tuple包裹的數據

cursor.fetchall()
# (('Jack',), ('Ben'))

現在希望得到一個(gè)list結果集，做到像Django中flat=True那樣的效果

有兩種方法

列表解析式(list comprehension)

name_list = [x[0] for x in cursor.fetchall()]

這個(gè)方法的缺點(diǎn)在于會(huì )先使用fetchall()將結果集讀到內存，再做列表轉換，并不高效。

itertools工具

name_list = list(itertools.chain.from_iterable(cursor))

推薦使用這個(gè)方式，第一它不會(huì )將所有結果fetch到內存中，第二使用itertools生成列表比列表解析式要快

如何在數據遷徙中使用Django的model

• 需要拷貝Django的settings文件，刪掉不需要的配置，并設置好遷徙目標數據庫

• 需要拷貝用到此model的文件

• 需要在settings.INSTALLED_APPS中引入models.py文件所在的目錄

• 在遷徙腳本頭部啟動(dòng)Django

import os
import django
import sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "translate.settings")
django.setup()

通過(guò)SSH隧道的本地轉發(fā)實(shí)現Django連接遠程數據庫

創(chuàng )建一個(gè)ssh隧道，將遠程數據庫映射到本地端口

ssh -L local_port:localhost:<remote mysql port> <username>@<remote host>

ssh連接進(jìn)行時(shí)，可以通過(guò)訪(fǎng)問(wèn)本地端口來(lái)訪(fǎng)問(wèn)遠程數據庫

在Django的settings中配置數據庫

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': db_name,
    'USER': remote_mysql_user, # 遠程數據庫賬號密碼
    'PASSWORD': remote_mysql_password,
    'HOST': "localhost",
    'PORT': local_port, # 遠程數據庫映射到本地的端口
    'OPTIONS': {'init_command': 'SET default_storage_engine=INNODB;'}
    }
}

至此，在使用Django的model時(shí)，將通過(guò)ssh隧道訪(fǎng)問(wèn)遠程數據庫

注意事項

• 事先了解遷徙數據量，并且取5%~10%的數據測試遷徙速度

• 由測試數據預估總遷徙用時(shí)，如果總遷徙用時(shí)大于一小時(shí)，一定要把遷徙腳本放到服務(wù)器運行，這樣遷徙過(guò)程不易中斷，且服務(wù)器性能遠比個(gè)人電腦更優(yōu)

• 盡量使用批量插入減少寫(xiě)數據庫的次數，使用cursor.executemany或者Django的bulk_create

• 遷徙過(guò)程要寫(xiě)好log，這樣能夠知道數據遷徙到了哪一步，如意外終端也能找到斷點(diǎn)繼續運行

• 創(chuàng )建時(shí)間字段加上auto_add_now會(huì )自動(dòng)記錄數據的創(chuàng )建時(shí)間，在插入數據的時(shí)候對這個(gè)字段賦值無(wú)效