Analysis of Mysql Data Migration Method Tool

This article mainly introduces the mysql data migration method tool analysis, in this article through the example code introduction is very detailed, for everyone's study or work has 1 set of reference learning value, the need for friends can refer to the following

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

mysqldump: Data migration with invariant data structure

Export data

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

Recovery of data

mysql -u root -p DATABESE_NAME < dump.sql

Or connect to the mysql client

mysql> source dump.sql

Connecting to the database using pymysql

Databases that can be directly connected by user name 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:
      # 1 Row data will become 1 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:
      # 1 Row data will become 1 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 1 Tunnels will set the server-side mysql Bind to local 3306 Port 
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 1 sql statement

2.cursor.fetchall()

Gets all the results of cursor, which is often used after select statement

3.cursor.fetchone()

Get the first result of cursor

4.cursor.lastrowid

id of the last data

5.cursor.executemany(insert_statement, data_list)

Insert 1 batch of data in batches, 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 the result of list type from cursor

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

Using fetchall () directly, you can only get the data of tuple package

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

Now you want to get an list result set like flat=True in Django

There are two ways

List resolution (list comprehension)

mysql -u root -p DATABESE_NAME < dump.sql

0

The disadvantage of this method is that it will use fetchall () to read the result set into memory first, and then do list conversion, which is not efficient.

itertools Tools

mysql -u root -p DATABESE_NAME < dump.sql

1

It is recommended to use this method. First, it will not put all the results fetch into memory. Second, it is faster to use itertools to generate a list than to parse a list

How to use model of Django in data migration

Need to copy the settings file of Django, delete the unnecessary configuration, 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 head of the migration script

mysql -u root -p DATABESE_NAME < dump.sql

2

Realization of Django connection to remote database through local forwarding of SSH tunnel

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

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

While an ssh connection is in progress, remote databases can be accessed by accessing the local port

Configuring the database in settings of Django

mysql -u root -p DATABESE_NAME < dump.sql

4

At this point, when using model of Django, the remote database will be accessed through ssh tunnel

Matters needing attention

Know the amount of migration data in advance, and take 5% ~ 10% of the data to test the migration speed The total migration time is estimated from the test data. If the total migration time is more than 1 hour, 1 must put the migration script on the server to run, so that the migration process is not easy to interrupt, and the server performance is far better than the personal computer Try to use bulk inserts to reduce the number of writes to the database, using cursor. executemany or bulk_create of Django log should be written in the migration process, so that you can know which step the data migrates to. If there is an accident, the terminal can find a breakpoint and continue to run The creation time field plus auto_add_now automatically records the creation time of the data, and the value assigned to this field is invalid when inserting the data