Inception备份库表数据中文乱码解决

HH
MySQL pythonInception备份库表数据中文乱码解决已关闭评论8,3811字数 3080阅读10分16秒阅读模式

MySQL环境

字符集: utf8mb4

乱码重现

  1. 要执行的 python 代码
#!/usr/bin/python
# -*- coding: utf-8 -*-
 
import MySQLdb
import sys
 
reload(sys)
sys.setdefaultencoding('utf-8')
 
sql= """
    /*--user=HH;--password=oracle;--host=192.168.1.233;--execute=1;--enable-remote-backup;--port=3307;*/
    inception_magic_start;
    use test;
    ALTER TABLE alifeba_user
        MODIFY username VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名',
        MODIFY realname VARCHAR(50) NOT NULL DEFAULT '' COMMENT '真实姓名';
    inception_magic_commit;
"""
 
try:
    conn=MySQLdb.connect(host='127.0.0.1',
                         user='HH',
                         passwd='oracle',
                         db='inception',
                         port=6669)
    cur=conn.cursor()
    ret=cur.execute(sql)
    result=cur.fetchall()
    num_fields = len(cur.description)
    field_names = [i[0] for i in cur.description]
    print ' | '.join(field_names)
    for row in result:
        print ' | '.join([str(col) for col in row])
    cur.close()
    conn.close()
except MySQLdb.Error, e:
    err_msg = 'Mysql Error {arg1}: {arg2}'.format(
                                     arg1 = e.args[0],
                                     arg2 = e.args[1])
    print err_msg

2.执行后查看备份库中 $_$inception_backup_information$_$ 和 alifeba_user 表记录如下输出文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/ 

SELECT * FROM $_$inception_backup_information$_$ \G
*************************** 1. row ***************************
        opid_time: 1473822723_81_1
start_binlog_file: 
 start_binlog_pos: 0
  end_binlog_file: 
   end_binlog_pos: 0
    sql_statement: ALTER TABLE alifeba_user
        MODIFY username VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户å<U+0090><U+008D>',
        MODIFY realname VARCHAR(50) NOT NULL DEFAULT '' COMMENT '真实姓å<U+0090><U+008D>'
             host: 192.168.1.233
           dbname: test
        tablename: alifeba_user
             port: 3307
             time: 2016-09-14 11:12:03
             type: ALTERTABLE
1 row in set (0.00 sec)
 
SELECT * FROM alifeba_user \G                      
*************************** 1. row ***************************
                id: 1
rollback_statement: ALTER TABLE `test`.`alifeba_user` CHANGE COLUMN `username` `username` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名' ,CHANGE COLUMN `realname` `realname` varchar(50) NOT NULL DEFAULT '' COMMENT '真实姓名' ;

从上面的输出可以看到 $_$inception_backup_information$_$ 中的数据已经乱码了文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/

3.将sql中添加 set names utf8mb4; 如下部分代码文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/ 

sql= """
    /*--user=HH;--password=oracle;--host=192.168.1.233;--execute=1;--enable-remote-backup;--port=3307;*/
    inception_magic_start;
    use test;
    set names utf8mb4;
    ALTER TABLE alifeba_user
        MODIFY username VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名',
        MODIFY realname VARCHAR(50) NOT NULL DEFAULT '' COMMENT '真实姓名';
    inception_magic_commit;
"""

4.执行修改后的代码再次查看 $_$inception_backup_information$_$ 和 alifeba_user 表记录文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/ 

SELECT * FROM $_$inception_backup_information$_$ \G
*************************** 1. row ***************************
        opid_time: 1473823848_90_2
start_binlog_file: 
 start_binlog_pos: 0
  end_binlog_file: 
   end_binlog_pos: 0
    sql_statement: ALTER TABLE alifeba_user
        MODIFY username VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名',
        MODIFY realname VARCHAR(50) NOT NULL DEFAULT '' COMMENT '真实姓名'
             host: 192.168.1.233
           dbname: test
        tablename: alifeba_user
             port: 3307
             time: 2016-09-14 11:30:48
             type: ALTERTABLE
1 row in set (0.00 sec)
 
SELECT * FROM alifeba_user \G
*************************** 1. row ***************************
                id: 1
rollback_statement: ALTER TABLE `test`.`alifeba_user` CHANGE COLUMN `username` `username` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名' ,CHANGE COLUMN `realname` `realname` varchar(50) NOT NULL DEFAULT '' COMMENT '真实姓名' ;
         opid_time: 1473823848_90_2
1 row in set (0.00 sec)

由上面可以看到中文乱码已经解决文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/

 文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/

昵称: HH文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/

QQ: 275258836文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/

ttlsa群交流沟通(QQ群②: 6690706 QQ群③: 168085569 QQ群④: 415230207（新） 微信公众号: ttlsacom)文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/

感觉本文内容不错，读后有收获？文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/

逛逛衣服店，鼓励作者写出更好文章。文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
HH
  • 本文由 发表于 18/10/2016 00:16:00
  • 转载请务必保留本文链接：https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
  • inception
  • mysql
  • python