Analysis of Mysql data migration methods and tools

This article mainly introduces the analysis of MySQL data migration methods and tools. The article introduces it in great detail through sample code, which has a certain reference value for everyone's study or work. Friends in need can refer to it.

Data migration is one of the tasks that every backend will encounter. This article introduces some common data migration methods and tools

mysqldump: Data migration without changing the data structure

Exporting Data

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

Recover Data

mysql -u root -p DATABESE_NAME < dump.sql

Or connect to mysql client

mysql> source dump.sql

Connect to the database using pymysql

Database that can be connected directly with username and password

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:
      # A row of data will become a dictionary 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()

use:

# 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()

Connecting to a database on a server that requires an SSH connection

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:
      # A row of data will become a dictionary 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()

use:

#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 service location# )
# server_db = {
# 'user': 'root',
# 'passwd': '',
# 'host': '127.0.0.1',
# 'db': 'server_db'
# }
# Create a tunnel to bind the server's mysql to the local port 3306 with const.SERVER as server:
  with SSHConnector(server, const.server_db) as cursor:
    cursor.execute('show tables;')
    data = cursor.fetchall()
    print(data)

Various operations of cursor

1.cursor.execute(sql_statement)

Execute a SQL statement

2. cursor.fetchall()

Get all the results of the cursor, often used after the select statement

3. cursor.fetchone()

Get the first result of the cursor

4. cursor.lastrowid

The last data id

5.cursor.executemany(insert_statement, data_list)

Batch insert a batch of data, such as

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)

Get list type results from cursor

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

Using fetchall() directly can only get the data wrapped in tuple

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

Now I want to get a list result set, just like flat=True in Django

There are two ways

List comprehension

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

The disadvantage of this method is that it first uses fetchall() to read the result set into memory and then converts it to a list, which is not efficient.

itertools

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

This method is recommended. First, it does not fetch all results into memory. Second, using itertools to generate lists is faster than list comprehension.

How to use Django models in data migration

• You need to copy Django's settings file, delete unnecessary configurations, and set up the migration target database.
• You need to copy the files used in this model
• You need to introduce the directory where the models.py file is located in settings.INSTALLED_APPS
• Start Django at the top of your migration script

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()

Django connects to a remote database via local forwarding in an SSH tunnel

Create an ssh tunnel to map the remote database to a local port

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

When the ssh connection is in progress, you can access the remote database by accessing the local port

Configure the database in Django settings

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': db_name,
    'USER': remote_mysql_user, # Remote database account password 'PASSWORD': remote_mysql_password,
    'HOST': "localhost",
    'PORT': local_port, # Remote database is mapped to the local port 'OPTIONS': {'init_command': 'SET default_storage_engine=INNODB;'}
    }
}

At this point, when using Django's model, you will access the remote database through the ssh tunnel

Precautions

• Know the amount of data to be migrated in advance and take 5% to 10% of the data to test the migration speed
• Estimate the total migration time based on test data. If the total migration time is greater than one hour, be sure to run the migration script on the server so that the migration process is not easily interrupted. The server performance is much better than that of a personal computer.
• Try to use batch inserts to reduce the number of times you write to the database, using cursor.executemany or Django's bulk_create
• The migration process should be logged so that you can know which step the data has been migrated to and find the breakpoint to continue running if an unexpected terminal occurs.
• The creation time field plus auto_add_now will automatically record the creation time of the data. When inserting data, assigning a value to this field will be invalid.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.