Shulou(Shulou.com)05/31 Report--

This article shares with you the content of a sample analysis of the Mysql data migration method tool. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

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 unchanged data structure

Export data

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

Recover data

Mysql-u root-p DATABESE_NAME

Source dump.sql

Connect to the database using pymysql

A database that can be connected directly with a 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 becomes 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 ()

Connect 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 becomes 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 services, 3306) # mysql service location #) # server_db = {#' user': 'root',#' passwd':', # 'host':' 127.0.0.1' # 'db':' server_db'#} # create a tunnel to bind the mysql on the server 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 cursor, often followed by a select statement using the

3.cursor.fetchone ()

Get the first result of cursor

4.cursor.lastrowid

Id of the last piece of data

5.cursor.executemany (insert_statement, data_list)

Insert a batch of data in bulk, 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 (% sdepartment% 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 the tuple package.

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

Now you want to get a list result set, like flat=True in Django.

There are two ways.

List analytic expression (list comprehension)

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

The disadvantage of this approach is that it is inefficient to use fetchall () to read the result set into memory and then convert the list.

Itertools tool

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

It is recommended that, first, it does not fetch all the results into memory, and second, using itertools to generate lists is faster than list parsing.

How to use Django's model in data migration

You need to copy the settings file of Django, delete the unwanted configuration, and set up the migration target database

You need to copy the files that use this model

The directory where the models.py file is located needs to be introduced into settings.INSTALLED_APPS

Start Django in the migration script header

Import osimport djangoimport syssys.path.append (os.path.dirname (os.path.dirname (os.path.abspath (_ _ file__) os.environ.setdefault ("DJANGO_SETTINGS_MODULE", "translate.settings") django.setup ()

Django connection to remote database through local forwarding of SSH tunnel

Create a ssh tunnel to map remote databases to local ports

Ssh-L local_port:localhost: @

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

Configure the database in the settings of Django

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 local port 'OPTIONS': {' init_command': 'SET default_storage_engine=INNODB '}

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

Matters needing attention

Know the amount of migration data in advance, and test the migration speed with 5% to 10% of the data.

If the total migration time is estimated from the test data, if the total migration time is more than one hour, be sure to put the migration script to the server to run, so that the migration process is not easy to interrupt, and the server performance is much better than the personal computer.

Try to use bulk inserts to reduce the number of writes to the database, using cursor.executemany or Django's bulk_create

Log should be written in the migration process, so that we can know where the data has been migrated, and if the terminal is unexpected, we can also find a breakpoint to continue to run.

The creation time field plus auto_add_now automatically records the creation time of the data. It is invalid to assign a value to this field when the data is inserted.

Thank you for reading! This is the end of the article on "sample Analysis of Mysql data Migration method tools". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.