resolve Emoji Emotion symbols when save to mysql<5.5

1. what’s Emoji Emotion symbols: it’s usually post by iPhone client;

2. when the content include a Emoji from API, it’s always a [] like “[]晚安俩宝”, actually it is a moon emotion, but when you insert it into a utf-8 mysql database , you will get a warning as: “Incorrect string value: ‘\xF0\x9F\x94\x8E’ for column ‘line’ at row 1.

The string ‘\xF0\x9F\x91\x8A, actually is a 4-byte unicode: u’\U0001f62a’, but utf-8 database could not accept a 4-byte, mysql under 5.5.3 don’t support 4-byte unicode.

3. using re.compile method you could replace all the 4-byte unicode to something you want.

[cce]
>>> import re
>>> highpoints = re.compile(u'[\U00010000-\U0010ffff]')
>>> example = u'Some example text with a sleepy face: \U0001f62a'
>>> highpoints.sub(u'[我是笑脸]', example)
u'Some example text with a sleepy face:[我是笑脸] '
[/cce]

here the "\U0001f62a" is just a Emoji smile face.
you can print that in python shell to confirm that.
4. if still can't print, check your Locale settings by tpye: locale
be sure to set :
[cce]
LANG=en_US.UTF-8
LC_ALL="en_US.UTF-8"
[/cce]
or you can type below to make the session take effect
[cce]
export LANG='en_US.UTF-8'
export LC_ALL='en_US.UTF-8'
[/cce]

mysql 自动备份 脚本

automysqlbackup-2.5.1-01.sh  最新版 点这里下载

使用说明:

1. 修改文件里的配置部分包括访问mysql的用户名密码,要备份的数据库,备份地址,发送邮件的地址(默认使用postfix):

[cce]
    # Username to access the MySQL server e.g. dbuser
	USERNAME=debian

	# Password to access the MySQL server e.g. password
	PASSWORD=

	# Host name (or IP address) of MySQL server e.g localhost
	DBHOST=localhost

	# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
	DBNAMES="all"

	# Backup directory location e.g /backups
	BACKUPDIR="/srv/backup/db"

	# Mail setup
	# What would you like to be mailed to you?
	# - log   : send only log file
	# - files : send log file and sql files as attachments (see docs)
	# - stdout : will simply output the log to the screen if run manually.
	# - quiet : Only send logs if an error occurs to the MAILADDR.
	MAILCONTENT="log"

	# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
	MAXATTSIZE="4000"

	# Email Address to send mail to? (user@domain.com)
	MAILADDR="maintenance@example.com"
[/cce]

也可以把上面的东西写到一个配置文件里:

[cce]CONFIGFILE="/etc/automysqlbackup/automysqlbackup.conf"[/cce]

2. 给脚本增加可执行权限

[cce]chmod +x /root/automysqlbackup-2.5.1-01.sh[/cce]
3.用crontab定一个任务:比如每周一的零点备份
[cce]
crontab e

#输入下面的任务:
#格式为dom=day of month
#m h  dom mon dow   command

0 0 * * 1 /var/www/sqlback/automysqlbackup.sh
[/cce]

4.安装postfix,备份日志会自动发送到上面指定的邮箱
[cce]
# for redhat
yum install postfix

# for ubuntu
apt-get install postfix
[/cce]
大功告成,该脚本会在备份文件夹里自动建立monthly,weekly,daily的文件夹