怎样添加openbsd中的mysql支持GBK和gb2312字符集

OpenBSD的安装、升级、更新等日常问题。

版主: chenjun天地乾坤

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-01 19:19

我使用一台openbsd有两年时间,mysql默认是utf8,不支持GBK和gb2312,怎样才能添加使它支持GBK和gb2312呢?我在#/usr/ports/databases/mysql下执行了#make WITH_CHARSET=GBK WITH_CHARSET=gb2312 经过长时间编译,编译中间也没报错,用mysql> show charset; 但是结果还是不支持。怎样才能添加进去呢?请各位高手给我帮助谢谢

头像
leo
帖子: 2465
注册时间: 2010-01-21 3:27

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 leo » 2012-01-02 9:22

czjin 写了:我使用一台openbsd有两年时间,mysql默认是utf8,不支持GBK和gb2312,怎样才能添加使它支持GBK和gb2312呢?我在#/usr/ports/databases/mysql下执行了#make WITH_CHARSET=GBK WITH_CHARSET=gb2312 经过长时间编译,编译中间也没报错,用mysql> show charset; 但是结果还是不支持。怎样才能添加进去呢?请各位高手给我帮助谢谢

不支持表现在哪里?使用GBK2312编码的数据库报错?错误信息是什么?
有没有在/etc/my.cnf里进行字符集和编码设置?或者把my.cnf贴出来。

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-02 16:39

以下是我的my.cnf: 和 mysql>show charset;
# vi my.cnf
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/run/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout


#mysql -u root -p
#passwd
mysql> show char set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
+----------+-----------------------------+---------------------+--------+
26 rows in set (0.04 sec)
mysql> \q

头像
leo
帖子: 2465
注册时间: 2010-01-21 3:27

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 leo » 2012-01-03 11:20

这个并非官方的方式,如果大家知道更好的方式请提供,还有问题的话贴出来再探讨。

编辑 /usr/ports/databases/mysql/Makefile 文件,加入如下红色的内容:

代码: 全选

.......
CONFIGURE_ARGS+= --localstatedir="${DB_DIR}" \
                 --with-big-tables \
                 --with-comment='OpenBSD port: ${FULLPKGNAME-server}' \
                 --with-libwrap \
                 --with-low-memory \
                 --with-mysqld-user="_mysql" \
                 --with-plugins=max-no-ndb \
                 --with-ssl=/usr \
                 --with-unix-socket-path="${SOCKET_DIR}/mysql.sock" \
                 --without-docs \
                 --without-readline \
 [color="Red"]                --with-charset="gbk" \
                 --with-charset="gb2312" \[/color]
.....
编译、重新启动电脑:

代码: 全选

# make install-all clean

代码: 全选

# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.60-log OpenBSD port: mysql-server-5.1.60

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show char set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
[color="Red"][B]| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |[/B][/color]
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
+----------+-----------------------------+---------------------+--------+
27 rows in set (0.00 sec)

mysql> 

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-03 17:04

非常感谢leo,照您的指点gb2312确实添加进去了。但我有两个疑问,一个是在#/usr/ports/databases/mysql/Makefile 下--without-readline \ 默认没有斜杠,我是人为把它添加上了。二是mysql> show char set; 里只看到gb2312而没有gbk,代码是写进去了呀,这样有区别和影响吗。再次感谢!
mysql> show char set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
+----------+-----------------------------+---------------------+--------+
27 rows in set (0.00 sec)
mysql>

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-03 21:40

不行啊,把网站放上去还是如下提示,还是找不到gbk_chinese,请leo帮助,谢谢
Discuz! info: MySQL Query Error

Time: 2012-1-4 5:20am
Script: /sxxbl/index.php

SQL: SELECT * FROM [Table]crons WHERE available>'0' AND nextrun<='1325625654' ORDER BY nextrun LIMIT 1
Error: Unknown collation 'gbk_chinese_ci' in table '[Table]crons' definition
Errno.: 1273
http://faq.comsenz.com 搜索此错误的解决方案

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-03 21:49

gbk和gb2312有区别吗,它们之间是什么关系?

头像
leo
帖子: 2465
注册时间: 2010-01-21 3:27

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 leo » 2012-01-04 0:48

"\"是换行符,主要目的是增加可读性,这里加不加随意,最后一行可以没有。
gb2312是gbk的子集,要支持gbk,把gb2312那行注释掉即可,我在上面仅是举例,你需要根据自己的情况调整。——我不用gbk编码的程序,所以很多问题可能不是很了解,如有误、请指教。

此外,my.cnf可能也要做相应的调整,否则也许还有可能中文显示为乱码。

代码: 全选

....
[client]
default-character-set=gbk
...
[mysqld]
default-character-set=gbk
...
btw:为什么不用utf-8编码的discuz程序?如果你用gbk编码的discuz程序,用户如果安装的是非中文系统,那么看你的论坛内容可能是一堆乱码。

代码: 全选

mysql> show char set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
[color="Red"]| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |[/color]
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
+----------+-----------------------------+---------------------+--------+
27 rows in set (0.01 sec)
以上在i386 current + php 5.3.8p0 + mysql 5.1.60 下自测时可正常安装discuz的GBK版本:

代码: 全选

# pkg_info
autoconf-2.63       automatically configure source code on many Un*x platforms
bzip2-1.0.6         block-sorting file compressor, unencumbered
curl-7.23.1         get files from FTP, Gopher, HTTP or HTTPS servers
ddclient-3.8.0p3    Dynamic DNS service update client
femail-0.97p1       simple SMTP client
femail-chroot-0.97p3 simple SMTP client for chrooted apache
gettext-0.18.1p0    GNU gettext
groff-1.21p6        GNU troff typesetter
help2man-1.29p0     GNU help2man
jpeg-8c             IJG's JPEG compression utilities
libgcrypt-1.4.6p1   crypto library based on code used in GnuPG
libgpg-error-1.10   error codes for GnuPG related software
libiconv-1.14       character set conversion library
libidn-1.22         internationalized string handling
libltdl-2.4.2       GNU libtool system independent dlopen wrapper
libmcrypt-2.5.8p1   interface to access block/stream encryption algorithms
libxml-2.7.8p3      XML parsing library
libxslt-1.1.26p3    XSLT C Library for GNOME
metaauto-1.0        wrapper for gnu auto*
mysql-client-5.1.60 multithreaded SQL database (client)
mysql-server-5.1.60 multithreaded SQL database (server)
mysql-tests-5.1.60  multithreaded SQL database (regression test suite/benchmark)
p5-Clone-0.31p1     recursively copy Perl datatypes
p5-DBD-mysql-4.020  MySQL drivers for the Perl DBI
p5-DBI-1.616        unified perl interface for database access
p5-FreezeThaw-0.43p2 module for converting structures to strings and back
p5-IO-Socket-SSL-1.44 perl interface to SSL sockets
p5-MLDBM-2.04       store multi-level hash structure in single-level tied hash
p5-Net-Daemon-0.43p0 extension for portable daemons
p5-Net-SSLeay-1.38  perl module for using OpenSSL
p5-Params-Util-1.00p2 utility to make parameter checking easier
p5-PlRPC-0.2018p1   module for writing rpc servers and clients
p5-SQL-Statement-1.33 sql parsing and processing
php-5.3.8p0         server-side HTML-embedded scripting language
php-bz2-5.3.8p0     bzip2 compression extensions for php5
php-curl-5.3.8p0    curl URL library extensions for php5
php-gd-5.3.8p0      image manipulation extensions for php5
php-mcrypt-5.3.8p0  mcrypt encryption/decryption extensions for php5
php-mysql-5.3.8p0   mysql database access extensions for php5
php-mysqli-5.3.8p0  mysql database access extensions for php5
php-soap-5.3.8p0    SOAP functions for php5
php-xsl-5.3.8p0     XSL functions for php5
png-1.5.6           library for manipulating PNG images
t1lib-5.1.0p2       Type 1 rasterizer library for UNIX/X11
unzip-6.0p0         extract, list & test files in a ZIP archive

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-04 10:34

非常好,非常详细,有您这样的前辈指点我们学习openbsd确实倍感欣慰,谢谢。
我的情况是这样,我这两年一直在学习openbsd,做了web页和samba的应用,安装软件也始终用packages安装,字符集始终用utf-8,我感觉openbsd是一个非常棒的系统,短小稳定,认硬件非常足够的多,基本上没有出过什么事。这次我的一个朋友要把他在虚拟主机上的discuz7.2程序和mysql库,我在我们学校我管理的机器上做个备份,我也答应了。可是数据和库移过来就出现了没有gbk编码的支持,原来也想将他的数据及库统统转为utf8,但因为是做备份所以就得改我这边,没想到还挺难弄的。将gbk的数据和库都转为utf8这样可行吗?因为我的机器里还有其他web页呢全是utf8的。leo前辈给我个解决方案。谢谢!就是这个站:http://www.sxxbl.com

头像
leo
帖子: 2465
注册时间: 2010-01-21 3:27

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 leo » 2012-01-04 11:11

czjin兄太客气了,别提指点了,没瞎指挥就不错了,discuz的数据库编码转换可以到discuz自己的论坛上、也就是discuz.net上问问,我记得有称为“手拉手”义务转换服务。
因为我对discuz的程序确实不熟悉(discuz很多的内容全在服务器的文件系统里、而不是在mysql数据库里,所以备份时可能需要先全部导入数据库或者另外单独备份一些内容才可以完整恢复)。

mysql数据库转码有相关的命令,即便不熟悉mysql也没有关系,先将数据库备份下来,然后在你熟悉的系统里操作,例如我原来也在windows下用emeditor转换过sql文件的编码,实际你可以用任何自己熟悉的编辑器操作,只要识别多种编码即可(UTF8 和 GBK)。

先以GBK编码打开这个数据库(假设gbk.sql),然后另外保存为一个UTF-8编码格式的文件(假设utf-8.sql),然后将utf-8.sql里面相关表的gbk字符集修改为utf8就行了(修改文件里的字符串)。——不过有些表可能这类软件处理不了,例如附件、图片......... ,这还需要做单独的处理。

建议最好直接找“手拉手”里的专业人士来做,这样比较保险。

此外,这里给出的方法仅可以全新安装成功,并不见得是完全准确得,你可以试试可否正常还原你的数据库,换句话说,sql文件里的帖子相关表的内容是否编码正常,例如用GBK/UTF-8编码打开文件时、这些表(帖子内容)里得内容显示的是正常的中文。

再列一下我的mysql参数吧,仅有一个character_set_system是UTF-8,我也不清楚这个是否需要修改为GBK,你还原时应该看得出来:

代码: 全选

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | gbk                              |
| character_set_connection | gbk                              |
| character_set_database   | gbk                              |
| character_set_filesystem | binary                           |
| character_set_results    | gbk                              |
| character_set_server     | gbk                              |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+----------------+
| Variable_name        | Value          |
+----------------------+----------------+
| collation_connection | gbk_chinese_ci |
| collation_database   | gbk_chinese_ci |
| collation_server     | gbk_chinese_ci |
+----------------------+----------------+
3 rows in set (0.00 sec)

mysql> 

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-05 9:21

leo兄太牛了按照您的指点我已经使论坛正常显示了,检查了几个页面没有发现错误。当然我做测试用的是全新系统,全新安装,下面的问题就是要解决在已有utf8网页系统的里怎样把GBK添加进去,而且utf8和gbk互不影响,我原来都是用packages安装的,现在删除mysql再用ports装一遍行吗,那么多的依赖关系怎么办,还请leo兄指点,谢谢。

头像
leo
帖子: 2465
注册时间: 2010-01-21 3:27

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 leo » 2012-01-05 10:07

czjin 写了:leo兄太牛了按照您的指点我已经使论坛正常显示了,检查了几个页面没有发现错误。当然我做测试用的是全新系统,全新安装,下面的问题就是要解决在已有utf8网页系统的里怎样把GBK添加进去,而且utf8和gbk互不影响,我原来都是用packages安装的,现在删除mysql再用ports装一遍行吗,那么多的依赖关系怎么办,还请leo兄指点,谢谢。
我没有明白你的意思,什么是“已有UTF-8网页的系统里怎么添加把GBK添加进去“?你的意思是mysql里还有其它的UTF-8的数据库?

如果不同编码的数据库共存在系统里,my.cnf 这里还是这样设置为妥:

代码: 全选

....
[client]
default-character-set=[color="Red"][B]utf8[/B][/color]
...
[mysqld]
default-character-set=[B][color="red"]utf8[/color][/B]
...
你可以为GBK的discuz单独建立一个数据库试试,例如:

代码: 全选

# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.54-log OpenBSD port: mysql-server-5.1.54p6
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE DATABASE `gobsd` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
 
mysql> grant all on gobsd.* to leo@localhost identified by 'gobsd.org';
Query OK, 0 rows affected (0.01 sec)
 
mysql>quit
这样应该是多个数据库互不干扰吧。

依赖关系很麻烦,如果搞不清楚,想快刀斩乱麻,可以删除原来安装的所有packages,这样:

代码: 全选

pkg_delete -q /var/db/pkg/*
然后按照提示做相关处理。

如果网速不行,先删除mysql-server 和 mysql-client, 肯定直接删除不了,这时根据提示先删除依赖包再删除这两个packages, 如果环境比较重要建议先在虚拟机上测试一下再到真实环境里操作。

安装好支持GBK的mysql后,在mysql的命令行里可以直接转换数据库编码。—— 不过据我所知,很多discuz用户更愿意用GBK编码的discuz是因为很多插件不兼容于UTF-8的discuz程序,一个方法是修改插件,另一个方法就是你自己辛苦些,搭建一个兼容于GBK的环境(最好搭建好以后将自己的数据库备份后再到原来的空间上还原一下,看看是否有问题,有时不见得是你的问题,但是可能原有空间的数据库编码设置的就不正常导致无法还原,这时你还要根据原有空间的设置调整,所以我建议还是找“手拉手”里的discuz专业人士来操作为好。),这样也便于今后将数据库“完好无损”地交还给人家。

Mysql因为涉及到支持GBK编码,所以要手动编译,剩下的仍可以使用packages的方式来安装,这样可以节省一些时间。

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-05 13:16

真不好意识我没叙述清楚,我是想这样,在根下装utf8,在根下级目录装gbk,
http://utf8/gbk/”因为是备份想采取这样的方法,不知行不行?
您说的非常正确必须在虚拟机上测试完成后再动手往实际机器上应用.我是这样做的。我是新建了一个名为discuz数据库,是用phpMyAdmin建的,没有用命令行建立,我下次就用命令行建立。谢谢

头像
leo
帖子: 2465
注册时间: 2010-01-21 3:27

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 leo » 2012-01-05 13:52

czjin兄别客气,大家一起探讨。
这样做应该没有问题,我记得discuz并不要求必须安装在网站的根目录下,而且即便真有此要求也可以通过自定义hosts解决,所以我觉得问题不大,先测试吧,否则全是纸上谈兵。
重新编译前先删除以往编译生成的packages。

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-06 22:25

leo兄:不行啊,先删除mysql-server 和 mysql-client后
mysql> show char set; gbk显示不出来,
#pkg_delete -q /var/db/pkg/* 再装gbk还是显示不出来,
下面是pkg_info和show char set; 只有系统重装再试试,重装倒数据库和修改代码手术就大了,不过也值得试一试。
# pkg_info
autoconf-2.63 automatically configure source code on many Un*x platforms
groff-1.21p4 GNU troff typesetter
metaauto-1.0 wrapper for gnu auto*
mysql-client-5.1.54p0 multithreaded SQL database (client)
mysql-server-5.1.54p9 multithreaded SQL database (server)
mysql-tests-5.1.54 multithreaded SQL database (regression test suite/benchmark)
p5-Clone-0.31p1 recursively copy Perl datatypes
p5-DBD-mysql-4.019 MySQL drivers for the Perl DBI
p5-DBI-1.616 unified perl interface for database access
p5-FreezeThaw-0.43p2 module for converting structures to strings and back
p5-MLDBM-2.04 store multi-level hash structure in single-level tied hash
p5-Net-Daemon-0.43p0 extension for portable daemons
p5-Params-Util-1.00p2 utility to make parameter checking easier
p5-PlRPC-0.2018p1 module for writing rpc servers and clients
p5-SQL-Statement-1.33 sql parsing and processing
#
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show char set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
+----------+-----------------------------+---------------------+--------+
26 rows in set (0.02 sec)
mysql> \q
Bye

头像
leo
帖子: 2465
注册时间: 2010-01-21 3:27

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 leo » 2012-01-06 22:59

/usr/ports/packages/i386/all 里的packages清空了吗?——否则系统不会重新编译,而是会直接用原有的packages,用你的平台名称替换上面的“i386”
此外:
我试了试,my.cnf这里设置为utf8的字符集,可以同时安装GBK和UTF8版本的discuz 6,所以没有问题,现在只需要你测试一下可否将你朋友的数据库还原到你的系统上了。

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-07 11:05

噢!真是要leo兄指点呢,我不知道要清空/usr/ports/packages/i386/all 里的packages,只是觉得一下子就装完了,没有任何编译的过程,这下明白了。谢谢啊
这个站的数据库因为太大我是拷贝进去的,修改了权限和属主,能够显示,有些页面链接错误,大体上是正常的。
my.cnf这里设置为utf8的字符集我没有操作,它已经认了,只是现在在虚拟机上运行的,192.168的地址链接看不到,插入图片咱们论坛是那个按钮我没找到地方,请leo兄告诉我,以下是我的my.cnf,我把有些注释去掉了。
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/run/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

头像
leo
帖子: 2465
注册时间: 2010-01-21 3:27

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 leo » 2012-01-07 12:36

在虚拟机的hosts里将本地设置为“www.sxxbl.com”试试,看看是不是因为域名导致的图片无法显示。同样浏览网站时也暂时将客端的hosts设置一下,将“www.sxxbl.com”指到虚拟机上。

关于怎样贴附件图片,你发帖时点“高级模式”,会看到下面有“管理附件”按钮,点击该按钮就可以上传图片了,不过图片大小有限制。

关于是否设置UTF8要看mysql版本,最新的版本可能默认就是utf8,所以不设可能也可以。
但是我看您的这里

代码: 全选

socket = /var/run/mysql/mysql.sock
没有设置为

代码: 全选

socket = /var/www/var/run/mysql/mysql.sock
为什么?是带 -u 参数运行的apache1.3吗?或者运行的是apache2?

czjin
钛 Ti
帖子: 26
注册时间: 2011-12-31 15:47

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 czjin » 2012-01-07 19:21

是的,我是在#/etc/rc.conf里将httpd设置为“-u”运行的apache1.3。因为刚学openbsd时对chroot这个设置的概念很不好理解,而且出过好几次错误,所以就一直用“-u”了,好像也没出什么差错,这个设置“”和“-u”有区别吗?
上传附件图片的地方我已找到了,hosts我会改动测试告诉您的。
说真心话就这几天我在leo兄这里学到很多openbsd的知识,谢谢

头像
leo
帖子: 2465
注册时间: 2010-01-21 3:27

回复: 怎样添加openbsd中的mysql支持GBK和gb2312字符集

帖子 leo » 2012-01-08 0:02

czjin 写了:是的,我是在#/etc/rc.conf里将httpd设置为“-u”运行的apache1.3。因为刚学openbsd时对chroot这个设置的概念很不好理解,而且出过好几次错误,所以就一直用“-u”了,好像也没出什么差错,这个设置“”和“-u”有区别吗?
上传附件图片的地方我已找到了,hosts我会改动测试告诉您的。
说真心话就这几天我在leo兄这里学到很多openbsd的知识,谢谢
有些是经验,但不见得就没有问题,大家一起探讨。
以下是我的理解,有不对的地方请指教。
“-u”和“”是有区别的,前者的apache没有运行在chroot环境里,可以调用chroot环境以外的程序,按照FAQ上的说法是不太安全,也就是说一旦web服务器有什么问题,例如php或者mysql甚至是修补过的apache1.3有什么漏洞,有可能造成基本系统受到攻击,而使用""时即便web服务器有什么问题,甚至崩溃或被完全控制了,受损范围也仅限于/var/www/内,甚至/var/www/htdocs内。所以我的理解是如果没有功能上的需要,建议还是使用建议的“”,或者加入其它确保安全的特定的参数。不过FAQ上也说了,并非所有的程序陡可以放进chroot环境,而且这样做也没有意义。不知道czjin兄出过什么“问题”,是不是有什么程序无法放进chroot环境里?

此外有关数据库,最好发一个中文帖子,然后再将数据库导出来,看一下相关表的编码是否正常,有的时候汉字可以以其它编码保存在数据库里(例如ISO-8859-1),而你浏览时一样可以显示汉字,但是编码的设置还是有问题的,这样的话今后可能在一份数据库里出现几种不同编码的中文,肯定不是你所希望的。所以想保险的话,最好在my.cnf里设置好编码。,这样可以一劳永逸。

回复

在线用户

正浏览此版面之用户: 没有注册用户 和 1 访客