UDN-企业互联网技术人气社区

板块导航

浏览  : 808
回复  : 0

[讨论交流] 利用redis协助mysql数据库搬迁

[复制链接]
菊花一朵朵的头像 楼主
发表于 2016-3-24 21:56:04 | 显示全部楼层 |阅读模式

  最近公司新项目上线,需要数据库搬迁,但新版本和老版本数据库差距比较大,关系也比较复杂。如果用传统办法,需要撰写很多mysql脚本,工程量虽然不大,但对于没有dba的公司来说,稍微有点难度。本人就勉为其难,用redis作为mysql中转站,先把原来的mysql数据转移到redis里面,再从redis转换到新的mysql。整个过程脉络清晰,脚本简单。

  首先,公司之前的项目是,flask+sqlalchemy的方式,直接复制原来的model文件,稍微修改一下,就有以下代码。
  1. # coding:utf-8
  2. from sqlalchemy import create_engine, ForeignKey, Column, Integer, String, Text, DateTime, Boolean, and_, or_,\
  3.     SmallInteger, func, Numeric, select, Float, Table, TIMESTAMP, DECIMAL, desc
  4. from sqlalchemy.orm import relationship, backref, sessionmaker, scoped_session
  5. from sqlalchemy.ext.declarative import declarative_base
  6. from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method, Comparator
  7. from sqlalchemy import event
  8. import redis
  9. import json

  10. mysql_info = "mysql://username:password@ip_address:3306/databasename?charset=utf8"
  11. redis_store = redis.Redis(host='127.0.0.1', port=6380, password='password', db=5)

  12. engine = create_engine(mysql_info, pool_recycle=7200)
  13. Base = declarative_base()


  14. class User(Base):
  15.     __tablename__ = 'user'

  16.     id = Column('id', Integer, primary_key=True)
  17.     phone_number = Column('phone_number', String(11), index=True)
  18.     password = Column('password', String(30))
  19.     nickname = Column('nickname', String(30), index=True, nullable=True)
  20.     headpic = Column('headpic', String(100), nullable=True)
  21.     register_time = Column('register_time', DateTime, index=True)
  22.     email = Column('email', String(50), default='', index=True)
  23.     real_name = Column('real_name', String(50), default='', index=True)
  24.     user_admin = Column('user_admin', Integer, index=True, default=0)
  25.     rc_token = Column('rc_token', String(100))
  26.     device_token = Column('device_token', String(100), unique=True, nullable=True)
  27.     unit_id = Column('unit_id', Integer, ForeignKey('unit.id'))
  28.     unit = relationship("Unit", backref=backref('users'))

  29.     def to_dict(self):
  30.         return dict(id=self.id, phone_number=self.phone_number,
  31.                     password=self.password, nickname=self.nickname,
  32.                     headpic=self.headpic, register_time=self.register_time.strftime('%Y-%m-%d %H:%M:%S'),
  33.                     user_admin=self.user_admin, rc_token=self.rc_token,
  34.                     unit_id=self.unit_id, device_token=self.device_token,
  35.                     )


  36. class Group(Base):
  37.     __tablename__ = 'groups'

  38.     id = Column('id', Integer, primary_key=True)
  39.     name = Column('name', String(100), index=True)
  40.     address = Column('address', String(100), index=True, nullable=True)
  41.     contact_person = Column('contactperson', String(30), nullable=True)
  42.     contact_number = Column('contactnumber', String(30), nullable=True)
  43.     unit_id = Column('unit_id', Integer, ForeignKey('unit.id'))
  44.     unit = relationship('Unit', backref=backref('groups'))


  45. class Monitor(Base):
  46.     __tablename__ = 'monitor'

  47.     id = Column('id', Integer, primary_key=True)
  48.     u_id = Column('sn_num', String(10), index=True)
  49.     phone_num = Column('phone_num', String(20), index=True, nullable=True)
  50.     name = Column('name', String(40), index=True, nullable=True)
  51.     position = Column('position', String(40), nullable=True)
  52.     join_time = Column('join_time', DateTime, index=True, nullable=True)
  53.     group_id = Column('group_id', Integer, ForeignKey('groups.id'))
  54.     group = relationship("Group", backref=backref('monitors'))
  55.     longitude = Column('longitude', DECIMAL(12, 9), default=31.000000)
  56.     latitude = Column('latitude', DECIMAL(12, 9), default=121.000000)

  57.     def to_dict(self):
  58.         unit_id = self.group.unit_id
  59.         return dict(id=self.id, u_id=self.u_id, phone_num=self.phone_num, name=self.name,
  60.                     position=self.position, join_time=self.join_time.strftime('%Y-%m-%d %H:%M:%S'), unit_id=unit_id,
  61.                     longitude=str(self.longitude), latitude=str(self.latitude))


  62. class Unit(Base):
  63.     __tablename__ = 'unit'

  64.     id = Column('id', Integer, primary_key=True)
  65.     name = Column('name', String(100), index=True, nullable=True)
  66.     address = Column('address', String(100), index=True, nullable=True)
  67.     contact_person = Column('contactperson', String(30), index=True, nullable=True)
  68.     contact_number = Column('contactnumber', String(30), nullable=True)
  69.     device_operation_password = Column('device_operation_password', String(4), nullable=True)
  70.     rc_group_id = Column('rc_group_id', String(50), index=True, nullable=True)
  71.     rc_group_name = Column('rc_group_name', String(50), index=True, nullable=True)
  72.     longitude = Column('longitude', DECIMAL(12, 9), nullable=True)
  73.     latitude = Column('latitude', DECIMAL(12, 9), nullable=True)
  74.     active = Column('active', SmallInteger, index=True, default=0)

  75.     def to_dict(self):
  76.         return dict(id=self.id, name=self.name, address=self.address,
  77.                     contact_person=self.contact_person, contact_number=self.contact_number,
  78.                     device_operation_password=self.device_operation_password,
  79.                     rc_group_id=self.rc_group_id, rc_group_name=self.rc_group_name,
  80.                     longitude=str(self.longitude), latitude=str(self.latitude), active=self.active)

  81. db_session = scoped_session(sessionmaker(autocommit=False,
  82.                                          autoflush=False,
  83.                                          bind=engine))

  84. Base.query = db_session.query_property()


  85. def old_sql_2_redis():
  86.     redis_store.flushdb()
  87.     units = Unit.query.all()
  88.     [redis_store.rpush('units', json.dumps(unit.to_dict())) for unit in units]

  89.     users = User.query.all()
  90.     [redis_store.rpush('users', json.dumps(user.to_dict())) for user in users]

  91.     monitors = Monitor.query.all()
  92.     [redis_store.rpush('monitors', json.dumps(monitor.to_dict())) for monitor in monitors]

  93. if __name__ == '__main__':
  94.     old_sql_2_redis()
复制代码

  把原来的数据库复制到本地redis了,看查看里面的元素。比如user数据,直接建一个user的list,每个appened其json格式的数据就可以了。主要注意点就是,如果新版本的表中没有的字段,就不要写入的redis了。

  第二步就是把本地redis里面的数据,放到新的数据库上。这边要注意以下,把需要修改的地方要标示出来,可能加了其他字段,这些字段没有默认值的话,要自己添加上。过程也比较简单。
  1. # coding:utf-8
  2. from sqlalchemy import create_engine, ForeignKey, Column, Integer, String, Text, DateTime, Boolean, and_, or_,\
  3.     SmallInteger, func, Numeric, select, Float, Table, TIMESTAMP, DECIMAL, desc
  4. from sqlalchemy.orm import relationship, backref, sessionmaker, scoped_session
  5. from sqlalchemy.ext.declarative import declarative_base
  6. from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method, Comparator
  7. from sqlalchemy import event
  8. import redis
  9. import json
  10. import datetime
  11. import uuid

  12. mysql_info = "mysql://username:password@ip_address:3306/database_name?charset=utf8"
  13. redis_store = redis.Redis(host='127.0.0.1', port=6380, password='password', db=5)

  14. engine = create_engine(mysql_info, pool_recycle=7200)
  15. Base = declarative_base()


  16. class User(Base):
  17.     __tablename__ = 'user'

  18.     id = Column('id', Integer, primary_key=True)
  19.     phone_number = Column('phone_number', String(11), index=True)
  20.     password = Column('password', String(30))
  21.     nickname = Column('nickname', String(30), index=True, nullable=True)
  22.     headpic = Column('headpic', String(100), nullable=True)
  23.     register_time = Column('register_time', DateTime, index=True, default=datetime.datetime.now)
  24.     user_admin = Column('user_admin', SmallInteger, index=True, default=0)
  25.     hidden_user = Column('hidden_user', SmallInteger, index=True, default=0)
  26.     rc_token = Column('rc_token', String(100), nullable=True)
  27.     device_token = Column('device_token', String(100), unique=True, nullable=True)
  28.     unit_id = Column('unit_id', Integer, ForeignKey('unit.id'))
  29.     unit = relationship('Unit', backref=backref('users'))


  30. class Monitor(Base):
  31.     __tablename__ = 'monitor'

  32.     id = Column('id', Integer, primary_key=True)
  33.     # device_type 1代表灭弧 2代表电气火灾
  34.     device_type = Column('device_type', SmallInteger, index=True, default=1)
  35.     u_id = Column('sn_num', String(10), index=True)
  36.     phone_num = Column('phone_num', String(20), index=True, nullable=True)
  37.     name = Column('name', String(40), index=True)
  38.     position = Column('position', String(40), nullable=True)
  39.     join_time = Column('join_time', DateTime, index=True)
  40.     longitude = Column('longitude', DECIMAL(12, 9), default=31.000000)
  41.     latitude = Column('latitude', DECIMAL(12, 9), default=121.000000)
  42.     unit_id = Column('unit_id', Integer, ForeignKey('unit.id'))


  43. class Unit(Base):
  44.     __tablename__ = 'unit'

  45.     id = Column('id', Integer, primary_key=True)
  46.     name = Column('name', String(100), index=True)
  47.     address = Column('address', String(100), index=True, nullable=True)
  48.     contact_person = Column('contactperson', String(30), nullable=True)
  49.     contact_number = Column('contactnumber', String(30), nullable=True)
  50.     device_operation_password = Column('device_operation_password', String(4), default='1234')
  51.     rc_group_id = Column('rc_group_id', String(36), default=str(uuid.uuid1()))
  52.     rc_group_name = Column('rc_group_name', String(50), nullable=True)
  53.     longitude = Column('longitude', DECIMAL(12, 9), default=31.000000)
  54.     latitude = Column('latitude', DECIMAL(12, 9), default=121.000000)
  55.     active = Column('active', SmallInteger, index=True, default=0)
  56.     is_group = Column('is_group', SmallInteger, index=True, default=0)
  57.     parent_id = Column('parent_id', Integer, ForeignKey('unit.id'))


  58. db_session = scoped_session(sessionmaker(autocommit=False,
  59.                                          autoflush=False,
  60.                                          bind=engine))

  61. Base.query = db_session.query_property()


  62. def redis_2_new_sql():
  63.     units_json = redis_store.lrange('units', 0, -1)
  64.     units = [Unit(is_group=0, parent_id=None, **json.loads(unit_json)) for unit_json in units_json]
  65.     [db_session.add(unit) for unit in units]
  66.     try:
  67.         db_session.commit()
  68.     except Exception as e:
  69.         print e
  70.         db_session.rollback()
  71.         return

  72.     users_json = redis_store.lrange('users', 0, -1)
  73.     users = [User(hidden_user=0, **json.loads(uer_json)) for uer_json in users_json]
  74.     [db_session.add(user) for user in users]
  75.     try:
  76.         db_session.commit()
  77.     except Exception as e:
  78.         print e
  79.         db_session.rollback()
  80.         return

  81.     monitors_json = redis_store.lrange('monitors', 0, -1)
  82.     monitors = [Monitor(**json.loads(monitor_json)) for monitor_json in monitors_json]
  83.     [db_session.add(monitor) for monitor in monitors]
  84.     try:
  85.         db_session.commit()
  86.     except Exception as e:
  87.         print e
  88.         db_session.rollback()
  89.         return

  90. if __name__ == '__main__':
  91.     redis_2_new_sql()
复制代码

  整个过程相当简单,需要注意的就是,前后顺序,因为user和monitor都有一个外键指向unit,所以要先恢复unit表,这样就不会出错了。

  看来以后我有新方法搬迁数据库了。
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部